用好索引的10条军规

前言

在大型系统性能瓶颈中，索引设计不当导致的性能问题占比超过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+树，缺失左侧列时将无法使用索引结构。

三、避免过度索引

避免过度索引，它是写操作的隐形杀手。

索引代价计算公式：

写操作代价 = 数据写入 + ∑(索引写入)

索引影响对比实验：

-- 测试表
CREATE TABLE test_table (
    id INT PRIMARY KEY,
    col1 VARCHAR(20),
    col2 VARCHAR(20),
    col3 VARCHAR(20)
);

-- 添加索引前后写入性能对比
INSERT INTO test_table VALUES (...) -- 无索引：0.5ms
CREATE INDEX idx1 ON test_table(col1);
INSERT INTO test_table VALUES (...) -- 单索引：0.8ms
CREATE INDEX idx2 ON test_table(col2);
CREATE INDEX idx3 ON test_table(col3);
INSERT INTO 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
CREATE TABLE access_log (
    id BIGINT,
    ip VARCHAR(15),
    INDEX idx_ip (ip)
);

-- 优化后：转换为整型存储
CREATE TABLE access_log (
    id BIGINT,
    ip INT UNSIGNED,
    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';

函数使用原则：

性能对比：日期范围查询优化后，执行时间从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的索引
CREATE INDEX idx_email ON users(email);

-- 查询问题
SELECT * FROM users WHERE email IS NULL; -- 可能不走索引

-- 优化方案
ALTER TABLE users MODIFY email VARCHAR(255) NOT NULL DEFAULT '';

NULL值索引存储结构：

最佳实践：重要查询字段设置NOT NULL DEFAULT，默认值根据业务设置如0、''、'N/A'等。

九、索引维护

索引维护，它是性能稳定的守护者。

维护脚本示例：

-- 重建碎片化索引
ALTER TABLE orders REBUILD INDEX idx_user_status;

-- 更新统计信息
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 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%。

总结

1. 业务驱动：索引设计始于业务场景分析
2. 左前缀优先：复合索引必须遵守最左前缀原则
3. 适度精简：警惕过度索引的写放大效应
4. 覆盖为王：优先考虑覆盖索引解决方案
5. 类型优化：用小而精的数据类型降低索引体积
6. 函数规避：避免在索引列上使用函数
7. 前缀压缩：大文本字段使用前缀索引
8. NULL处理：重要字段避免NULL值
9. 定期维护：建立索引维护机制
10. 持续监控：构建索引生命周期管理体系

优秀的索引设计，是在查询效率与维护成本间找到完美平衡点。

索引是一把双刃剑，用得好所向披靡，用不好反伤己身。

最后说一句(求关注，别白嫖我)

如果这篇文章对您有所帮助，或者有所启发的话，帮忙关注一下我的同名公众号：苏三说技术，您的支持是我坚持写作最大的动力。

求一键三连：点赞、转发、在看。

关注公众号：【苏三说技术】，在公众号中回复：进大厂，可以免费获取我最近整理的10万字的面试宝典，好多小伙伴靠这个宝典拿到了多家大厂的offer。

本文收录于我的技术网站：http://www.susan.net.cn

原创作者: 12lisu 转载于: https://www.cnblogs.com/12lisu/p/18960989