MySQL 索引建立與優化核心細節：從時機選擇到組合索引最左原則

索引是 MySQL 查詢性能的核心杠桿，但「建了索引就快」是危險誤區。本文梳理索引創建時機、模糊查詢對策、組合索引最左前綴原則、索引緩存配置、維護成本權衡五大核心細節，幫助開發者建立科學的索引策略。

一、 索引創建時機：三大觸發場景

當某列出現在以下位置時，優先評估建索引收益：

1. WHERE 過濾條件：WHERE status = 1、WHERE create_time > '2024-01-01'
2. JOIN 關聯條件：ON a.user_id = b.id
3. ORDER BY / GROUP BY 排序分組列：ORDER BY create_time DESC、GROUP BY department_id

反例：SELECT * FROM t WHERE id > 100（主鍵已隱含索引）、WHERE gender = 'M'（基數極低，全表掃描更快）。

二、 模糊查詢：LIKE '%xxx' 無法用普通索引

• LIKE 'abc%'（前綴固定）可用 B-Tree 索引範圍掃描。
• LIKE '%abc' / LIKE '%abc%'（前導通配符）不可用 普通索引，必走全表掃描。

解決方案：

1. 全文索引：ALTER TABLE t ADD FULLTEXT INDEX ft_content (content); + MATCH(content) AGAINST('keyword' IN BOOLEAN MODE)。適用于自然語言文本，中文需分詞器插件（ngram/parser）。
2. 反向存儲/前綴表：存儲 REVERSE(col) 列，查 LIKE 'cba%'。
3. 外部搜索引擎：Elasticsearch/OpenSearch，專業全文檢索場景首選。

三、 組合索引：最左前綴原則（核心鐵律）

定義組合索引 INDEX idx_a_b_c (a, b, c)，等價於擁有三個虛擬索引：(a)、(a,b)、(a,b,c)。

能用到索引的查詢模式

列順序設計原則

1. 區分度高（基數大）的列放左邊：Cardinality 越大，過濾效果越好。
2. 等值查詢列放左邊，範圍/排序列放右邊：範圍條件（>, <, LIKE 'abc%'）會阻斷後續列使用索引。
3. 覆蓋索引優化：若 SELECT a, b FROM t WHERE a=1 AND b=2，索引 (a,b) 直接覆蓋查詢，無需回表。

四、 索引緩存：Key Buffer / Buffer Pool 配置

InnoDB 將數據頁與索引頁統一緩存在 Buffer Pool，無需單獨配置索引緩存。innodb_buffer_pool_size 是 InnoDB 性能的單一最關鍵參數。

五、 索引維護成本：寫入性能的隱形殺手

• 每次 INSERT/UPDATE/DELETE 都需同步更新所有相關索引（B-Tree 分裂/合併、頁寫入）。
• 索引越多、越寬、基數越低，寫入放大越嚴重。
• mysqlreport / SHOW GLOBAL STATUS 關注 Com_insert, Com_update, Com_delete 佔比，評估讀寫比例。

實用原則

1. 單表索引數量建議 ≤ 5 個（主鍵除外），超過需嚴格論證。
2. 定期 ANALYZE TABLE 更新統計信息，防止優化器誤判（Cardinality 過期導致錯誤索引選擇）。
3. 刪除未使用/重複索引：SELECT * FROM sys.schema_unused_indexes (MySQL 5.7+ sys schema) 發現無用索引。
4. 主鍵設計：優先自增整數，避免 UUID/字符串主鍵導致頁分裂、二級索引膨脹（二級索引葉子節點存主鍵值）。

總結

索引優化是「業務查詢模式驅動結構設計」的動態過程：
1. 慢查詢日誌發現問題 → 2. EXPLAIN 分析計劃 → 3. 按最左前綴設計/調整組合索引 → 4. 驗證讀寫權衡 → 5. 監控統計信息鮮度。
切記：沒有完美的索引，只有適合當前業務負載的索引。