使用合理的分頁方式以提高分頁的效率

正如樓主所說，分頁查詢在我們的實際應用中非常普遍，也是最容易出問題的查詢場景。比如對於下面簡單的語句，一般想到的辦法是在name，age，register_time欄位上建立複合索引。這樣條件排序都能有效的利用到索引，效能迅速提升。

如上例子，當 LIMIT 子句變成 “LIMIT 100000, 50” 時，此時我們會發現，只取50條語句為何會變慢?

原因很簡單，MySQL並不知道第 100000條記錄從什麼地方開始，即使有索引也需要從頭計算一次，因此會感覺非常的慢。

通常，我們在做分頁查詢時，是可以獲取上一頁中的某個資料標誌來縮小查詢範圍的，比如時間，可以將上一頁的最大值時間作為查詢條件的一部分，SQL可以最佳化為這樣：

這個得明白mysql的儲存原理 講個例子 一個小區有很多棟 一棟有很多層 一層有很多室 你哪個小區哪一棟那一層都不告訴它 它能一下找到嗎 雖然它也想快點幫你找到

個人實戰經驗分享一下，商品表，資料量還是相對較大的，有好幾百萬。當時最初架構也是遇到這種問題，因為最初設計的時候沒想到會有這麼大資料量，也就應對10萬以內的架構設計。那後來也是透過不斷找尋方案，最終採用了一種橋連線表的方案。主表是商品表，幾百萬或者上千萬商品。

第一步，建立橋資料表，一個自增ID，一個商品ID，主要這倆欄位，額外排序條件也可以加進去，均int型別，不宜過大。這個表就是存商品ID用的。

第二步，查詢分頁的時候，先在這個橋表做分頁查詢，表小，都是索引，速度非常快，然後取出商品ID後，再用已知商品ID對商品表做in查詢。查出具體資訊。這樣速度提升巨大。簡單實用，僅需對分頁部分做查詢修改即可完成。

目前三五百萬商品表，都是毫秒級查詢，沒改造之前需要半分鐘。

再說一句是MySQL資料庫，有的說什麼分表分庫，那都沒必要，用in條件查詢，那是最快的，直接告訴在哪，取數就行了。

題主給的這個sql其實想要的資料也就20條吧(你那個300020應該是打錯了，不可能是實際業務一頁顯示30多萬條記錄)，單純查三十多萬資料其實很快，為什麼分頁後就很慢？

變慢的原因，一方面是select *，另一方面是資料量較大，還有一個是帶有排序操作。本質是分頁查詢時，會先查詢出limit + offset條記錄，然後擷取後面的offset記錄。

Mysql資料庫作為一款比較主流的開源關係型資料庫，市場上我覺得貌似開發者沒有一個沒用過吧。

影響MySQL查詢效能的因素有很多，比如sql，表結構設計，磁碟io，網絡卡io，高併發，資料庫相關引數配置，還有伺服器硬等。

這裡面涉及最多也是面試中最常問的就是有關sql的最佳化。

因為很多效能上的問題來自sql的比較多，mysql資料庫在資料量級達到百萬以上效能是逐漸下降的。

關於sql最佳化又有很多最佳化的方向和手段。比如對錶結構的欄位型別，預設值，索引等最基礎的做一些最佳化，然後編寫的sql最好要能完全命中索引。

當然並不是說建索引就一定命中，不走索引就一定慢。這取決於mysql的執行計劃。

還有建索引也並不是越多越好，單表索引最好不要超過6個，畢竟索引也佔空間，資料更新的同時，還牽扯到索引檔案的維護。

OK說了這麼多，到底該怎麼對這個分頁又排序做最佳化呢？

我的做法就是合理利用主鍵索引來處理

