Mysql基础(十七)：SQL调优实战

目录

1、千万级用户场景下的运营系统SQL调优（1）

2、千万级用户场景下的运营系统SQL调优（2）

3、千万级用户场景下的运营系统SQL调优（3）

4、亿级数据量商品系统的SQL调优实战（1）

 5、亿级数据量商品系统的SQL调优实战（2）

6、亿级数据量商品系统的SQL调优实战（3）

7、数十亿数量级评论系统的SQL调优实战（1）

8、千万级数据删除导致的慢查询优化实践（1）

9、千万级数据删除导致的慢查询优化实践（2）

10、千万级数据删除导致的慢查询优化实践（3）

---

1、千万级用户场景下的运营系统SQL调优（1）

1、背景：

        这个互联网公司的用户量是比较大的，有百万级日活用户的一个量级。在这个互联网公司里，有一个系统是专门通过各种条件筛选出大量的用户，接着对那些用户去推送一些消息的，有的时候可能是一些促销活动的消息，有的时候可能是让你办会员卡的消息，有的时候可能是告诉你有一个特价商品的消息。总而言之，其实通过一些条件筛选出大量的用户，接着针对这些用户做一些推送，是互联网公司的运营系统里常见的一种功能，在这个过程中，比较坑爹，也比较耗时的，其实是筛选用户的这个过程。

        因为这种互联网公司，我们已经说过了，用户是日活百万级的，注册用户是千万级的，而且如果还没有进行分库分表的话，那么这个数据库里的用户表可能就一张，单表里是上千万的用户数据，大概是这么一个情况。

2、SQL

一个简化的SQL 经过：

select id, name from users where id in (select user_id from users_extent_info where latest_login_time < xxxxx)

就是说一般存储用户数据的表会分为两张表，

• 一个表用来存储用户的核心数据，比如id、name、昵称、手机号之类的信息，也就是上面SQL语句里的users表。
• 另外一个表可能会存储用户的一些拓展信息，比如说家庭住址、兴趣爱好、最近一次登录时间之类的，就是上面的users_extent_info表。

        SQL：有一个子查询，里面针对用户的拓展信息表，也就是users_extent_info查询了一下最近一次登录时间小于某个时间点的用户，这里其实可以是查询最近才登陆过的用户，也可以查询的是很长时间没登录过的用户 ，然后给他们发送一些push，无论哪种场景，

        这个SQL 都是适用的。 然后在外层的查询里，直接就是用了 id IN 字句去查询 id 在子查询结果范围里的 users 表的所有数据，此时这个 SQL往往一下子会查出来很多数据，可能几千、几万、几十万，都有可 能。

        所以一 般运行这类SQL之前，会先跑一个count聚合函数 ，看看有多少条：

SELECT COUNT(id) FROM users WHERE id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)

        然后 内存里做一个小批量多批次读取数据的操作 ，比如判断如果在1000 条以内，那么就一下子读取出来，如果超过1000 条，可以通过 LIMIT 语句，每次就从这个结果集里查 1000 条数据，查 1000 条就做一次批量PUSH ，再查下一波 1000 条。

3、问题

        就是在千万级数据量的大表场景下，上面的SQL 直接轻松跑出来耗时几十秒的速度，所以说， 这个SQL 不优化是绝对不行了！

2、千万级用户场景下的运营系统SQL调优（2）

        系统运行的时候，肯定会先跑一下COUNT聚合函数来查查这个结果集有多少数据，然后再

分批查询。结果就是这个 COUNT 聚合函数的 SQL ，在千万级大表的场景下，都要花几十秒才能跑出来，简直是大跌眼镜，这种性能，系统基本就没法跑了！

