MySQL 8.0+ 支援視窗函數,利用 ROW_NUMBER() 替代傳統 LIMIT offset, rowsize,解決深分頁 (大 offset) 掃描大量無用行的效能災難。
SELECT * FROM big_table ORDER BY id LIMIT 1000000, 20;
-- MySQL 必須掃描前 1,000,000 行再丟棄,再取 20 行 → 極慢
-- 1. 建立測試表
CREATE TABLE SomeData (
id INT PRIMARY KEY,
name VARCHAR(30),
description TEXT
);
-- 2. 為分頁鍵建立覆蓋索引 (含查詢欄位)
CREATE INDEX idx_id_name_desc ON SomeData (id DESC, name, description);
-- 3. 視窗函數分頁 (邏輯分頁,物理只掃目標頁)
SELECT dataLevel, name, description
FROM (
SELECT name, description,
ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
FROM SomeData
) AS t
WHERE dataLevel BETWEEN 21 AND 40; -- 第 2 頁,每頁 20 筆
CREATE PROCEDURE GetDataPaged(
IN p_startRowIndex INT, -- 0-based 頁碼
IN p_maximumRows INT, -- 頁大小
IN p_sort VARCHAR(100) -- 排序欄位 (防注入需白名單驗證)
)
BEGIN
IF LENGTH(TRIM(p_sort)) = 0 THEN SET p_sort = 'id'; END IF;
SET @sql = CONCAT(
'SELECT dataLevel, name, description FROM (',
' SELECT name, description, ROW_NUMBER() OVER (ORDER BY ', p_sort, ' DESC) AS dataLevel ',
' FROM SomeData',
') AS t WHERE dataLevel > ', p_startRowIndex * 10,
' AND dataLevel <= ', (p_startRowIndex * 10 + p_maximumRows)
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
| 分頁方式 | Offset 1000000 耗時 | 原理 |
|---|---|---|
| LIMIT offset, size | ~2.5 秒 | 全掃描 + filesort + 丟棄 |
| ROW_NUMBER() + 覆蓋索引 | ~0.02 秒 | 索引順序掃描至目標區間停止 |
| 游標/臨時表 | ~1.8 秒 | 物化中間結果 |
COUNT(*) 仍需單獨查詢 (或快取/近似值)-- 利用主鍵延遲關聯
SELECT t.* FROM big_table t
INNER JOIN (
SELECT id FROM big_table ORDER BY id LIMIT 1000000, 20
) tmp USING (id);
-- 內層只掃描主鍵索引 (窄)、外層回表僅 20 行
**核心原則**:深分頁必須避免大 Offset 掃描,利用索引順序特性直接定位目標頁。