Explain Visualizer プラグイン

このテキストは第三者が書いたもので、自動翻訳されています。英語の原文を読む

クエリ実行プランをインタラクティブな図に変換し、クエリの遅い部分を簡単に見つけられるようにする Beekeeper Studio プラグインです。

操作手順: クエリを右クリックしてプランを開き、Explain Analyze を実行して遅いステップへ移動

プランのダイアグラム。各ステップが実行時間（または推定コスト）に占める割合で色分けされます。リンクが太いほど多くの行が流れています。
explain.depesz.com 風のテーブルビュー。セルフタイムで並べ替えできます。
各ステップの詳細: セルフタイムと合計時間、実際の行数と推定行数、ループ数、コスト、条件、バッファ、メモリとディスクの使用量、ワーカー。
よくある問題を指摘するインサイト: 大半の行を捨てるフルスキャン、結合の行ごとにスキャンされるテーブル、ディスクに溢れるソートやハッシュ、依存サブクエリ、SQLite が毎回作成する一時インデックス、大きな行数の見積もり誤りなど。
生のプラン出力。そのままコピーや保存ができます。

詳しくは下のスクリーンショットをご覧ください。

対応データベース

データベース	Explain	Explain Analyze	プランの貼り付け
PostgreSQL	対応	対応	JSON と psql のテキスト
MySQL	対応	8.0.18+、読み取りステートメントのみ	ツリー、JSON（両バージョン）、従来のテーブル形式
MariaDB	対応	10.1+、読み取りステートメントのみ	JSON と従来のテーブル形式
SQLite、LibSQL	対応	非対応: SQLite にはステップごとの時間計測がありません	sqlite3 シェルのツリー、または結果の行
SQL Server	予定

PostgreSQL 12、17、18、MySQL 5.7、8.0.17、8.0、8.4、9.7、MariaDB 10.4、10.6、11.4、SQLite 3.16、3.27、3.45 でテスト済みです。プラグインはデータベースに依存しないプランモデルを中心に作られているため、各データベースに必要なのはアダプターだけです（データベースの追加を参照）。

使い方

開き方はいくつかあります:

クエリエディター: クエリを右クリックして Visualize Explain Plan を選びます。選択中のテキスト、またはカーソル位置のステートメントが使われます。
新規タブメニュー: Explain Visualizer を開き、ステートメントを書いて Explain または Explain Analyze を選びます。
結果グリッド: 自分で EXPLAIN を実行した後、プランを右クリックして次を選びます: Visualize Explain Plan:
- 1つのセルに収まるプラン（PostgreSQL の FORMAT JSON、MySQL のツリーや JSON）: セルを右クリック
- 複数行にわたるプラン（PostgreSQL のテキスト出力）: QUERY PLAN 列のヘッダーを右クリック
- テーブル形式（MySQL の従来の EXPLAIN、SQLite の EXPLAIN QUERY PLAN）: すべての行と列を選択し、選択範囲を右クリック。SQLite では detail 列だけでも動作しますが、入れ子構造は表示されません。
Paste Plan: psql、mysql や sqlite3 シェル、あるいは同僚からもらった EXPLAIN の出力を貼り付けます。

Explain と Explain Analyze

Explain はステートメントを実行せずにデータベースへプランを問い合わせます。常に安全なので、エディターからタブを開くと自動的に実行されます。

Explain Analyze はステートメントを実行し、各ステップを計測します。安全を保つ方法はデータベースごとに異なります（下記参照）。エディターのテキストがすでに EXPLAIN ANALYZE（またはそのデータベースの同等の構文）で始まっている場合はそれを使います。分析できないステートメントは代わりに Explain され、その理由がエディターの下に表示されます。

PostgreSQL

プラグインは EXPLAIN ANALYZE ...; ROLLBACK; を1つのリクエストとして送信します。そのため両方のステートメントが1つの暗黙的トランザクションを共有し、ステートメントによる変更はすべてロールバックされます。書き込みを行う関数を呼び出せる通常の SELECT も対象です。ステートメントが失敗した場合、トランザクションは自動的に中断され、プールされた接続はクリーンな状態に保たれます。シーケンス値など、トランザクション外の影響は元に戻りません。

データを変更するステートメント（INSERT、UPDATE、DELETE、MERGE、データ変更を伴う CTE…）は、確認の後にのみ分析されます。プラグインは文字列、コメント、ドル引用符について PostgreSQL 自身のルールに従うレキサーでステートメントを読み取り、複数のステートメントを含む入力は拒否します。

