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