【MySQL】大表优化

当MySQL单表记录数太多时，各项操作性能会明显下降。

相关优化策略有很多，但肯定不是所有策略都适合某个特定案例。也许在技术层面上，很多策略都能提高性能，但我们也得考虑成本和收益（ROI）。

另，是否有性能提升，或者说从整体业务角度而言综合性能表现是否更符合需求，需要经过实战测试才能确定。

 

此文仅提供几种常见优化方案。对于某些遗留项目【其它方案】中提及的途径可能更合适 —— 优化SQL语句和索引，甚至设计表结构。

 

1. 限制数据范围

通过限定数据操作的范围可以有效减少数据量。
现实业务中需要一次获取大量数据记录的场景其实是比较少的。如，对于查找账单的需求，可以按月份查找，而非一次性获取所有账单。

 

2. 缓存数据

缓存一般适合数据改动频率较低的场景。如果改动频繁，就没有缓存的意义，还会为了维护缓存而增加过多开销。
在具体业务场景链路中哪些位置设置缓存也需要认真考量。持久层框架（如，MyBatis）、应用服务层、Web页面、浏览器客户端都可以做缓存。

 

注意：MySQL的查询缓存从5.6开始默认是关闭的，在5.7.20中被“deprecated”，在8.0中被移除。因为MySQL的查询缓存实现中用到了全局互斥锁，所以会降低性能（无法有效发挥多核服务器的优势）。

 

未认真考量缓存对业务的影响，特别是缓存失效时如何更新数据，是大多数新手会犯的典型错误。这种情况在新手写的应用服务层代码中出现频率较高。

建议：永远不要单人作战引入缓存机制。必须向产品经理等领导详细推演展示缓存机制的影响，确保相关干系人心中有素。认真听取老手的建议，切不可犯蠢。

在实际项目中，拍板做决定的人理解相关细节的影响是非常重要的。我见过一个案例中，产品组内掌握话语权的人集体“犯蠢/宕机”，未听取专家建议，在没有任何缓存失效应对方案的情况下使用缓存，结果在用户环境暴露重大缺陷，最后彻底弃用缓存。“将熊熊一窝”、“统帅无能，累死三军”说的就是这类情况。

 

3. 读写分离

写主库，读从库。

 

这种方案适用于 读操作 远多于 写操作的情况，而且需容忍读操作所得数据不是最新数据的情况。

 

《How to Setup MySQL (master-slave) Replication in CentOS 7》

《How to Set Up Master Slave Replication in MySQL》

《Replication and Binary Logging Options and Variables》

 

4. 表分区

表分区是一种特殊的 水平拆分 方式，也是一种特殊的 库内分表。

它可以 缓解 单一表数据量过大 的问题，

但它对于减轻数据库服务器压力的作用不大，所有请求还是在争用同一服务器的资源。

 

MySQL 的 InnoDB 分区表就是典型代表。其优缺点：

优点：

• 方便对数据分而治之

  • 可以通过删除分区来删除无用的数据。
  • 为新增数据新开分区可以加快执行效率。
  • 可以对分区单独优化、检查、修复、备份、恢复。

• 提高数据查询效率

  可通过查询条件排除不符合条件的分区，提高效率。

 

缺点：

• InnoDB 分区表不支持外键。
  分区表不能引用其它表中的列作为外键；
  其它表不能引用分区表中的列作为外键。
• 不支持全文索引（Fulltext Index）。
• 不支持空间类型数据（如，Point、Geometry）。
• 分区索引不支持子查询。

 

5. 垂直拆分

注：垂直拆分后单表数据量未变，依然很大（需要水平拆分）。

 

垂直分库

优点：可以用 不同数据库 支撑不同的业务，降低单个数据库的压力。

缺点：

• 上层业务需额外处理不同的数据源；
• 可能存在数据一致性维护问题。

 

垂直分表

优点：

• 表结构简化，易于维护；
• 单行数据量变小，一个数据块可存放更多行数据，可减少IO次数。

缺点：

• 需管理额外的主键冗余列；
• 事务处理复杂；
• 需要额外的表连接操作（join）。可在业务服务器join，减少数据库压力。

 

