大家好，我是苏三，又跟大家见面。
前言
在大型系统性能瓶颈中，索引设计不当导致的性能问题占比超过60%。
经过多年的工作经历，我处理过多起数据库性能事故。
总结出索引设计的核心原则：索引不是越多越好，而是越精准越好 。
这篇文章跟大家一起聊聊设计索引的10条军规，希望对你会有所帮助。
最近建了一些工作内推群，各大城市都有，欢迎各位HR和找工作的小伙伴进群交流，群里目前已经收集了不少的工作内推岗位。
扫码加苏三的微信：li_su223，备注：所在城市，即可进群。
一、理解业务场景
理解业务场景，它是索引设计的基石。
错误示例：盲目添加索引
-- 未分析业务场景就创建索引
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值
- 定期维护 ：建立索引维护机制
- 持续监控 ：构建索引生命周期管理体系
优秀的索引设计，是在查询效率与维护成本间找到完美平衡点 。
索引是一把双刃剑，用得好所向披靡，用不好反伤己身。
最后欢迎加入苏三的星球，你将获得：商城微服务实战、AI开发项目课程、苏三AI项目、秒杀系统实战、商城系统实战、秒杀系统实战、代码生成工具、系统设计、性能优化、技术选型、底层原理、Spring源码解读、工作经验分享、痛点问题、面试八股文等多个优质专栏。
还有1V1答疑、修改简历、职业规划、送书活动、技术交流。
扫描下方二维码，加入星球可以优惠40元（只有20个优惠名额）：
目前星球已经更新了5200+篇优质内容，还在持续爆肝中.....