MySQL9.0の新機能 Saving JSON output from EXPLAIN ANALYZE INTO を触ってみる

MySQL9.0 登場！

2024/07/01にMySQL9.0がOracleよりリリースされました。イノベーションリリースの最新版です。

https://dev.mysql.com/doc/refman/9.0/en/mysql-nutshell.html

主な新機能

• JavaScript stored programs(エンプラのみ)
• VECTOR type suport
• Saving JSON output from EXPLAIN ANALYZE INTO

新機能の中でも、Saving JSON output from EXPLAIN ANALYZE INTOは気軽に試せそうなので実験してみます。

Saving JSON output from EXPLAIN ANALYZE INTO

EXPLAINとEXPLAIN ANALYZEの違い

• EXPLAINはクエリを実行する前の計画を出力する。（よく実行計画と言われる）
• EXPLAIN ANALYZEは実際にクエリを実行し、その実行時間などを出力する。

そして、MySQL9.0ではEXPLAIN ANALYZE FORMAT=JSONの結果をユーザ変数に格納する機能が追加されました。
何かしらの重いクエリの調査をしたい際に仕込んておくと便利かもしれないですね。
実際に動かしてみましょう。

クライアントドライバの用意

新しい構文は、古いドライバで呼び出そうとしても未知の構文なので失敗しました。
今回はMacでDataGripを使用します。 まずはMySQL9.0に対応したドライバを用意します。
https://dev.mysql.com/downloads/connector/j/

1. Select Operating System:は Platform Independentを選択
2. Platform Independent (Architecture Independent), ZIP Archive を適当な場所に保存
3. DataGripのData Source and DriversのDriversタブでUserDriversを新規作成
4. Driver Filesの＋ボタンでCustom JASs... で先程ダウンロードしたドライバのJarを選択
5. クラスはcom.mysql.jdbc.Driver
   
6. あとはDataSourcesで先程作成したDriverを選択し、以下のように設定すればクライアント側の準備はOKです。
   

サーバーシステム変数の設定

デフォルトでは、mysql9.0であっても、デフォルトではexplain_json_format_versionが1になっており、上記新機能が使えません

test_schema> EXPLAIN ANALYZE FORMAT=JSON INTO @variables SELECT * FROM item Where name like '商品%'
[2024-07-19 11:38:57] [0A000][6412] EXPLAIN ANALYZE does not support FORMAT=JSON with explain_json_format_version=1.

サーバーシステム変数を書き換えて新機能が使えるようにします。

my.cnf

[mysqld]
explain_json_format_version = 2

test_schema> EXPLAIN ANALYZE FORMAT=JSON INTO @variables SELECT * FROM item Where name like '商品%'
[2024-07-19 11:41:26] 33 ms で完了しました

EXPLAIN ANALYZE INTO結果のINSERTを試してみる

GitHubにサンプルリポジトリを用意しました。Docker環境が必要です。
https://github.com/k-ishii1020/mysql9_test

mysql9_test/sample.sql

USE test_db;
CREATE TABLE item (
                      id INT PRIMARY KEY AUTO_INCREMENT,
                      name VARCHAR(10),
                      description VARCHAR(30),
                      price INT UNSIGNED,
                      created_at DATETIME
);

CREATE TABLE analysis (
    id SERIAL PRIMARY KEY AUTO_INCREMENT,
    analysis JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO item () VALUES ();
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;

UPDATE item SET
  name = CONCAT('商品', id),
  description = SUBSTRING(MD5(RAND()), 1, 30),
  price = CEIL(RAND() * 10000),
  created_at = ADDTIME(CONCAT_WS(' ','2014-01-01' + INTERVAL RAND() * 180 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401))));

テストデータの作成はこちらを参考にさせて頂きました。
https://tech.kurojica.com/archives/29823/

EXPLAIN ANALYZE INTO結果のINSERTを試してみる

EXPLAIN ANALYZE FORMAT=JSON INTO @variables SELECT * FROM item Where name like '商品%';
INSERT INTO analysis (analysis) VALUES (@variables);

SELECT * FROM analysis;

結果

{
    "query": "/* select#1 */ select `test_schema`.`item`.`id` AS `id`,`test_schema`.`item`.`name` AS `name`,`test_schema`.`item`.`description` AS `description`,`test_schema`.`item`.`price` AS `price`,`test_schema`.`item`.`created_at` AS `created_at` from `test_schema`.`item` where (`test_schema`.`item`.`name` like '商品%')",
    "inputs": [
        {
            "operation": "Table scan on item",
            "table_name": "item",
            "access_type": "table",
            "actual_rows": 32768,
            "schema_name": "test_schema",
            "actual_loops": 1,
            "used_columns": [
                "id",
                "name",
                "description",
                "price",
                "created_at"
            ],
            "estimated_rows": 34083,
            "actual_last_row_ms": 21.819370000000003,
            "actual_first_row_ms": 0.173959,
            "estimated_total_cost": 3480.55
        }
    ],
    "condition": "(item.`name` like '商品%')",
    "operation": "Filter: (item.`name` like '商品%')",
    "query_type": "select",
    "access_type": "filter",
    "actual_rows": 32768,
    "actual_loops": 1,
    "estimated_rows": 3786.621406450868,
    "filter_columns": [
        "test_schema.item.`name`"
    ],
    "actual_last_row_ms": 28.602702,
    "actual_first_row_ms": 0.179834,
    "estimated_total_cost": 3480.55
}

重たいクエリを調査する際や、実行した結果のコストなどを調査したい際に便利ですね。
スローログと併用すると便利そうです。
GAはまだまだ先になると思いますが、今後のアップデートに期待です。