17370845950

分析SQL聚合查询执行计划需关注聚合类型、数据来源、排序与临时表开销。应优先使用索引加速WHERE过滤，确保GROUP BY字段有序以启用Stream Aggregate，避免多余排序或磁盘临时表；将非聚合条件置于WHERE中减少输入量，仅在依赖聚合结果时使用HAVING，从而提升整体性能。

分析SQL聚合查询的执行计划，核心在于理解数据是如何被收集、分组和计算的。它不像普通的单表查询那样直接，多了一层“数据聚拢”的逻辑。我们要特别关注的是聚合操作本身（比如

GROUP BY

），看它是在什么时候发生的，是以什么方式进行的（哈希聚合还是流式聚合），以及这个过程中有没有产生额外的开销，比如排序或临时表的使用。通过这些，我们能判断聚合的效率，并找出潜在的优化点。

解决方案

说起来，分析聚合查询的执行计划，我个人觉得得有点像侦探破案，一步步拆解数据流向。首先，眼睛肯定得盯住那些“聚合”相关的操作符。不同的数据库可能有不同的叫法，比如MySQL里可能直接显示

Using temporary

和

Using filesort

伴随着

GROUP BY

，PostgreSQL则有

HashAggregate

或

GroupAggregate

，SQL Server则可能是

Hash Match (Aggregate)

或

Stream Aggregate

。

当我们看到这些聚合操作符时，需要重点关注以下几点：

1. 聚合类型： 是

   Hash Aggregate

   还是

   Stream Aggregate

   ？这俩性能表现差异很大。

   Hash Aggregate

   通常用于输入数据未经排序的情况，它会在内存中构建哈希表来完成分组和计算，如果数据量太大内存不够，就可能溢出到磁盘，导致性能急剧下降。而

   Stream Aggregate

   则要求输入数据是按

   GROUP BY

   字段排序的，它能以流式方式高效处理，通常性能更好。
2. 输入数据来源： 聚合操作的输入是什么？是全表扫描、索引扫描，还是经过了其他过滤或连接操作的结果？如果聚合前的输入数据量非常大，即使聚合操作本身效率高，整体性能也可能受影响。理想情况是，

   WHERE

   子句能尽可能早地过滤掉无关数据，减少进入聚合环节的数据量。
3. 排序开销： 如果执行计划中在聚合操作之前出现了

   Sort

   操作（比如MySQL的

   Using filesort

   ），这通常意味着数据库为了进行

   Stream Aggregate

   或者处理

   GROUP BY

   字段未被索引覆盖的情况，不得不先对数据进行排序。排序是个非常耗资源的操作，尤其是当数据量大时，可能需要使用临时文件（磁盘），这会成为性能瓶颈。
4. 临时表（Temporary Table）使用： 某些聚合操作，特别是涉及

   DISTINCT

   或复杂

   GROUP BY

   的，数据库可能需要创建内部临时表来存储中间结果。在MySQL的

   EXPLAIN

   结果中，

   Using temporary

   就是一个明显的信号。临时表如果是在内存中还好，一旦溢出到磁盘，I/O开销会非常大。
5. 索引利用： 检查

   GROUP BY

   字段上是否有合适的索引。一个覆盖

   GROUP BY

   字段的索引，不仅可以加速数据查找，更重要的是，它能提供预排序的数据，使得数据库可以选择更高效的

   Stream Aggregate

   ，甚至完全避免额外的排序操作。

举个例子，假设我们有这样的查询：

SELECT category, COUNT(*)
FROM products
WHERE price > 100
GROUP BY category
ORDER BY COUNT(*) DESC;

在分析其执行计划时，我会看：

• WHERE price > 100

  是否利用了

  price

  上的索引来快速过滤。

• GROUP BY category

  是

  Hash Aggregate

  还是

  Stream Aggregate

  ？如果是

  Stream Aggregate

  ，前面有没有

  Sort

  操作？

• category

  字段上是否有索引？如果有，是否能避免排序？

