MySQL道普請便り

第233回MySQL 9.0と9.1の新機能について

MySQLのInnovation ReleaseとなるMySQL 9.0が2024年7月、MySQL 9.1が2024年10月にリリースされました。今回は、その中から気になる新機能をいくつかピックアップして、簡単に紹介したいと思います。

MySQL 9.0の新機能

ここでは、MySQL 9.0の新機能について紹介します。

EXPLAIN ANALYZEのJSONフォーマット結果をユーザー変数へ格納可能に

EXPLAIN ANALYZEのJSONフォ−マット結果をユーザー変数に格納することができるようになりました。

EXPLAIN ANALYZEのJSONフォーマットを利用するには、システム変数explain_json_format_versionを2に変更してから実行する必要があります。デフォルトは1になっています。

INTO句後に格納するユーザー変数を指定します。

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

mysql>  EXPLAIN ANALYZE FORMAT=JSON INTO @buf SELECT * FROM t1 WHERE col1=100;
Query OK, 0 rows affected (0.00 sec)

mysql>  SELECT @buf\G
*************************** 1. row ***************************
@buf: {
  "query": "/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`col1` = 100)",
  "covering": false,
  "operation": "Index lookup on t1 using col1 (col1 = 100)",
  "index_name": "col1",
  "query_type": "select",
  "table_name": "t1",
  "access_type": "index",
  "actual_rows": 0.0,
  "key_columns": [
    "col1"
  ],
  "schema_name": "test",
  "actual_loops": 1,
  "used_columns": [
    "id",
    "col1",
    "col2",
    "d"
  ],
  "estimated_rows": 1.0,
  "lookup_condition": "col1 = 100",
  "index_access_type": "index_lookup",
  "actual_last_row_ms": 0.019208999999999997,
  "actual_first_row_ms": 0.019208999999999997,
  "estimated_total_cost": 0.35
}
1 row in set (0.00 sec)

変数に格納することで、JSON関数を利用してEXPLAINの結果を操作することができるようになります。

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

ちなみに、今回紹介したEXPLAIN ANALYSIS FORMAT=JSON INTOはMySQL 9.0でサポートされましたが、EXPLAIN FORMAT=JSON INTOはMySQL 8.1からサポートされています。

Performance Schemaのシステム変数テーブル

Performance Schemaに新たにvariables_metadataテーブルとglobal_variable_attributesテーブルが追加されました。

variables_metadataテーブルはシステム変数の一般的な情報を表示します。max_connectionsシステム変数を例に見てみましょう。

mysql> SELECT * FROM variables_metadata WHERE VARIABLE_NAME = 'MAX_CONNECTIONS'\G
*************************** 1. row ***************************
 VARIABLE_NAME: max_connections
VARIABLE_SCOPE: GLOBAL
     DATA_TYPE: Integer
     MIN_VALUE: 1
     MAX_VALUE: 100000
 DOCUMENTATION: The number of simultaneous clients allowed
1 row in set (0.00 sec)

