Distinct Optimization

本文介绍 TiDB 查询优化器中的 distinct 优化，包括 SELECT DISTINCT 和聚合函数中的 DISTINCT。

SELECT 语句中的 DISTINCT 修饰符

DISTINCT 修饰符指定从结果集中去除重复的行。SELECT DISTINCT 会被转换为 GROUP BY，例如：

mysql> explain SELECT DISTINCT a from t;
+--------------------------+---------+-----------+---------------+-------------------------------------------------------+
| id                       | estRows | task      | access object | operator info                                         |
+--------------------------+---------+-----------+---------------+-------------------------------------------------------+
| HashAgg_6                | 2.40    | root      |               | group by:test.t.a, funcs:firstrow(test.t.a)->test.t.a |
| └─TableReader_11         | 3.00    | root      |               | data:TableFullScan_10                                 |
|   └─TableFullScan_10     | 3.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo                        |
+--------------------------+---------+-----------+---------------+-------------------------------------------------------+
3 rows in set (0.00 sec)

聚合函数中的 DISTINCT 选项

通常，带有 DISTINCT 选项的聚合函数在 TiDB 层以内以单线程执行模型执行。

tidb_opt_distinct_agg_push_down 系统变量在 TiDB 中控制是否重写带有 DISTINCT 的聚合查询，并将其下推到 TiKV 或 TiFlash 计算节点。

以下示例展示了此优化的效果。tidb_opt_distinct_agg_push_down 默认为禁用状态，意味着聚合函数在 TiDB 层执行。通过将其值设置为 1，可以启用此优化，将 count(distinct a) 中的 distinct a 部分下推到 TiKV 或 TiFlash 计算节点：在 TiKV 计算节点中存在一个 HashAgg_5，用于去除列 a 上的重复值。这可能会减少 TiDB 层中 HashAgg_8 的计算开销。

mysql> desc select count(distinct a) from test.t;
+-------------------------+----------+-----------+---------------+------------------------------------------+
| id                      | estRows  | task      | access object | operator info                            |
+-------------------------+----------+-----------+---------------+------------------------------------------+
| StreamAgg_6             | 1.00     | root      |               | funcs:count(distinct test.t.a)->Column#4 |
| └─TableReader_10        | 10000.00 | root      |               | data:TableFullScan_9                     |
|   └─TableFullScan_9     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo           |
+-------------------------+----------+-----------+---------------+------------------------------------------+
3 rows in set (0.01 sec)

mysql> set session tidb_opt_distinct_agg_push_down = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> desc select count(distinct a) from test.t;
+---------------------------+----------+-----------+---------------+------------------------------------------+
| id                        | estRows  | task      | access object | operator info                            |
+---------------------------+----------+-----------+---------------+------------------------------------------+
| HashAgg_8                 | 1.00     | root      |               | funcs:count(distinct test.t.a)->Column#3 |
| └─TableReader_9           | 1.00     | root      |               | data:HashAgg_5                           |
|   └─HashAgg_5             | 1.00     | cop[tikv] |               | group by:test.t.a,                       |
|     └─TableFullScan_7     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo           |
+---------------------------+----------+-----------+---------------+------------------------------------------+
4 rows in set (0.00 sec)