SQL

SQLの効率化

More than 3 years have passed since last update.

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は条件に合致するものを1行でも見つけたらそこで検索終了

  • 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)
-- の方が検索が1度で良い。または
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にも活用していきたいですね。