クエリチューニングをする際には、最初に実行計画を確認します。クエリチューニングをよく行う方は、少し複雑なクエリの実行計画を見たときに、key列に<auto_key0>という表示を見つけたことがあると思います。今回はこの実行計画に表示される<auto_key0>（以後、auto_key）について紹介したいと思います。

auto_keyとは

auto_keyは1つの最適化機能であり、クエリ実行中に作成された内部一時テーブルに対して自動的に生成されるインデックスを指します。実行計画のkey列やEXPLAINの後にSHOW WARNINGSステートメントを発行することで表示される追加情報にて、auto_keyが作成されたかどうかを確認することができます。

auto_keyは、準結合（セミジョイン）の内部一時テーブルを利用した戦略や、内部一時テーブル（導出テーブル）を利用したJOINなどのクエリにて作成されることが多いです。内部一時テーブルをJOINの内部表として選択し、その結合条件のカラムにauto_keyが作成され、それを利用しネステッドループジョイン（NLJ）する動作になります。セミジョインについては以前の記事で紹介していますので、先にご確認ください。

• 第43回 MySQLの準結合（セミジョイン）について
• 第103回 MySQL 8.0のセミジョインの変更点

auto_keyの実行計画を確認する

以下のようなテーブルを用意します。

mysql> CREATE TABLE t1 ( c0 bigint key, c1 varchar(10),c2 varchar(512));
Query OK, 0 rows affected (0.01 sec)
 
mysql> CREATE TABLE t2 ( c0 bigint key ,c1 varchar(10),c2 varchar(512));
Query OK, 0 rows affected (0.00 sec)

t2テーブルのGROUP BYしたc1カラムとt1テーブルをc2カラムで結合したクエリの実行計画で確認してみます。

〈一部表示を割愛し行数の説明を追加しています〉

mysql> EXPLAIN SELECT * FROM t1 JOIN ( SELECT c1 FROM t2 GROUP BY c1) t2 ON t2.c1=t1.c2;
+----+-------------+------------+------+-------------+---------+-----------+------+----------+--------------------------+
| id | select_type | table      | type | key         | key_len | ref       | rows | filtered | Extra                    |
+----+-------------+------------+------+-------------+---------+-----------+------+----------+--------------------------+
|  1 | PRIMARY     | t1         | ALL  | NULL        | NULL    | NULL      |    1 |   100.00 | Using where              | （1行目）
|  1 | PRIMARY     | <derived2> | ref  | <auto_key0> | 43      | db0.t1.c2 |    2 |   100.00 | Using where; Using index | （2行目）
|  2 | DERIVED     | t2         | ALL  | NULL        | NULL    | NULL      |    1 |   100.00 | Using temporary          | （3行目）
+----+-------------+------------+------+-------------+---------+-----------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `db0`.`t1`.`c0` AS `c0`,`db0`.`t1`.`c1` AS `c1`,`db0`.`t1`.`c2` AS `c2`,`t2`.`c1` AS `c1` from `db0`.`t1` join (/* select#2 */ select `db0`.`t2`.`c1` AS `c1` from `db0`.`t2` group by `db0`.`t2`.`c1`) `t2` where (`t2`.`c1` = `db0`.`t1`.`c2`)

実行計画から以下のことが確認できます。

1. （3行目）Extra列のUsing temporaryとselect_type列のDERIVEDから、はじめにt2テーブルのGROUP BYしたクエリの結果が内部一時テーブルとして作成されています。
2. （1行目）table列から駆動表としてt1テーブルが選択されていることがわかります。
3. （2行目）table列の<derived2>は（3行目）の内部一時テーブルを意味します。（⁠3行目）のidが2なので、<derived2>となります。そして、<auto_key0>の表示が確認できるので、その内部一時テーブルに対して自動的にインデックスが生成されました。ref列の値db0.t1.c2は結合元のカラムなので、そのカラムと結合しているc1カラムに対してインデックスが作成されたことがわかります。

このように内部一時テーブルに対してインデックスを作成してから結合することにより、クエリの速度が向上します。この最適化がないと、「⁠t1テーブルの行数×内部一時テーブルのすべての行」を走査することになります。このauto_keyによる最適化は基本的にはオプティマイザーが自動で選択してくれます。しかし、一部auto_keyによる最適化を狙ったクエリチューニングもありますので、次項で紹介します。

auto_keyによる最適化を狙ったクエリチューニング

