SQL文を書く時に気を付けていること

SQL関連で、自分が今までに教わってきたことを一部まとめてみます。

早い段階で取得するデータを絞る

クエリを速くするには、とにかく早い段階で取得するデータ量を少なくする必要がある。
サブクエリをたくさん含む複雑なクエリを作成する場合も、
最後にまとめてWHEREで絞るのではなく、各サブクエリ内でレコード数を減らすことが重要だ。

SELECT文の評価順序

1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.HAVING
7.SELECT
8.DISTINCT
9.ORDER BY
10.TOP（LIMIT）

上記のSELECT文の評価順序から
可能な場合は、WHEREで条件指定するよりも、JOINのONで条件指定するほうが、
取得されるデータ量が少なくなるため、処理がはやくなることがわかる。
他にも

• ルールベースのオプティマイザの場合は、WHEREは選択条件が少なくなる条件から記述する
• 検索条件が複数ある場合、抽出する件数が少なくなる条件から先に行う
• インデックスが貼られていたり、カーディナリティ度が高い場合、それが先に評価されるので注意が必要。正確な評価順序は統計情報とプランナに依存する
• 結合時にはあらかじめ結合の件数を減らす
• LEFTなどで結合する際は、各々の件数を減らした上で結合する
• ソート処理はできるだけ少ない件数で行う
• ソート処理は負荷が大きい処理のため、できるだけ少ないデータで行った方が高速になる。

などに気を付ける。

SELECTでは必要なカラムのみを取得する

レコード長が長いときや、フィールド数が多いときには、取得するカラムを絞ることで検索速度が上昇する。
「＊」を指定すると、参照系のSQL文では、すべてのフィールドを繰り返してコピーするため、リソースを無駄に使うことになる。

before

SELECT *
FROM Order

after

SELECT id
      ,name
FROM Order

演算はできるだけ避ける

• WHEREでインデックス列に算術演算子や関数を使用しない
• インデックス列に算術演算や関数を指定するとインデックスが有効にならないため、検索速度が低下する。

before

-- priceはインデックス列とする
SELECT id
FROM Order
WHERE price * number > 10000

after

-- priceはインデックス列とする
SELECT id
FROM Order
WHERE price > 10000 / number

• サーバサイドで計算する
• アクセスが多い処理の場合、簡単なSQLでデータを取得後、サーバーサイドのプログラミング側で計算するということもある。

Oracleの場合、"IS NULL"や"IS NOT NULL"を単独で使わない

これらの条件を指定するときは、単独で指定 するのではなく、何らかの絞り込める条件を合わせて指定する。
条件を表すWHEREにIS NULL/IS NOT NULLを指定したときは、インデックスを定義したフィールドであっても、全表走査が行われるためである。

例えば、問い合わせの結果を変更せずに「val > 5」の条件を付加できるなら

before

SELECT name
FROM Order
WHERE id IS NULL

after

SELECT name
FROM Order
WHERE id IS NULL AND val > 5 

とする。
また、NULL比較をしなくても良いようにNULL値に意味を持たせないようにするといった対策も有効である。

一方で、MySQL、SQL Server、PostgreSQLの場合は、NULLを条件にしたときもインデックススキャンされる。
参考:IS NULLはインデックスを使うのか

さいごに

SQLは、テーブルの件数やインデックスが貼られているかどうかなどによっても最適解は異なります。
どのデータベースを使用するかによって方言が異なるため、リファレンスを確認すること、また、このような記事を鵜呑みにしないで実際にパフォーマンステストをしてみることも大切です。
自分も引き続き精進します。
ご清覧ありがとうございました。