mysql5.7执行计划 导出_通过EXPLAIN查询执行计划

示例

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