MySQL MyISAM 分頁優化實戰：爬蟲翻頁 LIMIT 深偏移災難，覆蓋索引 + 子查詢破解

業務背景

• **引擎**：MyISAM (MySQL 5.1)
• **場景**：爬蟲抓取數據，深度翻頁 `LIMIT 286660, 20`
• **痛點**：`OFFSET` 越大越慢，掃描並丟棄海量行

原始慢 SQL

SELECT Aa.*, B.col, B.col, C.col, C.col
FROM (
  SELECT A.col, A.col, ... -- 12 個列
  FROM A 
  WHERE A.class1id = 1000000 
  ORDER BY A.oktime DESC 
  LIMIT 286660, 20
) Aa
LEFT JOIN B ON Aa.class2id = B.id
LEFT JOIN C ON Aa.username = C.username;

優化核心思路

1. **子查詢只取主鍵**：內層僅 `SELECT askid`，利用**覆蓋索引** 避免回表。
2. **延遲關聯**：外層再 `JOIN` 回原表取業務列 + 關聯表。
3. **索引設計**：`(oktime, askid, class1id)` 支援 `WHERE class1id=... ORDER BY oktime DESC` 覆蓋。

優化後 SQL

-- 1. 創建覆蓋索引
CREATE INDEX idx_1 ON solve_answerinfo (oktime, askid, class1id);

-- 2. 改寫查詢
SELECT Aa.*, B.col, B.col, C.col, C.col
FROM (
  SELECT A.col, A.col, ... -- 業務列
  FROM A
  INNER JOIN (
    SELECT askid 
    FROM solve_answerinfo 
    USE INDEX (idx_1)
    WHERE class1id = 1000000 
    ORDER BY oktime DESC 
    LIMIT 389300, 20  -- 深偏移
  ) aaa USING (askid)
) Aa
LEFT JOIN B ON Aa.class2id = B.id
LEFT JOIN C ON Aa.username = C.username;

效能對比

指標	優化前	優化後
內層掃描行數	~38 萬 (全表掃描排序)	~20 (索引直接定位)
回表次數	38 萬	20
執行時間	秒級/分級	毫秒級

關鍵技術點

• MyISAM 覆蓋索引：索引葉節點直接包含所有查詢列，無需回表數據文件 (`.MYD`)。
• USE INDEX 強制：防止優化器誤選全表掃描。
• 遞歸 CTE 替代 (8.0+)：現代版本可用 `WITH RECURSIVE` 遊標式翻頁，避免 OFFSET。
• ID 續傳法 (通用)：記錄上頁最大 `oktime`/`askid`，`WHERE (oktime, askid) < (last_oktime, last_askid) ORDER BY ... LIMIT 20`。

適用邊界

此優化針對 **MyISAM + 深分頁 + 固定排序**。InnoDB 聚簇索引特性不同，同樣原理但索引設計需含主鍵。