MySQL 索引缺點與實戰使用注意事項全解析

索引的代價（缺點）

• 寫入性能下降：INSERT/UPDATE/DELETE 需同步維護索引文件
• 佔用磁盤空間：組合索引在大表上膨脹明顯
• 非銀彈：海量數據需結合優化查詢、最優索引設計，單靠加索引不夠

使用索引 5 大核心技巧

1. 索引不包含 NULL 值

單列含 NULL 不入索引；組合索引任一列含 NULL 導致該列對組合索引無效。建議：字段默認 NOT NULL。

2. 使用短索引（前綴索引）

CHAR(255) 列若前 10-20 字符區分度高，僅索引前綴：INDEX(name(10))。節省空間、提升 I/O 效率。

3. 索引列排序與 ORDER BY

MySQL 單查詢僅用一個索引。WHERE 已用索引時，ORDER BY 列不再用索引。建議：默認排序滿足需求時避免排序；多列排序建立組合索引。

4. LIKE 語句優化

• LIKE 'aaa%' → 可用索引
• LIKE '%aaa%' → 全表掃描，索引失效

5. 避免列上運算導致索引失效

-- 錯誤：全表掃描
SELECT * FROM users WHERE YEAR(adddate) < 2007;
-- 正確：索引生效
SELECT * FROM users WHERE adddate < '2007-01-01';

同理避免 NOT IN、<> 等導致索引失效的操作。

索引類型詳解

普通索引 (INDEX/KEY)

純加速查詢，選擇區分度高、緊湊的列（如整數）。

唯一索引 (UNIQUE)

約束數據唯一性，簡化 MySQL 管理，插入時自動檢查重複。

主鍵索引 (PRIMARY KEY)

關鍵字 PRIMARY 而非 UNIQUE，表僅能有一個主鍵。

外鍵索引

定義外鍵約束時 MySQL 自動創建內部索引輔助管理。

組合索引 (INDEX(a,b,c))

遵循最左前綴原則：可作為 a、(a,b)、(a,b,c) 索引使用，但不可用於 b、c、 (b,c)。

全文索引 (FULLTEXT)

適用大文本字段模糊搜尋，MATCH(col) AGAINST('word') 語法。注意：InnoDB 5.6+ 才支援，早期版本僅 MyISAM。

InnoDB 索引與行鎖關係

InnoDB 行級鎖基於索引實現。無合適索引時，行鎖退化為表鎖，嚴重影響並發。SELECT ... LOCK IN SHARE MODE、FOR UPDATE、DML 操作均依賴索引加鎖。

EXPLAIN 實戰分析

關鍵列解讀：

• type：system > const > eq_ref > ref > range > index > ALL（越前越好）
• key_len：越小越好（如 INT 為 4 字節）
• rows：預估掃描行數，乘積反映總掃描量
• Extra：Using temporary、Using filesort 為優化信號

總結

索引是雙刃劍：加速讀、拖慢寫、佔空間。遵循「只為高頻查詢/排序列建索引、組合索引遵循最左前綴、避免列運算、善用 EXPLAIN」原則，定期審視索引效果，刪除無效索引。