LoginSignup
104

More than 5 years have passed since last update.

SQLの効率化

Posted at

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にも活用していきたいですね。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
104