索引能將全表掃描 (O(N)) 降為對數級 (O(logN)),但每個索引都增加寫入開銷 (INSERT/UPDATE/DELETE 需維護 B+ 樹)、佔用磁碟空間、擴大優化器搜索空間。盲目加索引反而拖垮效能。
EXPLAIN 觀察 key_len/ref 確認命中。COUNT(DISTINCT col)/COUNT(*) > 0.1 才值得建索引;性別/狀態碼 (低基數) 建索引無意義。INT(4B) < BIGINT(8B) < CHAR(32) < VARCHAR(255)。索引越窄,單頁容納條目越多,樹高越低。INDEX idx_email_prefix (email(19)),在選擇性足夠前提下節省空間。(a,b,c) 等價於 (a)、(a,b)、(a,b,c) 三個索引。查詢必須從最左列開始連續匹配。(a,b) 再建 (a) 屬冗餘;pt-duplicate-key-checker 可自動掃描。| 類型 | InnoDB | MyISAM | Memory | 適用場景 |
|---|---|---|---|---|
| B+Tree (預設) | ✅ 聚簇+輔助 | ✅ 非聚簇 | ✅ | 範圍、排序、全值、前綴匹配 |
| Hash | ❌ (自適應哈希) | ❌ | ✅ 預設 | 精確等值查找 (=/IN) |
| Fulltext | ✅ 5.6+ | ✅ | ❌ | 全文檢索 |
| Spatial (R-Tree) | ✅ | ✅ | ❌ | GIS 地理空間 |
EXPLAIN SELECT * FROM orders WHERE user_id=100 AND status=1 ORDER BY created_at DESC LIMIT 10;
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ref | idx_uid_st | idx_uid_st| 5 | const | 12 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
type:system/const > eq_ref > ref > range > index > ALL (全表掃描最差)。key:實際使用的索引;NULL 表示無索引可用。key_len:索引使用長度,推斷複合索引用了幾列。Extra:Using index (覆蓋索引)、**Using filesort (需排序)**、**Using temporary (臨時表)** 為優化重點。查詢列完全包含於索引中,Extra: Using index,避免回表。
範例:INDEX idx_uid_status_created (user_id, status, created_at) 覆蓋 SELECT user_id, status, created_at FROM orders WHERE user_id=?。
MySQL 5.6+:Storage Engine 層直接用索引中列過濾 WHERE 條件,減少回表次數。
EXPLAIN 出現 Using index condition 表示生效。
SELECT ... FOR UPDATE 若 WHERE 不走索引,會鎖住掃描到的所有行 (含不符合條件)。-- 查看索引
SHOW INDEX FROM tbl;
-- 分析統計資訊 (更新 Cardinality)
ANALYZE TABLE tbl;
-- 檢查碎片
SELECT TABLE_NAME, INDEX_NAME, STAT_VALUE
FROM INFORMATION_SCHEMA.INNODB_INDEXES
WHERE STAT_VALUE > 0; -- 需配合 innodb_stats_persistent
-- 重建索引 (在線 DDL 5.6+)
ALTER TABLE tbl DROP INDEX idx_old, ADD INDEX idx_new (...);
-- 或優化表 (重建所有索引)
OPTIMIZE TABLE tbl;
| 反模式 | 後果 | 修正 |
|---|---|---|
WHERE YEAR(dt) = 2023 | 函數導致索引失效 | WHERE dt >= '2023-01-01' AND dt < '2024-01-01' |
LIKE '%keyword%' | 前導通配符無法用索引 | 全文索引 / ES / 反向索引技巧 |
col != 100 / NOT IN | 範圍查詢導致全掃 | col > 100 OR col < 100 / NOT EXISTS |
隱式類型轉換 WHERE phone = 13800000000 | 字符列轉數字,索引失效 | WHERE phone = '13800000000' |
| 複合索引列順序錯誤 | 最左前綴斷裂 | 按區分度高→低、WHERE/JOIN 順序排列 |
索引設計是數據庫效能的基石,測試驗證 > 經驗拍腦袋。善用 EXPLAIN、SHOW INDEX、pt-index-usage,讓每個索引都物有所值。