MySQL LIMIT offset, rows 機制導致「深分頁」效能急劇下降:LIMIT 1000000, 10 需掃描並捨棄前 100 萬行。本文提供三種經典優化方案,從基礎到進階解決百萬級資料分頁難題。
LIMIT 1000000, 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 次。
適用於「下一頁」「無限捲動」場景,不支援跳頁,但效能恆定 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);
子查詢與主查詢皆走覆蓋索引,零回表,效能極致。
-- 建立測試環境
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)
• 加速器:覆蓋索引消除回表
• 架構層:快取熱門頁、離線預計算、搜尋引擎分流
根據業務場景選擇適當方案,百萬級資料分頁也能毫秒級響應。