EXPLAIN select count(id) from users where id in(select user_id from users_extent_info where latest_login_time < xxxxx)

        下面的执行计划是当时我们为了调优，在测试环境的单表2万条数据场景下跑出来的执行计划，即使是5 万条数据，当时这个 SQL 都跑了十多秒，所以足够复现当时的生产问题了，所以大家注意下执行计划里的数据量问题。

        这条SQL语句的一个执行过程

        首先，针对子查询，是执行计划里的第三行实现的，他清晰的表明，针对users_extent_info ，使用了 idx_login_time这个索引，做了 range 类型的索引范围扫描，查出来了 4561 条数据，没有做其他的额外筛选，所以fitered 是 100% 。

        接着 MATERIALIZED ，表明了 这里把子查询的4561条数据代表的结果集进行了物化，物化成了一个临时表，这个临时表物化，一定是会把4561条数据临时落到磁盘文件里去 的，这个过程其实就挺慢的。

        然后第二条执行计划表明，接着就是针对users 表做了一个全表扫描，在全表扫描的时候扫出来了49651条数据，同时大家注意看 Extra 字段，显示了一个 Using join buffer 的信息，这个明确表示，此处居然在执行join 操作？

        接着看执行计划里的第一条，这里他是针对子查询产出的一个物化临时表做了一个全表查询，把里面的数据都扫描了一遍，那么为什么要对这个临时表进行全表扫描呢？

        原因就是在让users 表的每一条数据，都要去跟物化临时表里的数据进行 join ，所以针对 users 表里的每一条数据，只能是去全表扫描一遍物化临时表，找找物化临时表里哪条数据是跟他匹配的，才能筛选出来一条结果。

        第二条执行计划的全表扫描的结果表明是一共扫到了49651 条数据，但是全表扫描的过程中，因为去跟物化临时表执行了一个join 操作，而物化临时表里就 4561 条数据，所以最终第二条执行计划的 filtered显示的是10% ，也就是说，最终从 users 表里筛选出了也是 4000 多条数据。

总结：

 

        执行的过程就是先执行了子查询查出来4561 条数据，物化成了一个临时表，接着他对 users主表做了一个全表扫描，扫描的过程中把每一条数据都放到物化临时表里去做全表扫描，本质在

做 join 的事情。

3、千万级用户场景下的运营系统SQL调优（3）

为什么会跑的这么慢呢？

•  首先他对子查询的结果做了一次物化临时表，落地磁盘了。
• 接着全表扫描了users表的所有数据，每一条数据居然跑到一个没有索引的物化临时表里再做一次全表扫描找匹配数据。

在这个过程里，对 users表的全表扫描耗时不耗时？ 对 users 表的每一条数据跑 到物化临时表里做全表扫描，耗时不耗时？ 所以这个过程必然是非常慢的，几乎就没怎么用到索引。

为什么会出现上述的一个全表扫描users表，然后跟物化临时表做join，join的时候还要全表扫描物化临时 表的过程？

一个技巧：

在执行完上述 SQL 的 EXPLAIN 命令，看到执行计划之后，可以执行一下 show warnings 命令。

显示出来的内容如下：

/* select#1 */

select count( d2. users . user_id `) AS COUNT(users.user_id)` from d2 . users users semi join xxxxxx ，

        下面省略一大段内容，因为可读性实在不高，大家关注的应该是这里的semi join这个关键字

这里就显而易见了！

        MySQL 在这里，生成执行计划的时候， 自动就把一个普通的IN子句，“优化”成了基于semi join来进行IN+子查询的操 作，这个 semi join 是什么意思呢？

        semi join: 简单来说，对users 表不是全表扫描了么？对 users 表里每一条数据，去对物化临时表全表扫描做 semi join，不需要把 users 表里的数据真的跟物化临时表里的数据 join 上。 只要users表里的一条数据，在物化临时表里可以找到匹配的数据，那么users表里的数据就会返回 ，这就叫做 semi join ，他是用来筛选的。

        所以就慢在这里了，那既然知道了是semi join 和物化临时表导致的问题，应该如何优化呢？

         执行SET optimizer_switch='semijoin=off'，也就是关闭掉半连接优化 ，此时执行EXPLAIN 命令看一下此时的执行计划，发现此时会恢复为一个正常的状态。

        就是有一个SUBQUERY 的子查询，基于 range 方式去扫描索引搜索出 4561 条数据，接着有一个 PRIMARY类型的主查询，直接是基于 id 这个 PRIMARY 主键聚簇索引去执行的搜索，然后再把这个 SQL 语句真实跑一下看看， 发现性能一下子提升了几十倍，变成了100多毫秒！

        因此到此为止，这个SQL 的性能问题，其实反而是他自动执行的 semi join 半连接优化，给咱们导致了问题，一旦禁止掉semi join 自动优化，用正常的方式让他基于索引去执行，性能提升。

实际，在生产环境是不能随意更改这些设置的。

        多种办法尝试去修改SQL 语句的写法，在不影响他语义的情况下，尽可能的去改变SQL 语句的结构和格式，最终被我们尝试出了一个写法，如下所示：

SELECT COUNT(id)

FROM users

WHERE ( id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx) OR id IN

(SELECT user_id FROM users_extent_info WHERE latest_login_time < -1))

        在上述写法下，WHERE 语句的 OR 后面的第二个条件，根本是不可能成立的，因为没有数据的 latest_login_time是小于 -1 的，所以那是不会影响 SQL 语义的，但是我们发现改变了 SQL 的写法之后，执行计划也随之改变。他并没有再进行semi join 优化了，而是正常的用了子查询，主查询也是基于索引去执行的，这样们在 线上上线了这个SQL 语句，性能从几十秒一下子就变成几百毫秒了。