6. 水平拆分

优点：不存在单表数据量过大 和 请求高并发 的性能问题，提高了系统的负载能力 和 稳定性。

 

缺点：

• 数据分片的事务一致性难以解决。
• 跨节点 Join 的逻辑复杂，性能差。
• 数据多次扩展 的 难度 和 维护量 非常大。

 

7. 其它方案

出于各种原因，与上面那些方案相比，这些方案的普适性可能会更小一点。像“优化SQL语句” 和 “优化索引” 可能在遗留项目中更适用。

甚至 重新设计表结构 是最佳方案，既优化数据库读写性能，又可以重构上层过时的业务逻辑。

 

7.1 使用更紧凑的数据类型

更紧凑的数据类型可以减少对存储空间的需求，读写开销也会更小。如：

• 不要让单表包含太多字段。
• 用 TINYINT 等占用空间更小的类型替代 INT。对于无负数的情况，还可将其设置为 Unsigned。
• 【谨慎】用 ENUM 或 整型 替代字符串类型。
  因为 ENUM降低了可扩展性，整型降低了可读性。如，用整型存储IP就是牺牲了可读性。
• 【谨慎】使用 TIMESTAMP 替代 DATETIME。
  因为 TIMESTAMP 虽然可减少所需存储空间，但放弃了时区信息（被统一为UTC），且存取数据需要额外的时区转换操作。

我遇到过一个项目，用UUID作为主键。

UUID的正常显示格式是带有连接号（-）的。如，“1ccaf0bc-46fd-11e9-b210-d663bd873d93”。

业务不需要显示连接号，所以生成UUID时会去除连接号。

存储时将通过MySQL的 unhex() 方法 将其转换为二进制；读取时通过 hex() 方法转换为16进制数字串；字段类型为 binary(16)，比 char(32) 更节省空间。

 

7.2 优化索引

索引的优化也是非常需要大规模多场景实战性能测试的。很多时候会展现出令人沮丧的结果——还不如“优化”前快。

• 索引（尽可能）恰到好处。

  可根据 Where 和 Order By 语句中涉及的字段建立索引。用 Explain 命令查看查询语句用了索引还是全表扫描。
  重复值过多的字段也不适合建索引。
  索引过多，消耗的存储空间越多，维护开销也多，MySQL在判断使用哪个索引时也更耗时（每次查询只能用一个索引）。

• 多列索引的使用需注意查询条件是否能启用该索引。多列索引通常由于拆分的单列索引。如果所查字段都在多列索引内，可以减少读取操作。
• 【谨慎】用自增字段作为主键。通常应选取具有实际业务意义的字段作为主键。
• 【谨慎】不使用 外键、UNIQUE 等约束，由上层业务程序提供保障。

 

7.3 优化SQL语句

优化SQL语句是个大话题，内容繁杂。SQL语句的小技巧很多。如，使用 in 而不是 or；对连续值使用 between 而不是 in。

此外还需要非常了解业务。新工程中SQL语句不合适的情况较少。而对于遗留的数据库，特别是原本业务就复杂的情况，很可能会因表结构设计不合理，导致SQL语句逐渐演变性能越来越差。

 

关于慢查询分析。
慢查询日志（slow_query_log）是最常被提及的方式。但是这种方式非常繁琐。前期准备工作麻烦。为了运行分析工具 mysqldumpslow 还要装 perl。各种变量的设置容易出错，打断工作流程。各项步骤对人很不友好。
通过MySQL Workbench 使用 Performance Schema 方便多了。

 

7.4 使用不同类型的数据库

某些场景确实无法发挥出 MySQL InnoDB 的优势。

对于读密集的场景 MyISAM 因可能更有性能优势。

有些数据也不适合用关系型数据库存储。可以考虑一些NoSQL数据库。

如，日志、监控数据、非结构化 或 弱结构化的数据、对事务和关联操作支持要求较低的数据 等。

 

7.5 升级硬件

这个方案往往会被认为没有技术含量。但很多时候却真的是很值得考虑的方案。
通过收集服务器性能指标，结合业务进行分析，得出恰当的硬件升级方案也不是件容易的事。