読み取り専用の接続では Beekeeper Studio が ROLLBACK を許可しないため、データを変更するステートメントは Explain のみ可能で、Explain Analyze が自動実行されることはありません。書き込みを行う関数を呼び出す SELECT はそこでは検出されません。これはクエリタブで実行する場合と同じです。

Options メニューで BUFFERS、VERBOSE、SETTINGS、WAL を切り替えられます。サーバーが対応していないオプションは無効になります。$1 パラメーターを含むステートメントは、PostgreSQL 16+ では GENERIC_PLAN で Explain されます。

MySQL と MariaDB

プラグインはサーバーのバージョンを読み取り、利用できる最新のプラン形式を使います:

サーバー	Explain	Explain Analyze
MySQL 8.0.18+	`EXPLAIN FORMAT=TREE`	`EXPLAIN ANALYZE`
MySQL 5.6 〜 8.0.17	`EXPLAIN FORMAT=JSON`	利用不可
MariaDB 10.1+	`EXPLAIN FORMAT=JSON`	`ANALYZE FORMAT=JSON`
それ以前のサーバー	従来の `EXPLAIN` テーブル	利用不可

単一テーブルの UPDATE と DELETE にはツリーがないため、プラグインは FORMAT=JSON にフォールバックします。JSON でも表示できない MySQL 9 では従来のテーブル形式を使います。TiDB は識別したうえで、誤読せずに拒否します。

Explain Analyze は読み取りステートメントのみ実行します。PostgreSQL と異なり、MySQL では失敗したリクエストが確実に元に戻るとは限りません。START TRANSACTION; ...; ROLLBACK; として送信すると、失敗したステートメントがプールされた接続上でトランザクションを開いたままにし、その接続を他のタブが使ってしまいます。書き込みを行うストアド関数を呼び出す SELECT は、クエリタブで実行した場合と同様に関数の処理をコミットします。MariaDB では Beekeeper Studio の読み取り専用モードが ANALYZE ステートメントを拒否するため、読み取り専用接続では Explain Analyze を利用できません。

ステートメントは MySQL のルールで読み取られます: 改行でのみ終わる # と -- コメント、バックスラッシュエスケープ、"..." 文字列、そして中身を MySQL が実行する /*! ... */ コメントです。NO_BACKSLASH_ESCAPES と ANSI_QUOTES は文字列の終わり方を変え、プールされた接続にはどちらが設定されている可能性もあるため、ステートメントはそのすべての組み合わせで1つのステートメントとして、かつ読み取りとして解釈できる必要があります。サーバーによって複数の解釈があり得るテキストは推測せずに拒否します: バージョン指定付きの /*!50700 ... */ や MariaDB 専用の /*M! ... */ コメント、-- コメント内の復帰文字、NUL 文字などです。

MariaDB はステップごとではなくテーブルごとに時間を計測するため、結合や一時テーブルの時間は、読み取ったテーブル以外にクエリが要した時間となります。

SQLite

EXPLAIN QUERY PLAN は、どのテーブルがどの順序で、どのインデックスを使って読まれるかを示します。SQLite はコスト、行数、時間を報告しないため、ダイアグラムにはステップが、テーブルビューにはその詳細が表示され、Explain Analyze はありません。インサイトは、SQLite が毎回作成する一時インデックス、結合や相関サブクエリの行ごとにスキャンされるテーブル、一時 B-tree でのソートを指摘します。LibSQL 接続も同じエンジンを使います。

Beekeeper Studio は SQLite のテキストを実行前に独自のパーサーでステートメントに分割します。そのため、内部にセミコロンを含むステートメント（文字列、名前、コメント内）は拒否されます。こうすれば、どの分割処理でも2つ目のステートメントが見つかることはありません。

スクリーンショット

Explain Analyze で遅いステップを選択し、そのインサイトを表示した状態:

シーケンシャルスキャンを選択し、そのインサイトを表示した分析済みプラン

同じプランをテーブル表示し、セルフタイムで並べ替えた状態:

セルフタイムで並べ替えたテーブルビュー

CTE、InitPlan、SubPlan を含むクエリ。それぞれが独立したブランチとして表示されます:

CTE、InitPlan、SubPlan のブランチを含むプラン

プラグインは Beekeeper Studio のテーマに従います:

ライトテーマで表示したプラン

開発

Node.js 22+ と Yarn 1 が必要です。

yarn install
yarn dev        # builds with hot reload and links the plugin into Beekeeper Studio
yarn test       # unit tests
yarn build      # type-check and build into dist/

yarn dev は @beekeeperstudio/vite-plugin を使い、このフォルダを Beekeeper Studio のプラグインディレクトリにシンボリックリンクします。

