三種建立索引語法對比

1. CREATE TABLE 同步建立 (推薦新表)

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(191) NOT NULL,
    name VARCHAR(100) NOT NULL,
    status TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_email (email),
    KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. ALTER TABLE 追加 (線上 DDL，5.6+ 無鎖大多數操作)

-- 普通索引
ALTER TABLE users ADD INDEX idx_name (name);

-- 唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_phone (phone);

-- 複合索引 (順序至關重要！)
ALTER TABLE orders ADD INDEX idx_uid_status_date (user_id, status, created_at);

-- 主鍵 (表只能有一個)
ALTER TABLE logs ADD PRIMARY KEY (id);

3. CREATE INDEX 獨立建立 (語法糖，等同 ALTER TABLE)

CREATE INDEX idx_email ON users (email);
CREATE UNIQUE INDEX uk_username ON users (username);
-- 不支援建立 PRIMARY KEY

索引類型選擇決策樹

需要唯一約束？
  ├─ 是 → UNIQUE INDEX (含主鍵)
  └─ 否 → 查詢模式？
          ├─ 精確匹配 (=, IN) + 排序 + 範圍 → B+Tree (預設)
          ├─ 全文搜尋 (MATCH...AGAINST) → FULLTEXT (5.6+ InnoDB 支援)
          ├─ GIS 空間查詢 → SPATIAL (R-Tree)
          └─ Memory 表極高頻等值查找 → HASH (重啟丟失，慎用)

複合索引設計鐵律：最左前綴原則

索引 (a, b, c) 的 B+Tree 排序順序：先按 a，再按 b，最後按 c。

查詢條件	能否用到索引	用到列數
WHERE a=1 AND b=2 AND c=3	✅	3 (完整匹配)
WHERE a=1 AND b=2	✅	2 (最左前綴)
WHERE a=1	✅	1 (最左前綴)
WHERE b=2	❌	0 (斷層)
WHERE a=1 AND c=3	⚠️	1 (b 缺失，c 無法用)
WHERE a=1 ORDER BY b, c	✅	3 (過濾+排序)
WHERE a=1 ORDER BY c	⚠️	1 (b 缺失導致 filesort)

查看與驗證索引

-- 1. 顯示所有索引詳情
SHOW INDEX FROM users;
-- 關鍵欄位：Key_name, Seq_in_index, Column_name, Cardinality, Sub_part, Index_type

-- 2. 執行計劃驗證
EXPLAIN SELECT * FROM users WHERE email='test@example.com';
-- 期望：type=ref/const, key=uk_email, rows=1, Extra=Using index

-- 3. 統計資訊更新 (Cardinality 準確度影響優化器決策)
ANALYZE TABLE users;

刪除索引

-- 刪除普通/唯一索引
DROP INDEX idx_name ON users;
ALTER TABLE users DROP INDEX idx_name;

-- 刪除主鍵 (僅一個)
ALTER TABLE users DROP PRIMARY KEY;

避坑速查：10 大索引反模式

#	反模式	後果	修正
1	對低基數列建索引 (性別/狀態/布爾)	優化器拒用，白佔空間	組合索引放前面，或不建
2	對長字串全長建索引 INDEX idx_desc (description(500))	索引巨大，插入極慢	前綴索引 (19) 或全文索引
3	冗餘索引 INDEX(a), INDEX(a,b)	寫入放大 2 倍	保留 (a,b) 刪 (a)
4	複合索引列順序隨意	最左前綴失效	按 =/IN 最多 → 範圍 → 排序 順序排列
5	WHERE 列用函數 DATE(created_at)=...	索引失效	改範圍查詢 created_at >= ...
6	隱式轉換 WHERE phone=13800000000 (phone 是 varchar)	索引失效	補引號 '13800000000'
7	OR 條件跨列 WHERE a=1 OR b=2	通常全表掃描	UNION ALL 分開查詢或建聯合索引
8	外鍵列未建索引	子表操作鎖父表全表	FK 列必須有索引
9	過度索引 (單表 > 5 個索引)	寫入效能暴跌	定期 pt-duplicate-key-checker 清理
10	忽略統計資訊更新	優化器誤判走錯索引	定時 ANALYZE TABLE 或 innodb_stats_persistent=ON

進階：覆蓋索引與索引下推 (ICP)

-- 覆蓋索引：查詢列全在索引中，Extra=Using index
INDEX idx_uid_status_name (user_id, status, name)
SELECT user_id, status, name FROM users WHERE user_id=100 AND status=1;

-- ICP (5.6+)：Storage Engine 層用索引中列過濾 WHERE，減少回表
-- 索引 (user_id, status, age)
SELECT * FROM users WHERE user_id=100 AND status=1 AND age > 20;
-- ICP 生效：Extra=Using index condition

線上 DDL 注意事項 (MySQL 5.6+ / 8.0 增強)

• ALGORITHM=INPLACE, LOCK=NONE：大多數 ADD INDEX 支援線上，不阻塞讀寫。
• 大表加索引仍會佔用臨時空間 (約 1.5 倍表大小)，磁碟需預留。
• 主鍵變更、列類型變更、字符集變更通常需 ALGORITHM=COPY (鎖表)，謹慎規劃。
• 8.0 瞬間加列、刪列、重命名索引真正 INSTANT (僅元數據修改)。

索引是查詢效能的杠桿，也是寫入效能的成本。**以查詢負載為導向，以 EXPLAIN 為標尺，以 ANALYZE 為儀表**，持續迭代索引策略。