尝鲜ClickHouse原生EXPLAIN查询功能

Nauu

发布于 2020-07-15 15:12:55

6K0

发布于 2020-07-15 15:12:55

文章被收录于专栏：ClickHouse的秘密基地ClickHouse的秘密基地

在之前的一篇文章中，我曾经介绍过如何借助 ClickHouse 的服务日志查看 SQL 执行计划，从而变相达到 EXPLAIN 查询的目的。

在先前的方法中，由于 CH 没有原生的 EXPLAIN 查询，所以如果想要看到执行计划，那么 SQL 语句必须被真正的执行，所以这种方法只能作为一种事后分析，有着明显的缺点。

现在，事情有了转机。在本周的早些时候，一个名为 Initial explain #11873 的 PR 被合入了主线。是的，新鲜出炉的，ClickHouse 原生的 EXPLAIN 查询诞生了。

那还不赶快尝鲜一下 ?!

由于这个 PR 本周才被合并，所以目前在官方提供的编译好的安装包中是看不到这个功能的，只能通过 master 分支自行编译。

如上图所示，是我在本地编译的版本，版本号是 20.6。所以可以肯定的是在这个版本中可以使用到 CH 原生的 EXPLAIN 查询。希望后续可以 backport 到低版本。

EXPLAIN 的完整语法如下所示:

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

其中:

PLAN 用于查看执行计划;

AST 用于查看语法树;

SYNTAX 用于优化语法;

PIPELINE 用于查看 PIPELINE 计划。

PLAN 和 PIPELINE 还可以进行额外的显示设置，这一部分放到后续的演示过程中解释。

接下来就让我们来挨个尝鲜吧。

PLAN

PLAN是最常用的功能，即查看 SQL 执行计划，例如执行:

EXPLAIN PLAN SELECT 1:

Union
  Expression (Projection)
    Expression (Before ORDER BY and SELECT)
      ReadFromStorage (Read from SystemOne)

4 rows in set. Elapsed: 0.001 sec.

如上所示，该 SQL 的执行计划被显示出来了。

PLAN 是 EXPLAIN 的默认值，所以 PLAN 修饰词可以省略，现在我们换条复杂一点的 SQL，执行:

EXPLAIN
SELECT 
    database,
    table,
    column,
    any(type),
    sum(column_data_compressed_bytes) AS compressed,
    sum(column_data_uncompressed_bytes) AS uncompressed,
    round(uncompressed / compressed, 2) AS ratio,
    compressed / sum(rows) AS bpr,
    sum(rows)
FROM system.parts_columns
WHERE active AND (database != 'system')
GROUP BY 
    database,
    table,
    column
ORDER BY 
    database ASC,
    table ASC,
    column ASC
FORMAT TSV

查看结果:

Union
  Expression (Projection)
    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 and SELECT)
            Aggregating
              Expression (Before GROUP BY)
                Filter (WHERE)
                  ReadFromStorage (Read from SystemPartsColumns)

10 rows in set. Elapsed: 0.003 sec.

该 SQL 的每一步执行计划都被显示出来了，此时我们进一步观察 CH 的日志:

{bbfc1521-13fe-4d5a-b99f-4d8f6ed9dd6c} <Debug> executeQuery: (from 127.0.0.1:59522) EXPLAIN SELECT database, table, column, any(type), sum(column_data_compressed_bytes) AS compressed, sum(column_data_uncompressed_bytes) AS uncompressed, round(uncompressed / compressed, 2) AS ratio, compressed / sum(rows) AS bpr, sum(rows) FROM system.parts_columns WHERE active AND database != 'system' GROUP BY database, table, column ORDER BY database ASC, table ASC, column ASC;
2020.07.12 02:07:46.580977 [ 8451277 ] {bbfc1521-13fe-4d5a-b99f-4d8f6ed9dd6c} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
2020.07.12 02:07:46.581329 [ 8451277 ] {bbfc1521-13fe-4d5a-b99f-4d8f6ed9dd6c} <Debug> executeQuery: Query pipeline:
One

2020.07.12 02:07:46.581556 [ 8451277 ] {bbfc1521-13fe-4d5a-b99f-4d8f6ed9dd6c} <Information> executeQuery: Read 10 rows,

可以看到，真正执行 EXPLAIN 查询的计划是：

Query pipeline:
One

我们输入的 SQL 并没有被真正执行，所以这种方式可以用于事前分析。

