MySQL GROUP BY查询优化：请提供调优方案及SQL优化建议

原始查询SQL #

SELECT
    SUM(SkusClosingStock.closing_stock) AS closing_stock,
    SUM(SkusClosingStock.closing_pending_stock) AS closing_pending_stock,
    SUM(SkusClosingStock.closing_bad_stock + SkusClosingStock.closing_damaged_stock) AS closing_bad_stock,
    SUM(GREATEST(SkusClosingStock.is_low_stock, 1)) AS low_stock,
    SUM(GREATEST(SkusClosingStock.closing_stock, 1)) AS stock_out,
    skus.id
FROM
    skus_closing_stocks AS SkusClosingStock
 JOIN
    (
        SELECT id
        FROM skus
        WHERE company_id = '8308'
    ) AS skus ON skus.id = SkusClosingStock.sku_id
GROUP BY SkusClosingStock.sku_id limit 1000;

执行计划解析 #

执行计划原始输出：

'1', 'SIMPLE', 'skus', NULL, 'ref', 'PRIMARY,idx_skus_company_id,idx_co_lowstock', 'idx_skus_company_id', '5', 'const', '377590', '100.00', 'Using index; Using temporary; Using filesort'
'1', 'SIMPLE', 'SkusClosingStock', NULL, 'ref', 'idx_skus_closing_stocks_sku_id_warehouse_id', 'idx_skus_closing_stocks_sku_id_warehouse_id', '4', 'primary_db.skus.id', '11', '100.00', NULL

关键问题分析 #

• skus表部分：
  • 虽使用idx_skus_company_id覆盖索引（Using index无需回表），但出现Using temporary和Using filesort，意味着MySQL需创建临时表并排序，面对377590条数据时，额外性能开销会显著放大。
• SkusClosingStock表部分：
  • 用idx_skus_closing_stocks_sku_id_warehouse_id做关联查询（ref类型性能较好），每条skus记录关联约11条数据，但聚合计算需遍历所有关联数据，未利用索引完成聚合。

优化方案与调优技巧 #

1. 简化子查询，消除临时表与文件排序 #

将子查询替换为直接JOIN，避免MySQL为子查询生成临时表，优化后SQL：

SELECT
    SUM(SkusClosingStock.closing_stock) AS closing_stock,
    SUM(SkusClosingStock.closing_pending_stock) AS closing_pending_stock,
    SUM(SkusClosingStock.closing_bad_stock + SkusClosingStock.closing_damaged_stock) AS closing_bad_stock,
    SUM(GREATEST(SkusClosingStock.is_low_stock, 1)) AS low_stock,
    SUM(GREATEST(SkusClosingStock.closing_stock, 1)) AS stock_out,
    skus.id
FROM
    skus_closing_stocks AS SkusClosingStock
JOIN skus ON skus.id = SkusClosingStock.sku_id
WHERE skus.company_id = '8308'
GROUP BY SkusClosingStock.sku_id 
LIMIT 1000;

调整后MySQL可更高效利用索引关联表，降低临时表生成概率。

2. 创建覆盖索引，让聚合计算直接在索引中完成 #

针对skus_closing_stocks表创建包含关联字段和所有聚合所需字段的覆盖索引，避免回表且直接在索引上完成聚合：

CREATE INDEX idx_sku_agg ON skus_closing_stocks(sku_id, closing_stock, closing_pending_stock, closing_bad_stock, closing_damaged_stock, is_low_stock);

该索引包含GROUP BY字段sku_id及所有聚合字段，MySQL无需访问表数据即可完成计算，大幅提升查询速度。

3. 调整GROUP BY字段，匹配关联逻辑 #

当前GROUP BY SkusClosingStock.sku_id与SELECT skus.id逻辑等价（JOIN条件为skus.id = SkusClosingStock.sku_id），可将GROUP BY改为skus.id：

GROUP BY skus.id

因skus.id是主键（本身有序），可避免额外的文件排序开销。

4. 优化GREATEST函数计算逻辑 #

• 若is_low_stock为布尔型（0/1），SUM(GREATEST(SkusClosingStock.is_low_stock, 1))等价于COUNT(*) AS low_stock，直接替换可减少函数计算开销。
• 针对SUM(GREATEST(SkusClosingStock.closing_stock, 1))，若业务是统计库存为0时计1、否则计原值，可替换为SUM(CASE WHEN closing_stock = 0 THEN 1 ELSE closing_stock END) AS stock_out，简化函数调用。

5. 验证LIMIT必要性 #

若业务无需限制前1000条，建议移除LIMIT 1000——LIMIT可能导致优化器选择低效执行计划；若确实需要，需确认其符合业务场景。

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