MySQL原理简介—11.优化案例介绍

大纲

1.禁止或改写SQL避免自动半连接优化

2.指定索引避免按聚簇索引全表扫描大表

3.按聚簇索引扫描小表减少回表次数

4.避免产生长事务长时间执行

1.禁止或改写SQL避免自动半连接优化

(1)业务场景介绍

(2)SQL性能问题分析

(3)SQL性能调优

(1)业务场景介绍

某互联网公司的用户量比较大，有百万级日活用户的一个量级。该公司的运营系统会专门通过各种条件筛选出大量用户发送推送消息，比如一些促销活动的消息、办会员卡的消息、特价商品的消息。在这个过程中，比较耗时的是筛选用户的过程。

因为该公司当时的用户情况是：日活百万级、注册用户是千万级，而且还没有进行分库分表，其数据库里的用户表可能就一张，单表里是上千万的用户数据。

现在对运营系统筛选用户的SQL做一个简化，这个SQL经过简化看起来可能是这样的：

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

一般存储用户数据的表会分为两张表：users表用来存储用户的核心数据，如id、name、昵称、手机号等信息。users_extent_info表则存储用户的一些拓展信息，如最近登录时间等。

所以上面的SQL语句的意思就是：首先有个子查询向用户拓展信息表查询最近登录时间小于某时间的用户，然后在外层查询用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条。

这就是这个案例的一个完整的业务背景，那么它会产生的问题是：在千万级数据量的大表场景下，执行上面查询数量的SQL需耗时几十秒，所以这个SQL急需优化。

(2)SQL性能问题分析

通过如下语句可得这个复杂SQL的执行计划：

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

为了方便查看，下面对执行计划进行简化，只保留最关键的字段。另外，下面执行计划是在测试环境的单表5万条数据场景下跑出来的。即使是几万条数据，这个SQL都跑了十多秒，所以足够复现生产问题。

+----+-------------+-------------------+-------+----------------+------+----------+-------+
| id | select_type | table             | type  | key            | rows | filtered | Extra |
+----+-------------+-------------------+-------+----------------+-----------------+-------+
| 1  | SIMPLE      | <subquery2>       | ALL   | NULL           | NULL | 100.00   | NULL  |
| 1  | SIMPLE      | users             | ALL   | NULL           | 49651| 10.00    | Using where; Using join buffer(Block Nested Loop) |
| 2  | MATERIALIZED| users_extent_info | range | idx_login_time | 4561 | 100.00   | NULL  |
+----+-------------+-------------------+-------+----------------+------+----------+-------+

从上面的执行计划由下往上看，可清晰看到这个SQL语句的执行过程。首先第一条执行计划是在第三行，针对的是子查询的执行计划。表示对users_extent_info使用idx_login_time索引做了range类型的查询，查出来4561条数据，没有做其他的额外筛选，所以filtered是100%。MATERIALIZED表明把子查询的结果集进行物化，物化成一个临时表。这个临时表物化，会把4561条数据临时存放到磁盘文件里，该过程较慢。

然后第二条执行计划的ALL类型表明，会针对users表进行全表扫描。会扫出来49651条数据，其中Extra显示了一个Using join buffer的信息。这个Using join buffer明确表示，此处在执行join操作。

最后第三条针对子查询的物化临时表，也就是做全表查询。把里面的数据都扫描一遍，那么为什么要对这个临时表进行全表扫描呢？原因就是让users表的每一条数据，跟物化临时表里的数据进行join。所以针对users表里的每一条数据，会去全表扫描一遍物化临时表，查找物化临时表里哪条数据是跟它匹配的，从而筛选出一条结果。

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

以上的执行计划，不同MySQL版本可能不一样，甚至差别很大。但是对这个SQL语句的执行计划过程的分析过程基本是一样的。

(3)SQL性能调优

一.总结上述SQL的执行过程

