メタデータの末尾にスキップ
メタデータの先頭に移動

EXPLAIN ステートメントは、Sierraとして知られている Clustrix クエリ オプティマイザ が INSERT, SELECTUPDATE および DELETE ステートメントをどのように実行するかを明らかにするために使われます。EXPLAIN の出力は3つのカラムを示します:

  • "Operation" -  タスクを遂行する内部のオペレータ
  • "Est. Cost" - 見積もりコストは操作が遂行されるのにどれだけの壁時計の時間が必要になるかの測定比率です。
  • "Est. Rows" - Sierraがオペレータによって出力されるだろうと思っている行の見積もり数。

These describe the execution plan as a physical plan to implement the declarative SQL statement. ほとんどの場合、EXPLAINの出力の各行は、単一の操作の収集の入力あるいは続く行が一段インデントされている入力との連携を表します。In other words, most explain statements can be read as most indented is executed first and the total execution follows toward least indented.

データ

EXPLAIN 出力を説明するために、顧客と売った商品を追跡するデータベースを定義および使用する練習をしてみようと思います。この例は説明のためであり、あなたのアプリケーションのためのデータを設計する必ずしも良い方法ではありません -- あなたのアプリケーションのための良いデータモデルは、あなたのビジネスで必要なものと使用パターンによるでしょう。このデータモデルは完結したデータ一貫性モデルよりも関係性に重点を置いています。

We will start with this basic data model, created and populated with a script.

CREATE TABLE customers (
   c_id INTEGER AUTO_INCREMENT
 , name VARCHAR(100)
 , address VARCHAR(100)
 , city VARCHAR(50)
 , state CHAR(2)
 , zip CHAR(10)
 , PRIMARY KEY c_pk (c_id)
) /*$ SLICES=3 */
CREATE TABLE products (
   p_id INTEGER AUTO_INCREMENT
 , name VARCHAR(100)
 , price DECIMAL(5,2)
 , PRIMARY KEY p_pk (p_id)
) /*$ SLICES=3 */
CREATE TABLE orders (
   o_id INTEGER AUTO_INCREMENT
 , c_id INTEGER
 , created_on DATETIME
 , PRIMARY KEY o_pk (o_id)
 , KEY c_fk (c_id)
 , CONSTRAINT FOREIGN KEY c_fk (c_id) REFERENCES customers (c_id)
) /*$ SLICES=3 */
CREATE TABLE order_items (
   oi_id INTEGER AUTO_INCREMENT
 , o_id INTEGER
 , p_id INTEGER
 , PRIMARY KEY oi_pk (oi_id)
 , KEY o_fk (o_id)
 , KEY p_fk (p_id)
 , CONSTRAINT FOREIGN KEY order_fk (o_id) REFERENCES orders (o_id)
 , CONSTRAINT FOREIGN KEY product_fk (p_id) REFERENCES products (p_id)
)  /*$ SLICES=3 */

データベースに投入した後で、 1,000 の顧客、100 の製品、4,000 の注文、および 約 10,000 の商品の注文 があります。

新しく始める

全ての顧客について全ての情報を与える単純なクエリから始めましょう。

> EXPLAIN SELECT * FROM customers;
+------------------------------------------------------+-----------+-----------+
| Operation                                            | Est. Cost | Est. Rows |
+------------------------------------------------------+-----------+-----------+
| stream_combine                                       |    712.70 |   1000.00 |
|   index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+------------------------------------------------------+-----------+-----------+

