高質量索引的十條軍規
前言
在大型系統性能瓶頸中，索引設計不當導致的性能問題占比超過60%。
經過多年的工作經歷，我處理過多起數據庫性能事故。
總結出索引設計的核心原則：索引不是越多越好，而是越精準越好。
這篇文章跟大家一起聊聊設計索引的10條軍規，希望對你會有所幫助。
一、理解業務場景
理解業務場景，它是索引設計的基石。
錯誤示例：盲目添加索引
-- 未分析業務場景就創建索引
CREATE INDEX idx_all_columns ON orders (customer_id, product_id, status, create_time);正確實踐：業務場景分析矩陣
查詢類型 | 頻率 | 響應要求 | 數據量 | 索引策略 |
用戶訂單查詢 | 高 | <100ms | 百萬級 | (user_id, status) |
商品訂單統計 | 中 | <1s | 千萬級 | (product_id) |
訂單狀態更新 | 極高 | <50ms | 百萬級 | (status) |
業務場景分析流程圖如下：
深度洞察：某電商系統通過業務分析，將訂單查詢性能從2s優化到50ms，TPS提升300%。
二、最左前綴原則
最左前綴原則，它是復合索引的靈魂。
索引結構解析
查詢匹配規則：
-- 命中索引
SELECT * FROM orders
WHERE user_id = 1001 AND status = 'PAID';
-- 命中索引（最左前綴）
SELECT * FROM orders
WHERE user_id = 1001;
-- 未命中索引（違反最左前綴）
SELECT * FROM orders
WHERE status = 'PAID';原理剖析：復合索引按聲明順序構建B+樹，缺失左側列時將無法使用索引結構。
三、避免過度索引
避免過度索引，它是寫操作的隱形殺手。
索引代價計算公式：
寫操作代價 = 數據寫入 + ∑(索引寫入)索引影響對比實驗：
-- 測試表
CREATETABLE test_table (
idINT PRIMARY KEY,
col1 VARCHAR(20),
col2 VARCHAR(20),
col3 VARCHAR(20)
);
-- 添加索引前后寫入性能對比
INSERTINTO test_table VALUES (...) -- 無索引：0.5ms
CREATEINDEX idx1 ON test_table(col1);
INSERTINTO test_table VALUES (...) -- 單索引：0.8ms
CREATEINDEX idx2 ON test_table(col2);
CREATEINDEX idx3 ON test_table(col3);
INSERTINTO test_table VALUES (...) -- 三索引：1.8ms索引寫入耗時如下圖所示：
黃金法則：單表索引不超過5個，單個索引字段不超過3列。
四、覆蓋索引
覆蓋索引，它是查詢性能的終極大招。
未使用覆蓋索引：
EXPLAIN SELECT order_no, amount
FROM orders
WHERE user_id = 1001 AND status = 'PAID';執行計劃：
| id | select_type | table | type | key | Extra |
|----|-------------|--------|------|-------------------|-------------|
| 1 | SIMPLE | orders | ref | idx_user_status | Using where|使用覆蓋索引：
-- 創建覆蓋索引
CREATE INDEX idx_covering ON orders(user_id, status, order_no, amount);
EXPLAIN SELECT order_no, amount
FROM orders
WHERE user_id = 1001 AND status = 'PAID';執行計劃：
| id | select_type | table | type | key | Extra |
|----|-------------|--------|------|--------------|--------------------------|
| 1 | SIMPLE | orders | ref | idx_covering | Using index |性能對比：覆蓋索引減少磁盤I/O，查詢速度提升5-10倍。
五、數據類型優化
數據類型優化，它是索引大小的隱形杠桿。
常見類型空間占用：
數據類型 | 字節數 | 索引大小（百萬行） |
BIGINT | 8 | 15MB |
INT | 4 | 7.5MB |
MEDIUMINT | 3 | 5.6MB |
CHAR(32) | 32 | 61MB |
VARCHAR(32) | 變長 | 20-50MB |
優化案例：
-- 優化前：使用字符串存儲IP
CREATETABLE access_log (
idBIGINT,
ip VARCHAR(15),
INDEX idx_ip (ip)
);
-- 優化后：轉換為整型存儲
CREATETABLE access_log (
idBIGINT,
ip INTUNSIGNED,
INDEX idx_ip (ip)
);空間節省：IP字段索引大小從78MB降至12MB，內存命中率提升40%。
六、函數陷阱
函數陷阱，它是索引失效的元兇。
索引失效案例：
-- 創建索引
CREATE INDEX idx_create_time ON orders(create_time);
-- 索引失效查詢
SELECT * FROM orders
WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2023-06-01';
-- 優化后查詢
SELECT * FROM orders
WHERE create_time BETWEEN '2023-06-01 00:00:00' AND '2023-06-01 23:59:59';函數使用原則：
graph LR
A[查詢條件] --> B{是否包含函數}
B -->|是| C[索引可能失效]
B -->|否| D[正常使用索引]
C --> E[重寫條件]
E --> D性能對比：日期范圍查詢優化后，執行時間從1200ms降至15ms。
七、前綴索引
前綴索引，它是大文本字段的救星。
創建方法：
-- 原始字段索引
CREATE INDEX idx_product_desc ON products(description); -- 無法創建，text字段過大
-- 前綴索引
CREATE INDEX idx_product_desc_prefix ON products(description(20));長度選擇算法：
-- 計算最佳前綴長度
SELECT
COUNT(DISTINCT LEFT(description, 10)) / COUNT(*) AS selectivity10,
COUNT(DISTINCT LEFT(description, 20)) / COUNT(*) AS selectivity20,
COUNT(DISTINCT LEFT(description, 30)) / COUNT(*) AS selectivity30
FROM products;前綴長和區分度對比：
前綴長度 | 區分度 | 建議 |
10 | 0.65 | 不足 |
20 | 0.92 | 推薦 |
30 | 0.95 | 邊際收益低 |
空間節省：500萬行數據的描述字段，索引從1.2GB降至120MB。
八、NULL值處理
NULL值處理，它是索引中的幽靈。
NULL值索引問題：
-- 包含NULL的索引
CREATEINDEX idx_email ONusers(email);
-- 查詢問題
SELECT * FROMusersWHERE email ISNULL; -- 可能不走索引
-- 優化方案
ALTERTABLEusersMODIFY email VARCHAR(255) NOTNULLDEFAULT'';NULL值索引存儲結構：
圖片
最佳實踐：重要查詢字段設置NOT NULL DEFAULT，默認值根據業務設置如0、''、'N/A'等。
九、索引維護
索引維護，它是性能穩定的守護者。
維護腳本示例：
-- 重建碎片化索引
ALTERTABLE orders REBUILDINDEX idx_user_status;
-- 更新統計信息
ANALYZETABLE orders UPDATE HISTOGRAM ONstatusWITH32 BUCKETS;
-- 監控腳本
SELECT
index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb,
index_type,
table_rows
FROM mysql.innodb_index_stats
WHERE table_name = 'orders';碎片化影響曲線：
圖片
維護建議：每月對核心表執行索引維護，碎片率超過30%必須重建。
十、監控與調優
監控與調優，它是索引的生命周期管理。
索引使用分析：
-- 查看未使用索引
SELECT
object_schema,
object_name,
index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema NOT IN ('mysql', 'sys');索引監控體系：
圖片
真實案例：某金融系統通過索引監控，清理200+無效索引，寫性能提升50%。
總結
- 業務驅動：索引設計始于業務場景分析
- 左前綴優先：復合索引必須遵守最左前綴原則
- 適度精簡：警惕過度索引的寫放大效應
- 覆蓋為王：優先考慮覆蓋索引解決方案
- 類型優化：用小而精的數據類型降低索引體積
- 函數規避：避免在索引列上使用函數
- 前綴壓縮：大文本字段使用前綴索引
- NULL處理：重要字段避免NULL值
- 定期維護：建立索引維護機制
- 持續監控：構建索引生命周期管理體系
優秀的索引設計，是在查詢效率與維護成本間找到完美平衡點。
索引是一把雙刃劍，用得好所向披靡，用不好反傷己身。