总结：

 

主要问题在于 MySQL 内部自动使用了半连接优化，结果半连接的时候导致大量无索引的全表扫描，引发了性能的急剧下降；

4、亿级数据量商品系统的SQL调优实战（1）

        MySQL数据库在选择索引时，选择了一个不太合适的索引，导致了性能极差引发了慢查询。

1、背景

        线上的商品系统出现的一个慢查询告警，这个报警的意思是数据库突然涌现出了大量的慢查询，而且因为大量的慢查询，导致每一个数据库连接执行一个慢查询都要耗费很久。那这样的话，必然会导致突然过来的很多查询需要让数据库开辟出来更多的连接，因此这个时候报警也告诉我们，数据库的连接突然也暴增了，而且每个连接都打满，每个连接都要执行一个慢查询，慢查询 还跑的特别慢。接着引发的问题，就是数据库的连接全部打满，没法开辟新的连接了，但是还持续的有新的查询发送过来，导致数据库没法处理新的查询，很多查询发到数据库直接就阻塞然后超时了，这也直接导致线上的商品系统频繁的报警，出现了大量的数据库查询超时报错的异常！

        因为这种情况，基本意味着你的商品数据库以及商品系统濒临于崩溃了，大量慢查询耗尽了数据库的连接资源，而且一直阻塞在数据库里执行，数据库没法执行新的查询，商品数据库没法执行查询，用户没法使用商品系统，也就没法查询和筛选电商网站里的商品了！

        当时正好是晚上晚高峰的时候！也就是一个电商网站比较繁忙的时候，虽说商品数据 是有多级缓存架构的，但是实际上在下单等过程中，还是会大量的请求商品系统的，所以晚高峰的时

候，商品系统本身 TPS 大致是在每秒几千的。 因此这个时候，发现数据库的监控里显示，每分钟的慢查询超过了10w+ ！也就是说商品系统大量 的查询都变成了慢查询！

满查询语句：

select * from products where category='xx' and sub_category='xx' order by id desc limit xx,xx

含义：用户在电商网站上根据商品的品类以及子类在进行筛选，然后按id倒序排序，最后是分页，

        语句执行的商品表里大致是1亿左右的数据量，这个量级已经稳定了很长时间了，主要也就是这么 多商品，但是上面的那个语句居然一执行就是几十秒！几十秒，这还得了？基本上数据库的连接全部被慢查询打满，一个连接要执行几十秒的SQL，然后才能执行下一个SQL，此时数据库基本就废了，没法执行什么查询了！ 难怪商品系统本身也大量的报警说查询数据库超时异常了！

 5、亿级数据量商品系统的SQL调优实战（2）

