In AnalyticDB for MySQL, you can retrieve a text execution plan by using the EXPLAIN statement, or a graphical execution plan by using the Visual Explain feature that is provided by Data Management (DMS). This topic describes how to retrieve a text execution plan by using the EXPLAIN statement and how to interpret its output. EXPLAIN is a standard SQL statement widely used by commercial databases. AnalyticDB for MySQL extends and improves the feature of the EXPLAIN statement in a continuous way.
Retrieve only the summary of an execution plan
- Syntax
Explain sql_statement;
- Sample
EXPLAIN SELECT count(*) FROM nation, region, customer WHERE c_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'ASIA';
The following output of the sample EXPLAIN statement is returned:
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}
As shown in the output, the execution plan is decomposed into 18 mini plans. Each mini plan contains an execution operator. These mini plans are indented based on the execution order of operators.
The execution plan indicates that at the top layer, the COUNT operation is performed for an aggregation. The aggregation result is returned from a join in step 6. The join type is an inner join and the join method is a hash join. The hash join uses the customer table as the left input, and the joining result of the nation and region tables as the right input. An inner join is performed in step 12 to join the nation table with the region table by using a hash join. The inner join operator joins the left nation table before the right region table. In step 15, a hash table is built. In step 16, a broadcast operation is performed.
Retrieve a detailed execution plan
- Syntax
EXPLAIN(FORMAT DETAIL) sql_statement;
You can include the FORMAT DETAIL option in the EXPLAIN statement to retrieve a detailed execution plan.
- Sample
AnalyticDB for MySQL allows you to view the summary and details of an execution plan in two separate areas. You may view only the summary, or map the details to the summary of an execution plan by the mini plan ID.
| 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
The plan summary omits a large number of details that most users do not concern, and contains only the most important information such as the join method, join type, join order, data shuffling, and relation name. You can understand an execution plan by viewing the summary of the execution plan.