后端工程师 | MySQL索引：SQL调优

▌ 技术引导
MySQL索引是SQL调优的硬骨头，没点实战经验别想搞明白。我见过太多人把索引当成万能钥匙，结果数据库性能反而更差。索引不是随便加的，得看查询模式、数据分布、写入频率，甚至得看业务场景。如果你的查询是WHERE id = 1，加索引没用；但如果是WHERE name LIKE '%aaa%'，那索引可能彻底失效。我之前在某个电商项目里，误将全表扫描的慢查询加了复合索引，导致写入速度降了50%，读取速度反而没提。踩坑后才明白，索引的维护成本不一定比扫描低。真正的调优，是用EXPLAIN看执行计划，分析索引选择，再结合业务数据量变化做调整。别迷信“加索引就快”，得用具体命令测试，比如用pt-index-usage查真实使用情况，别让索引成为系统负担。

▌ 技术参考
一 索引的本质是数据结构，不是万能的，但能有效减少I/O。在MySQL中，B+树是默认的索引类型，它平衡了查询速度和写入效率。索引的字段顺序很重要，比如联合索引（name, age）能覆盖WHERE name = 'xxx' AND age > 18的查询，但无法覆盖WHERE age > 18的独立查询。我之前接手一个用户表，主键是自增ID，但业务经常查手机号，结果发现手机号字段没有索引，导致每次查询都全表扫描，平均耗时接近1秒。这种情况下，我新建了基于手机号的索引，但没注意区分度，导致索引反而成为负担。

二 索引的创建方式有CREATE INDEX、ALTER TABLE、优化器提示等。比如创建一个联合索引的命令：CREATE INDEX idx_user_name_age ON user (name, age); 这个命令会生成一个B+树，包含name和age字段。但要注意，索引字段的顺序直接影响查询效率。我见过某个项目为了优化LIKE查询，把索引顺序调反了，导致查询速度反而更差。索引的维护成本需要权衡，特别是写入频繁的表，索引越多，写入越慢。比如在日志表中，如果频繁插入，索引可能会影响吞吐量，这时候要考虑按时间分区、使用覆盖索引或延迟索引。

三 索引失效的情况很多，最常见的是使用函数、隐式类型转换或前导模糊查询。比如WHERE YEAR(create_time) = 2026，create_time字段虽然有索引，但被YEAR函数调用后，索引就不起作用了。我之前在处理一个订单状态查询时，用了status = '已发货'，但status字段是枚举类型，数据库优化器会自动处理，没问题。但如果是status = '已发货' AND create_time BETWEEN '2025-01-01' AND '2026-06-30'，那联合索引的顺序就很重要了。此外，如果索引字段是前导模糊，如LIKE '%aaa%'，那索引几乎没用，这时候得考虑其他方案，比如全文索引或倒排索引。

四 使用EXPLAIN查看执行计划是SQL调优的核心。我通常在查询前加EXPLAIN，看是否命中索引，是否使用了filesort或temporary。比如EXPLAIN SELECT FROM user WHERE name LIKE '%aaa%'，如果type显示ALL，说明全表扫描，这时候得考虑是否需要重建索引。另外，MySQL的索引优化器有时会做一些奇怪的决策，比如在有多个索引的情况下选择效率低的。我之前在一个分页查询中，优化器选择了索引扫描而不是范围查询，导致查询效率下降。这时候可以使用FORCE INDEX提示让优化器强制使用某个索引。

五 索引的维护和重建对性能影响很大。比如对一个10亿行的订单表做重建索引，可能耗时几个小时，甚至导致锁表。这时候得考虑在线重建或者使用pt-online-schema-change工具。我之前在做索引优化时，发现某个索引碎片率高达30%，直接用ALTER TABLE user REBUILD INDEX idx_user_name_age，结果数据库卡了整整一天。后来换用pt-online-schema-change，虽然过程复杂，但没锁表，也不会中断服务。此外，定期分析表也是必须的，比如ANALYZE TABLE user，这样优化器能更准确地评估索引使用情况。

六 索引的存储空间和I/O开销需要提前估算。比如一个VARCHAR(255)的字段，如果索引了，可能会占用500MB以上的空间，特别是如果表很大。我在一个金融系统里，误把某些高频查询字段都加了索引，结果磁盘空间被占满，不得不手动删除。这时候得看字段的区分度，比如性别字段加索引没意义，而身份证号这种唯一值字段就适合加。另外，索引的更新成本也很高，每次写入都要同步更新索引，可能影响吞吐量。比如在一个写入量大的日志表中，如果索引太多，写入速度会明显下降。

