在mysql中翻頁是不是有比LIMIT OFFSET更好的選擇

以前在數(shù)據(jù)中寫翻頁的代碼時，一直把sql寫成這樣

十載的永福網(wǎng)站建設(shè)經(jīng)驗，針對設(shè)計、前端、開發(fā)、售后、文案、推廣等六對一服務(wù)，響應(yīng)快，48小時及時工作處理。成都全網(wǎng)營銷的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動調(diào)整永福建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計，從而大程度地提升瀏覽體驗。創(chuàng)新互聯(lián)建站從事“永福網(wǎng)站設(shè)計”,“永福網(wǎng)站推廣”以來，每個客戶項目都認(rèn)真落實執(zhí)行。

SELECT * FROM table_name ORDER BY id ASC LIMIT 10 OFFSET 10

也就是用LIMIT和OFFSET來控制需要輸出的內(nèi)容區(qū)域，后來我在翻其他人代碼時看到了這種寫法

SELECT * FROM table_name WHERE id 10 ORDER BY id ASC LIMIT 10

這種寫法是否比前一種寫法的速度要更快，因我看到它使用id主鍵的索引，而且還把檢索區(qū)域用id 10縮小了，理論上應(yīng)該更快。但這種寫法應(yīng)該也有一定的局限性，比如最大的局限就是它只能用于那種類似timeline的，從第一頁往后翻的應(yīng)用場景，因為你要翻下一頁必須知道前一頁的最后偏移是多少。

不知道我這樣的分析是否正確，各位有什么樣的看法。

MySql中查詢語句實現(xiàn)分頁功能

pageNow代表當(dāng)前頁面，第一頁。

第一部分：看一下分頁的基本原理：

對上面的mysql語句說明：limit 10000,20的意思掃描滿足條件的10020行，扔掉前面的10000行，返回最后的20行，問題就在這里，如果是limit 100000,100，需要掃描100100行，在一個高并發(fā)的應(yīng)用里，每次查詢需要掃描超過10W行，性能肯定大打折扣。文中還提到limit n性能是沒問題的，因為只掃描n行。

第二部分：根據(jù)雅虎的幾位工程師帶來了一篇Efficient Pagination Using MySQL的報告內(nèi)容擴(kuò)展：在文中提到一種clue的做法，給翻頁提供一些線索，比如還是SELECT * FROM message ORDER BY id DESC，按id降序分頁，每頁20條，當(dāng)前是第10頁，當(dāng)前頁條目id最大的是1020，最小的是1000，如果我們只提供上一頁、下一頁這樣的跳轉(zhuǎn)（不提供到第N頁的跳轉(zhuǎn)），那么在處理上一頁的時候SQL語句可以是：

處理下一頁的時候SQL語句可以是：

不管翻多少頁，每次查詢只掃描20行。

缺點是只能提供上一頁、下一頁的鏈接形式，但是我們的產(chǎn)品經(jīng)理非常喜歡“上一頁 1 2 3 4 5 6 7 8 9 下一頁”這樣的鏈接方式，怎么辦呢？

如果LIMIT m,n不可避免的話，要優(yōu)化效率，只有盡可能的讓m小一下，我們擴(kuò)展前面的clue做法，還是SELECT * FROM message ORDER BY id DESC，按id降序分頁，每頁20條，當(dāng)前是第10頁，當(dāng)前頁條目id最大的是2519，最小的是2500;

當(dāng)是第10頁的SQL如下：

比如要跳到第9頁，SQL語句可以這樣寫：

比如要跳到第8頁，SQL語句可以這樣寫：

原理還是一樣，記錄住當(dāng)前頁id的最大值和最小值，計算跳轉(zhuǎn)頁面和當(dāng)前頁相對偏移，由于頁面相近，這個偏移量不會很大，這樣的話m值相對較小，大大減少掃描的行數(shù)。其實傳統(tǒng)的limit m,n，相對的偏移一直是第一頁，這樣的話越翻到后面，效率越差，而上面給出的方法就沒有這樣的問題。

mysql如何實現(xiàn)高效分頁

先看一下分頁的基本原理（我拿的是CSDN那個百萬級數(shù)據(jù)庫來測試?。篠ELECT * FROM `csdn` ORDER BY id DESC LIMIT 100000,2000;

耗時： 0.813ms分析：對上面的mysql語句說明：limit 100000,2000的意思掃描滿足條件的102000行，扔掉前面的100000行，返回最后的2000行。問題就在這里，如果是limit 100000,20000，需要掃描120000行，在一個高并發(fā)的應(yīng)用里，每次查詢需要掃描超過100000行，性能肯定大打折扣。在《efficient pagination using mysql》中提出的clue方式。利用clue方法，給翻頁提供一些線索，比如還是SELECT * FROM `csdn` order by id desc，按id降序分頁，每頁2000條，當(dāng)前是第50頁，當(dāng)前頁條目id最大的是102000，最小的是100000。如果我們只提供上一頁、下一頁這樣的跳轉(zhuǎn)（不提供到第N頁的跳轉(zhuǎn)）。那么在處理上一頁的時候SQL語句可以是：

SELECT * FROM `csdn` WHERE id=102000 ORDER BY id DESC LIMIT 2000; #上一頁

耗時：0.015ms處理下一頁的時候SQL語句可以是：

耗時：0.015ms這樣，不管翻多少頁，每次查詢只掃描20行。效率大大提高了！但是，這樣分頁的缺點是只能提供上一頁、下一頁的鏈接形式。