MySQL limit分页大偏移量慢的原因及优化方案
在MySQL中通常我们使用limit来完成页面上的分页功能,但是当数据量达到一个很大的值之后,越往后翻页,接口的响应速度就越慢。
本文主要讨论limit分页大偏移量慢的原因及优化方案,为了模拟这种情况,下面首先介绍表结构和执行的SQL。
场景模拟
建表语句
user表的结构比较简单,id、sex和name,为了让SQL的执行时间变化更加明显,这里有9个姓名列。
CREATETABLE`user`( `id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键', `sex`tinyint(4)NULLDEFAULTNULLCOMMENT'性别0-男1-女', `name1`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'姓名', `name2`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'姓名', `name3`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'姓名', `name4`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'姓名', `name5`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'姓名', `name6`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'姓名', `name7`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'姓名', `name8`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'姓名', `name9`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'姓名', PRIMARYKEY(`id`)USINGBTREE, INDEX`sex`(`sex`)USINGBTREE )ENGINE=InnoDBAUTO_INCREMENT=9000001CHARACTERSET=utf8COLLATE=utf8_general_ciROW_FORMAT=Dynamic;
数据填充
这里建立了一个存储过程来进行数据的填充,一共9000000条数据,执行完函数后再执行一句SQL,修改性别字段。
ps:这个函数执行的挺久的,我运行了617.284秒。
CREATEDEFINER=`root`@`localhost`PROCEDURE`data`() begin declareiint; seti=1; while(i<=9000000)do insertintouservalues(i,0,i,i,i,i,i,i,i,i,i); seti=i+1; endwhile; end --将id为偶数的user设置性别为1-女 updateusersetsex=1whereid%2=0;
SQL与执行时间
SQL | 执行时间 |
---|---|
select*fromuserwheresex=1limit100,10; | OK,Time:0.005000s |
select*fromuserwheresex=1limit1000,10; | OK,Time:0.007000s |
select*fromuserwheresex=1limit10000,10; | OK,Time:0.016000s |
select*fromuserwheresex=1limit100000,10; | OK,Time:0.169000s |
select*fromuserwheresex=1limit1000000,10; | OK,Time:5.892000s |
select*fromuserwheresex=1limit10000000,10; | OK,Time:33.465000s |
可以看到,limit的偏移量越大,执行时间越长。
原因分析
首先来分析一下这句SQL执行的过程,就拿上面表格中的第一行来举例。
由于sex列是索引列,MySQL会走sex这棵索引树,命中sex=1的数据。
然后又由于非聚簇索引中存储的是主键id的值,且查询语句要求查询所有列,所以这里会发生一个回表的情况,在命中sex索引树中值为1的数据后,拿着它叶子节点上的值也就是主键id的值去主键索引树上查询这一行其他列(name、sex)的值,最后返回到结果集中,这样第一行数据就查询成功了。
最后这句SQL要求limit100,10,也就是查询第101到110个数据,但是MySQL会查询前110行,然后将前100行抛弃,最后结果集中就只剩下了第101到110行,执行结束。
小结一下,在上述的执行过程中,造成limit大偏移量执行时间变久的原因有:
- 查询所有列导致回表
- limita,b会查询前a+b条数据,然后丢弃前a条数据
综合上述两个原因,MySQL花费了大量时间在回表上,而其中a次回表的结果又不会出现在结果集中,这才导致查询时间变得越来越长。
优化方案
覆盖索引
既然无效的回表是导致查询变慢的主要原因,那么优化方案就主要从减少回表次数方面入手,假设在limita,b中我们首先得到了a+1到a+b条数据的id,然后再进行回表获取其他列数据,那么就减少了a次回表操作,速度肯定会快上不少。
这里就涉及到覆盖索引了,所谓的覆盖索引就是从非主聚簇索引中就能查到的想要数据,而不需要通过回表从主键索引中查询其他列,能够显著提升性能。
基于这样的思路,优化方案就是先查询得到主键id,然后再根据主键id查询其他列数据,优化后的SQL以及执行时间如下表。
优化后的SQL | 执行时间 |
---|---|
select*fromuserajoin(selectidfromuserwheresex=1limit100,10)bona.id=b.id; | OK,Time:0.000000s |
select*fromuserajoin(selectidfromuserwheresex=1limit1000,10)bona.id=b.id; | OK,Time:0.00000s |
select*fromuserajoin(selectidfromuserwheresex=1limit10000,10)bona.id=b.id; | OK,Time:0.002000s |
select*fromuserajoin(selectidfromuserwheresex=1limit100000,10)bona.id=b.id; | OK,Time:0.015000s |
select*fromuserajoin(selectidfromuserwheresex=1limit1000000,10)bona.id=b.id; | OK,Time:0.151000s |
select*fromuserajoin(selectidfromuserwheresex=1limit10000000,10)bona.id=b.id; | OK,Time:1.161000s |
果然,执行效率得到了显著提升。
条件过滤
当然还有一种有缺陷的方法是基于排序做条件过滤。
比如像上面的示例user表,我要使用limit分页得到1000001到1000010条数据,可以这样写SQL:
select*fromuserwheresex=1andid>(selectidfromuserwheresex=1limit1000000,1)limit10;
但是使用这样的方式优化是有条件的:主键id必须是有序的。在有序的条件下,也可以使用比如创建时间等其他字段来代替主键id,但是前提是这个字段是建立了索引的。
总之,使用条件过滤的方式来优化limit是有诸多限制的,一般还是推荐使用覆盖索引的方式来优化。
小结
主要分析了limit分页大偏移量慢的原因,同时也提出了响应的优化方案,推荐使用覆盖索引的方式来优化limit分页大偏移执行时间久的问题。
希望能帮助到大家。
以上就是MySQLlimit分页大偏移量慢的原因及优化方案的详细内容,更多关于MySQLlimit分页的资料请关注毛票票其它相关文章!