为何数据量更少的MySQL Flow表查询反而更慢？

表结构与索引 #

Flow表包含字段：taskId、wName、key、status、envNo、updateTime，无关联表。
已创建索引：

• index1(taskId)
• index2(wName, key, name, envNo)
• index3(name)
• index4(key)
• index5(updateTime)
• index6(envNo)

查询1详情 #

返回11条记录

select * from Flow where status = 0 and wName = 'abc' order by updateTime LIMIT 50

执行EXPLAIN结果：

• type: index
• possible_key: index2
• actual_key: index5
• rows: 3000
• Filtered: 0.05
• Extra: Using Where

查询2详情 #

返回50条记录

select * from Flow where status = 0 and wName = 'xyz' order by updateTime LIMIT 50

执行EXPLAIN结果：

• type: index
• possible_key: index2
• actual_key: index5
• rows: 108
• Filtered: 1.84
• Extra: Using Where

特定wName的总记录数 #

select count(*) from Flow where wName = 'abc' -- 返回3000条
select count(*) from Flow where wName = 'xyz' -- 返回120000条

核心问题 #

查询1耗时20秒，查询2耗时不足1秒，为何数据量更少的查询反而更慢？

补充现象：去掉LIMIT 50后，两个查询均毫秒级响应；去掉order by updateTime后，两个查询也均毫秒级响应。
注：仅该场景异常，其他情况正常，且无法修改现有索引。

推测根因与验证方案 #

已推测根因的验证方法 #

1. 数据存储碎片化

   • 验证步骤：
     • 执行SHOW TABLE STATUS LIKE 'Flow'\G，查看Data_free字段值（InnoDB引擎），如果该值远大于单条记录的平均大小，说明表存在明显碎片；
     • 查看缓冲池命中率：通过SELECT * FROM Flow WHERE wName='abc' ORDER BY updateTime，结合INFORMATION_SCHEMA.INNODB_BUFFER_PAGE查询这些数据页在缓冲池中的占比，若命中率极低，说明需要频繁从磁盘读取碎片化的页；
     • 临时验证优化：执行OPTIMIZE TABLE Flow（注意锁表风险），之后重新跑查询1，若耗时明显降低，即可确认碎片是主因。

2. 索引选择性问题导致执行计划不合理

   • 验证步骤：
     • 计算index2中wName='abc'的选择性：先执行SELECT COUNT(DISTINCT wName) FROM Flow得到总唯一值数，再计算3000/总记录数，如果该值远低于120000/总记录数，说明wName='abc'的索引选择性更低；
     • 强制使用index2执行查询1：执行SELECT * FROM Flow FORCE INDEX(index2) WHERE status = 0 AND wName = 'abc' ORDER BY updateTime LIMIT 50，如果耗时大幅降低，说明MySQL原本选的index5不是最优解，是优化器对选择性判断失误导致的；
     • 更新统计信息后再看：执行ANALYZE TABLE Flow更新表统计信息，重新跑EXPLAIN查询1，如果actual_key变成index2且耗时下降，说明是旧统计信息让优化器选了错误索引。

其他可能的原因 #

• 缓冲池冷热数据差异：wName='xyz'的记录被频繁访问，已经加载到InnoDB缓冲池里，而abc的数据长期没被碰过，需要从磁盘读取大量冷数据，导致耗时飙升；验证方法：跑一次查询1后立刻再跑一次，如果第二次耗时大幅降低，就是冷数据的问题；
• updateTime字段分布差异：wName='abc'的记录中updateTime分布极散，MySQL用index5扫描时，要跳转到大量不同的数据页回表取完整记录，而xyz的updateTime相对集中，回表时命中的页少很多；验证方法：统计abc和xyz记录的updateTime对应的主键分布，对比回表时的页访问次数；
• 锁等待或并发干扰：查询1执行时刚好碰到其他事务对abc相关记录加锁，导致等待；验证方法：执行查询1的同时，跑SHOW ENGINE INNODB STATUS查看事务等待信息，确认是否存在锁等待。

内容的提问来源于stack exchange，提问作者rahulP