🎅

今さらMySQL8の新機能を眺めてみる

toshiki

2024/12/25に公開

MySQL

MySQL 8

tech

!＼スニダンを開発しているSODA inc.の Advent Calendar 2024 25日目の記事です!!!／

 はじめにみなさんこんにちは。メリークリスマス🎅

弊社が提供するサービスSNKRDUNKは先日ようやくMySQL8にアップグレードが完了しました。
どのようにアップグレードを行なったかについては弊社SREチームが公開してるこちらの記事をご覧ください(近々日本語記事も公開されると思います)

https://qiita.com/dangminhduc/items/4cd579a5c741f744d83b
せっかくアップグレードされたので新機能を使っていこうという思い、どのようなことができるようになったのか個人的に気になった箇所をドキュメントを読みながらいくつかピックアップしまとめてみました。

https://dev.mysql.com/doc/refman/8.0/ja/mysql-nutshell.html
それではいきましょう。

 データディクショナリv8以前はテーブル、カラム、インデックスetcなどのメタデータはメタデータファイル、非トランザクションテーブルおよびストレージエンジン固有のデータディクショナリに格納されていましたが、v8からInnoDBテーブルで管理されるようになりました。
InnoDBで管理されたことによって、メタデータにアクセスするSHOW TABLESやSELECT FROM INFORMATION_SCHEMAのパフォーマンスが向上したり、後述するアトミックDDLがサポートされたりなどの利点があるようです。

 アトミックDDLテーブルやインデックスの作成・変更・削除といったDDL操作が完全にアトミックな操作として扱われるようになりました。

v8以前だとALTER TABLEなどでエラーが発生した場合に、テーブルが中途半端な状態になってしまい、手動で巻き戻す必要がありましたが、v8からは途中で失敗した場合はロールバックされるので安心です。

 InnoDBの拡張機能
 innodb_deadlock_detectデッドロック検出を無効にする動的変数innodb_deadlock_detectが新しく追加されました。

デッドロック検出を無効にし、デッドロック発生時のトランザクションロールバックのinnodb_lock_wait_timeout設定に依存する方が効率的な場合があるようです。

 NOWAIT および SKIP LOCKED オプションSELECT ... FOR SHARE, SELECT ... FOR UPDATEで使用できるオプションで挙動を以下のようになります。

 NOWAITリクエストされた行が別のトランザクションによってロックされている場合は即時に結果が返却されます。

 SKIP LOCKEDロックされた行以外が結果セットとして返却されます。
# Session 1:
mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

mysql> INSERT INTO t (i) VALUES(1),(2),(3);

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# Session 2:
mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

# Session 3:

mysql> START TRANSACTION;

mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+

 innodb_autoinc_lock_modeのデフォルト値が2に変更
 innodb_autoinc_lock_mode = 0 (「従来」 ロックモード)このモードの特徴は、各INSERT操作に対して、次に使うAUTO_INCREMENT値を確保するためにテーブル全体をロックします。

 innodb_autoinc_lock_mode = 1 (「連続」 ロックモード)単純INSERT(事前に挿入される行数がわかってる場合)はテーブルロックが回避されるが、INSERT ... SELECTのような一括挿入はテーブルロックが使用されます。

 innodb_autoinc_lock_mode = 2 (「インターリーブ」 ロックモード)全てのINSERTステートメントでテーブルロックが回避される。並列実行時のスケーラビリティは一番高いが、AUTO_INCREMENTの値が連続しない場合があります。

 ALGORITHM=INSTANTのサポート追加v8以前にサポートされていたALGORITHM=COPY, ALGORITHM=INPLACEではALTER TABLEする際に以下のような課題がありました。

 ALGORITHM=COPYテーブル全体を新しい構造でコピーして置き換えるので、大量データを持つテーブルでは時間がかかり、ストレージの使用量も増加します。

 ALGORITHM=INPLACEテーブル全体をコピーせずに変更を適用しますが、いくつかの操作でデータの移動が発生。

一部の変更でロックが必要です。

 ALGORITHM=INSTANTデータファイルには変更を加えず、メタデータを更新するだけなので、データの移動やロックが不要です。

ALTER TABLEが非常に高速に完了し、アプリケーションへの影響が最小限です。
ALGORITHM=INSTANT登場したことによって、オンラインDDLが捗りますね。

サポートされてる操作は以下にまとまってます。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html

 パラレルクラスタインデックス読取り変数innodb_parallel_read_threadsでInnoDBのクラスタインデックスのページを同時に読み取るスレッドの数を指定できます。デフォルトは4つです。

