一般情況下,用Typecho或者Wordpress做博客系統,數據量都不大,所以大家都很少遇到分頁導致的性能問題.然而,當數據量上升(譬如做大量產品資料的企業網站),MySQL的分頁查詢性能問題將越來越明顯.

如何通過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;

簡單來說,對于分頁的優化便是...避免數據量大時掃描過多的記錄.