なつかしの not exists なクエリ

ひさしぶりに MySQL のクエリチューニングの話。すごい昔に「explain したら not exists って出てきたけどこれ何よ?」というエントリを書いたんですが、あいかわらずなことやってんな、と思った日常の一風景です。

ちなみに今夜の MySQL は

Server version: 5.0.67-modified-log MySQL Community Server (GPL) (portions (c) Tritonn Project)

です。

お題

t_key
- id int
- table_name char
- key_name char
- primary key (id), unique key (table_name, key_name)
t_val
- key_id int
- date date
- value int unsigned
- primary key (key_id, date), key (value)

みたいなテーブルがあるとするじゃないですか。 t_val は日付ごとに key_id の値が入っていると。また、t_key.id は t_val.key_id にない id もたくさん (1億とかのオーダで) 含むとします。

このうち、 value = 0 しか入っていない key_name のリストを取得するにはどうすればいいでしょう?

ナイーブなアプローチ

value >= 0 であることを知っているので、"mysql> " のプロンプトに続けてパーッと書いたのはこんなの。

SELECT
     t_key.key_name,
     t_val.key_id,
     COUNT(*)
FROM t_key, t_val
WHERE t_key.id = t_val.key_id
    AND t_key.table_name = 't_val'
GROUP BY key_id HAVING MAX(value) = 0

しかしぜんぜん終わんないのね。30分くらい待って止めちゃった。

方針

やりたかったのは

t_val から MAX(value) = 0 な t_val.key_id のリストを取得
t_val.key_id に対応する t_key.key_name を ref で拾ってくる

なかんじかな?

しかし、 t_val.key_id に存在しない t_key.id (table_name <> 't_val' な行) が圧倒的に多いので t_key.table_name の using where が効いてしまい、 explain すると案の定 using where; using temporary; using filesort になるわけです (まあ仕方ないと思ってたけど...) 。方針通りにやるなら table_name の制約外すんですが、examined rows がひどいことに。

ちなみに COUNT(*) はついでで、あんまり重要ではないです。

もうちょい賢く

ついでにわかるといいものは、あとでリストができてから別に調べればいいので、とりあえず最低限必要なものだけを拾うようにします。なので SELECT DISTINCT にしましょう。

それから、 MAX(value) = 0 というのは「unsigned int ですべて 0」と等価ですが、MySQL がそれを理解してくれるはずもなかったのでした。すなおに「0 しか入っていない」を表現するクエリにします。

そこでどう考えるかとういと、 t_key.table_name で using where は仕方ないので、

t_key のうち table_name = 't_val' な key_id のリストを取得
t_val で 0 以外の値が出てきたらリストから削る

な方針にします。

この「リストから削る」というのが、 not exists なクエリになるわけですね。

改善

ということで、こんなかんじ。

SELECT DISTINCT
    t_key.key_name,
    t_key.id
FROM t_key
LEFT JOIN t_val
ON t_key.id = t_val.key_id
    AND t_val.value > 0
WHERE t_key.table_name = 't_val'
    AND t_val.key_id IS null

explain すると、t_key が "Using where; Using temporary" で、 t_val が "Using where; Not Exists; Distinct" となり、めでたく5分くらいで結果が帰ってきたのでした。

おまけ

さて、上に書いた改善したクエリだと、実はある例外パターンをうまく扱えていないんです。さらに言うならば、もとのお題の説明がちょっと不十分。何のことか、気づきました?

正解は、 t_key.table_name = 't_val' だか t_val にエントリが無い t_key.id もこのクエリで拾ってきてしまう、ということ。「 0 しかないリスト」を「全体から 0 以外を除外したリスト」に置き換えたんですが、そもそも 0 も 0 以外も出てこなかった ( join するレコードがなかった ) 場合は結果リストに残ってしまうんですね。ご注意くださいませ。

explain して「そんなに rows あるのかー」と思ってクエリ書きなおすのって、なんか悔しいですね＞＜