セカンダリインデックススキャンには適用されないようです。

 JSONの拡張機能
 JSON_TABLEv8から追加されたJSON_TABLE()関数では以下のようにリレーショナルテーブルとして返却してくれます。

使い所によっては非常に便利そうですね。
mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a":3,"b":"0"},{"a":"3","b":"1"},{"a":2,"b":1},{"a":0},{"b":[1,2]}]',
    ->     "$[*]" COLUMNS(
    ->       rowid FOR ORDINALITY,
    ->
    ->       xa INT EXISTS PATH "$.a",
    ->       xb INT EXISTS PATH "$.b",
    ->
    ->       sa VARCHAR(100) PATH "$.a",
    ->       sb VARCHAR(100) PATH "$.b",
    ->
    ->       ja JSON PATH "$.a",
    ->       jb JSON PATH "$.b"
    ->     )
    ->   ) AS  jt1;
+-------+------+------+------+------+------+--------+
| rowid | xa   | xb   | sa   | sb   | ja   | jb     |
+-------+------+------+------+------+------+--------+
|     1 |    1 |    1 | 3    | 0    | 3    | "0"    |
|     2 |    1 |    1 | 3    | 1    | "3"  | "1"    |
|     3 |    1 |    1 | 2    | 1    | 2    | 1      |
|     4 |    1 |    0 | 0    | NULL | 0    | NULL   |
|     5 |    0 |    1 | NULL | NULL | NULL | [1, 2] |
+-------+------+------+------+------+------+--------+

 オプティマイザの変更点
 降順インデックスのサポートv8以前もインデックスにDESCを指定することはできましたが、インデックスを逆順でスキャンしていたためパフォーマンス面で問題がありました。

降順インデックスがサポートされたことで、インデックス順にスキャンすることができるようになりました。
↓はMySQL公式ドキュメント8.3.13 降順インデックスに記載されているスニペットですが、4パターンのORDER BYをした時にoptimizerは全て正引きのインデックスを選択します。
CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

ORDER BY c1 ASC, c2 ASC    -- optimizer can use idx1
ORDER BY c1 DESC, c2 DESC  -- optimizer can use idx4
ORDER BY c1 ASC, c2 DESC   -- optimizer can use idx2
ORDER BY c1 DESC, c2 ASC   -- optimizer can use idx3

 関数インデックスキー通常のインデックスは特定のカラムに対して作成しますが、関数インデックスではカラムに適用した関数や式の結果に基づいてインデックスを作成します。
例としてjsonカラムの特定フィールドの検索にインデックスを効かせる方法は以下のようになります。
-- テーブル作成
mysql> CREATE TABLE products (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     attributes JSON
    -> );
Query OK, 0 rows affected (0.04 sec)

-- attributesカラムのpriceフィールドを検索のインデックスを作成
mysql> CREATE INDEX idx_price ON products ((CAST(attributes->>'$.price' AS UNSIGNED)));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 以下のクエリでpossible_keysに追加したidx_priceが選択される
mysql> EXPLAIN SELECT *
    -> FROM products
    -> WHERE CAST(attributes->>'$.price' AS UNSIGNED) > 100;
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | range | idx_price     | idx_price | 9       | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

 共通テーブル式v8から導入されたWITHは複雑なクエリを簡潔に記述できる強力な機能で、一時的な結果セットを名前付きで定義し、それをメインクエリやサブクエリで再利用できます。
以下は全従業員の部署ごとの平均給与と個々の給与を比較する例です。

↓にサブクエリとWITHを使った場合の両方のクエリを書きましたが、WITHを使った方がクエリの見通しが良いですよね。
-- サブクエリで取得する書き方
SELECT emp_name, department, salary, avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS avg_table
JOIN employees
ON employees.department = avg_table.department;
-- WITHを使って取得する書き方
WITH avg_table AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT emp_name, department, salary, avg_salary
FROM avg_table
JOIN employees
ON employees.department = avg_table.department;
他にも階層構造のデータを再起的に取得する使い方もできます。

詳しくはこちらをご参照ください。

https://dev.mysql.com/doc/refman/8.0/ja/with.html

 JSON スキーマ検証JSONドキュメントを再度JSONスキーマで検証するためのJSON_SCHEMA_VALID()および JSON_SCHEMA_VALIDATION_REPORT()の2つの関数が追加されています。