分析：

        一个索 引，KEY index_category(catetory,sub_category)肯定是存在的，所以基本可以确认上SQL绝对是可以用上索引的。因为如果你一旦用上了品类的那个索引，那么按品类和子类去在索引里筛选，其实第一，筛选很快速。第二，筛出来的数据是不多的，按说这个语句应该执行的速度是很快的，即使表有亿级数据，但是执行时间也最多不应该超过1s

执行计划：
        possible_keys里是有我们的index_category的，结果实际用的key不是这个索引，而是PRIMARY！而且Extra里清晰写了Using where。

        到此为止，这个SQL 语句性能差的原因找到了，他其实本质上就是在主键 的聚簇索引上进行扫描，一边扫描，一边还用了where条件里的两个字 段去进行筛选，所以这么扫描的话，那必然就是会耗费几十秒了！ 因此此时为了快速解决这个问题，就需要 强制性的改变MySQL自动选择这个不合适的聚簇索引进行扫描的行为

那么怎么改变呢？

就是使用 force index语法， 如下：

select * from products force index(index_category) where category='xx' and sub_category='xx'  order by id desc limit xx,xx

        使用上述语法过后，强制让SQL语句使用了你指定的索引，此时再次执行这个 SQL 语句，会发现他仅仅耗费100 多毫秒而已！性能瞬间就提升上来了！

面试： 如果MySQL使用了错误的执行计划，应该怎么办？

方法就是force index语法就可以了。

6、亿级数据量商品系统的SQL调优实战（3）

问题1:
为什么针对： select * from products where category='xx' and sub_category='xx' order by id desc limit xx,xx 这样一个SQL语句，MySQL要选择对聚簇索引进行扫描呢？

        因为这个表是一个亿级数据量的大表，那么对于他来说， index_category这个二级索引也是比较大的 。所以此时对于MySQL 来说，他有这么一个判断，他觉得如果要是从 index_category 二级索引里来查找到 符合where 条件的一波数据，接着还得回表，回到聚簇索引里去。

        因为SQL 语句是要 select * 的，所以这里必然涉及到一次回表操作，回到聚簇索引里去把所有字段的数据都查出来，但是在回表之前，他必然要做完order by id desc limit xx,xx 这个操作。

 举个例子：

        比如他根据where category='xx' and sub_category='xx'，从 index_category 二级索引里

查找出了一大波数据。 比如从二级索引里假设搂出来了 几万条数据 ，接着因为二级索引里是包含主键id 值的，所以 此时他就得按照order by id desc这个排序语法，对这几万条数据基于临时磁盘文件进行fifilesort磁盘 排序，排序完了之后，再按照 limit xx,xx语法，把指定位置的几条数据拿出来，假设就是limit 0,10，那么就是把10条 数据拿出来。

        拿出来10 条数据之后， 再回到聚簇索引里去根据id查找，把这10条数据的完整字段都查出 来，这就是MySQL认为如果你使用 index_category 的话，可能会发生的一个情况。

        所以他担心的是，你根据where category='xx' and sub_category='xx'，从 index_category 二级索引里 查出来的数据太多了，还得在临时磁盘里排序，可能性能会很差，因此MySQL 就把这种方式判定为一种不太好的方式。

        因此他才会选择换一种方式，也就是说， 直接扫描主键的聚簇索引，因为聚簇索引都是按照id值有序的，所以扫描的时候，直接按order by id desc这个倒序顺序扫描过去就可 以了，然后因为他知道你是limit 0,10的，也就知道你仅仅只要拿到 10 条数据就行了。

        所以他在按顺序扫描聚簇索引的时候，就会对每一条数据都采用Using where 的方式，跟 wherecategory='xx' and sub_category='xx'条件进行比对，符合条件的就直接放入结果集里去，最多就是放10条数据进去就可以返回了。

        此时 MySQL认为，按顺序扫描聚簇索引，拿到10条符合where条件的数据，应该速度是很快的，很可能比使用index_category二级索引那个方案更快 ，因此此时他就采用了扫描聚簇索引的这种方式！

问题2:

