MySQL5.6执行查询持续Sending Data但MariaDB10可正常运行求助

问题根因 #

MySQL 5.6 版本的查询优化器对相关子查询的优化能力远低于 MariaDB 10，你提供的原始 SQL 中包含 3 个依赖外层查询结果的关联子查询，相当于 v_stock 视图每返回一行数据，就要对 tbl_cummulative 表执行至少 3 次全量扫描，数据量稍大就会出现执行效率指数级下降，卡 Sending Data 状态的问题（该状态本质是 MySQL 正在内部处理查询数据，而非真的在发送返回结果）。

优化方案 #

1. 改写SQL，消除关联子查询

将多次重复执行的关联子查询改为一次性预聚合的 JOIN 逻辑，大幅降低表扫描次数：

SELECT 
    s.partName,
    GROUP_CONCAT(DISTINCT c.mscode SEPARATOR ',') AS model,
    agg.min_fob AS fob,
    s.qtyStock,
    SUM(c.qtyOrder) AS qtyOrder,
    MIN(c.cummulativeQty) AS qtyShortage,
    agg.min_cum_qty AS qtyOrderClosest
FROM dbbomv2.v_stock s
LEFT JOIN dbbomv2.tbl_cummulative c ON s.partName = c.partName
-- 提前聚合计算每个part需要的最小fob、最小负累计量，仅需扫一次表
LEFT JOIN (
    SELECT 
        partName,
        MIN(fob) AS min_fob,
        MIN(cummulativeQty) AS min_cum_qty
    FROM dbbomv2.tbl_cummulative
    WHERE cummulativeQty < 0
    GROUP BY partName
) agg ON s.partName = agg.partName
GROUP BY s.partName, s.qtyStock, agg.min_fob, agg.min_cum_qty
ORDER BY agg.min_fob IS NULL, agg.min_fob, s.partName;

2. 新增覆盖索引

给 tbl_cummulative 表添加联合覆盖索引，让查询全程走索引无需回表读数据，进一步提升执行速度：

ALTER TABLE dbbomv2.tbl_cummulative 
ADD INDEX idx_part_cum_query (partName, cummulativeQty, fob, mscode, qtyOrder);

优化说明 #

• 原SQL的ORDER BY子句使用了非分组字段c.fob，不符合MySQL 5.6的分组语法规则，会导致执行计划错乱，优化后改用聚合后的min_fob排序，逻辑完全一致且兼容性更好
• 改写后仅需扫描2次tbl_cummulative表，对比原SQL的N次扫描，执行效率有数量级提升
• 覆盖索引包含了查询需要的所有字段，避免了磁盘IO开销

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