select a.* from table a inner join (select id from table

limit 300000,20）

b on a.id=b.id;

然後排序最好放到程式碼層面上去。

很多應用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導航欄。然而，如何透過MySQL更好的實現分頁，始終是比較令人頭疼的問題。雖然沒有拿來就能用的解決辦法，但瞭解資料庫的底層或多或少有助於最佳化分頁查詢。

我們先從一個常用但效能很差的查詢來看一看。

SELECT *

FROM city

ORDER BY id DESC

LIMIT 0, 15

這個查詢耗時0.00sec。So，這個查詢有什麼問題呢？實際上，這個查詢語句和引數都沒有問題，因為它用到了下面表的主鍵，而且只讀取15條記錄。

CREATE TABLE city (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

city varchar(128) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

真正的問題在於offset(分頁偏移量)很大的時候，像下面這樣：

SELECT *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

上面的查詢在有2M行記錄時需要0.22sec，透過EXPLAIN檢視SQL的執行計劃可以發現該SQL檢索了100015行，但最後只需要15行。大的分頁偏移量會增加使用的資料，MySQL會將大量最終不會使用的資料載入到記憶體中。就算我們假設大部分網站的使用者只訪問前幾頁資料，但少量的大的分頁偏移量的請求也會對整個系統造成危害。Facebook意識到了這一點，但Facebook並沒有為了每秒可以處理更多的請求而去最佳化資料庫，而是將重心放在將請求響應時間的方差變小。

對於分頁請求，還有一個資訊也很重要，就是總共的記錄數。我們可以透過下面的查詢很容易的獲取總的記錄數。

SELECT COUNT(*)

FROM city;

然而，上面的SQL在採用InnoDB為儲存引擎時需要耗費9.28sec。一個不正確的最佳化是採用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能夠在分頁查詢時事先準備好符合條件的記錄數，隨後只要執行一句 select FOUND_ROWS(); 就能獲得總記錄數。但是在大多數情況下，查詢語句簡短並不意味著效能的提高。不幸的是，這種分頁查詢方式在許多主流框架中都有用到，下面看看這個語句的查詢效能。

SELECT SQL_CALC_FOUND_ROWS *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

這個語句耗時20.02sec，是上一個的兩倍。事實證明使用 SQL_CALC_FOUND_ROWS 做分頁是很糟糕的想法。

下面來看看到底如何最佳化。文章分為兩部分，第一部分是如何獲取記錄的總數目，第二部分是獲取真正的記錄。

高效的計算行數

如果採用的引擎是MyISAM，可以直接執行COUNT(*)去獲取行數即可。相似的，在堆表中也會將行數儲存到表的元資訊中。但如果引擎是InnoDB情況就會複雜一些，因為InnoDB不儲存表的具體行數。

SELECT COUNT(*)

FROM city

USE INDEX(PRIMARY);

獲取記錄

下面進入這篇文章最重要的部分，獲取分頁要展示的記錄。上面已經說過了，大的偏移量會影響效能，所以我們要重寫查詢語句。為了演示，我們建立一個新的表“news”，按照時事性排序(最新發布的在最前面)，實現一個高效能的分頁。為了簡單，我們就假設最新發布的新聞的Id也是最大的。

CREATE TABLE news(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(128) NOT NULL

) ENGINE=InnoDB;

一個比較高效的方式是基於使用者展示的最後一個新聞Id。查詢下一頁的語句如下，需要傳入當前頁面展示的最後一個Id。

SELECT *

FROM news WHERE id < $last_id

ORDER BY id DESC

LIMIT $perpage

查詢上一頁的語句類似，只不過需要傳入當前頁的第一個Id，並且要逆序。

SELECT *

FROM news WHERE id > $last_id

ORDER BY id ASC

LIMIT $perpage

SELECT id

FROM (

SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt

FROM news

JOIN (SELECT @cnt:= 0)T

WHERE id < $last_id

ORDER BY id DESC

LIMIT $perpage * $buttons

)C

WHERE cnt = 0;

透過上面的語句可以為每一個分頁的按鈕計算出一個offset對應的id。這種方法還有一個好處。假設，網站上正在釋出一片新的文章，那麼所有文章的位置都會往後移一位，所以如果使用者在釋出文章時換頁，那麼他會看見一篇文章兩次。如果固定了每個按鈕的offset Id，這個問題就迎刃而解了。Mark Callaghan發表過一篇類似的部落格，利用了組合索引和兩個位置變數，但是基本思想是一致的。

SET p:= 0;

UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;

當然，也可以新增一個專用於分頁的表，可以用個後臺程式來維護。

UPDATE pagination T

JOIN (

SELECT id, CEIL((p:= p + 1) / $perpage) page

FROM news

ORDER BY id

)C

ON C.id = T.id

SET T.page = C.page;

現在想獲取任意一頁的元素就很簡單了：

SELECT *

FROM news A

JOIN pagination B ON A.id=B.ID

WHERE page=$offset;

還有另外一種與上種方法比較相似的方法來做分頁，這種方式比較試用於資料集相對小，並且沒有可用的索引的情況下—比如處理搜尋結果時。在一個普通的伺服器上執行下面的查詢，當有2M條記錄時，要耗費2sec左右。這種方式比較簡單，建立一個用來儲存所有Id的臨時表即可(這也是最耗費效能的地方)。

CREATE TEMPORARY TABLE _tmp (KEY SORT(random))

SELECT id, FLOOR(RAND() * 0x8000000) random

FROM city;

ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;

接下來就可以向下面一樣執行分頁查詢了。

SELECT *

FROM _tmp

WHERE OFFSET >= $offset

ORDER BY OFFSET

LIMIT $perpage;

簡單來說，對於分頁的最佳化就是。。。避免資料量大時掃描過多的記錄。