第97回　JOIN_ORDERを使ってJOINの順番を決める

MySQL 5.7とそれ以前では、テーブルの結合を実施する際に、INNER JOINの場合はSTRAIGHT_JOINを利用し結合することで、テーブルのなかで先に読み取りを行うテーブルを決めることができました。最新バージョンのMySQL 8.0からは、Join-Order オプティマイザヒント句を用いてJOINする順番を決めることができるようになりました。

今回は、従来のSTRAIGHT_JOINとJoin-Order オプティマイザヒント句の使い方を確認していきたいとおもいます。なお、使用しているMySQLは8.0.15,OSはCentOS7を利用しています。

STRAIGHT_JOIN

STRAIGHT_JOINは、JOINを行う際に対象のテーブルの駆動表を固定したいときに使われる構文です。STRAIGHT_JOINを用いてJOINを行うと、先に出てきたテーブルを駆動表として扱い、INNER JOINを行います。

たとえば、worldデータベースに対して下記のようなSQLで実行計画を取得してみると、オプティマイザは駆動表にCountryを選択しています。

mysql> EXPLAIN SELECT Country.Name as CountryName, City.Name AS City FROM City INNER JOIN Country ON City.CountryCode=Country.Code;
+----+-------------+---------+------------+------+---------------+-------------+---------+--------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref                | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | Country | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL               |  239 |   100.00 | NULL  |
|  1 | SIMPLE      | City    | NULL       | ref  | CountryCode   | CountryCode | 3       | world.Country.Code |   18 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+-------------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

これをSTRAIGHT_JOINを使って書き換えると、駆動表がCityに変更されます。

mysql> EXPLAIN SELECT STRAIGHT_JOIN Country.Name as CountryName, City.Name AS City FROM City INNER JOIN Country ON City.CountryCode=Country.Code;
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                    | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------+
|  1 | SIMPLE      | City    | NULL       | ALL    | CountryCode   | NULL    | NULL    | NULL                   | 4188 |   100.00 | NULL  |
|  1 | SIMPLE      | Country | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.City.CountryCode |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------+
2 rows in set, 1 warning (0.04 sec)

詳しい説明はここでは割愛しますが、この特性を利用して、ORDER BY狙いのINDEXを作成してクエリを高速化させるテクニックがあります。詳細についてはyoku0825さんのWhere狙いのキー、order by狙いのキーをご確認ください。

Join-Orderオプティマイザヒント句

MySQL8.0からは多くのヒント句が追加され、その中でJOINの順番を指定するヒント句が4つあります。

JOIN_FIXED_ORDER: 強制的に結合する順番をFROM句の指定の順番で行います。
JOIN_ORDER: 指定した順序で表を結合するようにオプティマイザに教えます。
JOIN_PREFIX: 指定したテーブルを先に結合するようにオプティマイザに教えます。
JOIN_SUFFIX: 指定したテーブルを後に結合するようにオプティマイザに教えます。

Join-Order オプティマイザヒント句を利用する場合は、結合するSELECTの後に挿入します。たとえば、t1, t2のテーブルに対してJOIN_PREFIXを利用する場合は下記のようになります。

SELECT /*+ JOIN_PREFIX(t1) */ t2.id FROM t2 LEFT JOIN t1 on t1.id = t2.id;

もし、テーブルにエイリアスを指定する場合は、同様にヒント句にもそのエイリアス指定すれば利用できます。Join-Orderオプティマイザヒント句を指定しているにもかかわらず、ヒント句で元のテーブル名を記述したり、間違ったエイリアスを指定した場合はワーニングが表示されます。


〈 エイリアスを使用した例 〉

mysql> SELECT /*+ JOIN_PREFIX(b2) */ b2.id FROM t1  INNER JOIN t2 as b2  on t1.id = b2.id;
+----+
| id |
+----+
|  1 |
|  2 |

〈 省略 〉

〈 ワーニングの例 〉

mysql> EXPLAIN SELECT /*+ JOIN_PREFIX(t2) */ b2.id FROM t1  INNER JOIN t2 as b2  on t1.id = b2.id;
+----+-------------+-------+------------+--------+----------------------+---------+---------+----------+-------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys        | key     | key_len | ref      | rows  | filtered | Extra       |
+----+-------------+-------+------------+--------+----------------------+---------+---------+----------+-------+----------+-------------+
|  1 | SIMPLE      | b2    | NULL       | index  | PRIMARY              | PRIMARY | 4       | NULL     | 12010 |   100.00 | Using index |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY,idx_pk_value | PRIMARY | 4       | d1.b2.id |     1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+----------------------+---------+---------+----------+-------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

Warning (Code 3128): Unresolved name `t2` for JOIN_PREFIX hint
Note (Code 1003): /* select#1 */ select `d1`.`b2`.`id` AS `id` from `d1`.`t1` join `d1`.`t2` `b2` where (`d1`.`t1`.`id` = `d1`.`b2`.`id`)

クエリブロックネームを利用して、JOINの順番を指定することも可能です。クエリブロックネームとは、クエリブロックに対して名前をつけることです。以下のようなサブクエリを例にします。

SELECT a1.id FROM (SELECT id FROM t1 ) as a1;

このサブクエリに対して、クエリブロックごとにqb1,qb2のようなクエリブロックネームを付与することができます。

> SELECT /*+ QB_NAME(qb1) */ a1.id FROM (SELECT /*+ QB_NAME(qb2) */id FROM t1 ) as a1;

クエリブロックネームは、クエリ単位に名前をつけて、その名前をもとにヒント句で利用できるようにするものです。このクエリブロックネームを利用している場合は、JOIN_ORDER(t1@qb1)のようにテーブルネーム＠クエリブロックネームを用いることでオプティマイザヒント句を利用できます。

クエリブロックネームの詳細な説明については、公式ドキュメントのOptimizer Hints for Naming Query Blocksをご確認ください。

STRAIGHT_JOINとの違い

STRAIGHT_JOINは指定した順番を強制させるものでしたが、JOIN_ORDER、JOIN_PREFIX、JOIN_SUFFIXは順番を強制しないので、オプティマイザがコストが高いと判断した場合は選択されない可能性があります。

まとめ

今回は、STRAIGHT_JOINとJoin-Orderオプティマイザヒント句による結合する順番の操作について見ていきました。Join-Orderによるオプティマイザヒント句の機能追加によって、より細かく結合する順番をオプティマイザに指示することが可能となりました。もし、結合する順番によってクエリの最適化ができそうであれば、利用してみても良いかもしれません。