とある日

今さながら、8.1.0の変更を少し触ってみる。

MySQL :: MySQL 8.1 Release Notes :: Changes in MySQL 8.1.0 (2023-07-18, Innovation Release)

EXPLAIN FORMAT=JSON の出力をキャプチャする

EXPLAINの結果をJSON形式にしてユーザー変数に保存できるようになった。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 9.4 ユーザー定義変数

SQL 構文に関する注意事項

*JSON:*EXPLAIN FORMAT=JSONこのリリースで追加された構文拡張機能を使用して、ユーザー変数の出力をキャプチャできるようになりました任意の Explainable ステートメントと連携して出力をユーザー変数に保存し、後で分析に使用するために取得できます。この値は有効な JSON ドキュメントであり、などの MySQL JSON 関数を使用して検査および操作できます。( 「JSON 関数」を参照してください。) EXPLAIN FORMAT=JSON INTO *var_name* *stmt*stmt**var_nameJSON_EXTRACT()

この句は;INTOでのみサポートされます。 FORMAT=JSONシステム変数の値は、 explain_formatこの要件には影響しません。(構文エラーなどにより) ステートメントを実行できない場合、ユーザー変数は更新されません。

MySQL :: MySQL 8.1 リリースノート :: MySQL 8.1.0 の変更点 (2023-07-18、イノベーションリリース)

ちなみに、公式のworld_xデータベースを使って検証しています。

EXPLAINをJSON形式で取得する。

(root@localhost) [world_x] 8.1.0 >  EXPLAIN FORMAT=JSON select * from city where id = 3306\G;
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"
    },
    "table": {
      "table_name": "city",
      "access_type": "const",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "ID"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.00",
        "eval_cost": "0.10",
        "prefix_cost": "0.00",
        "data_read_per_join": "256"
      },
      "used_columns": [
        "ID",
        "Name",
        "CountryCode",
        "District",
        "Info"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

これをユーザー変数に保存する。

(root@localhost) [world_x] 8.1.0 > EXPLAIN FORMAT=JSON INTO @stmt select * from city where id = 3306;
Query OK, 0 rows affected (0.00 sec)

stmtを参照すれば先程と同じ結果を取得できる。

(root@localhost) [world_x] 8.1.0 > select @stmt\G;
*************************** 1. row ***************************
@stmt: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"
    },
    "table": {
      "table_name": "city",
      "access_type": "const",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "ID"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.00",
        "eval_cost": "0.10",
        "prefix_cost": "0.00",
        "data_read_per_join": "256"
      },
      "used_columns": [
        "ID",
        "Name",
        "CountryCode",
        "District",
        "Info"
      ]
    }
  }
}
1 row in set (0.00 sec)

この結果に対して色々と検索できる。

(root@localhost) [world_x] 8.1.0 >  select JSON_EXTRACT(@stmt,'$.query_block.table.access_type');
+-------------------------------------------------------+
| JSON_EXTRACT(@stmt,'$.query_block.table.access_type') |
+-------------------------------------------------------+
| "const"                                               |
+-------------------------------------------------------+
1 row in set (0.00 sec)

それっぽく使ってみる

SQLとEXPLAINの結果をテーブルに格納して正しくINDEXを使っているか分析してみる。

EXPLAINの結果を格納するテーブル作成

まず、SQLとEXPLAINを格納するテーブルを作成する。

(root@localhost) [world_x] 8.1.0 >  create table i (id int primary key auto_increment, sql_data text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, explain_data json);
Query OK, 0 rows affected (0.04 sec)

(root@localhost) [world_x] 8.1.0 > desc i
    -> ;
+--------------+------+------+-----+---------+----------------+
| Field        | Type | Null | Key | Default | Extra          |
+--------------+------+------+-----+---------+----------------+
| id           | int  | NO   | PRI | NULL    | auto_increment |
| sql_data     | text | YES  |     | NULL    |                |
| explain_data | json | YES  |     | NULL    |                |
+--------------+------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

EXPLAIN JSONのデータを格納する

(root@localhost) [world_x] 8.1.0 >  EXPLAIN FORMAT=JSON INTO @stmt select * from city where id = 3306;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [world_x] 8.1.0 > insert into i(sql_data,explain_data) values ('select * from city where id = 3306',@stmt);
Query OK, 1 row affected (0.01 sec)

