This article is about Learning Guide for Big Data Specialists from Zero (Full Upgrade) ClickHouse: Partial supplement.
0 Explain View Execution Plan
To view the execution plan of an sql statement before Clickhouse version 20.6, you need to set the log level to trace to be visible and can only actually execute sql and view it in the execution log. Native execution plan syntax was introduced in version 20.6. Version 20.6.3 became an official version of functionality.
This document is based on the newer stable version 18.104.22.168.
1 Basic Grammar
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]
PLAN: Used to view the execution plan, default value.
*◼header Prints the head er description of each step in the plan, off by default, with a default value of 0;
*◼Description Prints the description of each step in the plan, turned on by default, default value 1;
*◼actions prints the details of each step in the plan, turned off by default, with a default value of 0.
_AST: used to view the grammar tree;
_SYNTAX: used to optimize grammar;
_PIPELINE: Used to view PIPELINE plans.
*◼header prints the head er description of each step in the plan, turned off by default;
*◼The graph describes the pipeline graph in DOT graphics language. By default, it is closed. The related graph needs to be viewed with graphviz.
*◼ActionsIf graph is turned on, compact printing is turned on by default.
Note: PLAN and PPELINE can also have additional display settings, as shown in the parameters above.
2 Case Practices
2.1 New version uses EXPLAIN
You can install another version above 20.6, or you can test it directly on the official website by selecting a higher version.
Official Web Test Link:https://play.clickhouse.tech/?file=welcome
1) View PLAIN
explain plan select arrayJoin([1,2,3,null,null]);
Execution plan for complex SQL
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;
Open execution plan for all parameters
EXPLAIN header=1, actions=1,description=1 SELECT number from system.numbers limit 10;
2) AST Grammar Tree
EXPLAIN AST SELECT number from system.numbers limit 10;
3) SYNTAX syntax optimization
//Make a query first SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10); //View grammar optimization EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10); //Turn on ternary operator optimization SET optimize_if_chain_to_multiif = 1; //Review grammar optimization again EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10); //Return optimized statement SELECT multiIf(number = 1, \'hello\', number = 2, \'world\', \'xyz\') FROM numbers(10)
4) View PIPELINE
EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20; //Open Other Parameters EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM numbers_mt(10000) GROUP BY number%20;
2.2 Old Version View Execution Plan
clickhouse-client -h host name --send_logs_level=trace <<< "sql" > /dev/null
The send_logs_level parameter specifies that the log level is trace, <<<< Redirect the SQL statement to clickhouse-client for query, >/dev/null redirects the query result to empty device swallow for observation of the log.
1. By setting ClickHouse's service log to the DEBUG or TRACE level, EXPLAIN queries can be implemented in disguised form.
2. Executing a real SQL query is required for CH to print the plan log, so if the table has a large amount of data, it is best to use the LIMIT clause to reduce the amount of data returned by the query.