在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中的计划信息就可以大致了解查询的执行计划。