【クエリ最適化/SQL】クエリを最適化する方法

今回の課題

クエリ最適化の英語の記事があったので、
英語の勉強も含めて要約した。

※参考：SQL Query Optimization Techniques

1,テーブルの中から特定の要素を見つける場合、COUNT()の代わりにEXISTS()を使う

COUNT()を使用すると、全量スキャンになってしまい非効率。
一方、EXISTS()は、特定の要素を見つけた段階でスキャンが止まる。
そのため、クエリの実行時間を節約することができる。

⭕推奨

select
    exists(
        select
            *
        from
            table
        where
            element_column = 'val'
    )

❌非推奨

select
    count(*)
from
    table
where
    element_column = 'val'

2,Charの代わりにVarcharを使用する

charとvarcharはテーブルの文字列の型として使われる。
しかし、varcharはcharよりもメモリ効率がかなり良い。

char型は指定した固定長の文字列のみを格納できる。もし、指定した文字列よりも短い文字数だった場合、指定した文字数との差分の量、メモリに無駄が生じる。
一方varchar型は、指定された最大長以下の可変長の文字列を格納できる。格納された文字数に応じてメモリを消費するので、メモリに無駄が生じない。

3,WHERE句ではサブクエリを避ける

サブクエリが大量の行を返す場合、コストが高くなり実行が困難になる。
サブクエリを使用する代わりに、JOINを使って同じ結果を得ることができる。

⭕推奨

select
    orders.*
from
    orders
    join customers -- customersテーブルと、顧客idをキーにしてinner join結合
        on orders.customer_id = customers.id
where
    customers.country = 'INDIA'
;

❌非推奨

select
    *
from
    orders
where  -- サブクエリでcountryがINDIAの場合の顧客のidを抽出している
    customer_id in (select id from customers where country = 'INDIA')
;

4,大テーブルから小テーブルに向ってJOIN(結合)するようにする

そのように結合するとSQLの実行が速くなる。
理由としては、小テーブルから大テーブルに結合すると、SQLエンジンは大テーブルからマッチする行を探さなければいけない。
一方、大テーブルから小テーブルに結合すると、小テーブルからマッチする行を探すため効率が良い。

⭕推奨

select
    *
from
    BigTable as b
    left join SmallTable as s
        on b.id = s.id

5,Like句の代わりにregexp_like(正規表現での曖昧検索)を使用する

Like句は特定の値を探すのにデータベースを全量スキャンしなければならなく、大きいテーブルだと時間がかかってしまう。
一方、regexp_likeは効率的で、正規表現を使って複雑なパターンの値を探すことができる。Likeのようなシンプルなワイルドカードマッチングよりも正確に特定の値を見つけられるので、検索すべきデータ量が減り、クエリの実行速度が速くなる。

⭕推奨

select
    *
from
    table
where
    regexp_like(name, '^[AB].*')
;

❌非推奨

select
    *
from
    table
where
    (
    name like 'A%'
    or name like 'B%'
    )