mysql分页原理和⾼效率的mysql分页查询语句
以前我在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语句可以是：
复制代码代码如下:
简单的mysql语句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数据总量的表中测试，效果⾮常明显
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论