SQL実行プラン

pev2

SQLのDML処理の改善をする為には各RDBMSのオプティマイザーがそれぞれの環境でどのようにSQLを処理しているか知る必要があります。EXPLAINコマンドがMySQLでもPostgreSQLでも提供されていますが、結果の長い実行プランは可読性が良く無いので、読み解くまでに時間がかかってしまうので、MySQL、PostgreSQL共にExpalinの結果をVisualizeして確認する事が出来る様になっているので是非活用しましょう。

Explain on MySQL

MySQLでは、EXPLAIN, EXPLAIN ANALYZE, EXPLAIN … FOR CONNECTION等を利用してCLIベースで実行プランを確認する事が出来ますが。Visualizeする場合は、以下の様にMySQLチームからオフィシャルに提供されているMySQL Workbenchで可視化すると非常に分かりやすいので便利です。

explainでselect_typeの次に意識しなければいけないのはtypeフィールドになります。
レコードアクセスタイプとも呼ばれ、対象のテーブルに対してどのような方法でアクセスするかを示しています。 問題になるクエリーはこのフィールドを見れば一目で分かるのでとても重要なフィールドとなります。

const・・・PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速。
eq_ref・・・JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ていますがJOINで用いられるところが違う。
ref・・・ユニーク(PRIMARY or UNIQUE)でないインデックスを使って等価検索(WHERE key = value)を行った時に使われるアクセスタイプ。
range・・・インデックスを用いた範囲検索。
index・・・フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。
ALL・・・フルテーブルスキャン。インデックスがまったく利用されていないことを示す。OLTP系の処理では改善必須。
WorkBenchのVisual Explainを利用すると視覚的な色をベースに直感的に判断する事が可能です。
Slow Plan
Moderate Plan
Best Plan

MySQL Community Downloads

13.8.2 EXPLAIN ステートメント

Explain on PostgreSQL

PostgreSQLもMySQL同様にExplain, Explain Analyze等を実行してSQLの実行プランを確認する事が可能です。Analyzeを実行した場合は、実際のSQLの処理も実行される為、参照処理以外のANALYZEオプションの利用は注意が必要です。

可視化するツールはPostgreSQLチームからオフィシャルに提供されてませんが、オープンソースで提供されているツールがあるのでそちらを利用する事で長い実行プランを読みやすくする事が出来るので非常に便利です。

Pev2というツールがCompile済みのTarが準備されているので非常に便利で分かり易いです。

以下の様に、EXPALIN ANALYZEの結果を張り付けて上げると可視化してくれます。

POC=# select p12_001,p12_002,
ST_X(ST_Centroid(ST_Transform(geom, 4326))) AS long,
ST_Y(ST_Centroid(ST_Transform(geom, 4326))) AS lat
from "p12a-14_01" limit 10;
 p12_001 |   p12_002    |        long        |        lat
---------+--------------+--------------------+--------------------
   10022 | 大雪山       | 142.85420010160806 |   43.6636206598375
   10028 | 利尻山       | 141.24202302994075 |  45.17848605936035
   10019 | 羊蹄山       | 140.81121805004122 |  42.82822319916714
   10018 | 駒ヶ岳       |  140.6860528893914 | 42.065889439398575
   10024 | 十勝岳連山   | 142.68604910040537 | 43.417843671767066
   10029 | 斜里岳       | 144.71794277638253 |  43.76567263086662
   10026 | サロベツ原野 | 141.69903725156732 | 45.091089361848084
   10013 | 釧路湿原     | 144.40048171739738 |  43.10513200906569
   10033 | 霧多布湿原   | 145.07577566927762 | 43.083032411348654
   10020 | 雨竜沼湿原   | 141.60188935971385 |  43.69970475866003
(10 rows)

POC=# explain analyze select p12_001,p12_002,
POC-# ST_X(ST_Centroid(ST_Transform(geom, 4326))) AS long,
POC-# ST_Y(ST_Centroid(ST_Transform(geom, 4326))) AS lat
POC-# from "p12a-14_01" limit 10;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..525.44 rows=10 width=40) (actual time=0.049..0.080 rows=10 loops=1)
   ->  Seq Scan on "p12a-14_01"  (cost=0.00..1839.03 rows=35 width=40) (actual time=0.048..0.076 rows=10 loops=1)
 Planning Time: 0.071 ms
 Execution Time: 0.104 ms
(4 rows)

POC=#
  • Tarを展開して、フォルダー毎ブラウザーで開ける場所に配置して、Index.htmlを開くと以下の管理画面が出てくるので、上記の様に取得した実行プランの結果を張り付けてSubmitすると実行プランを可視化する事が出来ます。
pev2

留意:loops値はそのノードを実行する総回数を報告し、表示される実際の時間と行数は1実行当たりの平均です。 これで値を表示された推定コストと比較できるようになります。 loops値をかけることで、そのノードで実際に費やされた総時間を得ることができます。

参照:https://www.postgresql.jp/document/11/html/using-explain.html

可視化ツール:pev2   Pev2ダウンロード:Pev2 Compile済みv0.24.0  NPM: pev2-cli

参考:Using EXPLAIN

実行プランのまとめ

カテゴリー:

最近のコメント

表示できるコメントはありません。