MySQL 8.0.34現在、auto_keyとして作成可能なバイト数は1000バイトになります。よって、サイズの大きいvarcharの結合においてはauto_keyを作成することができません。その場合はHASH JOINが選択されます。

mysql> CREATE TABLE test1 ( id serial, c1 varchar(512) NOT NULL);
mysql> CREATE TABLE test2 ( id serial, c1 varchar(512) NOT NULL);

mysql> EXPLAIN SELECT * FROM test1 JOIN ( SELECT c1 FROM test2 GROUP BY 1) test2 USING(c1);

〈一部表示割愛〉
+----+-------------+------------+------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table      | type | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+------------+------+------+---------+------+------+----------+--------------------------------------------+
|  1 | PRIMARY     | test1      | ALL  | NULL | NULL    | NULL |    1 |   100.00 | NULL                                       |
|  1 | PRIMARY     | <derived2> | ALL  | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (hash join) |
|  2 | DERIVED     | test2      | ALL  | NULL | NULL    | NULL |    1 |   100.00 | Using temporary                            |
+----+-------------+------------+------+------+---------+------+------+----------+--------------------------------------------+

Note (Code 1003): /* select#1 */ select `db0`.`test1`.`c1` AS `c1`,`db0`.`test1`.`id` AS `id` from `db0`.`test1` join (/* select#2 */ select `db0`.`test2`.`c1` AS `c1` from `db0`.`test2` group by `db0`.`test2`.`c1`) `test2` where (`test2`.`c1` = `db0`.`test1`.`c1`)

HASH JOINであれば実行時間は速いので、auto_keyの最適化が選択されなくてもレイテンシーとしては問題ないでしょう。しかし、HASH JOINはメモリを多く使用しリソースを消費するため、頻繁に呼ばれるクエリの場合はNLJへ変更した方が良いこともあります。

このケースでは以下のように書き換えることで、auto_keyを利用したNLJへ変更できます。

mysql EXPLAIN SELECT * FROM test1 JOIN ( SELECT CONVERT(c1 USING latin1) AS c1 FROM test2 GROUP BY 1) test2 ON CONVERT(test1.c1 USING latin1)=test2.c1;
+----+-------------+------------+------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | type | possible_keys | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------+---------------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY     | test1      | ALL  | NULL          | NULL    | NULL |    1 |   100.00 | NULL                     |
|  1 | PRIMARY     | <derived2> | ref  | <auto_key0>   | 515     | func |    2 |   100.00 | Using where; Using index |
|  2 | DERIVED     | test2      | ALL  | NULL          | NULL    | NULL |    1 |   100.00 | Using temporary          |
+----+-------------+------------+------+---------------+---------+------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `db0`.`test1`.`id` AS `id`,`db0`.`test1`.`c1` AS `c1`,`test2`.`c1` AS `c1` from `db0`.`test1` join (/* select#2 */ select convert(`db0`.`test2`.`c1` using latin1) AS `c1` from `db0`.`test2` group by convert(`db0`.`test2`.`c1` using latin1)) `test2` where (convert(`db0`.`test1`.`c1` using latin1) = `test2`.`c1`)

チューニングの詳細を説明します。MySQL 8.0のデフォルトキャラクターセットはutf8mb4なので、最大4バイトを使用します。

c1カラムの格納する最大文字数を表す長さが512であり、実データにかかわらず512×4バイトで2048バイトを使用する可能性があるため、このクエリはauto_keyの作成ができません。そこで、最大1バイトを使用するlatin1のキャラクターセットにコンバートすることで対応しています。1000バイト以下になるようにしてからJOINすることで、auto_keyが作成できるようになります。ただし、この方法はc1カラムのデータにマルチバイト文字が含まれていると、正しい結果にならない可能性があるのでご注意ください。あまり使用する機会のないチューニング方法だと思いますので、参考程度に覚えておくとよいでしょう。

まとめ

今回は実行計画に表示されるauto_keyについて紹介しました。MySQL 8.0以降はHASH JOINが導入され、auto_keyによる最適化を意識しなくても良いようになっています。MySQL 5.7とそれ以前では、auto_keyの最適化がされていないとブロックネステッドループジョイン（BNJ）となり、クエリが非常に遅くなるケースがあります。MySQL 5.7とそれ以前を運用されている方は、ここで紹介したチューニング方法を含め、覚えておいたほうがいいでしょう。

参考資料

• 8.8.3 拡張 EXPLAIN 出力形式
• 13.2.11.8 導出テーブル