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很快恢复正常。此后，大量数据清理全部放在凌晨执行，那个时候就没什么人使用系统了，所以查询也很少。

mysqlcheck 是 MySQL 自带的一个工具，作用就是保养 表，其实就是检查，分析，修复和优化了。下面来介绍 mysqlcheck 工具的简单使用，官方文档在这里

以下的例子都是基于 MySQL 5.6 版本运行状态下（mysqlcheck是个在线工具）, 不同的存储引擎对于这个命令的支持程度不同（指的是 check, repair, analyze, optimize)，下面内容偏于 *** 作，主要基于 innodb 引擎。

提示：OPTIMIZE 在大表时候可能会消耗很多时间，不清楚原理情况下请谨慎使用!!! innodb 一般不用 OPTIMIZE，请参见 Using MySQL OPTIMIZE tables? For InnoDB? Stop

检查特定的表

注意在shell中执行，不是在mysql的交互环境下

如果应用中提示某个表坏了，使用下面的命令来检查。

$ mysqlcheck -c newmandela order -uroot -pEnter password:

newmandela.order

OK

newmandela 是库名， order是表名，还需要输入用户名和密码

检查一个库中的所有表

$ mysqlcheck -c newmandela -uroot -p

Enter password:

newmandela.account OK

newmandela.alarm OK

newmandela.alarm_settings OK

newmandela.auth_group OK

newmandela.auth_group_permissions OK

newmandela.auth_permission OK...

索引就是为特定的mysql字段进行一些特定的算法排序，比如二叉树的算法和哈希算法，哈希算法是通过建立特征值，然后根据特征值来快速查找。

1.普通索引:(index)最基本的索引，没有任何限制  目的:加快数据的查询速度

2.唯一索引:(unique)  与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

3.主键索引(primary key) 它 是一种特殊的唯一索引，不允许有空值。

4.复合索引:index(a,b,c)  为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

5.全文索引:fulltext  仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时耗空间。

第一类是myisam存储引擎使用的叫做b-tree结构，

第二类是innodb存储引擎使用的叫做聚簇结构（也是一种 b-tree）。 如下图：

注意：

1.myisam不需要回行处理 

2.innodb不需要回行处理,直接可以获取数据,因为innodb的储存引擎是包含了数据和索引文件的,其主键索引包含了数据,(唯一索引及普通索是没有直接包含数据的)

1、索引列不能参与计算

​ 有索引列参与计算的查询条件对索引不友好（甚至无法使用索引），如from_unixtime(create_time) = '2014-05-29'。

​ 原因很简单，如何在节点中查找到对应key？如果线性扫描，则每次都需要重新计算，成本太高；如果二分查找，则需要针对from_unixtime方法确定大小关系。

因此，索引列不能参与计算。上述from_unixtime(create_time) = '2014-05-29'语句应该写成create_time = unix_timestamp('2014-05-29')。

2、最左前缀匹配

​ 如有索引(a, b, c, d)，查询条件a = 1 and b = 2 and c >3 and d = 4，则会在每个节点依次命中a、b、c，无法命中d。也就是最左前缀匹配原则。

3、冗余和重复索引

​ ​ 冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。比如有一个索引(A,B)，再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)

4、避免多个范围条件

        select user.* from user where login_time >'2017-04-01' and age between 18 and 30

​ 比如想查询某个时间段内登录过的用户：它有两个范围条件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但无法同时使用它们 .

5、覆盖索引 (能扩展就不新建)

​ 如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：

1.索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量2.索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

6、选择区分度高的列作索引

如，用性别作索引，那么索引仅能将1000w行数据划分为两部分（如500w男，500w女），索引几乎无效。

区分度的公式是count(distinct ) / count(*)，表示字段不重复的比例，比例越大区分度越好。唯一键的区分度是1，而一些状态、性别字段可能在大数据面前的区分度趋近于0。

7、删除长期未使用的索引

场景一(覆盖索引 5)

索引应该建在选择性高的字段上（键值唯一的记录数/总记录条数），选择性越高索引的效果越好、价值越大，唯一索引的选择性最高；

组合索引中字段的顺序，选择性越高的字段排在最前面；

where条件中包含两个选择性高的字段时，可以考虑分别创建索引，引擎会同时使用两个索引（在OR条件下，应该说必须分开建索引）；

不要重复创建彼此有包含关系的索引，如index1(a,b,c) 、index2(a,b)、index3(a)；

组合索引的字段不要过多，如果超过4个字段，一般需要考虑拆分成多个单列索引或更为简单的组合索引；

不要滥用索引。因为过多的索引不仅仅会增加物理存储的开销，对于插入、删除、更新 *** 作也会增加处理上的开销，而且会增加优化器在选择索引时的计算代价。

因此太多的索引与不充分、不正确的索引对性能都是毫无益处的。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。

---