Execute the EXPLAIN statement to query an execution plan

Explain sql_statement;

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.

EXPLAIN(FORMAT DETAIL) sql_statement;

You can include the FORMAT DETAIL option in the EXPLAIN statement to retrieve a detailed execution plan.

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.