• ORDER BY COUNT(*) DESC

  会在聚合之后进行排序，这通常是不可避免的，但如果前面的聚合步骤已经优化，这里的排序压力也会小很多。

聚合查询中，

Hash Aggregate

和

Stream Aggregate

有什么区别？什么时候用哪个？

这俩哥们儿，在聚合查询的执行计划里可是常客，但它们的脾气秉性完全不同。

Hash Aggregate

就像个大厨，把所有食材（数据）都倒进一个大锅（内存），然后用刀（哈希函数）把它们分门别类地切好，再统计。它不怕你给它的食材是乱七八糟的，都能处理。每个

GROUP BY

键值都会在内存中对应一个哈希桶，当新行进来时，计算其键值的哈希，找到对应的桶，然后更新聚合值。这种方式的好处是，对输入数据的顺序没有要求，所以即使数据是乱序的，也能高效处理。但它的缺点也很明显：如果数据量太大，哈希表无法完全放入内存，就得溢出到磁盘，这会产生大量的I/O操作，性能直线下降。

而

Stream Aggregate

则像一个流水线工人，它要求输入的数据必须是按照

GROUP BY

字段预先排好序的。它会一行一行地处理数据，当发现当前行的

GROUP BY

键值和上一行相同时，就继续更新当前的聚合值；一旦键值发生变化，就认为一个分组结束了，输出当前分组的聚合结果，然后开始处理下一个分组。这种方式的效率非常高，因为它只需要一次遍历，而且内存占用相对较小。但前提是，数据必须是排好序的。如果输入数据本身就是无序的，数据库就得先插入一个

Sort

操作符，把数据排好序再交给

Stream Aggregate

处理，这个额外的排序开销可能非常大。

至于什么时候用哪个，这通常是数据库优化器根据当前查询的上下文自动决定的。如果

GROUP BY

字段上有合适的索引，并且这个索引能提供预排序的数据，那么优化器很可能会选择

Stream Aggregate

。反之，如果数据是无序的，或者数据量太大以至于排序成本过高，优化器就可能倾向于选择

Hash Aggregate

。作为开发者，我们能做的就是通过创建合适的索引，或者在

WHERE

子句中尽可能地过滤数据，来“引导”优化器选择更高效的

Stream Aggregate

路径，避免不必要的排序或哈希溢出。

为什么聚合查询的执行计划中常出现临时表（

Using temporary

）？如何避免？

临时表，这玩意儿在执行计划里出现，基本就意味着你的查询可能有点“重”了。我见过不少情况，就是因为数据库发现它没法在内存里把所有数据都规规整整地聚拢好，就只好找个“仓库”（磁盘）先存着，等需要的时候再拿出来。这就像你收拾屋子，东西太多没地方放，就先堆在走廊里，等你收拾好一个房间，再把走廊里的东西搬进去。这来来回回，效率自然就下来了。

聚合查询中出现临时表，通常有几个常见原因：

1. GROUP BY

   或

   DISTINCT

   操作需要排序，但内存不足： 当

   GROUP BY

   的字段没有索引覆盖，或者索引不能提供所需的排序顺序时，数据库需要对数据进行内部排序。如果待排序的数据量超过了数据库为排序分配的内存（比如MySQL的

   sort_buffer_size

   ），那么一部分数据就会被写入磁盘上的临时文件进行排序，这就是

   Using temporary

   和

   Using filesort

   常常同时出现的原因。

   COUNT(DISTINCT column)

   这样的操作也经常需要临时表来去重。

2. UNION

   操作：

   UNION

   默认会去重，这通常需要数据库构建一个哈希表或临时表来识别并移除重复行。
3. 复杂的子查询或视图： 如果聚合操作是基于一个复杂子查询或视图的结果，而这个中间结果集又很大，也可能导致临时表的使用。

要避免或减少临时表的使用，我们可以从以下几个方面入手：

