MySQL のJOIN に関するメモ

内容

• FROM 句のテーブルの順番と、MySQL がテーブルをJOIN する順番は別
• STRAIGHT_JOIN と eq_ref, ref
• eq_ref になるようにするために
• JOIN 条件の書き方
• STRAIGHT_JOIN をいつ使うか

今回の検証に用いたMySQL は4.0.26。また、例として、以下のテーブルを用いる。(テーブルは、「逆算式SQL教科書」のもの)

[study]> SHOW FIELDS FROM uriage;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| denpyo_no   | int(11) | NO   | PRI | NULL    | auto_increment | 
| uriage_date | date    | NO   |     | NULL    |                | 
| shohin_id   | int(11) | YES  | MUL | NULL    |                | 
| suryo       | int(11) | YES  |     | NULL    |                | 
| shain_id    | int(11) | YES  |     | NULL    |                | 
| kokyaku_id  | int(11) | YES  |     | NULL    |                | 
+-------------+---------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

[study]> SHOW FIELDS FROM shohin;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| shohin_id    | int(11)      | NO   | PRI | NULL    | auto_increment | 
| shohin_name  | varchar(100) | YES  | UNI | NULL    |                | 
| category     | varchar(50)  | YES  |     | NULL    |                | 
| shiire_tanka | int(11)      | YES  |     | NULL    |                | 
| hanbai_tanka | int(11)      | YES  |     | NULL    |                | 
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

FROM 句のテーブルの順番と、MySQL がテーブルをJOIN する順番は別

• どのようにJOIN されているかはEXPLAIN で調べられる
• 次の例を見ると、FROM 句の順序に関係なく、uriage テーブルが先にスキャンされ、その結果に対してshohin テーブルの該当行がフェッチされているのが分かる

[study]> EXPLAIN SELECT * FROM uriage, shohin where uriage.shohin_id = shohin.shohin_id;
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-------+
| id | select_type | table  | type   | possible_keys | key     | key_len | ref                    | rows | Extra |
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-------+
|  1 | SIMPLE      | uriage | ALL    | shohin_id     | NULL    | NULL    | NULL                   |   77 |       | 
|  1 | SIMPLE      | shohin | eq_ref | PRIMARY       | PRIMARY | 4       | study.uriage.shohin_id |    1 |       | 
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-------+
2 rows in set (0.00 sec)

[study]> EXPLAIN SELECT * FROM shohin, uriage where uriage.shohin_id = shohin.shohin_id;
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-------+
| id | select_type | table  | type   | possible_keys | key     | key_len | ref                    | rows | Extra |
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-------+
|  1 | SIMPLE      | uriage | ALL    | shohin_id     | NULL    | NULL    | NULL                   |   77 |       | 
|  1 | SIMPLE      | shohin | eq_ref | PRIMARY       | PRIMARY | 4       | study.uriage.shohin_id |    1 |       | 
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-------+
2 rows in set (0.00 sec)

STRAIGHT_JOIN と eq_ref, ref

• JOIN ではどのテーブルからスキャンされるかは非常に重要
• オプティマイザはどの順序でJOIN するかを総当りで調査する
  • これはオプティマイザにとって、どのインデックスを使用するかの判断以上に難しい
• JOIN では、type がeq_ref になるようにする。これは、JOIN においてユニークキーまたはプライマリーキーが用いられていることを示す
  • 例えば、以下の例ではtype がeq_ref になっている。これは、uriage テーブルの各レコードのshohin_id に一致するshohin.shohin_id(これがプライマリーキー)をshohin からフェッチするため

[study]> EXPLAIN SELECT * FROM uriage INNER JOIN shohin ON uriage.shohin_id = shohin.shohin_id;
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-------+
| id | select_type | table  | type   | possible_keys | key     | key_len | ref                    | rows | Extra |
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-------+
|  1 | SIMPLE      | uriage | ALL    | shohin_id     | NULL    | NULL    | NULL                   |   77 |       | 
|  1 | SIMPLE      | shohin | eq_ref | PRIMARY       | PRIMARY | 4       | study.uriage.shohin_id |    1 |       | 
+----+-------------+--------+--------+---------------+---------+---------+------------------------+------+-------+
2 rows in set (0.00 sec)

