【SQLチューニング】クエリを書くコツ

SQLアンチパターン

インプリシットカラム

クエリを簡単に書くためにワイルドカードを使うことがあるだろう。( SELECT * FROM ...的な)
しかし、全カラムを利用するようなSELECT文でなければ、カラムの計算量を落とすために明示的に選択カラムを記載すべき。
（バグ検知もしやすくなる。）

スパゲッティクエリ

効率的なクエリを目指した結果、1つのクエリで複数のタスクを行おうとすることで、意図しない結果になるだけでなく、クエリの修正/デバッグ/テストが難しくなること。

スパゲッティクエリの回避策【分割統治をおこなう】

#1 UNIONを利用する
#2 CASE式とSUM関数を組み合わせる
#3 (SQLを用いてSQLを自動的に記述する)

SQLチューニング

1. サブクエリを使う際は、inではなくexistsを利用

tuning_case1.sql

# チューニング前
SELECT id FROM idtable WHERE user_type IN (SELECT user_type FROM subscribetable)
# チューニング後
SELECT id FROM idtable WHERE EXISTS (SELECT user_type FROM subscribetable WHERE id_table.user_type = subsucribetale.user_type)

INの引数にサブクエリを与える場合、DB はまずサブクエリから実行し、その結果を一時的なワーク・テーブルに格納し、その後、ビューを全件走査する。
EXISTS はuser_type列をソートした一時テーブルを作り、2分探索することで、全表走査よりも効率的に検索を行なう。一行でも条件に合致する行を見つけたらそこで検索を打ち切るので、IN のように全表検索の必要がないため計算コストが低くなる。

2. exists句のサブクエリ内では、SELECT *を利用する

tuning_case2.sql

# チューニング前
SELECT id FROM idtable WHERE EXISTS (SELECT user_type FROM subscribetable WHERE id_table.user_type = subsucribetale.user_type)
# チューニング後
SELECT id FROM idtable WHERE EXISTS (SELECT * FROM subscribetable WHERE id_table.user_type = subsucribetale.user_type)

EXISTS句は、行へのポインタさえ得られれば実際の行を読む必要がないため、SLECTする列を指定するよりも*を指定して、オプティマイザに使用する列をゆだねることで速度向上の可能性が有る。

3. exists句は inner join で代用可能

tuning_case3.sql

# チューニング前
SELECT id FROM idtable WHERE EXISTS (SELECT * FROM subscribetable WHERE id_table.user_type = subsucribetale.user_type)
# チューニング後
SELECT id FROM idtable INNER JOIN (SELECT DISTINCT user_type FROM subscribetable ) subsctable ON idtable.user_type = subsctable.user_type 

EXISTS句は、メインクエリを実行後サブクエリを実行するが、INNER JOINを利用するとメインクエリの中で絞り込みを実施することができるため、速度向上の可能性がある。
（但し、DISTINCTの計算コストが高くなることで、性能劣化の可能性もある）

4. not exists句は left join で代用可能

tuning_case4.sql

# チューニング前
SELECT id FROM idtable WHERE NOT EXISTS (SELECT * FROM subscribetable WHERE id_table.user_type = subsucribetale.user_type)
# チューニング後
SELECT id FROM idtable LEFT JOIN (SELECT DISTINCT user_type FROM subscribetable ) subsctable ON idtable.user_type = subsctable.user_type WHERE subsctable.user_type is null

tuning_case3と同様。left outer joinを使うことで、サブクエリが巨大な場合、速度改善の可能性が有る。

5. 暗黙の型変換の回避

tuning_case4.sql

# チューニング前
SELECT col_1 FROM SomeTable WHERE col_1 = 10
# チューニング後
SELECT col_1 FROM SomeTable WHERE col_1 = '10'

データ型と代入値が一致しない場合、型を変換して代入が可能であれば暗黙の型変換が行なわれます。「気の利いた」機能ですが、暗黙の型変換は一度代入に失敗した後に行なわれるため、オーバーヘッドが発生する。また、インデックスも利用されない。

DB2のアクセスプランの読み方

       1000     → オペレータが返した行数
      TBSCAN    → オペレーション
      (   3)    → オペレーション番号
     81.9241    → コスト(単位はtimeron)
        12      → I/Oコスト
        |      
       1000     → オブジェクトのサイズ(行数)
   TABLE: USER  → オブジェクトの種類とスキーマ名
     EMPLOYEE   → オブジェクト名
        Q2              

使ってみたいメモ

GROUP_CONTACT関数

参考文献

SQLを速くするぞ
EXISTSとSQLの高速化について
18章 インプリシットカラム（暗黙の列）
sqlアンチパターン - BillKarwin 著 和田卓人、和田省二 監訳 児島修 訳
即戦力のDB2管理術 - 下佐粉昭 著