MySQL LIMIT 與 JOIN 優化實戰：大偏移量分頁、小表驅動大表原理

前言

LIMIT 分頁與 JOIN 關聯是高頻 SQL 場景，但大偏移量分頁效能崩塌、JOIN 順序選擇錯誤導致全表掃描是常見痛點。本文深度解析原理與優化實戰。

一、LIMIT 大偏移量效能問題

原理：掃描 M+N 行再丟棄 M 行

SELECT * FROM t WHERE uid = 177 ORDER BY id DESC LIMIT 1234567, 20;
-- MySQL 實際執行：掃描 1,234,587 行，丟棄前 1,234,567 行，返回 20 行

偏移量越大，掃描行數線性增長，IO 與 CPU 暴增。

實測對比 (240萬行表，cpid=769 約 90萬行)

-- 方案 1：大偏移量 LIMIT (3.84 秒)
SELECT id, mobile, time, cpid, linkid FROM cp_mo100227 WHERE cpid=769 LIMIT 888888, 10;

-- 方案 2：書籤分頁 / 遞延關聯 (0.00 秒)
SELECT id, mobile, time, cpid, linkid FROM cp_mo100227 WHERE cpid=769 AND id >= 11535090 LIMIT 10;

-- 方案 3：時間範圍分頁 (0.01 秒)
SELECT id, mobile, time, cpid, linkid FROM cp_mo100227 WHERE cpid=769 AND time >= '2010-02-24 21:07:48' LIMIT 10;

優化方案總結

書籤分頁實作範例

-- 第一頁
SELECT * FROM t WHERE uid=1 ORDER BY id DESC LIMIT 20;
-- 記住最小 id = 1000

-- 下一頁
SELECT * FROM t WHERE uid=1 AND id < 1000 ORDER BY id DESC LIMIT 20;
-- 記住最小 id = 980

-- 上一頁 (需記住上頁最大 id)
SELECT * FROM t WHERE uid=1 AND id > 1020 ORDER BY id ASC LIMIT 20;
-- 結果再反轉

二、JOIN 優化：小表驅動大表的真相

Nested Loop Join 原理

MySQL 只支援 Nested Loop Join (NLJ)：

FOR each row in outer_table (驅動表) DO
    FOR each row in inner_table (被驅動表) DO
        IF join_condition MATCH THEN OUTPUT
    END FOR
END FOR

總掃描行數 ≈ 驅動表結果集行數 × 被驅動表單次掃描行數

關鍵結論：以「結果集大小」決定驅動表，而非「表大小」

-- 表 A: 100萬行，條件後 10行
-- 表 B: 1萬行，條件後 5000行
-- 驅動表應選 A (結果集 10 行)，而非 B (結果集 5000 行)

實戰優化步驟

1. 預估結果集：EXPLAIN 看 rows、filtered
2. 小結果集驅動大結果集：STRAIGHT_JOIN 強制順序
3. 被驅動表關聯欄位必須有索引：否則全表掃描
4. 條件下推：WHERE 條件盡早套用在驅動表

LEFT/RIGHT JOIN 條件放置陷阱

-- ❌ 錯誤：FROM 表條件放 WHERE，導致外連變內連
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.status = 1;

-- ✅ 正確：從表條件放 ON
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id AND b.status = 1;

-- ✅ 主表條件放 WHERE
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id WHERE a.type = 2;

三、綜合實戰：分頁 + JOIN

-- 訂單分頁查詢用戶資訊 (訂單表 1000萬、用戶表 100萬)

-- ❌ 慢：大偏移量 + JOIN
SELECT o.*, u.name FROM orders o
JOIN users u ON o.uid = u.id
WHERE o.status = 1
ORDER BY o.id DESC LIMIT 1000000, 20;

-- ✅ 快：書籤分頁 + JOIN (驅動表 orders 結果集小)
SELECT o.*, u.name FROM orders o
JOIN users u ON o.uid = u.id
WHERE o.status = 1 AND o.id < 5000000  -- 上頁最小 id
ORDER BY o.id DESC LIMIT 20;

-- ✅ 遞延關聯 (若無書籤)
SELECT o.*, u.name FROM 
(SELECT id, uid FROM orders WHERE status = 1 ORDER BY id DESC LIMIT 1000000, 20) o
JOIN users u ON o.uid = u.id;

四、優化檢查清單

1. LIMIT 偏移量 > 10000 時，強制改用書籤/遞延關聯
2. JOIN 使用 EXPLAIN 確認驅動表為小結果集
3. 被驅動表關聯欄位有索引 (type=ref/eq_ref)
4. LEFT JOIN 從表條件在 ON、主表條件在 WHERE
5. 避免 SELECT *，只取需要欄位 (覆蓋索引機會)
6. 大表分頁頁面 UI 改為「首頁、前後 N 頁、末頁」

結語

LIMIT 大偏移量與 JOIN 順序錯誤是效能殺手。理解 Nested Loop Join 原理、掌握書籤分頁模式、善用 EXPLAIN 驗證驅動表選擇，是解決此類問題的核心技能。前端分頁 UI 的合理妥協，往往是成本最低的根治方案。

1. 上一篇文章： MySQL 效能優化八大核心策略：從架構到 SQL 的系統化調優
2. 下一篇文章： MySQL LEFT JOIN 效能分析與優化：執行原理、NULL 陷阱與優化器行為

MySQL 綜合教學

macOS 修改 MySQL 預設字符集為 utf8mb4 完整步驟

問題背景 macOS 官方 pkg 安裝的 MySQL 預設

MySQL 真實環境冷備份實戰：結合 LVM 確保數據安全

MySQL 真實環境冷備份實戰：結合 LVM 確保數據安全

MySQL 字符集編碼查看、修改與亂碼排查全攻略

核心概念：字符集 vs 校對規則 字符集：字符到二進制的映

MySQL 與 MSSQL 查詢語法對比（七）—— SELECT、子查詢、正則與分頁

MySQL 基本 SELECT 語法結構 SELECT 屬性