MySQL 的分頁查詢優化策略解析

作者：寫代碼的SharkChili 2025-01-15 12:48:30

本文通過一張大表結合一個分頁查詢的場景為讀者演示的大表分頁查詢的技巧，整體來說，針對大表查詢時，我們的SQL優化要遵循以下幾點。

千萬級別的MySQL單表查詢算是近幾年面試時碰到的一道比較棘手的問題，因為很多開發沒有這方面的經驗，所以最終回答都不是很好，所以筆者就以MySQL8作為實驗數據庫為讀者演示一下筆者日常的處理思路和技巧。

百萬級別數據查詢實踐

前置準備

為了方便演示筆者，這里拿出一張曾經作為批量插入的數據表，該表差不多有1000w左右的數據:

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='測試批量插入，一行數據1k左右';

如何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
inner join (
 select
  id
 from
  batch_insert_test
 limit 5000000,
 10) as b2 on
 b1.id = b2.id;

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;

其他注意事項

還有一點細節上的優化，MySQL的基本單位是頁，所以每次查詢都是以頁為單位進行查詢，所以高效的查詢也要求我們用盡可能少的塊查到存儲盡可能多的數據，所以查詢時我們建議沒有用到的列就不要查詢來了。

以筆者為例，只需用到3個字段，則直接將*改為了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;

小結

來簡單小結一下，本文通過一張大表結合一個分頁查詢的場景為讀者演示的大表分頁查詢的技巧，整體來說，針對大表查詢時，我們的SQL優化要遵循以下幾點：

盡可能利用索引，確保用最小的開銷得到索引。
結合業務場景和服務器性能壓測出最合適的limit數據量。
盡量不要查詢沒必要的列。

責任編輯：趙寧寧來源：寫代碼的SharkChili