MySQL 深分頁優化實戰：從 32 秒降至 3 秒的延遲關聯改寫技巧

MySQL 深分頁優化實戰：從 32 秒降至 3 秒的延遲關聯改寫技巧

一、問題現場

某業務查詢涉及兩張表：字典表（1 欄位）+ 業務表（150 欄位，單行約 2KB）。日常 5 萬筆尚可，月末單日激增至 20 萬筆，原 SQL 超時失敗。表統計顯示：InnoDB 頁 16KB，每頁僅容 8 行，隨機 I/O 極大。

二、直觀 LIMIT 方案為何慢？

SELECT a1.*, 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 秒，絕大多數耗在 Sending data 階段（伺服器檢索全量符合條記錄、讀取 150 個欄位、傳輸）。

三、優化核心思路：延遲關聯

原理：LIMIT offset, rows 會掃描並丟棄前 offset 行。若只對主鍵分頁（極窄），再用主鍵回表取完整欄位，可避開大量隨機 I/O。

改寫後 SQL

SELECT a1.*, 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 a1
        WHERE a1.STATUS <> '00' AND a1.settle_date = 20151230
        ORDER BY 1
        LIMIT 50000, 10000
    ) AS t
);

關鍵點解析

1. 內層子查詢只選主鍵 seq_id，覆蓋索引掃描，無回表，極快。
2. 中層 AS t 別名繞過 MySQL「不支援 LIMIT IN 子查詢」限制（ERROR 1235）。
3. 外層用 IN 回表僅 1 萬行，隨機 I/O 量減少 90%+。

四、優化效果對比

執行時間：~3.2 秒，提速 10 倍。

五、優化器等價改寫驗證

透過 EXPLAIN EXTENDED + SHOW WARNINGS 可見優化器內部重寫邏輯，確認符合預期：先取主鍵集合，再 JOIN 回表。

六、延伸技巧：Profiling 定位耗時細節

SET profiling=1;
-- 執行目標 SQL
SHOW PROFILES;           -- 取得 Query_ID
SHOW PROFILE FOR QUERY ;  -- 細分各階段耗時

可精確定位 Sending data、Sorting、Locking 等階段耗時分佈。

七、總結

• 深分頁 LIMIT M,N 隨 M 增大線性劣化。
• 延遲關聯：子查詢只取主鍵 → 回表取完整行，是通用高效模式。
• MySQL 8.0+ 支援 LIMIT ... OFFSET ... 配合索引下推，但延遲關聯仍具通用性。
• 寬表（大欄位、大行長）場景收益最大。