在后端開發(fā)中，分頁查詢是高頻需求，但當(dāng)數(shù)據(jù)量達到百萬級、分頁頁碼翻到數(shù)千頁后，你可能會遇到一個棘手問題：limit 5000000,10 這類深分頁查詢，響應(yīng)時間突然從幾十毫秒飆升到幾秒甚至更久。今天就來拆解這個問題的根源，以及如何用 “覆蓋索引 + 子查詢” 的方案實現(xiàn)性能躍遷。

一、深分頁查詢：為什么越往后越慢？

先搞懂一個核心問題：同樣是查 10 條數(shù)據(jù)，limit 10,10 很快，limit 5000000,10 卻很慢，差別到底在哪？

這要從 MySQL 處理 limit offset, size 的邏輯說起：

1. 當(dāng)執(zhí)行 limit 5000000,10 時，MySQL 會先掃描并排序前 5000010 條數(shù)據(jù)（offset + size）；
2. 然后丟棄前 5000000 條數(shù)據(jù)，只返回剩下的 10 條；
3. 如果查詢語句是 select *，且沒有適配的索引，MySQL 還需要從磁盤讀取全表數(shù)據(jù)，再進行排序 —— 這個 “掃描 + 排序 + 丟棄” 的過程，會消耗大量 CPU 和 IO 資源，數(shù)據(jù)量越大，耗時越夸張。

舉個真實案例：一張 1000 萬數(shù)據(jù)的商品表 tb_sku，執(zhí)行 select * from tb_sku order by id limit 5000000,10，在沒有優(yōu)化的情況下，響應(yīng)時間高達 4.8 秒；而優(yōu)化后，耗時直接降到 0.08 秒，性能提升 60 倍。

二、優(yōu)化核心思路：減少 “無效工作”

既然慢的根源是 “掃描了太多不需要的數(shù)據(jù)”，那優(yōu)化方向就很明確：讓 MySQL 只處理 “真正需要的那部分數(shù)據(jù)”，減少無效掃描和排序。

這里的關(guān)鍵是利用 “覆蓋索引” 和 “子查詢” 組合：

1. 覆蓋索引：如果索引包含查詢所需的所有字段，MySQL 無需回表查主數(shù)據(jù)，直接從索引獲取數(shù)據(jù)即可 —— 這里我們用主鍵索引 id（主鍵默認是聚簇索引，本身有序，還能定位到主數(shù)據(jù)）；
2. 子查詢優(yōu)先定位主鍵：先用子查詢 select id from tb_sku order by id limit 5000000,10，通過主鍵索引快速找到 “目標 10 條數(shù)據(jù)的 id”（因為主鍵索引有序，無需額外排序，直接定位 offset 位置）；
3. 關(guān)聯(lián)主表查詳情：再用找到的 id 關(guān)聯(lián)主表 tb_sku，精準獲取這 10 條數(shù)據(jù)的完整信息 —— 此時 MySQL 只需讀取 10 條主數(shù)據(jù)，無需掃描百萬級數(shù)據(jù)。

三、實操方案：優(yōu)化后的 SQL 與索引配置

1. 優(yōu)化后的 SQL 語句

直接上代碼，核心就是 “子查詢查 id + 關(guān)聯(lián)查詳情”：

select t.* 
from tb_sku t
inner join (
    -- 子查詢：通過主鍵索引快速定位目標 10 條數(shù)據(jù)的 id
    select id 
    from tb_sku 
    order by id  -- 主鍵索引本身有序，無需額外排序
    limit 5000000, 10
) a on t.id = a.id;  -- 用 id 關(guān)聯(lián)主表，精準獲取詳情

2. 必須配置的索引

這個方案能生效，前提是 id 是主鍵（或有基于 id 的索引）—— 主鍵默認是聚簇索引，本身就包含排序?qū)傩?，所以無需額外創(chuàng)建索引。如果排序字段不是主鍵（比如按 create_time 排序），則需要創(chuàng)建聯(lián)合索引：

-- 若按 create_time 分頁，創(chuàng)建覆蓋索引（包含排序字段和主鍵）
create index idx_sku_create_time on tb_sku(create_time, id);

此時子查詢可以改為：

select t.* 
from tb_sku t
inner join (
    select id 
    from tb_sku 
    order by create_time 
    limit 5000000, 10
) a on t.id = a.id;

四、原理拆解：為什么這個方案這么快？

對比優(yōu)化前后的執(zhí)行邏輯，就能明白性能提升的關(guān)鍵：

簡單說：優(yōu)化前 MySQL 在 “做無用功”（掃描、排序、丟棄大量數(shù)據(jù)），優(yōu)化后只做 “必要工作”（定位 id、查 10 條詳情），自然速度更快。

五、注意事項：避免踩坑

1. 排序字段必須在索引中：如果子查詢的 order by 字段不在索引里，MySQL 還是會全表排序，優(yōu)化失效。比如按 price 排序，就必須創(chuàng)建包含 price 和 id 的索引；
2. 關(guān)聯(lián)字段用主鍵 / 唯一鍵：關(guān)聯(lián)主表時，要用 id 這類主鍵或唯一鍵 —— 主鍵是聚簇索引，查詢速度最快，避免用普通字段關(guān)聯(lián)導(dǎo)致全表掃描；
3. offset 過大仍有瓶頸：如果 offset 達到千萬級（比如 limit 10000000,10），子查詢定位 id 仍會有輕微耗時，此時建議用 “游標分頁”（比如 where id > 上一頁最大id limit 10），徹底避免 offset 問題；
4. 驗證執(zhí)行計劃：優(yōu)化后用 explain 查看執(zhí)行計劃，確保子查詢的 type 是 range 或 ref，Extra 沒有 Using filesort（排序）和 Using temporary（臨時表）—— 這兩個關(guān)鍵字出現(xiàn)，說明索引沒生效。

六、總結(jié)

深分頁查詢的優(yōu)化，核心不是 “用更復(fù)雜的技術(shù)”，而是 “讓 MySQL 少做無效工作”。本文的 “覆蓋索引 + 子查詢” 方案，本質(zhì)是利用索引的有序性和精準定位能力，把 “百萬級數(shù)據(jù)處理” 壓縮到 “10 條數(shù)據(jù)處理”，實現(xiàn)性能質(zhì)的飛躍。

如果你的項目中也有深分頁場景，不妨試試這個方案 —— 從幾秒到幾十毫秒的提升，可能只需要改一行 SQL。

到此這篇關(guān)于解決MySQL分頁優(yōu)化的實現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL分頁優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論