一般的に、explainの出力は最も内側のインデントから読まれ、最終的に出力の最初の行に行き着くインデントが少ない方へ進みます。上のexplainの出力を読むと、最初に起こるのはインデックスcustomers.__idx_customers__PRIMARYindex_scan で、プライマリキーインデックスであり、読み込みの結果に対して名前を"1"に割り当てます。この場合、名前は再び使われることはありません。リレーションの中で1,000の顧客があるにも関わらず、見積もりの行は約333であることに注意してください。これは、各index_scan が私たちがスライスと呼んでいるクラスタ内に分散されたデータのサブセット読んでいるからです。3つのスライスがあるこのリレーションのスキーマでは、顧客の情報を集めるために並行して3つのindex_scan オペレーションが実行されます。3つの index_scan オペレーションは stream_combine オペレータに渡されます。このオペレータは名前が示すようにストリームを1つに結合してクライアントに届けることができるようにするでしょう。stream_combine オペレータは単純に最初の入力の全体の内容を一つの出力ストリームにコピーし、全てのストリームが結合されるまで続けることで動作します。
クエリにlimitを追加すると何が起こるかを見てみましょう。

> EXPLAIN SELECT * FROM customers LIMIT 10;
+----------------------------------------------------------+-----------+-----------+
| Operation                                                | Est. Cost | Est. Rows |
+----------------------------------------------------------+-----------+-----------+
| row_limit LIMIT := param(0)                              |    615.70 |     10.00 |
|   stream_combine                                         |    615.70 |     30.00 |
|     row_limit LIMIT := param(0)                          |    203.90 |     10.00 |
|       index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+----------------------------------------------------------+-----------+-----------+

row_limitオペレータが追加された以前とほとんど同じ実行プランを得ます。row_limit オペレータは入力ストリームを取り、limit(およびoffset)を満たすと入力を閉じます。. Since there are three parallel streams, Sierra "pushes down" a copy of the row_limit operator to each index_scan stream since there is no need to read more than 10 rows from each slice. ストリームが結合された後で、クライアントが要求した10行を取得するために再び出力を制限します。

結果について並べたいとしましょう。

> EXPLAIN SELECT * FROM customers ORDER BY c_id;
+------------------------------------------------------+-----------+-----------+
| Operation                                            | Est. Cost | Est. Rows |
+------------------------------------------------------+-----------+-----------+
| stream_merge KEYS=[(1 . "c_id") ASC]                 |    816.70 |   1000.00 |
|   index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+------------------------------------------------------+-----------+-----------+

このプランは、今度は結果を結合するために stream_combine ではなく stream_merge があることを除いて、ソートしないバージョンに似ています。stream_merge オペレータは順番に基づいて全ての入力ストリームから取り出して出力ストリームに入れることで動作します。この場合、順番はc_idカラムの昇順で、stream_merge は全てのストリームで一番小さな行を取り出すでしょう。
Clustrixでは、データは一般的にノード間でハッシュ分散されます。 stream_combine は最初にきたものを何であれ返すため、分散されてない常にデータを順番で読み込むデータベースとは結果が異なって見えるかも知れません。例えば:

> SELECT * FROM customers LIMIT 10;
+------+---------------------+--------------+-------------+-------+-------+
| c_id | name                | address      | city        | state | zip   |
+------+---------------------+--------------+-------------+-------+-------+
|    1 | Chanda Nordahl      | 4280 Maple   | Greenville  | WA    | 98903 |
|    2 | Dorinda Tomaselli   | 8491 Oak     | Centerville | OR    | 97520 |
|    9 | Minerva Donnell     | 4644 1st St. | Springfield | WA    | 98613 |
|   21 | Chanda Nordahl      | 5090 1st St. | Fairview    | OR    | 97520 |
|    4 | Dorinda Hougland    | 8511 Pine    | Springfield | OR    | 97477 |
|    6 | Zackary Velasco     | 6296 Oak     | Springfield | OR    | 97286 |
|   11 | Tennie Soden        | 7924 Maple   | Centerville | OR    | 97477 |
|    3 | Shawnee Soden       | 4096 Maple   | Ashland     | WA    | 98035 |
|   24 | Riley Soden         | 7470 1st St. | Greenville  | WA    | 98613 |
|   12 | Kathaleen Tomaselli | 8926 Maple   | Centerville | OR    | 97477 |
+------+---------------------+--------------+-------------+-------+-------+

このクエリの繰り返しは異なる結果を取得するかも知れません。ステートメントにORDER BY節を付けることで、一貫性のある結果を取得することができることを保証します。もっと面白くするために、順番を昇順から降順にも変えてみましょう。

