MySQL 索引類型全解析：普通、唯一、主鍵、複合、全文索引建立與最佳實踐

索引分類速覽

建立語法三種方式

-- 1. 建表時指定
CREATE TABLE t (
  id INT PRIMARY KEY,
  email VARCHAR(100),
  name VARCHAR(20),
  city VARCHAR(50),
  UNIQUE INDEX uk_email (email),
  INDEX idx_name_city (name(10), city)
) ENGINE=InnoDB;

-- 2. ALTER TABLE 新增 (線上 DDL 5.6+)
ALTER TABLE t ADD INDEX idx_city (city);
ALTER TABLE t ADD UNIQUE INDEX uk_phone (phone);

-- 3. CREATE INDEX (等同 ALTER TABLE)
CREATE INDEX idx_age ON t (age);
CREATE FULLTEXT INDEX ft_content ON t (content);

刪除索引

DROP INDEX idx_name ON t;
ALTER TABLE t DROP INDEX uk_email;
-- 主鍵刪除
ALTER TABLE t DROP PRIMARY KEY;

複合索引核心：最左前綴原則

索引 INDEX idx_a_b_c (a, b, c) 等效於擁有：

• (a)
• (a, b)
• (a, b, c)

**可用**：WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3、ORDER BY a, b。
**不可用**：WHERE b=2、WHERE c=3、WHERE a=1 AND c=3 (跳過 b)。

前綴索引：長字串優化利器

-- VARCHAR(255) 欄位，前 10 字元區分度已達 95%+
ALTER TABLE t ADD INDEX idx_name_prefix (name(10));

優點：索引體積縮小 80%+，INSERT/UPDATE 更快，緩衝池容納更多索引頁。
查詢：WHERE name LIKE 'abc%' 可用，WHERE name LIKE '%abc' 不可用。

索引建立時機判斷

1. WHERE、JOIN ON、ORDER BY、GROUP BY 出現的欄位。
2. 選擇性高 (Cardinality/Rows > 0.1) 的欄位優先。
3. 小表 (< 1000 行) 或低選擇性欄位 (性別、狀態) **不建索引**。
4. LIKE 'prefix%' 可用索引，LIKE '%suffix' 無效。

索引濫用副作用

• 寫入放大：每次 INSERT/UPDATE/DELETE 需同步維護所有索引 B+Tree。
• 空間膨脹：複合索引、長字串索引佔用大量磁碟/記憶體。
• 優化器誤判：索引過多導致統計資訊不準，選錯執行計畫。

使用索引 6 大注意事項

1. NULL 值不進索引：複合索引任一欄為 NULL，該行不進索引。設計時設 NOT NULL DEFAULT ''。
2. 短索引：長字串用前綴長度。
3. 索引列不參與運算：WHERE YEAR(dt) < 2023 → WHERE dt < '2023-01-01'。
4. 避免 NOT IN / != / <> ：導致全表掃描，改用 NOT EXISTS 或 LEFT JOIN ... IS NULL。
5. 覆蓋索引：SELECT a,b FROM t WHERE c=1 建 INDEX (c,a,b)，Extra 顯示 Using index。
6. 定期 ANALYZE TABLE：更新 Cardinality 統計，修正優化器誤判。

總結

索引類型選擇口訣：主鍵必建、唯一業務鍵建 UNIQUE、多條件組合建複合、長字串用前綴、全文搜尋用 FULLTEXT。以 EXPLAIN 驗證生效，定期審視 sys.schema_unused_indexes 刪除無用索引，保持索引集合精簡高效。