EXPLAIN
更新時間：
Copy as MD
該語句用於解釋SQL語句的執行計畫，包括SELECT、DELETE、INSERT、REPLACE或UPDATE語句。
文法
擷取SQL計劃資訊：
EXPLAIN
{LOGICALVIEW | LOGIC | SIMPLE | DETAIL | EXECUTE | PHYSICAL | OPTIMIZER | SHARDING
| COST | ANALYZE | BASELINE | JSON_PLAN }
{SELECT statement | DELETE statement | INSERT statement | REPLACE statement| UPDATE statement}樣本
EXPLAIN語句：展示基本的SQL執行計畫，該執行計畫是運算元組成，主要體現SQL在CN上的整個執行過程。
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，取值為falseortrue；TemplateId表示對該計劃的標識，具有全域唯一性。EXPLAIN LOGICALVIEW語句：展示LogicalView所表示的下推SQL在DN上的執行計畫。
EXPLAIN LOGICALVIEW select 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: 5819c807EXPLAIN EXECUTE語句：表示下推SQL在MySQL的執行情況，這個語句和MySQL的explain語句同義。通過該語句可以查看下推SQL在DN上有沒有使用索引，有沒有做全表掃描。
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上掃描的物理分區情況。
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。
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: 5819c807EXPLAIN ANALYZE語句：相對於explain cost語句，除了顯示各個運算元基於統計資訊估算的代價以外，該語句可以收集真實運行過程中運算元輸出的rowCount等資訊。
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，先做預彙總再做最終的彙總計算，每個片段的執行度可以不同。
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)
该文章对您有帮助吗？