この文を使用すると、SQL 文の実行計画を確認できます。SELECT、DELETE、INSERT、REPLACE、UPDATE 文をサポートしています。
構文
SQL 実行計画情報を取得するには、以下の構文を使用します：
EXPLAIN
{LOGICALVIEW | LOGIC | SIMPLE | DETAIL | EXECUTE | PHYSICAL | OPTIMIZER | SHARDING
| COST | ANALYZE | BASELINE | JSON_PLAN }
{SELECT 文 | DELETE 文 | INSERT 文 | REPLACE 文 | UPDATE 文}使用例
`EXPLAIN` 文は、基本的な SQL 実行計画を表示します。この計画はオペレーターで構成され、コンピュートノード (CN) 上での完全な実行プロセスを示します。
EXPLAIN select count(*) from lineitem group by L_ORDERKEY; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTION PLAN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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` 文は、データノード (DN) 上でプッシュダウンされた SQL の実行計画を表示します。
EXPLAIN LOGICALVIEW select count(*) from lineitem group by L_ORDERKEY; +----------------------------------------------------------+ | LOGICAL EXECUTION PLAN | +----------------------------------------------------------+ | 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` 文と機能的に同一であり、DN 上でプッシュダウンされた SQL がインデックスを使用しているか、あるいは全表スキャンを実行しているかを確認できます。
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 COST select count(*) from lineitem group by L_ORDERKEY; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTION PLAN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 ANALYZE select count(*) from lineitem group by L_ORDERKEY; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTION PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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` 文は、実行モード、フラグメント間の依存関係、および並列処理の次数を表示します。このクエリはシングルノード・シングルスレッドモード（TP_LOCAL）で実行されます。計画は Fragment-0、Fragment-1、Fragment-2 の 3 つのフラグメントに分割され、まずプリアグリゲーションを実行し、その後ファイナルアグリゲーションを実行します。各フラグメントは異なる並列処理の次数で実行可能です。
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)