SQLアンチパターンを知ることで、パフォーマンスの向上やバグの回避に役立てることができます。また、SQLをよく知ることは、ソフトウェア開発初級者が中級者へと進むための重要なステップでもあります。
良いSQLを知ることで一つ先のステージへ進めるように、ここではSQLにおけるアンチパターンをいくつかご紹介します。
アンチパターンとは？
アンチパターンとはソフトウェア開発におけるよくある失敗パターンを整理し、それを回避する方法をまとめたものです。前回データベース設計におけるアンチパターンを解説しましたが、SQLはデータベースと違い、厳密性よりもパフォーマンスやバグに関する問題点が浮上しやすいことが特徴です。
特にSQLのNULLの扱いについては直感的に理解しづらく、とはいえ切っても切り離せない存在なので、十分に注意を払う必要があります。
（SQLのアンチパターン）NULL値の多用
未知と適用不能
みなさんはSQLにおけるNULLとは一体なんのことなのかご存知ですか？JavaやPHPなどのプログラミング言語ではnullは「値が未知（unknown）であること」を指します。そこは同じなのですが、SQLではそれに加えて「適用不能であること」も追加されます。
例えば、LEFT JOINで結合する際、結合対象が存在しない行は全てNULLになっていると思います。あのNULLが適用不能の一つの例です。
このように、SQLでは2つの意味のNULLが存在するので、そのNULLが一体何を指しているのかが分かりにくいというデメリットがあります。
= nullが意図しない結果を返却するのはなぜ？
また、nullは比較をする際、is nullとする必要があります。なぜ= nullではなくis nullなのか、SQLを触ったことのある方であれば誰もが一度は疑問に思うのではないでしょうか。
これについての答えは明確で、SQLでの値とnullの比較は全て「unknown」という結果になるからです。基本的に条件文はtrueかfalseのどちらかを返す「2値理論」が使われます。これは非常に直感的で分かりやすいです。
しかし、SQLの条件文はtrueとfalseの他にunknownを返す場合があり、これを「3値理論」と呼びます。この3値理論が人間の直感から大きく外れており、理解しにくい元凶です。
例えばですが、次の条件式は全てunknownを返します。
- 列名 = NULL‘リテラル’ = NULL100 < NULLa = b AND b = c (cはNULL)NULL = NULL3 > 4 OR 4 < NULL
このように、式の中にNULLが含まれているものを比較しようとすると、必ずunknownになります。なぜかと言うと、比較した結果がtrueなのかfalseなのかが分からないからです。SQLはtrueになった行のみ返すので、falseはもちろんunknownになった行も返しません。
上記のような単純な比較ならまだマシですが、これがNULLを許容している列のデータとなると、もしNULLかどうかでケース分けするのを忘れると、結果が取得できずバグに直結します。また、NULLは算術演算と結びついても結果をNULLにしてしまいます。
- 100 + NULLNULL - 10
これもいちいち、「値がNULLの場合は0にする」とか「NULLの場合は計算しない」とかをSQL内に記述する必要が出てきます。このように、NULLは演算の中に入ってくると、結果を全てNULLにしてしまう特性を持っており、これもバグに直結します。これがNULL(unknown)の危険性です。
このようにNULLは直感性に欠ける挙動を起こすことがあり、それがバグを引き起こす原因となるので多用すべきではありません。とはいえ先ほどのLEFT JOINの話もそうですが、NULLはデータ操作にとって切っても切り離せない存在で、完全にNULLのない世界は作れません。
（SQLのアンチパターン）Null値の多用の回避方法
ここからはNULLを多用しないためにどうしたら良いのかを考えてみたいと思います。
数値の場合：システム上あり得ない数値を入れる
例えば-1や最大値など、システム上あり得ない数値を入れることでNULLを回避できます。もちろんこれでも条件文を記述する必要に変わりはありませんが、unknownを排除できるので2値理論の中で条件を記述することができ、シンプルになります。
文字列の場合：空文字を入れる
文字列の場合は単純に空文字を入れておくことでNULLを回避できます。しかし「空文字が意味を持つ列」の場合はこれだとダブルミーニングになってしまうので、その場合はNULLが必要になるでしょう。
日付の場合：1980/01/01と9999/12/31を使う
日付の場合も数値と同じくあり得ない最小値や最大値を使うことでNULLを回避できます。
（SQLのアンチパターン）SQLがDRYではない
ある結果を取得するためのSQLを、至る所で使用しているのはDRY(Don't Repeat Yourself)ではありません。DRY原則を守ることは再利用性に優れたSQLを記述できるという点で非常に重要です。ですので、ビュー機能を使い、同じクエリ結果を1つのビューにしてしまうことをおすすめします。
ただし、SQLの抽象化は手続型言語の抽象化に比べ、レスポンスに大きな影響を及ぼしがちなので、パフォーマンスと原理原則のどちらを優先すべきかはその時々の要件によって異なります。少し扱いは難しいですが、場合によってはマテリアライズドビューの検討も視野に入れてみましょう。
（SQLのアンチパターン）インデックスが使われていない
大量のデータが格納されているテーブルは、主キーだけでは検索パフォーマンスに優れず、時間がかかり過ぎてしまうことも多いです。そういった場合はインデックスを作ることが基本ですが、インデックスを作成するとインデックス用のデータを作成するので容量を圧迫する点に注意が必要です。
また、インデックスを作成すると検索は早くなりますが、その分更新処理を行う場合はインデックスデータの書き換えが発生し、更新に時間がかかるようになります。なので、作ったインデックスはしっかりと使われるようにしましょう。作ったインデックスが使われないパターンの例は以下です。
否定形を使う
基本的に否定形を使用するとインデックスは使われません。否定形の条件に合致した行を取得するには全ての行を参照する必要があるからです（これを全表検索と言います)。
<>やNOT INなどが対象です。
安易に否定形を使わずに同じことができないかを模索してみましょう。
ORを使う
別々のインデックスを持った列をORを使って条件を結合するとインデックスが利用されなくなります。ただし、ORの場合はビットマップ・インデックスというOR検索に向いたインデックスが利用できます。
しかし、これも作成することで更新時のレスポンスに影響を及ぼしますので、パフォーマンスへの影響を加味しながら導入を検討してください。
IS NULLを使う
インデックスデータにNULLは存在しないので、IS NULLを使うと強制的に全表検索になり、インデックスは使われません。なぜインデックスデータにNULLがないのかと言うと、NULLが値ではないからです。
なるべくNULLを使わず、別の値に置き換えてNULLを表現することでインデックスが使えるようになります。
（まとめ）アンチパターンも一長一短
ここまでSQLのアンチパターンについて解説させていただきました。どれをみても分かる通り、アンチパターンというのは絶対にやってはいけないものではありません。その時々でアンチパターンを使うことが正解の場合もあり、我々ITエンジニアはその選択肢の中からどれを選ぶのが最適かを判断する必要があります。
その判断をできるようにするためにも、こういったアンチパターン、デザインパターンを知ることは、プロジェクトの成功に大きく関わってくる部分です。ここでご紹介したアンチパターンはごく一部です。ぜひ他のパターンも探してみてくださいね。