(root@localhost) [world_x] 8.1.0 > TABLE i\G;
*************************** 1. row ***************************
          id: 1
    sql_data: select * from city where id = 3306
explain_data: {"query_block": {"table": {"key": "PRIMARY", "ref": ["const"], "filtered": "100.00", "cost_info": {"eval_cost": "0.10", "read_cost": "0.00", "prefix_cost": "0.00", "data_read_per_join": "256"}, "key_length": "4", "table_name": "city", "access_type": "const", "used_columns": ["ID", "Name", "CountryCode", "District", "Info"], "possible_keys": ["PRIMARY"], "used_key_parts": ["ID"], "rows_examined_per_scan": 1, "rows_produced_per_join": 1}, "cost_info": {"query_cost": "1.00"}, "select_id": 1}}
1 row in set (0.00 sec)

もう1件だけデータを追加する。

insert into i(sql_data,explain_data) values ('select * from city where Name = "Gaza"',@stmt);
EXPLAIN FORMAT=JSON INTO @stmt select * from city where Name = "Gaza";

テーブルの中身。

(root@localhost) [world_x] 8.1.0 > table i\G;
*************************** 1. row ***************************
          id: 1
    sql_data: select * from city where id = 3306
explain_data: {"query_block": {"table": {"key": "PRIMARY", "ref": ["const"], "filtered": "100.00", "cost_info": {"eval_cost": "0.10", "read_cost": "0.00", "prefix_cost": "0.00", "data_read_per_join": "256"}, "key_length": "4", "table_name": "city", "access_type": "const", "used_columns": ["ID", "Name", "CountryCode", "District", "Info"], "possible_keys": ["PRIMARY"], "used_key_parts": ["ID"], "rows_examined_per_scan": 1, "rows_produced_per_join": 1}, "cost_info": {"query_cost": "1.00"}, "select_id": 1}}
*************************** 2. row ***************************
          id: 2
    sql_data: select * from city where Name = "Gaza"
explain_data: {"query_block": {"table": {"filtered": "10.00", "cost_info": {"eval_cost": "39.72", "read_cost": "365.48", "prefix_cost": "405.20", "data_read_per_join": "99K"}, "table_name": "city", "access_type": "ALL", "used_columns": ["ID", "Name", "CountryCode", "District", "Info"], "attached_condition": "(`world_x`.`city`.`Name` = 'Gaza')", "rows_examined_per_scan": 3972, "rows_produced_per_join": 397}, "cost_info": {"query_cost": "405.20"}, "select_id": 1}}
2 rows in set (0.00 sec)

access_typeを検索する

JSONの使い方がわからなかったので以下を見て勉強しました。

MySQLでJSON体験（その1：基本編） - sakaikの日々雑感～(T)編

単純にドットで対象のaccess_typeに対してアクセスして評価する。

select * from i where JSON_EXTRACT(explain_data,'$.query_block.table.access_type') = "const";

結果

(root@localhost) [world_x] 8.1.0 > select * from i where JSON_EXTRACT(explain_data,'$.query_block.table.access_type') = "const"\G;
*************************** 1. row ***************************
          id: 1
    sql_data: select * from city where id = 3306
explain_data: {"query_block": {"table": {"key": "PRIMARY", "ref": ["const"], "filtered": "100.00", "cost_info": {"eval_cost": "0.10", "read_cost": "0.00", "prefix_cost": "0.00", "data_read_per_join": "256"}, "key_length": "4", "table_name": "city", "access_type": "const", "used_columns": ["ID", "Name", "CountryCode", "District", "Info"], "possible_keys": ["PRIMARY"], "used_key_parts": ["ID"], "rows_examined_per_scan": 1, "rows_produced_per_join": 1}, "cost_info": {"query_cost": "1.00"}, "select_id": 1}}
1 row in set (0.00 sec)

〆

JSON形式で取得できるので結構色々できそうです。

いい感じに収集できればINDEXが正しく使われていないSQLを検索することも簡単になりそうです。

ちなみに、最初使い方がわからなくて色々と模索してました。

今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、MySQL 8.1.0でEXPLAIN FORMAT=JSON の結果を使って分析してみたの日。

目次