您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 索引設計與使用避坑指南：從原理到實戰

編輯：MySQL 綜合教學

MySQL 索引設計與使用避坑指南：從原理到實戰

接續配置調優，索引是 SQL 層面最高性價比的優化手段。本文系統總結索引建立原則、使用技巧、EXPLAIN 深度解讀、SQL 重寫模式，助你避開 90% 索引陷阱。

一、表結構設計基礎

儲存引擎選擇

原則：預設 InnoDB，極少數唯讀分析場景考慮 MyISAM/ColumnStore。

欄位類型優化

PROCEDURE ANALYSE() 輔助建議最佳類型
非 NULL 欄位設 NOT NULL（索引必須）
定長優於變長：CHAR(32) > VARCHAR(32)（頻繁更新表）
整數優於字串：IP 存 INT UNSIGNED、狀態用 TINYINT

二、索引建立八大原則

搜索/排序/分組列建索引：WHERE、ORDER BY、GROUP BY、JOIN ON 條件列
高選擇性優先：唯一索引 > 高基數 > 低基數（性別/狀態勿建）
短索引原則：前綴索引 INDEX(name(10)) 減少 IO、提高命中率
複合索引替代多單列：單查詢僅用一索引，多條件組合查詢必須複合索引
最左前綴：INDEX(a,b,c) 支援 a、a,b、a,b,c，不支援 b、c、b,c
克制建立：僅核心查詢路徑建立，每表建議 ≤5 個索引
覆蓋索引：SELECT a,b FROM t WHERE c=1 → INDEX(c,a,b) 避免回表
慢查詢驅動：long_query_time=1 + pt-query-digest 定期分析

三、索引使用七大避坑

引擎	適用場景	特點
InnoDB	95%+ 一般業務	事務、行鎖、MVCC、外鍵、崩潰安全
MyISAM	唯讀/日誌/全文檢索	表鎖、無事務、壓縮表、count(*) 極快

❌ 錯誤寫法	✅ 正確寫法	原理
WHERE col * 2 < 100	WHERE col < 50	索引列參與運算/函數導致失效
WHERE YEAR(dt) = 2023	WHERE dt >= '2023-01-01' AND dt < '2024-01-01'	函數破壞索引有序性
WHERE name LIKE '%abc'	WHERE name LIKE 'abc%'	前導通配符無法走索引
WHERE int_col = '123'	WHERE int_col = 123	隱式轉換導致索引失效
ORDER BY a ASC, b DESC	ORDER BY a ASC, b ASC	混合排序無法用索引（8.0 支援降序索引）
WHERE a=1 OR b=2	UNION ALL 分拆	OR 通常導致全掃，除非兩側都有索引且優化器選擇 index_merge
SELECT * FROM t1, t2 WHERE t1.id=t2.id	SELECT * FROM t1 INNER JOIN t2 ON t1.id=t2.id	顯式 JOIN 語法，優化器更易優化

四、強制索引與連接控制

-- 強制使用指定索引
SELECT * FROM t FORCE INDEX(idx_name) WHERE name = 'xxx';

-- 強制連接順序（驅動表優先）
SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.id = t2.t1_id WHERE t1.type = 1;

-- 忽略錯誤索引
SELECT * FROM t IGNORE INDEX(bad_idx) WHERE ...

⚠️ 慎用 FORCE INDEX：資料分佈變化可能導致反效果，優先優化統計資訊 ANALYZE TABLE。

五、SQL 重寫模式庫

1. 子查詢 → JOIN

-- 相關子查詢
SELECT a.id, (SELECT MAX(created) FROM posts WHERE author_id = a.id) AS latest FROM authors a;
-- 重寫
SELECT a.id, MAX(p.created) AS latest FROM authors a INNER JOIN posts p ON a.id = p.author_id GROUP BY a.id;

-- IN 子查詢
SELECT song_id FROM song_lib WHERE singer_id IN (SELECT singer_id FROM singer_lib WHERE first_char='A');
-- 重寫
SELECT a.song_id FROM song_lib a INNER JOIN singer_lib b ON a.singer_id=b.singer_id AND b.first_char='A';

2. UPSERT 模式

INSERT INTO action_today(user_id, song_id, action_count) VALUES(1,1,1)
ON DUPLICATE KEY UPDATE action_count = action_count + 1;

3. 避免游標

游標效能極差，改用：臨時表 + 多表 UPDATE/JOIN、應用層批次處理。

六、EXPLAIN 深度解讀

EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id WHERE t1.type = 1;

欄位	關鍵值	含義
type	system > const > eq_ref > ref > range > index > ALL	存取類型，目標 ≥ ref
possible_keys	索引名列表	理論可用索引
key	實際使用索引	NULL = 全表掃描
key_len	位元組數	判斷複合索引用了幾列
rows	預估掃描行數	越小越好，1 最理想
Extra	Using index / Using where / Using filesort / Using temporary	filesort/temporary 需優化

進階用法

EXPLAIN EXTENDED + SHOW WARNINGS：查看優化器重寫後 SQL
EXPLAIN PARTITIONS：分區表剪枝驗證
EXPLAIN FORMAT=JSON (5.6+)：結構化輸出，含成本資訊

七、維護與監控

-- 更新統計資訊（輕量，線上可執行）
ANALYZE TABLE t;

-- 檢查表健康度
CHECK TABLE t;

-- 整理碎片/回收空間（InnoDB 重建表）
OPTIMIZE TABLE t;
-- 或
ALTER TABLE t ENGINE=InnoDB;

-- 索引使用情況
SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_key 高 = 索引有效
-- Handler_read_rnd_next 高 = 全掃嚴重，需加索引