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

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

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

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

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

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

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

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

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

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

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

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

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

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

2. 必須配置的索引

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

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

此時(shí)子查詢可以改為：

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;

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

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

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

五、注意事項(xiàng)：避免踩坑

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

六、總結(jié)

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

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

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

最新評(píng)論