引言

在日常業(yè)務(wù)開發(fā)中，分頁查詢是高頻操作，比如列表頁數(shù)據(jù)展示、歷史記錄查詢等。但當(dāng)數(shù)據(jù)量達(dá)到萬級以上時，普通的limit分頁往往會出現(xiàn)性能瓶頸。本文基于實際測試場景，詳細(xì)分析MySQL分頁查詢的執(zhí)行原理，并針對不同排序場景提供優(yōu)化方案，附完整測試代碼與執(zhí)行計劃對比。

一、測試環(huán)境搭建：模擬萬級數(shù)據(jù)量

為了更真實地復(fù)現(xiàn)分頁查詢問題，我們先創(chuàng)建測試表并插入10萬條測試數(shù)據(jù)，確保測試環(huán)境的一致性。

1.1 創(chuàng)建測試表

use martin;  -- 切換到目標(biāo)數(shù)據(jù)庫
drop table if exists t1;  -- 若表已存在則刪除

CREATE TABLE `t1` (            
  `id` int NOT NULL auto_increment,  -- 自增主鍵
  `a` int DEFAULT NULL,              -- 普通字段，用于非主鍵排序測試
  `b` int DEFAULT NULL,              -- 普通字段
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時間',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '記錄更新時間',
  PRIMARY KEY (`id`),  -- 主鍵索引
  KEY `idx_a` (`a`),   -- 為字段a創(chuàng)建普通索引，用于非主鍵排序優(yōu)化
  KEY `idx_b` (`b`)    -- 為字段b創(chuàng)建普通索引（備用）
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.2 批量插入測試數(shù)據(jù)

通過存儲過程批量插入10萬條數(shù)據(jù)，避免手動插入的繁瑣：

drop procedure if exists insert_t1;  -- 若存儲過程已存在則刪除
delimiter ;;  -- 修改語句結(jié)束符，避免與存儲過程內(nèi)的分號沖突
create procedure insert_t1()       
begin
  declare i int;                  
  set i=1;                        
  while(i<=100000)do  -- 插入10萬條數(shù)據(jù)
    insert into t1(a,b) values(i, i);  -- a、b字段值與自增id一致
    set i=i+1;                  
  end while;
end;;
delimiter ;  -- 恢復(fù)語句結(jié)束符為分號
call insert_t1();  -- 調(diào)用存儲過程插入數(shù)據(jù)

二、基礎(chǔ)分頁查詢：問題與執(zhí)行計劃分析

最常見的分頁查詢方式是使用limit offset, size，但當(dāng)offset（偏移量）較大時，性能會顯著下降。我們以“查詢第10001-10010條數(shù)據(jù)”為例，分析其執(zhí)行邏輯。

2.1 普通limit分頁SQL

-- 查詢a、b字段，跳過前10000條，取10條
select a,b from t1 limit 10000,10;

2.2 執(zhí)行計劃分析

通過explain查看SQL執(zhí)行計劃，關(guān)鍵信息如下（對應(yīng)測試截圖結(jié)果）：

• type：可能為ALL（全表掃描）或range（范圍掃描），取決于是否使用索引；
• key：若未命中索引，key字段為空，意味著需要掃描全表數(shù)據(jù)；
• rows：掃描行數(shù)接近10010行（需跳過前10000行，再取10行），數(shù)據(jù)量越大，掃描行數(shù)越多，性能越差。

核心問題：limit 10000,10會先掃描前10010條數(shù)據(jù)，再丟棄前10000條，僅返回最后10條，大量數(shù)據(jù)的“無效掃描”導(dǎo)致性能損耗。

三、優(yōu)化方案一：基于自增連續(xù)主鍵的分頁查詢

若分頁查詢基于自增且連續(xù)的主鍵排序（如按id升序），可通過“主鍵范圍查詢”替代limit offset，徹底避免無效數(shù)據(jù)掃描。

3.1 優(yōu)化后的SQL

-- 直接查詢id在10001-10010之間的數(shù)據(jù)，無需跳過前10000條
select a,b from t1 where id > 10000 and id <= 10010;

3.2 執(zhí)行計劃對比

再次使用explain分析優(yōu)化后的SQL，執(zhí)行計劃發(fā)生顯著變化：

• type：變?yōu)?code>range（范圍掃描），僅掃描主鍵索引中id在10001-10010之間的記錄；
• key：命中主鍵索引（PRIMARY），無需掃描全表；
• rows：掃描行數(shù)僅為10行，與需要返回的數(shù)據(jù)量完全一致，性能大幅提升。

