SQLの効率化

SQLを高速化について、少し勉強したのでメモを残しておきます。

サブクエリを引数に取る時はINよりEXISTSまたは結合

SELECT * FROM A WHERE id IN(SELECT id FROM B);
-- より
SELECT * FROM A AA WHERE EXISTS(SELECT B BB WHERE AA.id = BB.id);
-- の方が早い。もしくは
SELECT AA.id, AA.name FROM A INNER JOIN B BB ON AA.id = BB.id;

• EXISTSは条件に合致するものを１行でも見つけたらそこで検索終了
• INは全表検索してしまう
• 結合キーにインデックスが張られていればインデックス参照だけで済む

※PostgreSQLは7.4からIN述語にサブクエリが速度改善されている
※OracleはINを使ってもインデックススキャンを行う様に工夫されている

DISTINCTをEXISTSで

SELECT AA.name FROM A AA INNNER JOIN B BB ON AA.name = BB.name;
-- より
SELECT name FROM A AA WHERE EXISTS(SELECT * FROM B BB WHERE AA.name = BB.name); 

• DISTINCTは重複を排除するソート
• 上記であればソートが発生しない

ALLオプション

SELECT * FROM A
UNION
SELECT * FROM B
-- より
SELECT * FROM A
UNION ALL
SELECT * FROM B

• UNIONは重複排除のソートを行う
• ALLを使えばソートは発生しない

GROUP BY と ORDER BY でインデックスを使う

• インデックスの存在するキーを指定すればソート時に高速化できる
• ユニークインデックスの場合はソートをスキップできる

MAXまたMINでインデックスを使う

• 引数にインデックスが存在した場合、そのインデックスだけで済む

-- IN述語で複数のキーを使う場合はまとめる
SELECT id,name,email FROM A AA 
WHERE name IN (SELECT name FROM B BB WHERE AA.id = BB.id) 
AND email IN (SELECT email FROM B BB WHERE AA.id = BB.id);
-- より
SELECT * FROM A AA
WHERE id || name || email IN (SELECT id || name || email FROM B BB)
-- の方が検索が１度で良い。または
SELECT * FROM A AA
WHERE (id,name,email) IN (SELECT id,name,email FROM B BB);
-- インデックスを利用できる

インデックスをうまく活用する

• 検索条件の右側で式を使えばインデックスを使用できる

-- test_1列にインデックスが張られているとして
SELECT * FROM A WHERE test_1 > 108 / 1.08 

• IS NULL・IS NOT NULL 述語を使わない

-- IS NULL または IS NOT NULL はインデックスを利用できない
SELECT * FROM A WHERE test_1 IS NOT NULL;

• 否定型は使わない

<>、 !=、 NOT INなどは全表検索し、インデックスを使用しない

• 前方一致を使う

SELECT * FROM A WHERE test_1 LIKE 'z%';

ちなみにインデックスとは
以下参照元：http://www.techscore.com/tech/sql/15_01.html/#sql151

引用：インデックスとは、データの検索速度を向上させるために、どの行がどこにあるかを示した索引のことです。

つまり、目次ですね。本などで調べる時はやはり目次を見た方が早いですよね。

作り方は

-- postgreSQL
CREATE [UNIQUE] INDEX インデックス名 ON テーブル名(列名,...) ;
-- oracle
CREATE [UNIQUE] INDEX インデックス名 ON テーブル名(列名 [ ASC | DESC ],...) ;

消し方

DROP INDEX インデックス名 ;

SQLの勉強することによりアプリケーションの速度向上も望めると思います。
今後はこの情報をAppにも活用していきたいですね。