排序、聚合、集合、分析函数、Limit-k - 聚合 - 《OceanBase v1.4 官方教程》

简介

聚合操作指的是将多行结果按照指定方式组合成一行。常见的聚合有GROUP BY语句，聚合函数和DISTINCT操作。窗口函数可以看做特殊的聚合函数，之后会单独介绍。

本节将会从算子层面介绍聚合，聚合算子主要包括：GROUP BY，DISTINCT和WINDOW FUNCTION。本节只介绍GROUP BY和DISTINCT。

GROUP BY

GROUP BY的逻辑算子有三种不同的算法： SCALAR GROUP BY、MERGE GROUP BY和HASH GROUP BY。

SCALAR GROUP BY

SCALAR GROUP BY是指聚合后生成的结果是一个标量的算法。比如下面这个例子，SELECT语句中有聚合函数，但是没有GROUP BY语句，这时候语义为：对t1的a列取和，结果为标量，优化器决定使用SCALAR GROUP BY。

OceanBase_114 (root@test)> explain select sum(a) from t1\G
*************************** 1. row ***************************
Query Plan: ========================================
|ID|OPERATOR       |NAME|EST. ROWS|COST|
----------------------------------------
|0 |SCALAR GROUP BY|    |1        |624 |
|1 | TABLE SCAN    |t1  |1000     |433 |
========================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_SUM(t1.a)]), filter(nil),
      group(nil), agg_func([T_FUN_SUM(t1.a)])
  1 - output([t1.a]), filter(nil),
      access([t1.a]), partitions(p0)
1 row in set (0.01 sec)

MERGE GROUP BY

当语句包含GROUP BY语句时，优化器可能会生成MERGE GROUP BY算子。MERGE GROUP BY要求下层必须按照GROUP BY的列有序，否则MERGE GROUP BY会自行分配SORT算子。比如下面这个例子，t1主键列为a，GROUP BY的表达式同为a，此时，MERGE GROUP BY可以使用下层的序。

OceanBase_114 (root@test)> explain extended_noaddr select sum(a) from t1 group by a\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR      |NAME|EST. ROWS|COST|
---------------------------------------
|0 |MERGE GROUP BY|    |250      |686 |
|1 | TABLE SCAN   |t1  |1000     |411 |
=======================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_SUM(t1.a)]), filter(nil),
      group([t1.a]), agg_func([T_FUN_SUM(t1.a)])
  1 - output([t1.a]), filter(nil),
      access([t1.a]), partitions(p0),
      is_index_back=false,
      range_key([t1.a]), range(MIN ; MAX)always true
1 row in set (0.01 sec)

而当我们ORDER BY表达式为b时，我们不能直接利用下层TABLE SCAN算子结果的序，所以优化器会在MERGE GROUP BY下分配SORT算子。

OceanBase_114 (root@test)> explain extended_noaddr select /*+ NO_USE_HASH_AGGREGATION */ sum(a) from t1 group by b\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR      |NAME|EST. ROWS|COST|
---------------------------------------
|0 |MERGE GROUP BY|    |250      |2256|
|1 | SORT         |    |1000     |1981|
|2 |  TABLE SCAN  |t1  |1000     |433 |
=======================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_SUM(t1.a)]), filter(nil),
      group([t1.b]), agg_func([T_FUN_SUM(t1.a)])
  1 - output([t1.a], [t1.b]), filter(nil), sort_keys([t1.b, ASC])
  2 - output([t1.a], [t1.b]), filter(nil),
      access([t1.a], [t1.b]), partitions(p0),
      is_index_back=false,
      range_key([t1.a]), range(MIN ; MAX)always true
1 row in set (0.01 sec)

另外，MERGE GROUP BY生成的结果是按照GROUP BY表达式有序的。下面语句输出结果需要按照a排序，而因为MERGE GROUP BY的结果已经有序，所以优化器会把最上层的ORDER BY消除。