デフォルトでは、Beekeeper Studio は許可リストにある独自のプラグインのみを読み込みます。このプラグインを読み込むには、ユーザー設定ファイルに以下を追加してアプリを再起動してください:

[pluginSystem]
disabled = false

次にデータベースに接続すると、クエリエディターのコンテキストメニューに Visualize Explain Plan が、新規タブメニューに Explain Visualizer が表示されます。

ハーネス

dev/harness.html は Beekeeper Studio の代わりとなるものです。プラグインを iframe で読み込み、アプリと同じメッセージでリクエストに応答し、実際のデータベースに対してクエリを実行します。UI の開発、タブのあらゆる開き方の確認、データベースやテーマの切り替えを最も手早く行える方法です。

docker run -d --name pg -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:17
psql postgres://postgres:pw@localhost:5432/postgres -f scripts/fixtures/seed.sql

docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=pw -e MYSQL_DATABASE=shop -p 3306:3306 mysql:8.4
mysql -h127.0.0.1 -uroot -ppw shop < scripts/fixtures/seed-mysql.sql

PG_URL=postgres://postgres:pw@localhost:5432/postgres \
MYSQL_URL=mysql://root:pw@127.0.0.1:3306/shop \
yarn harness

MARIADB_URL でハーネスの MariaDB を別のサーバーに向けられます（指定しない場合は MYSQL_URL を使用）。SQLite は準備不要です: 初回使用時に scripts/fixtures/seed-sqlite.sql から一時データベースが作成されます。SQLITE_PATH で既存のものを指定することもできます。

テスト

tests/postgres は PostgreSQL 12、17、18 から取得した実際のプラン（tests/fixtures/postgres）をパースします。各フィクスチャには同じプランが JSON とテキストの両形式で含まれ、テストでは両方が同じツリーになることを確認します。
tests/mysql は MySQL 5.7、8.0.17、8.0、8.4、9.7 と MariaDB 10.4、10.6、11.4 から取得したプラン（tests/fixtures/mysql）をパースします。各フィクスチャには1つのステートメントについてそのサーバーが持つすべての形式が含まれます: ツリー、EXPLAIN ANALYZE、両バージョンの JSON、MariaDB の ANALYZE、従来のテーブル形式。テストでは各形式の結果が一致することを確認します。
tests/sqlite は SQLite 3.16（3.24 より前の形式）、3.27、3.45 の EXPLAIN QUERY PLAN の行（tests/fixtures/sqlite）をパースします。
tests/integration は稼働中のサーバーに対して EXPLAIN を実行します: PG_URL が設定されていれば PostgreSQL、MYSQL_URL が設定されていれば MySQL または MariaDB、SQLite は Node 組み込みの node:sqlite を通じて常に実行します。
tests/state はフェイクホストを使ってタブの動作をテストします。

別のサーバーバージョンからフィクスチャを取得するには、データを投入してからキャプチャスクリプトを実行します:

# PostgreSQL: seed with scripts/fixtures/seed.sql
PG_URL=postgres://... FIXTURE_PREFIX=pg16- node scripts/fixtures/capture-postgres.mjs
# MySQL or MariaDB: seed with scripts/fixtures/seed-mysql.sql; named after the server
MYSQL_URL=mysql://root:pw@127.0.0.1:3306/shop node scripts/fixtures/capture-mysql.mjs
# SQLite: uses Python's bundled SQLite, so older Python images give older SQLite
python3 scripts/fixtures/capture-sqlite.py
docker run --rm -v "$PWD:/w" -w /w python:3.6-stretch python3 scripts/fixtures/capture-sqlite.py

仕組み

src/
  core/        database-neutral plan model, analysis, metrics, layout, formatting
  engines/     one adapter per database family, plus the registry
    postgres/  EXPLAIN builder, JSON and text parsers, timing rules, insights
    mysql/     version detection; tree, JSON (both versions) and table readers; insights
    sqlite/    EXPLAIN QUERY PLAN rows and shell text; insights
    shared/    helpers adapters build on: a configurable SQL lexer, statement
               checks, Explain Analyze policies, result readers, a JSON parser
               that keeps repeated keys
  host/        everything that talks to Beekeeper Studio
  state/       the tab's state and actions
  components/  Vue components: diagram, table, details, raw view