EXPLAIN PLAN 目前还可以设置 3 个参数，它们分别是：

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

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

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

例如执行:

EXPLAIN header=1, actions=1 SELECT 1;

将会看到:

Union
Header: 1 UInt8 Const(size = 0, UInt8(size = 1))
  Expression (Projection)
  Header: 1 UInt8 Const(size = 0, UInt8(size = 1))
  Actions: PROJECT 1
    Expression (Before ORDER BY and SELECT)
    Header: _dummy UInt8 UInt8(size = 0)
            1 UInt8 Const(size = 0, UInt8(size = 1))
    Actions: COPY _dummy = dummy (can replace)
             REMOVE dummy
             ADD 1 UInt8 Const(UInt8)
      ReadFromStorage (Read from SystemOne)
      Header: dummy UInt8 UInt8(size = 0)

13 rows in set. Elapsed: 0.001 sec.

AST

AST 可以查看 SQL 的语法树，例如执行：

EXPLAiN AST SELECT * FROM `system`.numbers n LIMIT 10000

返回语法树结构:

SelectWithUnionQuery (children 1)
 ExpressionList (children 1)
  SelectQuery (children 3)
   ExpressionList (children 1)
    Asterisk
   TablesInSelectQuery (children 1)
    TablesInSelectQueryElement (children 1)
     TableExpression (children 1)
      Identifier system.numbers (alias n)
   Literal UInt64_10000

10 rows in set. Elapsed: 0.001 sec.

SYNTAX

SYNTAX 可以帮助我们优化 SQL 语句，例如下面的这条 SQL 语句:

SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') FROM numbers(10);

通过 SYNTAX 优化:

SET optimize_if_chain_to_multiif = 1;
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') FROM numbers(10);

返回优化后的语句:

SELECT multiIf(number = 1, \'hello\', number = 2, \'world\', \'xyz\')
FROM numbers(10)

PIPELINE

查看 PIPELINE 过程，例如执行:

EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20

会返回:

(Union)
Converting
  (Expression)
  ExpressionTransform
    (Expression)
    ExpressionTransform
      (Aggregating)
      Resize 8 → 1
        AggregatingTransform × 8
          (Expression)
          ExpressionTransform × 8
            (ReadFromStorage)
            NumbersMt × 8 0 → 1

13 rows in set. Elapsed: 0.002 sec.

PIPELINE 目前也可以设置 3 个参数，它们分别是：

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

graph，用DOT图形语言描述管道图，默认关闭；

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

例如执行:

EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20

会看到:

digraph
{
  rankdir="LR";
  { node [shape = box]
    subgraph cluster_0 {
      label ="Aggregating";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n3 [label="AggregatingTransform × 8"];
        n4 [label="Resize"];
      }
    }
    subgraph cluster_1 {
      label ="ReadFromStorage";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n1 [label="NumbersMt × 8"];
      }
    }
    subgraph cluster_2 {
      label ="Union";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n7 [label="Converting"];
      }
    }
    subgraph cluster_3 {
      label ="Expression";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n5 [label="ExpressionTransform"];
      }
    }
    subgraph cluster_4 {
      label ="Expression";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n2 [label="ExpressionTransform × 8"];
      }
    }
    subgraph cluster_5 {
      label ="Expression";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n6 [label="ExpressionTransform"];
      }
    }
  }
  n3 -> n4 [label="× 8
modulo(number, 20) UInt8 UInt8(size = 0)
sum(number) UInt64 UInt64(size = 0)"];
  n4 -> n5 [label="
modulo(number, 20) UInt8 UInt8(size = 0)
sum(number) UInt64 UInt64(size = 0)"];
  n1 -> n2 [label="× 8
number UInt64 UInt64(size = 0)"];
  n5 -> n6 [label="
modulo(number, 20) UInt8 UInt8(size = 0)
sum(number) UInt64 UInt64(size = 0)"];
  n2 -> n3 [label="× 8
number UInt64 UInt64(size = 0)
modulo(number, 20) UInt8 UInt8(size = 0)"];
  n6 -> n7 [label="
sum(number) UInt64 UInt64(size = 0)"];
}

77 rows in set. Elapsed: 0.002 sec.

借助这些信息，我们可以很方便的将其进行可视化。

在 ClickHouse 有了原生的 EXPLAIN 查询之后，日常工作中我们对于 SQL 的调优和问题排查工作就会更加便捷了。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-07-12，如有侵权请联系 cloudcommunity@tencent.com 删除

sql