クエリチューニングをする際には、最初に実行計画を確認します。クエリチューニングをよく行う方は、少し複雑なクエリの実行計画を見たときに、key列に<auto_
という表示を見つけたことがあると思います。今回はこの実行計画に表示される<auto_
auto_keyとは
auto_
auto_
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`)
実行計画から以下のことが確認できます。
- (3行目)
Extra列の Using temporary
とselect_type列の DERIVED
から、はじめにt2テーブルのGROUP BYしたクエリの結果が内部一時テーブルとして作成されています。 - (1行目)
table列から駆動表としてt1テーブルが選択されていることがわかります。 - (2行目)
table列の <derived2>
は(3行目) の内部一時テーブルを意味します。 (3行目) のidが2なので、 <derived2>
となります。そして、<auto_
の表示が確認できるので、その内部一時テーブルに対して自動的にインデックスが生成されました。ref列の値key0> db0.
は結合元のカラムなので、そのカラムと結合しているc1カラムに対してインデックスが作成されたことがわかります。t1. c2
このように内部一時テーブルに対してインデックスを作成してから結合することにより、クエリの速度が向上します。この最適化がないと、
auto_keyによる最適化を狙ったクエリチューニング
MySQL 8.
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_
このケースでは以下のように書き換えることで、auto_
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.
c1カラムの格納する最大文字数を表す長さが512であり、実データにかかわらず512×4バイトで2048バイトを使用する可能性があるため、このクエリはauto_
まとめ
今回は実行計画に表示されるauto_