3.3 關(guān)鍵注意事項

此方案的前提是主鍵必須連續(xù)。若主鍵不連續(xù)（如刪除過數(shù)據(jù)），會導(dǎo)致查詢結(jié)果與普通limit分頁不一致，示例如下：

先刪除一條數(shù)據(jù)，破壞主鍵連續(xù)性：

delete from t1 where id=10;  -- 刪除id=10的記錄

對比兩種查詢結(jié)果：

• 普通limit：select a,b from t1 limit 10000,10會跳過前10000條（包含被刪除的id=10，實際掃描10001條有效數(shù)據(jù)），返回第10001-10010條有效數(shù)據(jù)；
• 主鍵范圍查詢：select a,b from t1 where id >10000 and id <=10010會跳過id=10的空缺，直接返回id=10001-10010的10條數(shù)據(jù)，與預(yù)期結(jié)果不一致。

適用場景：主鍵自增且無刪除操作的表（如日志表、流水表）。

四、優(yōu)化方案二：基于非主鍵字段排序的分頁查詢

若分頁查詢需要按非主鍵字段排序（如按a字段升序），直接使用order by + limit會觸發(fā)filesort（文件排序），性能極差。我們通過“子查詢查主鍵 + 關(guān)聯(lián)查詳情”的方式優(yōu)化。

4.1 普通非主鍵排序分頁的問題

以“按a字段排序，查詢第99001-99002條數(shù)據(jù)”為例，普通SQL如下：

select * from t1 order by a limit 99000,2;

• 執(zhí)行計劃問題：order by a會觸發(fā)filesort（即使a字段有索引idx_a，若查詢字段包含非索引字段，仍需回表，可能導(dǎo)致filesort）；
• 性能損耗：需掃描大量數(shù)據(jù)并排序，數(shù)據(jù)量越大，排序耗時越長。

4.2 優(yōu)化后的SQL

核心思路：先通過子查詢僅查詢“排序后的主鍵id”（利用索引避免filesort），再通過主鍵關(guān)聯(lián)查詢完整數(shù)據(jù)：

-- 子查詢：按a排序，取第99001-99002條的id（僅掃描索引，無filesort）
-- 主查詢：通過id關(guān)聯(lián)表t1，查詢完整數(shù)據(jù)（主鍵關(guān)聯(lián)性能極高）
select f.* from t1 f 
inner join (select id from t1 order by a limit 99000,2) g 
on f.id = g.id;

4.3 執(zhí)行計劃優(yōu)化點

• 子查詢：select id from t1 order by a limit 99000,2命中索引idx_a，type為index，無filesort，僅掃描99002條索引記錄（遠(yuǎn)少于全表掃描）；
• 主查詢：通過主鍵id關(guān)聯(lián)，type為eq_ref（主鍵等值匹配，性能最優(yōu)），rows僅為2行，無額外性能損耗。

適用場景：所有需要按非主鍵字段排序的分頁查詢，尤其適合數(shù)據(jù)量超過10萬級的表。

五、總結(jié)：不同場景的分頁查詢選型

通過以上優(yōu)化方案，可有效解決MySQL分頁查詢在大數(shù)據(jù)量下的性能問題，實際項目中需根據(jù)業(yè)務(wù)場景（排序字段、主鍵連續(xù)性）選擇合適的方案，并結(jié)合索引設(shè)計進(jìn)一步提升性能。

以上就是MySQL分頁查詢優(yōu)化的實踐指南的詳細(xì)內(nèi)容，更多關(guān)于MySQL分頁查詢優(yōu)化的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論