带GROUP BY的SQL查询耗时过长，求原因分析与优化方案

建站初期数据库为空时，以下查询语句性能正常，但当订单表积累到10万行数据后，查询耗时达到3-4秒；移除GROUP BY及聚合函数后，查询仅需0.00005秒。

原查询语句：

SELECT c.*, p.*, COUNT(o.OrderID) as nb_total 
FROM orders o 
INNER JOIN products p on o.OrderProductID = p.ProductID 
INNER JOIN categories c on c.CategoryID = p.ProductCategoryID 
WHERE p.deleted = 0 
GROUP BY o.OrderProductID 
ORDER BY nb_total DESC 
LIMIT 10

该查询用于获取销量Top10的商品，同时返回商品（名称、缩略图）及对应分类（颜色、图标）的信息。目前已在ProductCategoryID、CategoryID、OrderProductID字段建立索引。

原因分析 #

• 聚合运算的本质开销：GROUP BY需要对匹配的订单数据执行分组统计，10万行数据下会触发大量的排序或哈希分组操作。而移除GROUP BY后，查询仅需完成三张表的关联并返回前10条结果，中间结果集极小，因此速度极快。
• 索引未适配聚合场景：现有单个字段索引仅能支持关联查询，但无法覆盖分组+聚合的需求。数据库执行原查询时，需要先关联三张表生成大中间结果集，再对该结果集分组，导致额外的内存和IO开销。
• 关联与分组的顺序不合理：原查询先关联三张表，再执行分组统计，会先产生包含10万条订单关联数据的中间表，再对其分组，这远大于直接在订单表上分组的开销。

优化方案 #

1. 先聚合统计，再关联其他表 #

优先在orders表内完成销量统计并取Top10，再关联products和categories表获取详情。这样能将中间结果集从10万行压缩到10行，大幅降低关联开销：

SELECT 
    c.CategoryID, c.Color, c.Icon, -- 仅查询需要的分类字段
    p.ProductID, p.Name, p.Thumbnail, -- 仅查询需要的商品字段
    o.nb_total
FROM (
    SELECT 
        OrderProductID, 
        COUNT(OrderID) as nb_total
    FROM orders
    GROUP BY OrderProductID
    ORDER BY nb_total DESC
    LIMIT 10
) o
INNER JOIN products p ON o.OrderProductID = p.ProductID
INNER JOIN categories c ON c.CategoryID = p.ProductCategoryID
WHERE p.deleted = 0
ORDER BY o.nb_total DESC

2. 创建适配聚合场景的覆盖索引 #

• 对orders表创建复合索引：CREATE INDEX idx_order_product_id ON orders(OrderProductID, OrderID);
  该索引包含分组字段OrderProductID和聚合需要的OrderID，数据库可直接从索引完成统计，无需回表访问订单主表。
• 对products表创建复合索引：CREATE INDEX idx_product_id_deleted ON products(ProductID, deleted, ProductCategoryID);
  该索引覆盖关联字段、筛选条件和关联分类所需字段，避免关联时回表。

3. 避免SELECT *，只查询必要字段 #

原查询使用c.*、p.*会返回不必要的字段，增加数据传输和内存占用。明确指定需要的字段，不仅提升性能，还能让覆盖索引的效果最大化。

4. 用EXPLAIN验证执行计划 #

执行EXPLAIN命令查看查询的执行计划，确认索引是否被正确使用：

EXPLAIN -- 你的优化后查询语句

重点关注：

• type列：是否为ref或range（表示索引被有效使用）
• key列：是否命中创建的复合索引
• Extra列：是否出现Using index（表示使用覆盖索引，无回表操作），避免Using filesort或Using temporary（表示存在额外排序/临时表开销）

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