1. 索引优化的核心逻辑与价值

数据库索引就像图书馆的目录系统——没有索引时找书需要遍历整个书架（全表扫描），而合理的索引设计能让查询效率提升几个数量级。但索引并非越多越好，就像图书馆目录卡过多反而会增加维护成本一样，每个额外的索引都会带来写入时的性能开销和存储空间占用。

我在处理一个千万级用户表时曾遇到典型场景：某核心查询从2秒优化到20毫秒，仅通过调整索引策略。但随后发现批量导入性能下降了40%，这就是典型的索引权衡案例。优化索引本质上是在查询速度与写入开销之间寻找最佳平衡点。

2. 索引类型选型策略

2.1 B-Tree索引的适用场景

作为最普遍的索引类型，B-Tree适合等值查询和范围查询。在电商订单系统中，对order_date + user_id的联合索引能高效支持"查询某用户某时间段的订单"这类操作。但要注意：

经验：联合索引字段顺序决定有效性。把选择性高的字段（如user_id）放在前面，比order_date + user_id的组合效率更高，因为日期字段通常重复值较多。

2.2 哈希索引的快速定位

适用于等值查询且不排序的场景，如内存表。某社交平台用哈希索引优化用户session查找，QPS从5k提升到80k。但无法支持WHERE create_time > '2023-01-01'这类范围查询。

2.3 全文索引的文本搜索

处理商品描述等文本内容时，FULLTEXT索引比LIKE模糊查询高效得多。一个知识库系统改用全文索引后，搜索响应时间从3秒降至200毫秒。注意需要配置最小词长度（MySQL默认4字符）。

3. 索引失效的七大陷阱

3.1 最左前缀原则违反

建立(status, category)索引后，以下查询将失效：

SELECT * FROM products WHERE category = 'electronics'

因为跳过了最左的status字段。就像电话簿按"姓+名"排序时，单查"名"是无法利用索引的。

3.2 隐式类型转换

user_id是varchar但查询用数字：

SELECT * FROM users WHERE user_id = 12345 -- 失效

我在日志分析系统中遇到过因此导致的性能骤降，查询从0.1秒恶化到12秒。

3.3 索引列参与运算

SELECT * FROM orders WHERE YEAR(create_time) = 2023 -- 失效

应该改为范围查询：

WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'

4. 高级优化技巧实战

4.1 覆盖索引的妙用

某报表系统需要频繁查询用户姓名和等级：

SELECT username, level FROM users WHERE vip_status = 1

建立(vip_status, username, level)索引后，引擎无需回表直接返回数据，查询速度提升8倍。

4.2 索引下推优化

MySQL 5.6+的ICP特性，能在索引遍历时就进行WHERE条件过滤。某物流系统升级后，扫描行数从50万降至3万，CPU使用率降低65%。

4.3 索引合并策略

当多个单列索引通过OR组合时：

SELECT * FROM articles WHERE tag_id = 5 OR author_id = 10

使用INDEX_MERGE比全表扫描快，但不如设计合理的联合索引。曾通过改为(tag_id, author_id)联合索引，将执行时间从1.2秒降至0.03秒。

5. 监控与维护实践

5.1 索引使用率分析

通过performance_schema查看未使用索引：

SELECT * FROM sys.schema_unused_indexes

某金融系统定期执行此检查，清理了30%的冗余索引，使写入TPS提升25%。

5.2 索引碎片整理

对于频繁更新的表，每月执行：

ALTER TABLE orders ENGINE=InnoDB

某电商平台发现重组后索引大小减少40%，查询性能回归初始水平。

5.3 压力测试验证

使用sysbench模拟真实负载时，要监控：

• 索引缓冲区命中率（应>95%）
• 每秒索引扫描次数
• 临时表创建频率

某次压测发现添加索引后，虽然查询变快但系统整体吞吐量下降15%，最终采用部分索引方案折中。

6. 特殊场景解决方案

6.1 海量数据的分区索引

按时间分区的日志表，只在当前分区建立完整索引，历史分区仅保留必要索引。某IoT平台采用此策略，索引维护成本降低70%。

6.2 低基数字段的优化

对性别这种只有2-3个值的列，直接全表扫描可能比索引更快。可采用过滤索引：

CREATE INDEX idx_active_men ON users(gender) WHERE gender = 'M' AND is_active = 1

6.3 JSON字段的索引

MySQL 8.0+支持：

CREATE INDEX idx_profile ON users((CAST(profile->'$.address.city' AS CHAR(30))))

某用户画像系统借此将地理位置查询提速20倍。

索引优化是门需要持续实践的艺术。每次架构评审时，我都会问团队三个问题：这个索引解决什么问题？它会被如何使用？维护成本是否可接受？这三个问题的答案，往往就是性能优化的关键所在。