InnoDB COUNT(*) 優化祕籍：為何二級索引掃描比主鍵快？

InnoDB COUNT(*) 效能陷阱與破解

在 InnoDB 中執行 SELECT COUNT(*) FROM table 或帶條件 WHERE pk >= 0 時，預設走主鍵 (Clustered Index) 掃描，必須讀取完整行資料，大表極慢。實測發現，改用二級索引 (Secondary Index) 條件 WHERE secondary_col >= 0，速度顯著提升。

核心原理：聚簇索引 vs 二級索引

• 聚簇索引 (Primary Key)：葉節點直接儲存完整行資料 (Row Data)，索引即資料，資料即索引。掃描主鍵等同於全表掃描，I/O 開銷最大。
• 二級索引：葉節點僅儲存索引欄位值 + 主鍵值 (指標)，不包含完整行。結構更緊湊，頁面能容納更多條目，掃描同等行數時 I/O 更少。

優化實戰

-- 慢：全表掃描主鍵
SELECT COUNT(*) FROM t WHERE id >= 0;
SELECT COUNT(*) FROM t;

-- 快：掃描較窄的二級索引
SELECT COUNT(*) FROM t WHERE aid >= 0;  -- aid 為二級索引欄位

注意：aid >= 0 條件需覆蓋全表資料分佈，否則統計結果不準。若業務允許近似值，可結合 EXPLAIN 估算行數或使用資訊結構 INFORMATION_SCHEMA.TABLES.TABLE_ROWS。

延伸思考

• InnoDB 5.7+ 引入 INNODB_METRICS 與 INNODB_TABLESTATS 可獲取更精準的統計資訊。
• 極大表建議維護一張計數表，透過觸發器或應用層異步更新，查詢時直接讀取計數值，O(1) 複雜度。