MySQL查询在不同配置KVM虚拟机中执行时长差异过大的原因排查

这种看似违背常理的性能差异，核心原因大概率是执行计划偏差、虚拟化资源调度瓶颈或系统/数据库实际运行状态不一致导致的，具体拆解如下：

1. MySQL查询优化器选择了不同执行计划 #

虽然SQL和索引配置一致，但MySQL优化器会根据当前机器的资源情况（内存、CPU、数据统计信息）动态选择执行策略：

• 低配置机器（1核1G）内存有限，优化器更倾向于使用嵌套循环连接+索引扫描的组合，这种方式内存占用低，且你的SQL中每个goods行都会触发8次相关子查询，在索引覆盖的场景下执行效率极高。
• 高配置机器（2核4G）内存充足时，优化器可能尝试哈希连接或更复杂的执行逻辑，但如果表的统计信息不准确（比如数据分布变化但未更新统计），反而会选中低效路径——比如对大表做全表扫描，导致每个子查询的耗时被放大，最终整体时间暴增。

另外你SQL中的((z.id_stores = -1)OR(-1 <= 0))等价于恒真条件（-1<=0永远成立），优化器在不同机器上对这种无效条件的简化逻辑可能存在差异，部分机器未过滤冗余条件，额外增加了计算开销。

2. KVM虚拟机的实际资源调度问题 #

虚拟化环境下的纸面配置不代表实际可用资源，以下情况会导致性能倒挂：

• CPU抢占：第三台机器（2.8GHz）的宿主机可能同时运行了其他高负载虚拟机，CPU被频繁抢占，实际用于执行SQL的CPU时间远低于纸面配置；而低配置机器可能被分配了独占CPU核心，反而能稳定高效运行。
• 内存交换（Swap）：高配置机器的MySQL若设置了过大的缓冲池（比如innodb_buffer_pool_size），会导致宿主机内存不足触发swap，磁盘IO速度远低于内存，直接拖慢查询；低配置机器因内存限制，缓冲池设置更保守，反而避免了swap问题。
• 磁盘IO差异：虚拟机的磁盘存储介质/位置可能不同——低配置机器可能用了SSD，高配置机器用了机械盘，或者宿主机磁盘IO被其他虚拟机占用，导致高配置机器读取表数据的耗时剧增。

3. 缓存命中情况不一致 #

如果几次查询不是在完全冷缓存状态下执行：

• 低配置机器可能之前执行过该查询，相关表数据已加载到MySQL缓冲池，后续查询直接从内存读取，速度极快。
• 高配置机器的缓冲池可能没有相关数据，或被其他业务的缓存数据覆盖，需要从磁盘重新读取，耗时大幅增加。

验证建议 #

要定位具体原因，可以在三台机器上分别执行EXPLAIN ANALYZE（MySQL 8.0+支持）对比实际执行计划，查看是否存在全表扫描、连接方式差异；同时检查：

• 三台机器SHOW VARIABLES LIKE 'innodb_buffer_pool_size'的实际值
• 宿主机对虚拟机的CPU/内存分配策略（是否有CPU亲和性、资源限制）
• 虚拟机的磁盘IO延迟（用Windows性能监视器查看磁盘队列长度、读取耗时）

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