首页 > 数据库 >mysql索引命中后速度依然慢的原因_mysqlI/O瓶颈排查

mysql索引命中后速度依然慢的原因_mysqlI/O瓶颈排查

来源：互联网 2026-04-20 12:58:32

EXPLAIN显示走索引但查询仍慢，主因是索引扫描行数过多或回表开销大；需检查rows_examined_per_scan、filtered、覆盖索引、ORDER BY是否匹配索引、I/O等待、临时表落盘及缓冲池命中率。 EXPLAIN显示走索引但查询慢的原因一个常见的误区是，只要看到EXPLAI

EXPLAIN显示走索引但查询仍慢，主因是索引扫描行数过多或回表开销大；需检查rows_examined_per_scan、filtered、覆盖索引、ORDER BY是否匹配索引、I/O等待、临时表落盘及缓冲池命中率。

EXPLAIN显示走索引但查询慢的原因

一个常见的误区是，只要看到EXPLAIN输出里type显示ref或range，key字段也有值，就认为索引已经发挥了最大效能，查询理应很快。但实际情况往往更复杂：索引命中了，速度却上不来。这背后，大概率是两大原因在起作用——「索引扫描行数过多」和「巨大的回表开销」。

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

例如，一个查询条件是 WHERE status = 1 AND create_time > ‘2024-01-01’。即便status字段上有索引，如果表中status=1的记录占比高达80%，那么MySQL依然需要扫描索引树上80%的节点。这还不算完，如果查询是SELECT *，数据库还得根据索引找到的主键ID，再回到主键聚簇索引里把整行数据捞出来，这个“回表”过程会产生大量的随机I/O，性能瓶颈立刻显现。

因此，遇到这种情况，不能只看EXPLAIN的基础信息，需要深入分析：

使用 EXPLAIN FORMAT=JSON 命令，重点关注 rows_examined_per_scan（每次扫描检查的行数）和 filtered（过滤比例）这两个字段。它们能告诉你，实际扫描的数据量是否远超预期。
检查SELECT列表。是否使用了SELECT *？尝试只查询索引已经包含的字段，也就是利用「覆盖索引」，直接从索引中取数，彻底避免回表。
审视复合索引的设计。索引(a,b,c)对于查询WHERE b=1 ORDER BY c基本上是无效的，因为最左前缀原则没满足。索引字段的顺序必须与查询条件和排序需求高度匹配。

判断磁盘 I/O 瓶颈的方法

有时，慢查询的原因不能全归咎于SQL。当索引扫描量看起来合理，执行计划也挑不出毛病，但查询响应时间波动大，一上并发就下降，这时就该关注底层——磁盘I/O很可能成了瓶颈。

如何验证？可以从数据库内部和操作系统两个层面入手：

在MySQL内部，查看 SHOW PROFILE 或者查询性能模式（performance_schema）中的事件，观察 wait/io/file 类型的等待事件是否占据了主导地位。
在操作系统层面，使用 iostat -x 1 命令实时观察磁盘状态。如果%util（磁盘利用率）持续高于80%，或者await（平均等待时间）超过20ms（机械硬盘）或2ms（SSD），I/O压力就很大了。
使用 pt-ioprofile 这样的工具，可以直接定位到MySQL进程正在频繁读取哪些文件（比如ibdata1、各个表的.ibd文件），非常直观。
检查缓冲池配置。关键指标是 Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests，如果这个比值大于0.01，说明很多数据无法从内存缓冲池中读取，不得不进行物理磁盘读，缓存命中率堪忧。此时，可能需要评估是否要调大 innodb_buffer_pool_size。

ORDER BY 与索引不匹配导致性能问题

另一个典型的性能陷阱出现在排序环节。即便WHERE条件完美命中了索引，但如果ORDER BY的字段不在这个索引里，或者顺序、方向不匹配，MySQL就无法利用索引的有序性来避免排序。执行计划里会出现 Using filesort。

这个filesort阶段可能把大量数据拉到内存里排序，如果内存不够，还会用到磁盘临时文件，瞬间占用大量I/O和CPU资源。

要避开这个坑，可以这么做：

确保ORDER BY的字段已经包含在联合索引的最右侧位置，并且排序方向一致。例如，索引是(a, b, c ASC)，那么ORDER BY a, b, c是有效的；而ORDER BY a, b, c DESC在MySQL 8.0之前可能无法高效利用索引降序扫描（8.0+版本因支持降序索引而改善）。
尽量避免在ORDER BY子句中使用RAND()函数或对字段进行函数运算（如ORDER BY UPPER(name)），这类操作基本上会宣告索引排序失效。
对于分页查询，特别是深度分页（LIMIT 10000, 20），考虑使用基于游标的分页（利用上一次查询的最大ID或时间戳）来替代传统的LIMIT offset, N，避免每次都要从头扫描和排序大量数据。

临时表写磁盘导致 I/O 激增的排查与优化

当查询涉及到GROUP BY、DISTINCT、复杂的多表JOIN（且缺少合适的连接索引）时，MySQL为了完成计算，可能会在内部创建临时表。如果中间结果集的大小超过了tmp_table_size和max_heap_table_size这两个参数中较小的那个值，临时表就会从内存转移到磁盘上，默认使用MyISAM引擎。这个落盘过程会产生大量的磁盘I/O，成为性能瓶颈。

如何发现和应对？

监控状态：执行 SHOW STATUS LIKE ‘Created_tmp%’。如果 Created_tmp_disk_tables 这个计数器增长得非常快，就说明磁盘临时表创建频繁。
分析执行计划：EXPLAIN 输出中如果出现 Using temporary，就表明使用了临时表。用 EXPLAIN FORMAT=JSON 可以查看更详细的 using_temporary_table 字段。
调整参数：适当调大 tmp_table_size 和 max_heap_table_size（通常建议两者设为相同值），让更多临时表操作在内存中完成。但要注意，总大小不宜超过物理内存的20%，以防引发OOM（内存溢出）风险。
根治优化：最根本的方法还是优化SQL本身。例如，为GROUP BY的字段添加索引，或者将复杂查询拆解为多个利用索引的子查询，从而减少中间结果集的大小。