一.复杂的深分页问题优化
背景
有一个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'条件的,后面就不用进行过滤了,就不会就是符合就不用对create_time过滤。
流程确实是这个流程,但是去查询时,如果limit还是210000, 100时,还是查不出数据,几分钟都没有数据,一直到navica提示超时,使用Explain看的话,确实命中索引了,如果将offset调小,调成6000, 100,勉强可以查出数据,但是需要46s,所以瓶颈不在这里。
真实原因如下:
先看关于深分页的两个查询,id是主键,val是普通索引
直接查询法
select * from test where val=4 limit 300000,5;点击复制代码复制出错复制成功
先查主键再join
select * from test a inner join (select id from test where val=4 limit 300000,5) as b on a.id=b.id;点击复制代码复制出错复制成功
这两个查询的结果都是查询出offset是30000后的5条数据,区别在于第一个查询需要先去普通索引val中查询出300005个id,然后去聚集索引下读取300005个数据页,然后抛弃前面的300000个结果,只返回最后5个结果,过程中会产生了大量的随机I/O。第二个查询一开始在普通索引val下就只会读取后5个id,然后去聚集索引下读取5个数据页。
同理我们业务中那条查询其实是更加复杂的情况,因为我们业务的那条SQL不仅会读取article表中的210100条结果,而且会每条结果去content表中查询文章相关内容,而这张表有几个TEXT类型的字段,我们使用show table status命令查看表相关的信息发现
Name | Engine | Row_format | Rows | Avg_Row_length |
article | InnoDB | Compact | 2682682 | 266 |
content | InnoDB | Compact | 2824768 | 16847 |
发现两个表的数据量都是200多万的量级,article表的行平均长度是266,content表的平均长度是16847,简单来说是当 InnoDB 使用 Compact 或者 Redundant 格式存储极长的 VARCHAR 或者 BLOB 这类大对象时,我们并不会直接将所有的内容都存放在数据页节点中,而是将行数据中的前 768 个字节存储在数据页中,后面会通过偏移量指向溢出页。
(详细了解可以看看这篇文章深度好文带你读懂MySQL和InnoDB)
这样再从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。