MySQL Covering Index（覆蓋索引）原理與實戰：讓查詢只走索引不回表

什麼是 Covering Index？

查詢所需的所有欄位都包含在索引中，執行計畫 `Extra` 欄顯示 Using index，不需回表查詢資料行，大幅減少 I/O。

經典案例

-- 原查詢
SELECT a FROM t WHERE b = 100;
-- 單獨在 b 建索引：索引定位 → 回表取 a → 兩次 I/O

-- 優化：建立複合索引 (b, a)
ALTER TABLE t ADD INDEX idx_b_a (b, a);
-- 執行計畫顯示 Using index，直接從索引葉節點取得 a，零回表

核心限制

• 只 SELECT 需要的欄位：`SELECT *` 會包含非索引欄位，導致回表，覆蓋索引失效。
• 索引欄位順序：`WHERE` 條件欄位在前，`SELECT` 欄位在後（`idx_b_a` 而非 `idx_a_b`）。
• 索引長度限制：InnoDB 單索引最大 3072 bytes（utf8mb4 下約 1024 字元），超長欄位無法納入覆蓋索引。

實戰範例 1：文章分類計數

SELECT COUNT(*) FROM articles WHERE category_id = 5;
-- category_id 索引即為覆蓋索引（COUNT(*) 不需讀取行資料）

實戰範例 2：大偏移分頁優化

-- 原寫法：LIMIT 10000, 10 需掃描 10000 行
SELECT id, title, content FROM articles ORDER BY created DESC LIMIT 10000, 10;

-- 優化：子查詢只取 id（覆蓋索引 created, id），再 JOIN 回表取內容
SELECT a.id, a.title, a.content
FROM articles a
INNER JOIN (
  SELECT id FROM articles ORDER BY created DESC LIMIT 10000, 10
) b USING (id)
ORDER BY a.created DESC;
-- 建立複合索引 (created, id) 使子查詢走覆蓋索引，極速定位 id

驗證方法

EXPLAIN SELECT ...;
-- 關鍵看 Extra 欄：Using index = 覆蓋索引生效

避坑指南

1. 不要為了覆蓋索引而建立包含所有欄位的超大索引（寫入效能暴跌、空間暴增）。
2. 含 `TEXT/BLOB` 欄位無法放入索引，無法覆蓋。
3. 覆蓋索引對 `UPDATE`/`DELETE` 也有加速效果（定位更快）。