MySQL 索引優化策略全攻略：從建立到維護的 7 大核心原則

MySQL 索引優化策略全攻略：從建立到維護的 7 大核心原則

索引建立後能否生效取決於查詢寫法與設計模式。本文彙總 7 大實戰優化策略，涵蓋查詢改寫、複合索引設計、覆蓋索引、排序優化、鎖優化與維護工具。

1. 索引欄位禁用運算式/函數

-- ❌ 失效
SELECT * FROM users WHERE UPPER(name) = 'ABC';
-- ✅ 生效
SELECT * FROM users WHERE name = 'ABC';

原則：索引欄位保持「乾淨」，所有轉換移至常數側。

2. 前綴索引與選擇性平衡

• 選擇性 = COUNT(DISTINCT col) / COUNT(*)，越接近 1 越好
• 長字串欄位 (VARCHAR(255)) 只索引前綴：INDEX idx_name (name(10))
• 前綴長度選擇：在區分度與空間佔用間平衡，通常 10-20 字元足夠

3. 複合索引欄位順序三原則

1. 高頻優先：最常用於 WHERE/JOIN 的欄位放最左
2. 高選擇性優先：區分度高的欄位靠左 (避免狀態碼等低選擇性欄位佔據最左)
3. 小寬度優先：定長/短欄位靠左 (INT < BIGINT < VARCHAR < TEXT)

遵循最左前綴原則：INDEX (a,b,c) 支援 a, a,b, a,b,c 查詢。

4. 覆蓋索引 - 避免回表

定義： 索引葉節點已包含查詢所需所有欄位，無需回叢集索引查完整行。

-- 索引
INDEX idx_order (user_id, status, created_at)

-- 查詢可走覆蓋索引
SELECT user_id, status, created_at FROM orders WHERE user_id = 123;

優勢： 減少隨機 I/O → 順序掃描索引、避免 InnoDB 雙重查詢、減少 MyISAM 系統呼叫。

失效場景： SELECT *、雙 % LIKE、引擎不支援。

5. 索引掃描優化 ORDER BY

條件：

1. 索引欄位順序與 ORDER BY 完全一致
2. 所有欄位排序方向 (ASC/DESC) 完全一致
3. ORDER BY 欄位全屬關聯查詢的第一張表 (驅動表)

6. 索引優化鎖競爭

• 精準索引 → 鎖定行數少 → 降低鎖衝突機率
• 加速執行 → 鎖持有時間短 → 釋放快 → 併發度高

7. 清理重複與冗餘索引

工具檢測：pt-duplicate-key-checker -h localhost

維護指令

-- 更新統計資訊 (優化器決策依據)
ANALYZE TABLE table_name;

-- 整理碎片 (InnoDB 重建表)
OPTIMIZE TABLE table_name;
-- 或
ALTER TABLE table_name ENGINE=InnoDB;

查詢未使用索引

-- 5.7+ performance_schema
SELECT * FROM sys.schema_unused_indexes;