• 意図しない順番でJOIN されてしまう場合、STRAIGHT_JOIN を使い、JOIN の順序を強制することができる
  • 試しに、先のJOIN を逆順にしてみる

[study]> EXPLAIN SELECT * FROM shohin STRAIGHT_JOIN uriage ON uriage.shohin_id = shohin.shohin_id;
+----+-------------+--------+------+---------------+-----------+---------+------------------------+------+-------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref                    | rows | Extra       |
+----+-------------+--------+------+---------------+-----------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | shohin | ALL  | PRIMARY       | NULL      | NULL    | NULL                   |   13 |             | 
|  1 | SIMPLE      | uriage | ref  | shohin_id     | shohin_id | 5       | study.shohin.shohin_id |    8 | Using where | 
+----+-------------+--------+------+---------------+-----------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)

• • ここではtype がref になっている。これは、shohin テーブルの各レコードのshohin_id に一致するuriage.shohin_id(これはプライマリーキーではないため、条件一致するレコードが複数ある)をuriage からフェッチするため
  • uriage.shohin_id はユニークではないため、この探索はユニークキーの探索に比べて遅く、このJOIN の順番はよくないと判断できる
  • このように、STRAIGHT_JOIN を誤って使うと、クエリはかえって遅くなる
• 複数のJOIN を強制する場合は、「FROM A STRAIGHT_JOIN B STRAIGHT_JOIN C」と、都度STRAIGHT_JOIN を書く

eq_ref になるようにするために

• 左側のテーブルに、これからJOIN するテーブルの結合条件に用いられるユニークキーに対応するカラムがあればいい
  • 「左側の」とは、直前にJOIN したテーブルではなく、これまでにJOIN したテーブル内にということ
  • A とB, BとC, AとD にリレーションがある場合、「A B C D」順でJOIN した時、A.id = D.a_id なら、eq_ref でJOIN できる。決して、C のテーブルのカラムにa_id がないとダメということではない
  • ここ、今までは直前にJOIN したテーブル内に対応カラムがないとダメだと思ってた

JOIN 条件の書き方(SQL の可読性の話)

• 以下のように、「右→左」が矛盾しないように書く

SELECT * FROM uriage INNER JOIN shohin ON uriage.shohin_id = shohin.shohin_id;

• 以下はだめ、uriage テーブルが先にスキャンされ左のテーブルになるので、shohin.shohin_id が左にくるのはおかしい
  • shohin.shohin_id はまだ左のテーブル内にない
  • 個々のuriage.shohin_id に対応するshohin.shohin_id をshohin に対してフェッチするので「uriage.shohin_id → shohin.shohin_id」のようになる

SELECT * FROM uriage INNER JOIN shohin ON shohin.shohin_id = uriage.shohin_id;

STRAIGHT_JOIN をいつ使うか

• 基本的には、以下の2つのどちらかだと思う
  • 期待する順番でJOIN してくれない
  • 期待する順番でJOIN しているが、その判定に時間がかかりすぎる
    • STRAIGHT_JOIN ヒントがあれば、オプティマイザはJOIN の順番を計算する必要がない

• 地道にEXPLAIN してクエリを変更しみて、ということを行なうしかない。以下は経験則
  • 同じ結合でも、結合条件以外のwhere 句があるかないによってベストな結合順序が変わる場合がある
    • 検索に対応するクエリを生成する際に、検索条件が指定されたらSTRAIGHT_JOIN を、指定されなかったらINNER JOIN を使うみたいなことをやって上手くいったことがある
  • INNER JOIN だけからなる場合、STRAIGHT_JOIN を使わなくても大体いける
    • 400万レコードを持つテーブルを含み11個のテーブルをJOIN した際、STRAIGHT_JOIN を使用しなくても、全てのJOIN がeq_ref を使うようにJOIN され、1秒以内に結果が返ってきた
    • 一方、LEFT JOIN を含む場合、3つくらいのJOIN であってもSTRAIGHT_JOIN でクエリ性能が上ることが多い

参考

• 実践ハイパフォーマンスMySQL: ジェレミ・D. ザウドニ, デレク・J. ベリング, Jeremy D. Zawodny, Derek J. Balling, 林 秀幸: 本
• 漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!