OceanBase_114 (root@test)> explain extended_noaddr select sum(a) from t1 group by a order by a\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR      |NAME|EST. ROWS|COST|
---------------------------------------
|0 |MERGE GROUP BY|    |250      |686 |
|1 | TABLE SCAN   |t1  |1000     |411 |
=======================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_SUM(t1.a)]), filter(nil),
      group([t1.a]), agg_func([T_FUN_SUM(t1.a)])
  1 - output([t1.a]), filter(nil),
      access([t1.a]), partitions(p0),
      is_index_back=false,
      range_key([t1.a]), range(MIN ; MAX)always true
1 row in set (0.01 sec)

HASH GROUP BY

当语句包含GROUP BY语句时，优化器可能会生成HASH GROUP BY算子。HASH GROUP BY内部实现使用了HASH算法，所以该算子对下层算子结果的序没有依赖，产生的结果是无序的。

OceanBase_114 (root@test)> explain extended_noaddr select sum(a) from t1 group by b order by b\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR      |NAME|EST. ROWS|COST|
---------------------------------------
|0 |SORT          |    |250      |1338|
|1 | HASH GROUP BY|    |250      |993 |
|2 |  TABLE SCAN  |t1  |1000     |433 |
=======================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_SUM(t1.a)]), filter(nil), sort_keys([t1.b, ASC])
  1 - output([T_FUN_SUM(t1.a)], [t1.b]), filter(nil),
      group([t1.b]), agg_func([T_FUN_SUM(t1.a)])
  2 - output([t1.a], [t1.b]), filter(nil),
      access([t1.a], [t1.b]), partitions(p0),
      is_index_back=false,
      range_key([t1.a]), range(MIN ; MAX)always true
1 row in set (0.02 sec)

对于MERGE GROUP BY与HASH GROUP BY的选择：

当一个语句中有存在GROUP BY语句时，优化器可能会根据代价选择 MERGE/HASH GROUP BY。通常来说，HASH GROUP BY的代价比MERGE GROUP BY要大，但是如果MERGE GROUP BY算子要求下层分配SORT算子时，聚合的整体代价应该视为MERGE GROUP BY和下层SORT算子的代价之和，此时，HASH GROUP BY的代价反而可能更低。通俗来讲也就是，如果GROUP BY算子下层有可以利用的序时通常会走MERGE GROUP BY，否则优化器有可能会选择HASH GROUP BY。

说明

注意：用户可以通过HINT(NO_USE_HASH_AGGREGATION和USE_HASH_AGGREGATION)指定用哪种聚合算法。

DISTINCT

DISTINCT内部算法的实现其实与GROUP BY类似，种类有MERGE DISTINCT与HASH DISTINCT。优化器在选择DISTINCT算法时的过程也与GROUP BY类似，同时也可以使用HINT(NO_USE_HASH_AGGREGATION和USE_HASH_AGGREGATION)控制。下面不再赘述，仅举出例子。

MERGE DISTINCT

MERGE DISTINCT算子与MERGE GROUP BY类似，也要求下层有序，算子输出的结果也是有序的。下面例子的t1主键为a。

OceanBase_114 (root@test)> explain extended_noaddr select distinct a from t1 order by a\G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |1000     |411 |
===================================
Outputs & filters:
-------------------------------------
  0 - output([t1.a]), filter(nil),
      access([t1.a]), partitions(p0),
      is_index_back=false,
      range_key([t1.a]), range(MIN ; MAX)always true
1 row in set (0.02 sec)

HASH DISTINCT

HASH DISTINCT算子与HASH GROUP BY类似，不要求下层有序，输出的结果无序。下面例子的t1主键为a。

OceanBase_114 (root@test)> explain extended_noaddr select distinct b from t1 order by b\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR      |NAME|EST. ROWS|COST|
---------------------------------------
|0 |SORT          |    |250      |1237|
|1 | HASH DISTINCT|    |250      |940 |
|2 |  TABLE SCAN  |t1  |1000     |433 |
=======================================
Outputs & filters:
-------------------------------------
  0 - output([t1.b]), filter(nil), sort_keys([t1.b, ASC])
  1 - output([t1.b]), filter(nil),
      distinct([t1.b])
  2 - output([t1.b]), filter(nil),
      access([t1.b]), partitions(p0),
      is_index_back=false,
      range_key([t1.a]), range(MIN ; MAX)always true
1 row in set (0.01 sec)