MySQL道普請便り

第222回EXPLAIN FORMATによるクエリ実行計画の出力の違い

通常、MySQLのクエリチューニングするときに、EXPLAIN構文を使って実行計画を確認することがあると思います。このEXPLAIN構文は昔から存在し、バージョン5.7ではJSON形式での表示、バージョン8.0ではツリー形式の表示が可能になりました。また、2024年5月にリリースされたバージョン8.4では、さらにいくつかの変更点が導入されています。この記事ではEXPLAINによる実行計画の出力の違いについて見ていきましょう。なお、実行計画の中身の見方については今回は解説しません。

EXPLAIN構文

mysql> EXPLAIN SELECT randum_string FROM dummy WHERE randum_number = 19918;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | dummy | NULL       | ref  | idx           | idx  | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

クエリの実行計画を確認するには、クエリの前にEXPLAINを付けて実行します。EXPLAINの出力形式は、FORMATオプションで指定可能で、Traditional(デフォルト⁠⁠、JSON、Treeの3種類があります。バージョン5.6以降では、EXPLAINはSELECT文だけでなく、INSERT、UPDATE、DELETE、REPLACE文の実行計画も出力できるようになりました。また、EXPLAIN構文はDESCRIBE構文と同義であり、テーブル構造の表示や実行計画の取得に使用できます。

mysql> EXPLAIN dummy;
+---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| id            | int        | NO   | PRI | NULL    |       |
| randum_number | int        | NO   | MUL | NULL    |       |
| randum_string | mediumtext | YES  |     | NULL    |       |
| randum_status | int        | NO   | MUL | NULL    |       |
| randum_date   | datetime   | NO   |     | NULL    |       |
| randum_date2  | datetime   | NO   |     | NULL    |       |
+---------------+------------+------+-----+---------+-------+
6 rows in set (0.04 sec)

mysql> DESC SELECT randum_string FROM dummy WHERE randum_number = 19918;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | dummy | NULL       | ref  | idx           | idx  | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

実行中のクエリに対しても実行計画を取得することが可能です。取得したいクエリが属するconnection_idを確認した後、EXPLAIN FOR CONNECTION {connection_id} を実行することで、そのクエリの実行計画を確認できます。この方法は、リアルタイムでクエリのパフォーマンスを分析する際に特に便利です。

他の出力形式

Format JSON

FORMAT = JSONオプションを指定することで、EXPLAINの出力をJSON形式で得ることが可能です。この機能はバージョン5.7で導入されました。JSON形式では、従来の形式に加えてcost_infoなどの詳細な情報も確認できるため、クエリのコストや行の処理数に関する深い洞察が可能になります。

mysql> EXPLAIN FORMAT = JSON SELECT randum_string FROM dummy WHERE randum_number = 19918\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
(省略)
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "40"
      },
(省略)
}

Format tree

mysql> EXPLAIN FORMAT = tree SELECT randum_string FROM dummy WHERE randum_number = 19918\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on dummy using idx (randum_number=19918)  (cost=0.35 rows=1)

1 row in set (0.00 sec)

バージョン8.0.16以降、FORMAT= TREEが追加され、EXPLAINの出力をツリー形式で表示できるようになりました。この形式ではクエリの実行構造を視覚的に把握しやすくなり、複雑なクエリの解析が直感的で効率的に行えるようになります。ツリー形式は、クエリの各ステップがどのように連携しているかを明確に示すため、パフォーマンスのチューニング時に特に有用です。

EXPLAIN ANALYZE

MySQL 8.0.18以降、EXPLAIN ANALYZEというものが追加されています。EXPLAINによる実行計画は予測に基づく情報を返すのに対し、これはクエリが実際にどのように実行されたかを示し、実行時間やオペレーターの実行コスト、行数の推移など実際に実行した結果を返します。これもFORMAT = Tree形式で結果を表示します。

実際に例として実行してみると、上記のFORMAT = Tree形式の出力に追加して、actual timeの項目が出力されていることが確認できます。

mysql> EXPLAIN ANALYZE SELECT randum_string FROM dummy WHERE randum_number = 19918\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on dummy using idx (randum_number=19918)  (cost=0.35 rows=1) (actual time=0.0401..0.0401 rows=0 loops=1)

1 row in set (0.01 sec)

バージョン8.4の追加点

2024年5月にリリースされたMySQLバージョン8.4でもEXPLAIN周りにいくつか追加された機能があります(8.1以降に追加された機能も含みます⁠⁠。

explain_json_format_version

バージョン8.4で導入された新しいJSONフォーマットは、MySQL Optimizerの将来のバージョンとの互換性を目的として追加されました。この新フォーマットに切り替えるには、システム変数@@explain_json_format_versionでバージョンを指定します。デフォルト設定はバージョン1ですが、この設定を2に変更することで異なるフォーマットの詳細を得ることが可能です。バージョン1と比較するとkey_lenなどの情報がなくなっていることが確認できます。

mysql> set session explain_json_format_version = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN FORMAT = JSON SELECT randum_string FROM dummy WHERE randum_number = 19918\G
*************************** 1. row ***************************
EXPLAIN: {
  "query": "/* select#1 */ select `d1`.`dummy`.`randum_string` AS `randum_string` from `d1`.`dummy` where (`d1`.`dummy`.`randum_number` = 19918)",
  "covering": false,
  "operation": "Index lookup on dummy using idx (randum_number=19918)",
  "index_name": "idx",
  "query_type": "select",
  "table_name": "dummy",
  "access_type": "index",
  "schema_name": "d1",
  "used_columns": [
    "randum_number",
    "randum_string"
  ],
  "estimated_rows": 1.0,
  "lookup_condition": "randum_number=19918",
  "index_access_type": "index_lookup",
  "estimated_total_cost": 1.1
}

FOR SCHEMAによる実行SCHEMAの指定

EXPLAINを実行する際にFOR SCHEMA {SCHEMA_NAME}を追加することで、EXPLAINを実行するSCHEMAを指定できるようになりました。

mysql> EXPLAIN SELECT randum_string FROM dummy WHERE randum_number = 19918\G
ERROR 1046 (3D000): No database selected
mysql> EXPLAIN FOR SCHEMA d1 SELECT randum_string FROM dummy WHERE randum_number = 19918\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dummy
   partitions: NULL
         type: ref
possible_keys: idx
          key: idx
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

INTO オプション

EXPLAINのINTOオプションを使用すると、EXPLAINの出力結果をJSON形式で直接変数に格納することができます。この機能により、得られた実行計画のデータをJSON関数を使用して加工することが可能となり、さらに他の実行計画との比較分析などが容易に行えるようになります。

mysql> EXPLAIN FORMAT = JSON INTO @a SELECT randum_string FROM dummy WHERE randum_number = 19918\G
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT json_extract(@a, "$.access_type");
+-----------------------------------+
| json_extract(@a, "$.access_type") |
+-----------------------------------+
| "index"                           |
+-----------------------------------+
1 row in set (0.00 sec)

まとめ

今回はEXPLAIN構文について、バージョンアップと共に追加された機能をピックアップして簡単に紹介しました。これらの新機能により、MySQLのクエリ実行計画の分析がより詳細かつ柔軟に行えるようになりました。特にFORMAT=JSONFORMAT=TREEの導入、EXPLAIN ANALYZEの追加は、実行計画の解析をより深く理解するのに役立つかもしれません。より詳細については公式ドキュメント「EXPLAIN Statement」を参考にしてください。

おすすめ記事

記事・ニュース一覧