サブクエリを引数に取る場合はINよりも結合を使う
SELECT table_a.id, table_a.name FROM table_a INNER JOIN table_b ON table_a.id = table_b.id;
メリットとしては、
- どちらかのテーブルのid列のインデックスを使用可能
- サブクエリがないことで中間テーブルが作成されない
しかし、インデックスがない場合はEXISTSの方が良い場合があります
ソートの回避
SQLでは暗黙的にソートが発生する演算が存在するので、
パフォーマンスにも影響するため、ソートが必要ない場合は考慮する必要があります
ソートが発生する演算
- GROUP BY句
- ORDER BY 句
- 集約関数（SUM, COUNT, AVG）
- DISTINCT
- 集合演算子（UNION, INTERSECT, EXCEPT）
- ウィンドウ関数（RANK, ROW_NUMBER 等）
メモリ上でのソートだけではなく、ストレージを使ったソートが行われるとパフォーマンスが大きく低下します
集合演算子のALLを使う
集合演算子（UNION, INTERSECT, EXCEPT）では、
普通に使用するだけで 「重複削除のためのソート」 が行われます
重複を気にしなくても良い場合は、ALL オプションをつけることでソートされません
SELECT * FROM table_a UNION ALL SELECT * FROM table_b;
DISTINCT の代わりに EXISTSを使用する
DISTINCTを使用すると、重複を排除してソートを行います。
SELECT table_a.name FROM table_a INNNER JOIN table_b ON table_a.name = table_b.name;
しかし、EXISTSであればソートされないので結果的に早くなります
SELECT name FROM table_a WHERE EXISTS(
SELECT * FROM table_b WHERE table_a.name = table_b.name
);
インデックスが正しく使用されているか
インデックスを使用しているつもりが実際は全件検索を行ってしまっている例を挙げます
索引列に加工を行なっている
SELECT * FROM class_a WHERE col_1 * 1.1 > 100;
上記のSQLではインデックスは適用されません。
検索条件の右側で式を使うことでインデックスが適用されます
SELECT * FROM class_a WHERE col_1 > 100 / 1.1;
インデックス列にNULLが存在する
IS NULLや、IS NOT NULLを使用すると、インデックスが使用されなかったり、NULLが多い列はインデックスが利用されなかったりします。
理由としては、NULLが列の正当な値では無いからです。
否定系を使用している
- <>
- !=
- NOT IN
このような場合は、インデックスは適用されません。
/* インデックスは適用されない */
SELECT name FROM table_a WHERE price <> 100000;
ORを使用している
SELECT * FROM table_a WHERE col_1 > 100 OR col_2 = 'abc';
col_1, col_2 それぞれで別々のインデックスを貼っている場合、また、複合インデックスを張っている場合。
いずれにおいてもOR条件を使用することでインデックスが利用出来ません。
仮に使えたとしてもANDよりも非効率な検索になります
複合インデックスの場合に、列の順番を間違えている
(col_1, col_2, col_3) に対して複合インデックスが張られている場合。
SELECT * FROM table_a WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 'abc'; /* インデックス適用可能 */
SELECT * FROM table_a WHERE col_1 = 10 AND col_2 = 100; /* インデックス適用可能 */
SELECT * FROM table_a WHERE col_2 = 100 AND col_3 = 'abc';
SELECT * FROM table_a WHERE col_1 = 10 AND col_3 = 'abc';
後方一致、中間一致のLIKE述語を使用している
前方一致のみがインデックスの使用がされます
SELECT * FROM table_a WHERE col_1 LIKE 'a%'; /* インデックス適用可能 */
SELECT * FROM table_a WHERE col_1 LIKE '%a';
SELECT * FROM table_a WHERE col_1 LIKE '%a%';
暗黙の型変換を行なっている
文字列で定義されたcol_1に対する条件を書く場合
SELECT * FROM table_a WHERE col_1 = '10'; /* インデックス適用可能 */
SELECT * FROM table_a WHERE col_1 = CAST(10, AS CHAR(2)); /* インデックス適用可能 */
SELECT * FROM table_a WHERE col_1 = 10;
暗黙の型変換は、オーバーヘッドを発生させるだけでなく、インデックスの使用も不可になる
中間テーブルを減らす
sqlでは、サブクエリの結果を中間テーブルとしてコード内で扱うことができます。
しかし、中間テーブルを不必要に使用することでパフォーマンスの低下に繋がります
中間テーブルの問題点は、
- データ展開のためにメモリを消費すること
- 元テーブルに存在するインデックスを使用することが難しくなる
集約よりも結合を先に行う
集約よりも結合を先に行うことで極力中間テーブルの使用を避けることができます
安易にビューを使用しない
ビュー定義のクエリに以下の演算が含まれている場合、非効率なSQLとなり思わぬ速度低下を招く原因となる
- 集約関数（AVG, COUNT, SUM, MIN, MAX）
- 集合演算子（UNION, INTERSECT, EXCEPT等）
まとめ
データベースをSQLにおいて、最大のボトルネックはストレージ(ハードディスク)へのアクセスです。
そのため、メモリの増設や、高速アクセス可能なフラッシュストレージの導入がパフォーマンスを向上させます。
以上のチューニングは、低速ストレージへのアクセスを減らすことを目的としています
参考