前言
在大型系统性能瓶颈中，索引设计不当导致的性能问题占比超过 60%。
经过多年的工作经历，我处理过多起数据库性能事故。
总结出索引设计的核心原则：索引不是越多越好，而是越精准越好。
这篇文章跟大家一起聊聊设计索引的 10 条军规，希望对你会有所帮助。
更多精彩内容，可以查看我博客园的首页，里面有我发表过的历史文章。
一、理解业务场景
理解业务场景，它是索引设计的基石。
错误示例：盲目添加索引
-- 未分析业务场景就创建索引CREATE INDEX idx_all_columns ON orders (customer_id, product_id, status, create_time);
复制代码
正确实践：业务场景分析矩阵
业务场景分析流程图如下：
深度洞察：某电商系统通过业务分析，将订单查询性能从 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.5msCREATE INDEX idx1 ON test_table(col1);INSERT INTO test_table VALUES (...) -- 单索引：0.8msCREATE 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 ordersWHERE 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 ordersWHERE 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 倍。
五、数据类型优化
数据类型优化，它是索引大小的隐形杠杆。
常见类型空间占用：
优化案例：
-- 优化前：使用字符串存储IPCREATE 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 ordersWHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2023-06-01';
-- 优化后查询SELECT * FROM ordersWHERE 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 selectivity30FROM products;
复制代码
前缀长和区分度对比：
空间节省：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_rowsFROM mysql.innodb_index_statsWHERE table_name = 'orders';
复制代码
碎片化影响曲线：
维护建议：每月对核心表执行索引维护，碎片率超过 30%必须重建。
十、监控与调优
监控与调优，它是索引的生命周期管理。
索引使用分析：
-- 查看未使用索引SELECT object_schema, object_name, index_nameFROM performance_schema.table_io_waits_summary_by_index_usageWHERE index_name IS NOT NULLAND count_star = 0AND object_schema NOT IN ('mysql', 'sys');
复制代码
索引监控体系：
真实案例：某金融系统通过索引监控，清理 200+无效索引，写性能提升 50%。
总结
业务驱动：索引设计始于业务场景分析
左前缀优先：复合索引必须遵守最左前缀原则
适度精简：警惕过度索引的写放大效应
覆盖为王：优先考虑覆盖索引解决方案
类型优化：用小而精的数据类型降低索引体积
函数规避：避免在索引列上使用函数
前缀压缩：大文本字段使用前缀索引
NULL 处理：重要字段避免 NULL 值
定期维护：建立索引维护机制
持续监控：构建索引生命周期管理体系
优秀的索引设计，是在查询效率与维护成本间找到完美平衡点。
索引是一把双刃剑，用得好所向披靡，用不好反伤己身。
文章转载自：苏三说技术
原文链接：https://www.cnblogs.com/12lisu/p/18960989
体验地址：http://www.jnpfsoft.com/?from=001YH
评论