忘れてしまったほうがいいSQLチューニングテクニックについて

10年ぐらい前はJavaで文字列を連結するときは String ではなく StringBuffer を使ったほうが速いなんて言われてたんだけど、それがテクニックとして有効だったのは「StringBufferを使えばこれこれの内部動作になる」という関係が固定されていたからで、仮にJVMのリビジョンによってどっちが速いかがコロコロ変わったり、文字列の長さが一定以上になると突然あっちの方が速くなったり、みたいな状態だったら「文字列の連結はStringBufferの方が...」はテクニックとして成り立たなくなってしまう。
が、SQLについての言説の一部はまさにこの状態で、本来成り立たないことがチューニングテクニックとして語られていると思う。
そういうのを暗記するのはやめて実行計画を見ましょう、という話。

「INとEXISTSはどっちが速いか」の不毛

昔から「INを等価なEXISTSに書き換えると速くなる」なんて言われていて、「これは特定のRDBMSに限定されない、汎用的なテクニックだ」という嘘までセットになってたりするんだけど、実際はいろいろ条件が揃わないとEXISTSはINに勝てない。
1000万件のテーブルをメインクエリに、1000件のテーブルをサブクエリに置いて、INとEXISTSのどっちが速いか確認してみよう。

-- (1)IN
explain analyze
select count(*) 
from accounts a
where bid in
(select tid from tellers);

-- (2)EXISTS
explain analyze
select count(*) 
from accounts a
where exists
(select * from tellers where tid=a.bid);

上記2本のSQLを同じ実行計画で処理するRDBMSもあるんだけどそれでは同じ速さになってしまうので、(1)(2)で異なる実行計画を吐いてくれるPostgreSQL8.3を使って実験してみると、手元の環境ではINの方が1.5倍速い。

EXISTSを勝たせるにはどうするかというと、メインクエリのWHERE句に絞り込みの条件を付けて、サブクエリにぶつける件数を少なくすればEXISTSの方が速くなる。
手元の環境では、1000万件を1000件まで絞るとようやく逆転してくれた。

-- EXISTSの方が7%速い

explain analyze
select count(*) 
from accounts a
where bid in
(select tid from tellers)
and aid between 1 and 1000;

explain analyze
select count(*) 
from accounts a
where exists
(select * from tellers where tid=a.bid)
and aid between 1 and 1000;

ところがここで結合カラムを変更し、サブクエリ側テーブルにインデックスのない列(=bid)で結合すると、再びINの方が速くなる。

-- INの方が15%速い

explain analyze
select count(*) 
from accounts a
where bid in
(select bid from tellers)
and aid between 1 and 1000;

explain analyze
select count(*) 
from accounts a
where exists
(select * from tellers where bid=a.bid)
and aid between 1 and 1000;

以上をまとめると「メインクエリ側のWHERE句で件数を十分に絞り込み、サブクエリ側にインデックスのある列で結合する場合は、EXISTSはINより速い」ということになるがこんなこと覚えても役に立たない。これは当該サーバ・当該DBに固有の結果であって環境が変われば結果も変わる。例えば「tellersが500万件もある」とか「ストレージがSSDだ」という環境ではどのクエリでもEXISTSが圧勝するんじゃないだろうか。
要するに「INとEXISTSはどっちが速いか」という問題の立て方がよくないわけです。

実行計画を見てみる

EXISTSが勝ったり負けたりした理由は実行計画を見れば明白で、この環境では IN は Hash Join(=両テーブル1回ずつのシーケンシャルスキャン)で実行されるのに対して、EXISTS は Nested Loop Join 風の動きでtellersにランダムアクセスしていた。
3つのテスト結果はSQLの水準で考えると何だか不安定に見えるが、より低レベルに降りてみれば

ランダムアクセスを1000万回やったら1回のシーケンシャルスキャンに負けた

ランダムアクセスを1000回に減らせば1回のシーケンシャルスキャンに勝った

インデックスがないと1000回のランダムアクセスが1000回のシーケンシャルスキャンに変わるので、1回のシーケンシャルスキャンに負けた

という、ランダムアクセスとシーケンシャルスキャンの特性から推測できる話に過ぎない。この特性はあらゆる環境に共通するので、上で太字にしたTipsと違って覚えておけばどこでも使える。

つまりSQLではなく実行計画のレベルでチューニングを考えると、覚えなくてはならないことが少数の汎用的な知識だけになってありがたいわけです。
逆にSQLのレベルでチューニングテクニックを暗記すると、覚えることが増える上に効果が出ない原因が理解できずRDBが嫌になってきます。