【基礎知識思考整理 】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