【基础知识思考整理 】Mysql高效率的分页查询

基础知识思考整理
http://write.blog.csdn.net/mdeditor#!postId=52836140

在工程实践中，数据库表中的数据量一般不超过 100W行。当我们想要查询数据表中满足条件的第 n个100 行数据的时候，怎样的查询方法才能保证较高的性能呢？

首先想到的，一般分页都是用 “limit 起始, 行数” 这样的方式。

上面的含义是：即使我们只要取得从第 2个开始的3 行数据，也要扫描前面的 5行并且丢弃前面的2行最终返回后面的 3行。
可以想象，当起始非常大而行数非常小的时候，我们的查询效率是非常低的。在一个高并发的应用里，每次查询需要扫描超过 10W行，性能肯定大打折扣。

雅虎工程师在 Efficient Pagination Using MySQL 中提到一种做法：
比如还是 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页：
SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 0,20//20
跳到第 8页：(10-8)*20 取最后20条
SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 20,20//40
跳到第 7页：(10-7)*20 取最后20条
SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 40,20//60
跳到第 11页：
SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 0,20//20
跳到第 12页：(12-10)*20 取最后20条
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

注意 SQL语句里面的ASC 和DESC，如果是 ASC取出来的结果，显示的时候记得倒置一下。

原理还是一样，记录住当前页 id的最大值和最小值，计算跳转页面和当前页相对偏移，由于页面相近，这个偏移量不会很大，这样的话 m值相对较小，大大减少扫描的行数。

[1] http://www.jb51.net/article/46015.htm
[2]https://www.percona.com/files/presentations/ppc2009/PPC2009_mysql_pagination.pdf