天天看點
傳回

【clickhouse】Clickhouse 原生執行計劃1.概述2.執行計劃：3.AST 文法樹：4. syntax文法優化：

文章目錄

  • 1.概述
  • 2.執行計劃：
  • 3.AST 文法樹：
  • 4. syntax文法優化：
【clickhouse】Clickhouse 原生執行計劃1.概述2.執行計劃：3.AST 文法樹：4. syntax文法優化：

1.概述

轉載：https://vkingnew.blog.csdn.net/article/details/107390097

在clickhouse 20.6版本之前要檢視SQL語句的執行計劃需要設定日志級别為trace才能可以看到，在20.6版本引入了原生的執行計劃的文法。在20.6.3版本成為正式版本的功能。

Clickhouse> select version();
 
SELECT version()
 
┌─version()───┐
│ 20.6.1.4066 │
└─────────────┘
 
1 rows in set. Elapsed: 0.007 sec.

執行計劃的文法：

PLAN     用于檢視執行計劃，預設值。
  header       列印計劃中各個步驟的 head 說明，預設關閉，預設值0;
  description  列印計劃中各個步驟的描述，預設開啟，預設值1；
  actions      列印計劃中各個步驟的詳細資訊，預設關閉，預設值0。
AST      用于檢視文法樹;
SYNTAX   用于優化文法;
PIPELINE 用于檢視 PIPELINE 計劃。
  header     列印計劃中各個步驟的 head 說明，預設關閉;
  graph     用DOT圖形語言描述管道圖，預設關閉，需要檢視相關的圖形需要配合graphviz檢視；
  actions   如果開啟了graph，緊湊列印打，預設開啟。

PLAN 和 PIPELINE 還可以進行額外的顯示設定，如上所示。

2.執行計劃：

簡單的執行計劃示例：

Clickhouse> explain plan select arrayJoin([1,2,3,null,null]);
 
EXPLAIN
SELECT arrayJoin([1, 2, 3, NULL, NULL])
FORMAT TSV
 
Union
  Expression (Projection)
    Expression (Before ORDER BY and SELECT)
      ReadFromStorage (Read from SystemOne)
 
4 rows in set. Elapsed: 0.005 sec.

複雜SQL的執行計劃：

Clickhouse> explain 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;
 
EXPLAIN
SELECT 
    database,
    table,
    count(1) AS cnt
FROM system.parts
WHERE database IN ('datasets', 'system')
GROUP BY 
    database,
    table
ORDER BY 
    database ASC,
    cnt DESC
LIMIT 2 BY database
FORMAT TSV
 
————————————————
Union
  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 and SELECT)
                Aggregating
                  Expression (Before GROUP BY)
                    Filter (WHERE)
                      ReadFromStorage (Read from SystemParts)
 
12 rows in set. Elapsed: 0.015 sec.

可以看到explain 預設等價于explain plan。

打開全部的參數的執行計劃：

Clickhouse> EXPLAIN header=1, actions=1,description=1 SELECT number from system.numbers limit 10;;
 
EXPLAIN header = 1, actions = 1, description = 1
SELECT number
FROM system.numbers
LIMIT 10
FORMAT TSV
 
Union
Header: number UInt64 UInt64(size = 0)
  Expression (Projection)
  Header: number UInt64 UInt64(size = 0)
  Actions: PROJECT number
    Limit (preliminary LIMIT)
    Header: number UInt64 UInt64(size = 0)
    Limit 10
    Offset 0
      Expression (Before ORDER BY and SELECT)
      Header: number UInt64 UInt64(size = 0)
        ReadFromStorage (Read from SystemNumbers)
        Header: number UInt64 UInt64(size = 0)
 
13 rows in set. Elapsed: 0.004 sec.

3.AST 文法樹： 

Clickhouse> EXPLAIN AST SELECT number from system.numbers limit 10;;
 
EXPLAIN AST
SELECT number
FROM system.numbers
LIMIT 10
FORMAT TSV
 
SelectWithUnionQuery (children 1)
 ExpressionList (children 1)
  SelectQuery (children 3)
   ExpressionList (children 1)
    Identifier number
   TablesInSelectQuery (children 1)
    TablesInSelectQueryElement (children 1)
     TableExpression (children 1)
      Identifier system.numbers
   Literal UInt64_10
 
10 rows in set. Elapsed: 0.004 sec.

4. syntax文法優化： 

Clickhouse> SELECT number,number%3 = 1 ? 'Clickhouse' : (number%3 = 2 ? 'TiDB' : 'Unkown') stmt FROM numbers(10);
 
SELECT 
    number,
    if((number % 3) = 1, 'Clickhouse', if((number % 3) = 2, 'TiDB', 'Unkown')) AS stmt
FROM numbers(10)
 
┌─number─┬─stmt───────┐
│      0 │ Unkown     │
│      1 │ Clickhouse │
│      2 │ TiDB       │
│      3 │ Unkown     │
│      4 │ Clickhouse │
│      5 │ TiDB       │
│      6 │ Unkown     │
│      7 │ Clickhouse │
│      8 │ TiDB       │
│      9 │ Unkown     │
└────────┴────────────┘
 
10 rows in set. Elapsed: 0.016 sec.

文法優化：

Clickhouse> SET optimize_if_chain_to_multiif = 1;
 
SET optimize_if_chain_to_multiif = 1
 
Ok.
 
0 rows in set. Elapsed: 0.011 sec. 
 
