Auroraアップデートのユニットテストで直面した2つの問題

初めまして、今年4月に新卒としてウェルスナビにJOINしたバックエンドグループの藤原です。
今回はAuroraアップデートのユニットテストで直面した2つの問題についてお話ししたいと思います。

Auroraのアップデートについて

弊社では、データベースにAurora 2系（MySQL 5.7）を使用して開発を進めています。
しかし、Aurora 2系は2024年10月31日に標準サポートが終了するため、
現在、Aurora 3系（MySQL 8.0）への移行準備を進めています。

ユニットテストの作成/実施

移行準備の一環として、新卒はSpringのRepositoryに関するユニットテストを作成することになりました。ユニットテストでは、テストコードを書き終えた後、MySQL 5.7とMySQL 8.0の両方でデータが正しく更新・取得できているかを検証しました。その際、MySQL 5.7とMySQL 8.0でテスト結果が異なる問題が発生しました。主に2種類の問題があったので解説していきます。

1. 結合アルゴリズムが変更されたことによる問題

初めにORDER BYを使用した時に生じた問題について、
下記のような２つのテーブルがあったとします。

MyBooksテーブル

Id	Title	CategoryId
4	やまなし	2
1	注文の多い料理店	1
2	銀河鉄道の夜	1
3	よだかの星	1
5	海辺のカフカ	1

テーブル定義

CREATE TABLE MyBooks
(
    Id           bigint(20) NOT NULL, /*MySQLのオプティマイザによっては
                                        PRIMARY KEYでソートしてしまうため、
                                        今回はあえてPRIMARY KEYをつけていません*/
    Title        varchar(255) NOT NULL,
    CategoryId   bigint(20) NOT NULL
)CHARSET=utf8

BookCategoryテーブル

CategoryId	Category
1	SF
2	サスペンス

テーブル定義

CREATE TABLE BookCategory
(
    CategoryId            bigint(20) NOT NULL,
    Category              varchar(255) NOT NULL
)CHARSET=utf8

そしてMyBooksテーブルとBookCategoryテーブルを内部結合させ、BookCategoryのCategoryIdでソートさせたクエリを実行してみます。

SELECT M.Id, M.Title, B.Category FROM MyBooks M JOIN BookCategory B 
ON M.CategoryId = B.CategoryId ORDER BY B.CategoryId

するとMySQL 5.7とMySQL 8.0で違う結果が返ってきました。

MySQL 5.7

Id	Title	Category
5	海辺のカフカ	SF
1	銀河鉄道の夜	SF
2	注文の多い料理店	SF
3	よだかの星	SF
4	やまなし	サスペンス

MySQL 8.0

Id	Title	Category
1	銀河鉄道の夜	SF
2	注文の多い料理店	SF
3	よだかの星	SF
5	海辺のカフカ	SF
4	やまなし	サスペンス

CategoryがSFであるレコードの取得順がMySQL 5.7とMySQL 8.0で異なっています。
このことから、2つ以上のテーブルを結合し、ソートの対象に同値が含まれる（今回の場合はCategoryIdに1が４つ含まれている）クエリでは、MySQL 5.7とMySQL 8.0で差異が生じることが明らかになりました。なお、ORDER BYにM.CategoryIdを指定しても同じ結果でした。

原因

結論として、原因はMySQL 8.0で結合アルゴリズムが変更されたことでした。
この変更によって、ORDER BYによるレコードの取得順が変わってしまいます。
具体的な確認のため、クエリにEXPLAINを付け加えて実行計画を見てみます。

EXPLAIN SELECT M.Id, M.Title, B.Category FROM MyBooks M JOIN BookCategory B 
ON M.CategoryId = B.CategoryId ORDER BY B.CategoryId

MySQL 5.7（カラムが多いため一部割愛）

ID	SELECT_TYPE	TABLE	PARTITIONS	TYPE	・・・	ROWS	FILTERED	EXTRA
1	SIMPLE	B	NULL	ALL		2	100.0	Using temporary; Using filesort
1	SIMPLE	M	NULL	ALL		5	20.0	Using where; Using join buffer (Block Nested Loop)

MySQL 8.0

ID	SELECT_TYPE	TABLE	PARTITIONS	TYPE	・・・	ROWS	FILTERED	EXTRA
1	SIMPLE	B	NULL	ALL		2	100.0	Using temporary; Using filesort
1	SIMPLE	M	NULL	ALL		5	20.0	Using where; Using join buffer (hash join)

実行計画とは、データベースがクエリをどのように実行するかを示す詳細な指示や戦略のことです。
両者の結果を比較すると、EXTRAカラムに含まれる値が異なっています。

• MySQL 5.7: Block Nested Loop
• MySQL 8.0: Hash Join

これらはRDBMSでテーブル同士を結合する際に用いられるアルゴリズムです。
Block Nested Loopは、外側のテーブルを一定のブロックサイズに分け、それぞれのブロックに対して内側のテーブルを全てスキャンし、条件に合致する行を探して結合する手法です。
以下の図はMyBooksを外側のテーブル、BookCategoryを内側のテーブルとした場合です。

