【SQL】パフォーマンスの良いSQLを記述 ～検索条件編～

DBパフォーマンス

■ はじめに

https://dk521123.hatenablog.com/entry/2010/08/06/110122

の続き。

パフォーマンスの良いSQLを記述するためのTipsを纏める
今回は、検索条件に絞る。

目次

【１】WHEREの左辺で算術演算子や関数を使わない
【２】「!=」「<>」で検索することは、極力避ける
【３】IS NULL/IS NOT NULLで検索することは、極力避ける
【４】演算やキャストはなるべく避ける
【５】暗黙の型変換を行わず、明示的に型変換を行うこと
【６】選択条件が少なくなる条件から記述する
【７】OR/IN条件で、多そうな条件から並べて実装する
【８】NOT INの使用は、極力避ける
【９】ORを利用する場合、インデックス付き列を利用する
【１０】「後方一致」検索、極力避ける
【１１】HAVINGの使用は、極力避ける

【１】WHEREの左辺で算術演算子や関数を使わない

 * WHERE句の左辺に算術演算や関数を指定すると、
　インデックスが使われないため

１）解決策

 * 以下のサンプルのように、右辺で行うようにする

２）サンプル

X SELECT * FROM SampleTable WHERE salary * 1.1 > 250000;
O SELECT * FROM SampleTable WHERE salary > 250000 / 1.1;

【２】「!=」「<>」で検索することは、極力避ける

 * インデックスが使用されなくなるため

１）サンプル

X SELECT * FROM SampleTable WHERE empId <> 10;
O SELECT * FROM SampleTable WHERE empId < 10 OR empId > 10;

【３】IS NULL/IS NOT NULLで検索することは、極力避ける

 * 索引データの中に NULL は存在しないから
 * 詳細および対策は以下を参照のこと。

【４】演算やキャストはなるべく避ける

* インデックスを活かすために、演算やキャストはなるべく避ける

【５】暗黙の型変換を行わず、明示的に型変換を行うこと

以下の理由により、暗黙の型変換を行わず、明示的に型変換を行うこと

 * 暗黙の型変換は、一度代入に失敗した後に行なわれるため、オーバーヘッドが発生する
 * インデックスが使用されなくなる

http://www.geocities.jp/mickindex/database/db_optimize.html#LocalLink-type

１）サンプル

X SELECT * FROM SampleTable WHERE id = 2;
O SELECT * FROM SampleTable WHERE id = '2';
O SELECT * FROM SampleTable WHERE id = CAST(1, AS CHAR(1));

【６】選択条件が少なくなる条件から記述する

 * 検索条件が複数ある場合、抽出する件数が少なくなる条件から先に行う

１）サンプル

-- sex(性別)で選択するより、
-- grade(学年)で選択した方が件数が少なくなり、無駄が少なくなる

X SELECT * FROM SampleTable WHERE sex = 'male' AND grade = 3;
O SELECT * FROM SampleTable WHERE grade = 3 AND sex = 'male';

【７】OR/IN条件で、多そうな条件から並べて実装する

 * 初めに条件が合致すれば、以降の処理を行わずに済むため

 ※業務で使ったが、作成するシステムの業務のことをちゃんと理解する必要がある

１）サンプル

東京近郊にデータが多い場合 

X SELECT * FROM SampleTable WHERE place IN('Gifu', 'Osaka', 'Tokyo');
O SELECT * FROM SampleTable WHERE place IN('Tokyo', 'Osaka', 'Gifu');

【８】NOT INの使用は、極力避ける

* NOT INより、NOT EXISTSの方がパフォーマンスがいいため

【９】ORを利用する場合、インデックス付き列を利用する

 * ひとつでもインデックス付きの列がないと、全表走査となる

【１０】「後方一致」検索、極力避ける

 * インデックスを検索せずにデータ部の全表走査が行われてしまうため、
   使わずに済むならなるべく使わないようにする

どうしても必要な場合、以下のようにして、事前に絞り込めることを考慮すること

 * 少量まで絞り込める条件とAND条件で組み合わせる

【１１】HAVINGの使用は、極力避ける

 * 全表走査になるため、WHERE文に置き換えた方がいい
 * 余分なディスク入出力が発生し、ディスク領域を使うため、使わずに済むならなるべく使わないようにする

関連記事

パフォーマンスの良いSQLを記述 ～ 全般編 ～
https://dk521123.hatenablog.com/entry/2010/08/06/110122