EXPLAIN SELECT COUNT(id) FROM users WHERE id IN 
(SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)
+----+-------------+-------------------+-------+----------------+------+----------+-------+
| id | select_type | table             | type  | key            | rows | filtered | Extra |
+----+-------------+-------------------+-------+----------------+-----------------+-------+
| 1  | SIMPLE      | <subquery2>       | ALL   | NULL           | NULL | 100.00   | NULL  |
| 1  | SIMPLE      | users             | ALL   | NULL           | 49651| 10.00    | Using where; Using join buffer(Block Nested Loop) |
| 2  | MATERIALIZED| users_extent_info | range | idx_login_time | 4561 | 100.00   | NULL  |
+----+-------------+-------------------+-------+----------------+------+----------+-------+

首先执行子查询查出4561条数据，然后将这些数据物化成一个临时表。接着对users主表进行全表扫描，本质就是和物化临时表进行join操作。全表扫描的过程会把users表的每条数据都放到物化临时表里全表匹配。

二.上述SQL为什么会这么慢

根据explain出来的执行过程，对users表的全表扫描很耗时。对users表的每一条数据再到物化临时表里做全表扫描，也很耗时。所以整个过程必然是非常慢的，几乎没怎么用到索引。

三.semi join是什么意思

那么为什么会出现上述的这么一个过程：即首先全表扫描users表，然后再和物化临时表进行join，接着join的时候还全表扫描物化临时表。

这里有一个技巧，就是在执行完SQL的explain命令，看到执行计划后，可以执行一下show warnings命令。这个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的意思就是：对users表里每一条数据，去物化临时表进行全表扫描做semi join。此时不需要把users表里的数据真的跟物化临时表里的数据join上，而是只要users表里的一条数据在物化临时表里找到匹配的数据就返回。这就叫做semi join，它是用来筛选的，所以慢就慢在这里了。

四.如何优化semi join

那既然知道了是semi join和物化临时表导致的问题，那应该如何优化？

先执行SET optimizer_switch='semijoin=off'，即关闭掉半连接优化。再执行EXPLAIN命令看一下执行计划，发现此时会恢复为正常的状态。有个SUBQUERY子查询，基于range方式扫描索引查询出4561条数据。有一个PRIMARY主查询，基于id这个PRIMARY聚簇索引去执行的搜索。最后再重新执行该SQL，发现性能一下提升了几十倍，变成100多毫秒。

因此到此为止，这个SQL的性能问题，就是MySQL自动执行的semi join半连接优化导致的。一旦禁止semi join自动优化，让MySQL基于索引执行，性能是可以的。

当然生产环境是不能更改这些设置的，所以要尝试修改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语句后，性能从几十秒一下子就变成几百毫秒了。

2.指定索引避免按聚簇索引全表扫描大表

(1)业务场景引入

(2)SQL性能问题分析

(3)SQL性能调优分析

(4)案例总结

(1)业务场景引入

前面案例的主要问题在于MySQL内部自动使用了半连接优化，结果半连接时导致大量无索引的全表扫描，引发了性能的急剧下降。接下来的案例也类似，MySQL在选择索引时选了一个不太合适的索引。

先从当时线上的商品系统出现的一个慢查询告警开始讲起，某天晚上突然收到线上数据库的频繁报警。这个报警的意思是，数据库突然涌现出了大量慢查询。

因为大量的慢查询，导致每个数据库连接执行一个慢查询都要耗费很久。这样也必然导致突然过来的很多查询需要让数据库开辟出更多的连接。因此这时也发出了报警，显示数据库的连接突然也暴增了。于是连接池被打满，每个连接都要执行一个慢查询，慢查询还特别慢。

接着引发的问题，就是数据库的连接池全部被打满，没法开辟新的连接。但是还持续有新的查询发送过来，导致数据库没法处理新的查询。很多查询发到数据库直接阻塞然后超时，导致线上商品系统频繁报警，出现了大量数据库查询超时报错的异常。

