MySQL 表索引設計 9 大核心原則：從創建到優化的完整指南

前言

索引是查詢性能的核心杠桿，但「索引越多越好」是危險誤區。本文總結 9 大索引設計原則，涵蓋何時建、怎麼建、何時刪、如何驗證，助你構建精準高效的索引體系。

原則 1：主鍵、外鍵必須有索引

• 主鍵自動創建聚簇索引 (InnoDB)
• 外鍵列必須手動創建索引 (InnoDB 不自動創建)，否則修改父表時需鎖定子表全表
• 建議：所有關聯鍵 (JOIN ON 列) 都建索引

原則 2：數據量 > 300 行的表應建索引

• 小表 (< 300 行) 全表掃描可能比索引查找更快 (優化器會自動選擇)
• 大表無索引 = 災難性全表掃描
• 經驗值：300 行是優化器切換策略的大致閾值

原則 3：經常 JOIN 的表，關聯字段必建索引

-- 大表關聯查詢
SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id  -- o.user_id、u.id 都需索引
WHERE u.status = 1;

• 驅動表 (WHERE 過濾後行數少) 的關聯鍵建索引
• 被驅動表的關聯鍵必須建索引 (否則 Block Nested Loop 極慢)

原則 4：WHERE/ORDER BY/GROUP BY 高頻列建索引

• 選擇性高 (唯一值多/基數大) 的列優先
• 組合查詢模式：WHERE a=1 AND b=2 ORDER BY c → 復合索引 (a,b,c)
• 避免在低選擇性列 (性別、狀態、布爾值) 建單列索引

原則 5：索引建在高選擇性列上

原則 6：索引建在小字段上，大字段用前綴索引

• VARCHAR(255)、TEXT、BLOB 建全列索引 → 索引巨大、I/O 高
• 前綴索引：INDEX idx_email_20 (email(20))
• 選擇前綴長度：SELECT COUNT(DISTINCT LEFT(email,20))/COUNT(*) FROM users > 0.9 即可
• 收益：索引體積減 50%+，內存命中率提升

原則 7：復合索引需嚴謹分析，遵循最左前綴

設計決策樹

需要復合索引嗎？
├─ 多列經常 AND 組合出現在 WHERE → ✅ 是
├─ 多列經常一起 ORDER BY / GROUP BY → ✅ 是
├─ 單列查詢極少 → ✅ 適合復合
└─ 單列查詢頻繁 → ❌ 拆分為單列索引

列順序規則

1. 最左放最高選擇性/最常用過濾列
2. 範圍查詢列 (>, <, >=, <=, LIKE '%...') 放最後，後面列無法用索引
3. 排序列放在過濾列之後

復合索引 = 多個索引 (最左前綴特性)

INDEX (a, b, c) 等價於提供：
(a, b, c)
(a, b)
(a)
-- 注意：(b)、(c)、(b,c) 無法使用此索引

過度索引判斷

• 已有單列索引 (a)、(b)，又建復合 (a,b) → 通常可刪除復合
• 復合索引列數 > 3 → 仔細評估必要性，考慮拆分

原則 8：高頻寫入表，嚴控索引數量

原則 9：定期清理無用索引

-- 1. 查看索引使用統計 (5.6+ performance_schema / sys schema)
SELECT * FROM sys.schema_unused_indexes;

-- 2. 手動分析
SHOW INDEX FROM table_name;
-- 對比查詢日志、慢查詢、EXPLAIN 實際使用情況

-- 3. 刪除無用索引
DROP INDEX idx_unused ON table_name;
-- 或
ALTER TABLE table_name DROP INDEX idx_unused;

無用索引判斷標準

• 創建超 3 個月從未被 EXPLAIN 使用
• sys.schema_unused_indexes 顯示
• 與其他索引功能重疊 (如前綴包含關係)
• 導致 INSERT/UPDATE 明顯變慢 (通過性能基線對比)

索引設計工作流

1. 收集核心 SQL (慢查詢 TOP 50、高頻 API SQL)
2. 分析 WHERE/JOIN/ORDER BY/GROUP BY 涉及列
3. 計算列選擇性 (COUNT(DISTINCT col)/COUNT(*))
4. 設計復合索引 (最左前綴、範圍列後置)
5. 評估寫入影響 (索引數 vs 寫入 QPS)
6. 上線前 EXPLAIN 驗證
7. 上線後監控: sys.schema_unused_indexes、寫入延遲
8. 定期 (月/季) 清理無用索引

總結

索引設計鐵律：主鍵外鍵必建、高選擇性優先、復合索引最左前綴、大字段用前綴、寫入頻高嚴控數量、定期清理無用。每個索引都要有「被核心 SQL 使用」的證據，否則就是累贅。建立「索引變更 → EXPLAIN 驗證 → 上線觀察 → 定期審計」閉環。

1. 上一篇文章： PHP 防 SQL 注入 7 大核心防禦要點：從參數過濾到架構層面
2. 下一篇文章： MySQL 匯入匯出完整操作手冊：資料庫、資料表、Source 命令三大場景

MySQL 綜合教學

CentOS 上將 MySQL 從 5.5 升級至 5.6 完整實戰記錄

前言 本文詳細記錄在 CentOS 6.3 環境下，將 My

MySQL 啟動報錯 1067：埠號被佔用排查與解決全記錄

MySQL 服務啟動失敗錯誤 1067 完整排查指南 Win

PHP連接MySQL中文亂碼終極解決方案：SET NAMES與字符集全鏈路配置

亂碼根因：字符集協商失敗 MySQL 4.1+引入字符集系統

Java 存儲中文到 MySQL 報 Incorrect string value 解決：字符集診斷與修復全流程

Java 應用向 MySQL 寫入中文報錯 Incorrec