mysql提高分页效率(MySQL优化教程之超大分页查询)
mysql提高分页效率
MySQL优化教程之超大分页查询目录
- 背景
- LIMIT优化
-
优化方式
- 1、使用覆盖索引
- 2、子查询优化
- 3、延迟关联
- 4、记录上次查询结束的位置
- 总结
背景
基本上只要是做后台开发,都会接触到分页这个需求或者功能吧。基本上大家都是会用MySQL的LIMIT来处理,而且我现在负责的项目也是这样写的。但是一旦数据量起来了,其实LIMIT的效率会极其的低,这一篇文章就来讲一下LIMIT子句优化的。
LIMIT优化
很多业务场景都需要用到分页这个功能,基本上都是用LIMIT来实现。
建表并且插入200万条数据:
|
# 新建一张t5表 CREATE TABLE `t5` ( `id` int NOT NULL AUTO_INCREMENT, ` name ` varchar (50) NOT NULL , `text` varchar (100) NOT NULL , PRIMARY KEY (`id`), KEY `ix_name` (` name `), KEY `ix_test` (`text`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # 创建存储过程插入200万数据 CREATE PROCEDURE t5_insert_200w() BEGIN DECLARE i INT ; SET i=1000000; WHILE i<=3000000 DO INSERT INTO t5(` name `,text) VALUES ( 'god-jiang666' ,concat( 'text' , i)); SET i=i+1; END WHILE; END ; # 调用存储过程插入200万数据 call t5_insert_200w(); |
在翻页比较少的情况下,LIMIT是不会出现任何性能上的问题的。
但是如果用户需要查到最后面的页数呢?
通常情况下,我们要保证所有的页面可以正常跳转,因为不会使用order by xxx desc这样的倒序SQL来查询后面的页数,而是采用正序顺序来做分页查询:
|
select * from t5 order by text limit 100000, 10; |
采用这种SQL查询分页的话,从200万数据中取出这10行数据的代价是非常大的,需要先排序查出前1000010条记录,然后抛弃前面1000000条。我的macbook pro跑出来花了5.578秒。
接下来我们来看一下,上面这条SQL语句的执行计划:
|
explain select * from t5 order by text limit 1000000, 10; |
从执行计划可以看出,在大分页的情况下,MySQL没有走索引扫描,即使text字段我已经加上了索引。
这是为什么呢?
回到MySQL索引(二)如何设计索引中有提及到,MySQL数据库的查询优化器是采用了基于代价的,而查询代价的估算是基于CPU代价和IO代价。
如果MySQL在查询代价估算中,认为全表扫描方式比走索引扫描的方式效率更高的话,就会放弃索引,直接全表扫描。
这就是为什么在大分页的SQL查询中,明明给该字段加了索引,但是MySQL却走了全表扫描的原因。
然后我们继续用上面的查询SQL来验证我的猜想:
|
explain select * from t5 order by text limit 7774, 10; |
|
explain select * from t5 order by text limit 7775, 10; |
以上的实验均在我的mbp上运行的,在7774这个临界点上,MySQL分别采用了索引扫描和全表扫描的查询优化方式。
所以可以认为MySQL会根据它自己的代价查询优化器来判断是否使用索引。
由于MySQL的查询优化器的算法核心是我们无法人工干预的,所以我们的优化思路就要着手于如何让分页维持在最佳的的分页临界点。
优化方式
1、使用覆盖索引
如果一条SQL语句,通过索引可以直接获取查询的结果,不再需要回表查询,就称这个索引为覆盖索引。
在MySQL数据库中使用explain关键字查看执行计划,如果extra这一列显示Using index,就表示这条SQL语句使用了覆盖索引。
让我们来对比一下使用了覆盖索引,性能会提升多少吧。
|
# 没有使用覆盖索引 select * from t5 order by text limit 1000000, 10; |
这次查询花了3.690秒,让我们看一下使用了覆盖索引优化会提升多少性能吧。
|
# 使用了覆盖索引 select id, `text` from t5 order by text limit 1000000, 10; |
从上面的对比中,超大分页查询中,使用了覆盖索引之后,花了0.201秒,而没有使用覆盖索引花了3.690秒,提高了18倍多,这在实际开发中,就是一个大的性能优化了。(该数据在我的mbp上运行得出)
2、子查询优化
因为实际开发中,用SELECT查询一两列操作是非常少的,因此上述的覆盖索引的适用范围就比较有限。
所以我们可以通过把分页的SQL语句改写成子查询的方法获得性能上的提升。
|
select * from t5 where id>=( select id from t5 order by text limit 1000000, 1) limit 10; |
其实使用这种方法,提升的效率和上面使用了覆盖索引基本一致。
但是这种优化方法也有局限性:
- 这种写法,要求主键ID必须是连续的
- Where子句不允许再添加其他条件
3、延迟关联
和上述的子查询做法类似,我们可以使用JOIN,先在索引列上完成分页操作,然后再回表获取所需要的列。
|
select a.* from t5 a inner join ( select id from t5 order by text limit 1000000, 10) b on a.id=b.id; |
从实验中可以得出,在采用JOIN改写后,上面的两个局限性都已经解除了,而且SQL的执行效率也没有损失。
4、记录上次查询结束的位置
和上面使用的方法都不同,记录上次结束位置优化思路是使用某种变量记录上一次数据的位置,下次分页时直接从这个变量的位置开始扫描,从而避免MySQL扫描大量的数据再抛弃的操作。
|
select * from t5 where id>=1000000 limit 10; |
根据以上实验,不难得出,由于使用了主键索引做分页操作,SQL的性能是最快的。
总结
- 介绍了超大分页查询性能过差的原因,还有分享了几个优化思路
- 超大分页的优化思路就是让分页的SQL尽量在最佳的性能区间执行,不要触发全表扫描即可
- 希望以上的分享,可以让你们在MySQL这条路上少走弯路~~~
参考资料
- 《MySQL性能优化》第六章 查询优化性能
- 《数据库查询优化器的艺术》
到此这篇关于MySQL优化教程之超大分页查询的文章就介绍到这了,更多相关MySQL超大分页查询内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://blog.csdn.net/weixin_37686415/article/details/109607960
- mysql集群是用什么搭建的(MySQL5.7 集群配置的步骤)
- mysql如何操作日期
- mysqlinnodb数据存储格式(MySQL InnoDB ReplicaSet副本集简单介绍)
- mysql游标的使用(MySQL 游标的作用与使用相关)
- 最新版MySQL 8.0.22下载安装超详细教程(Windows 64位)(最新版MySQL 8.0.22下载安装超详细教程Windows 64位)
- mysql标签的用法(mysql 的indexof函数用法说明)
- mysql主键什么情况用uuid(Mysql主键UUID和自增主键的区别及优劣分析)
- mysql权限收回(MySQL如何利用DCL管理用户和控制权限)
- mysql意外查不到数据(MySQL 丢失数据的原因及解决)
- mysql基础操作报告(gorm操作MySql数据库的方法)
- mysql语句运行顺序(浅谈mysql执行过程以及顺序)
- mysql5.5.36版本介绍(WDCP控制面板升级mysql为5.7.11的方法)
- mysql基本查询方法(MySQL 重写查询语句的三种策略)
- mysql视图管理方法(MySQL 视图View原理解析)
- mysql字符集怎么看(mysql字符集相关总结)
- 如何使java与sqlserver数据库连接(java连接mysql数据库 java连接sql server数据库)
- 二次创业 的富士胶片,在进博会上首次展示完成转型后的全线医疗产品(二次创业的富士胶片)
- 富士胶片 中国 我们对上海的信心没有任何改变(富士胶片中国)
- 赢麻了 富士公布2021年度财报 营利同比增长240(富士公布2021年度财报)
- 医事文化谈屑 | 古人的名 字 号(医事文化谈屑古人的名)
- ()
- 网友很惭愧,自己写了很多年的字,到头来还不如一名小学生写的好(自己写了很多年的字)
热门推荐
- nginx把https转为http请求(使用nginx方式实现http转换为https的示例代码)
- docker 跨主机的网络通信(docker 基于golang镜像构建 ssh服务的方法)
- python按顺序遍历字典(Python字典遍历操作实例小结)
- python函数基本使用(Python3中exp函数用法分析)
- 超级小旋风asp服务器软件使用图文教程(超级小旋风asp服务器软件使用图文教程)
- 服务器如何搭建自己的网站(个人搭建网站的服务器选择方法)
- laravel 开发自定义组件(解决Laravel blade模板转义html标签的问题)
- thinkphp5设计权限(Thinkphp5.0 框架视图view的比较标签用法分析)
- dedecms标签语法(DEDECMS文档关键词关联文章BUG的修正方法)
- mysql行级锁使用教程(浅析MySQL的lru链表)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9