为什么会在某一天晚上突然的就大量报慢查询，耗时几十秒了呢？

        原因也很简单，其实就是因为之前的时候，where category='xx' and sub_category='xx'这个条件通常都是有返回值的，就是说根据条件里的取值，扫描聚簇索引的时候，通常都是很快就能找到符合条件的值以及返回的，所以之前其实性能也没什么问题。

        但是后来可能是商品系统里的运营人员，在商品管理的时候加了几种商品分类和子类，但是这几种分类和子类的组合其实没有对应的商品。也就是说，那一天晚上，很多用户使用这种分类和子类去筛选商品， where category='新分类' and sub_category='新子类'这个条件实际上是查不到任何数据的！

        所以说，底层在扫描聚簇索引的时候， 扫来扫去都扫不到符合where条件的结果，一下子就把聚簇索引全部扫了一遍，等于是上亿数据全表扫描了一遍，都没找到符合where category='新分类' and sub_category='新子类'这个条件的数据 。 也正是因为如此，才导致这个SQL 语句频繁的出现几十秒的慢查询，进而导致 MySQL 连接资源打满，商 品系统崩溃！

7、数十亿数量级评论系统的SQL调优实战（1）

针对一个商品几十万评论的深分页问题。

对评论表进行分页查询的SQL语句：

select * from comments where product_id ='xx' and is_good_comment='1' order by  id desc limit 100000,20

         比如用户选择了查看某个商品的评论，因此必须限定Product_id ，同时还选了只看

好评，所以 is_good_commit 也要限定一下。接着他要看第5001 页评论，那么此时 limit 的 offset 就会是 (5001 - 1) * 20 ，其中 20 就是每一页的数量，此时起始offset 就是 100000 ，所以 limit 后 100000,20

对这个评论表呢，最核心的索引就是一个，那就是 index_product_id ，所以对上述 SQL 语句，正常情况 下，肯定是会走这个索引的，也就是说，会通过index_product_id 索引，根据 product_id ='xx' 这个条件从表里先删选出来这个表里指定商品的评论数据。

        那么接下来第二步呢？当然是得按照 is_good_comment='1' 条件，筛选出这个商品评论数据里的所有好评了！但是问题来了，这个index_product_id 的索引数据里，并没有 is_good_commet 字段的值，所以此时进行回表。

        也就是说，对这个商品的每一条评论，都要进行一次回表操作，回到聚簇索引里，根据id 找到那条数据，取出来is_good_comment 字段的值，接着对 is_good_comment='1' 条件做一个比对，筛选符合条件的数据。那么假设这个商品的评论有几十万条，岂不是要做几十万次回表操作？虽然每次回表都是根据 id 在聚簇索引里快速查找的，但还是架不住你每条数据都回表啊！！！

        接着对于筛选完毕的所有符合WHERE product_id ='xx' and is_good_comment='1' 条件的数据，假设有十多万条吧，接着就是按照id 做一个倒序排序，此时还得基于临时磁盘文件进行倒序排序，又得耗时很久。

        排序完毕了，就得基于limit 100000,20 获取第 5001 页的 20 条数据，最后返回。

        这个过程，因为有几十万次回表查询，还有十多万条数据的磁盘文件排序，所以当时发现，这条SQL 语句基本要跑个1 秒 ~2 秒。

那么如何对他进行优化呢？

        采取如下方式改造分页查询语句：

SELECT * from comments a, (SELECT id FROM comments WHERE product_id ='xx' and is_good_comment='1' ORDER BY id desc LIMIT 100000,20) b WHERE a.id=b.id

        上面那个SQL语句的执行计划就会彻底改变他的执行方式，他通常会先执行括号里的子查询，子查询反而会使用PRIMARY 聚簇索引，按照聚簇索引的 id 值的倒序方向进行扫描，扫描过程中就把符合 WHEREproduct_id ='xx' and is_good_comment='1'条件的数据给筛选出来。

        比如这里就筛选出了十万多条的数据，并不需要把符合条件的数据都找到，因为limit 后跟的是

