生产环境中MySQL上亿大表，如何优化？

背景

XX 实例（一主一从）xxx 告警中每天凌晨在报 SLA 报警，该报警的意思是存在一定的主从延迟。（若在此时发生主从切换，需要长时间才可以完成切换，要追延迟来保证主从数据的一致性）。

XX 实例的慢查询数量最多（执行时间超过 1s 的 SQL 会被记录），XX 应用那方每天晚上在做删除一个月前数据的任务。

分析

使用 pt-query-digest 工具分析最近一周的 MySQL-slow.log：

结果第一部分：

最近一个星期内，总共记录的慢查询执行花费时间为 25403s，最大的慢 SQL 执行时间为 266s，平均每个慢 SQL 执行时间 5s，平均扫描的行数为 1766 万。面试宝典：https://www.yoodb.com

结果第二部分：

select arrival_record 操作记录的慢查询数量最多有 4 万多次，平均响应时间为 4s，delete arrival_record 记录了 6 次，平均响应时间 258s。

select xxx_record 语句

select arrival_record 慢查询语句都类似于如下所示，where 语句中的参数字段是一样的，传入的参数值不一样：

select arrival_record 语句在 MySQL 中最多扫描的行数为 5600 万、平均扫描的行数为 172 万，推断由于扫描的行数多导致的执行时间长。

查看执行计划：

用到了索引 IXFK_arrival_record，但预计扫描的行数很多有 3000 多万行：

①该表总记录数约 1 亿多条，表上只有一个复合索引，product_id 字段基数很小，选择性不好。

②传入的过滤条件：

没有 station_nu 字段，使用不到复合索引 IXFK_arrival_record 的 product_id，station_no，sequence，receive_time 这几个字段。JAVA新特性：https://www.yoodb.com/java/characteristic/java-8/java8-stream.html

③根据最左前缀原则，select arrival_record 只用到了复合索引 IXFK_arrival_record 的第一个字段 product_id，而该字段选择性很差，导致扫描的行数很多，执行时间长。

④receive_time 字段的基数大，选择性好，可对该字段单独建立索引，select arrival_record sql 就会使用到该索引。

现在已经知道了在慢查询中记录的 select arrival_record where 语句传入的参数字段有 product_id，receive_time，receive_spend_ms，还想知道对该表的访问有没有通过其他字段来过滤了

神器 tcpdump 出场的时候到了，使用 tcpdump 抓包一段时间对该表的 select 语句：

获取 select 语句中 from 后面的 where 条件语句：

select 该表 where 条件中有 product_id，station_no，sequence 字段，可以使用到复合索引 IXFK_arrival_record 的前三个字段。

综上所示，优化方法为：

• 删除复合索引 IXFK_arrival_record

• 建立复合索引 idx_sequence_station_no_product_id

• 建立单独索引 indx_receive_time

   

delete xxx_record 语句

该 delete 操作平均扫描行数为 1.1 亿行，平均执行时间是 262s。

delete 语句如下所示，每次记录的慢查询传入的参数值不一样：

执行计划：

该 delete 语句没有使用索引（没有合适的索引可用），走的全表扫描，导致执行时间长。

优化方法也是：建立单独索引 indx_receive_time(receive_time)。

测试

拷贝 arrival_record 表到测试实例上进行删除重新索引操作。

XX 实例 arrival_record 表信息：

磁盘占用空间 48G，MySQL 中该表大小为 31G，存在 17G 左右的碎片，大多由于删除操作造成的。（记录被删除了，空间没有回收)

备份还原该表到新的实例中，删除原来的复合索引，重新添加索引进行测试。

mydumper 并行压缩备份：

并行压缩备份所花时间（52s）和占用空间（1.2G，实际该表占用磁盘空间为 48G，mydumper 并行压缩备份压缩比相当高）：

拷贝 dump 数据到测试节点：

多线程导入数据：

逻辑导入该表后磁盘占用空间：

分别使用 online DDL 和 pt-osc 工具来做删除重建索引操作。Spring注解大全：https://www.yoodb.com/spring/spring-annotate.html

先删除外键，不删除外键，无法删除复合索引，外键列属于复合索引中第一列：

online DDL 花费时间为 34 分钟，pt-osc 花费时间为 57 分钟，使用 onlne DDL 时间约为 pt-osc 工具时间的一半。

做 DDL 参考：

实施

由于是一主一从实例，应用是连接的 vip，删除重建索引采用 online DDL 来做。公众 号Java精选，回复java面试，获取面试资料，支持在线刷题。

停止主从复制后，先在从实例上做（不记录 binlog），主从切换，再在新切换的从实例上做（不记录 binlog)：

执行时间：

删除重建索引花费时间为 28 分钟，添加外键约束时间为 48 分钟。

再次查看 delete 和 select 语句的执行计划：

explain select count(*) from arrival_record where receive_time < STR_TO_DATE('2019-03-10', '%Y-%m-%d')G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: arrival_recordpartitions: NULLtype: rangepossible_keys: idx_receive_timekey: idx_receive_timekey_len: 6ref: NULLrows: 7540948filtered: 100.00Extra: Using where; Using index

都使用到了 idx_receive_time 索引，扫描的行数大大降低。

索引优化后

delete 还是花费了 77s 时间：

delete 语句通过 receive_time 的索引删除 300 多万的记录花费 77s 时间。

delete 大表优化为小批量删除

应用端已优化成每次删除 10 分钟的数据（每次执行时间 1s 左右），xxx 中没在出现 SLA（主从延迟告警）：

另一个方法是通过主键的顺序每次删除 20000 条记录：

总结

表数据量太大时，除了关注访问该表的响应时间外，还要关注对该表的维护成本（如做 DDL 表更时间太长，delete 历史数据）。

对大表进行 DDL 操作时，要考虑表的实际情况（如对该表的并发表，是否有外键）来选择合适的 DDL 变更方式。

对大数据量表进行 delete，用小批量删除的方式，减少对主实例的压力和主从延迟。

作者：jiaxin_12 https://www.cnblogs.com/YangJiaXin/p/10828244.html