MySQL 分頁查詢優化指南
本文以MySQL8為例演示一下分頁查詢技巧和常見優化思路，希望對你有幫助。
一、業務妥協向的非跳頁查詢
該問題實際上有兩種比較常見的方案，一種是search_after上下翻頁查詢，如方案名所說，當用戶進行上下翻頁的時候，永遠都是基于本次分頁的結果的區間定位上一頁和上一頁，這也就意味著該查詢必須要求用戶的數據必須具備有序的字段，例如我們當前查詢到id為20~30的數據，基于該方案我們獲取下一頁的數據就是找到大于30的前10條數據：
對應的我們也給出這條SQL示例：
-- 下一頁（假設id為主鍵且連續）
SELECT * FROM table
WHERE id > 30
ORDER BY id
LIMIT 10;而查詢上一頁也是同理，通過當前頁碼的最小值，定位到上一頁的最大值，從而獲取上一頁的結果區間：
圖片
對應的我們也給出這段SQL示例：
-- 上一頁（需要前端記錄歷史游標）
SELECT * FROM table
WHERE id < 20
ORDER BY id DESC
LIMIT 10;二、支持跳頁的分頁查詢SQL
1. 準備測試數據和腳本
為了方便演示筆者，這里拿出一張曾經作為批量插入的數據表，該表差不多有200w左右的數據:
CREATE TABLE`batch_insert_test` (
`id`intNOTNULL AUTO_INCREMENT,
`fileid_1`varchar(100) DEFAULTNULL,
`fileid_2`varchar(100) DEFAULTNULL,
`fileid_3`varchar(100) DEFAULTNULL,
`fileid_4`varchar(100) DEFAULTNULL,
`fileid_5`varchar(100) DEFAULTNULL,
`fileid_6`varchar(100) DEFAULTNULL,
`fileid_7`varchar(100) DEFAULTNULL,
`fileid_8`varchar(100) DEFAULTNULL,
`fileid_9`varchar(100) DEFAULTNULL,
`fileid_10`varchar(100) DEFAULTNULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULTCHARSET=utf8mb3 COMMENT='測試批量插入，一行數據1k左右';對應的我們也給出批量插入模擬數據的腳本：
-- 創建臨時存儲過程執行批量插入
DELIMITER //
CREATEPROCEDURE batch_insert_data()
BEGIN
DECLARE i INTDEFAULT0;
DECLARE batch_count INTDEFAULT1000; -- 每批插入1000條
DECLARE total_rows INTDEFAULT20000000; -- 總插入量200w
-- 顯示開始時間
SELECTCONCAT('開始批量插入數據: ', NOW()) AS message;
-- 使用事務提高性能
STARTTRANSACTION;
WHILE i < total_rows DO
-- 構建批量插入語句
SET @insert_sql = 'INSERT INTO batch_insert_test (fileid_1, fileid_2, fileid_3, fileid_4, fileid_5, fileid_6, fileid_7, fileid_8, fileid_9, fileid_10) VALUES ';
-- 生成當前批次的1000條數據
SET @batch_values = '';
SET @j = 0;
WHILE @j < batch_count AND i < total_rows DO
-- 生成隨機UUID格式的fileid
SET @uuid = REPLACE(UUID(), '-', '');
-- 添加到批量值
IF @j > 0 THEN
SET @batch_values = CONCAT(@batch_values, ',');
ENDIF;
SET @batch_values = CONCAT(@batch_values,
'("', @uuid, '","', @uuid, '","', @uuid, '","', @uuid, '","', @uuid, '",',
'"', @uuid, '","', @uuid, '","', @uuid, '","', @uuid, '","', @uuid, '")');
SET @j = @j + 1;
SET i = i + 1;
ENDWHILE;
-- 執行批量插入
SET @sql = CONCAT(@insert_sql, @batch_values);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;
-- 每插入10萬條顯示進度
IF i % 100000 = 0 THEN
SELECTCONCAT('已插入: ', i, ' 條記錄, 進度: ', ROUND(i/total_rows*100, 2), '%, 時間: ', NOW()) AS progress;
ENDIF;
ENDWHILE;
COMMIT;
-- 顯示完成時間
SELECTCONCAT('批量插入完成! 總插入量: ', i, ' 條, 結束時間: ', NOW()) AS message;
END//
DELIMITER ;
-- 執行存儲過程
CALL batch_insert_data();
-- 刪除臨時存儲過程
DROPPROCEDUREIFEXISTS batch_insert_data;2. 如何limit檢索
按照分頁查詢公式，查詢第N頁的sql就是limit (page-1)*size, size，所以筆者對如下幾個分頁查詢進行實驗，不難看出，隨著分頁深度的增加，查詢也變得十分耗時：
select * from batch_insert_test bit2 limit 10,10;
select * from batch_insert_test bit2 limit 100,10;
select * from batch_insert_test bit2 limit 1000,10;
select * from batch_insert_test bit2 limit 10000,10;
select * from batch_insert_test bit2 limit 100000,10;
select * from batch_insert_test bit2 limit 1000000,10;
select * from batch_insert_test bit2 limit 5000000,10;查看第500w頁的數據10條，花費了將近10s：
select * from batch_insert_test limit 5000000,10;因為查詢時沒有使用任何索引，所以查詢時直接進行完整的table scan即針對整顆聚簇索引樹的非空data域進行掃描檢索：
查看其執行計劃，可以發現本次查詢走了全表掃描，性能表現非常差勁:
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra|
--+-----------+-----+----------+----+-------------+---+-------+---+-------+--------+-----+
1|SIMPLE |batch_insert_test | |ALL | | | | |9004073| 100.0| |所以我們需要對這些SQL進行改造，因為筆者這張表是以有序自增id作為主鍵的，所以我們可以很好的利用這一點，通過定位當前頁的第一個id，然后通過這個id篩選對應頁的數據：
對應SQL如下所示，經過筆者的實驗耗時大約在500ms左右：
select
*
from
batch_insert_test
where
id >=(select id from batch_insert_test bit2 limit 5000000,1)
limit 10;查看這條sql的執行計劃可以發現，這條sql是直接通過索引直接定位id，避免走向葉子節點直接返回，再通過走索引的方式進行范圍查詢性能提升了不少。
id|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows |filtered|Extra |
--+-----------+-----+----------+-----+-------------+-------+-------+---+-----+--------+------------------------------+
1|PRIMARY | | | | | | | | | |no matching row in const table|
2|SUBQUERY |bit2 | |index| |PRIMARY|4 | |38677| 100.0|Using index |當然，我們也可以通過子查詢的方式先定位到索引區間，然后再和查詢的表進行關聯完成檢索，性能表現也差不多，這里不多做贅述了：
select
b1.*
from
batch_insert_test b1
innerjoin (
select
id
from
batch_insert_test
limit5000000,
10) as b2 on
b1.id = b2.id;3. limit量級多少合適
接下來就是limit數據量的選擇了，有些讀者可能為了方便直接在業務上進行改造，一次性查詢大幾十萬數據給用戶。 可以看到隨著數據量的增加，查詢耗時主鍵增大，所以讀者在進行這方面考慮的時候務必要結合壓測，根據自己業務上所能容忍的延遲涉及最大的pageSize，以筆者為例大約10w條以內的數據查詢性能差異是不大的(上下相差200ms左右)：
select * from batch_insert_test bit2 limit 1000000,10;
select * from batch_insert_test bit2 limit 1000000,100;
select * from batch_insert_test bit2 limit 1000000,1000;
select * from batch_insert_test bit2 limit 1000000,10000;
select * from batch_insert_test bit2 limit 1000000,100000;
select * from batch_insert_test bit2 limit 1000000,1000000;
select * from batch_insert_test bit2 limit 1000000,10000000;4. 減少查詢的字段
還有一點細節上的優化，MySQL的基本單位是頁，所以每次查詢都是以頁為單位進行查詢，所以高效的查詢也要求我們用盡可能少的塊查到存儲盡可能多的數據，所以查詢時我們建議沒有用到的列就不要查詢來了。
以筆者為例，只需用到3個字段，則直接將*改為了id,fileid_1 ,fileid_4
select
id,fileid_1 ,fileid_4
from
batch_insert_test bit25. 利用索引覆蓋
延遲關聯查詢法在若帶有通過其它字段進行分頁查詢或者排序時，我們務必針對該字段創建一個索引，假設我們要查詢19001頁的數據，對應的SQL如下所示：
select
id,fileid_1 ,fileid_4,fileid_8
from
batch_insert_test
order by fileid_8 limit 190000,10;假設分頁查詢有一個limit_count記錄分頁偏移量，如果file_8沒有創建索引，這條查詢的執行過程為：
- 進行全表掃描，并基于filesort完成數據排序
- 基于排序結果掃描到第一條符合要求的數據返回給server層。
- 此時server層發現limit_count為0，即沒有完成跳躍篩選的工作，故舍棄這條記錄，limit_count++。
- 重復步驟2執行190000次。
- 步驟4完成后，返回10條完整的記錄給客戶端。
因為涉及文件排序和全表掃描，所以這條SQL的查詢表現比較差勁，查詢耗時為1m39s ，對應的我們也給出相應的執行計劃印證：
id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra |
--+-----------+-----------------+----------+----+-------------+---+-------+---+--------+--------+--------------+
1|SIMPLE |batch_insert_test| |ALL | | | | |19554981| 100.0|Using filesort|所以在此基礎上，我們會考慮在fileid_8 上增加一個索引，為后續的優化做鋪墊：
CREATE INDEX batch_insert_test_fileid_8_IDX USING BTREE ON db.batch_insert_test (fileid_8);有了索引之后，使用二級索引進行排序，查詢耗時變為400ms，但這還不夠，原因很簡單，通過下述的SQL很好的利用二級索引完成排序，但是檢索數據時整體過程還是：
- 基于二級索引完成排序
- 基于排序結果掃描到第一條符合要求，通過回表定位到完整的數據返回給server層
- 此時server層發現limit_count為0，即沒有完成跳躍篩選的工作，故舍棄這條記錄，limit_count++。
- 重復步驟2和步驟3執行190000次。
- 步驟4完成后，返回10條完整的記錄給客戶端。
因為二級索引b+樹記錄的是索引和主鍵的映射，若需要投影其它字段，還需要經過回表這一步：
對應的我們也給出執行計劃：
id|select_type|table |partitions|type |possible_keys|key |key_len|ref|rows |filtered|Extra|
--+-----------+-----------------+----------+-----+-------------+------------------------------+-------+---+------+--------+-----+
1|SIMPLE |batch_insert_test| |index| |batch_insert_test_fileid_8_IDX|303 | |190010| 100.0| |因為我們基于fileid_8創建了二級索引，所以我們可以借助MySQL中索引覆蓋的特性，在排序時通過掃描二級索引定位到主鍵索引區間，并基于這個主鍵區間一次性到聚簇索引樹上獲取所有數據，避免多次回表的開銷。
對應的我們給出下面這條SQL，需要注意的是MySQL默認語法不允許in直接和子查詢的select id 子句一起使用，若使用該語句則會拋出This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery';異常，所以筆者對應的SQL上增加了將select id這個子查詢結果構建成一張只有id的虛表t1和外部關聯：
select
id,
fileid_1 ,
fileid_4,
fileid_8
from
batch_insert_test
WHERE
idIN ( SELECT t1.id from ( selectidfrom batch_insert_test sub orderby fileid_8 limit190000,10) as t1);對應查詢結果一下子優化至20ms，從執行結果上可以看出：
- 子查詢直接Using index直接拿到主鍵，雖然評估掃描大約是190010，但是避免了回表，性能較為可觀。
- 主表batch_insert_test通過聚簇索引id和被驅動表t1直接關聯，快速得到數據。
三、小結
來簡單小結一下，本文通過一張大表結合一個分頁查詢的場景為讀者演示的大表分頁查詢的技巧，整體來說，針對大表查詢時，我們的SQL優化要遵循以下幾點：
- 盡可能利用索引，確保用最小的開銷得到索引。
- 結合業務場景和服務器性能壓測出最合適的limit數據量。
- 盡量不要查詢沒必要的列。
- 利用好索引覆蓋避免回表的開銷。