100000,20 ，理论上，只要有 100000+20 条符合条件的数据，而且是按照 id 有序的，此时就可以执行根据limit 100000,20 提取到 5001 页的这 20 条数据了。 接着你会看到执行计划里会针对这个子查询的结果集，一个临时表，进行全表扫描，拿到 20 条数据，接着对20 条数据遍历，每一条数据都按照 id 去聚簇索引里查找一下完整数据，就可以了。

        所以针对我们的这个场景，反而是优化成这种方式来执行分页，他会更加合适一些，他只有一个扫描聚簇索引筛选符合你分页所有数据的成本，你的分页深度越深，扫描数据越多，分页深度越浅，那扫描数据就越少，然后再做一页20 条数据的 20 次回表查询就可以了。

        当时我们做了这个分页优化之后，发现这个分页语句一下子执行时间降低到了几百毫秒了，此时就达到了我们优化的目的。

8、千万级数据删除导致的慢查询优化实践（1）

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

导致慢查询的原因：

• 1、SQL的问题。
• 2、MySQL生产服务器的问题。
• 3、Mysql的 profifilling工具去细致的分析SQL语句的执行过程和耗时。

        实际上个别特殊情况下，MySQL出现慢查询并不是SQL语句的问题，而是他自己生产服务器的负载太高了，导致SQL语句执行很慢。

        给大家举个例子，比如现在MySQL服务器的磁盘IO负载特别高，也就是每秒执行大量的高负载的随机IO，但是磁盘本身每秒能执行的随机IO是有限的。结果呢，就导致你正常的SQL语句去磁盘上执行的时候，如果要跑一些随机IO，你的磁盘太繁忙了，顾不上你了，导致你本来很快的一个SQL，要等很久才能执行完毕，这个时候就可能导致正常SQL语句也会变成慢查询！

        还有一个例子就是 网络，也许网络负载很高，就可能会导致你一个SQL语句 要发送到MySQL上去，光是等待获取一个跟MySQL的连接 ，都很难，要等很久， MySQL 自己网络负载太高了，带宽打满，带宽打满了之后，你一个SQL 也许执行很快，但是他查出来的数据返回给你，网络都送不出去，此时也会变成慢查询。

        另外一个关键的点就是 CPU负载，如果说CPU负载过高的话，也会导致CPU过于繁忙去执行别的任务了，没时间执行你这个SQ L 语句，此时也有可能会导致你的 SQL 语句出现问题的，所以这个大家得注意。

         排查一下当时MySQL服务器的负载，尤其看看磁盘、网络以及CPU的负载，是否正常。

        之前解决过一个典型的问题，就是当某个离线作业瞬间大批量把数据往MySQL里灌入的时候，他一瞬间服务器磁盘、网络以及CPU的负载会超高。此时你一个正常SQL执行下去，短时间内一定会慢查询的，针对类似的问题，优化手段更多的是控制你导致MySQL负载过高的那些行为，比如灌入大量数据，最好在凌晨低峰期灌入，别影响线上系统运行。

9、千万级数据删除导致的慢查询优化实践（2）

        一个SQL调优的利器了，也就是profiling工具，这个工具可以对SQL语句的执行耗时进行非常深入和细致的分析。

使这个工具的过程：

        首先要打开这个profiling ，使用 set profiling=1这个命令，接着MySQL就会自动记录查询语句的profiling信息了。此时如果执行show profiling命令，就会给你列出各种查询语句的profiling信息        这里很关键的一点，就是他会记录下来每个查询语句的query id，所以你要针对你需要分析的 query 找到对他的 query id ，我们当时就是针对慢查询的那个SQL 语句找到了 query id 。

        然后就可以针对单个查询语句，看一下他的profifiling 具体信息，使用 show profifile cpu, block io forquery xx，这里的 xx 是数字，此时就可以看到具体的 profifile 信息了。除了cpu 以及 block io 以外，你还可以指定去看这个 SQL 语句执行时候的其他各项负载和耗时。

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

        这里我们当时仔细检查了一下这个SQL 语句的 profifiling 信息，重点发现了一个问题，他的 Sending Data的耗时是最高的，几乎使用了1s的时间，占据了SQL执行耗时的99% 。

        因为其他环节耗时低是可以理解的，毕竟这种简单SQL 执行速度真的很快，基本就是 10ms 级别的，结果跑成了1s ，那肯定 Sending Data 就是罪魁祸首了！