> EXPLAIN SELECT * FROM customers ORDER BY c_id DESC LIMIT 10;
+------------------------------------------------------------------+-----------+-----------+
| Operation                                                        | Est. Cost | Est. Rows |
+------------------------------------------------------------------+-----------+-----------+
| row_limit LIMIT := param(0)                                      |    622.70 |     10.00 |
|   stream_merge KEYS=[(1 . "c_id") DSC]                           |    622.70 |     30.00 |
|     row_limit LIMIT := param(0)                                  |    203.90 |     10.00 |
|       index_scan 1 := customers.__idx_customers__PRIMARY REVERSE |    203.90 |    333.33 |
+------------------------------------------------------------------+-----------+-----------+

この実行プランによると、データベースはまず index_scan オペレータを使って全ての利用可能なスライスから並行して逆にプライマリインデックスを読み、row_limit オペレータを使って10行を読んだ後で停止し、stream_merge オペレータを使ってそれぞれのストリームから c_id に関する一番大きな値を選択することでそれらのストリームをマージし、最後に度々でてくるrow_limitオペレータの適用によって10行に制限されるでしょう。

explainのjoin

これまでのところ、単一のリレーションの読み込みを見てきています。Sierraのジョブの一つに、異なるjoinの順番のコストの比較と、最もコストが低いプランを選択することがあります。このクエリは、order_item 内のそれぞれの行について、注文id、製品名、および価格をもたらすでしょう。

1
2
3
4
5
6
7
8
9
10
11
12
13
> EXPLAIN SELECT o_id, name, price FROM orders o NATURAL JOIN order_items NATURAL JOIN products;
+-------------------------------------------------------------------------------+-----------+-----------+
| Operation                                                                     | Est. Cost | Est. Rows |
+-------------------------------------------------------------------------------+-----------+-----------+
| nljoin                                                                        |  95339.90 |   9882.00 |
|   nljoin                                                                      |  50870.90 |   9882.00 |
|     stream_combine                                                            |     82.70 |    100.00 |
|       index_scan 3 := products.__idx_products__PRIMARY                        |     23.90 |     33.33 |
|     nljoin                                                                    |    507.88 |     98.82 |
|       index_scan 2 := order_items.p_fk, p_id = 3.p_id                         |     63.19 |     98.82 |
|       index_scan 2 := order_items.__idx_order_items__PRIMARY, oi_id = 2.oi_id |      4.50 |      1.00 |
|   index_scan 1 := orders.__idx_orders__PRIMARY, o_id = 2.o_id                 |      4.50 |      1.00 |
+-------------------------------------------------------------------------------+-----------+-----------+

このプランはすこし複雑で何が起きるかを知るためにすこし説明が必要でしょう。

  1. インデントにより、index_scanが最初に起きるだろうことが推測できます。explainの出力の中で、8行目のproductsのプライマリキーの index_scan の中で見つかる p_id が 10行目の order_itemsの p_fk インデックスを読む時に使われ、11行目の order_items プライマリキーを読む時に oi_id が使われることが分かります。本質的には同時に、productsは stream_combine オペレータを使って集められ、order_itemsの情報は order_items.p_fk と order_items プライマリキーインデックスの nljoin を行うことで集められます。
  2. nljoin オペレータはリレーショナル equi-join を実装した 入れ子のループjoin です。
  3. そして、products の stream_combine と order_items の nljoin は、他の nljoinでjoin されます。
  4. order_items.o_id は 読み込みの orders で使われ、その結果は全て最後の nljoin に入れられます。

最後の nljoin の 見積もりの行を見ると、このデータセットの場合、Sierraは 約 9882 order_items 行があると考えていることが分かります。

 
ステージ
オペレーション
Lookup/Scan 表現
Lookup/Scan キー
Run on Node
1Lookup および Forward
__idx_products__PRIMARY
none (スライスを持つ全てのノード)クエリが始まるノード
     
