また、SQLの最適化の実行前と実行後で、SQL文の解析結果を比較することが重要です。適当にSQLをいじって速くなったからそれでよし、という非科学的な態度じゃダメですよ。SQLの解析方法については、別の文章で説明します(ただしオラクルのみ。僕がオラクルしか使ったことがないので)。
なお、SQLの応答速度が遅い場合、その原因はSQL単体にあるとは限りません。索引の使用が不適切だったり、メモリの使用効率が悪かったり、ファイルI/Oが多くなっていたり、という他の原因も考えられます。
- サブクエリーを引数に取る場合、IN述語よりもEXISTS述語を使え
- 繰り返す。サブクエリーを引数に取る場合、IN述語を使うな
- IN述語の引数リストには、最もありそうなキーを左寄せしろ
- EXISTS述語のサブクエリー内では、SELECT * を使え
- 3つ以上のテーブルを結合させる時は冗長な結合条件を書け
- 行数を数えるときはCOUNT(*)よりもCOUNT(カラム名)を使え
- WHERE句の抽出条件は、最も制限の強いものから並べろ
- UNIONは使うな。UNION ALLを使え
- 実はインデックスが使用されていないという罠
- で、結局のところROWIDによるアクセスが最速なわけだが
- エディタにはEmacsを使え
- サブクエリーを引数に取る場合、IN述語よりもEXISTS述語を使え
IN[NOT IN]とEXISTS[NOT EXISTS]は、たいていの場合、全く等しい結果集合を返します。しかし、この両者でサブクエリーを作る場合は、EXISTSの方が圧倒的に速い。例えば、有名人の誰かと同じ誕生日に生まれた社員を全て探すためのSQLを考えます。以下の2つのSQLは、同じ結果を返しますが、2番目の方が速いです。
使用テーブル：
Personnel(社員テーブル)
Celebrities(有名人テーブル)
１．INを使った場合(遅い)
SELECT name
FROM Personnel
WHERE birthday IN (SELECT birthday
FROM Celebrities);
２．EXISTSを使った場合(速い)
SELECT P.name
FROM Personnel AS P
WHERE EXISTS (SELECT
FROM Clelebrities AS C
WHERE P.birthday = C.birthday);
なぜ、EXISTSの方が速いか？その理由は以下の2点です。
もし結合キー(この場合はbirthday)にインデックスが張られていれば、Celebritiesテーブルの実表は見に行かず、インデックスのみを参照する。
もしCelebritiesテーブルがインデックスを持っていなくても、優れたオプティマイザならば、birthday列をソートした一時テーブルを作り、2分探索することで、全表走査よりも効率的に検索を行なう。
- 繰り返す。サブクエリーを引数に取る場合、IN述語を使うな
EXISTSで代用でき、しかもたいてい、その方が圧倒的に速いからです。
INの引数にサブクエリーを取る場合、DBは、まずサブクエリーから実行し、その結果を一時的な作業テーブル上に格納します。つまりインライン・ビューを作るのです。その後に、その作業テーブルを全件走査します。これは、多くの場合、非常にコストがかかります。EXISTSを使えば、既に見たように、一時テーブルなど作成されません。
ただし、ソースコードの可読性という点において、IN述語はEXISTS述語に勝ります。要するに、IN述語で書いた方がぱっと見て意味が分かりやすいコードになります。従って、IN述語を使っても十分短い応答時間が確保されているなら、そのSQL文を敢えてEXISTS述語で書き直す必要はありません。
- IN述語の引数リストには、最もありそうなキーを左寄せしろ
なぜなら、INは、左から右へ引数を評価し、見つかった時点でtrueを返しそれより右の引数は見ないからです。以下の2つのSQLを比較してください。
１．遅い(かもしれない)
SELECT *
FROM Address
WHERE prefecture IN ('鳥取', '徳島', '東京', '大阪');
２．速い(かもしれない)
SELECT *
FROM Address
WHERE prefecture IN ('東京', '大阪', '鳥取', '徳島');
- EXISTS述語のサブクエリー内では、SELECT * を使え
サブクエリーのSELECT句を書くには、以下の3つの選択肢があります。
①EXISTS (SELECT * FROM …)
②EXISTS (SELECT カラム名 FROM …)
③EXISTS (SELECT 定数 FROM …)
このうち、最も良いのは①です。この書き方は、オプティマイザにどのカラムを使うべきかの選択を委ねることになります。そして、カラムにインデックスが張られていれば、実表を走査する必要はありません。
ただし、例外的に②や③の方が①よりも高速な場合もあります。古いSQL製品の場合は②のように列名を指定した方が速いこともあります。また、Oracleその他の製品では、③のように「SELECT 1 FROM …」など、定数を指定すると高速になります。この書き方は、行へのポインタさえ得られれば、実際の行を読む必要がないことを、DBMSに指摘するからです。
しかし、②は、もはや使う機会はないでしょうし、③の書き方は意味的な混乱を招くので、①を採用するべきです。
- 3つ以上のテーブルを結合させる時は冗長な結合条件を書け
Table1：小さい
Table2：大きい
Table3：大きい
上記のようなサイズの３つのテーブルがあるとします。共通のカラムで3つのテーブルを結合するとき、次のように書けます。
SELECT *
FROM Table1, Table2, Table3
WHERE Table1.common = Table2.common
AND Table1.common = Table3.common;
あるいは、次のようにも書けます。しかし、こちらの方が遅くて非効率です。
SELECT *
FROM Table1, Table2, Table3
WHERE Table2.common = Table3.common
AND Table1.common = Table3.common;
なぜなら、2番目のSQLは、最初にTable2とTable3という大きなテーブルを結合した大きな結果集合と、Table1とTable3を結合した小さな結果集合でマッチングを行なうからです。だから、結合条件は、小さなテーブルを最初に書くべきです。
最も良い書き方は、テーブルのサイズが変わっても大丈夫なように、オプティマイザ自身にテーブルのサイズを決定させる書き方です。そのためには、次のように結合条件に冗長性を持たせます。
SELECT *
FROM Table1, Table2, Table3
WHERE Table1.common = Table2.common
AND Table2.common = Table3.common;
AND Table3.common = Table1.common;
もちろん、テーブルのサイズにあまり変化がない場合は、あえて結合条件に冗長性を持たせる必要はありません。
- 行数を数えるときはCOUNT(*)よりもCOUNT(カラム名)を使え
このトリックは、インデックスを使います。したがって、これがうまく働くためには、COUNT関数の引数となるカラムにインデックスが張られている必要があります。例えば、
SELECT COUNT(*)
FROM Sales;
よりは、次のSQLの方が速いかもしれない。
SELECT COUNT(sale_id)
FROM Sales;
ここで、sale_idがSalesテーブルの主キーだとすれば、当然、sale_idにはユニークなインデックスが存在します。それを利用するのが、このトリックです。
- WHERE句の抽出条件は、最も制限の強いものから並べろ
抽出条件がANDでつながっている場合は、制限の強いもの、つまりそれによって選択される行数が少ないものから順に並べる方が、効率がよいです。例えば、以下の通り。
１．遅い
SELECT *
FROM Student
WHERE sex = 'male'
AND grade = 'A';
２．速い
SELECT *
FROM Student
WHERE grade = 'A'
AND sex = 'male';
なぜなら、評価がAの生徒の方が、男子学生よりも少ないから。この学校がハーレムのごとき場所なら話は別ですが。
- UNIONは使うな。UNION ALLを使え。
UNIONは、二つの結果集合をマージします。しかし、重複行を排除するためのソート処理にコストがかかります。もし重複を気にしなくてよい場合なら、UNIONの代わりにUNION ALLを使ってください。そうすればソートは発生しません。
- 実はインデックスが使用されていないという罠。
皆さんが検索するテーブルには、きっとインデックスが張られているでしょう。インデックスは、行数や列数の多い表を短時間で検索するためのテクニックとしては非常にポピュラーです。その原理は、C言語のポインタ配列と同じです。サイズの大きなオブジェクト配列を検索するよりも、サイズの小さなポインタ配列を検索した方が効率がいい、というわけです。しかも、2分探索による高速検索が可能なよう工夫されています。
さて、Aという列にインデックスが張られているとします。以下のSQLはそのインデックスを使うつもりで、実のところテーブルXXを全件検索してしまっています。
①検索条件の左側で式を用いている
SELECT A
FROM XX
WHERE A * 1.1 > 100
検索条件の右側で式を用いれば、インデックスが使用されます。従って、代わりに
WHERE A > 100/1.1
という条件を使えばいいわけです。あるいは、関数索引を使うという方法もありますが、トリッキーなので推奨しません。
②NULLを指定している
SELECT A
FROM XX
WHERE A IS NULL
なぜなら、インデックスの中にはNULLは存在しないから。NULLは値ではないのです。
③否定形を用いている
SELECT A
FROM XX
WHERE A <> 100
否定形(NOT EQUAL, NOT IN)はインデックスを使用できません。
④ORを用いている
SELECT A
FROM XX
WHERE A > 100 OR B = 'abc'
理由は知らない。とにかくORを使うな。どうしても使いたいならビットマップ索引を張りましょう。
⑤LIKE述語を用いている
SELECT A
FROM XX
WHERE A LIKE '%a'
理由は知らない。とにかくLIKEを使うな。
- で、結局のところROWIDによるアクセスが最速なわけだが。
もしあなたがROWIDの存在を知らなければ、今すぐに
SELECT rowid
FROM 適当なテーブル名;
というSQLを実行してください。
ROWID
------------------
AAAF+OAAIAAABmMABI
AAAF+OAAIAAABmMABK
AAAF+OAAIAAABmMABL
などといった列が選択されるはずです。 ROWIDはどのテーブルでも必ず持っている擬似列であり、そこに格納されている値はレコードの物理アドレスです。つまりROWIDはポインタの役割を果たす。インデックスもROWIDを使用しています。 ROWIDは、セッションが終了すると変化するかもしれませんが、ユーザセッション中は不変であり、Oracleでは常に最速のアクセスが保証されます。
- エディタにはEmacsを使え
SQLに限った話ではないのですが、プログラムソースをコーディングするための最も効率のよいエディタは、現時点(2002年)では間違いなくEmacsです。反論は許されません。もともとUNIX上のエディタとして有名でしたが、最近ではWindows上でも動作します。
Emacsを使ってSQLを効率よくコーディングするためには、以下の2つの機能を効果的に使う必要があります。
１．sql-mode
２．abbreviation（入力補完）
sql-modeは、その名の通り、SQLをコーディングするための機能(Emacsの用語ではメジャーモード)です。予約語のハイライト(ANSI、Oracle、Postgresに対応)、や自動インデント、さらにSql*Plusのインターフェースとして使うことができます。とりわけ、このインターフェース機能は重要です。なぜなら、Sql*PlusがWindowsのエディタに劣らず貧弱だからです。コマンドの履歴はもてない、行編集をすることもできない、入力補完機能もない。editコマンドを使えばOSのエディタを呼び出せるものの、そこで呼び出されたエディタがメモ帳では焼け石に水と言うほかありません。またオラクル以外にもPostgres、MySQL、DB2、SQLServerなどのDBMSに対応しています。
0 件のコメント:
コメントを投稿