MySQL 不同数据量的优化方案

MySQL 数据量达到百万级时，数据库的性能问题会逐渐显露出来，故本文就不同阶段不同数据量级的情况聊聊对应的优化方案。

百万级数据量

这个数据量基本上大家都经历过，也能感知一些性能问题显露出来了，这个阶段的优化几乎是最重要的，因为到后期千万级，甚至亿级别的阶段，数据库几乎无法动弹，可调整性很低。下面主要以字段，索引，sql 编写三方面优化：

字段优化

• 表字段 not null，因为 null 值很难查询优化且占用额外的索引空间，推荐默认数字 0。
• 数据状态类型的字段，比如 status, type 等等，尽量不要定义负数，如 -1。因为这样可以加上 UNSIGNED，数值容量就会扩大一倍。
• 可以的话用 TINYINT、SMALLINT 等代替 INT，尽量不使用 BIGINT，因为占的空间更小。
• 字符串类型的字段会比数字类型占的空间更大，所以尽量用整型代替字符串，很多场景是可以通过编码逻辑来实现用整型代替的。
• 字符串类型长度不要随意设置，保证满足业务的前提下尽量小。
• 用整型来存 IP。
• 单表不要有太多字段，建议在20以内。
• 为能预见的字段提前预留，因为数据量越大，修改数据结构越耗时。
• ...

索引设计

索引，空间换时间的优化策略，基本上根据业务需求设计好索引，足以应付百万级的数据量，养成使用 explain 的习惯，关于 explain 也可以访问： explain 让你的 sql 写的更踏实 了解更多。

• 一个常识：索引并不是越多越好，索引是会降低数据写入性能的。
• 索引字段长度尽量短，这样能够节省大量索引空间；
• 取消外键，可交由程序来约束，性能更好。
• 复合索引的匹配最左列规则，索引的顺序和查询条件保持一致，尽量去除没必要的单列索引。
• 值分布较少的字段（不重复的较少）不适合建索引，比如像性别这种只有两三个值的情况字段建立索引意义不大。
• 需要排序的字段建议加上索引，因为索引是会排序的，能提高查询性能。
• 字符串字段使用前缀索引，不使用全字段索引，可大幅减小索引空间。
• ...

编写习惯

• 查询不使用 select *，尽量查询带索引的字段，避免回表。
• 尽量使用 limit 对查询数量进行限制。
• 查询字段尽量落在索引上，尤其是复合索引，顺序很重要。
• 拆分大的 delete / insert 操作，一方面会锁表，影响其他业务操作，还有一方面是 MySQL 对 sql 长度也是有限制的。
• 不建议使用 MySQL 的函数，计算等，可先由程序处理，从上面提的一些点会发现，能交由程序处理的尽量不要把压力转至数据库上。
• 查询 count，性能：count(1) = count(*) > count(主键) > count(其他字段)。
• 查询操作符能用 between 则不用 in，能用 in 则不用 or。
• 避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符，因为这些查询无法使用索引。
• sql 尽量简单，少用 join，不建议两个 join 以上。
• ...

注：上述优化涉及到表结构修改，这个时候一定要小心，最好先做实验，一些耗时的操作中断也要好久。

千万级数据量

到了这个阶段的数据量，数据本身已经有很大的价值了，数据除了满足常规业务需求外，还会有一些数据分析的需求。而这个时候数据可变动性不高，基本上不会考虑修改原有结构，一般会考虑从分区，分表，分库三方面做优化：

分区

分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分，是一种水平划分。对应用来说是完全透明的，不影响应用的业务逻辑，即不用修改代码。因此能存更多的数据，查询，删除也支持按分区来操作，从而达到优化的目的。如果有考虑分区，可以提前做准备，避免下列一些限制：

• 一个表最多只能有1024个分区（mysql5.6之后支持8192个分区）。
• 如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引。
• 分区表中无法使用外键约束。
• NULL值会使分区过滤无效。
• 目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引。
• 所有分区必须使用相同的存储引擎。

分区可按以下四种类型分区：

• RANGE表分区：范围表分区，按照一定的范围值来确定每个分区包含的数据;
• LIST表分区：列表表分区，按照一个一个确定的值来确定每个分区包含的数据;
• HASH表分区：哈希表分区，按照一个自定义的函数返回值来确定每个分区包含的数据;
• KEY表分区 ：key表分区，与哈希表分区类似，只是用MySQL自己的HASH函数来确定每个分区包含的数据。

分表

分表分水平分表和垂直分表。

水平分表即拆分成数据结构相同的各个小表，如拆分成 table 1, table 2...，从而缓解数据库读写压力。

垂直分表即将一些字段分出去形成一个新表，各个表数据结构不相同，可以优化高并发下锁表的情况。

可想而知，分表的话，程序的逻辑是需要做修改的，所以，一般是在项目初期时，预见到大数据量的情况，才会考虑分表。后期阶段不建议分表，成本很大。

分库

分库一般是主从模式，一个数据库服务器主节点复制到一个或多个从节点多个数据库，主库负责写操作，从库负责读操作，从而达到主从分离，高可用，数据备份等优化目的。

当然，主从模式也会有一些缺陷，主从同步延迟，binlog 文件太大导致的问题等等，这里不细讲（笔者也学不动了）。

亿级数据量

提前说明，这个数据量笔者没实践过，只能根据看到的听到的分享下自己的看法:blush:。

理论上，MySQL 绝对是可以支撑这个级别数据的，但是现在大部分公司数据库性能水平完全取决于开发人员能力，当然一些公司有 DBA 把关，如没有上述的优化习惯，大多情况到这个阶段，MySQL 数据库性能已经接近崩溃临界点，甚至有些都到不了这个阶段。

以下列了几点建议，可以做参考：

• 运维方面做优化，MySQL 参数调优，增强硬件设备。
• 分析一个业务需求时不仅仅只考虑数据库方面，应该全方面的考量，考虑缓存，日志，延迟读写等方面来分担数据库压力。
• 不排除采用类如 HBase 这种大数据处理方案和云解决方案，尽管这个阶段还能勉强支撑，但如果数据库已经到崩溃边缘，风险也已经很大了。
• ...

总结

本文分别从三个阶段聊了优化方案，百万级数据量阶段主要列了一些优化习惯，绝对不止这些，若有疑问或补充的都可以联系笔者：[email protected]。千万级数据量阶段主要提供了优化方向，具体优化要结合自身的业务特性，没有深入，点到为止。亿级数据量，主要分享了自身的一些想法，仅限参考。强调的一点就是：数据本身以及衍生挖掘的价值也足够大。

参考

• 《高性能 MySQL》
• https://www.jianshu.com/p/7b01f4ee0d6d
• https://zhuanlan.zhihu.com/p/50597960
• https://blog.csdn.net/weixin_43981319/article/details/88359837
• https://blog.csdn.net/u011504963/article/details/79727849
• https://www.cnblogs.com/wangning528/p/6388538.html