2.1Index Scan
__idx_products__PRIMARY
None, 全ての行__idx_products__PRIMARY のスライスのノード
2.2Lookup および Forwardp_fk
p_id = 3.p_id
同じ
     
3.1Index Scanp_fk
p_id = 3.p_id
p_fk のスライスのノード
3.2Join  同じ
3.3Lookup および Forward
__idx_order_items__PRIMARY 
 oi_id = 2.oi_id同じ
     
4.1Index Scan
__idx_order_items__PRIMARY
oi_id = 2.oi_id
__idx_order_items__PRIMARY のスライスのノード
4.2 Join  同じ
4.3 Lookup および Forward__idx_orders__PRIMARY o_id = 2.o_id同じ
     
5.1Index Scan__idx_orders__PRIMARYo_id = 2.o_id__idx_orders__PRIMARY のスライスのノード
5.2Join   
5.3Lookup および ForwardGTMnone - single GTM node 
     
6ユーザに返されます  クエリが始まるノード

 

ロック

Clustrixデータベースは直列性の保証するために同時実行制御として 2フェーズロック (2PL) を使用します。Sierraはトランザクション中のupdateのreadsと同様にwritesについてもロックを計画します。まず、既に10以上の値を持つpriceの値を1ずつ増加させる単純なupdateを調べましょう。

> EXPLAIN UPDATE products SET price = price + 1 WHERE price > 10;
+-------------------------------------------------------------------------+-----------+-----------+
| Operation                                                               | Est. Cost | Est. Rows |
+-------------------------------------------------------------------------+-----------+-----------+
| table_update products                                                   |   1211.58 |     81.00 |
|   compute expr0 := (1.price + param(0))                                 |   1211.58 |     81.00 |
|     filter (1.price > param(1))                                         |   1210.50 |     81.00 |
|       nljoin                                                            |   1208.70 |     90.00 |
|         pk_lock "products" exclusive                                    |    803.70 |     90.00 |
|           stream_combine                                                |     83.70 |     90.00 |
|             filter (1.price > param(1))                                 |     24.57 |     30.00 |
|               index_scan 1 := products.__idx_products__PRIMARY          |     23.90 |     33.33 |
|         index_scan 1 := products.__idx_products__PRIMARY, p_id = 1.p_id |      4.50 |      1.00 |
+-------------------------------------------------------------------------+-----------+-----------+

このクエリプランの中では:

  1. index_scanを使ってproducts プライマリキーを読み込み、それぞれのスライスで 10以上では無いpriceの行を無視する"pushed down" filter に出力を送信します。
  2. それらの出力はその後stream_combine を使って結合され、そのストリームは pk_lock オペレータを使って行が見つかった行で排他的なプライマリキーロックを取得するためにクラスタ内に分散されます。
  3. そして、見つけたp_idを使って他の index_scan でプライマリキーインデックスを読むことができます。
  4. 最初のindex_scanで見つかった行は行の読み込みとロックの取得から変更されたpriceをもつため、再び filter を適用されます。
  5. 一致した行はpriceの新しい値を計算するcompute オペレータに送られ、新しい行は新しい値を書き込む table_update オペレータに送られます。

いくつかの点で、それぞれの行で修正するために個々の行ロックを取ることは単純に単一のテーブルロックを取って全ての資格のある行を修正することより効果です。The Sierra optimizer will consider using table locks instead of row locks during plan exploration and choose the plan with the lowest cost. In this example, 100 rows would normally be too small to bother with a table lock though if Sierra chose to take a table lock, the plan would look like the following explain.

> EXPLAIN UPDATE products SET price = price + 1;
+------------------------------------------------------------+-----------+-----------+
| Operation                                                  | Est. Cost | Est. Rows |
+------------------------------------------------------------+-----------+-----------+
| table_locks 1                                              |   8084.03 |    100.00 |
|   table_update products                                    |     84.03 |    100.00 |
|     stream_combine                                         |     84.03 |    100.00 |
|       compute expr0 := (1.price + param(0))                |     24.34 |     33.33 |
|         table_lock "products" exclusive                    |     23.90 |     33.33 |
|           index_scan 1 := products.__idx_products__PRIMARY |     23.90 |     33.33 |
+------------------------------------------------------------+-----------+-----------+

