tiger

2012.04.18

MySQLではIN句とサブクエリの組み合わせはインデックスが効かない！？

モニプラ for Facebookを担当しています高橋です。
よろしくお願いします。

前回に引き続きMySQLネタです。
以前パフォーマンスチューニングを行っていたときにわかった
MySQLのIN句とサブクエリにまつわる仕様がありましたのでそれを書きたいと思います。

説明に使うテーブル定義はこんな感じです。
person.id と person_diary.person_id がリレーションしています。
また、MySQLのバージョンは5.1.56です。

person

カラム 型 インデックス
id int(11) PRIMARY
name varchar(20)
gender varchar(1)
person_diary

カラム 型 インデックス
id int(11) PRIMARY
title varchar(20)
content text
person_id int(11) INDEX

事象

nameに”TAKA”が含まれるpersonに紐づくperson_diaryを次のSQLのようにIN句とサブクエリを使って取得するとします。

SELECT
*
FROM
person_diary pd
WHERE
pd.person_id IN (SELECT p.id FROM person p WHERE p.name LIKE ‘%TAKA%’);

ふつーに考えると期待としては
・person_diary.person_id にインデックスを張ってあるのでperson_diaryはインデックスが適用され
・personはLIKEの全文検索をしているのでインデックスが効かない
だろうと思われます。

がしかし

EXPLAINをかけてみると…

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY pd ALL NULL NULL NULL NULL 1101 Using where
2 DEPENDENT SUBQUERY p unique_subquery PRIMARY PRIMARY 4 func 1 Using where

な、なんと
person_diaryはインデックスが適用されずにフルスキャンされ（１行目のkeyがNULL）
逆にpersonはid列に設定してあるプライマリキーが適用される(2行目のkeyがPRIMARY)
という二つの謎な現象が発生しました。

そもそもpersonはnameカラムに対してLIKE検索しているのに、id列のプライマリキーが効いちゃうのは全く納得いきません。なぜ、どうしてこんなことが起こるのでしょう？

原因

私がMySQLに期待していた動きとしては
①サブクエリを実行してperson.idのリストをメモリ中に作成
②person.person_idに張られているインデックスを使って検索
というところでした。

期待通りに動いてくれなかったのには二つのMySQLの特性が関係していました。

特性① サブクエリを含むSQLは外側から先に実行される

MySQLの場合、サブクエリを含むSQLは外側から内側に向かって順に実行されるらしいです。
この例の場合は
1.person_diary
2.person
の順で実行されます。

1の段階ではperson.idのリストができていないのでそれ以外の条件で
検索するしかありません。
SQLで表現するとこんな感じです。

SELECT
*
FROM
person_diary pd

当然、適用できるインデックスはなくフルスキャンされることとなります。
これで一つ目の謎は解けました。

特性② IN句とサブクエリの組み合わせは内部的にEXISTSに変換しちゃう

なぜかはわかりませんが、IN句とサブクエリの組み合わせは処理できないらしく
内部的にはEXISTSに変換しちゃうらしいです。
SQLだとこんな感じになります。

SELECT
*
FROM
person_diary pd
WHERE
EXISTS (SELECT * FROM person p WHERE p.name LIKE ‘%TAKA%’
AND p.id = pd.person_id);

おーーー
SQLを書き換えたことでperson.id列が条件に加わりました。
id列に設定してあるプライマリキーがインデックスとして適用されるのも納得いきます。
これで二つ目の謎も解けました。

回避策

謎は解けましたがフルスキャンしてしまう問題は解消していません。
これを回避するためにはどうすればよいのでしょうか？
ズバリ
・IN句の中をサブクエリではなく事前にプログラムで取得しておいてカンマ区切りで渡す
・JOINしちゃう
のどちらかです。

サブクエリはだめで、JOINがOKな理由はMySQLにはクエリオプティマイザが
備わっているためです。
http://dev.mysql.com/doc/refman/5.1/ja/controlling-optimizer.html

サブクエリを使うと実行順が外側からと固定になってしまいますが、JOINの場合は
MySQLが最適な実行順を勝手に計算して実行してくれます。

試しに先ほどのSQLをJOINに書き換えて実行してみます。

SELECT
pd.*
FROM
person_diary pd
INNER JOIN person p ON pd.person_id = p.id
WHERE
p.name LIKE ‘%TAKA%’

EXPLAINをかけてみると期待通り、初めにpersonを検索し、次にperson_diaryを
インデックスを使って検索してくれていることがわかります。

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p ALL PRIMARY NULL NULL NULL 100 Using where
1 SIMPLE pd ref idx_1 idx_1 5 temp.p.id 4 Using where

最後に

結論としてはMySQLのサブクエリはクエリオプティマイザの対象外なのでイケてません。
とはいえMySQLでサブクエリが使えるようになったのは5.0からなので、これからに期待ですね。

アライドアーキテクツではJOINしてくれるエンジニアを随時募集しております。
興味が少しでもあればぜひ。採用サイトはこちらです。

追記
MySQL5.6ではこの問題は修正されそうです。
こちらの記事もぜひ。
MySQL5.6の気になる新機能をチェック！