Discuz! 熱帖翻頁性能優化：從 filesort 到覆蓋索引再到延遲關聯

Discuz! 熱帖翻頁性能優化：從 filesort 到覆蓋索引再到延遲關聯

Discuz! 熱帖深度翻頁（如 LIMIT 129860, 15）會導致數據庫 CPU 飆升、甚至癱瘓。根因是原有聯合索引 displayorder(tid, invisible, dateline) 因 invisible IN ('0','-2') 範圍查詢導致無法利用索引排序，退化為 Using filesort。本文演示三級優化：新建索引消除排序、強制索引應對深分頁、延遲關聯徹底解決大偏移量性能崩塌。

問題 SQL 與原執行計劃

SELECT * FROM pre_forum_post 
WHERE tid=8201301 AND `invisible` IN('0','-2') 
ORDER BY dateline LIMIT 15;

EXPLAIN ...\G
type: ref
key: displayorder
rows: 593371
Extra: Using index condition; Using where; Using filesort

首頁翻頁尚可（Handler_read_next ~33萬），但深分頁代價指數級增長：

LIMIT 129860, 15
Handler_read_key: 129876   -- 跳過大量鍵
Handler_read_rnd: 129875   -- 大結果集排序

根因分析

索引 displayorder(tid, invisible, dateline) 中，invisible 為範圍查詢（IN 視為範圍），導致 dateline 無法用於索引排序，必須 filesort。若條件為 invisible = '0' AND dateline = ... 則可用全索引。

優化一：新建索引消除 filesort

ALTER TABLE pre_forum_post ADD INDEX idx_tid_dateline (tid, dateline);

EXPLAIN ... LIMIT 15\G
key: idx_tid_dateline
Extra: Using where  -- filesort 消失！

首頁翻頁性能大幅提升。

優化二：深分頁強制使用新索引

優化器認為深分頁用新索引掃描行數更多（70萬 vs 59萬），傾向舊索引+filesort。實測 profiling 顯示排序代價遠大於掃描代價，需強制：

SELECT * FROM pre_forum_post USE INDEX(idx_tid_dateline) 
WHERE tid=8201301 AND `invisible` IN('0','-2') 
ORDER BY dateline LIMIT 129860,15;

-- Profiling 對比
-- 舊索引: Creating sort index 0.94s
-- 新索引: Sending data 0.92s（無排序，整體快 ~2%）

優化三：延遲關聯 (Delayed Join) 徹底解決大偏移量

SELECT * FROM pre_forum_post t1
INNER JOIN (
  SELECT id FROM pre_forum_post USE INDEX(idx_tid_dateline)
  WHERE tid=8201301 AND `invisible` IN('0','-2')
  ORDER BY dateline LIMIT 129860,15
) t2 USING (id);

-- Profiling
Sending data: 0.22s  -- 效率提升 4 倍+

原理：子查詢只掃描索引取 id（覆蓋索引，極窄），再回主表取完整行，避免大偏移量下的全行掃描與排序。

建議 Discuz! 官方集成方案

1. 新增索引 idx_tid_dateline(tid, dateline)。
2. 代碼層判斷：若 LIMIT offset, N 且 offset > 閾值(如 10000)，強制使用新索引 + 改寫為延遲關聯。
3. 普通帖子（回復頁數少）維持原計劃，無副作用。

總結

熱帖翻頁性能殺手是「範圍查詢打斷索引排序 + 大偏移量 filesort」。三步走：新索引消除排序 → 強制索引規避誤判 → 延遲關聯消除大偏移掃描，層層遞進，徹底解決。