画像: NicoElNino / Shutterstock
MySQLでSQL文を取得する
前回の記事 実行中のSQL文を取得する でも触れたとおり、リレーショナルデータベース(RDBMS)を使うアプリケーションのパフォーマンス分析では、SQL文の取得が欠かせません。MySQLでSQL文を分析する際には、文脈を正しく把握するために補足的なメタ情報も併せて収集することが重要です。具体的には、対象テーブルの構造、テーブルデータや構造に関する統計情報、SQL文の準備・実行における各ステップのタイミングなどです。これらの情報は、SQL文の実行前・実行中・実行後と、それぞれ異なるタイミングで収集します。
補足情報の収集
MySQLでは、以下の補足情報がアプリケーションのSQL文を検証・評価・チューニングする際に役立ちます。いずれもMySQL標準のツールで収集できます。
- 返却行数(実行後)
- 実行時間(実行後)
- クエリ実行プラン(QEP)(実行前)
- テーブル構造(実行前)
- インデックスのカーディナリティ(実行前)
- クエリ実行コスト(実行前)
- クエリオプティマイザの評価(実行前)
- ステータス値(実行前・実行中・実行後)
- クエリプロファイリング(実行後)
- プロセスリスト(実行中)
特に断りのない限り、以下の出力例は mysql コマンドラインクライアントを使用したものです。
注: 本記事のSQL例はMySQL 5.7とMySQL 8.0のいずれでも動作します。MySQL 5.7コミュニティ版がEOL(サポート終了)となったため、出力例とドキュメントの参照リンクはすべて現行のMySQL 8.0を基準にしています。MySQL 5.7では出力内容が一部異なる場合があります。
1\. 返却行数
MySQLクライアントでの返却行数
mysql クライアントでSQLクエリを実行すると、結果の後に表示されるステータス行に返却行数が含まれます。ただし、利用するには取得した出力全体をパースする必要があります。
mysql> SELECT ...
10 rows in set ...
スロークエリログでの返却行数
スロークエリログでは、記録された各SQL文の `Rows_sent` 値として返却行数が出力されます。
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
MySQLスロークエリログの設定・利用・確認方法は、実行中のSQL文を取得する で詳しく解説しています。
performance/sysスキーマでの返却行数
PERFORMANCE_SCHEMA および sys スキーマからも rows_sent を取得できますが、原則として集計値として記録されます。次の例は exec_count=1 なので、rows_sent の値が単一実行のものとして正確に取れています。
mysql> SELECT * FROM sys.x$statement_analysis WHERE query LIKE '%airport%'\G
query: SELECT `a` . `country_code` , `c` . `name` , COUNT ( * ) AS `cnt` FROM `airport` `a` INNER JOIN `country` `c` USING ( `country_code` ) GROUP BY `country_code` , NAME ORDER BY ? DESC LIMIT ?
db: airport
full_scan: *
exec_count: 1
err_count: 0
warn_count: 0
total_latency: 477392952000
max_latency: 477392952000
avg_latency: 477392952000
lock_latency: 221000000
rows_sent: 10
rows_sent_avg: 10
rows_examined: 152960
rows_examined_avg: 152960
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 1
tmp_disk_tables: 0
rows_sorted: 10
sort_merge_passes: 0
digest: 616aa31e4f99b22a194c91ae9f1cfbfb95d6957621b55393b4a01603d8e1fd47
first_seen: 2024-01-18 19:23:42.595635
2\. クエリ実行時間
MySQLクライアントでの実行時間
返却行数と同様に、mysql クライアントは合計実行時間を10ミリ秒単位で表示します。
mysql> SELECT ...
10 rows in set (0.28 sec)
プロファイリングでの実行時間
プロファイリングを使えば、SQL文の合計実行時間を高精度で取得できます。
mysql > SET @@profiling=1;
mysql > SELECT ...
mysql > SET @@profiling=0;
mysql > SHOW PROFILES;
mysql > SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE query_id=1;
注: 別のSELECT文を続けて実行した場合は、対象クエリに合わせて query_id の値を読み替えてください。
mysql > SHOW PROFILES;
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| 1| 0.23497150 | SELECT a.country_code, c.name, COUNT(*) AS cnt
FROM airport a
INNER JOIN country c USING (country_code)
GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10 |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE query_id=9;
+----------------+
| SUM(DURATION) |
+----------------+
| 0.2349715 |
+----------------+
プロファイリングの詳細は後のセクションで解説します。
スロークエリログでの実行時間
スロークエリログに記録されたクエリは、Query_time の値からSQL文の実行時間を高精度で確認できます。
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
3\. クエリ実行プラン(QEP)
EXPLAIN 文はQEPを生成し、MySQLがそのSQL文をどのように実行するかについて貴重な情報を返します。QEPでは、クエリを実行する各ステップが順番に表示され、検討されたインデックス、対象パーティション、結合方式、行数の見積もり、Extra カラムに記載される追加情報などが確認できます。
mysql> EXPLAIN SELECT ...
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+-------+----------+---------------------------------+
| 1 | SIMPLE | a | NULL | ALL | country_code | NULL | NULL | NULL | 86733 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 8 | airport.a.country_code | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+-------+----------+---------------------------------+
2 rows in set, 1 warning (0.04 sec)
EXPLAIN 文は必ず1件の警告を返します。これは内部実行向けに書き換えられたSQL文です。警告が2件以上ある場合は、先に進む前に内容を確認してください。
> SHOW WARNINGS;
Note (Code 1003): /* select#1 */ select `airport`.`a`.`country_code` AS `country_code`,`airport`.`c`.`name` AS `name`,count(0) AS `cnt` from `airport`.`airport` `a` join `airport`.`country` `c` where (`airport`.`c`.`country_code` = `airport`.`a`.`country_code`) group by `airport`.`a`.`country_code` order by count(0) desc limit 10
従来のテーブル形式(FORMAT=TRADITIONAL)に加え、新しいJSON形式も用意されています。JSON形式では cost_info など追加のデータも出力されます。
mysql> EXPLAIN FORMAT=JSON SELECT ...
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "105264.60"
},
"ordering_operation": {
"using_filesort": true,
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [\
{\
"table": {\
"table_name": "a",\
"access_type": "ALL",\
"possible_keys": [\
"country_code"\
],\
"rows_examined_per_scan": 86733,\
"rows_produced_per_join": 86733,\
"filtered": "100.00",\
"cost_info": {\
"read_cost": "1185.00",\
"eval_cost": "8673.30",\
"prefix_cost": "9858.30",\
"data_read_per_join": "256M"\
},\
"used_columns": [\
"airport_id",\
"name",\
"country_code"\
]\
}\
},\
{\
"table": {\
"table_name": "c",\
"access_type": "eq_ref",\
"possible_keys": [\
"PRIMARY"\
],\
"key": "PRIMARY",\
"used_key_parts": [\
"country_code"\
],\
"key_length": "8",\
"ref": [\
"airport.a.country_code"\
],\
"rows_examined_per_scan": 1,\
"rows_produced_per_join": 86733,\
"filtered": "100.00",\
"cost_info": {\
"read_cost": "86733.00",\
"eval_cost": "8673.30",\
"prefix_cost": "105264.60",\
"data_read_per_join": "160M"\
},\
"used_columns": [\
"country_code",\
"name"\
]\
}\
}\
]
}
}
}
}
詳しくは MySQL 8.0リファレンスマニュアル をご参照ください。
後ほど紹介する EXPLAIN ANALYZE オプションの例では、FORMAT=TREE 形式の出力も確認できます。
4\. テーブル構造
mysqlクライアントでテーブル構造を取得する (1)
SQL文に登場する各テーブルについて、カラムや制約の情報を集めることが重要です。SHOW CREATE TABLE 構文を使えば確認できます。
mysql> SHOW CREATE TABLE airport\G
*************************** 1. row ***************************
Table: airport
Create Table: CREATE TABLE `airport` (
`airport_id` int unsigned NOT NULL,
`ident` varchar(7) NOT NULL,
`type` varchar(20) NOT NULL,
`name` varchar(100) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`elevation_ft` smallint DEFAULT NULL,
`continent_code` char(2) NOT NULL,
`country_code` char(2) NOT NULL,
`region_code` char(7) NOT NULL,
`municipality` varchar(60) NOT NULL,
`scheduled_service` tinyint(1) NOT NULL,
`gps_code` char(4) DEFAULT NULL,
`iata_code` char(3) DEFAULT NULL,
`local_code` varchar(7) DEFAULT NULL,
`home_url` varchar(128) DEFAULT NULL,
`wikipedia_url` varchar(128) DEFAULT NULL,
`keywords` varchar(300) DEFAULT NULL,
PRIMARY KEY (`airport_id`),
KEY `name` (`name`),
KEY `country_code` (`country_code`),
KEY `region_code` (`region_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.03 sec)
注: この構文の利点は、出力をそのまま使ってテーブルを再作成できる点です。
詳しくは MySQL 8.0リファレンスマニュアル をご参照ください。
mysqlクライアントでテーブル構造を取得する (2)
DESC SQL文でもテーブル定義を確認できます。ただしこの出力からはテーブルを再作成できず、インデックスのカラムや並び順も正確には特定できません。
mysql> DESC airport;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| airport_id | int unsigned | NO | PRI | NULL | |
| ident | varchar(7) | NO | | NULL | |
| type | varchar(20) | NO | | NULL | |
| name | varchar(100) | NO | MUL | NULL | |
| latitude | float | NO | | NULL | |
| longitude | float | NO | | NULL | |
| elevation_ft | smallint | YES | | NULL | |
| continent_code | char(2) | NO | | NULL | |
| country_code | char(2) | NO | MUL | NULL | |
| region_code | char(7) | NO | MUL | NULL | |
| municipality | varchar(60) | NO | | NULL | |
| scheduled_service | tinyint(1) | NO | | NULL | |
| gps_code | char(4) | YES | | NULL | |
| iata_code | char(3) | YES | | NULL | |
| local_code | varchar(7) | YES | | NULL | |
| home_url | varchar(128) | YES | | NULL | |
| wikipedia_url | varchar(128) | YES | | NULL | |
| keywords | varchar(300) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+-------+
18 rows in set (0.00 sec)
詳しくは MySQL 8.0リファレンスマニュアル をご参照ください。
mysqldumpクライアントでテーブル構造を取得する
あるスキーマ配下のすべてのテーブル定義をまとめて取得したいとき、あるいは単一テーブルの定義だけを取得したいときに便利です。
$ mysqldump --no-data --compact --set-gtid-purged=OFF \
-h${INSTANCE_ENDPOINT} -u${USER} -p${DBA_PASSWD} \
${DB_NAME} airport
出力は SHOW CREATE TABLE 文と同じ内容になります。テーブル構造に付随するコメント行は無視して構いません。
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `airport` (
`airport_id` int unsigned NOT NULL,
`ident` varchar(7) NOT NULL,
`type` varchar(20) NOT NULL,
`name` varchar(100) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`elevation_ft` smallint DEFAULT NULL,
`continent_code` char(2) NOT NULL,
`country_code` char(2) NOT NULL,
`region_code` char(7) NOT NULL,
`municipality` varchar(60) NOT NULL,
`scheduled_service` tinyint(1) NOT NULL,
`gps_code` char(4) DEFAULT NULL,
`iata_code` char(3) DEFAULT NULL,
`local_code` varchar(7) DEFAULT NULL,
`home_url` varchar(128) DEFAULT NULL,
`wikipedia_url` varchar(128) DEFAULT NULL,
`keywords` varchar(300) DEFAULT NULL,
PRIMARY KEY (`airport_id`),
KEY `name` (`name`),
KEY `country_code` (`country_code`),
KEY `region_code` (`region_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
詳しくは MySQL 8.0リファレンスマニュアル をご参照ください。
Information Schemaでテーブル構造を取得する
INFORMATION_SCHEMA テーブルを使ってDESC相当の出力を再現することもできます。ただし他に有効な手段がある以上、実用的とは言えないため、ここでは完全な例ではなく抜粋にとどめています。完全なSQL文にするには、各種データ型のフォーマット、NULL許容性、文字セット、照合順序、すべての制約への対応を加える必要があります。
> SELECT column_name, data_type, character_maximum_length, numeric_precision,
numeric_scale, datetime_precision, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'airport'
AND table_name = 'airport'
ORDER BY ordinal_position;
+-------------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+----------------+
| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | IS_NULLABLE | COLUMN_DEFAULT |
+-------------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+----------------+
| airport_id | int | NULL | 10 | 0 | NULL | NO | NULL |
| ident | varchar | 7 | NULL | NULL | NULL | NO | NULL |
| type | varchar | 20 | NULL | NULL | NULL | NO | NULL |
| name | varchar | 100 | NULL | NULL | NULL | NO | NULL |
| latitude | float | NULL | 12 | NULL | NULL | NO | NULL |
| longitude | float | NULL | 12 | NULL | NULL | NO | NULL |
| elevation_ft | smallint | NULL | 5 | 0 | NULL | YES | NULL |
| continent_code | char | 2 | NULL | NULL | NULL | NO | NULL |
| country_code | char | 2 | NULL | NULL | NULL | NO | NULL |
| region_code | char | 7 | NULL | NULL | NULL | NO | NULL |
| municipality | varchar | 60 | NULL | NULL | NULL | NO | NULL |
| scheduled_service | tinyint | NULL | 3 | 0 | NULL | NO | NULL |
| gps_code | char | 4 | NULL | NULL | NULL | YES | NULL |
| iata_code | char | 3 | NULL | NULL | NULL | YES | NULL |
| local_code | varchar | 7 | NULL | NULL | NULL | YES | NULL |
| home_url | varchar | 128 | NULL | NULL | NULL | YES | NULL |
| wikipedia_url | varchar | 128 | NULL | NULL | NULL | YES | NULL |
| keywords | varchar | 300 | NULL | NULL | NULL | YES | NULL |
+-------------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+----------------+
5\. インデックスのカーディナリティ
テーブル構造からはテーブルに定義されたインデックスを把握できますが、QEPはインデックス列の統計情報の影響も受けます。これを概算で確認する手段の一つが、SHOW INDEXES 文によるインデックスの各列のカーディナリティの確認です。
> SHOW INDEXES FROM airport;
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| airport | 0 | PRIMARY | 1 | airport_id | A | 86733 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | name | 1 | name | A | 70534 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | country_code | 1 | country_code | A | 214 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | region_code | 1 | region_code | A | 3334 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)
注: カーディナリティ列の値が大きいほど、特定の行を絞り込むSELECT文に有利です。
EXTENDEDキーワードを使えばさらに詳細な情報を得られますが、エンドユーザー向けには実用的ではありません。
> SHOW EXTENDED INDEXES FROM airport;
+---------+------------+--------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+--------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| airport | 0 | PRIMARY | 1 | airport_id | A | 86733 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 2 | DB_TRX_ID | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 3 | DB_ROLL_PTR | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 4 | ident | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 5 | type | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 6 | name | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 7 | latitude | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 8 | longitude | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 9 | elevation_ft | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 10 | continent_code | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 11 | country_code | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 12 | region_code | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 13 | municipality | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 14 | scheduled_service | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 15 | gps_code | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 16 | iata_code | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 17 | local_code | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 18 | home_url | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 19 | wikipedia_url | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 20 | keywords | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 1 | name | 1 | name | A | 70534 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | name | 2 | airport_id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | country_code | 1 | country_code | A | 214 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | country_code | 2 | airport_id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | region_code | 1 | region_code | A | 3334 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | region_code | 2 | airport_id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+--------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
26 rows in set (0.00 sec)
詳しくは MySQL 8.0リファレンスマニュアル をご参照ください。
6\. クエリ実行コスト
EXPLAIN ANALYZE コマンドは、クエリ実行の各ステップに関する詳細なコスト情報を返します。実行時間や処理行数などのリソース消費が大きいステップを特定するのに非常に有用です。
mysql > EXPLAIN ANALYZE SELECT ...
-> Limit: 10 row(s) (actual time=350.043..350.045 rows=10 loops=1)
-> Sort: cnt DESC, limit input to 10 row(s) per chunk (actual time=350.043..350.043 rows=10 loops=1)
-> Table scan on <temporary> (actual time=0.003..0.049 rows=245 loops=1)
-> Aggregate using temporary table (actual time=349.935..349.994 rows=245 loops=1)
-> Nested loop inner join (cost=112.60 rows=248) (actual time=0.083..257.689 rows=76475 loops=1)
-> Table scan on c (cost=25.80 rows=248) (actual time=0.051..0.225 rows=248 loops=1)
-> Index lookup on a using country_code (country_code=c.country_code) (cost=0.25 rows=1) (actual time=0.043..0.993 rows=308 loops=248)
詳しくは MySQL 8.0リファレンスマニュアル をご参照ください。
7\. クエリオプティマイザの評価
MySQLでSQLクエリが実行される際は、まずクエリの解析が行われます。続いて、実行ユーザーが対象のテーブルやカラムに対して必要な権限を持っているかが確認されます。クエリキャッシュが有効な場合は、既存のキャッシュ内に該当するクエリがないかが照合され、合致すればキャッシュ済みの結果が返されます。その後、クエリオプティマイザがさまざまな評価とテーブルストレージエンジンの機能を踏まえて、最適な実行経路を決定します。オプティマイザトレースを使うと、オプティマイザが評価した手順や、コストベースの判断に用いた統計情報の詳細を取得できます。
注: この情報は専門性が非常に高く、十分な知識を持つ担当者による特殊な評価のためにのみ収集してください。
> SET SESSION optimizer_trace="enabled=on";
> SELECT ...
> SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
{
"steps": [\
{\
"join_preparation": {\
...\
},\
{\
"join_optimization": {\
"select#": 1,\
"steps": [\
...\
},\
{\
"rows_estimation": [\
{\
"table": "`airport` `a`",\
"table_scan": {\
"rows": 86733,\
"cost": 296.25\
}\
},\
{\
"table": "`country` `c`",\
"table_scan": {\
"rows": 248,\
"cost": 1\
}\
}\
]\
},\
...\
},\
{\
"join_execution": {\
"select#": 1,\
"steps": [\
{\
"temp_table_aggregate": {\
"select#": 1,\
"steps": [\
{\
"creating_tmp_table": {\
"tmp_table_info": {\
"in_plan_at_position": 2,\
"columns": 3,\
"row_length": 218,\
"key_length": 210,\
"unique_constraint": false,\
"makes_grouped_rows": true,\
"cannot_insert_duplicates": false,\
"location": "TempTable"\
}\
}\
}\
]\
}\
},\
{\
"sorting_table": "<temporary>",\
"filesort_information": [\
{\
"direction": "desc",\
"expression": "`cnt`"\
}\
],\
"filesort_priority_queue_optimization": {\
"limit": 10,\
"chosen": true\
},\
"filesort_execution": [\
],\
"filesort_summary": {\
"memory_available": 262144,\
"key_size": 8,\
"row_size": 226,\
"max_rows_per_buffer": 11,\
"num_rows_estimate": 18446744073709551615,\
"num_rows_found": 245,\
"num_initial_chunks_spilled_to_disk": 0,\
"peak_memory_used": 2574,\
"sort_algorithm": "std::sort",\
"unpacked_addon_fields": "using_priority_queue",\
"sort_mode": "<fixed_sort_key, additional_fields>"\
}\
}\
]\
}\
}\
]\
}\
```\
注: 上記のJSON出力は数ページにわたるため抜粋しています。完全版は[こちら](https://gist.github.com/ronaldbradford/14f3e253342da7c7ce64756976b690f7)で参照できます。\
詳しくは[MySQL 8.0リファレンスマニュアル](https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimizer-trace-table.html)をご参照ください。\
8\. MySQLのステータス値\
MySQLには内部的に300を超えるメトリクスがあり、GLOBAL または SESSION の粒度で参照できます。適切なステータス変数を選べば、インデックス利用、WHERE句での絞り込み、データのソートやグルーピングなど、クエリ実行の各段階で行われた内部処理を検証できます。次の簡単な例では内部一時テーブルが使われていますが、それがディスクに書き出されたかどうかを把握しておくことは、実行時間を短縮するためのチューニング余地を見極めるうえで重要です。
注: この情報は専門性が非常に高く、どのメトリクスを使い、どれを切り捨てるべきかを判断できる担当者による特殊な評価のためにのみ収集してください。情報を収集する行為自体が一部のメトリクスに影響を与える点にも注意が必要です。\
mysql> SHOW SESSION STATUS LIKE 'Created_tmp%tables';\
+-------------------------+-------+\
| Variable_name | Value |\
+-------------------------+-------+\
| Created_tmp_disk_tables | 0 |\
| Created_tmp_tables | 4 |\
+-------------------------+-------+\
mysql> SELECT ...\
mysql> SHOW SESSION STATUS LIKE 'Created_tmp%tables';\
+-------------------------+-------+\
| Variable_name | Value |\
+-------------------------+-------+\
| Created_tmp_disk_tables | 1 |\
| Created_tmp_tables | 5 |\
+-------------------------+-------+\
```\
詳しくは[MySQL 8.0リファレンスマニュアル](https://dev.mysql.com/doc/refman/8.0/en/show-status.html)をご参照ください。\
9\. クエリプロファイリング\
クエリに対してプロファイリングを有効にすると、内部処理の各ステップを確認でき、MySQLのソースコードと突き合わせて読むこともできます。次の出力では、実行時間の大半が単一のステップに集中しており、たとえば14行目の0.234060秒がその例です。多くのクエリは似たような時間配分になりますが、複雑なクエリではさらに多くのステップが現れます。
注: この情報は専門性が非常に高く、十分な知識を持つ担当者による特殊な評価のためにのみ収集してください。本記事ではこの分析の詳細には踏み込みません。\
mysql > SET @@profiling=1;\
mysql > SELECT ...\
mysql > SET @@profiling=0;\
mysql > SHOW PROFILES;\
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE query_id=1;\
```\
```\
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+\
| QUERY_ID | SEQ | STATE | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION | SOURCE_FILE | SOURCE_LINE |\
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+\
| 9 | 2 | starting | 0.000125 | 0.000130 | 0.000021 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |\
| 9 | 3 | Executing hook on transaction | 0.000010 | 0.000009 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1376 |\
| 9 | 4 | starting | 0.000011 | 0.000010 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1378 |\
| 9 | 5 | checking permissions | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 2303 |\
| 9 | 6 | checking permissions | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 2303 |\
| 9 | 7 | Opening tables | 0.000180 | 0.000156 | 0.000025 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | open_tables | sql_base.cc | 5803 |\
| 9 | 8 | init | 0.000008 | 0.000006 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 570 |\
| 9 | 9 | System lock | 0.000009 | 0.000007 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 331 |\
| 9 | 10 | optimizing | 0.000012 | 0.000011 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 344 |\
| 9 | 11 | statistics | 0.000027 | 0.000023 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 663 |\
| 9 | 12 | preparing | 0.000017 | 0.000014 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 747 |\
| 9 | 13 | Creating tmp table | 0.000047 | 0.000041 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | create_intermediate_table | sql_executor.cc | 195 |\
| 9 | 14 | executing | 0.234060 | 0.202675 | 0.000000 | 65 | 3 | 0 | 0 | 0 | 0 | 0 | 1222 | 0 | ExecuteIteratorQuery | sql_union.cc | 1130 |\
| 9 | 15 | end | 0.000022 | 0.000020 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 603 |\
| 9 | 16 | query end | 0.000009 | 0.000009 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command_internal | sql_parse.cc | 6372 |\
| 9 | 17 | waiting for handler commit | 0.000231 | 0.000231 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ha_commit_trans | handler.cc | 1707 |\
| 9 | 18 | closing tables | 0.000018 | 0.000021 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | mysql_execute_command_internal | sql_parse.cc | 6450 |\
| 9 | 19 | freeing items | 0.000018 | 0.000015 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | finish_dispatch_sql_command | sql_parse.cc | 6971 |\
| 9 | 20 | logging slow query | 0.000150 | 0.000150 | 0.000000 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 4 | 0 | log_slow_do | log.cc | 1784 |\
| 9 | 21 | cleaning up | 0.000010 | 0.000010 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | finish_dispatch_command | sql_parse.cc | 3409 |\
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+\
20 rows in set, 1 warning (0.00 sec)\
```\
詳しくは[MySQL 8.0リファレンスマニュアル](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html)をご参照ください。\
10\. プロセスリスト\
SQL文の実行中、processlist は実行に時間のかかっているクエリの State や Time を確認するのに役立ちます。ロックなど、他に同時実行されているSQL文への影響を把握する用途でも有効です。processlist をさまざまな方法で取得する手順は 実行中のSQL文を取得する をご参照ください。\
値の精度に関する注意点\
本記事で紹介した分析ツールの多くは、状況によって異なる結果を返します。実行のたびに必ず一致するべき値もあり、たとえば返却行数やテーブル構造がそれにあたります。
繰り返し実行してもほぼ一定に保たれる出力もあります。たとえばQEPはステップ数こそ同じでも、特定のカラムの値だけが異なるといったケースです。QEPは、SQL文に渡す定数値、MySQLのバージョン、設定の変更などにより変化することがあります。
一方で、ほぼ一致しない値もあります。高精度のクエリ実行時間、クエリコスト、プロファイリングの個別ステップなどは平均をとり、適切なパーセンタイル値で類似実行を見極めるべきです。\
まとめ\
SQL文の実行に伴う補足情報を収集するためのネイティブな手段は、MySQLクライアントツールに複数用意されています。これらの情報はSQL文の分析と最適化を進めるうえで欠かせません。
本記事は、SQL文を丸ごと取得するためのさまざまな手法を解説した 実行中のSQL文を取得する の続編です。\
付録\
使用したサンプルデータ\
本記事で利用しているairportデータセットは、こちらで公開されています: https://github.com/ronaldbradford/data/tree/main/mysql-data/airport\
使用したサンプルSQL\
各収集手法では、以下のサンプルSQL文を使用しました。\
SELECT a.country_code, c.name, COUNT(*) AS cnt\
FROM airport a\
INNER JOIN country c USING (country_code)\
GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10;\
```\
### このSQL文は、次のような結果セットを返します:\
```\
+--------------+----------------+-------+\
| country_code | name | cnt |\
+--------------+----------------+-------+\
| US | United States | 30581 |\
| BR | Brazil | 6849 |\
| JP | Japan | 3430 |\
| CA | Canada | 3075 |\
| AU | Australia | 2576 |\
| MX | Mexico | 2288 |\
| RU | Russia | 1556 |\
| KR | South Korea | 1400 |\
| GB | United Kingdom | 1398 |\
| DE | Germany | 1042 |\
+--------------+----------------+-------+\
10 rows in set (0.47 sec)\
```\
注: 空港の実件数や国の並び順は、データセットを取得した時期によって変わる場合があります。\
バッククォート(\`)について\
多くの例では、オブジェクト名がバッククォート(`)で囲まれているのを目にします。これは予約語の利用を許容するための仕様の名残で、予約語はバッククォートで囲む必要があります。ただしツール側は必要なときだけ付与するのではなく、すべてのテーブルやカラムに対して一律に付けるため、可読性を損なう一因となっています。\