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