ブロックなしの通常のNested Loopの場合は外側の１レコードに対して毎回内部のテーブルにアクセスし条件に合うレコードを探していくため、必ず５回は内部テーブルにアクセスしなければなりません。
一方、Block Nested Loopの場合はブロック単位で内部テーブルにアクセスするため2回のアクセスで済みます。（今回の例では３レコードで１ブロック）

Hash Joinは、片方のテーブルの結合キーを使用してハッシュテーブルを作成し、もう片方のテーブルをスキャンしてハッシュテーブルと照合して結合する手法です。
MySQL 8.0以降は、結合キーにインデックスが設定されていない場合、デフォルトでHash Joinが使用されるようになりました。
https://dev.mysql.com/doc/refman/8.0/ja/hash-joins.html
要するに、結合アルゴリズムの種類によってORDER BYによるレコードの走査順が変わってしまうことが今回の原因でした。

続いて、本当に結合アルゴリズムによってレコードの取得順が変わってしまうのか、MySQL 5.7に適用されているBlock Nested Loopを無効にすることで検証してみます。
DMLに以下のクエリを追加します。

SET optimizer_switch = 'block_nested_loop=off'

このクエリを実行することでBlock Nested Loopを無効化できます。
クエリが適用されているのか先ほど使用したEXPLAIN構文で確認します。
結果

ID	SELECT_TYPE	TABLE	PARTITIONS	TYPE	・・・	ROWS	FILTERED	EXTRA
1	SIMPLE	B	NULL	ALL		2	100.0	Using temporary; Using filesort
1	SIMPLE	M	NULL	ALL		5	20.0	Using where

EXTRAカラムにあったUsing join buffer (Block Nested Loop)が消えていることが確認できました。
では、再度以下のクエリを実行してレコードの取得順を確認します。

SELECT M.Id, M.Title, B.Category FROM MyBooks M JOIN BookCategory B 
ON M.CategoryId = B.CategoryId ORDER BY B.CategoryId

結果

Id	Title	Category
1	銀河鉄道の夜	SF
2	注文の多い料理店	SF
3	よだかの星	SF
5	海辺のカフカ	SF
4	やまなし	サスペンス

見事、MySQL 8.0と同じ取得順になりました。
やはり結合アルゴリズムによって、レコードの取得順が変わることが確認できました。

なお、余談ですが、MySQLでは、オプティマイザと呼ばれるコンポーネントが、実行計画を生成します。どのインデックスを使用するかはオプティマイザによって判断され、ソートの必要性や処理方法についてもオプティマイザが考慮しますが、具体的なソートアルゴリズムの選択はデータのサイズや状況に基づいて決定されます。
https://dev.mysql.com/doc/refman/8.0/ja/order-by-optimization.html

2. 暗黙ソートがされなくなったことによる問題

続いて、GROUP BYを使用したことによって差異が生じた問題について説明します。
MyBooksテーブルをCategoryIdでグループ化した下記のクエリを実行してみます。

SELECT CategoryId, count(*) FROM MyBooks GROUP BY CategoryId

すると、結果は以下の通りでした。
MySQL 5.7

CategoryId	count(*)
1	4
2	1

MySQL 8.0

CategoryId	count(*)
2	1
1	4

この結果から、MySQL 5.7ではGROUP BYに指定されているカラムが自動で昇順ソートされるのに対し、MySQL 8.0ではソートされないことがわかります。ちなみに、GROUP BYに指定されたカラムが自動で昇順ソートされることを暗黙ソートと呼びます。

原因

MySQL 8.0以降は暗黙ソートがされなくなりました。そのため、ソートしたい場合は以下のようにGROUP BYの後にORDER BYを指定する必要があります。

SELECT CategoryId, count(*) FROM MyBooks GROUP BY CategoryId
                                            ORDER BY CategoryId

解決方法

今回は以下の手順を踏んで対応しました。

1. 上記２種類の問題に当てはまるRepositoryメソッドについては、メソッドの使用箇所においてソートの順番が変わっても影響がないか調査を行う。
2. 影響がある場合はクエリの修正もしくはメソッドの使用箇所においてそれぞれ独自にソートを行うよう変更を加える。
3. 影響がない場合はMySQL 5.7とMySQL 8.0どちらでもテストが通るようにテストの実装方法を変える。

結論

今回のプロジェクトではメソッドの使用箇所においてソートの順番が変わっても影響がなかったため、テストの実装方法を変えました。
メソッドの戻り値の並び順がどのように変わっても問題ないように、Stream APIのanyMatchメソッドを使ってアサーションを行うようにしました。

assertTrue(result.stream().anyMatch(e -> e.getId() == 1L &&
                                         e.getTitle() == "銀河鉄道の夜" && 
                                         e.getCategory() == "SF"));

参考

Aurora3系を使うときに気にしたい変更点まとめ
https://zenn.dev/levtech/articles/127a8ec2137603
非公式MySQL 8.0オプティマイザガイド
https://yakst.github.io/unofficialmysqlguide-ja/sorting.html
MySQLのソート処理について
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0111
結合アルゴリズムの仕組みについて
https://zenn.dev/miya_tech/articles/724a16662c5450
Block Nested Loopの仕組みについて
https://enterprisezine.jp/article/detail/3606