MySQL删除千万级数据量导致的慢查询优化

有人删了千万级的数据，结果导致频繁的慢查询。

线上收到大量慢查询告警，于是检查慢查询的SQL，发现不是啥复杂SQL，这些SQL主要针对一个表，基本都是单行查询，看起来应该不会有慢查询。这种SQL基本上都是直接根据索引查找出来的，性能应该极高。

是否可能慢查询不是SQL问题，而是MySQL生产服务器的问题？特殊情况下，MySQL出现慢查询还真不是SQL问题，而是他自己生产服务器的负载太高，导致SQL语句执行慢。比如现在MySQL服务器的

磁盘I/O负载高，每秒执行大量高负载的随机I/O，但磁盘本身每秒能执行的随机I/O有限，导致正常SQL在磁盘执行时，若跑一些随机IO，你的磁盘太忙，顾不上你了，导致你本来很快的一个SQL，要等很久才能执行完毕，这时就可能导致正常SQL也变成慢查询。

也许网络负载高，导致你一个SQL语句要发到MySQL，光是等待获取一个和MySQL的连接，都很难，要等很久或MySQL自己网络负载太高，带宽打满，带宽打满后，你一个SQL也许执行很快，但其查出来的数据返回给你，网络都送不出去，也会变成慢查询。

若CPU负载过高，也会导致CPU过于繁忙去执行别的任务，没时间执行你的SQL。

所以慢查询不一定是SQL本身导致，若觉得SQL不应该会慢查询，结果他那个时间段跑这个SQL 就是慢，应排查当时MySQL服务器的负载，尤其看看磁盘、网络及 CPU 的负载，是否正常。

当某个离线作业瞬间大批量把数据往MySQL里灌入的时，他一瞬间服务器磁盘、网络以及CPU的负载会超高。

此时你一个正常SQL执行下去，短时间内一定会慢查询，类似问题，优化手段更多是控制你导致MySQL负载过高的那些行为，比如灌入大量数据，最好在业务低峰期灌入，别影响高峰期的线上系统运行。

但看了下MySQL服务器的磁盘、网络以及CPU负载，一切正常，似乎也不是这问题导致。看起来无解了？

慢 SQL 的头两步排查手段：

这两种办法都不奏效之后，第三步：用MySQL profilling工具去细致的分析SQL语句的执行过程和耗时。

这个工具可以对SQL语句的执行耗时进行非常深入和细致的分析

打开profiling，使用

接着MySQL就会自动记录查询语句的profiling信息。此时若执行show profiles，就会给你列出各种查询语句的profiling信息，会记录下来每个查询语句的query id，所以你要针对你需要分析的query找到对他的query id，我们当时就是针对慢查询的那个SQL语句找到了query id。

然后针对单个查询语句，看其profiling信息，使用show profile cpu, block io for query xx，这里的xx是数字，此时就可以看到具体的profile信息。

除了cpu以及block io以外，还能指定去看这个SQL语句执行时候的其他各项负载和耗时。

会给你展示出来SQL语句执行时候的各种耗时，比如磁盘IO的耗时，CPU等待耗时，发送数据耗时，拷贝数据到临时表的耗时等，SQL执行过程中的各种耗时都会展示。

检查该SQL语句的profiling信息后，发现问题，其Sending Data耗时最高，几乎使用1s，占据SQL执行耗时的99%！其他环节耗时低可以理解，毕竟这种简单SQL执行速度真的很快，基本就是10ms级别，结果跑成1s，那肯定Sending Data就是问题根源！

这Sending Data在干啥呢？

MySQL官方释义：为一个SELECT语句读取和处理数据行，同时发送数据给客户端的过程，简单来说就是为你的SELECT语句把数据读出来，同时发送给客户端。

但这过程为啥这么慢？profiling确实是提供给我们更多的线索了，但似乎还是没法解决问题。但已经捕获到异常关键点，就是Sending Data的耗时很高！

接着：

看innodb存储引擎的一些状态，此时发现一个奇怪的指标：history list length，值特别高，达到上万。

MVCC就是多个事务在对同一个数据， 有人写，有人读，此时可以有多种隔离级别，对一个数据有个多版本快照链条，才能实现MVCC和各种隔离级别。

所以当你有大量事务执行时，就会构建这种undo多版本快照链条，此时history list length就会很高。然后在事务提交后，会有一个多版本快照链条的自动purge清理机制，清理了，该值就会降低。一般该值不应过高，所以注意到第二个线索：history list length过高，即大量的undo多版本链条数据没有清理。推测可能有的事务长时间运行，所以其多版本快照不能被purge清理，进而导致history list length过高。

经过这俩线索推测，在大量简单SQL变成慢查询时，SQL因为Sending Data环节异常，耗时过高；同时此时出现一些长事务长时间运行，大量的频繁更新数据，导致有大量undo多版本快照链条，还无法purge清理。

因为发现有大量的更新语句在活跃，而且有那种长期活跃的长事务一直在跑而没有结束，问了下系统负责人，在后台跑了个定时任务：他居然开了一个事务，然后在一个事务里删除上千万数据，导致该事务一直在运行。

