1.EXPLAIN 語法示例
EXPLAIN [AST | SYNTAX | QUERY TREE | PLAN | PIPELINE | ESTIMATE | TABLE OVERRIDE] [setting = value, ...]
? ? [
? ? ? SELECT ... |
? ? ? tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
? ? ]
? ? [FORMAT ...]
查詢計劃的類型說明:
?AST(抽象語法樹):在AST級別優化之后的查詢文本?
?SYNTAX(語法優化):在AST級別優化之后的查詢文本，返回優化后的sql?
?QUERY TREE(查詢樹):在查詢樹級別優化之后的查詢樹
?PLAN(執行計劃):查詢執行計劃，默認是此值
?PIPELINE(管道):查詢執行管道
1.1AST(抽象語法樹)
查詢語法樹執行計劃
explain AST SELECT
? ? ? ? ? ? path,code,v1
? ? ? ? FROM
? ? ? ? ? ? autorun_t_index
? ? ? ? WHERE
? ? ? ? ? ? index_id = 1227
? ? ? ? ? AND cluster_id =27
? ? ? ? ? AND code IN('queue_name', 'queue_allocatedmb', 'queue_availablemb','queue_allocatedvcores','queue_availablevcores')
? ? ? ? ? AND update_date IN(SELECT MAX(update_date) FROM autorun_t_index WHERE index_id = 1227 AND cluster_id = 27 and update_date <='2023-08-04 01:38:48')
展示結果:
? SelectQuery (children 3)
? ?ExpressionList (children 3)
? ? Identifier path
? ? Identifier code
? ? Identifier v1
? ?TablesInSelectQuery (children 1)
? ? TablesInSelectQueryElement (children 1)
? ? ?TableExpression (children 1)
? ? ? TableIdentifier autorun_t_index
? ?Function and (children 1)
? ? ExpressionList (children 4)
? ? ?Function equals (children 1)
? ? ? ExpressionList (children 2)
? ? ? ?Identifier index_id
? ? ? ?Literal UInt64_1227
? ? ?Function equals (children 1)
? ? ? ExpressionList (children 2)
? ? ? ?Identifier cluster_id
? ? ? ?Literal UInt64_27
? ? ?Function in (children 1)
? ? ? ExpressionList (children 2)
? ? ? ?Identifier code
? ? ? ?Literal Tuple_('queue_name', 'queue_allocatedmb', 'queue_availablemb', 'queue_allocatedvcores', 'queue_availablevcores')
? ? ?Function in (children 1)
? ? ? ExpressionList (children 2)
? ? ? ?Identifier update_date
? ? ? ?Subquery (children 1)
? ? ? ? SelectWithUnionQuery (children 1)
? ? ? ? ?ExpressionList (children 1)
? ? ? ? ? SelectQuery (children 3)
? ? ? ? ? ?ExpressionList (children 1)
? ? ? ? ? ? Function MAX (children 1)
? ? ? ? ? ? ?ExpressionList (children 1)
? ? ? ? ? ? ? Identifier update_date
? ? ? ? ? ?TablesInSelectQuery (children 1)
? ? ? ? ? ? TablesInSelectQueryElement (children 1)
? ? ? ? ? ? ?TableExpression (children 1)
? ? ? ? ? ? ? TableIdentifier autorun_t_index
? ? ? ? ? ?Function and (children 1)
? ? ? ? ? ? ExpressionList (children 3)
? ? ? ? ? ? ?Function equals (children 1)
? ? ? ? ? ? ? ExpressionList (children 2)
? ? ? ? ? ? ? ?Identifier index_id
? ? ? ? ? ? ? ?Literal UInt64_1227
? ? ? ? ? ? ?Function equals (children 1)
? ? ? ? ? ? ? ExpressionList (children 2)
? ? ? ? ? ? ? ?Identifier cluster_id
? ? ? ? ? ? ? ?Literal UInt64_27
? ? ? ? ? ? ?Function lessOrEquals (children 1)
? ? ? ? ? ? ? ExpressionList (children 2)
? ? ? ? ? ? ? ?Identifier update_date
? ? ? ? ? ? ? ?Literal '2023-08-04 01:38:48'
?Identifier TabSeparatedWithNamesAndTypes
1.2SYNTAX(語法優化)
語法優化前執行sql
explain SYNTAX ?SELECT
? ? ? ? ? ? path,code,v1
? ? ? ? FROM
? ? ? ? ? ? autorun_t_index
? ? ? ? WHERE
? ? ? ? ? ? index_id = 1227
? ? ? ? ? AND cluster_id =27
? ? ? ? ? AND code IN('queue_name', 'queue_allocatedmb', 'queue_availablemb','queue_allocatedvcores','queue_availablevcores')
? ? ? ? ? AND update_date IN(SELECT MAX(update_date) FROM autorun_t_index WHERE index_id = 1227 AND cluster_id = 27 and update_date <='2023-08-04 01:38:48')
語法優化后推薦的sql
SELECT
? ? path,
? ? code,
? ? v1
FROM autorun_t_index
WHERE (index_id = 1227) AND (cluster_id = 27) AND (code IN ('queue_name', 'queue_allocatedmb', 'queue_availablemb', 'queue_allocatedvcores', 'queue_availablevcores')) AND (update_date IN ((
? ? SELECT max(update_date)
? ? FROM autorun_t_index
? ? WHERE (index_id = 1227) AND (cluster_id = 27) AND (update_date <= '2023-08-04 01:38:48')
) AS _subquery132))
1.3QUERY TREE(查詢樹)
略
1.4PLAN(執行計劃)
默認值就是PLAN，一般用來查看sql的執行計劃，是否使用引擎，分區，索引等信息
未添加索引前
explain ?PLAN ?SELECT
? ? ? ? ? ? path,code,v1
? ? ? ? FROM
? ? ? ? ? ? autorun_t_index
? ? ? ? WHERE
? ? ? ? ? ? index_id = 1227
? ? ? ? ? AND cluster_id =27
? ? ? ? ? AND code IN('queue_name', 'queue_allocatedmb', 'queue_availablemb','queue_allocatedvcores','queue_availablevcores')
? ? ? ? ? AND update_date IN(SELECT MAX(update_date) FROM autorun_t_index WHERE index_id = 1227 AND cluster_id = 27 and update_date <='2023-08-04 01:38:48')
未添加索引查詢計劃
Expression (Projection)
? CreatingSets (Create sets before main query execution)
? ? Expression (Before ORDER BY)
? ? ? Filter (WHERE)
? ? ? ? SettingQuotaAndLimits (Set limits and quota after reading from storage)
? ? ? ? ? ReadFromStorage (Log)
? ? CreatingSet (Create set for subquery)
? ? ? Expression ((Projection + Before ORDER BY))
? ? ? ? Aggregating
? ? ? ? ? Expression (Before GROUP BY)
? ? ? ? ? ? Filter (WHERE)
? ? ? ? ? ? ? SettingQuotaAndLimits (Set limits and quota after reading from storage)
? ? ? ? ? ? ? ? ReadFromStorage (Log)
添加引擎，分區后優化結果
Expression ((Projection + Before ORDER BY))
? SettingQuotaAndLimits (Set limits and quota after reading from storage)
? ? ReadFromMergeTree
1.5PIPELINE(管道)
官網示例
EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;
執行結果:
(Expression)
ExpressionTransform
? (Aggregating)
? Resize 4 → 1
? ? AggregatingTransform × 4
? ? ? (Expression)
? ? ? ExpressionTransform × 4
? ? ? ? (SettingQuotaAndLimits)
? ? ? ? ? (ReadFromStorage)
? ? ? ? ? NumbersMt × 4 0 → 1
?
)
