はじめに
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データベースを使用して、以下のように実行します。
|
1 |
mysql> SELECT *, sleep(10) FROM city WHERE CountryCode = 'JPN' AND Population > 3000000; |
※手軽にSQLの実行時間を長くしたい為、sleep関数を使用しています。
このSQLを実行している別のコネクションで、プロセス状況を確認すると、先程、実行したSQLのIDが 12 であることが分かります。
|
1 2 3 4 5 6 7 8 |
mysql> SELECT * FROM performance_schema.processlist; +----+-----------------+-----------+-------+---------+------+------------------------+----------------------------------------------------------------------------------+------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | EXECUTION_ENGINE | +----+-----------------+-----------+-------+---------+------+------------------------+----------------------------------------------------------------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 569 | Waiting on empty queue | NULL | PRIMARY | | 12 | root | localhost | world | Query | 3 | User sleep | SELECT *, sleep(10) FROM city WHERE CountryCode = 'JPN' AND Population > 3000000 | PRIMARY | | 13 | root | localhost | NULL | Query | 0 | executing | SELECT * FROM performance_schema.processlist | PRIMARY | +----+-----------------+-----------+-------+---------+------+------------------------+----------------------------------------------------------------------------------+------------------+ |
実行したSQLが終了する前に、以下のように EXPLAIN FOR CONNECTION で確認した 12 を指定すると、実行中のSQLの実行計画を確認することができます。
|
1 2 3 4 5 6 |
mysql> EXPLAIN FOR CONNECTION 12; +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+ | 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 12 | const | 248 | 100.00 | Using index condition; Using where | +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+ |
もし、実行したSQLが中々終了せずに、実行中に意図通りの実行計画となっているか確認したい場合等には有用です。
EXPLAIN FOR CONNECTION についての詳細は、以下のリファレンスをご確認下さい。
EXPLAIN ANALYZE
MySQL 8.0.18 からは、 EXPLAIN ANALYZE が実行できるようになり、こちらでは実行計画を取得するだけでなく、実際にSQLを実行し、その実行過程において何の処理にどの位の時間がかかったかまで確認することができます。
実行例（EXPLAIN ANALYZE）
以下のようなSQLを実行してみます。
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN ANALYZE SELECT * FROM city WHERE CountryCode = 'JPN' AND Population > 3000000; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (city.Population > 3000000) (cost=27 rows=82.7) (actual time=0.366..0.752 rows=2 loops=1) -> Index lookup on city using CountryCode (CountryCode='JPN'), with index condition: (city.CountryCode = 'JPN') (cost=27 rows=248) (actual time=0.364..0.729 rows=248 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
この実行結果から、この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 データベースを２つ用意し、一方の city テーブルからは Country インデックスを削除しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
mysql> show create table world1.city\G *************************** 1. row *************************** Table: city Create Table: CREATE TABLE 'city' ( 'ID' int NOT NULL AUTO_INCREMENT, 'Name' char(35) NOT NULL DEFAULT '', 'CountryCode' char(3) NOT NULL DEFAULT '', 'District' char(20) NOT NULL DEFAULT '', 'Population' int NOT NULL DEFAULT '0', PRIMARY KEY ('ID'), KEY 'CountryCode' ('CountryCode') ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci mysql> show create table world2.city\G *************************** 1. row *************************** Table: city Create Table: CREATE TABLE 'city' ( 'ID' int NOT NULL AUTO_INCREMENT, 'Name' char(35) NOT NULL DEFAULT '', 'CountryCode' char(3) NOT NULL DEFAULT '', 'District' char(20) NOT NULL DEFAULT '', 'Population' int NOT NULL DEFAULT '0', PRIMARY KEY ('ID') ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
(バッククォートは表示の問題上、シングルクォートに変換しています)
この状態で、それぞれのスキーマを指定して実行した結果、Country インデックスを削除した world2 データベースでは、テーブルフルスキャンになっていることが分かります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> EXPLAIN FOR SCHEMA world1 SELECT * FROM city WHERE CountryCode = 'JPN' AND Population > 3000000; +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+ | 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 12 | const | 248 | 33.33 | Using index condition; Using where | +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+ mysql> EXPLAIN FOR SCHEMA world2 SELECT * FROM city WHERE CountryCode = 'JPN' AND Population > 3000000; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 3.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ |
使用してみると、通常余り活用するケースがないように思いますが、例えば水平分割をデータベース単位で行っている場合であれば、カレントデータベースを変更しないでかつ、SQLそのものには手を入れずに実行計画を取得することができるという利点が挙げられます。
存在しないデータベースを指定した場合
もし、存在しないデータベースを指定した場合は、エラーが返されます。
|
1 2 |
mysql> EXPLAIN FOR SCHEMA world3 SELECT * FROM city WHERE CountryCode = 'JPN' AND Population > 3000000; ERROR 1049 (42000): Unknown database 'world3' |
FORMAT指定との併用
従来通り、FORMAT指定とも併用が可能です。
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN FORMAT=tree FOR SCHEMA world1 SELECT * FROM city WHERE CountryCode = 'JPN' AND Population > 3000000; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (city.Population > 3000000) (cost=27 rows=82.7) -> Index lookup on city using CountryCode (CountryCode='JPN'), with index condition: (city.CountryCode = 'JPN') (cost=27 rows=248) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
EXPLAIN ANALYZE との併用
先程ご紹介しました EXPLAIN ANALYZE とも併用して実行することが可能です。
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN ANALYZE FOR SCHEMA world1 SELECT * FROM city WHERE CountryCode = 'JPN' AND Population > 3000000; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (city.Population > 3000000) (cost=27 rows=82.7) (actual time=0.53..1.03 rows=2 loops=1) -> Index lookup on city using CountryCode (CountryCode='JPN'), with index condition: (city.CountryCode = 'JPN') (cost=27 rows=248) (actual time=0.522..1 rows=248 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
しかしながら、EXPLAIN FOR CONNECTION とは併用して使用することができません。
INTO句 との併用
イノベーションリリースとなる MySQL 8.1.0 で EXPLAIN の結果を変数に代入する INTO 句が追加されましたが、EXPLAIN ANALYZE と EXPLAIN FOR CONNECTION は INTO を現時点では指定することができませんが、EXPLAIN FOR SCHEMA では FORMAT=JSON を指定することで使用することが可能です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
mysql> EXPLAIN FORMAT=JSON INTO @result FOR SCHEMA world1 SELECT * FROM city WHERE CountryCode = 'JPN' AND Population > 3000000; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @result\G *************************** 1. row *************************** @result: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "43.55" }, "table": { "table_name": "city", "access_type": "ref", "possible_keys": [ "CountryCode" ], "key": "CountryCode", "used_key_parts": [ "CountryCode" ], "key_length": "12", "ref": [ "const" ], "rows_examined_per_scan": 248, "rows_produced_per_join": 82, "filtered": "33.33", "index_condition": "(<code>world1</code>.<code>city</code>.<code>CountryCode</code> = 'JPN')", "cost_info": { "read_cost": "18.75", "eval_cost": "8.27", "prefix_cost": "43.55", "data_read_per_join": "20K" }, "used_columns": [ "ID", "Name", "CountryCode", "District", "Population" ], "attached_condition": "(<code>world1</code>.<code>city</code>.<code>Population</code> > 3000000)" } } } |
まとめ
通常、EXPLAIN FOR SCHEMA は中々使いどころがないかもしれません。
ですが、データベース単位で水平分割しているようなケースでは、以下のOracle社のブログで紹介されているような各データベースから一括でクエリのコストを確認するようなこともできますので、この機能を大いに活用できるようなシステムもあるのではないでしょうか。