• 创建合适的索引： 这是最直接有效的方法。在

  GROUP BY

  涉及的列上创建索引，尤其是复合索引，可以帮助数据库直接利用索引的预排序特性，从而避免额外的排序操作。如果索引能覆盖查询所需的所有列（包括

  WHERE

  和

  SELECT

  中的列），那就更好了，可以避免回表查询。
• 优化

  WHERE

  子句，尽早过滤数据： 在聚合之前，尽可能地通过

  WHERE

  子句过滤掉不必要的行。数据量越小，需要聚合、排序的数据就越少，临时表的风险自然就降低了。
• 调整数据库参数： 适当增加与排序和临时表相关的内存参数，比如MySQL的

  sort_buffer_size

  、

  tmp_table_size

  和

  max_heap_table_size

  。但要非常小心，这些是全局参数，设置过大可能导致服务器内存耗尽，需要根据实际负载和硬件资源进行权衡。
• 重写复杂查询： 有时，一个复杂的聚合查询可以通过拆分成多个简单查询，或者使用派生表、CTE（Common Table Expressions）来优化。例如，对于

  COUNT(DISTINCT ...)

  ，有时候先对数据进行

  GROUP BY

  ，然后在外层

  COUNT(*)

  可能会有更好的性能。
• 避免不必要的

  DISTINCT

  ： 仔细检查查询逻辑，看是否真的需要

  DISTINCT

  。如果业务允许，或者其他方式已经保证了唯一性，就尽量避免使用它。

聚合查询中，

WHERE

和

HAVING

子句对执行计划有什么影响？

WHERE

和

HAVING

，这哥俩虽然都是做筛选的，但它们出场的时机和对整个查询性能的影响，那可是天差地别。我通常把

WHERE

看作是“预筛选”，它在数据还没被聚拢之前，就先把那些不相干的、我们压根儿不关心的行给剔除了。这就像你准备做一锅汤，在洗菜的时候就把烂叶子、虫眼儿的菜都扔掉了，只留下好的食材进锅。这样，锅里要处理的就少多了，效率自然高。

具体来说：

• WHERE

  子句：
  • 执行顺序：

    WHERE

    子句是在数据被

    GROUP BY

    聚合之前执行的。它是对原始表或连接结果中的行进行过滤。
  • 影响： 对性能的影响至关重要。它能显著减少进入聚合操作的数据量。数据量越小，后续的聚合、排序、临时表等操作的开销就越低。在执行计划中，

    WHERE

    条件通常会出现在表扫描或索引扫描的阶段，作为早期的数据过滤条件。一个高效的

    WHERE

    子句能够利用索引来快速定位和过滤数据，从而极大地提升查询效率。
  • 优化： 尽可能地把过滤条件放在

    WHERE

    子句中，特别是那些不依赖于聚合结果的条件。

• HAVING

  子句：
  • 执行顺序：

    HAVING

    子句是在数据被

    GROUP BY

    聚合之后执行的。它是对已经形成的组进行过滤，所以它可以使用聚合函数的结果作为过滤条件。
  • 影响：

    HAVING

    子句虽然也会过滤结果，但它是在所有分组和聚合计算完成之后才进行的。这意味着，即使

    HAVING

    条件最终过滤掉了大部分组，之前的聚合操作仍然需要处理所有符合

    WHERE

    条件的行，并为它们生成聚合结果。因此，

    HAVING

    对聚合操作本身的性能影响较小，它主要影响的是最终返回给用户的结果集大小。在执行计划中，

    HAVING

    条件通常会出现在聚合操作之后，作为对聚合结果的进一步过滤。
  • 优化： 只有当过滤条件依赖于聚合函数的结果时，才使用

    HAVING

    。如果条件不依赖聚合函数，那么它应该被移到

    WHERE

    子句中，以便在聚合之前就减少数据量。

简而言之，优化聚合查询时，首要原则就是“尽早过滤”。能用

WHERE

解决的过滤，就不要留给

HAVING

。只有当你的过滤条件确实需要依赖

COUNT()

,

SUM()

,

AVG()

等聚合函数的结果时，

HAVING

才是你的选择。