該当するシステム変数の取り得る最小値(MIN_VALUE⁠⁠、最大値(MIN_VALUE)や、システム変数に関する説明(DOCUMENTATION)などを確認することができます。注意として、現在のシステム変数の値は表示されません。現在の値を確認したい場合はglobal_variablesテーブル、またはsession_variablesテーブルを利用します。

つづいて、global_variable_attributesテーブルはMySQLサーバーによってグローバルスコープのシステム変数に割り当てられた属性と値のペアに関する情報を表示します。MySQLサーバー自体が管理し使用するテーブルのため、ユーザーが操作や閲覧することはありません。

LIMIT 1を使用した相関サブクエリの最適化

MySQL 8.0.24でoptimizer_switch変数のsubquery_to_derivedフラグが有効な場合、相関するスカラーサブクエリを派生テーブルに変換し、内部でLEFT JOINのクエリに書き換える最適化がサポートされました。この最適化では相関サブクエリにLIMIT 1が含まれている場合は最適化されない仕様になっていましたが、この変更により、LIMIT 1が含まれている場合でも最適化されるようになりました。

最適化前のEXPLAINの例
mysql> explain SELECT id, (SELECT id FROM t1 t WHERE t.col1=t1.col1 LIMIT 1) FROM t1 LIMIT 10;
+----+--------------------+-------+------------+-------+---------------+------+---------+--------------+--------+----------+-------------+
| id | select_type        | table | partitions | type  | possible_keys | key  | key_len | ref          | rows   | filtered | Extra       |
+----+--------------------+-------+------------+-------+---------------+------+---------+--------------+--------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | index | NULL          | col1 | 5       | NULL         | 131035 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | t     | NULL       | ref   | col1          | col1 | 5       | test.t1.col1 |      1 |   100.00 | Using index |
+----+--------------------+-------+------------+-------+---------------+------+---------+--------------+--------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
最適化後のEXPLAINの例
mysql> explain SELECT id, (SELECT id FROM t1 t WHERE t.col1=t1.col1 LIMIT 1) FROM t1 LIMIT 10;
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref          | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------+--------+----------+-------------+
|  1 | PRIMARY     | t1         | NULL       | index | NULL          | col1        | 5       | NULL         | 131035 |   100.00 | Using index |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>   | <auto_key0> | 5       | test.t1.col1 |     10 |   100.00 | NULL        |
|  2 | DERIVED     | t          | NULL       | index | col1          | col1        | 5       | NULL         | 131035 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+--------------+--------+----------+-------------+
3 rows in set, 2 warnings (0.01 sec)

VECTOR型のサポート

VECTOR型がサポートされました。VECTORは、バイナリ文字列値またはリスト形式の文字列として表現できるエントリ(4バイト浮動小数点値)のリストで構成されるデータ構造です。VECTOR列は最大長またはエントリ数(括弧内)で宣言され、デフォルトは2048、最大は16383です。

mysql> CREATE TABLE v (vcol VECTOR(3000));

あわせてSTRING_TO_VECTOR()VECTOR_DIM()など、VECTOR値を取り扱うSQL関数がサポートされました。詳しくは14.21 Vector Functionsをご参照ください。

MySQL 9.1の新機能

つづいて、MySQL 9.1の新機能について紹介します。

読み取り専用操作においてのトリガーの最適化

以前のバージョンでは、トリガーをもつテーブルにアクセスするたびにトリガーを解析し、メモリにロードしていました。そのため、解析によるオーバヘッドや不必要なトリガーロードによるメモリの消費により、予想外のリソース消費や不要な実行時間が加わっていました。

MySQL9.1ではトリガーの解析とメモリーへのロードプロセスを2つのフェーズに分け、適切な場面で実行するようになりました。それにより、不必要なリソースの消費と不要な解析によるオーバヘッドが行われないように修正されました。

CREATE VIEW構文のIF NOT EXISTS句の対応

CREATE VIEW構文にIF NOT EXISTS句がサポートされました。IF NOT EXISTSを指定すると、VIEWが存在しなければ作成されます。VIEWがすでに存在する場合は、エラーではなく警告を表示して成功します。

mysql> CREATE VIEW IF NOT EXISTS view1 AS SELECT col1,col2 FROM t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

また、IF NOT EXISTSOR REPLACEと一緒に使用できません。実行するとエラーになります。

mysql> CREATE OR REPLACE VIEW IF NOT EXISTS view1 AS SELECT col1,col2 FROM t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS view1 AS SELECT col1,col2 FROM t1' at line 1

データベースに関するDDLのクラッシュセーフ

MySQL 8.0からDDLがクラッシュセーフになりました。しかし、CREATE DATABASE構文は実行中にクラッシュすると、ファイルシステム上にファイルが残り、手動での削除が必要でした。また、DROP DATABASE構文は実行中にクラッシュすると、トランザクションがロールバックされないことがありました。

MySQL 9.1からはそれらが改善され、CREATE DATABASEとInnoDBのようなアトミックDDLをサポートするストレージエンジンのテーブルで構成されているデータベースに対するDROP DATABASEが、アトミックでクラッシュセーフになりました。

まとめ

今回はMySQL 9.0とMySQL 9.1のいくつかの新機能について紹介しました。以下のドキュメントを参考にしました。新機能だけでなく、削除や非推奨になった機能もありますので、詳しく知りたい方はご参照ください。

おすすめ記事

記事・ニュース一覧