这个Sending Data是在干什么呢？

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

可是为什么这个过程会这么慢呢？

profiling 确实是提供给我们更多的线索了，但是似乎还是没法解决掉问题。但是毕竟我们已经捕获到了第一个比较异常的点了，就是Sending Data 的耗时很高！

接着我们又用了一个命令： show engine innodb status，看一下innodb存储引擎的一些状态 ，此时

发现了一个奇怪的指标，就是 history list length 这个指标，他的值特别高，达到了上万这个级别。

        所以当你有大量事务执行的时候，就会构建这种undo多版本快照链条，此时history list length的值就 会很高。然后在事务提交之后，会有一个多版本快照链条的自动purge清理机制，只要有清理，那么这个值就会降低。

        一般来说，这个值是不应该过于高的，所以我们在这里注意到了第二个线索，history list length 值过 高！大量的undo 多版本链条数据没被清理！推测很可能就是有的事务长时间运行，所以他的多版本快照不能被purge 清理，进而导致了这个 history list length 的值过高！

第二个线索 Get ！ 基本可以肯定的一点是，经过两个线索的推测，在大量简单 SQL 语句变成慢查询的时候，SQL 是因为 Sending Data 环节异常耗时过高，同时此时出现了一些长事务长时间运行，大量的频繁更新数据，导致有大量的undo 多版本快照链条，还无法 purge 清理。

10、千万级数据删除导致的慢查询优化实践（3）

        发现有大量的更新语句在活跃，而且有那种长期活跃的超长事务一直在跑没有结束，结果一问系统负责人， 发现他在后台跑了一个定时任务，定时清理数据，结果清理的时候一下子清理了上千万 的数据。

这个清理是怎么做的呢？

他居然开了一个事务，然后在一 个事务里删除上千万数据，导致这个事务一直在运行。这种长事务的运行会导致一个问题，那就是你删除的时候仅仅只是对数据加了一个删除标记 ，

事实上并没有彻底删除掉。此时你如果跟长事务同时运行的其他事务里在查询，他在查询的时候是可能会把那上千万被标记为删除的数据都扫描一遍的。

这种长事务的运行会导致一个问题，那就是你删除的时候仅仅只是对数据加了一个删除标记，事实上并没有彻底删除掉。此时你如果跟长事务同时运行的其他事务里在查询，他在查询的时候是可能

会把那上千万被标记为删除的数据都扫描一遍的。

        这个问题的关键点就在于，那个删除千万级数据的事务是个长事务！

也就是说，当你启动新事务查询的时候，那个删除千万级数据的长事务一直在运行，是活跃的！所以大 家还记得我们之前讲解MVCC 的时候，提到的一个 Read View 的概念么？ MVCC 是如何实现的？不就是基于一个Read View 机制来实现的么？

        当你启动一个新事务查询的时候，会生成一个Read View ，里面包含了当前活跃事务的最大 id 、最小 id 和事务id 集合，然后他有一个判定规则，具体判定规则大家不记得可以回顾一下当时我们讲过的内容。总之就是，你的新事务查询的时候，会根据ReadView 去判断哪些数据是你可见的，以及你可见的数据版本是哪个版本，因为一个数据有一个版本链条，有的时候你可能可见的仅仅是这个数据的一个历史版本而已。

        所以正是因为这个长事务一直在运行还在删除大量的数据，而且这些数据仅仅是标记为删除，实际还没删除，所以此时你新开事务的查询是会读到所有被标记为删除的数据的，就会出现千万级的数据扫描，才会造成慢查询！

        针对这个问题，其实大家要知道的一点是，永远不要在业务高峰期去运行那种删除大量数据的语句，因为这可能导致一些正常的SQL 都变慢查询，因为那些 SQL 也许会不断扫描你标记为删除的大量数据，好不容易扫描到一批数据，结果发现是标记为删除的，于是继续扫描下去，导致了慢查询！

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