MySQL 索引設計的核心原則是 在查詢性能與存儲成本之間取得平衡。以下是經過實踐驗證的 10 大設計原則及具體實現策略：

---

一、基礎原則

原則	說明	示例/反例
1. 高頻查詢優先	為 WHERE、JOIN、ORDER BY、GROUP BY 頻繁出現的列建索引	? SELECT * FROM orders WHERE user_id=100 → 為 user_id 建索引
2. 高區分度優先	選擇區分度高的列（唯一值比例 ≈1）	? 身份證號 > 性別 ? 在 gender（僅2種值）建索引效果差
3. 最左前綴匹配	聯合索引按查詢順序從左到右排列	索引 (a,b,c) 生效場景： ? WHERE a=1 ? WHERE a=1 AND b=2 ? WHERE b=2

---

二、字段選擇原則

原則	說明	最佳實踐
4. 短字段優先	更小的索引 → 更高緩存命中率	用 SMALLINT 代替 INT 用 CHAR(10) 代替 VARCHAR(100)
5. 整型優于字符型	整型比較比字符串快，且節省空間	用 IP 轉 INT (INET_ATON()) 代替字符串存儲 IP
6. 避免 NULL 列	NULL 增加索引復雜度	建表時設置 NOT NULL DEFAULT ''

---

三、索引類型選擇

場景	推薦索引類型	優勢
7. 精確匹配	B+Tree 索引	標準場景，支持 =, >, <, BETWEEN
8. 全文搜索	FULLTEXT 索引	對 TEXT 內容高效搜索 (MATCH AGAINST)
9. 空間數據	SPATIAL 索引	地理位置計算 (GIS)
10. 哈希去重	唯一索引 (UNIQUE)	強制業務唯一性（如用戶名）

---

四、聯合索引設計策略

1. 列順序決策公式

優先級 = 查詢頻率 × 區分度

• 正確示例：
  表 orders 的查詢模式：

  SELECT * FROM orders 
  WHERE status='paid'          -- 區分度低 (3種狀態)AND create_time > '2023-01-01' -- 區分度高
  

  → 聯合索引應設為 (create_time, status)

2. 覆蓋索引優化

-- 未優化
SELECT name, email FROM users WHERE age>30; -- 優化方案：創建覆蓋索引
ALTER TABLE users ADD INDEX idx_age_name_email (age, name, email);

• ? 效果：索引覆蓋所有查詢字段，避免回表

---

五、避坑指南（常見錯誤）

錯誤做法	問題	改進方案
盲目創建索引	寫性能下降 30%~50%	用慢查詢日志定位真正需要的索引
無效索引	WHERE status=1 (status=1 占比 95%)	刪除低區分度索引
冗余索引	已有 (a,b) 又建 (a)	刪除單列索引 (a)
索引列參與運算	WHERE YEAR(create_time)=2023	改范圍查詢：WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'

---

六、高級優化技巧

1. 索引下推 (ICP)

• 啟用條件：MySQL 5.6+，聯合索引部分條件過濾
• 效果：

  -- 索引 (city, age)
  SELECT * FROM users 
  WHERE city='杭州' AND age>20;-- 5.6 前：先取所有 city='杭州' 數據 → 回表 → 過濾 age>20
  -- 5.6+：在索引層直接過濾 age>20 → 僅回表匹配行
  

2. 索引合并優化

-- 存在索引 (a) 和 (b)
SELECT * FROM table WHERE a=1 OR b=2;-- 優化器可能合并索引掃描 (Index Merge)

---

七、索引監控與維護

1. 分析索引使用率

-- 查看未使用索引
SELECT * FROM sys.schema_unused_indexes;-- 索引統計信息
SHOW INDEX FROM orders;

2. 碎片整理

-- InnoDB 索引重建
ALTER TABLE orders ENGINE=InnoDB; -- 優化索引頁
OPTIMIZE TABLE orders;

---

八、設計流程圖

九、總結：黃金準則

1. 必要性原則：只為必要的查詢建索引
2. 左前綴原則：聯合索引嚴格按查詢順序設計
3. 覆蓋索引優先：避免 SELECT * 回表開銷
4. 短小精悍：整型優于字符串，小字段優于大字段
5. 持續監控：定期清理無效索引（寫代價 > 讀收益）

📊 數據佐證：根據阿里云數據庫團隊統計，合理索引設計可使查詢性能提升 10~100 倍，降低 70% 的數據庫負載。