通常、MySQLのクエリチューニングするときに、EXPLAIN構文を使って実行計画を確認することがあると思います。このEXPLAIN構文は昔から存在し、バージョン5.
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
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_EXPLAIN FOR CONNECTION {connection_ を実行することで、そのクエリの実行計画を確認できます。この方法は、リアルタイムでクエリのパフォーマンスを分析する際に特に便利です。
他の出力形式
Format JSON
FORMAT = JSONオプションを指定することで、EXPLAINの出力をJSON形式で得ることが可能です。この機能はバージョン5.
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.
EXPLAIN ANALYZE
MySQL 8.
実際に例として実行してみると、上記の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.
explain_json_format_version
バージョン8.
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_を追加することで、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=JSONやFORMAT=TREEの導入、EXPLAIN ANALYZEの追加は、実行計画の解析をより深く理解するのに役立つかもしれません。より詳細については公式ドキュメント