MySQL 課程 – 索引 (Index)

主題：覆蓋索引與複合索引

---

簡介

在日常的資料庫開發與維運工作中，索引是提升查詢效能的關鍵工具。
即使是最簡單的單欄位索引，也能讓巨量資料表的搜尋速度從秒級降到毫秒。然而，隨著業務需求變得更複雜，單一欄位索引往往不足以滿足效能與資源的最佳平衡。這時，覆蓋索引 (Covering Index) 與 複合索引 (Composite Index) 就成為進階優化的核心手段。

本篇文章將從概念、實作、常見陷阱與最佳實踐，逐步帶領讀者了解如何在 MySQL 中正確運用這兩種索引，並透過實務範例說明它們在真實應用情境中的威力。即使你是剛入門的初學者，只要跟著步驟操作，也能快速掌握索引的設計技巧；對於已有一定經驗的開發者，更能從細部的調校中得到性能上的顯著提升。

---

核心概念

1️⃣ 複合索引 (Composite Index)

複合索引是指同時包含多個欄位的索引。MySQL 會依照定義的欄位順序建立 B‑Tree 結構，搜尋時會先比對第一個欄位，若相等才會往下比對第二個欄位，如此類推。

• 左前綴原則 (Left‑most Prefix Rule)：只有符合左側前綴的查詢才能使用到複合索引。例如，索引 (a, b, c) 可以被以下條件使用：WHERE a = ?、WHERE a = ? AND b = ?、WHERE a = ? AND b = ? AND c = ?；但 WHERE b = ? 或 WHERE c = ? 則不會使用到該索引（除非啟用「索引條件下推」或「索引合併」）。

範例 1：建立與使用複合索引

-- 建立一個會員資料表
CREATE TABLE members (
    member_id   INT PRIMARY KEY AUTO_INCREMENT,
    country     VARCHAR(3) NOT NULL,
    city        VARCHAR(50) NOT NULL,
    age         TINYINT NOT NULL,
    status      ENUM('active','inactive') NOT NULL,
    INDEX idx_country_city_age (country, city, age)   -- 複合索引
);

-- 查詢符合左前綴的條件，索引會被完整利用
SELECT member_id, status
FROM members
WHERE country = 'TW' AND city = 'Taipei' AND age >= 30;

重點：上述查詢只需要讀取索引頁面即可定位符合條件的列，避免了全表掃描。

範例 2：左前綴失效的情況

-- 只使用 city 欄位，無法利用 idx_country_city_age
SELECT member_id, status
FROM members
WHERE city = 'Kaohsiung';

解法：可以再額外建立 (city, country) 的索引，或調整查詢順序使其符合左前綴。

2️⃣ 覆蓋索引 (Covering Index)

覆蓋索引（又稱「索引覆寫」）指的是查詢所需要的所有欄位，都已經包含在索引本身之中。當發生覆蓋索引時，MySQL 只會在索引樹上完成搜尋與返回結果，根本不會回表（即不會去讀取原始資料列），因此 I/O 負擔大幅降低。

• 何時會產生覆蓋索引：
  1. SELECT 的欄位全部在索引的鍵值或附加列 (INCLUDE) 中。
  2. WHERE、GROUP BY、ORDER BY、JOIN 條件也全部使用索引欄位。

範例 3：建立覆蓋索引

-- 假設我們常常查詢會員的狀態與年齡，且只需要這兩個欄位
CREATE INDEX idx_status_age ON members (status, age);

-- 這個查詢只需要 status、age 兩個欄位，完全被 idx_status_age 覆蓋
SELECT status, age
FROM members
WHERE status = 'active' AND age BETWEEN 20 AND 30;

結果：執行計劃會顯示 Using index，代表 MySQL 僅在索引中完成所有工作，不會回表。

範例 4：使用 INCLUDE（MySQL 8.0+）建立更靈活的覆蓋索引

-- MySQL 8.0 支援「隱式列」(included columns) 讓索引更小
CREATE INDEX idx_country_city ON members (country, city) INCLUDE (status);

-- 查詢同時需要 country、city、status，仍可完全覆蓋
SELECT country, city, status
FROM members
WHERE country = 'TW' AND city = 'Tainan';

好處：status 雖未列在索引鍵順序中，但因為被 INCLUDE，仍可在索引內直接取得，避免回表。

3️⃣ 複合索引與覆蓋索引的結合

在實務中，往往會同時考慮 查詢條件 與 返回欄位，設計出既能符合左前綴，又能成為覆蓋索引的複合索引。

範例 5：一次滿足左前綴與覆蓋需求

-- 常用的報表查詢：依國家、城市篩選，同時顯示會員狀態與年齡
CREATE INDEX idx_country_city_status_age
ON members (country, city, status, age);

