MySQL 分頁優化實戰：從 32 秒到 3 秒，延遲關聯法拯救大表深分頁

場景：150 欄寬表，20 萬數據，深分頁超時

• 表 upay_csys_scquery_txn_log_his：150 個欄位，行長 ~2KB
• InnoDB 頁 16KB → 每頁僅 8 行，**隨機 IO 概率極高**
• 原 SQL：... WHERE status<>'00' AND settle_date=20151230 LIMIT 50000,10000
• 執行 32 秒，Sending data 佔 99%

為什麼 LIMIT 深分頁慢？

MySQL 實現機制：**先掃描所有滿足 WHERE 的行，排序，再丟棄前 50000 行，取後 10000 行**。
掃描 6 萬行寬表 = 大量隨機回表讀取 150 個欄位 = IO 地獄。

優化思路：延遲關聯

核心原則： **只對主鍵分頁，再用主鍵回表取完整行**。

優化前 SQL

SELECT a1.`col1`...`col150`, a2.`trans_name`
FROM `upay_csys_scquery_txn_log_his` a1
LEFT JOIN `upay_csys_trans_code` a2 ON a1.`int_trans_code`=a2.`trans_code`
WHERE a1.`status`<>'00' AND a1.`settle_date`=20151230
LIMIT 50000,10000;  -- 32 秒

優化後 SQL（延遲關聯）

SELECT a1.`col1`...`col150`, a2.`trans_name`
FROM `upay_csys_scquery_txn_log_his` a1
LEFT JOIN `upay_csys_trans_code` a2 ON a1.`int_trans_code`=a2.`trans_code`
WHERE a1.`seq_id` IN (
    SELECT seq_id FROM (
        SELECT `seq_id` 
        FROM `upay_csys_scquery_txn_log_his` 
        WHERE `status`<>'00' AND `settle_date`=20151230 
        ORDER BY 1 LIMIT 50000,10000
    ) AS t
);  -- 3 秒，提速 10 倍！

關鍵技術點解析

1. 子查詢只選主鍵 seq_id

• 覆蓋索引掃描（若有 INDEX(status, settle_date, seq_id)），**無回表**
• 只傳輸 8 字節整數，網絡/內存壓力極小

2. 必須雙層子查詢繞過 MySQL 限制

MySQL 不支援 IN (SELECT ... LIMIT ...) 直接嵌套，報錯 ERROR 1235: This version doesn't support 'LIMIT & IN/ALL/ANY/SOME subquery'。

-- 錯誤寫法
WHERE seq_id IN (SELECT seq_id FROM tbl WHERE ... LIMIT 50000,10000);

-- 正確寫法：多包一層派生表
WHERE seq_id IN (SELECT seq_id FROM (SELECT seq_id FROM tbl WHERE ... LIMIT 50000,10000) AS t);

3. EXPLAIN EXTENDED + SHOW WARNINGS 驗證優化器改寫

EXPLAIN EXTENDED SELECT ...优化后SQL...;
SHOW WARNINGS;
-- 輸出的 Note 中可見優化器等價改寫後的 SQL，確認執行順序符合預期

效果對比

適用條件與避坑

• ✅ 表有**單調遞增主鍵**（自增 ID/序列號）
• ✅ WHERE 條件能走**覆蓋索引**（含主鍵）
• ✅ 分頁偏移量大（深分頁）
• ❌ 無主鍵/主鍵非單調/WHERE 無覆蓋索引 → 效果有限
• ❌ 極淺分頁 (LIMIT 0,10) → 優化器可能自動優化，無需手工改寫

總結

**延遲關聯** 是 MySQL 深分頁優化的經典模式：**主鍵分頁 → 回表取列**。配合覆蓋索引、雙層子查詢繞過限制，將寬表深分頁從「IO 地獄」拉回「毫秒級響應」。建議納入團隊《SQL 開發規範》：大表深分頁必用延遲關聯。