ClickHouse 执行计划查看

在clickhouse 20.6版本之前要查看SQL语句的执行计划需要设置日志级别为trace才能可以看到，并且只能真正执行sql，在执行日志里面查看。在20.6版本引入了原生的执行计划的语法。在20.6.3版本成为正式版本的功能。

1、基本语法

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]

1、PLAN：用于查看执行计划，默认值。

　　header 打印计划中各个步骤的 head 说明，默认关闭，默认值0;

　　description 打印计划中各个步骤的描述，默认开启，默认值1；

　　actions 打印计划中各个步骤的详细信息，默认关闭，默认值0。

2、AST ：用于查看语法树;

3、SYNTAX：用于优化语法;

4、PIPELINE：用于查看 PIPELINE 计划。

　　header 打印计划中各个步骤的 head 说明，默认关闭;

　　graph 用DOT图形语言描述管道图，默认关闭，需要查看相关的图形需要配合graphviz查看；

　　actions 如果开启了graph，紧凑打印打，默认开启。

注：PLAN 和 PIPELINE 还可以进行额外的显示设置，如上参数所示。

2、使用案例

2.1、新版本使用 EXPLAN

1、查看PLAIN

简单查询

explain plan select arrayJoin([1,2,3,null,null]);
Expression ((Projection + Before ORDER BY))
  SettingQuotaAndLimits (Set limits and quota after reading from storage)
    ReadFromStorage (SystemOne)

复杂SQL的执行计划

xplain
select database, table, count(1) cnt
from system.parts
where database in ('datasets', 'system')
group by database, table
order by database, cnt desc
limit 2 by database;

Expression (Projection)
  LimitBy
    Expression (Before LIMIT BY)
      MergingSorted (Merge sorted streams for ORDER BY)
        MergeSorting (Merge sorted blocks for ORDER BY)
          PartialSorting (Sort each block for ORDER BY)
            Expression (Before ORDER BY)
              Aggregating
                Expression (Before GROUP BY)
                  Filter (WHERE)
                    SettingQuotaAndLimits (Set limits and quota after reading from storage)
                      ReadFromStorage (SystemParts)

打开全部的参数的执行计划

EXPLAIN header=1, actions=1,description=1
SELECT number
from system.numbers
limit 10;
Expression ((Projection + Before ORDER BY))
Header: number UInt64
Actions: INPUT :: 0 -> number UInt64 : 0
Positions: 0
  SettingQuotaAndLimits (Set limits and quota after reading from storage)
  Header: number UInt64
    Limit (preliminary LIMIT)
    Header: number UInt64
    Limit 10
    Offset 0
      ReadFromStorage (SystemNumbers)
      Header: number UInt64

2、AST语法树

EXPLAIN AST
SELECT number
from system.numbers
limit 10;
  SelectQuery (children 3)
   ExpressionList (children 1)
    Identifier number
   TablesInSelectQuery (children 1)
    TablesInSelectQueryElement (children 1)
     TableExpression (children 1)
      TableIdentifier system.numbers
   Literal UInt64_10
 Identifier TabSeparatedWithNamesAndTypes

3、SYNTAX语法优化

//先做一次查询
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'wdh01') FROM numbers(10);
//查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'wdh01') FROM numbers(10);

//开启三元运算符优化
SET optimize_if_chain_to_multiif = 1;
//再次查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'wdh01') FROM numbers(10);

//返回优化后的语句
SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'xyz')
FROM numbers(10)

这种方式可以优化写好的SQL，平时使用过程中，也可以先使用这种方式对写好的SQL进行优化，再去执行优化后的SQL。

4、查看PIPELINE

EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20;
(Expression)
ExpressionTransform
  (Aggregating)
  Resize 2 → 1
    AggregatingTransform × 2
      (Expression)
      ExpressionTransform × 2
        (SettingQuotaAndLimits)
          (ReadFromStorage)
          NumbersMt × 2 0 → 1

//打开其他参数
EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM numbers_mt(10000) GROUP BY number%20;
digraph
{
"  rankdir=""LR"";"
  { node [shape = box]
"        n2 [label=""Limit""];"
"        n1 [label=""Numbers""];"
    subgraph cluster_0 {
"      label =""Expression"";"
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
"        n5 [label=""ExpressionTransform""];"
      }
    }
    subgraph cluster_1 {
"      label =""Expression"";"
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
"        n3 [label=""ExpressionTransform""];"
      }
    }
    subgraph cluster_2 {
"      label =""Aggregating"";"
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
"        n4 [label=""AggregatingTransform""];"
      }
    }
  }
"  n2 -> n3 [label="""
"number UInt64 UInt64(size = 0)""];"
"  n1 -> n2 [label="""
"number UInt64 UInt64(size = 0)""];"
"  n3 -> n4 [label="""
number UInt64 UInt64(size = 0)
"modulo(number, 20) UInt8 UInt8(size = 0)""];"
"  n4 -> n5 [label="""
modulo(number, 20) UInt8 UInt8(size = 0)
"sum(number) UInt64 UInt64(size = 0)""];"
}

2.2、老版本使用 EXPLAN

clickhouse-client -h 主机名 --send_logs_level=trace <<< "sql" > /dev/null

其中，send_logs_level参数指定日志等级为trace，<<<将SQL语句重定向至clickhouse-client进行查询，> /dev/null将查询结果重定向到空设备吞掉，以便观察日志。

注意：

1、通过将ClickHouse的服务日志，设置到DEBUG或者TRACE级别，才可以变相实现EXPLAIN查询的作用。

2、需要真正的执行SQL查询，CH才能打印计划日志，所以如果表的数据量很大，最好借助LIMIT子句，减小查询返回的数据量。