这种情况基本意味着商品数据库以及商品系统濒临崩溃，大量慢查询耗尽了数据库的连接资源，最终导致数据库没法执行新查询。商品数据库没法执行查询，用户就没法正常使用商品系统。虽说商品数据会有多级缓存，但在下单等过程，还是会大量请求MySQL。也就是晚高峰时，商品系统本身TPS大致是在每秒几千；因此这时数据库的监控里显示，每分钟的慢查询超过了10w+，也就是商品系统大量的查询都变成了慢查询。

那么慢查询的都是一些什么语句呢？其实主要就是下面这条语句，这里做了一个简化：

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

这是一个很平常的SQL语句，就是根据商品的品类以及子类来浏览商品。这个语句执行的商品表里大致是1亿左右的数据量，这个量级已经稳定了很长时间，主要也就是这么多商品。但上面的语句居然一执行就需要几十秒，基本上数据库的连接池会全部被慢查询打满。一个连接要执行几十秒的SQL，然后才能执行下一个SQL，此时数据库基本就废了，没法执行什么查询了，所以商品系统大量进行报警说查询数据库超时异常了。

(2)SQL性能问题分析

上面那条让用户根据品类筛选商品的SQL语句，在一个亿级数据量的商品表里执行，需要耗时几十秒，结果导致数据库的连接池全部打满，商品系统无法运行，处于崩溃状态。

下面来分析一下，到底为什么会出现这样的一个情况。这个表当时肯定是对经常用到的查询字段都建立好了索引的。那么可以认为索引index_category(catetory, sub_category)肯定存在的，所以基本可以确认上面的SQL语句是可以用上索引的。

理论上一旦用上index_category索引，那么按品类和子类在索引里筛选。第一筛选很快速，第二筛出来的数据是不多的。所以按道理这个SQL语句应该执行的速度是很快的，即使表里有亿级数据，但是执行时间也最多不应超过1s。但是实际上这个SQL语句跑了几十秒，那么说明它肯定没用上建立的那个索引，所以才会这么慢。

那么它到底是怎么执行的呢，可以先来看一下它的执行计划：

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

从执行计划中的possible_keys里发现是有index_category的，结果实际用的key不是index_category索引，而是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语句使用指定的索引。此时再次执行会发现仅仅耗费100多毫秒而已，性能瞬间就提升上来了。因此当时在紧急关头中，一下子就把这个问题给解决了。

这也是如何去强制改变MySQL执行计划的实战技巧，也就是如果MySQL使用了错误的执行计划，可以使用force index语法。

(3)SQL性能调优分析

一.为什么案例中的MySQL会默认选择对主键的聚簇索引进行扫描

二.为什么案例中没有使用index_category这个二级索引进行扫描

三.即使使用了聚簇索引，为什么这个SQL以前没问题，现在突然有问题

一.为什么MySQL默认选择聚簇索引而不选择index_category二级索引

因为这个商品表是一个亿级数据量的大表，那么对于它来说，index_category这个二级索引也是比较大的。所以此时MySQL就会对判断要选择执行方式一还是选择执行方式二。

执行方式一：

首先从index_category二级索引里来查找到符合where条件的大量数据，接着需要做完order by id desc limit 0,10这个排序和分页的操作，然后回表回到聚簇索引里把所有字段的数据都查出来。

举个例子，比如首先从index_category二级索引里查找出几万条数据。接着因为二级索引里包含主键id值，所以会按order by id desc执行排序。排序时需要对这几万条数据基于临时磁盘文件进行filesort排序。排序完后再按照limit 0,10语法，把指定位置的几条数据拿出来。拿出数据后，再回到聚簇索引里根据id查找，把数据的完整字段查出来。

以上就是MySQL认为使用index_category索引时可能会发生的执行情况。MySQL可能会担心从index_category二级索引里查出来的数据太多了，这么多数据还需要在临时磁盘里排序，这样性能就会很差。因此MySQL就会认为使用index_category二级索引的方式不太好。