-- 完全覆蓋且符合左前綴
SELECT country, city, status, age
FROM members
WHERE country = 'TW' AND city = 'Taichung' AND status = 'active';

此索引：

1. WHERE 條件使用左前綴 country, city, status。
2. SELECT 欄位全部在索引內（age 為最後一欄），因此 不會回表。

---

常見陷阱與最佳實踐

陷阱	說明	解決方案
過度建立複合索引	每個索引都會佔用磁碟與記憶體，寫入操作會因維護索引而變慢。	只保留最常被查詢使用的左前綴組合，定期使用 EXPLAIN 觀察索引命中率。
忽略欄位順序	複合索引的順序直接影響查詢能否使用索引。	依照 篩選條件的選擇性（Selectivity）排序，先放最具過濾力的欄位。
覆蓋索引過大	索引包含太多欄位會導致索引頁面變大，反而降低效能。	只把 必要的欄位 放入索引；若需要額外欄位，可使用 INCLUDE（MySQL 8.0+）。
使用 LIKE '%xxx'	前置通配符會使索引失效。	改成全文索引（FULLTEXT）或使用前綴搜索 LIKE 'xxx%'。
錯誤估計資料分布	低選擇性的欄位放在前面會造成大量索引掃描。	觀察 SHOW INDEX FROM table; 中 Cardinality，或使用 ANALYZE TABLE 更新統計資訊。

最佳實踐要點

1. 先分析查詢：使用 EXPLAIN、slow_query_log 找出瓶頸查詢，再決定索引方向。
2. 遵循左前綴：設計複合索引時，將最常用於 等值條件 的欄位排在最前。
3. 盡可能讓查詢成為覆蓋：把 SELECT 必要欄位納入索引，避免回表。
4. 適度使用 INCLUDE（MySQL 8.0+）：在不影響鍵值排序的情況下，加入額外欄位提升覆蓋率。
5. 定期重建與分析：對於寫入頻繁的表，使用 OPTIMIZE TABLE 或 ALTER TABLE … FORCE 重建索引，並執行 ANALYZE TABLE 更新統計。

---

實際應用場景

場景一：電商平台的訂單查詢

• 需求：依 order_status、order_date、customer_id 篩選，返回 order_id、total_amount。
• 設計：

CREATE INDEX idx_status_date_customer
ON orders (order_status, order_date, customer_id, total_amount);

• 效果：左前綴滿足篩選條件，total_amount 透過 INCLUDE 成為覆蓋欄位，查詢僅在索引上完成，報表生成速度提升 3–5 倍。

場景二：社群網站的貼文列表

• 需求：顯示特定 user_id 的貼文，依 created_at 降序，僅需要 post_id、title、created_at。
• 設計：

CREATE INDEX idx_user_created
ON posts (user_id, created_at DESC) INCLUDE (post_id, title);

• 效果：WHERE user_id = ? 使用左前綴，ORDER BY created_at DESC 直接利用索引排序，且 post_id、title 已被 INCLUDE，不會回表，即時牆載入時間下降至毫秒級。

場景三：分析報表的多維聚合

• 需求：每日、每國家、每產品類別的銷售總額。
• 設計：

CREATE INDEX idx_sales_report
ON sales (sale_date, country, product_category) INCLUDE (amount);

• 查詢：

SELECT sale_date, country, product_category, SUM(amount) AS total_sales
FROM sales
WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY sale_date, country, product_category;

• 效益：索引同時支援 WHERE、GROUP BY，且 amount 已被 INCLUDE，聚合過程只在索引層完成，大幅降低磁碟 I/O。

---

總結

• 複合索引透過左前綴原則，讓多條件查詢一次定位；設計時須注意欄位順序與選擇性。
• 覆蓋索引則是把查詢所需欄位全部納入索引，省去回表的成本，特別適合讀取密集且欄位固定的報表與 API。
• 結合兩者，在同一個索引裡同時滿足左前綴與覆蓋需求，是提升 MySQL 效能的最佳實踐。
• 實務上，先分析慢查詢 → 設計符合左前綴的複合索引 → 讓常用查詢變成覆蓋，再配合 INCLUDE、統計資訊更新與定期重建，即可在寫入與讀取之間取得理想的平衡。

透過本文的概念與範例，你已掌握了在 MySQL 中建立與調校 覆蓋索引、複合索引的核心技巧。接下來，建議在自己的開發環境中挑選一兩個關鍵查詢練習設計索引，觀察 EXPLAIN 的變化，將理論轉化為實際的效能提升。祝你在資料庫優化的道路上越走越順！ 🚀