七 全文索引是处理文本搜索的利器，但不是每种场景都适用。我之前用MyISAM引擎做了全文索引，结果发现查询速度反而比普通索引慢，因为需要额外的存储和计算开销。后来换成InnoDB，配合MySQL 8.0的全文索引功能，性能提升了不少。不过全文索引对短文本效果一般，比如订单号这类字段不太适合。另外，全文索引的分词规则也很关键，比如用ngram_tokenizer来处理中文，但得注意分词粒度，太细会影响查询效率。

八 索引的覆盖查询可以大幅提升性能。比如查询只需要name和age字段，而这两个字段都在索引里，那数据库可以直接从索引中读取，不用回表。我之前在写一个统计查询时，发现没用覆盖索引，每次都要访问主表，导致查询时间翻倍。后来我创建了一个联合索引，把查询的字段都包含进去，结果性能提升了40%。但覆盖查询也有代价，索引占用的存储空间更大，写入成本也更高。特别是在高并发写入场景下，得权衡利弊，看是否值得。

九 索引的失效往往是因为查询条件变化，或者数据分布不均。我之前在某个项目里，用了WHERE name LIKE 'aaa%'，查询速度很快，但后来业务加入了模糊匹配，变成WHERE name LIKE '%aaa%'，这时候索引完全失效。这种情况下，得考虑使用全文索引或倒排索引，但不是所有数据库都支持。另外，如果某个索引的使用率低于1%，那它很可能是个累赘。这时候可以用pt-index-usage工具来监控索引使用情况，及时清理无效索引。

十 频繁写入的表不适合加太多索引，但读取密集的表可以适当增加。比如在电商系统中，商品表的查询频率很高，但写入频率相对较低，这时候可以考虑加多个索引。但如果是订单表，每天上亿条数据写入，那索引数量就得控制。我之前在某个订单表里，加了10个索引，导致写入速度从每秒5000条降到800条，严重影响业务。后来删掉了一些低频查询的索引，性能恢复了。索引的添加要遵循“最小必要”原则，避免过度设计。

十一 索引的类型选择也很关键，覆盖索引、唯一索引、主键索引、哈希索引各有优劣。比如在高并发的点查询场景，主键索引效率最高，因为它是唯一且顺序存储的。但如果是范围查询，比如WHERE create_time BETWEEN '2025-01-01' AND '2025-06-30'，那就更适合用B+树索引。哈希索引在MySQL中不支持，只能在Memory存储引擎中使用。我之前在缓存表里用哈希索引，结果某个查询条件改变了，导致索引失效，不得不重新设计。

十二 索引的使用和查询优化往往需要结合具体业务场景。比如在某个购物车表里，用户ID和商品ID经常一起查询，这时候加联合索引很有必要。但如果是按时间排序的查询，那索引可能只对部分条件有效。我有一次在优化秒杀活动中的订单表，发现WHERE user_id = 1000 AND create_time > '2026-01-01'查询效率低，于是加了联合索引，但因为create_time是时间戳类型，索引还加了索引前缀，比如只覆盖前8字节，这样既能提高效率又减少存储空间。这种细节在实战中往往决定成败。

十三 索引的维护需要定期监控，避免索引失效或性能下降。我用过的工具包括pt-index-usage、SHOW INDEX FROM table、ANALYZE TABLE等。比如用pt-index-usage可以查出哪些索引没有被使用，哪些索引有高使用率。在某个数据仓库项目里，我发现某个联合索引的使用率只有0.5%，直接删掉后节省了200MB的磁盘空间。但删索引前必须确认业务逻辑没有依赖，否则会引发严重问题。定期查看执行计划，是索引优化的日常工作。

十四 使用索引优化器提示时要谨慎。比如FORCE INDEX可以让优化器强制使用某个索引，但可能带来性能问题。我之前在一次高并发查询中用了FORCE INDEX，结果发现这个索引在某些情况下效率更低，反而导致数据库负载升高。这时候得看具体情况，如果优化器选择错误，可以考虑更换索引类型或改变字段顺序。此外，对于某些复杂查询，可能需要使用USE INDEX来指定使用某个索引，但必须确保该索引能覆盖查询条件。

十五 索引的使用还需要考虑存储引擎的选择。比如InnoDB适合高并发读写，而MyISAM更适合读多写少的场景。我之前在一个读写分离的系统里，把某些查询表从MyISAM迁移到InnoDB，结果发现索引的维护成本增加了，但查询性能反而更好。另外，对于大字段和blob类型，加索引可能效果不大，甚至浪费资源。这时候要考虑是否使用冗余字段或分表策略。在某个日志系统里，我用分表按日期分割，每个表只加了必要的索引，效果比全表加索引好得多。