mysql查询速度慢如何优化_通过添加合适的索引提升性能

索引并非万能，查询慢常见原因包括WHERE未用索引字段、函数/类型转换致失效、未加LIMIT返回大量行；需用EXPLAIN分析执行计划，遵循最左前缀等原则，并权衡读写性能。

为什么加了索引查询还是慢

给表加了索引，查询速度却没起色？这事儿在数据库运维里可太常见了。问题往往出在几个关键细节上：要么是WHERE条件压根没用到索引字段，要么是用了函数或发生了类型转换导致索引“罢工”，还有一种情况是查询一口气要返回海量数据，却忘了加LIMIT来约束。

长期稳定更新的攒劲资源： >>>点此立即查看<<<

举个典型的例子：SELECT * FROM users WHERE YEAR(create_time) = 2023。这个YEAR()函数一包裹，create_time字段上就算有索引也完全失效了，数据库只能老老实实做全表扫描。

遇到这种情况，别急着怀疑人生，可以按下面几步来排查：

• 首先，请出EXPLAIN这个神器。仔细看执行计划，重点关注type字段（是不是range或ref这类高效类型）、key字段（实际用了哪个索引）、以及rows字段（预估要扫描多少行）。
• 其次，在写查询时要有意识地避开那些“索引杀手”：避免在索引列上做计算、小心隐式的类型转换（比如字符串字段和数字直接比较）、还有像LIKE '%abc'这种以通配符开头的模糊查询。
• 最后，如果用的是复合索引，一定要牢记最左前缀原则。比如索引是(a, b, c)，那么WHERE a=1 AND b=2就能用上，但WHERE b=2 AND c=3就不行，因为跳过了最左边的a。

哪些字段适合建索引

那么，到底该给哪些字段建索引呢？一个简单的判断标准是：那些高频出现在WHERE、JOIN ON、ORDER BY、GROUP BY子句里的字段，绝对是优先候选。但话说回来，索引也不是越多越好——每多一个索引，写入数据时的负担就重一分，同时还会占用额外的磁盘和内存空间。

具体操作时，可以把握这几个要点：

• 主键字段会自动拥有聚簇索引，不需要再额外创建。
• 外键字段强烈建议加上索引，否则在进行JOIN操作或者触发DELETE CASCADE时，可能会引发恼人的锁表问题。
• 选择区分度高的字段（比如email）建单列索引，效果远好于区分度低的字段（比如只有0/1两种状态的status）。
• 更聪明的做法是，把经常一起出现的过滤条件“打包”成一个复合索引。例如，如果业务里频繁出现这样的查询：WHERE category_id = AND is_deleted = 0 ORDER BY created_at DESC，那么直接建一个(category_id, is_deleted, created_at)的复合索引，往往能事半功倍。

如何安全地添加索引不锁表

在线上生产环境给表加索引，最怕的就是长时间锁表，影响业务。好消息是，从MySQL 5.6版本开始，引入了ALGORITHM=INPLACE选项来支持在线DDL。但这里有个坑：并非所有操作都真正“免锁”。比如给大表加一个普通索引，在5.6到5.7版本中，默认行为仍然可能锁表。直到8.0版本，多数的DDL操作才真正实现了在线执行。

因此，在生产环境操作前，务必谨慎：

• 先在从库或者影子库上模拟操作，验证执行时间和资源消耗，做到心里有数。
• 执行时，显式声明算法和锁类型：ALTER TABLE t ADD INDEX idx_name (col) ALGORITHM=INPLACE, LOCK=NONE;。如果这条命令报错，就说明当前环境不支持真正的无锁添加，千万别强行执行。
• 对于数据量特别大的表（比如千万级以上），可以考虑使用pt-online-schema-change这类专业工具。它的原理是通过创建触发器和影子表来实现双写，从而在变更过程中最大程度避免锁表。
• 另外，注意一下innodb_online_alter_log_max_size这个配置参数。如果它设置得太小，在线DDL操作过程中产生的日志可能无处安放，导致变更中途失败。

索引太多反而让查询变慢

是不是觉得索引建得越多，查询就越快？其实不然。当一张表拥有几十个索引时，查询优化器在选择执行计划时“挑花眼”、甚至选错路径的概率会显著上升。更重要的是，每一个索引背后都是一棵需要维护的B+树，每次INSERT、UPDATE、DELETE操作，都要同步更新所有相关的索引树，写入开销成倍增加。更扎心的是，有些“看起来有用”的索引，可能从来就没被使用过。

所以，定期给索引做“体检”和“瘦身”非常必要：

• 定期查询information_schema.STATISTICS系统表，或者在MySQL 8.0及以上版本中，直接使用sys.schema_unused_indexes视图，来识别那些长期未被使用的“僵尸索引”。
• 清理重复索引。例如，如果已经有一个复合索引(a, b)，那么再建一个单列索引(a)就是完全冗余的。
• 监控Handler_read_next和Handler_read_rnd_next这两个状态变量。如果后者的值持续偏高，往往意味着查询进行了大量的排序或使用了临时表，这时候可能需要调整索引，使其能覆盖更多的查询字段。
• 最后，不要迷信“覆盖索引”就一定是灵丹妙药。如果只是为了覆盖查询中的几个小字段，而把索引的宽度扩得非常大（达到几MB），反而可能导致索引本身无法被高效地缓存进内存，拖累整体性能。

说到底，索引优化本质上是一场权衡的艺术：是追求读得更快一点，还是保证写得更稳一点；是力求查询更精准一点，还是希望存储空间更节省一点。从来没有一劳永逸的银弹方案，持续观察EXPLAIN的执行计划，并结合慢查询日志里的真实行为进行分析，才是让数据库保持健康的不二法门。