MySQL慢查询优化（线上案例调优）

一.复杂的深分页问题优化

背景

有一个article表，用于存储文章的基本信息的，有文章id，作者id等一些属性，有一个content表，主要用于存储文章的内容，主键是article_id，需求需要将一些满足条件的作者发布的文章导入到另外一个库，所以我同事就在项目中先查询出了符合条件的作者id，然后开启了多个线程，每个线程每次取一个作者id，执行查询和导入工作。

查询出作者id是1111，名下的所有文章信息，文章内容相关的信息的SQL如下：

SELECT
	a.*, c.*
FROM
	article a
LEFT JOIN content c ON a.id = c.article_id
WHERE
	a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 210000,100

因为查询的这个数据库是机械硬盘的，在offset查询到20万时，查询时间已经特别长了，运维同事那边直接收到报警，说这个库已经IO阻塞了，已经多次进行主从切换了，我们就去navicat里面试着执行了一下这个语句，也是一直在等待， 然后对数据库执行show proceesslist 命令查看了一下，发现每个查询都是处于Writing to net的状态，没办法只能先把导入的项目暂时下线，然后执行kill命令将当前的查询都杀死进程(因为只是客户端Stop的话，MySQL服务端会继续查询)。

然后我们开始分析这条命令执行慢的原因：

是否是联合索引的问题

当前是索引情况如下：

article表的主键是id，author_id是一个普通索引
content表的主键是article_id

所以认为当前是执行流程是先去article表的普通索引author_id里面找到1111的所有文章id，然后根据这些文章id去article表的聚集索引中找到所有的文章，然后拿每个文章id去content表中找文章内容等信息，然后判断create_time是否满足要求，进行过滤，最终找到offset为20000后的100条数据。

所以我们就将article的author_id索引改成了联合索引(author_id,create_time),这样联合索引(author_id,create_time)中的B+树就是先安装author_id排序，再按照create_time排序，这样一开始在联合(author_id,create_time)查询出来的文章id就是满足create_time < '2020-04-29 00:00:00’条件的，后面就不用进行过滤了，就不会就是符合就不用对create_time过滤。
流程确实是这个流程，但是去查询时，如果limit还是210000, 100时，还是查不出数据，几分钟都没有数据，一直到navica提示超时，使用Explain看的话，确实命中索引了，如果将offset调小，调成6000, 100，勉强可以查出数据，但是需要46s，所以瓶颈不在这里。

查询慢的原因

SELECT
	a.*, c.*
FROM
	article a
LEFT JOIN content c ON a.id = c.article_id
WHERE
	a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 210000,100

首先我们需要知道innodb引擎在执行时，并不了解我们的业务规则，它是不知道article表中如果有一篇文章存在，那么在content表里面一定会有这篇文章的内容信息，也就是它不知道article表的id在content表中一定会有一个article_id与之对应。所以innodb引擎的执行流程是这样：
1.先去article表中找出满足a.author_id = 1111 AND a.create_time < '2020-04-29 00:00:00’条件的22000条数据的所有字段，加载到内存中。（在MySQL进行join时，加载到内存中并不只是join字段，而是SELECT 的所有字段，很容易理解，如果只是join的字段，那么最后还需要根据join的字段去回表。）
2.然后根据这22000数据去content表里面查找文章内容相关的字段。（由于content表存储了文章内容，一些字段是特别大的，是不会存储在聚簇索引的叶子节点中的，而且存储在其他地方，所以会产生大量随机IO，这是导致这个查询这么慢的原因。）
3.最终把22000条数据返回给MySQL Server，取最后面的100条数据，返回给客户端。
使用show table status命令查看article表和content表显示的数据行平均长度

发现两个表的数据量都是200多万的量级，article表的行平均长度是266，content表的平均长度是16847，简单来说是当 InnoDB 使用 Compact 或者 Redundant 格式存储极长的 VARCHAR 或者 BLOB 这类大对象时，我们并不会直接将所有的内容都存放在数据页节点中，而是将行数据中的前 768 个字节存储在数据页中，后面会通过偏移量指向溢出页。

这样再从content表里面查询连续的100行数据时，读取每行数据时，还需要去读溢出页的数据，这样就需要大量随机IO，因为机械硬盘的硬件特性，随机IO会比顺序IO慢很多。所以我们后来又进行了测试，

只是从article表里面查询limit 200000，100的数据，发现即便存在深分页的问题，查询时间只是0.5s，因为article表的平均列长度是266，所有数据都存在数据页节点中，不存在页溢出，所以都是顺序IO，所以比较快。

//查询时间0.51s
SELECT a.* FROM article a  
WHERE a.author_id = 1111  
AND a.create_time < '2020-04-29 00:00:00' 
LIMIT 200100, 100

相反的，我们直接先找出100个article_id去content表里面查询数据，发现比较慢，第一次查询时需要3s左右(也就是这些id的文章内容相关的信息都没有过，没有缓存的情况)，第二次查询时因为这些溢出页数据已经加载到buffer pool，所以大概0.04s。

SELECT SQL_NO_CACHE c.* 
FROM article_content c 
WHERE c.article_id in(100个article_id)

解决方案

所以针对这个问题的解决方案主要有两种：

先查出主键id再inner join

非连续查询的情况下，也就是我们在查第100页的数据时，不一定查了第99页，也就是允许跳页查询的情况，那么就是使用先查主键再join这种方法对我们的业务SQL进行改写成下面这样，下查询出210000, 100时主键id，作为临时表temp_table，将article表与temp_table表进行inner join，查询出中文章相关的信息，并且去left Join content表查询文章内容相关的信息。 第一次查询大概1.11s，后面每次查询大概0.15s

SELECT
	a.*, c.*
FROM article a
INNER JOIN(
	SELECT	id FROM	article a
	WHERE	a.author_id = 1111
	AND a.create_time < '2020-04-29 00:00:00'
	LIMIT 210000 ,
	100
) as temp_table ON a.id = temp_table.id
LEFT JOIN content c ON a.id = c.article_id

优化结果

优化前，offset达到20万的量级时，查询时间过长，一直到超时。

优化后，offset达到20万的量级时，查询时间为1.11s。

利用范围查询条件来限制取出的数据

这种方法的大致思路如下，假设要查询test_table中offset为10000的后100条数据，假设我们事先已知第10000条数据的id，值为min_id_value

select * from test_table where id > min_id_value order by id limit 0, 100，就是即利用条件id > min_id_value在扫描索引是跳过10000条记录，然后取100条数据即可，这种处理方式的offset值便成为0了，但此种方式有限制，必须知道offset对应id，然后作为min_id_value，增加id > min_id_value的条件来进行过滤，如果是用于分页查找的话，也就是必须知道上一页的最大的id，所以只能一页一页得查，不能跳页，但是因为我们的业务需求就是每次100条数据，进行分批导数据，所以我们这种场景是可以使用。针对这种方法，我们的业务SQL改写如下：

//先查出最大和最小的id
SELECT min(a.id) as min_id , max(a.id) as max_id 
FROM article a 
WHERE a.author_id = 1111  
AND a.create_time < '2020-04-29 00:00:00' 
//然后每次循环查找
while(min_id<max_id) {
		SELECT a.*, c.* FROM article a LEFT JOIN content c ON a.id = c.article_id  WHERE a.author_id = 1111  AND a.id > min_id LIMIT 100
		//这100条数据导入完毕后，将100条数据数据中最大的id赋值给min_id，以便导入下100条数据
}

优化结果

优化前，offset达到20万的量级时，查询时间过长，一直到超时。

优化后，offset达到20万的量级时，由于知道第20万条数据的id，查询时间为0.34s。