这种长事务的运行会导致你删除时，仅只是对数据加了一个删除标记，事实上并没有彻底删除。此时你若和长事务同时运行的其它事务里再查询，他在查询时可能会把那上千万被标记为删除的数据都扫描一遍。因为每次扫描到一批数据，都发现标记为删除了，接着就会再继续往下扫描，所以才导致一些查询语句很慢。

那为何你启动一个事务，在事务里查询，凭什么就要去扫描之前那个长事务标记为删除状态的上千万的垃圾数据？讲道理，那些数据都被删了，跟你没关系了呀，你可以不去扫描他们 嘛！

而问题症结在于，那个 删除千万级数据的事务是个长事务 ！即当你启动新事务查询时，那个删除千万级数据的长事务一直在运行，它是活跃的！结合MVCC的Read View机制，当你启动一个新事务查询时，会生成一个Read View。你的新事务查询时，会根据ReadView去判断哪些数据可见及可见的数据版本号，因为每个数据都有个版本链条，有时你能可见的仅是这个数据的一个 历史 版本。

所以正是因为该长事务一直在运行，还在删除大量数据，而且这些数据仅是逻辑删除，所以此时你新开事务的查询还是会读到所有逻辑删除数据，也就会出现千万级的数据扫描，导致了慢查询！

所以禁止在业务高峰期运行那种删除大量数据的语句，因为这可能导致一些正常的SQL都变慢查询，因为那些SQL也许会不断扫描你标记为删除的大量数据，好不容易扫描到一批数据，结果发现是标记为删除的，于是继续扫描下去，导致慢查询！

直接kill那个正在删除千万级数据的长事务，所有SQL很快恢复正常。此后，大量数据清理全部放在凌晨执行，那个时候就没什么人使用系统了，所以查询也很少。

MySQL 在崩溃恢复时，会遍历打开所有 ibd 文件的 header page 验证数据字典的准确性，如果 MySQL 中包含了大量表，这个校验过程就会比较耗时。 MySQL 下崩溃恢复确实和表数量有关，表总数越大，崩溃恢复时间越长。另外磁盘 IOPS 也会影响崩溃恢复时间，像这里开发库的 HDD IOPS 较低，因此面对大量的表空间，校验速度就非常缓慢。另外一个发现，MySQL 8 下正常启用时居然也会进行表空间校验，而故障恢复时则会额外再进行一次表空间校验，等于校验了 2 遍。不过 MySQL 8.0 里多了一个特性，即表数量超过 5W 时，会启用多线程扫描，加快表空间校验过程。

如何跳过校验MySQL 5.7 下有方法可以跳过崩溃恢复时的表空间校验过程嘛？查阅了资料，方法主要有两种：

1. 配置 innodb_force_recovery可以使 srv_force_recovery != 0 ，那么 validate = false，即可以跳过表空间校验。实际测试的时候设置 innodb_force_recovery =1，也就是强制恢复跳过坏页，就可以跳过校验，然后重启就是正常启动了。通过这种临时方式可以避免崩溃恢复后非常耗时的表空间校验过程，快速启动 MySQL，个人目前暂时未发现有什么隐患。2. 使用共享表空间替代独立表空间这样就不需要打开 N 个 ibd 文件了，只需要打开一个 ibdata 文件即可，大大节省了校验时间。自从听了姜老师讲过使用共享表空间替代独立表空间解决 drop 大表时性能抖动的原理后，感觉共享表空间在很多业务环境下，反而更有优势。

临时冒出另外一种解决想法，即用 GDB 调试崩溃恢复，通过临时修改 validate 变量值让 MySQL 跳过表空间验证过程，然后让 MySQL 正常关闭，重新启动就可以正常启动了。但是实际测试发现，如果以 debug 模式运行，确实可以临时修改 validate 变量，跳过表空间验证过程，但是 debug 模式下代码运行效率大打折扣，反而耗时更长。而以非 debug 模式运行，则无法修改 validate 变量，想法破灭。

mysql的优化大的有两方面：

1、配置优化

配置的优化其实包含两个方面的： *** 作系统内核的优化和mysql配置文件的优化

1)系统内核的优化对专用的mysql服务器来说，无非是内存实用、连接数、超时处理、TCP处理等方面的优化，根据自己的硬件配置来进行优化，这里不多讲；

2)mysql配置的优化，一般来说包含：IO处理的常用参数、最大连接数设置、缓存使用参数的设置、慢日志的参数的设置、innodb相关参数的设置等，如果有主从关系在设置主从同步的相关参数即可，网上的相关配置文件很多，大同小异，常用的设置大多修改这些差不多就够用了。

2、sql语句的优化

1）  尽量稍作计算

Mysql的作用是用来存取数据的，不是做计算的，做计算的话可以用其他方法去实现，mysql做计算是很耗资源的。

2）尽量少 join

MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈

3）尽量少排序

排序 *** 作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL的响应时间。

对于MySQL来说，减少排序有多种办法，比如：

通过利用索引来排序的方式进行优化

减少参与排序的记录条数

非必要不对数据进行排序

4）尽量避免 select *

在数据量少并且访问量不大的情况下，select * 没有什么影响，但是量级达到一定级别的时候，在执行效率和IO资源的使用上，还是有很大关系的，用什么字段取什么字段，减少不必要的资源浪费。

5）尽量用 join 代替子查询

虽然 Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。

---