MySQL 索引 (Index) – 什麼是索引與何時使用

簡介

在大量資料的資料庫中，查詢效能往往是系統成功與否的關鍵。如果每一次 SELECT 都必須全表掃描 (Full Table Scan)，不僅會佔用大量 CPU 與 I/O，還會讓使用者等待時間急速上升。
MySQL 提供的「索引」正是為了加速資料定位而設計的資料結構，類似書本的目錄，讓資料庫能在 O(log N) 的時間內找到目標列，而不是線性掃描。

本篇文章將從 什麼是索引、何時需要使用、如何正確建立與維護 這幾個面向，帶領讀者一步步掌握索引的概念與實務技巧，適合剛踏入資料庫開發的初學者，也能為已有經驗的開發者提供最佳實踐的參考。

核心概念

1. 索引的基本原理

索引本質上是一棵 B‑Tree（或在特定情況下的 Hash、R‑Tree、Full‑Text）資料結構，儲存了「索引欄位的值」以及「對應資料列的指標」。當執行 SELECT ... WHERE column = value 時，MySQL 會先在索引樹中搜尋符合的鍵值，快速定位到實際資料列所在的磁碟頁 (page)。

重點：索引只包含被索引的欄位與指向原始資料的指標，不會存放完整的資料列，因此建立索引會佔用額外的磁碟空間。

2. 常見的索引類型

類型	說明	典型使用情境
PRIMARY KEY	隱含唯一索引，且資料行的物理排列依照此鍵 (Clustered Index)	唯一標識每筆紀錄，例如 `id`
UNIQUE	保證欄位值唯一，同時具備搜尋加速	電子郵件、身分證號
普通索引 (INDEX)	非唯一索引，只提供搜尋速度提升	常用於查詢條件的欄位
FULLTEXT	針對文字內容的全文搜尋，使用倒排索引	文章、部落格內容
SPATIAL	針對地理座標的空間資料索引	GIS、地圖服務
HASH (Memory 引擎)	直接以雜湊表儲存，適合等值查詢	快速的快取資料表

小技巧：對於 InnoDB 引擎，PRIMARY KEY 會自動成為聚簇索引 (Clustered Index)，而其他索引則是二級索引，會包含主鍵作為指向實際資料的「隱藏列」。

3. 建立與刪除索引

範例 1：建立單欄位普通索引

-- 為 `users` 表的 `email` 欄位建立普通索引
CREATE INDEX idx_users_email ON users(email);

範例 2：建立唯一索引

-- 確保 `username` 欄位不會重複
CREATE UNIQUE INDEX uq_users_username ON users(username);

範例 3：建立複合索引 (Composite Index)

-- 常見的搜尋條件是 WHERE country = ? AND city = ?
CREATE INDEX idx_users_country_city ON users(country, city);

說明：複合索引的左前綴原則 (Left‑most Prefix) 表示，若查詢只使用 country，仍能利用此索引；但若只使用 city，則無法受惠。

範例 4：刪除索引

-- 刪除先前建立的普通索引
DROP INDEX idx_users_email ON users;

範例 5：重新整理索引 (OPTIMIZE)

-- 重新整理 `orders` 表的所有索引，釋放碎片
OPTIMIZE TABLE orders;

4. 索引的使用時機

條件	建議使用索引
`WHERE` 子句使用等值或範圍條件	✅
`JOIN` 時的連接欄位	✅
`ORDER BY`、`GROUP BY` 涉及的欄位	✅（若能覆蓋排序）
`DISTINCT`、`UNION` 中的欄位	✅
`LIKE 'abc%'`（前綴匹配）	✅（普通索引）
`LIKE '%abc'`（通配符在前）	❌（需要 FULLTEXT）
高更新率的欄位（頻繁 INSERT/UPDATE/DELETE）	⚠️ 需斟酌，避免過多索引造成寫入負擔

實務建議：在 開發階段先寫好查詢，再根據 EXPLAIN 分析是否需要索引；不要在「感覺需要」時盲目建立索引。

