MySQL 的分頁查詢優(yōu)化策略解析
千萬級(jí)別的MySQL單表查詢算是近幾年面試時(shí)碰到的一道比較棘手的問題，因?yàn)楹芏嚅_發(fā)沒有這方面的經(jīng)驗(yàn)，所以最終回答都不是很好，所以筆者就以MySQL8作為實(shí)驗(yàn)數(shù)據(jù)庫為讀者演示一下筆者日常的處理思路和技巧。
百萬級(jí)別數(shù)據(jù)查詢實(shí)踐
前置準(zhǔn)備
為了方便演示筆者，這里拿出一張?jiān)?jīng)作為批量插入的數(shù)據(jù)表，該表差不多有1000w左右的數(shù)據(jù):
CREATE TABLE `batch_insert_test` (
`id` int NOT NULL AUTO_INCREMENT,
`fileid_1` varchar(100) DEFAULT NULL,
`fileid_2` varchar(100) DEFAULT NULL,
`fileid_3` varchar(100) DEFAULT NULL,
`fileid_4` varchar(100) DEFAULT NULL,
`fileid_5` varchar(100) DEFAULT NULL,
`fileid_6` varchar(100) DEFAULT NULL,
`fileid_7` varchar(100) DEFAULT NULL,
`fileid_8` varchar(100) DEFAULT NULL,
`fileid_9` varchar(100) DEFAULT NULL,
`fileid_10` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25414 DEFAULT CHARSET=utf8mb3 COMMENT='測試批量插入，一行數(shù)據(jù)1k左右';如何limit
按照分頁查詢公式，查詢第N頁的sql就是limit (page-1)*size, size，所以筆者對(duì)如下幾個(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即針對(duì)整顆聚簇索引樹的非空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| |所以我們需要對(duì)這些SQL進(jìn)行改造，因?yàn)楣P者這張表是以id作為主鍵的，所以我們可以很好的利用這一點(diǎn)，通過定位當(dāng)前頁的第一個(gè)id，然后通過這個(gè)id篩選對(duì)應(yīng)頁的數(shù)據(jù)：
對(duì)應(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
inner join (
select
id
from
batch_insert_test
limit 5000000,
10) as b2 on
b1.id = b2.id;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;其他注意事項(xiàng)
還有一點(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 bit2
where
id >(select id from batch_insert_test bit2 limit 5000000,1)
limit 10;小結(jié)
來簡單小結(jié)一下，本文通過一張大表結(jié)合一個(gè)分頁查詢的場景為讀者演示的大表分頁查詢的技巧，整體來說，針對(duì)大表查詢時(shí)，我們的SQL優(yōu)化要遵循以下幾點(diǎn)：
- 盡可能利用索引，確保用最小的開銷得到索引。
- 結(jié)合業(yè)務(wù)場景和服務(wù)器性能壓測出最合適的limit數(shù)據(jù)量。
- 盡量不要查詢沒必要的列。
