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