MySQL 分頁查詢優(yōu)化指南
本文以MySQL8為例演示一下分頁查詢技巧和常見優(yōu)化思路，希望對你有幫助。
一、業(yè)務(wù)妥協(xié)向的非跳頁查詢
該問題實(shí)際上有兩種比較常見的方案，一種是search_after上下翻頁查詢，如方案名所說，當(dāng)用戶進(jìn)行上下翻頁的時(shí)候，永遠(yuǎn)都是基于本次分頁的結(jié)果的區(qū)間定位上一頁和上一頁，這也就意味著該查詢必須要求用戶的數(shù)據(jù)必須具備有序的字段，例如我們當(dāng)前查詢到id為20~30的數(shù)據(jù)，基于該方案我們獲取下一頁的數(shù)據(jù)就是找到大于30的前10條數(shù)據(jù)：
對應(yīng)的我們也給出這條SQL示例：
-- 下一頁（假設(shè)id為主鍵且連續(xù)）
SELECT * FROM table
WHERE id > 30
ORDER BY id
LIMIT 10;而查詢上一頁也是同理，通過當(dāng)前頁碼的最小值，定位到上一頁的最大值，從而獲取上一頁的結(jié)果區(qū)間：
圖片
對應(yīng)的我們也給出這段SQL示例：
-- 上一頁（需要前端記錄歷史游標(biāo)）
SELECT * FROM table
WHERE id < 20
ORDER BY id DESC
LIMIT 10;二、支持跳頁的分頁查詢SQL
1. 準(zhǔn)備測試數(shù)據(jù)和腳本
為了方便演示筆者，這里拿出一張?jiān)?jīng)作為批量插入的數(shù)據(jù)表，該表差不多有200w左右的數(shù)據(jù):
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='測試批量插入，一行數(shù)據(jù)1k左右';對應(yīng)的我們也給出批量插入模擬數(shù)據(jù)的腳本：
-- 創(chuàng)建臨時(shí)存儲(chǔ)過程執(zhí)行批量插入
DELIMITER //
CREATEPROCEDURE batch_insert_data()
BEGIN
DECLARE i INTDEFAULT0;
DECLARE batch_count INTDEFAULT1000; -- 每批插入1000條
DECLARE total_rows INTDEFAULT20000000; -- 總插入量200w
-- 顯示開始時(shí)間
SELECTCONCAT('開始批量插入數(shù)據(jù): ', NOW()) AS message;
-- 使用事務(wù)提高性能
STARTTRANSACTION;
WHILE i < total_rows DO
-- 構(gòu)建批量插入語句
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 ';
-- 生成當(dāng)前批次的1000條數(shù)據(jù)
SET @batch_values = '';
SET @j = 0;
WHILE @j < batch_count AND i < total_rows DO
-- 生成隨機(jī)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;
-- 執(zhí)行批量插入
SET @sql = CONCAT(@insert_sql, @batch_values);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;
-- 每插入10萬條顯示進(jìn)度
IF i % 100000 = 0 THEN
SELECTCONCAT('已插入: ', i, ' 條記錄, 進(jìn)度: ', ROUND(i/total_rows*100, 2), '%, 時(shí)間: ', NOW()) AS progress;
ENDIF;
ENDWHILE;
COMMIT;
-- 顯示完成時(shí)間
SELECTCONCAT('批量插入完成! 總插入量: ', i, ' 條, 結(jié)束時(shí)間: ', NOW()) AS message;
END//
DELIMITER ;
-- 執(zhí)行存儲(chǔ)過程
CALL batch_insert_data();
-- 刪除臨時(shí)存儲(chǔ)過程
DROPPROCEDUREIFEXISTS batch_insert_data;2. 如何limit檢索
按照分頁查詢公式，查詢第N頁的sql就是limit (page-1)*size, size，所以筆者對如下幾個(gè)分頁查詢進(jìn)行實(shí)驗(yàn)，不難看出，隨著分頁深度的增加，查詢也變得十分耗時(shí)：
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頁的數(shù)據(jù)10條，花費(fèi)了將近10s：
select * from batch_insert_test limit 5000000,10;因?yàn)椴樵儠r(shí)沒有使用任何索引，所以查詢時(shí)直接進(jìn)行完整的table scan即針對整顆聚簇索引樹的非空data域進(jìn)行掃描檢索：
查看其執(zhí)行計(jì)劃，可以發(fā)現(xiàn)本次查詢走了全表掃描，性能表現(xiàn)非常差勁:
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進(jìn)行改造，因?yàn)楣P者這張表是以有序自增id作為主鍵的，所以我們可以很好的利用這一點(diǎn)，通過定位當(dāng)前頁的第一個(gè)id，然后通過這個(gè)id篩選對應(yīng)頁的數(shù)據(jù)：
對應(yīng)SQL如下所示，經(jīng)過筆者的實(shí)驗(yàn)耗時(shí)大約在500ms左右：
select
*
from
batch_insert_test
where
id >=(select id from batch_insert_test bit2 limit 5000000,1)
limit 10;查看這條sql的執(zhí)行計(jì)劃可以發(fā)現(xiàn)，這條sql是直接通過索引直接定位id，避免走向葉子節(jié)點(diǎn)直接返回，再通過走索引的方式進(jìn)行范圍查詢性能提升了不少。
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 |當(dāng)然，我們也可以通過子查詢的方式先定位到索引區(qū)間，然后再和查詢的表進(jìn)行關(guān)聯(lián)完成檢索，性能表現(xiàn)也差不多，這里不多做贅述了：
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數(shù)據(jù)量的選擇了，有些讀者可能為了方便直接在業(yè)務(wù)上進(jìn)行改造，一次性查詢大幾十萬數(shù)據(jù)給用戶。 可以看到隨著數(shù)據(jù)量的增加，查詢耗時(shí)主鍵增大，所以讀者在進(jìn)行這方面考慮的時(shí)候務(wù)必要結(jié)合壓測，根據(jù)自己業(yè)務(wù)上所能容忍的延遲涉及最大的pageSize，以筆者為例大約10w條以內(nèi)的數(shù)據(jù)查詢性能差異是不大的(上下相差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. 減少查詢的字段
還有一點(diǎn)細(xì)節(jié)上的優(yōu)化，MySQL的基本單位是頁，所以每次查詢都是以頁為單位進(jìn)行查詢，所以高效的查詢也要求我們用盡可能少的塊查到存儲(chǔ)盡可能多的數(shù)據(jù)，所以查詢時(shí)我們建議沒有用到的列就不要查詢來了。
以筆者為例，只需用到3個(gè)字段，則直接將*改為了id,fileid_1 ,fileid_4
select
id,fileid_1 ,fileid_4
from
batch_insert_test bit25. 利用索引覆蓋
延遲關(guān)聯(lián)查詢法在若帶有通過其它字段進(jìn)行分頁查詢或者排序時(shí)，我們務(wù)必針對該字段創(chuàng)建一個(gè)索引，假設(shè)我們要查詢19001頁的數(shù)據(jù)，對應(yīng)的SQL如下所示：
select
id,fileid_1 ,fileid_4,fileid_8
from
batch_insert_test
order by fileid_8 limit 190000,10;假設(shè)分頁查詢有一個(gè)limit_count記錄分頁偏移量，如果file_8沒有創(chuàng)建索引，這條查詢的執(zhí)行過程為：
- 進(jìn)行全表掃描，并基于filesort完成數(shù)據(jù)排序
- 基于排序結(jié)果掃描到第一條符合要求的數(shù)據(jù)返回給server層。
- 此時(shí)server層發(fā)現(xiàn)limit_count為0，即沒有完成跳躍篩選的工作，故舍棄這條記錄，limit_count++。
- 重復(fù)步驟2執(zhí)行190000次。
- 步驟4完成后，返回10條完整的記錄給客戶端。
因?yàn)樯婕拔募判蚝腿頀呙瑁赃@條SQL的查詢表現(xiàn)比較差勁，查詢耗時(shí)為1m39s ，對應(yīng)的我們也給出相應(yīng)的執(zhí)行計(jì)劃印證：
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|所以在此基礎(chǔ)上，我們會(huì)考慮在fileid_8 上增加一個(gè)索引，為后續(xù)的優(yōu)化做鋪墊：
CREATE INDEX batch_insert_test_fileid_8_IDX USING BTREE ON db.batch_insert_test (fileid_8);有了索引之后，使用二級索引進(jìn)行排序，查詢耗時(shí)變?yōu)?00ms，但這還不夠，原因很簡單，通過下述的SQL很好的利用二級索引完成排序，但是檢索數(shù)據(jù)時(shí)整體過程還是：
- 基于二級索引完成排序
- 基于排序結(jié)果掃描到第一條符合要求，通過回表定位到完整的數(shù)據(jù)返回給server層
- 此時(shí)server層發(fā)現(xiàn)limit_count為0，即沒有完成跳躍篩選的工作，故舍棄這條記錄，limit_count++。
- 重復(fù)步驟2和步驟3執(zhí)行190000次。
- 步驟4完成后，返回10條完整的記錄給客戶端。
因?yàn)槎壦饕齜+樹記錄的是索引和主鍵的映射，若需要投影其它字段，還需要經(jīng)過回表這一步：
對應(yīng)的我們也給出執(zhí)行計(jì)劃：
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| |因?yàn)槲覀兓趂ileid_8創(chuàng)建了二級索引，所以我們可以借助MySQL中索引覆蓋的特性，在排序時(shí)通過掃描二級索引定位到主鍵索引區(qū)間，并基于這個(gè)主鍵區(qū)間一次性到聚簇索引樹上獲取所有數(shù)據(jù)，避免多次回表的開銷。
對應(yīng)的我們給出下面這條SQL，需要注意的是MySQL默認(rèn)語法不允許in直接和子查詢的select id 子句一起使用，若使用該語句則會(huì)拋出This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery';異常，所以筆者對應(yīng)的SQL上增加了將select id這個(gè)子查詢結(jié)果構(gòu)建成一張只有id的虛表t1和外部關(guān)聯(lián)：
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);對應(yīng)查詢結(jié)果一下子優(yōu)化至20ms，從執(zhí)行結(jié)果上可以看出：
- 子查詢直接Using index直接拿到主鍵，雖然評估掃描大約是190010，但是避免了回表，性能較為可觀。
- 主表batch_insert_test通過聚簇索引id和被驅(qū)動(dòng)表t1直接關(guān)聯(lián)，快速得到數(shù)據(jù)。
三、小結(jié)
來簡單小結(jié)一下，本文通過一張大表結(jié)合一個(gè)分頁查詢的場景為讀者演示的大表分頁查詢的技巧，整體來說，針對大表查詢時(shí)，我們的SQL優(yōu)化要遵循以下幾點(diǎn)：
- 盡可能利用索引，確保用最小的開銷得到索引。
- 結(jié)合業(yè)務(wù)場景和服務(wù)器性能壓測出最合適的limit數(shù)據(jù)量。
- 盡量不要查詢沒必要的列。
- 利用好索引覆蓋避免回表的開銷。
