在AnalyticDB for MySQL中支持通过两种方式查看生成的查询计划:通过EXPLAIN命令返回文本格式的查询计划或者使用DMS集成的图形化计划输出功能。本文介绍如何通过EXPLAIN命令返回文本格式的查询计划,EXPLAIN是大多数商业数据库的标准命令接口,AnalyticDB for MySQL也在不断扩展和优化EXPLAIN功能。
输出对应语句的执行计划
- 语法
Explain sql_statement;
- 示例
EXPLAIN SELECT count(*) FROM nation, region, customer WHERE c_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'ASIA';
上述EXPLAIN语句的返回结果如下:
1- Output[ Query plan ] 2 -> Aggregate (FINAL) 3 -> LocalExchange[SINGLE] 4 -> Exchange[GATHER] 5 -> Aggregate (PARTIAL) 6 -> InnerJoin[Hash Join] 7 - ScanProject {table: customer} 8 -> TableScan {table: customer} 9 -> LocalExchange[HASH] 10 -> Exchange[REPLICATE] 11 - Project 12 -> InnerJoin[Hash Join] 13 - ScanProject {table: region} 14 -> TableScan {table: region} 15 -> LocalExchange[HASH] 16 -> Exchange[REPLICATE] 17 - ScanProject {table: nation} 18 -> TableScan {table: nation}
在上例输出的执行计划中,标注了18个mini-plan,每一个对应一个执行算子,EXPLAIN结果中Plan的缩进代表了算子间相互的逻辑执行顺序。
上述示例中,最顶层是由Aggregation计算Count,Aggregation的结果来自于Join(Step 6),Join Type是Inner Join,Join Method是Hash Join,Customer是Hash Join的左表,而Nation与Region表Join的结果是Hash Join的右表。Nation表和Region表的Join (Step 12) Type是Inner Join,Join Method是HASH Join。在Inner Join算子下,先计算左表(Nation表),Step 15指Build Hash Table,Step 16是Broadcast,后出现的是右表(Region表)。
输出对应语句的执行计划(包含明细)
- 语法
EXPLAIN(FORMAT DETAIL) sql_statement;
可以使用EXPLAIN(FORMAT DETAIL)命令的Option来输出详细的计划细节。
- 示例
AnalyticDB for MySQL对EXPLAIN的输出格式做了进一步优化处理,例如可以只看查询计划的总结,不查看计划细节。也可以通过mini-plan的编号将查询计划细节和总结对应起来,EXPLAIN的结果将被分成两部分输出,如下所示。
| Plan Summary | +---------------+ 1- Output[ Query plan ] 2 -> Aggregate (FINAL) 3 -> LocalExchange[SINGLE] 4 -> Exchange[GATHER] 5 -> Aggregate (PARTIAL) 6 -> InnerJoin[Hash Join] 7 - ScanProject {table: customer} 8 -> LocalExchange[HASH] 9 -> Exchange[REPLICATE] 10 - Project 11 -> InnerJoin[Hash Join] 12 - ScanProject {table: region} 13 -> LocalExchange[HASH] 14 -> Exchange[REPLICATE] 15 - ScanProject {table: nation}
+---------------+ | Plan Details | +---------------+ 1- Output[count(*)] => [count:bigint] count(*) := count 2 - Aggregate(FINAL) => [count:bigint] count := `count`(`count_0_5`) 3 - LocalExchange[SINGLE] () => count_0_5:bigint 4 - RemoteExchange[GATHER] => count_0_5:bigint 5 - Aggregate(PARTIAL) => [count_0_5:bigint] count_0_5 := `count`(*) 6 - InnerJoin[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue_0_6] => [] 7 - ScanProject[table = adb:com.alibaba.cloud.analyticdb.connector.AdbTableHandle@354b905b, originalConstraint = (SELECT `c_nationkey` FROM tpch_100g.customer)] => [c_nationkey:integer, $hashvalue:bigint] $hashvalue := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`c_nationkey`), 0)) LAYOUT: com.alibaba.cloud.analyticdb.connector.AdbTableLayoutHandle@582531c1 c_nationkey := com.alibaba.cloud.analyticdb.connector.AdbColumnHandle@11d903d7 8 - LocalExchange[HASH][$hashvalue_0_6] ("n_nationkey") => n_nationkey:integer, $hashvalue_0_6:bigint 9 - RemoteExchange[REPLICATE] => n_nationkey:integer, $hashvalue_0_7:bigint 10 - Project[] => [n_nationkey:integer, $hashvalue_0_12:bigint] $hashvalue_0_12 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_nationkey`), 0)) 11 - InnerJoin[(`r_regionkey` = `n_regionkey`)][$hashvalue_0_8, $hashvalue_0_9] => [n_nationkey:integer] 12 - ScanProject[table = adb:com.alibaba.cloud.analyticdb.connector.AdbTableHandle@201cb3f2, originalConstraint = (SELECT `r_regionkey` , `r_name` FROM tpch_100g.region WHERE ('ASIA' = `r_name`))] => [r_regionkey:integer, $hashvalue_0_8:bigint] $hashvalue_0_8 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`r_regionkey`), 0)) LAYOUT: com.alibaba.cloud.analyticdb.connector.AdbTableLayoutHandle@7c6d54a r_regionkey := com.alibaba.cloud.analyticdb.connector.AdbColumnHandle@20e861e 13 - LocalExchange[HASH][$hashvalue_0_9] ("n_regionkey") => n_nationkey:integer, n_regionkey:integer, $hashvalue_0_9:bigint 14 - RemoteExchange[REPLICATE] => n_nationkey:integer, n_regionkey:integer, $hashvalue_0_10:bigint 15 - ScanProject[table = adb:com.alibaba.cloud.analyticdb.connector.AdbTableHandle@6ad3e560, originalConstraint = (SELECT `n_nationkey`, `n_regionkey` FROM tpch_100g.nation )] => [n_nationkey:integer, n_regionkey:integer, $hashvalue_0_11:bigint] $hashvalue_0_11 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_regionkey`), 0)) LAYOUT: com.alibaba.cloud.analyticdb.connector.AdbTableLayoutHandle@1fd4d6d n_nationkey := com.alibaba.cloud.analyticdb.connector.AdbColumnHandle@3b12e11a n_regionkey := com.alibaba.cloud.analyticdb.connector.AdbColumnHandle@29806c3
在Plan Summary中,屏蔽了大量一般用户不感兴趣的细节,只留下查询计划中最重要的计划信息,例如Join Method、Join Type、Join Order、Data Shuffling、Relation Name等相关信息。一般情况下,用户只需要查看Plan Summary中的计划信息就可以大致了解查询的执行计划。
在文档使用中是否遇到以下问题
更多建议
匿名提交