高效能 MySQL 分頁

Posted on  May 29, 2016  in  MySQL  by  Amo Chen  ‐ 2 min read

剛開始學寫程式時,是照著書本上的範例利用 LIMIT offset, row_count 來達到分頁的功能,但是隨著資料量越來越大,這種分頁方式會造成越後面的頁數 query 時間越長。因為在 MySQL 中有指定 offset 的情況下,並不會直接從該 offset 開始進行資料查詢,而是依照 where 條件取得所有資料之後,再從 offset 開始取出所需要的資料筆數。

也就是說,假設在滿足 where 條件有 100 萬個結果時,如果要只取出最後 1 千筆結果,用 LIMIT 999000, 1000 的話, MySQL 仍然會先找出 100 萬筆後,再從第 999000 筆開始取出最後 1 千筆結果,造成資料越多查詢速度越慢。

比較好的方式不指定 offset ,只指定 limit 限制資料筆數。

例如:

LIMIT 1000

Offset 的部分則是想辦法利用 Unique Key, Primary Key 等條件找到需要的資料,搭配 ORDER BY 確保分頁的正確性。如此一來,就會先從找到的資料後開始取出所需要的資料筆數。

以使用者資料為例(user_account 是 PK),假設上一頁的使用者資料的最後一筆是 brandy ,那麼下一頁就可以從 brandy 開始查起。

SELECT
    user_account,
    first_name,
    last_name
FROM
    user
WHERE
    user_account > 'brandy'
ORDER BY
    user_account ASC
LIMIT
    1000

LIMIT offset, row_countLIMIT row_count 2 者的差異可以用 EXPLAIN 來比較,就可以看到差異。

改善前:

EXPLAIN SELECT
    user_account,
    first_name,
    last_name
FROM
    user
ORDER BY
    user_account ASC
LIMIT
    999000, 1000;

+----+-------------+--------------+-------+---------------+---------+---------+------+---------+-------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows    | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+------+---------+-------+
|  1 | SIMPLE      | user_account | index | NULL          | PRIMARY | 100     | NULL | 1000000 | NULL  |
+----+-------------+--------------+-------+---------------+---------+---------+------+---------+-------+

看得出來雖然有指定 offset MySQL 還是找了 1000000 筆。

改善後:

EXPLAIN SELECT
    user_account,
    first_name,
    last_name
FROM
    user
WHERE
    user_account > 'GeorgeMartin'
ORDER BY
    user_account ASC
LIMIT
    1000;

+----+-------------+--------------+-------+---------------+---------------+---------+------+--------+-----------------------+
| id | select_type | table        | type  | possible_keys | key           | key_len | ref  | rows   | Extra                 |
+----+-------------+--------------+-------+---------------+---------------+---------+------+--------+-----------------------+
|  1 | SIMPLE      | user_account | range | user_account  | user_account  | 100     | NULL | 300021 | Using index condition |
+----+-------------+--------------+-------+---------------+---------------+---------+------+--------+-----------------------+

上述的 explain 結果中的 rows 明顯下降(該值只是預估值,並非真正的查詢結果數),代表 MySQL 不再需要先找出所有資料後再取出所需要的筆數。

以上就是比較高效能的 MySQL 分頁方式,如果想知道更詳細的話,可以看看這份簡報 Efficient Pagination Using MySQL

對抗久坐職業傷害

研究指出每天增加 2 小時坐著的時間,會增加大腸癌、心臟疾病、肺癌的風險,也造成肩頸、腰背疼痛等常見問題。

然而對抗這些問題,卻只需要工作時定期休息跟伸展身體即可!

你想輕鬆改變現狀嗎?試試看我們的 PomodoRoll 番茄鐘吧! PomodoRoll 番茄鐘會根據你所設定的專注時間,定期建議你 1 項辦公族適用的伸展運動,幫助你打敗久坐所帶來的傷害!

贊助我們的創作

看完這篇文章了嗎? 休息一下,喝杯咖啡吧!

如果你覺得 MyApollo 有讓你獲得實用的資訊,希望能看到更多的技術分享,邀請你贊助我們一杯咖啡,讓我們有更多的動力與精力繼續提供高品質的文章,感謝你的支持!