内容
- 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 でクエリ性能が上ることが多い
- 同じ結合でも、結合条件以外のwhere 句があるかないによってベストな結合順序が変わる場合がある