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

SQL DBパフォーマンス

■ はじめに

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

目次

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

 【１】全般

 １）クエリの書き方を統一する

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

解決策 

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

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

 ２）ワイルドカード「*」を使用しない

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

 ３）表に別名を付ける

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

サンプル 

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';

 ４）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

 【２】インデックス

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

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

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

経験談も含めて ...

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

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

１）GROUP BY，ORDER BYの使用は、極力避ける

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

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

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

 【５】ビュー

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

 参考文献

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