【SQL】パフォーマンスの良いSQLを記述 ~ 全般編 ~

■ はじめに

パフォーマンスの良いSQLを記述する方法を記す。

目次

【1】全般
 1)クエリの書き方を統一する
 2)ワイルドカード「*」を使用しない
 3)表に別名を付ける
 4)DISTINCTの使用は、極力避ける
【2】インデックス
【3】相関サブクエリ / 自己相関サブクエリ
【4】ソート・グルーピング
 1)GROUP BY,ORDER BYの使用は、極力避ける
 2)GROUP BYは、NULL制約のない列を指定する
【5】ビュー

 【1】全般

 1)クエリの書き方を統一する

 * SQL文に定数を直接記述してしまうと、
   RDBMSは定数値だけが異なるSQL文を別のものと解釈するため、
   実行計画を再利用されない。

解決策

 * バインド変数を使用して,できる限りSQL文を統一する

 ※ 文字の大小や記述の仕方なども統一しておかないと別のSQL文だと認識されてしまうことに注意。
 (以下の参考文献でSQL コーディングを統一しておいた方がいい)

 2)ワイルドカード「*」を使用しない

 * 実行時に実際の列名への読替えが発生し、オーバヘッドが増加する
 * 必要のない列まで結果セットに含める必要があるため、無用のメモリまで使用してしまう

 3)表に別名を付ける

 * 解析時にどの列がどのテーブルに属するかの判定を省略できるため、
   明示的に表に別名を付けると、解析処理を減らすことができる

サンプル

X SELECT s.id, s.name FROM SampleTable WHERE id = '2';
O SELECT s.id, s.name FROM SampleTable AS s WHERE s.id = '2';

 4)DISTINCTの使用は、極力避ける

 * DISTINCTを指定すると、処理に非常に時間が掛かるため

解決策

 * DISTINCTの代りにEXISTSを使う

サンプル

X SELECT DISTINCT a.id, a.name
   FROM TABLE1 a, TABLE2 b
   WHERE a.id = b.id;
O SELECT a.id, a.name
   FROM TABLE1 a, TABLE2 b
   WHERE EXISTS (SELECT 'X' FROM TABLE2 b WHERE a.ID1 = b.ID2)

 参考文献
https://qiita.com/ichi_zamurai/items/fdbe3872a505c22ee431
http://msdn.microsoft.com/ja-jp/library/cc707343.aspx

 【2】インデックス

* インデックスを適切に設定する
* インデックスについては、以下の関連記事を参照のこと

https://dk521123.hatenablog.com/entry/2010/04/01/175501
使用上の注意

 * ただし、無駄なインデックスは、
   更新時にインデックスを付加する時間が加算されるため、パフォーマンスが低下する
 → むやみに使用せず、適切に使用すること

 参考資料
http://www.atmarkit.co.jp/fdb/rensai/basics_rdb/05/bscrdb0501.html
http://www.geocities.jp/mickindex/database/db_optimize.html#LocalLink-left

 【3】相関サブクエリ / 自己相関サブクエリ

経験談も含めて ...

 * データ量が数十万になる/見込みがあるテーブルに対して、
 相関サブクエリを使用する際は、
 インデックス追加とSQL文チューニングおよびテストをしっかり行うこと
 => 本番環境データ(それに近いデータ量)がある場合は、
 そのデータを使って確認しておいた方がいい

 【4】ソート・グルーピング

1)GROUP BY,ORDER BYの使用は、極力避ける

 * 余分なディスク入出力が発生し、ディスク領域を使うため、
 使わずに済むならなるべく使わないようにする
 * ORDER BYの場合、どのような場合でも全表走査となる

2)GROUP BYは、NULL制約のない列を指定する

 * 指定対象列が、NOT NULL制約でない場合、
 インデックス検索されないため

 【5】ビュー

 ビューを使いすぎていないか確認する

 参考文献

http://oracletech.jp/products/tsushima/000377.html
http://itpro.nikkeibp.co.jp/article/COLUMN/20060111/227105/

 関連記事

インデックス ~ 入門編 ~
https://dk521123.hatenablog.com/entry/2010/04/01/175501
SQL文が実行されるまでの工程
https://dk521123.hatenablog.com/entry/2012/04/28/232901
パフォーマンスの良いSQLを記述 ~ 検索条件編 ~
https://dk521123.hatenablog.com/entry/2012/05/05/003504