执行方式二：

MySQL可能会选择这一种方式，也就是直接扫描主键的聚簇索引。因为聚簇索引都是按id值有序的，所以扫描时可按order by id desc扫描。而且已经知道是limit 0,10，也就是仅仅只要拿到10条数据就行了。所以MySQL在按order by id desc扫描聚簇索引时，就会对每一条数据都采用Using where的方式，使用where category='xx' and sub_category='xx'的条件进行匹配。符合条件的就直接放入结果集里，最多只需要放10条数据就可以返回。

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

二.为什么这个SQL以前扫描聚簇索引没有问题，现在突然就有问题了

这个SQL语句以前在线上系统运行一直没什么问题，也就是之前即使采用扫描聚簇索引的方式，该SQL语句也没有运行很慢。为什么会在某一天晚上突然就大量报慢查询，耗时几十秒了呢？

原因如下：

where category='x' and sub_category='x'这个条件以前通常有返回值，也就是扫描聚簇索引时，通常都能很快找到符合条件的值然后进行返回，所以之前其实性能也没什么问题。但后来可能运营人员，在商品管理的时候加了几种商品分类和子类，而这几种分类和子类的组合其实还没有对应的商品。

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

(4)案例总结

第一个案例，通过禁用MySQL半连接优化或改写SQL语句结构来避免自动半连接优化。

第二个案例，就得通过force index语法来强制某个SQL使用指定的索引。

3.按聚簇索引扫描小表减少回表次数

(1)业务背景介绍

(2)如何进行优化

(3)案例总结

(1)业务背景介绍

有个商品评论系统的数据量非常大，拥有多达十亿量级的评论数据，所以对这个评论数据库进行了分库分表。基本上分完库和表过后，单表的评论数据在百万级别。每一个商品的所有评论都是放在同一个库的同一张表里，这样确保分页查询一个商品的评论时，从一个库的一张表查询即可。

有一些热门商品，可能销量多达上百万，商品的评论可能多达几十万条。有一些用户可能就喜欢看商品评论，不停对某热门商品的评论进行分页。一页一页翻，有时候还会用上分页跳转功能，直接输入要跳到第几页去。所以这时就会涉及到一个问题，针对一个商品几十万评论的深分页问题。

先来看一个经过简化后的对评论表进行分页查询的SQL语句：

SELECT * FROM comments 
WHERE product_id ='xx' and is_good_comment='1' 
ORDER BY id desc LIMIT 100000,20

这个SQL语句的意思就是：比如用户选择了查看某个商品的评论，因此必须限定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在聚簇索引里快速查找，但每条数据都回表。

步骤三：

假设筛选完所有符合条件的数据有十多万条，那么就要按id倒序排序。此时还得基于临时磁盘文件进行倒序排序，又要耗时很久。

步骤四：

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

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

(2)如何进行优化

第二个案例中会基于商品品类查商品表，尽量避免对聚簇索引进行扫描。因为有可能找不到指定的品类下的商品，出现聚簇索引全表扫描的问题。所以当时第二个案例里，选择强制使用一个联合索引，快速定位到数据。接着根据id在临时磁盘文件排序后找到10条分页数据，只需回表查10次。因此当时对第二个案例而言，因为不涉及到大量回表的问题。所以这么做基本是合适的，性能通常在1s以内。

但是这个案例里，就不是这么回事了，这个案例的优化思路反而和前面的第二个案例反过来了。因为在WHERE product_id ='xx' and is_good_comment='1'这个条件中，product_id和is_good_comment不是一个联合索引。所以这个案例中无论如何都会出现大量的回表操作，这个耗时是极高的。既然按二级索引还是按聚簇索引都要大量回表，还不如直接用聚簇索引。

由于第二个案例中如果指定使用联合索引，则不会出现大量的回表操作，所以第二个案例最好还是指定使用联合索引比较好。

