mysql分页原理和高效率的mysql分页查询语句
发布时间:2024-01-22 00:07:39
以前我在mysql中分页都是用的 limit 100000,20这样的方式,我相信你也是吧,但是要提高效率,让分页的代码效率更高一些,更快一些,那我们又该怎么做呢?
第一部分:看一下分页的基本原理:
mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20
***************** 1. row **************
id: 1
select_type: SIMPLE
table: message
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 10020
Extra:
1 row in set (0.00 sec)
对上面的mysql语句说明:limit 10000,20的意思扫描满足条件的10020行,扔掉前面的10000行,返回最后的20行,问题就在这里,如果是limit 100000,100,需要扫描100100行,在一个高并发的应用里,每次查询需要扫描超过10W行,性能肯定大打折扣。文中还提到limit n性能是没问题的,因为只扫描n行。
第二部分:根据雅虎的几位工程师带来了一篇Efficient Pagination Using MySQL的报告内容扩展:在文中提到一种clue的做法,给翻页提供一些线索,比如还是SELECT * FROM message ORDER BY id DESC,按id降序分页,每页20条,当前是第10页,当前页条目id最大的是1020,最小的是1000,如果我们只提供上一页、下一页这样的跳转(不提供到第N页的跳转),那么在处理上一页的时候SQL语句可以是:
SELECT * FROM message WHERE id>1020 ORDER BY id ASC LIMIT 20;//下一页
处理下一页的时候SQL语句可以是:
SELECT * FROM message WHERE id<1000 ORDER BY id DESC LIMIT 20;//上一页
不管翻多少页,每次查询只扫描20行。
缺点是只能提供上一页、下一页的链接形式,但是我们的产品经理非常喜欢“上一页 1 2 3 4 5 6 7 8 9 下一页”这样的链接方式,怎么办呢?
如果LIMIT m,n不可避免的话,要优化效率,只有尽可能的让m小一下,我们扩展前面的clue做法,还是SELECT * FROM message ORDER BY id DESC,按id降序分页,每页20条,当前是第10页,当前页条目id最大的是2519,最小的是2500;
当是第10页的SQL如下:
SELECT * FROM tb_goods_info WHERE auto_id >=2500 ORDER BY auto_id ASC LIMIT 0,20
比如要跳到第9页,SQL语句可以这样写:
SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 0,20
比如要跳到第8页,SQL语句可以这样写:
SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 20,20
比如要跳到第7页,SQL语句可以这样写:
SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 40,20
跳转到第11页:
SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 0,20
跳转到第12页:
SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 20,20
跳转到第13页:
SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 40,20
原理还是一样,记录住当前页id的最大值和最小值,计算跳转页面和当前页相对偏移,由于页面相近,这个偏移量不会很大,这样的话m值相对较小,大大减少扫描的行数。其实传统的limit m,n,相对的偏移一直是第一页,这样的话越翻到后面,效率越差,而上面给出的方法就没有这样的问题。
注意SQL语句里面的ASC和DESC,如果是ASC取出来的结果,显示的时候记得倒置一下。
已在60W数据总量的表中测试,效果非常明显
猜你喜欢
- 一、字典、元组的多重嵌套例 1:记录全班学生的成绩。分析:定义一个 SimpleGradebook类,学生名是字典self._grades的
- Shapely是一个Python库,用于操作和分析笛卡尔坐标系中的几何对象。引入包from shapely.geometry import
- 折纸是日本著名的折叠纸张的艺术。折纸艺术只是使用一些不同的折叠方式,却能被用各种各样的方式组合成错综复杂的设计。而受折纸启发的logo设计则
- 1,ConfigParser模块简介假设有如下配置文件,需要在Pyhton程序中读取$ cat config.ini[db]db_port
- 定位色块常用到hsv色彩空间下的颜色阈值,笔者曾经用openmv时,其IDE有自带一个阈值编辑器,使用起来非常方便,现在在linux上跑cv
- 一般写ASP PHP代码的朋友都估计是采用直接操作SQL的吧~ 看以下的代码 <% dim conn,rs&nbs
- Python中的单元测试我们先来回顾一下Python中的单元测试方法。下面是一个 Python的单元测试简单的例子:假如我们开发
- 代码如下:< % '功能:显示数据库中表名、字段名、字段内容 '原创:wangsdong
- 本文实例讲述了Python实现监控Nginx配置文件的不同并发送邮件报警功能。分享给大家供大家参考,具体如下:因为项目中经常涉及到多个Ngi
- 最近在使用Tensorflow 实现DNN网络时,遇到一些问题。目前网上关于Tensorflow的资料还比较少,现把问题和解决方法写出来,仅
- 本文实例讲述了Django框架用户注销功能实现方法。分享给大家供大家参考,具体如下:HttpResponse()里有个delete_cook
- sql server 全文检索有两种搜索方式,一种是contains,另一种是freetext。前者是包含,类似于 like '%关
- 当感觉mysql性能出现问题时,通常会先看下当前mysql的执行状态,使用 show processlist 来查看,例如:其中state状
- 目录多线程(连接池)操作MySQL插入数据1.主要模块2.创建连接池3.数据预处理4.线程任务5.启动多线程6.完整示例7.思考/总结多线程
- 需求是:针对三篇英文文章进行分析,计算出现次数最多的 10 个单词逻辑很清晰简单,不算难, 使用 python 读取多个 txt 文件,将文
- 前言事情是这样的马上就快到毕业季了,大四的学姐们快要离校了你心中那个没有说出口的学姐,你还记得吗跟着博主,用pygame给你心中那个学姐做一
- 一、为什么要安装虚拟环境 情景一、项目A需要某个库的1.0版本,项目B需要这个库的2.0版本。如果没有安装虚拟环境
- 本文记录了windows下python的安装,供大家参考,具体内容如下—–因为我是个真小白,网上的大多入门教程并不适合我这种超级超级小白,有
- 这个程序的功能非常的简单,就是每天在系统中新建一个文件夹。文件夹即当前的时间。此代码是在同事那边看到的,为了锻炼下自己薄弱的Python能力
- 启动sql server Net Start MSSqlServer 暂停sql server Net Pause MSSqlServer