ここで興味深いことは、index_scantable_lock の入力のようであるということです。This not the case since the table lock will be acquired prior to any read. この事を覚えておいて、プランを見ます:

  1. リレーションの全ての行を index_scan で読み込みます
  2. compute を使ってpriceに1を追加します
  3. それらの結果を stream_combine を使って一つのストリームに結合します 
  4. 新しい値を書き込むために出力を table_update に送ります

The table_locks operator is a helper operator for Sierra which has a heuristic for balancing the relatively inexpensive single lock with the fact that other updates are blocked, and thus consuming wall-clock time, during this transaction.

パフォーマンスの改善のためにインデックスを使用する

ここまで、結果を取得するためにプライマリキーインデックスを読むことだけを調査してきました。与えられた仕事のために意味のあるインデックスを追加することで、これを変更することができます。例えば、zipでソートされ小さな塊にくっついた顧客情報があればよりうまく行くビジネスプロセスがあるとしましょう。この情報を取得するには:

> EXPLAIN SELECT name, address, city, state, zip FROM customers ORDER BY zip LIMIT 10 OFFSET 0;
+----------------------------------------------------------+-----------+-----------+
| Operation                                                | Est. Cost | Est. Rows |
+----------------------------------------------------------+-----------+-----------+
| row_limit LIMIT := param(0)                              |   2632.70 |     10.00 |
|   sigma_sort KEYS=[(1 . "zip") ASC]                      |   2632.70 |   1000.00 |
|     stream_combine                                       |    712.70 |   1000.00 |
|       index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+----------------------------------------------------------+-----------+-----------+

これはプライマリキーを読み込み、結果を結合し、それらの行を sigma_sort オペレータに送信します。sigma_sort オペレータはメモリ内またはストレージにzipコードで見つかった行をソートするために必要な一時的なコンテナを構築します。一度結果の全てがソートされると、limitとoffsetを実行するためにそれらは row_limit オペレータに入れられます。
もし、全ての行を読むのでなくzipコードの順で読み、zipでソートし、次の10行の塊を返すのであれば、パフォーマンスを大幅に改善することができます。こうするために、customers.zip にインデックスを追加し、Sierraがどう実行プランを変更するかを見ます。

> ALTER TABLE customers ADD INDEX (zip);
> EXPLAIN SELECT name, address, city, state, zip FROM customers ORDER BY zip LIMIT 10 OFFSET 0;
+---------------------------------------------------------------------+-----------+-----------+
| Operation                                                           | Est. Cost | Est. Rows |
+---------------------------------------------------------------------+-----------+-----------+
| msjoin KEYS=[(1 . "zip") ASC]                                       |    674.70 |     10.00 |
|   row_limit LIMIT := param(0)                                       |    622.70 |     10.00 |
|     stream_merge KEYS=[(1 . "zip") ASC]                             |    622.70 |     30.00 |
|       row_limit LIMIT := param(0)                                   |    203.90 |     10.00 |
|         index_scan 1 := customers.zip                               |    203.90 |    333.33 |
|   index_scan 1 := customers.__idx_customers__PRIMARY, c_id = 1.c_id |      4.50 |      1.00 |
+---------------------------------------------------------------------+-----------+-----------+

ここで、クエリオプティマイザは以下を選択します:

  1. 全てのスライスで並行して index_scan オペレータを使って customers.zipインデックスを順番に読みます
  2. "pushed down" row_limit オペレータを使って結果を制限します
  3. それらの結果を結合し、stream_merge オペレータを使って順番を保持します
  4. 結合した結果をもう一つの row_limit を使って制限します
  5. 行の残りを読むためにzipインデックスで見つかったc_idを使います
  6. equi-join を実行するために msjoin オペレータを使います。

