MySQL 索引與查詢優化器深度解析：從原理到實戰調優

索引本質

索引是排序後的資料結構（B+ Tree），將無序表變為有序存取路徑。MyISAM 索引檔與資料檔分離，InnoDB 聚簇索引將資料與主鍵同頁儲存。

索引優勢

• 避免全表掃描，快速定位列
• 多表關聯大幅減少組合數（避免笛卡兒積）
• MIN/MAX 直接走索引極值
• 加速 ORDER BY/GROUP BY 消除 filesort
• 覆蓋索引：SELECT indexed_col FROM tbl WHERE indexed_col > 10 僅讀索引不回表

索引代價

• 磁碟空間：每個索引佔用額外儲存
• 寫入開銷：INSERT/UPDATE/DELETE 需同步維護所有索引
• 優化器誤判：統計資訊過期可能選錯索引

索引欄位選擇原則

1. 出現在 WHERE、JOIN ON、ORDER BY、GROUP BY 的欄位
2. 優先建立唯一索引（主鍵、UNIQUE）
3. 字串建立前綴索引：INDEX idx_name (name(10))，平衡選擇度與空間
4. 複合索引遵循最左前綴：INDEX (a,b,c) 支援 a、a,b、a,b,c，不支援 b、c、b,c
5. 單表索引數建議 ≤ 5 個，視寫入頻率調整
6. 索引適用運算子：=、>、>=、<、<=、BETWEEN、IN、LIKE 'prefix%'

查詢優化器決策輔助

-- 1. 分析查詢計畫
EXPLAIN SELECT * FROM tbl WHERE col = 1;

-- 關鍵欄位：
-- type: ALL(全掃) > index > range > ref > eq_ref > const > system
-- key: 實際使用索引
-- rows: 預估掃描列數
-- Extra: Using index(覆蓋)、Using where、Using temporary、Using filesort

-- 2. 更新統計資訊
ANALYZE TABLE tbl;  -- MyISAM/InnoDB

-- 3. 強制連接順序
SELECT * FROM tbl1 STRAIGHT_JOIN tbl2 ON ...;

-- 4. 慢查詢日誌分析
mysqldumpslow -s t /var/log/mysql/slow.log

優化器限制與繞過技巧

實戰檢查清單

1. EXPLAIN 確認 type 非 ALL、key 非 NULL
2. rows 預估是否合理（遠大於實際回傳可能統計過期）
3. 覆蓋索引消除回表
4. 避免 SELECT *，僅查詢所需欄位
5. 大表分區配合分區裁剪