当MySQL单表记录数过多时，增删改查性能会急剧下降。可以参考以下步骤进行优化：单表优化除非以后单表数据会不断增加，一开始就不要考虑拆分。拆分会带来逻辑、部署、运维等方面的各种复杂性。一般整数值在1000万以下的表和字符串在500万以下的表问题不大。其实在很多情况下，MySQL单表的性能还是有很大的优化空间，甚至可以正常支持千万级以上的数据量：尽量使用TINYINT、SMALLINT、MEDIUM_INT等整数类型，而不是INT为字段，并加上UNSIGNEDVARCHAR的长度，只分配真正需要的空间。使用枚举或整数而不是字符串类型。尝试使用TIMESTAMP而不是DATETIME。单个表中的字段不应太多。建议避免使用20以内的NULL字段，难以优化查询，占用额外空间。索引空间使用整数来存储IP索引。索引越多越好，应该根据查询来创建。考虑在WHERE和ORDERBY命令中涉及的列上建立索引。可以根据EXPLAIN查看是否使用了索引。在全表扫描中，尽量避免判断WHERE子句中字段的NULL值，否则引擎会放弃使用索引而进行全表扫描。值分布非常稀有的字段不适合做索引，比如“性别”，它只有两个或三个值的字符字段，只建立前缀索引。字符字段最好不要使用主键也不要使用外键。程序保证约束尽可能不UNIQUE。单列索引查询SQL可以通过打开慢查询日志找出慢SQL。无列操作：SELECTidWHEREage+1=10，对列的任何操作都会引起表扫描，其中包括数据库教程函数，计算表达式等，查询时将操作移到等号右边为尽可能多。sql语句尽量简单：一条sql只能在一个cpu上运行；大语句拆分成小语句，减少锁时间；一个大的sql可以阻塞整个库不要把SELECT*OR改写成IN：OR的效率是n级，IN的效率是log(n)级。建议在不使用函数和触发器的情况下将in的数量控制在200以内,在应用的实现中，避免%xxx查询，少用JOIN，使用同类型进行比较，比如比较'123'和'123'，比较123和123尽量避免使用!=或<>运算符在WHERE子句中，否则引擎将放弃使用索引并执行全表扫描。连续值，用BETWEEN代替IN：SELECTidFROMtWHEREnumBETWEEN1AND5list数据不要用全表，用LIMIT分页，每页页数不要太大引擎是目前广泛使用的MyISAM和InnoDB两种引擎：MyISAMMyISAM引擎是MySQL5.1及更早版本的默认引擎，其特点是：不支持行锁，读时锁定所有需要读的表，写时给表加排它锁，不支持事务，不支持外键，不支持崩溃后安全恢复，支持在表有读查询的情况下向表中插入新记录，支持BLOB和TEXT前500个字符的索引，支持全文索引支持索引的延迟更新，大大提高了写入性能。对于不会修改的表，支持压缩表，大大减少了占用的磁盘空间。InnoDBInnoDB在MySQL5.5之后成为默认索引。MVCC支持高并发，支持事务，支持外键，支持崩溃后安全恢复，不支持全文索引ps：据说InnoDB在MySQL5.6.4已经支持全文索引了，一般来说MyISAM是适用于SELECT密集型表，而InnoDB适用于INSERT型UPDATE密集型表系统调优参数，可以使用以下工具进行基准测试：sysbench：模块化、跨平台、多线程的性能测试工具iibench-mysql：基于Java的MySQL/Percona/MariaDB索引插入性能测试工具tpcc-mysql:Percona开发的TPC-C测试工具，有很多具体的调优参数。详情请参考官方文档。下面是一些重要的参数：back_log：back_log值表示在MySQL暂时停止响应新请求之前的短时间内，在一定时间内堆栈中可以存储多少请求。也就是说如果MySql的连接数据达到了max_connections，新的请求就会入栈等待一个连接释放资源。栈的数量是back_log，如果等待连接数超过back_log，连接资源将不会被授予。可以从默认的50增加到500wait_timeout：数据库连接的空闲时间，空闲连接会占用内存资源。可以从默认的8小时减少到半小时max_user_connection：最大连接数，默认0没有上限，最好设置一个合理的上限thread_concurrency：并发线程数，设置为CPU核数的两倍skip_name_resolve：禁止对外部连接进行DNS解析，免去DNS解析时间，但所有远程主机都需要使用IP访问key_buffer_size：索引块的缓存大小，提高索引处理速度会提高性能MyISAM表。对于4G左右的内存，可以设置为256M或者384M。通过查询“key_read%”之类的显示状态，最好确保key_reads/key_read_requests低于0.1%。通过查询showstatuslike'Innodb_buffer_pool_read%'，确保(Innodb_buffer_pool_read_requests–Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests越高越好innodb_additional_mem_pool_size：InnoDB存储引擎用于存储数据字典信息和一些内部数据结构内存空间大小，当数据库较多时objects，适当调整该参数的大小，保证所有的数据都能存放在内存中，提高访问效率。太小时，MySQL会在数据库的错误日志中记录Warning信息。这时候需要调整innodb_log_buffer_size这个参数的大小：一般来说，InnoDB存储引擎的事务日志使用的buffer不建议超过32MB。当某个表的数据发生变化时，所有引用该表的select语句在QueryCache中的缓存数据都会失效。因此，当我们的数据变化非常频繁时，使用QueryCache可能就得不偿失了。根据命中率调整(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))，一般不建议太大，256MB可能差不多，大的配置静态数据可以适当调整。可以使用命令showstatuslike'Qcache_%'查看当前系统Querycatchsizeread_buffer_size：MySql读取缓冲区大小。对表进行顺序扫描的请求将分配一个读取缓冲区，而MySql将为其分配一个内存缓冲区。如果表的顺序扫描请求非常频繁，可以通过增大该变量的值和内存缓冲区的大小来提高其性能。如果你想提高ORDERBY的速度，首先看看你是否可以让MySQL使用索引而不是额外的排序阶段。如果不行，可以尝试增加sort_buffer_size变量的大小read_rnd_buffer_size：MySql随机读缓冲区大小。当以任意顺序（例如，按排序顺序）读取行时，会分配一个随机读取缓冲区。在进行排序查询时，MySql会先扫描缓冲区，避免磁盘搜索，提高查询速度。如果需要排序的数据量很大，可以适当增加这个值。但是MySql会为每个客户端连接释放这个缓冲空间，所以你应该尽量适当地设置这个值，以避免过多的内存开销。record_buffer：每个执行顺序扫描的线程为其扫描的每个表分配一个这个大小的缓冲区。如果你做很多顺序扫描，你可能想增加这个值thread_cache_size：保存当前没有关联到一个连接但准备好稍后为新连接服务的线程，并且可以快速响应线程对连接的请求而无需创建新的table_cache：与thread_cache_size类似，但用于缓存表文件，对InnoDB影响不大。主要用于MyISAM升级硬件Scaleup。我不会说太多。根据MySQL是CPU密集型还是I/O密集型，通过增加CPU和内存以及使用SSD，两者都可以显着提高MySQL的性能。读写分离也是目前常用的优化方式。一般来说，不要使用双主或多主从主数据库读取和写入引入很多复杂性。尝试使用文章中的其他解决方案来提高性能。同时，目前很多拆分方案也考虑到了读写分离。Cache缓存可以发生在这几个层面：MySQL内部：在系统调优参数中，引入相关设置数据访问层：比如MyBatis缓存SQL语句，Hibernate可以精确到单条记录。这里缓存的对象主要是持久化对象。TransferObjectWeb层：网页缓存。浏览器客户端：客户端的缓存可以根据实际情况添加一级缓存或者多级组合缓存。这里着重介绍下服务层的缓存实现。目前主要有两种方式：WriteThrough：数据写入数据库后，同时更新缓存，保持数据库和缓存的一致性。这就是大多数当前应用程序缓存框架（如SpringCache）的工作方式。这种实现非常简单，同步性好，但通常效率低下。WriteBack：当有数据要写入数据库时??，只更新缓存，然后将缓存的数据异步批量同步到数据库。这种实现比较复杂，需要更多的应用逻辑，可能会导致数据库和缓存不同步，但是效率很高。TablePartitionMySQL在5.1版本引入的分区是一种简单的水平分割。用户创建表时需要添加分区参数，对应用透明，不需要修改代码。对于用户来说，分区表是一个独立的逻辑。表，但底层是由多个物理子表组成的。实现分区的代码其实是由底层表的一组对象封装的，但是对于SQL层来说，它是一个完全封装底层的黑盒。MySQL实现分区的方式也意味着索引也是根据分区的子表来定义的。没有全局索引。Mysql优化用户的SQL语句，针对分区表进行优化。SQL条件中必须包含分区条件的列，这样查询才能定位到少量的分区，否则会扫描所有分区。可以使用EXPLAINPARTITIONS查看某条SQL语句会落在哪些分区上，从而进行SQL优化，如下图，5条记录落在两个分区上：mysql优化分区好处是：可以做单表存储更多的数据，分区表的数据更容易维护。可以通过清空整个分区来批量删除大量数据，也可以添加新的分区来支持新插入的数据。此外，您还可以对独立的分区进行优化、检查、修复等操作。有些查询可以从查询条件判断只落在少数分区上，速度会很快。分区表中的数据还可以分布在不同的物理设备上，这很有趣。多个硬件设备可以使用分区表来避免一些特殊的瓶颈，比如InnoDB的单个索引的互斥访问，ext3文件系统的inode锁竞争可以备份和恢复单个分区分区的限制和缺点：一张表最多只能有1024个分区如果分区字段中有主键或唯一索引列，则必须包括所有主键列和唯一索引列。分区表不能使用外键约束。NULL值将使分区过滤失效。所有分区必须使用相同的存储引擎。类型：RANGE分区：根据属于给定连续区间的列值分配多行分区LIST分区：类似于RANGE分区，不同的是LIST分区是根据列值匹配一个值在用于选择的一组离散值HASH分区：根据使用要插入表的行的列值计算的用户定义表达式的返回值进行选择分区。这个函数可以包含任何在MySQL中有效的表达式，它产生一个非负整数值。KEY分区：类似于HASH分区，不同的是KEY分区只支持计算一列或多列，MySQL服务器提供了自己的哈希函数。必须有一个或多个包含整数值的列。适合分区的场景有：最适合的场景。数据的时间序列比较强，所以可以按时间分区，如下所示：YEAR(joined))(PARTITIONp0VALUESLESSTHAN(1960),PARTITIONp1VALUESLESSTHAN(1970),PARTITIONp2VALUESLESSTHAN(1980),PARTITIONp3VALUESLESSTHAN(1990),PARTITIONp4VALUESLESTHANMAXVALUE);查询时加上时间范围条件会非常高效，同时，不必要的历史数据可以很容易地批量删除。如果数据有明显的热点，而除了这部分数据之外其他数据很少被访问，那么可以将热点数据放在一个单独的分区中，让这个分区中的数据有机会缓存??到内存中，并且只在查询的时候访问一个小的分区表，可以有效的使用索引和缓存。另外，MySQL有一个早期的简单分区实现——合并表（mergetable），限制较多，缺乏优化。不推荐使用，应该换成新的分区机制垂直拆分垂直拆分是基于数据库中数据表的相关性。比如一个数据库中既有用户数据又有订单数据，那么垂直拆分可以将用户数据放入用户数据库和订单数据中。数据放在订单库中。垂直拆分表是一种垂直拆分数据表的方法。将一个大的多字段表按常用字段和不使用字段拆分是很常见的。每个表的数据记录条数一般是一样的，只是字段不一样。使用主键关联。比如原来的用户表是：mysql优化垂直拆分。mysql优化垂直拆分的优点是：可以让行数据变小，可以存储一个数据块（Block）更多的数据会减少查询时的I/O次数（每次查询读取的块数会beless）来达到最大化利用Cache的目的。具体来说，垂直拆分时，可以把不经常变化的字段放在一起，经常变化的数据放在一起维护简单缺点是：主键冗余，需要管理冗余列会导致表连接JOIN操作（增加CPU开销）可以减少业务服务器上加入数据库压力依然存在单表数据过多的问题（需要水平拆分）事务处理复杂水平拆分概述水平拆分是通过一定的策略将数据分段存储。会分布到不同的MySQL表或库中，达到分布式的效果，可以支持非常大的数据量。前面的表分区本质上是数据库中一种特殊的子表。简单的解决了单表数据量过大的问题。由于表中的数据并没有分布到不同的机器上，所以对于缓解MySQL服务器的压力来说是非常重要的，但是并没有太大的作用。大家还是在同一台物理机上争夺IO、CPU、网络。这个需要分库来解决。如果将之前垂直拆分的user表水平拆分，结果是：在mysql优化的实际情况中，往往是垂直拆分和水平拆分的结合，即将Users_A_M和Users_N_Z拆分为Users和UserExtras。四表水平拆分的优点是：没有单库大数据和高并发性能瓶颈应用端改造少提高系统稳定性和负载能力缺点是：分片事务一致性难以解决跨节点Join性能差,逻辑复杂的数据难以多次扩展维护大量分片原则可以没有区别，没有区别，参考单表优化，分片数尽量少，分片均匀分布在多个数据节点，因为跨分片的SQL查询越多，整体性能越差，虽然比所有数据分片的结果，只在必要的时候扩容，增加分片的数量。分片规则需要提前仔细选择和规划。分片规则的选择需要考虑数据的增长方式、数据访问方式和分片。关联问题，以及分片扩展问题，最近的分片策略是范围分片、枚举分片和一致性哈希分片。这些分片都有利于扩容。一个分片，分布式事务一直是一个比较难处理的问题。尽量优化查询条件，尽量避免Select*方式。大量的数据结果集会消耗大量的带宽和CPU资源。查询时尽量避免返回大量的结果集，尽量为经常使用的查询建立索引。通过数据冗余和表分区减少跨库连接的可能性。这里特别强调分片规则的选择。如果某个表中的数据有明显的时间特征，比如订单、交易记录等，通常更适合。时间范围分片。由于数据具有时效性，我们往往会关注它最近的数据。查询条件通常使用时间字段进行过滤。更好的解决方案是对当前活动数据使用相对较短的时间跨度。分片，历史数据存储的跨度比较大。一般来说，分片的选择取决于最频繁查询SQL的条件，因为没有任何Where语句的查询SQL会遍历所有分片，性能相对最差，所以这样的SQL越多，影响越大对系统的影响越大，所以要尽量避免这条SQL的产生。解决方案由于水平拆分涉及的逻辑复杂，目前已经有很多成熟的解决方案。这些方案分为两大类：客户端架构和代理架构。客户端架构通过修改数据访问层管理多个数据源，如JDBC、DataSource、MyBatis，通过配置，直接连接数据库，在模块内完成数据的分片与整合。一般以Jar包的形式呈现。ThisisaExampleofclientarchitecture:mysqloptimization可以看到分片是和应用服务器一起实现的。通过修改SpringJDBC层实现客户端架构的优点是：应用直接连接数据库，减少了对外围系统的依赖，宕机风险低。集成成本低。不需要额外运维的组件的缺点是：仅限于在数据库访问层做文章，扩展性一般。对于更复杂的系统，它可能无法如愿以偿。分片逻辑给应用服务器带来压力，带来额外的风险。代理架构通过独立的中间件管理所有数据源和数据分片集成。后端数据库集群对前端应用透明，需要代理组件独立部署和运维。这是一个代理架构的例子：mysql优化的代理组件一般以集群的形式存在，目的是为了分流和防止单点。同时可能需要Zookeeper等服务组件来管理代理架构。优点是：可以处理非常复杂的需求，不受数据库访问层原有实现的限制。强大的可扩展性对应用服务器是透明的，不会增加任何额外的负载。缺点是：需要部署和维护独立的代理中间件，成本高。各种方案的风险比较mysql优化方案那么多，怎么选？按照以下几行考虑：决定是使用代理还是客户端架构。中小型或者比较简单的场景倾向于选择客户端架构，复杂场景或者大型系统倾向于选择代理架构。具体功能是否满足，比如跨节点ORDERBY，那么一年内优先考虑不支持该功能。较新的产品表明发展停滞，甚至无人维护和技术支持。最好按照大公司->社区->小公司->个人的顺序选择口碑较好的制作者，比如githubstars，用户数量、质量和用户反馈优先开源。通常项目有特殊需求，可能需要更改源代码。根据以上思路，推荐以下方案：客户端架构：ShardingJDBC代理架构：MyCat或Atlas目前有一些兼容MySQL且可水平扩展的数据库开源数据库兼容MySQL协议，如：TiDBCubrid，但其工业化质量与MySQL相比还有较大差距，运维投入较大。如果想把原来的MySQL迁移到一个新的可以横向扩展的数据库，可以考虑一些云数据库：阿里云PetaData阿里云OceanBase腾讯云DCDBNoSQLShardingonMySQL是一场枷锁中的舞蹈。其实很多大表对MySQL等RDBMS的需求并不大，不需要ACID。这些表迁移到NoSQL，彻底解决横向扩展的问题，比如：日志、监控、统计数据。非结构化或弱结构化数据没有很强的事务需求，也没有太多的关联操作。资料参考：MysqlSomethingMysqlStrategyMySQL::MySQL5.6ReferenceManual
