文法
擷取SQL計劃資訊：
EXPLAIN
{LOGICALVIEW | LOGIC | SIMPLE | DETAIL | EXECUTE | PHYSICAL | OPTIMIZER | SHARDING
| COST | ANALYZE | BASELINE | JSON_PLAN | ADVISOR}
{SELECT statement | DELETE statement | INSERT statement | REPLACE statement| UPDATE statement} 示例
- explain語句：展示基本的SQL執行計劃，該執行計劃是算子組成，主要展現SQL在CN上的整個執行過程。
mysql> explain select count(*) from lineitem group by L_ORDERKEY;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project(count(*)="count(*)") |
| HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))") |
| Gather(concurrent=true) |
| LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`") |
| HitCache:false | |
| TemplateId: 5819c807 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ - 其中，
HitCache
PlanCache
false
true
TemplateId
- explain logicalview語句：展示LogicalView所表示的下推SQL在DN上的執行計劃。
mysql> explain LOGICALVIEW select mysql> explain select logialview count(*) from lineitem group by L_ORDERKEY;
+----------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+----------------------------------------------------------+
| Project(count(*)="count(*)") |
| HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))") |
| Gather(concurrent=true) |
| LogicalView |
| MysqlAgg(group="L_ORDERKEY", count(*)="COUNT()") |
| MysqlTableScan(name=[ads, lineitem]) |
| HitCache:true |
| Source:PLAN_CACHE |
| TemplateId: 5819c807 - explain execute語句：表示下推SQL在mysql的執行情況，這個語句和mysql的explain語句同義。通過該語句可以檢視下推SQL在DN上有沒有使用索引，有沒有做全表掃描。
mysql> explain EXECUTE select count(*) from lineitem group by L_ORDERKEY;
+----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+----------------------------------------------+
| 1 | SIMPLE | lineitem | NULL | index | PRIMARY | PRIMARY | 8 | NULL | 1 | 100 | Using index; Using temporary; Using filesort |
+----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+----------------------------------------------+
1 row in set (0.24 sec) - explain sharding語句：展示目前查詢在DN上掃描的實體分片情況。
mysql> explain sharding select count(*) from lineitem group by L_ORDERKEY;
+---------------+----------------------------------+-------------+-----------+-----------+
| LOGICAL_TABLE | SHARDING | SHARD_COUNT | BROADCAST | CONDITION |
+---------------+----------------------------------+-------------+-----------+-----------+
| lineitem | [000000-000003].lineitem_[00-15] | 16 | false | |
+---------------+----------------------------------+-------------+-----------+-----------+
1 row in set (0.04 sec) - explain cost語句：相對于explain語句，除了展示執行計劃以外，還會顯示各個算子基于統計資訊估算的代價，以及這條查詢被優化器識别的WORKLOAD。
mysql> explain COST select count(*) from lineitem group by L_ORDERKEY;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project(count(*)="count(*)"): rowcount = 2508.0, cumulative cost = value = 2.4867663E7, cpu = 112574.0, memory = 88984.0, io = 201.0, net = 4.75, id = 182 |
| HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))"): rowcount = 2508.0, cumulative cost = value = 2.4867662E7, cpu = 112573.0, memory = 88984.0, io = 201.0, net = 4.75, id = 180 |
| Gather(concurrent=true): rowcount = 2508.0, cumulative cost = value = 2.4860069E7, cpu = 105039.0, memory = 29796.0, io = 201.0, net = 4.75, id = 178 |
| LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`"): rowcount = 2508.0, cumulative cost = value = 2.4860068E7, cpu = 105038.0, memory = 29796.0, io = 201.0, net = 4.75, id = 109 |
| HitCache:true |
| Source:PLAN_CACHE |
| WorkloadType: TP |
| TemplateId: 5819c807 - explain analyze語句：相對于explain cost語句，除了顯示各個算子基于統計資訊估算的代價以外，該語句可以收集真實運作過程中算子輸出的rowCount等資訊。
mysql> explain ANALYZE select count(*) from lineitem group by L_ORDERKEY;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project(count(*)="count(*)"): rowcount = 2508.0, cumulative cost = value = 2.4867663E7, cpu = 112574.0, memory = 88984.0, io = 201.0, net = 4.75, actual time = 0.001 + 0.000, actual rowcount = 2506, actual memory = 0, instances = 1, id = 182 |
| HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))"): rowcount = 2508.0, cumulative cost = value = 2.4867662E7, cpu = 112573.0, memory = 88984.0, io = 201.0, net = 4.75, actual time = 0.000 + 0.000, actual rowcount = 2506, actual memory = 0, instances = 1, id = 180 |
| Gather(concurrent=true): rowcount = 2508.0, cumulative cost = value = 2.4860069E7, cpu = 105039.0, memory = 29796.0, io = 201.0, net = 4.75, actual time = 0.000 + 0.000, actual rowcount = 0, actual memory = 0, instances = 0, id = 178 |
| LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`"): rowcount = 2508.0, cumulative cost = value = 2.4860068E7, cpu = 105038.0, memory = 29796.0, io = 201.0, net = 4.75, actual time = 0.030 + 0.025, actual rowcount = 10000, actual memory = 0, instances = 0, id = 109 |
| HitCache:true |
| Source:PLAN_CACHE |
| TemplateId: 5819c807 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (1.08 sec) - explain physical語句：展示查詢在運作過程中執行模式、各個執行片段（Fragment）的依賴關系和并行度。該查詢被識别為單機單線程計劃模式（TP_LOCAL），執行計劃被分為三個片段Fragment-0、Fragment-1和Fragment-2，先做預聚合再做最終的聚合計算，每個片段的執行度可以不同。
mysql> explain physical select count(*) from lineitem group by L_ORDERKEY;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ExecutorMode: TP_LOCAL |
| Fragment 0 dependency: [] parallelism: 4 |
| Gather(concurrent=true) |
| LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`") |
| Fragment 1 dependency: [] parallelism: 8 |
| LocalBuffer |
| RemoteSource(sourceFragmentIds=[0], type=RecordType(INTEGER L_ORDERKEY, BIGINT count(*))) |
| Fragment 2 dependency: [0, 1] parallelism: 8 |
| Project(count(*)="count(*)") |
| HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))") |
| RemoteSource(sourceFragmentIds=[1], type=RecordType(INTEGER L_ORDERKEY, BIGINT count(*))) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.10 sec) - explain advisor語句：主要是會基于統計資訊，分析目前查詢的執行計劃，給使用者推薦可以加速查詢的全局二級索引。
mysql> explain advisor select count(*) from lineitem group by L_ORDERKEY \G;
*************************** 1. row ***************************
IMPROVE_VALUE: 4.4%
IMPROVE_CPU: 340.8%
IMPROVE_MEM: 0.0%
IMPROVE_IO: 1910.0%
IMPROVE_NET: 0.0%
BEFORE_VALUE: 2.48676627E7
BEFORE_CPU: 112573.7
BEFORE_MEM: 88983.8
BEFORE_IO: 201
BEFORE_NET: 4.7
AFTER_VALUE: 2.38256249E7
AFTER_CPU: 25536
AFTER_MEM: 88983.8
AFTER_IO: 10
AFTER_NET: 4.7
ADVISE_INDEX: ALTER TABLE `ads`.`lineitem` ADD GLOBAL INDEX `__advise_index_gsi_lineitem_L_ORDERKEY`(`L_ORDERKEY`) DBPARTITION BY HASH(`L_ORDERKEY`) TBPARTITION BY HASH(`L_ORDERKEY`) TBPARTITIONS 4;
NEW_PLAN:
Project(count(*)="count(*)")
HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))")
Gather(concurrent=true)
IndexScan(tables="[000000-000003].lineitem__what_if_gsi_L_ORDERKEY_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem__what_if_gsi_L_ORDERKEY` AS `lineitem__what_if_gsi_L_ORDERKEY` GROUP BY `L_ORDERKEY`")
INFO: GLOBAL_INDEX
1 row in set (0.13 sec) ![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)