由于这个案例中即便使用二级索引，也可能会出现大量的回表操作，所以还不如直接用聚簇索引，因此通常会采取如下方式改造分页查询语句：

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语句的执行计划就会彻底改变MySQL的执行方式，即会先执行括号里的子查询，子查询通常会使用PRIMARY聚簇索引。也就是会按照聚簇索引的id值的倒序方向进行扫描，选出符合WHERE product_id ='xx' and is_good_comment='1'的数据。

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

可见，出现临时表并非都不好，如果临时表的数量很少还是不影响的。这个案例就是通过少量数据的临时表替换大量数据的回表来提升性能。所以针对这个场景，反而是优化成这种方式来执行分页，会更加合理。分页深度越深扫描数据越多，分页深度越浅扫描数据就越少，然后再对筛选出的20条数据进行20次回表查询即可。当做了这个优化后，执行时间降低到了几百毫秒。

(3)案例总结

对于第二个案例来说：

按顺序扫描聚簇索引可能会因找不到数据导致亿级数据量的全表扫描，所以最好通过force index来强制指定根据联合索引去查找。

对于第三个案例来说：

因为前提是做了分库分表，评论表单表数据一般在一百万左右。首先即使一个商品没有评论，全表扫描也不会像扫描上亿数据表那么慢。其次如果根据product_id的二级索引查找，反而可能出现几十万次回表。所以按二级索引查找反而不适合，而按聚簇索引扫描回表更少更加适合。

简而言之，针对不同的场景，要具体情况具体分析。慢的原因在哪儿，为什么慢，然后再用针对性的方式去优化。

4.避免产生长事务长时间执行

(1)业务背景引入

(2)出现SQL慢查询的服务器原因

(3)出现慢SQL的排查方法总结

(4)使用profilling工具对SQL语句进行分析

(5)SQL语句性能调优

(1)业务背景引入

当时有运维删除了千万级的数据，结果导致了频繁的慢查询。接下来介绍这个案例整个排查、定位以及解决的一个过程。

这个案例一开始是从线上收到大量的慢查询告警，当收到大量慢查询告警后，就去检查慢查询SQL，结果发现是普通SQL。这些SQL语句主要都是针对一个表的，同时也比较简单，而且基本都是单行查询，看起来不应该会慢查询。

所以这时就感觉到特别奇怪的，因为SQL本身完全不应该有慢查询。按道理那种SQL语句，基本上会直接根据索引查找出来，性能是极高的。那么可能慢查询就不是SQL问题，而是MySQL生产服务器的问题。

(2)出现SQL慢查询的服务器原因

事实上在某些特定的情况下：MySQL出现慢查询并不是SQL语句的问题，而是它所在服务器负载太高，从而导致SQL语句执行很慢。

特定情况一：磁盘IO负载特别高

比如现在MySQL服务器的磁盘IO负载特别高，即每秒执行大量高负载的随机IO，但磁盘每秒能执行的随机IO是有限的。结果就导致正常的SQL语句去磁盘执行时，因为磁盘太繁忙而需要等待。从而导致本来很快的一个SQL，要等很久才能执行完毕，这时就可能导致正常SQL语句也会变成慢查询。

特定情况二：网络负载很高

同理，除了磁盘外还有的因素是网络。如果网络负载很高，那就可能会导致一个SQL语句发送到MySQL上，光是等待获取和连接都很久，或MySQL网络负载太高带宽打满了。这样即使一个SQL执行很快，但返回数据网络传输很慢，也是慢查询。

特定情况三：CPU负载很高

另外一个关键的因素就是CPU负载。如果CPU负载过高，也会导致CPU过于繁忙在执行别的任务，而没时间执行SQL语句，此时也有可能会导致SQL语句出现慢查询。

所以出现慢查询不一定就是SQL导致的，如果觉得SQL不应该慢查询，结果某个时间段跑这个SQL就是慢，此时应该排查一下当时MySQL服务器的负载。尤其看看磁盘、网络以及CPU的负载是否正常，如果发现那个时间段MySQL服务器的磁盘、网络或CPU负载特别高，那么可能就是服务器负载导致的。

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