常見陷阱與最佳實踐

過度索引
每新增一個索引，都會在寫入 (INSERT/UPDATE/DELETE) 時額外維護。過多的索引會導致 寫入效能下降，甚至產生死鎖。
最佳實踐：每張表的索引數量控制在 5–10 個（視欄位與查詢頻率而定），且定期檢視 SHOW INDEX FROM table_name;。
忽略左前綴原則
複合索引若查詢只使用右側欄位，索引將失效。
解決方式：依照最常用的查詢順序排列欄位，或建立多個單欄位索引作為備援。
索引過長
對於 VARCHAR(255) 之類的長字串建立索引會佔用大量空間。
技巧：使用 前綴索引（INDEX(col(20))），只索引前 20 個字符，對於大多數前綴搜尋已足夠。
```
CREATE INDEX idx_article_title ON articles(title(30));
```

忘記覆蓋索引 (Covering Index)
當查詢的 SELECT 欄位全部包含在索引中，MySQL 可以直接從索引返回結果，避免回表 (回到原始資料頁)。
範例：

CREATE INDEX idx_orders_user_status ON orders(user_id, status, order_date);
-- 查詢只使用上述三個欄位
SELECT user_id, status, order_date FROM orders WHERE user_id = 123 AND status = 'paid';

不當使用 NULL 欄位作為索引
MySQL 允許索引 NULL，但在統計資訊上可能造成估算不準確，導致執行計畫不理想。
建議：若欄位常為 NULL，考慮改為 NOT NULL 並使用預設值，或在查詢時加上 IS NOT NULL 條件。

實際應用場景

場景	索引策略	為什麼需要
電商平台的訂單查詢 `SELECT * FROM orders WHERE user_id = ? AND order_date BETWEEN ? AND ? ORDER BY order_date DESC`	複合索引 `INDEX idx_orders_user_date (user_id, order_date)`	同時滿足 `WHERE` 與 `ORDER BY`，避免回表與排序成本
社群網站的好友搜尋 `SELECT id, name FROM users WHERE username LIKE 'john%'`	單欄位索引 `INDEX idx_users_username (username(20))`	前綴 `LIKE` 可使用普通索引，提升搜尋速度
部落格全文搜尋 `SELECT id, title FROM posts WHERE MATCH(content) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE);`	FULLTEXT 索引 `FULLTEXT idx_posts_content (content)`	文字內容搜尋需要倒排索引，普通索引無法支援
物流系統的地理範圍查詢 `SELECT * FROM locations WHERE ST_Contains(poly, point);`	SPATIAL 索引 `SPATIAL INDEX idx_locations_geom (geom)`	空間查詢依賴 R‑Tree 結構，才能快速定位點與多邊形的關係
報表系統的日常匯總 `SELECT product_id, SUM(quantity) FROM sales WHERE sale_date >= CURDATE() - INTERVAL 30 DAY GROUP BY product_id;`	索引 `INDEX idx_sales_date_product (sale_date, product_id)`	篩選日期與分組欄位同時在索引中，減少掃描行數與排序成本

總結

索引是提升查詢效能的關鍵工具，但同時會增加寫入負擔與磁碟空間需求。
了解 B‑Tree、左前綴原則與覆蓋索引，才能在設計資料表時選擇最適合的索引類型。
建立索引前先以 EXPLAIN 檢視執行計畫，確認索引真的被使用；建立後持續監控 SHOW STATUS LIKE 'Handler_read%'; 以及 performance_schema 的相關指標。
避免過度索引、索引過長、忽視更新成本，是保持系統穩定與可擴展的最佳實踐。

掌握了「什麼是索引」以及「何時使用」的原則後，你就能在 MySQL 中針對不同業務需求，靈活地打造高效、可維護的資料庫結構，讓應用程式在面對海量資料時仍能保持快速回應。祝你在實務開發中玩得開心、查詢更快！