はじめに
SQLの処理速度が遅い!
こんなんじゃシステムとして失格だよ。。
なるほど、今回はSQLの性能改善(パフォーマンスチューニング)
について学習していこう!
パフォーマンスチューニング実例
以降、代表的なSQLのパフォーマンスチューニング例を紹介していきます。
また、以降のSQL文は基本、標準SQLで記載しているのでOracle、MySQL、PostgreSQL等では動作する想定です。
SELECT句の 「*」の排除
SELECT句の「*」は便利ですが、その反面、パフォーマンス面では良いことがありません。
テーブルのカラムが多いほど、取得に時間を要します。面倒ではありますが、SELECT句では一つ一つカラムを指定するようにしましょう。
INを使用している箇所をEXISTSにする
INは全表検索を行います。一方、EXISTSは1行でも条件に合致する行を見つけたら、検索を終了します。また、EXISTSの場合、結合キーにインデックスが貼られている場合、実表は見に行かず、インデックスの参照のみを行います。
SELECT * FROM item_a WHERE id IN (SELECT id FROM item_b);・・・NG
SELECT * FROM item_a A WHERE EXISTS (SELECT * FROM item_b B WHERE A.id = B.id);・・・OK
中間テーブルを減らす
サブクエリ等を使用して生成された中間テーブルは、データを展開するためにメモリを消費すること、インデックスを使用できなくなることからパフォーマンスの劣化につながります。パフォーマンスを意識する場合、可能な限り中間テーブルを排除する必要があります。
集約に対する条件はHAVING句を利用する
下記はNG例です。赤字箇所で一度、中間テーブルを作成し、WHERE句で条件を指定しています。
SELECT *
FROM (SELECT id,SUM(quantity) as sum
FROM item
GROUP BY id)
WHERE sum >= 100;
下記のようにHAVING句を使用することで中間テーブルは不要になります。
SELECT id,SUM(quantity) FROM item GROUP BY id HAVING SUM(quantity) >= 100;
WHERE句でのインデックスの利用を確認する
下記のようなSQLの書き方をしているとインデックスが利用されずに、テーブルを全件検索してしまいます。
カラムに対して計算を施している
SELECT * FROM users WHERE user_id * 10 > 100;
「WHERE user_id > 100/10」のように列に計算をしないようにすれば、インデックスが利用されます。
カラムに対して関数を使用している
SELECT * FROM users WHERE LOWER(name) = 'takeshi';
否定形の条件を使っている
SELECT * FROM users WHERE user_id <> 5;
否定形の場合もインデックスが使用できません。「<>」「!=」等を使用している箇所がないか見直しましょう。
NULLであることを条件にしている
SELECT * FROM users WHERE name IS NULL;
曖昧検索(後方一致又は中間一致)をしている
SELECT * FROM users WHERE name LIKE '%o';
LIKEを使用する場合、前方一致「’a%’」のみがインデックスが使用されます。
複合インデックスでの列の順番
id_1、id_2、id_3の順に複合インデックスが貼られている場合、必ず最初の列(id_1)を先頭に書かなければなりません。また、上記の順番も崩してはいけません。
SELECT * FROM users WHERE WHERE id_1 = 1 AND id_2 = 2 AND id_3 = 3;・・・OK
SELECT * FROM users WHERE WHERE id_1 = 1 AND id_2 = 2;・・・OK
SELECT * FROM users WHERE WHERE id_2 = 2 AND id_3 = 3;・・・NG
SELECT * FROM users WHERE WHERE id_1 = 1 AND id_3 = 3;・・・NG
できる限りソートを避ける
ソートがストレージ上で行われていると、パフォーマンスが大きく低下します。無駄なソートは減らすように心がけましょう。下記の演算がソートを行います。
- ORDER BY
- GROUP BY
- 集約関数(SUM,COUNT,MAX等)
- DISTINCT
- 集合演算(UNION、EXCEPT等)
ORDER BYとGROUP BYではインデックスを使う
ORDER BYやGROUP BYは通常、並び替えるためソートを行います。この際、インデックスの存在する列をキーに指定することで、検索を高速化することができます。
集合演算でALLを使用する
UNION、INTERSECT、EXCEPTなどの集合演算は、重複排除のためにソートを行います。
重複を気にしなくて良い場合、UNION ALLを使用することでソートが発生しないため、パフォーマンスの向上につながります。
終わりに
本記事はここまでとなります。ご覧いただきありがとうございました。
ご指摘等がございましたら頂けますと嬉しいです。
引き続き、プログラミングについて定期的に発信していきますのでよろしくお願いします!
また、もしよろしければtwitterもフォローしていただけると嬉しいです。
コメント