(3)出现慢SQL的排查方法总结

一.检查SQL是否有问题，主要就看执行计划

二.检查MySQL服务器的负载

三.都不行再用profilling工具去细致的分析SQL语句的执行过程和耗时

(4)使用profilling工具对SQL语句进行分析

回到千万级数据删除导致的慢查询的案例中，针对某个表的大量简单的单行数据查询SQL变成慢查询问题，于是先排查了SQL执行计划以及MySQL服务器负载，发现都没有问题。

此时就必须用上一个SQL调优的利器了，也就是profiling工具。这个工具可以对SQL语句的执行耗时进行非常深入和细致的分析。使用这个工具的过程，大致如下所示：

步骤一：

首先要使用命令：set profiling=1，打开profiling。接着MySQL就会自动记录查询语句的profiling信息。

此时如果执行show profiles命令，会列出各种查询语句的profiling信息。这里很关键的一点，就是它会记录下来每个查询语句的query id。所以要针对需要分析的query找对它的query id，假设针对慢查询的那个SQL语句找到了query id。

步骤二：

然后就可以针对单个查询语句，看一下它的profiling具体信息。使用命令：show profile cpu, block io for query xx。这里的xx是数字，此时就可以看到这个SQL语句执行时的profile信息了。除了CPU以及Block IO以外，还可指定去看其他各项负载和耗时。

步骤三：

使用show profile展示出SQL语句执行时的各种耗时等profiling信息，如磁盘IO耗时、CPU等待耗时、发送数据耗时、拷贝数据临时表耗时等。

当仔细检查一下这个SQL语句的profiling信息时，发现一个问题，就是它的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这个指标。这个指标它的值特别高，达到了上万这个级别。这个history list length与MVCC机制有关，MVCC与Read View机制有关，同时还与数据的undo多版本快照链有关。

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

一般来说，这个值是不应该过高的。而展示innodb存储引擎的状态表示，history list length值过高，这表明大量的undo多版本链条数据没被清理。所以推测可能有的事务长时间运行，导致其undo日志不能被purge清理，从而导致history list length的值过高。

至此，大量简单SQL语句变成慢查询，基本可以肯定的两点是：一.一些SQL因为Sending Data环节异常耗时过高。二.同时出现一些长事务长时间运行，导致大量undo日志无法purge清理。

(5)SQL语句性能调优

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

这个清理是怎么做的呢，就是居然开了一个事务。然后在一个事务里删除上千万数据，导致这个事务一直在运行，所以才看到这个案例出现的一些奇怪现象。

然后这种长事务的运行会导致一个问题：就是删除时只是对数据加了一个删除标记，事实上并没有彻底删除掉。

此时如果有跟长事务同时运行的其他事务，它们在查询时是可能会把那上千万被标记为删除的数据都扫一遍的。因为每次扫描到一批数据，都发现标记为删除了。接着就会再继续往下扫描，所以才导致一些查询语句会那么慢。

那么为什么启动一个事务，在事务里查询，凭什么要去扫描之前那个长事务标记为删除状态的垃圾数据呢？那些数据都被删除了，跟当前事务没关系了，应该可以不用扫描它们的。

这个问题的关键点就在于，那个删除千万级数据的事务是个长事务。当启动新事务查询时，那个删除千万级数据的长事务一直在运行活跃的。而启动一个新事务查询时，会生成一个Read View，这个Read View里包含了当前活跃事务的最大id、最小id和事务id。

然后它有一个判定规则：新事务查询时，会根据ReadView判断哪些数据是可见的，以及可见版本。因为一个数据有一个版本链条，有时可见的只是该数据的一个历史版本。

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

因此，永远不要在业务高峰期去运行那种删除大量数据的语句。所以解决方案很简单，直接kill掉那个正在删除千万级数据的长事务。