Clickhouse> explain SYNTAX  SELECT number,number%3 = 1 ? 'Clickhouse' : (number%3 = 2 ? 'TiDB' : 'Unkown') stmt FROM numbers(10);
 
EXPLAIN SYNTAX
SELECT 
    number,
    if((number % 3) = 1, 'Clickhouse', if((number % 3) = 2, 'TiDB', 'Unkown')) AS stmt
FROM numbers(10)
FORMAT TSV
 
SELECT 
    number,
    multiIf((number % 3) = 1, \'Clickhouse\', (number % 3) = 2, \'TiDB\', \'Unkown\') AS stmt
FROM numbers(10)
 
4 rows in set. Elapsed: 0.013 sec. 
 
 
 
Clickhouse> SELECT     number,     multiIf((number % 3) = 1, 'Clickhouse', (number % 3) = 2, 'TiDB', 'Unkown') AS stmt FROM numbers(10);
 
SELECT 
    number,
    multiIf((number % 3) = 1, 'Clickhouse', (number % 3) = 2, 'TiDB', 'Unkown') AS stmt
FROM numbers(10)
 
┌─number─┬─stmt───────┐
│      0 │ Unkown     │
│      1 │ Clickhouse │
│      2 │ TiDB       │
│      3 │ Unkown     │
│      4 │ Clickhouse │
│      5 │ TiDB       │
│      6 │ Unkown     │
│      7 │ Clickhouse │
│      8 │ TiDB       │
│      9 │ Unkown     │
└────────┴────────────┘
 
10 rows in set. Elapsed: 0.003 sec.

可以看到優化後的文法查詢速度更加快了

等效的語句：

Clickhouse> SELECT     number,     case number%3 when 1 then 'clickhouse' when 2 then 'TiDB' else 'Unkown' end  AS stmt FROM numbers(10);
 
SELECT 
    number,
    caseWithExpression(number % 3, 1, 'clickhouse', 2, 'TiDB', 'Unkown') AS stmt
FROM numbers(10)
 
優化為：
Clickhouse> explain SYNTAX SELECT     number,     case number%3 when 1 then 'clickhouse' when 2 then 'TiDB' else 'Unkown' end  AS stmt FROM numbers(10);
 
EXPLAIN SYNTAX
SELECT 
    number,
    caseWithExpression(number % 3, 1, 'clickhouse', 2, 'TiDB', 'Unkown') AS stmt
FROM numbers(10)
FORMAT TSV
 
SELECT 
    number,
    caseWithExpression(number % 3, 1, \'clickhouse\', 2, \'TiDB\', \'Unkown\') AS stmt
FROM numbers(10)
 
 4.pipeline：
Clickhouse> EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(10000) GROUP BY number%5;
 
EXPLAIN PIPELINE
SELECT sum(number)
FROM numbers_mt(10000)
GROUP BY number % 5
FORMAT TSV
 
(Union)
Converting
  (Expression)
  ExpressionTransform
    (Expression)
    ExpressionTransform
      (Aggregating)
      AggregatingTransform
        (Expression)
        ExpressionTransform
          (ReadFromStorage)
          Limit
            Numbers 0 → 1
 
13 rows in set. Elapsed: 0.004 sec.

帶參數的：需要結合graphviz 圖形工具檢視。

Clickhouse> EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM numbers_mt(10000) GROUP BY number%5;
 
EXPLAIN PIPELINE header = 1, graph = 1
SELECT sum(number)
FROM numbers_mt(10000)
GROUP BY number % 5
FORMAT TSV
 
digraph
{
  rankdir="LR";
  { node [shape = box]
    subgraph cluster_0 {
      label ="Expression";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n5 [label="ExpressionTransform"];
      }
    }
    subgraph cluster_1 {
      label ="ReadFromStorage";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n2 [label="Limit"];
        n1 [label="Numbers"];
      }
    }
    subgraph cluster_2 {
      label ="Aggregating";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n4 [label="AggregatingTransform"];
      }
    }
    subgraph cluster_3 {
      label ="Expression";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n3 [label="ExpressionTransform"];
      }
    }
    subgraph cluster_4 {
      label ="Expression";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n6 [label="ExpressionTransform"];
      }
    }
    subgraph cluster_5 {
      label ="Union";
      style=filled;
      color=lightgrey;
      node [style=filled,color=white];
      { rank = same;
        n7 [label="Converting"];
      }
    }
  }
  n5 -> n6 [label="
modulo(number, 5) UInt8 UInt8(size = 0)
sum(number) UInt64 UInt64(size = 0)"];
  n2 -> n3 [label="
number UInt64 UInt64(size = 0)"];
  n1 -> n2 [label="
number UInt64 UInt64(size = 0)"];
  n4 -> n5 [label="
modulo(number, 5) UInt8 UInt8(size = 0)
sum(number) UInt64 UInt64(size = 0)"];
  n3 -> n4 [label="
number UInt64 UInt64(size = 0)
modulo(number, 5) UInt8 UInt8(size = 0)"];
  n6 -> n7 [label="
sum(number) UInt64 UInt64(size = 0)"];
}
 
76 rows in set. Elapsed: 0.004 sec.

參考:

https://github.com/ClickHouse/ClickHouse/pull/11873

ClickHouse 原生 執行計劃
上一篇: MySQL資料庫學習筆記一
下一篇: kafka生産者(同步，異步，回調) 消費者（手動Offset，自動Offset）api示範

繼續閱讀