索引是 MySQL 查詢性能的核心杠桿。本文系統梳理 EXPLAIN 輸出字段含義、慢查詢日誌分析、索引設計原則 (最左前綴、覆蓋索引、回表)、常見反模式及優化檢查清單，幫助開發者從「能跑」走向「跑得快」。
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
| 列名 | 含義 | 關注重點 |
|---|---|---|
id | 查詢序號，標識執行順序 | 相同 id 從上到下執行；id 越大優先級越高 |
select_type | 查詢類型 | SIMPLE (無子查詢/UNION)、PRIMARY (最外層)、SUBQUERY、DERIVED (衍生表)、UNION |
table | 訪問的表 (或衍生表別名) | 確認是否掃描預期表 |
partitions | 匹配的分區 (分區表) | 檢查分區裁剪是否生效 |
type | 訪問類型 (核心指標) | 從好到壞：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL (全表掃描)。目標：至少達到 ref，避免 ALL/index。 |
possible_keys | 可能用到的索引 | 為空說明無可用索引，需建立 |
key | 實際使用的索引 | 若為 NULL 則未用索引 |
key_len | 索引鍵長度 (字節) | 越短越好；推斷聯合索引用了幾列 (如 INT=4, VARCHAR(20) UTF8=60+2) |
ref | 與索引比較的列或常量 | const 表示常量，列名表示關聯查詢 |
rows | 預估掃描行數 | 越小越好；與實際行數偏差大時需 ANALYZE TABLE |
filtered | 按條件過濾後剩餘行數百分比 | 越高越好；低說明索引列選擇性差 |
Extra | 額外信息 (關鍵優化線索) | 好：Using index (覆蓋索引)、Using where (Server 層過濾)。壞： Using filesort (需排序)、Using temporary (臨時表)、Using join buffer (Join Buffer)、Impossible WHERE (條件永假)。 |
(a,b,c) 僅當查詢條件包含 a、a,b、a,b,c (順序不限，優化器會重排) 時生效；b、c、b,c 不走索引。SELECT a,b FROM t WHERE c=1 配合索引 (c,a,b)，Extra 顯示 Using index，避免回表，極大提升性能。COUNT(DISTINCT col)/COUNT(*) 越接近 1 越適合建索引；性別、狀態等低選擇性列單列索引無效。INDEX(name(10))，平衡大小與選擇性。!=/<>、IS NULL/IS NOT NULL、LIKE '%abc' (前導通配)、隱式類型轉換 (WHERE varchar_col = 123)、對索引列運算 (WHERE YEAR(date_col)=2023)、OR 條件兩側索引不一致。slow_query_log=ON, long_query_time=1 (秒), log_queries_not_using_indexes=ON。EXPLAIN 分析 → 調整索引/重寫 SQL/調整參數 → 驗證效果 → 固化變更。| 現象 | 根因 | 對策 |
|---|---|---|
全表掃描 (type=ALL) | 無索引、索引失效、小表優化器選擇掃描 | 建立合適索引、強制索引 FORCE INDEX、重寫 SQL |
| Using filesort | ORDER BY 列無索引、聯合索引順序不符、方向混用 (ASC/DESC) | 建立排序列索引、調整索引列順序、MySQL 8.0 降序索引 |
| Using temporary | GROUP BY/DISTINCT 無索引、UNION、JOIN 產生臨時表 | 索引覆蓋 GROUP BY 列、避免不必要 DISTINCT |
| 回表過多 | 非覆蓋索引查詢需回主鍵索引取列 | 構建覆蓋索引、只查主鍵再關聯、冗余列 |
| 鎖爭用/死鎖 | 大事務、索引缺失導致鎖升級、死鎖循環等待 | 拆分大事務、補全索引、固定加鎖順序、降低隔離級別 |
SHOW INDEX FROM tbl 檢查重複/冗余索引 (如 PRIMARY KEY(id) + INDEX(id))。SELECT * FROM sys.schema_unused_indexes (MySQL 5.7+ sys schema) 找出長期未用索引並刪除。ANALYZE TABLE 更新統計信息，修正優化器誤判。Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests 命中率 > 99%。EXPLAIN 回歸測試，防止版本迭代引入性能退化。
