索引是儲存引擎用於快速查找行的資料結構,類似書的目錄。MySQL 索引在**儲存引擎層**實現,不同引擎 (InnoDB、MyISAM) 結構不同。
| 優點 | 缺點 |
|---|---|
| 大幅加速查詢 (WHERE、JOIN、ORDER BY、GROUP BY) | 寫入 (INSERT/UPDATE/DELETE) 需維護索引,降低寫效能 |
| 支援覆蓋索引 (避免回表) | 佔用磁碟空間與記憶體 (Buffer Pool) |
| 強制唯一性 (PRIMARY/UNIQUE) | 索引過多導致優化器選擇困難、統計資訊維護成本 |
INT (4B) 優於 BIGINT (8B);DATE (3B) 優於 DATETIME (8B) / CHAR(10)。INT UNSIGNED (用 INET_ATON/INET_NTOA)。INDEX idx_a_b_c (a, b, c)
-- 可用:WHERE a=1 | WHERE a=1 AND b=2 | WHERE a=1 AND b=2 AND c=3
-- 可用:ORDER BY a | ORDER BY a,b | ORDER BY a,b,c
-- 不可用:WHERE b=1 | WHERE c=1 | ORDER BY b | ORDER BY c
索引像電話簿:先按姓氏 (a) 排,同姓再按名字 (b) 排。不知道姓氏直接找名字,索引無效。
| 欄位 | 關鍵值 | 含義 |
|---|---|---|
| type | system > const > eq_ref > ref > range > index > ALL | 連接類型,越左越好;ref 以上通常用到索引 |
| possible_keys | 索引名列表 | 理論上可用的索引 |
| key | 實際使用的索引 | NULL 表示全表掃描 |
| key_len | 位元組數 | 實際用到的索引長度,推斷複合索引用了幾欄 |
| rows | 預估掃描行數 | 越小越好 |
| Extra | Using index / Using where / Using filesort / Using temporary | Using index = 覆蓋索引;Using filesort = 需外部排序,需優化 |
EXPLAIN 看 type、key、Extra。WHERE 條件欄位建立索引,高選擇性 (基數大) 欄位優先。SELECT *,只查需要欄位,爭取覆蓋索引。LIKE 'abc%' 可用索引,LIKE '%abc' 或 LIKE '%abc%' 導致全掃。WHERE YEAR(dt)=2023) 失效索引 → 改 WHERE dt >= '2023-01-01' AND dt < '2024-01-01'。OR 條件兩側皆有索引才可能用索引,否則全掃;考慮 UNION ALL 拆分。ANALYZE TABLE 更新統計資訊,讓優化器決策更準。