高質(zhì)量索引的十條軍規(guī)

作者：蘇三 2025-07-01 00:45:00

前言在大型系統(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ì)有所幫助。

前言

在大型系統(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ò)度索引，它是寫操作的隱形殺手。

索引代價(jià)計(jì)算公式：

寫操作代價(jià) = 數(shù)據(jù)寫入 + ∑(索引寫入)

索引影響對(duì)比實(shí)驗(yàn)：

-- 測(cè)試表
CREATETABLE test_table (
    idINT PRIMARY KEY,
    col1 VARCHAR(20),
    col2 VARCHAR(20),
    col3 VARCHAR(20)
);

-- 添加索引前后寫入性能對(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

索引寫入耗時(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ì)比：覆蓋索引減少磁盤I/O，查詢速度提升5-10倍。

五、數(shù)據(jù)類型優(yōu)化

數(shù)據(jù)類型優(yōu)化，它是索引大小的隱形杠桿。

常見(jiàn)類型空間占用：

數(shù)據(jù)類型	字節(jié)數(shù)	索引大小（百萬(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[重寫條件]
    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');