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.