mysql limit越大越慢(MySQL limit分页大偏移量慢的原因及优化方案)
mysql limit越大越慢
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 分页的资料请关注开心学习网其它相关文章!
原文链接:https://javageekers.club/archives/mysql-limit
- mysql order by id查询变慢(MySQL不使用order by实现排名的三种思路总结)
- php添加数据到mysql数据库(PHP通过代码连接XAMPP数据库及MySQL数据库方法)
- mysqltruncate(MYSQL中Truncate的用法详解)
- 在php中与数据库连接的技术(PHP7.0连接DB操作实例分析基于mysqli)
- linux安装nacos教程(Nacos配置MySQL8的方法)
- mysql是否支持透明数据加密(MySQL的加密解密的几种方式小结)
- mysql 索引使用总结(MySQL复合索引的深入探究)
- mysql配置多实例
- mysql 高级查询语法(MySQL查询语句进阶知识集锦)
- oraclemysql知识点(详解Mysql和Oracle之间的误区)
- idea的mysql如何连接(在IntelliJ IDEA中使用Java连接MySQL数据库的方法详解)
- linux系统登录mysql数据库(Linux手动部署远程的mysql数据库的方法详解)
- mysql 分组自定义排序(正排倒排,并不是 MySQL 的排序的全部!)
- mysql安装详解(MySQL Router的安装部署)
- mysql中常用的三种存储引擎的区别(MySQL 存储过程的优缺点分析)
- mysqlbinlog怎么分析(MySQL中使用binlog时格式该如何选择)
- 销 售 买 卖 你真的了解这四个字了吗(销售买)
- 谢娜是得罪快乐大本营造型师了吗 全场被黑化(谢娜是得罪快乐大本营造型师了吗)
- 前《iLOOK》时装总监 《快乐大本营》御用造型师上线(快乐大本营御用造型师上线)
- 释小龙晒杀青照片 多重身份惹观众期待(释小龙晒杀青照片)
- 《九牛之人降魔传》开机 演员祁高坤化身九牛之人除魔卫道(九牛之人降魔传开机)
- 王铲铲的致富之路无限金币卡法攻略教学(王铲铲的致富之路无限金币卡法攻略教学)
热门推荐
- ASP.NET给图片加文字水印
- 如何用python人脸识别(Python学习笔记之视频人脸检测识别实例教程)
- python字符串匹配教程(Python字符串匹配之6种方法的使用详解)
- javascript怎么编写游戏(javaScript实现网页版的弹球游戏)
- vue怎么在中间加图片(Vue实现多图添加显示和删除)
- HTTP header中Cache-control的介绍
- 云服务器需要都有什么技术(浅谈云服务器配置应该如何选择?)
- laravel api 调试(Laravel框架自定义公共函数的引入操作示例)
- 如何利用python编写日期的程序(Python读取指定日期邮件的实例)
- css3基础入门(详解使用CSS3的@media来编写响应式的页面)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9