MySQL 分頁查詢優化實戰：從 LIMIT 偏移量到延遲關聯的效能躍升

MySQL 分頁查詢優化實戰

Oracle 習慣用 ROWNUM 分頁，遷移到 MySQL 卻發現 LIMIT offset, size 在大偏移量時極慢。本文對比三種優化寫法，實測效能差異，給出最佳實踐建議。

表結構與基線查詢

CREATE TABLE t_user (
  user_id VARCHAR(32) PRIMARY KEY,
  user_name VARCHAR(50),
  password VARCHAR(100),
  contact_tel VARCHAR(20),
  email VARCHAR(100),
  create_date DATETIME
);
CREATE INDEX idx_user_id ON t_user(user_id);

-- 基線：大偏移量極慢
SELECT * FROM t_user ORDER BY user_id DESC LIMIT 100000, 20;

優化方案對比

方案一：子查詢延遲關聯（推薦）

SELECT * FROM t_user
WHERE user_id <= (
  SELECT user_id FROM t_user ORDER BY user_id DESC LIMIT 100000, 1
)
ORDER BY user_id DESC LIMIT 20;

原理：子查詢只掃描索引定位到目標 ID，外層再回表取 20 行，避免掃描 10 萬行再丟棄。

方案二：JOIN 延遲關聯

SELECT u.* FROM t_user u
JOIN (
  SELECT user_id FROM t_user ORDER BY user_id DESC LIMIT 100000, 1
) tmp ON u.user_id = tmp.user_id
ORDER BY u.user_id DESC LIMIT 20;

效能與方案一相近，寫法偏好因人而異。

方案三：覆蓋索引 + 書籤查詢（需業務配合）

若查詢只需索引欄位，可直接 SELECT user_id FROM t_user ORDER BY user_id DESC LIMIT 100000, 20 走覆蓋索引，極快。但需 SELECT * 時仍需回表。

總筆數查詢優化：SQL_CALC_FOUND_ROWS

-- 單次查詢同時取得分頁資料與總筆數
SELECT SQL_CALC_FOUND_ROWS * FROM t_user WHERE user_id <> 'root' LIMIT 20;
SELECT FOUND_ROWS();  -- 返回無 LIMIT 時的總行數

比單獨跑 COUNT(*) 省一次全表掃描，但在高併發下可能造成鎖爭用，視業務權衡。

實測數據（100 萬筆測試）

最佳實踐清單

1. 大偏移量分頁必須用延遲關聯（子查詢或 JOIN）
2. 確保 ORDER BY 欄位有索引，且索引順序與排序一致
3. 若只需主鍵 ID，優先用覆蓋索引避免回表
4. 總筆數查詢用 SQL_CALC_FOUND_ROWS 或快取 Redis 計數
5. 極大資料集考慮游標分頁（WHERE id > last_id LIMIT 20），彈性更好