MySQL の EXPLAIN FOR SCHEMA について

MySQL 8.0
目次

はじめに

MySQLでSQLの実行計画を確認する際には、Explain文 の出力結果を確認するというのが王道です。
Explain文には、出力フォーマットをテーブル形式にしたり、JSON形式にしたりといった出力フォーマットを指定することができますが、その他にも Explainの取得方法を選択することもできます。
MySQL の Innovation リリースとして、バージョン 8.3.0 が先月リリースしましたが、今回は、一つ前の バージョン 8.2.0 で追加された EXPLAIN FOR SCHEMA について、従来の取得方法も簡単に紹介しながら、取り上げたいと思います。

バージョン 8.2.0 のリリースノートは以下となります。

EXPLAIN FOR CONNECTION

MySQL5.7 からは、 EXPLAIN FOR CONNECTION なる構文が登場し、これは、現在実行中のSQLを別のコネクションから、IDを指定して EXPLAIN 情報を取得することができます。

実行例(EXPLAIN FOR CONNECTION)

まずは、MySQLのサンプルデータベースであるworldデータベースを使用して、以下のように実行します。

※手軽にSQLの実行時間を長くしたい為、sleep関数を使用しています。

このSQLを実行している別のコネクションで、プロセス状況を確認すると、先程、実行したSQLのIDが 12 であることが分かります。

実行したSQLが終了する前に、以下のように EXPLAIN FOR CONNECTION で確認した 12 を指定すると、実行中のSQLの実行計画を確認することができます。

もし、実行したSQLが中々終了せずに、実行中に意図通りの実行計画となっているか確認したい場合等には有用です。

EXPLAIN FOR CONNECTION についての詳細は、以下のリファレンスをご確認下さい。

EXPLAIN ANALYZE

MySQL 8.0.18 からは、 EXPLAIN ANALYZE が実行できるようになり、こちらでは実行計画を取得するだけでなく、実際にSQLを実行し、その実行過程において何の処理にどの位の時間がかかったかまで確認することができます。

実行例(EXPLAIN ANALYZE)

以下のようなSQLを実行してみます。

この実行結果から、このSQLで以下の処理を行ったことが読み取れます。

①最初に city テーブルの CountryCode インデックスを使用して CountryCode='JPN' のレコードを抽出します。 rows=248 loops=1 なので、この条件で抽出されたレコードは、248件 ということになります。
②インデックスを使用して CountryCode='JPN' のレコードが抽出された後、 city.Population > 3000000 のフィルタ処理を行い、最終的に rows=2 loops=1 なので、このSQLで抽出されたレコードは 2件 ということが分かります。

また、①の処理については、 actual time=0.364..0.729 の左側の値(0.364)が、この処理で最初の行を返した時間、右側の値(0.729)が最後の行を返した時間という風に読み取れます。
※actual time はミリ秒となります。

なので、上記の actual time の情報から、最初に①のレコードをフェッチするまでに0.364ミリ秒要し、②の処理の最後の行を返した時点が0.752秒であったことが分かります。

EXPLAIN ANALYZE ついての詳細は以下のリファレンスをご参照下さい。

EXPLAIN FOR SCHEMA

そして、本記事の冒頭に記載しました MySQL 8.3.0 で追加されたのが EXPLAIN FOR SCHEMA となります。
この機能は、実行計画を取得するスキーマを指定できるという機能になります。
リファレンスは、以下のリファレンスに簡単に説明が記載されています。

実行例(EXPLAIN FOR SCHEMA)

実際に実行してみたいと思います。
先程までの例同様に world データベースの city テーブルを使用しますが、world データベースを2つ用意し、一方の city テーブルからは Country インデックスを削除しています。

(バッククォートは表示の問題上、シングルクォートに変換しています)

この状態で、それぞれのスキーマを指定して実行した結果、Country インデックスを削除した world2 データベースでは、テーブルフルスキャンになっていることが分かります。

使用してみると、通常余り活用するケースがないように思いますが、例えば水平分割をデータベース単位で行っている場合であれば、カレントデータベースを変更しないでかつ、SQLそのものには手を入れずに実行計画を取得することができるという利点が挙げられます。

存在しないデータベースを指定した場合

もし、存在しないデータベースを指定した場合は、エラーが返されます。

FORMAT指定との併用

従来通り、FORMAT指定とも併用が可能です。

EXPLAIN ANALYZE との併用

先程ご紹介しました EXPLAIN ANALYZE とも併用して実行することが可能です。

しかしながら、EXPLAIN FOR CONNECTION とは併用して使用することができません。

INTO句 との併用

イノベーションリリースとなる MySQL 8.1.0 で EXPLAIN の結果を変数に代入する INTO 句が追加されましたが、EXPLAIN ANALYZE と EXPLAIN FOR CONNECTION は INTO を現時点では指定することができませんが、EXPLAIN FOR SCHEMA では FORMAT=JSON を指定することで使用することが可能です。

まとめ

通常、EXPLAIN FOR SCHEMA は中々使いどころがないかもしれません。
ですが、データベース単位で水平分割しているようなケースでは、以下のOracle社のブログで紹介されているような各データベースから一括でクエリのコストを確認するようなこともできますので、この機能を大いに活用できるようなシステムもあるのではないでしょうか。

スマートスタイルTECHブログについて

スマートスタイルTECHブログでは、日頃MySQLのサポート業務に従事している有資格者で構成された技術サポートチームがMySQLに関する技術情報を発信しています。データベースのお困りごとはお気軽にご相談下さい。

よかったらシェアしてね!
  • URLをコピーしました!
目次