引言

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

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

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

1.1 創(chuàng)建測(cè)試表

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

CREATE TABLE `t1` (            
  `id` int NOT NULL auto_increment,  -- 自增主鍵
  `a` int DEFAULT NULL,              -- 普通字段，用于非主鍵排序測(cè)試
  `b` int DEFAULT NULL,              -- 普通字段
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時(shí)間',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '記錄更新時(shí)間',
  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 批量插入測(cè)試數(shù)據(jù)

通過(guò)存儲(chǔ)過(guò)程批量插入10萬(wàn)條數(shù)據(jù)，避免手動(dòng)插入的繁瑣：

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

二、基礎(chǔ)分頁(yè)查詢：?jiǎn)栴}與執(zhí)行計(jì)劃分析

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

2.1 普通limit分頁(yè)SQL

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

2.2 執(zhí)行計(jì)劃分析

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

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

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

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

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

3.1 優(yōu)化后的SQL

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

3.2 執(zhí)行計(jì)劃對(duì)比

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

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

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

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

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

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

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

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

適用場(chǎng)景：主鍵自增且無(wú)刪除操作的表（如日志表、流水表）。

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

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

4.1 普通非主鍵排序分頁(yè)的問(wèn)題

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

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

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

4.2 優(yōu)化后的SQL

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

-- 子查詢：按a排序，取第99001-99002條的id（僅掃描索引，無(wú)filesort）
-- 主查詢：通過(guò)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í)行計(jì)劃優(yōu)化點(diǎn)

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

適用場(chǎng)景：所有需要按非主鍵字段排序的分頁(yè)查詢，尤其適合數(shù)據(jù)量超過(guò)10萬(wàn)級(jí)的表。

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

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

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

最新評(píng)論