MySQL limit分页大偏移量慢的原因及优化方案
在 MySQL 中通常我们使用 limit 来完成页面上的分页功能,但是当数据量达到一个很大的值之后,越往后翻页,接口的响应速度就越慢。
本文主要讨论 limit 分页大偏移量慢的原因及优化方案,为了模拟这种情况,下面首先介绍表结构和执行的 SQL。
场景模拟
建表语句
user 表的结构比较简单,id、sex 和 name,为了让 SQL 的执行时间变化更加明显,这里有9个姓名列。
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `sex` tinyint(4) NULL DEFAULT NULL COMMENT '性别 0-男 1-女', `name1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `name2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `name3` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `name4` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `name5` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `name6` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `name7` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `name8` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `name9` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', PRIMARY KEY (`id`) USING BTREE, INDEX `sex`(`sex`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 9000001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
数据填充
这里建立了一个存储过程来进行数据的填充,一共9000000条数据,执行完函数后再执行一句SQL,修改性别字段。
ps:这个函数执行的挺久的,我运行了617.284秒。
CREATE DEFINER=`root`@`localhost` PROCEDURE `data`() begin declare i int; set i=1; while(i<=9000000)do insert into user values(i,0,i,i,i,i,i,i,i,i,i); set i=i+1; end while; end -- 将id为偶数的user设置性别为1-女 update user set sex=1 where id%2=0;
SQL与执行时间
SQL | 执行时间 |
---|---|
select * from user where sex = 1 limit 100, 10; | OK, Time: 0.005000s |
select * from user where sex = 1 limit 1000, 10; | OK, Time: 0.007000s |
select * from user where sex = 1 limit 10000, 10; | OK, Time: 0.016000s |
select * from user where sex = 1 limit 100000, 10; | OK, Time: 0.169000s |
select * from user where sex = 1 limit 1000000, 10; | OK, Time: 5.892000s |
select * from user where sex = 1 limit 10000000, 10; | OK, Time: 33.465000s |
可以看到,limit 的偏移量越大,执行时间越长。
原因分析
首先来分析一下这句 SQL 执行的过程,就拿上面表格中的第一行来举例。
由于 sex 列是索引列,MySQL会走 sex 这棵索引树,命中 sex=1 的数据。
然后又由于非聚簇索引中存储的是主键 id 的值,且查询语句要求查询所有列,所以这里会发生一个回表的情况,在命中 sex 索引树中值为1的数据后,拿着它叶子节点上的值也就是主键 id 的值去主键索引树上查询这一行其他列(name、sex)的值,最后返回到结果集中,这样第一行数据就查询成功了。
最后这句 SQL 要求limit 100, 10,也就是查询第101到110个数据,但是 MySQL 会查询前110行,然后将前100行抛弃,最后结果集中就只剩下了第101到110行,执行结束。
小结一下,在上述的执行过程中,造成 limit 大偏移量执行时间变久的原因有:
- 查询所有列导致回表
- limit a, b会查询前a+b条数据,然后丢弃前a条数据
综合上述两个原因,MySQL 花费了大量时间在回表上,而其中a次回表的结果又不会出现在结果集中,这才导致查询时间变得越来越长。
优化方案
覆盖索引
既然无效的回表是导致查询变慢的主要原因,那么优化方案就主要从减少回表次数方面入手,假设在limit a, b中我们首先得到了a+1到a+b条数据的id,然后再进行回表获取其他列数据,那么就减少了a次回表操作,速度肯定会快上不少。
这里就涉及到覆盖索引了,所谓的覆盖索引就是从非主聚簇索引中就能查到的想要数据,而不需要通过回表从主键索引中查询其他列,能够显著提升性能。
基于这样的思路,优化方案就是先查询得到主键id,然后再根据主键id查询其他列数据,优化后的 SQL 以及执行时间如下表。
优化后的 SQL | 执行时间 |
---|---|
select * from user a join (select id from user where sex = 1 limit 100, 10) b on a.id=b.id; | OK, Time: 0.000000s |
select * from user a join (select id from user where sex = 1 limit 1000, 10) b on a.id=b.id; | OK, Time: 0.00000s |
select * from user a join (select id from user where sex = 1 limit 10000, 10) b on a.id=b.id; | OK, Time: 0.002000s |
select * from user a join (select id from user where sex = 1 limit 100000, 10) b on a.id=b.id; | OK, Time: 0.015000s |
select * from user a join (select id from user where sex = 1 limit 1000000, 10) b on a.id=b.id; | OK, Time: 0.151000s |
select * from user a join (select id from user where sex = 1 limit 10000000, 10) b on a.id=b.id; | OK, Time: 1.161000s |
果然,执行效率得到了显著提升。
条件过滤
当然还有一种有缺陷的方法是基于排序做条件过滤。
比如像上面的示例 user 表,我要使用 limit 分页得到1000001到1000010条数据,可以这样写 SQL:
select * from user where sex = 1 and id > (select id from user where sex = 1 limit 1000000, 1) limit 10;
但是使用这样的方式优化是有条件的:主键id必须是有序的。在有序的条件下,也可以使用比如创建时间等其他字段来代替主键id,但是前提是这个字段是建立了索引的。
总之,使用条件过滤的方式来优化 limit 是有诸多限制的,一般还是推荐使用覆盖索引的方式来优化。
小结
主要分析了 limit 分页大偏移量慢的原因,同时也提出了响应的优化方案,推荐使用覆盖索引的方式来优化 limit 分页大偏移执行时间久的问题。
希望能帮助到大家。
以上就是MySQL limit分页大偏移量慢的原因及优化方案的详细内容,更多关于MySQL limit 分页的资料请关注猪先飞其它相关文章!
相关文章
- 我们这里介绍php与KindEditor编辑器使用时如何利用KindEditor编辑器的分页功能实现文章内容分页,KindEditor编辑器在我们点击分页时会插入代码,我们只要以它为分切符,就...2016-11-25
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 最近接了一个项目,其中有需求要用到jquery分页控件,上网也找到了需要分页控件,各种写法各种用法,都是很复杂,最终决定自己动手写一个jquery分页控件,全当是练练手了。写的不好,还请见谅,本分页控件在chrome测试过,其他的兼容性...2015-10-30
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
- 本文实例讲述了jquery实现的伪分页效果代码。分享给大家供大家参考,具体如下:这里介绍的jquery伪分页效果,在火狐下表现完美,IE全系列下有些问题,引入了jQuery1.7.2插件,代码里有丰富的注释,相信对学习jQuery有不小的帮助,期...2015-10-30
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- Vue.js通过简洁的API提供高效的数据绑定和灵活的组件系统.这篇文章主要介绍了vue.js 表格分页ajax 异步加载数据的相关资料,需要的朋友可以参考下...2016-10-20
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
Springboot如何使用mybatis实现拦截SQL分页
这篇文章主要介绍了Springboot使用mybatis实现拦截SQL分页,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下...2020-06-19- 在php中要实现分页比起asp中要简单很多了,我们核心就是直接获取当前页面然后判断每页多少再到数据库中利用limit就可以实现分页查询了,下面我来详细介绍分页类实现程序...2016-11-25
- 首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
- MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
- 本文实例讲述了jquery实现的伪分页效果代码。分享给大家供大家参考,具体如下:这里介绍的jquery伪分页效果,在火狐下表现完美,IE全系列下有些问题,引入了jQuery1.7.2插件,代码里有丰富的注释,相信对学习jQuery有不小的帮助,期...2015-10-30
- 一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
- Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
- 这篇文章主要介绍了基于jquery实现表格无刷新分页,功能实现了前端排序功能,增加了前端搜索功能,感兴趣的小伙伴们可以参考一下...2016-01-08