JSON_SCHEMA_VALID()は、ドキュメントがスキーマに対して検証される場合は TRUE (1) を戻し、検証されない場合は FALSE (0) を戻します。JSON_SCHEMA_VALIDATION_REPORT()は、検証の結果に関する詳細情報を含む JSON ドキュメントを返します。

 JSON_SCHEMA_VALID()mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> },
    '> "required": ["latitude", "longitude"]
    '>}';
Query OK, 0 rows affected (0.01 sec)

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 10.445118
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

 JSON_SCHEMA_VALIDATION_REPORT()mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> },
    '> "required": ["latitude", "longitude"]
    '>}';
Query OK, 0 rows affected (0.01 sec)

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 10.445118
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true}                                   |
+---------------------------------------------------+
1 row in set (0.00 sec)

 EXPLAIN ANALYZEEXPLAIN ANALYZEは従来からあるクエリ分析コマンドのEXPLAINとは違って、実際にクエリを実行して見積もり結果と実際にかかったコストを表示してくれます。

実際にクエリを実行するので、optimizerが選択したっけindexが意図してなかった場合などが早期に発見しやすくなり、クエリチューニングがしやすくなります。
mysql> explain select * from items where sneakerId="306008-003";
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | items | NULL       | ref  | fkey_itemSneakerId | fkey_itemSneakerId | 1022    | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain analyze select * from items where sneakerId="306008-003";
+---------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                     |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on items using fkey_itemSneakerId (sneakerId='306008-003')  (cost=0.35 rows=1) (actual time=0.0515..0.0584 rows=1 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
https://dev.mysql.com/doc/refman/8.0/ja/explain.html#explain-analyze

 さいごにこの記事では自分が気になった箇所だけを取り上げていて、変更点は他にもたくさんあるので気になった方は公式ドキュメントをご参照ください。
https://dev.mysql.com/doc/refman/8.0/ja/mysql-nutshell.html
2024年もアドベントカレンダーお疲れ様でした！！！

SODA Engineering Blog

株式会社SODAの開発組織がお届けするZenn Publicationです。是非Entrance Bookもご覧ください！ → recruit.soda-inc.jp/engineer

今さらMySQL8の新機能を眺めてみる

はじめに

データディクショナリ

アトミックDDL

InnoDBの拡張機能

innodb_deadlock_detect

NOWAIT および SKIP LOCKED オプション

NOWAIT

SKIP LOCKED

innodb_autoinc_lock_modeのデフォルト値が2に変更

innodb_autoinc_lock_mode = 0 (「従来」ロックモード)

innodb_autoinc_lock_mode = 1 (「連続」ロックモード)

innodb_autoinc_lock_mode = 2 (「インターリーブ」ロックモード)

ALGORITHM=INSTANTのサポート追加

ALGORITHM=COPY

ALGORITHM=INPLACE

ALGORITHM=INSTANT

パラレルクラスタインデックス読取り

JSONの拡張機能

JSON_TABLE

オプティマイザの変更点

降順インデックスのサポート

関数インデックスキー

共通テーブル式

JSON スキーマ検証

JSON_SCHEMA_VALID()

JSON_SCHEMA_VALIDATION_REPORT()

EXPLAIN ANALYZE

さいごに

Discussion

はじめに

データディクショナリ

アトミックDDL

InnoDBの拡張機能

innodb_deadlock_detect

NOWAIT および SKIP LOCKED オプション

NOWAIT

SKIP LOCKED

innodb_autoinc_lock_modeのデフォルト値が2に変更

innodb_autoinc_lock_mode = 0 (「従来」 ロックモード)

innodb_autoinc_lock_mode = 1 (「連続」 ロックモード)

innodb_autoinc_lock_mode = 2 (「インターリーブ」 ロックモード)

ALGORITHM=INSTANTのサポート追加

ALGORITHM=COPY

ALGORITHM=INPLACE

ALGORITHM=INSTANT

パラレルクラスタインデックス読取り

JSONの拡張機能

JSON_TABLE

オプティマイザの変更点

降順インデックスのサポート

関数インデックスキー

共通テーブル式

JSON スキーマ検証

JSON_SCHEMA_VALID()

JSON_SCHEMA_VALIDATION_REPORT()

EXPLAIN ANALYZE

さいごに

Discussion

innodb_autoinc_lock_mode = 0 (「従来」ロックモード)

innodb_autoinc_lock_mode = 1 (「連続」ロックモード)

innodb_autoinc_lock_mode = 2 (「インターリーブ」ロックモード)