実行計画とは、データベース内でのSQLクエリ文の実行プロセスを記述したものです。ユーザーは EXPLAIN コマンドを使用して、指定されたSQLに対してオプティマイザが生成した論理的な実行計画を確認することができます。
特定のSQLのパフォーマンス問題を分析する場合、通常はまずそのSQLの実行計画を確認し、各ステップでの実行に問題がないかどうかを調査する必要があります。そのため、実行計画を理解することはSQL最適化の前提条件であり、実行計画の演算子を把握することが EXPLAIN コマンドを理解する鍵となります。
EXPLAINコマンドの形式
OceanBaseデータベースの実行計画コマンドには、EXPLAIN BASIC、EXPLAIN、EXPLAIN EXTENDED の3つのモードがあります。これら3つのモードでは、実行計画に対して異なる粒度の詳細情報を表示します。
EXPLAIN BASICコマンドは、最も基本的な計画表示に使用されます。EXPLAIN EXTENDEDコマンドは、最も詳細な計画表示に使用されます（通常、問題のトラブルシューティング時にこの表示モードが使用されます）。EXPLAINコマンドで表示される情報は、一般ユーザーが計画全体の実行方法を理解するのに役立ちます。
EXPLAIN コマンドの形式は以下のとおりです：
EXPLAIN [INTO table_name ] [SET statement_id = string] [explain_type] [PRETTY | PRETTY_COLOR] dml_statement;
explain_type：
BASIC
| OUTLINE
| EXTENDED
| EXTENDED_NOADDR
| PARTITIONS
| FORMAT = {TRADITIONAL| JSON}
dml_statement:
SELECT statement
| DELETE statement
| INSERT statement
| UPDATE statement
/* Oracleモードのみサポート */
| MERGE statement
/* MySQLモードのみサポート */
| REPLACE statement
EXPLAIN コマンドは、SELECT、DELETE、INSERT、REPLACE、UPDATE ステートメントに適用され、オプティマイザーが提供するステートメントの実行計画に関する情報を表示します。これには、そのステートメントをどのように処理するか、テーブルをどのように結合するか、またどの順序で結合するかなどの情報が含まれます。
一般的に、EXPLAIN EXTENDED コマンドを使用して、テーブルスキャンの範囲セグメントを表示できます。EXPLAIN OUTLINE コマンドを使用すると、アウトライン情報を表示できます。
FORMAT オプションを使用して出力形式を選択できます。TRADITIONAL は、出力をテーブル形式で表示することを意味し、これがデフォルト設定でもあります。JSON は、情報を JSON 形式で表示することを意味します。
EXPLAIN PARTITIONS を使用して、パーティションテーブルに関連するクエリをチェックすることもできます。パーティション化されていないテーブルに対するクエリをチェックする場合、エラーは発生しませんが、PARTITIONS 列の値は常に NULL になります。
特に、ユーザーは INTO table_name SQLステートメントを使用して、EXPLAIN の計画情報を指定されたテーブルに保存できます。デフォルトでは、PLAN_TABLE テーブルに照会されます。SET statement_id ステートメントを使用すると、現在のクエリに文字列タグを設定でき、後続のSQLの計画情報の照会を容易にします。デフォルトでは、空の文字列が情報タグとして使用されます。詳細については、EXPLAINを参照してください。
複雑な実行計画については、PRETTY または PRETTY_COLOR オプションを使用して、計画ツリー内の親ノードと子ノードをツリーラインまたはカラーツリーラインで接続することで、実行計画の表示をより読みやすくすることができます。例：
obclient> CREATE TABLE p1table(c1 INT ,c2 INT) PARTITION BY HASH(c1) PARTITIONS 2;
Query OK, 0 rows affected
obclient> CREATE TABLE p2table(c1 INT ,c2 INT) PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> EXPLAIN EXTENDED PRETTY_COLOR SELECT * FROM p1table p1 JOIN p2table p2 ON p1.c1=p2.c2；
*************************** 1. row ***************************
Query Plan: ==========================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
----------------------------------------------------------
|0 |PX COORDINATOR | |1 |278 |
|1 | EXCHANGE OUT DISTR |:EX10001|1 |277 |
|2 | HASH JOIN | |1 |276 |
|3 | ├PX PARTITION ITERATOR | |1 |92 |
|4 | │ TABLE SCAN |P1 |1 |92 |
|5 | └EXCHANGE IN DISTR | |1 |184 |
|6 | EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |184 |
|7 | PX PARTITION ITERATOR | |1 |183 |
|8 | TABLE SCAN |P2 |1 |183 |
==========================================================
Outputs & filters:
-------------------------------------
0 - output([INTERNAL_FUNCTION(P1.C1, P1.C2, P2.C1, P2.C2)]), filter(nil)
1 - output([INTERNAL_FUNCTION(P1.C1, P1.C2, P2.C1, P2.C2)]), filter(nil), dop=1
2 - output([P1.C1], [P2.C2], [P1.C2], [P2.C1]), filter(nil),
equal_conds([P1.C1 = P2.C2]), other_conds(nil)
3 - output([P1.C1], [P1.C2]), filter(nil)
4 - output([P1.C1], [P1.C2]), filter(nil),
access([P1.C1], [P1.C2]), partitions(p[0-1])
5 - output([P2.C2], [P2.C1]), filter(nil)
6 - (#keys=1, [P2.C2]), output([P2.C2], [P2.C1]), filter(nil), dop=1
7 - output([P2.C1], [P2.C2]), filter(nil)
8 - output([P2.C1], [P2.C2]), filter(nil),
access([P2.C1], [P2.C2]), partitions(p[0-3])
1 row in set
実行計画の形状と演算子情報
データベースシステムにおいて、実行計画は内部では通常木構造で表されますが、異なるデータベースではユーザーに表示する方法が異なります。
以下の例は、それぞれPostgreSQLデータベース、Oracleデータベース、およびOceanBaseデータベースにおけるTPCDS Q3の計画表示です。
obclient> SELECT /*TPC-DS Q3*/ *
FROM (SELECT dt.d_year,
item.i_brand_id brand_id,
item.i_brand brand,
Sum(ss_net_profit) sum_agg
FROM date_dim dt,
store_sales,
item
WHERE dt.d_date_sk = store_sales.ss_sold_date_sk
AND store_sales.ss_item_sk = item.i_item_sk
AND item.i_manufact_id = 914
AND dt.d_moy = 11
GROUP BY dt.d_year,
item.i_brand,
item.i_brand_id
ORDER BY dt.d_year,
sum_agg DESC,
brand_id)
WHERE ROWNUM <= 100;
PostgreSQLデータベースの実行計画表示は以下のとおりです：
Limit (cost=13986.86..13987.20 rows=27 width=91) Sort (cost=13986.86..13986.93 rows=27 width=65) Sort Key: dt.d_year, (sum(store_sales.ss_net_profit)), item.i_brand_id HashAggregate (cost=13985.95..13986.22 rows=27 width=65) Merge Join (cost=13884.21..13983.91 rows=204 width=65) Merge Cond: (dt.d_date_sk = store_sales.ss_sold_date_sk) Index Scan using date_dim_pkey on date_dim dt (cost=0.00..3494.62 rows=6080 width=8) Filter: (d_moy = 11) Sort (cost=12170.87..12177.27 rows=2560 width=65) Sort Key: store_sales.ss_sold_date_sk Nested Loop (cost=6.02..12025.94 rows=2560 width=65) Seq Scan on item (cost=0.00..1455.00 rows=16 width=59) Filter: (i_manufact_id = 914) Bitmap Heap Scan on store_sales (cost=6.02..658.94 rows=174 width=14) Recheck Cond: (ss_item_sk = item.i_item_sk) Bitmap Index Scan on store_sales_pkey (cost=0.00..5.97 rows=174 width=0) Index Cond: (ss_item_sk = item.i_item_sk)Oracleデータベースの実行計画表示は以下のとおりです：
Plan hash value: 2331821367 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 9100 | 3688 (1)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 2736 | 243K| 3688 (1)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY | | 2736 | 256K| 3688 (1)| 00:00:01 | | 4 | HASH GROUP BY | | 2736 | 256K| 3688 (1)| 00:00:01 | |* 5 | HASH JOIN | | 2736 | 256K| 3686 (1)| 00:00:01 | |* 6 | TABLE ACCESS FULL | DATE_DIM | 6087 | 79131 | 376 (1)| 00:00:01 | | 7 | NESTED LOOPS | | 2865 | 232K| 3310 (1)| 00:00:01 | | 8 | NESTED LOOPS | | 2865 | 232K| 3310 (1)| 00:00:01 | |* 9 | TABLE ACCESS FULL | ITEM | 18 | 1188 | 375 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | SYS_C0010069 | 159 | | 2 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID| STORE_SALES | 159 | 2703 | 163 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------OceanBaseデータベースの実行計画表示は以下のとおりです：
|ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------- |0 |LIMIT | |100 |81141| |1 | TOP-N SORT | |100 |81127| |2 | HASH GROUP BY | |2924 |68551| |3 | HASH JOIN | |2924 |65004| |4 | SUBPLAN SCAN |VIEW1 |2953 |19070| |5 | HASH GROUP BY | |2953 |18662| |6 | NESTED-LOOP JOIN| |2953 |15080| |7 | TABLE SCAN |ITEM |19 |11841| |8 | TABLE SCAN |STORE_SALES|161 |73 | |9 | TABLE SCAN |DT |6088 |29401| =======================================================
例から分かるように、OceanBaseデータベースの計画表示はOracleデータベースと類似しています。
OceanBaseデータベースの実行計画における各列の意味は以下のとおりです：
| 列名 | 含義 |
|---|---|
ID |
実行木が先順走査によって得られる番号（0から始まる）。 |
OPERATOR |
演算子の名前。 |
NAME |
対応するテーブル操作のテーブル名（インデックス名）。 |
EST. ROWS |
この演算子の出力行数の推定値。 |
COST |
この演算子の実行コスト（マイクロ秒）。 |
説明
テーブル操作において、NAMEフィールドにはその操作に関連するテーブルの名前（エイリアス）が表示されます。インデックスを使用してアクセスする場合は、名前の後にそのインデックスの名前も括弧内に表示されます。例えば、t1(t1_c2)はt1_c2インデックスが使用されていることを示します。スキャンの順序が逆順である場合は、後ろにREVERSEキーワードで識別されます。例えば、t1(t1_c2,REVERSE)のようになります。
OceanBaseデータベースのEXPLAINコマンド出力の最初の部分は、実行計画のツリー構造表示です。ツリー内の各操作の階層は、operatorにおけるインデントによって示されます。階層が最も深いものが優先的に実行され、階層が同じ場合は特定の演算子の実行順序に基づいて実行されます。
上記のTPCDS Q3の例の計画表示ツリーは以下のとおりです：
OceanBaseデータベースのEXPLAINコマンド出力の2番目の部分は、各操作演算子の詳細情報であり、出力式、フィルタ条件、パーティション情報、および各演算子固有の情報（ソートキー、結合キー、プッシュダウン条件など）が含まれます。例：
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC]), prefix_pos(1)
1 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil),
equal_conds([t1.c1 = t2.c2]), other_conds(nil)
2 - output([t2.c1], [t2.c2]), filter(nil), sort_keys([t2.c2, ASC])
3 - output([t2.c2], [t2.c1]), filter(nil),
access([t2.c2], [t2.c1]), partitions(p0)
4 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0)
OceanBaseデータベースのEXPLAIN情報をよりよく理解するために、実行計画演算子を参照してください。