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;

简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。

以上就是MySQL分页性能优化指南的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！