msjoin オペレータはnljoinに似ていますが、joinの間にソート順を保持する、"merge sort nested-loop join" です。Notice that in this plan, the sort order is read for the zip index and preserved all the way through the plan which eliminates the need to create a sigma container to sort the results. In other words, this plan streams all results as it goes which can be an important consideration when reading millions of rows.

集合

リレーショナルデータベースを使う時によくあるもう一つのタスクは、合計、平均、最小、または最大を計算するために大きなデータを篩にかけることです。These queries an executed by adding a GROUP BY clause to your statement which declares how you want the data to be aggregated. The Clustrix database also implements the MySQL extension to GROUP BY to allow inclusion of non-aggregated columns in the output columns. もしGROUP BY カラムと集合されないカラムの間に1対1の関係がない場合、集合されないカラムの値は行のうちの一つが返されますが、どの値が返るかは定義されていません。私たちのデータではzipとstateの間に1対1のマッピングがあるため、マッピングを生成する結果セットを生成することができます。

> EXPLAIN SELECT zip, state FROM customers GROUP BY zip;
+--------------------------------------------------------+-----------+-----------+
| Operation                                              | Est. Cost | Est. Rows |
+--------------------------------------------------------+-----------+-----------+
| sigma_distinct_combine KEYS=((1 . "zip"))              |   1303.90 |   1000.00 |
|   sigma_distinct_partial KEYS=((1 . "zip"))            |    203.90 |   1000.00 |
|     index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+--------------------------------------------------------+-----------+-----------+

このクエリは:

  1. まず index_scan を実行し、出力を sigma_distinct_partial オペレータに送ります。
  2. The sigma_distinct_partial operator which produces an output of one row per distinct value of KEYS on the same node as the read.
  3. Those distinct values are then sent to the sigma_distinct_combine operator which will do the same distinct operations on KEYS on the node where the query was initiated.

もっと現実的な集合のために、顧客の名前と各顧客がどれだけの多くの注文をしたかを知りたいとしましょう。

> EXPLAIN SELECT c.name, COUNT(*) FROM orders o NATURAL JOIN customers c GROUP BY o.c_id;
+-------------------------------------------------------------------------------+-----------+-----------+
| Operation                                                                     | Est. Cost | Est. Rows |
+-------------------------------------------------------------------------------+-----------+-----------+
| hash_aggregate_combine GROUPBY((1 . "c_id")) expr1 := countsum((0 . "expr1")) |  12780.38 |   4056.80 |
|   hash_aggregate_partial GROUPBY((1 . "c_id")) expr1 := count((0 . "expr0"))  |   7100.87 |   4056.80 |
|     compute expr0 := param(0)                                                 |   7100.87 |   4056.80 |
|       nljoin                                                                  |   7046.78 |   4056.80 |
|         stream_combine                                                        |    712.70 |   1000.00 |
|           index_scan 2 := customers.__idx_customers__PRIMARY                  |    203.90 |    333.33 |
|         index_scan 1 := orders.c_fk, c_id = 2.c_id                            |      6.33 |      4.06 |
+-------------------------------------------------------------------------------+-----------+-----------+

このプランでは:

  1. The index_scan of the customers primary key is first and combined with stream_combine and the c_id is used to read the orders.c_fk index with another index_scan.
  2. Those results are joined on the node where we read the orders.c_fk index with the nljoin operator and grouped and counted with the hash_aggregate_partial operator on the same node.
  3. The results are then sent to the hash_aggregate_combine operator on the originating node for a final group and count before returning rows to the user.

概要

Hopefully this is a sufficient introduction to the EXPLAIN output for the Clustrix Sierra Query Optimizer that you use to examine your own queries. EXPLAIN の中で現れるかも知れないオペレータの完全なリストについては、 List of Planner Operators を参考にしてください。Sierraがどのようにクエリを最適化するかについての詳細な情報は、分散データベース構造クエリ オプティマイザ 
を見てください。
これらの例においてこのデータセットで遊びたい場合は、データを入れるために使うスクリプトを添付されています。

  • ラベルなし
TOP
inserted by FC2 system