萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

MySQL 大數據量分頁優化:延遲關聯與覆蓋索引實戰方案

MySQL 深分頁效能殺手:LIMIT 偏移量大時的優化實戰

MySQL LIMIT offset, rows 機制導致「深分頁」效能急劇下降:LIMIT 1000000, 10 需掃描並捨棄前 100 萬行。本文提供三種經典優化方案,從基礎到進階解決百萬級資料分頁難題。

問題根源:LIMIT 掃描機制

LIMIT 1000000, 10 實際執行:

  1. 全索引掃描/表掃描讀取 1,000,010 行
  2. 捨棄前 1,000,000 行
  3. 返回後 10 行

偏移量越大,掃描成本線性增長,IOPS 與 CPU 暴增。

方案一:延遲關聯 / 子查詢優化 (最通用)

核心思想:**先在索引上定位主鍵 ID,再回表查試完整欄位**

-- 原始慢查詢
SELECT * FROM large_table ORDER BY id LIMIT 1000000, 10;

-- 優化:子查詢只掃描主鍵索引 (覆蓋索引),極快
SELECT a.* FROM large_table a
INNER JOIN (
    SELECT id FROM large_table ORDER BY id LIMIT 1000000, 10
) b ON a.id = b.id;

原理:子查詢僅讀取聚集索引 (主鍵) 的 id 欄位,屬於 Index Only Scan,極快;外層按主鍵 JOIN 回表僅 10 次。

方案二:記住上一頁最大 ID (游標分頁/Keyset Pagination)

適用於「下一頁」「無限捲動」場景,不支援跳頁,但效能恆定 O(1)。

-- 首頁
SELECT * FROM large_table ORDER BY id LIMIT 10;

-- 記住最後一筆 id = 98765,下一頁
SELECT * FROM large_table WHERE id > 98765 ORDER BY id LIMIT 10;

優點:無偏移掃描、效能不隨頁碼遞減、適合高併發 API。
缺點:不支援任意頁碼跳轉、需排序欄位唯一 (或加主鍵作第二排序)。

方案三:覆蓋索引優化 (配合方案一)

若查詢欄位較少,建立覆蓋索引直接滿足查詢,避免回表:

-- 查詢:SELECT id, name, created_at FROM large_table ...
-- 建立索引
CREATE INDEX idx_cover_paging ON large_table (id) INCLUDE (name, created_at);
-- MySQL 8.0+ 不支援 INCLUDE,改為複合索引
CREATE INDEX idx_cover_paging ON large_table (id, name, created_at);

子查詢與主查詢皆走覆蓋索引,零回表,效能極致。

方案四:應用層快取與預計算 (架構層面)

  • 熱門列表頁面:Redis 快取前 100 頁 HTML/JSON
  • 預計算分頁 ID 清單:離線任務每小時生成各頁 ID 陣列存 Redis
  • 搜尋結果分頁:Elasticsearch 原生支援深分頁優化 (search_after)

實戰測試腳本 (文中提供的測試框架)

-- 建立測試環境
CREATE DATABASE TestForPaging;
USE TestForPaging;

CREATE TABLE SomeData (
    id INT PRIMARY KEY,
    name VARCHAR(30),
    description TEXT
);

-- 插入測試資料 (實際測試需百萬級)
INSERT INTO SomeData VALUES (1,'num1','第1條'), (2,'num2','第2條') ...;

-- 驗證分頁邏輯
SELECT name, description, ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
FROM SomeData;

-- 目標頁面資料 (第 2-4 筆)
SELECT dataLevel, name, description FROM (
    SELECT name, description, ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
    FROM SomeData
) AS t WHERE dataLevel BETWEEN 2 AND 4;

分頁優化決策樹

需要跳頁? 
  ├─ 是 → 方案一 (延遲關聯) + 覆蓋索引
  └─ 否 (僅下一頁/無限捲動) → 方案二 (Keyset Pagination) 最優

資料量 < 10萬?
  └─ 直接 LIMIT 即可,優化投入產出比低

排序欄位非唯一?
  └─ 必須加上主鍵作為第二排序鍵: ORDER BY created_at, id

總結

MySQL 深分頁優化核心:**避免大偏移量掃描**。
• 通用方案:延遲關聯 (子查詢取 ID + JOIN 回表)
• 極致效能:Keyset Pagination (記住上頁最大 ID)
• 加速器:覆蓋索引消除回表
• 架構層:快取熱門頁、離線預計算、搜尋引擎分流

根據業務場景選擇適當方案,百萬級資料分頁也能毫秒級響應。

copyright © 萬盛學電腦網 all rights reserved