先日、こんなアナウンスがありました。
タイトルの訳がアレで意味不明ですが、Amazon Aurora MySQL互換エディション・バージョン1.16のラボモード（lab mode）でハッシュ結合（Hash Join）が使えるようになった、ということです。
※~~そう、正式サポートではなく、まだラボモードでの提供です。~~記事の更新を忘れていましたが、既に正式サポートとなっています。
Asynchronous Key Prefetchの検証のときの失敗を繰り返さないよう注意しつつ、検証してみます。
※といいつつ、最初に投稿したときに↑のリンク先を間違えた…。
なお、結論を先に言うと、今回はちゃんと効果を確認できました。
1. ハッシュ結合（Hash Join）とは
テーブルの結合処理をする際に、
- オプティマイザが「小さい」と見積もったほうのテーブルの対象行を、あらかじめ全て読み込んで、
- 結合キーをハッシュ化してメモリ上にテーブル（ハッシュテーブル）を作成し、
- もう一方（オプティマイザが「大きい」と見積もったほう）のテーブルをスキャンし条件に合う行を結合していく
というものです。
結合対象となるテーブルの行数が少ない場合は通常のNested Looped Joinでも問題ないのですが、行数が非常に多い場合、内部表となるテーブルのスキャン効率が悪くなるため、ハッシュ結合を使うと高速化できます。
2. Aurora（MySQL互換 Ver.1.16・ラボモード）での条件・制約
- Aurora MySQL でのハッシュ結合の使用（Amazon Aurora MySQL を使用する際のベストプラクティス）
この説明によると、以下のような条件・制約があるようです。
- 種類が異なる型のカラム間での結合には使えない（但し、
int・numericなどの真数値の間や、float・doubleといった概数値の間での結合には使える） - 文字列型の比較の場合は、キャラクタセットや
collationが一致している必要がある - 外部結合（
LEFT / RIGHT (OUTER) JOIN）には使えない - 準結合（semi join）には使えない
- 複数テーブルの
UPDATE/DELETEには使えない（但し更新・削除対象が単一テーブルの場合は使える）←ベストプラクティス日本語版は誤訳？（英語版を参照） -
BLOB・空間データの列を結合列に使えない（BLOBを結合列に使う人…!?）
特に、外部結合に使えない点に注意が必要です。
3. 使い方
まず、AuroraのDBパラメータグループで、**「aurora_lab_mode」に「1」**を指定してインスタンスに反映しておきます。
その状態で、SET optimizer_switch='hash_join=on'; とすると、コスト計算上有利な場合にハッシュ結合を使うようになります（というか、こちらがデフォルトで、SET optimizer_switch='hash_join=off'; でハッシュ結合が無効になります）。
なお、コストを無視して常にハッシュ結合を使う場合は、SET optimizer_switch='hash_join_cost_based=off'; とします（個人的にはお勧めしませんが）。
4. やってみる
検証には、以前こちらの記事で使ったテーブル・データをAuroraに持ってきて再利用します（使い回しの使い回し…）。
※2つのテーブルにデータが140万行ずつあり、ほとんどの行が「flag=0」です。
なお、検証にはr4.largeインスタンスを使用します（バッファキャッシュを途中でクリアするためにリードレプリカも作成）。
4-1. ハッシュ結合無効のケース
とりあえず、まずはハッシュ結合無効の状態で試してみます。
mysql> USE bp_opt_test;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=0 AND h2.flag=0;
+----------+
| COUNT(*) |
+----------+
| 1399720 |
+----------+
1 row in set (23.17 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=0 AND h2.flag=0;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
| 1 | SIMPLE | h2 | ref | PRIMARY,flag | flag | 5 | const | 650871 | Using index |
| 1 | SIMPLE | h | eq_ref | PRIMARY,flag | PRIMARY | 4 | bp_opt_test.h2.id | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
2 rows in set (0.00 sec)
…遅いですね。
対象のデータページがバッファキャッシュに載ったはずですので、もう一度同じSQLを流してみます。
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=0 AND h2.flag=0;
+----------+
| COUNT(*) |
+----------+
| 1399720 |
+----------+
1 row in set (2.83 sec)
こんな感じです。
4-2. ハッシュ結合有効のケース
フェイルオーバーでレプリカ側のバッファキャッシュをクリアして、ハッシュ結合有効状態で試してみます。
※実際には何度か繰り返しています。
mysql> USE bp_opt_test;
No connection. Trying to reconnect...
Connection id: 6
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=0 AND h2.flag=0;
+----------+
| COUNT(*) |
+----------+
| 1399720 |
+----------+
1 row in set (4.45 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=0 AND h2.flag=0;
+----+-------------+-------+-------+---------------+------+---------+-------+---------+-----------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+---------+-----------------------------------------------------------------------+
| 1 | SIMPLE | h2 | ref | PRIMARY,flag | flag | 5 | const | 650871 | Using index |
| 1 | SIMPLE | h | index | PRIMARY,flag | flag | 5 | NULL | 1326312 | Using where; Using index; Using join buffer (Hash Join Outer table h) |
+----+-------------+-------+-------+---------------+------+---------+-------+---------+-----------------------------------------------------------------------+
2 rows in set (0.08 sec)
EXPLAIN の結果を見るとかえって遅くなりそうな数字が出ていますが、Using join buffer (Hash Join Outer table h) が示す通り、ハッシュ結合が使われて、実行時間が1/5程度になりました！
※複数回繰り返してみたところ、平均で1/6程度でした。
引き続き、バッファキャッシュに載った状態で試してみます。
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=0 AND h2.flag=0;
+----------+
| COUNT(*) |
+----------+
| 1399720 |
+----------+
1 row in set (2.09 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=0 AND h2.flag=0;
+----+-------------+-------+-------+---------------+------+---------+-------+---------+-----------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+---------+-----------------------------------------------------------------------+
| 1 | SIMPLE | h2 | ref | PRIMARY,flag | flag | 5 | const | 650871 | Using index |
| 1 | SIMPLE | h | index | PRIMARY,flag | flag | 5 | NULL | 1326312 | Using where; Using index; Using join buffer (Hash Join Outer table h) |
+----+-------------+-------+-------+---------------+------+---------+-------+---------+-----------------------------------------------------------------------+
2 rows in set (0.02 sec)
※念のため EXPLAIN も再確認。
この後何度か試してみましたが、平均するとハッシュ結合のほうがわずかに速いという結果が出ました。
5. おわりに
特にまとめはありません。
この機能がどのように実装されているのかはわかりませんが、今後、「CPU負荷がスカスカ」らしい、ストレージノードの余ったCPUリソースを有効活用する機能が順次追加されていきそうです。