各アダプターは、データベースの EXPLAIN 出力を src/core/plan.ts の Plan モデルに変換します。PlanNode は、操作名、kind（scan、index、join、filter、sort…）、任意の推定値（totalCost、estimatedRows）、任意の計測値（actualRows、loops、時間）、算出される inclusiveTime、exclusiveTime、exclusiveCost、さらに詳細パネル用の条件とグループ化されたプロパティを持ちます。すべての指標は任意で、UI はエンジンが提供するものだけを表示します。SQLite のプランには数値がないため、テーブルにはコストの代わりに各ステップの詳細が表示されます。

セルフタイムはエンジン固有なので、アダプターが計算します。PostgreSQL のルールは src/engines/postgres/timing.ts にあります: ループごとの時間にループ数を掛け、並列ワーカーは1回だけ数え、CTE の時間はそれを読む CTE スキャンに、InitPlan の時間はそれを評価するノードに計上します。MySQL もループごとの時間を報告し、共通の computeLoopTimings（src/core/metrics.ts）を使います。select リスト内のサブクエリはクエリの時間に含まれないため、上乗せして加算します。MariaDB はステップではなくテーブルとクエリ全体の時間を計測するため、残りは computeMixedTimings が補います。

MySQL の3つの形式は同じ操作名に読み込まれます: ツリーテキストと JSON バージョン 2 は同じイテレーターを列挙し、クエリブロック形式の JSON（およびそれに再構築された従来のテーブル形式）はテーブル順の結合の連鎖になります。インサイトのルールはネイティブ出力ではなく、これらの名前に対して動作します。

src/core/analysis.ts はすべてのエンジンに共通する処理を追加します: 時間とコストの割合、最も遅いステップ、行数見積もり誤りのインサイトです。

データベースの追加

エンジンのフォルダとレジストリ以外を変更する必要はありません。

フォルダ src/engines/<name>/ を作成し、次を実装したオブジェクトを置きます: ExplainEngine (src/engines/types.ts):
- connectionTypes: 扱う Beekeeper Studio の接続タイプ。複数の製品を扱う場合（MySQL と MariaDB）は products も指定します。
- inspect(sql, server): ステートメントを分割・分類します（データを変更するか？）。src/engines/shared/lexer.ts は方言（コメントの形式、引用符、プレースホルダー）を受け取ります。
- analyzePolicy(statement, context): Explain Analyze で実行してよいか、事前に何を確認するか、エディターの下に何を表示するかを決めます。src/engines/shared/analyzePolicy.ts にロールバック方式と読み取り専用方式のポリシーがあります。
- explain(request, runQuery): データベースに必要な SQL を送信し、結果をパースします。古い形式にフォールバックする場合など、runQuery を複数回呼び出しても構いません。
- loadServerInfo(runQuery): バージョンと、エンジンが知る必要のあるその他の情報（MySQL の SQL モードなど）。ServerInfo を独自のフィールドで拡張してください。
- canParse / parse: 貼り付けられた出力や結果グリッドの値を読み取ります。パースが返す生テキストは、再度パースして同じプランになる必要があります。復元されたタブはそこから再構築されるためです。
- options: Options メニューに表示するトグル。新しいサーバーが必要なものにはそれぞれ unavailable(server) を指定します。
ネイティブの演算子を NodeKind にマッピングし、存在する指標を埋めます。コストと時間には適宜 src/core/metrics.ts のヘルパーを使い、最後に src/core/analysis.ts の finishPlan() を呼び出してください。
src/engines/registry.ts に登録し、実際のサーバーから取得したフィクスチャ（scripts/fixtures/ にキャプチャスクリプトを用意）と、他と同様のテストを追加します。

対応予定の SQL Server に関するメモ:

Beekeeper Studio の SQL Server クライアントはリクエストを ; で分割し、各ステートメントを別々のプール接続で実行することがあります。SET SHOWPLAN_XML ON はバッチ内で単独である必要があり、同じ接続の次のバッチに適用されるため、推定プランには1つの接続でバッチを実行する新しいホスト API が必要です。
SET STATISTICS XML ON とステートメントを1つのバッチ（; なし）にすると実際のプランが得られますが、ステートメントが実行され、その行がプラグインに返されます。
SSMS から貼り付けたプラン XML（.sqlplan）にはホスト側の対応は不要です。

リリース

bin/make-release.sh を実行します。既存のタグから次のバージョンを提案し、manifest.json と package.json の version を設定してコミットし、v0.1.0 のような対応するタグをプッシュします。
リリースワークフローがビルドとテストを行い、manifest.json と explain-visualizer-<version>.zip を含む GitHub リリースの下書きを作成します。下書きを公開してください。

yarn build && yarn package で同じアーカイブをローカルに作成できます。

ライセンス

GPL-3.0。LICENSE をご覧ください。

Explain Visualizer

Readme