「SQLアンチパターン」を避けるためのチェックリスト③（SQLクエリ設計編）

　引き続き「SQLアンチパターン」について、自分なりのチェックポイントを言語化していきたいと思います。

• 「SQLアンチパターン」を避けるためのチェックリスト①（DB論理設計編） - log4ketancho
• 「SQLアンチパターン」を避けるためのチェックリスト②（DB物理設計編） - log4ketancho
• 「SQLアンチパターン」を避けるためのチェックリスト③（SQLクエリ設計編） - log4ketancho 【本記事】
• 「SQLアンチパターン」を避けるためのチェックリスト④（アプリケーション設計編） - log4ketancho

この記事では、SQL クエリを作成するときに抑えておきたい勘所について整理します。

SQLアンチパターン

SQLクエリ設計をする際のチェックポイント

□ NULLを一般値として使用していないか？一般値をNULLに相当するものとして扱っていないか？
□ GROUP BY句を使う場合、単一値の原則を満たすクエリとなっているか
□ ランダムにレコードを取得する場合、性能劣化しない方式を採用できているか
□ データ量が増える可能性のあるテーブルに、あいまい検索していないか
□ 複雑すぎるクエリを作っていないか
□ 必要のない列まで SELECT していないか

13. フィア・オブ・ジ・アンノウン（恐怖の unknown）：NULLを一般値として使用していないか？一般値をNULLに相当するものとして扱っていないか？

　NULL を含む可能性のある列に対して、どのようなクエリを書くかについてです。

アンチパターン例

　大きくふたつのアンチパターンがあります。

１）NULLを一般値として使用してしまう

　例えば、下記のような利用者の名前と血液型を管理するテーブルがあったとします。

User

user_id(PK)	name	blood_type
1	Alice	A
2	Bob	O
3	Carol	NULL

最近、利用者として追加された Carol はまだ血液型の情報を入力していないため、 NULL としています。このようなテーブルに対して「血液型が入力されていない人」を取得するために、

SELECT user_id, name
FROM User
WHERE blood_type NOT IN ('A', 'B', 'O', 'AB')

とするのはアンチパターンな（というより期待する値が取れない）クエリです。

２）一般値をNULLに相当するものとして定義してしまう

　１）のようなケースがあるので NULL を悪者と捉え、その列にNOT NULL制約をつけ、NULLの替わりに'unknown'を入れておこう！-1を入れておこう！とするのもアンチパターンです。

User

user_id(PK)	name	blood_type
1	Alice	A
2	Bob	O
3	Carol	unknown

　このような定義をしてしまうと、将来的にunknownや-1が意味をなす値になった場合に、移行作業が発生しうるからです。また、開発者が「この列の-1は意味のない値なんだな」ということを覚えておかねばならず、ジワジワと生産性を下げる遠因となります。

グッドパターン例

　設計時に意識すべきことは下記の２つです。

• NULL を検索したいときは IS NULL 熟語を使いましょう
• 列にNOT NULL制約をつけるのは、値のない列がその行にとって意味をなさない場合に限りましょう

　また、本の中では NULL の扱いについて紹介されています。

• なぜ NULL = 12345 が FALSE ではないのか？
  • NULL という不明な値が、12345 と等しいかどうかがわからないので
• NULL = NULL は TRUE ではないのか？
  • 不明な値と不明な値が等しいかどうかは分からないので

など、他にも直感的に期待する振る舞い（？）と異なるケースの説明がされています。

14. アンビキュアスグループ（曖昧なグループ）：GROUP BY句を使う場合、単一値の原則を満たすクエリとなっているか

アンチパターン例

　GROUP BY を使って集約する際に、非グループ化列（SELECTの対象になっているが、GROUP BY で指定されてない列）を参照すると、直感とは異なる結果が返ってきたり（エンジンがMySQL*1かSQLiteを用いている場合）、エラーが発生したりします。これは GROUP BY 句を含むクエリを実行する場合、「 SELECT句で指定された列はグループごとに単一の値になる必要がある」という単一値の原則があり、この原則に違反するようなクエリを投げしまっているからです。

　例えば、下記のようなテーブルがあったとします。

Teacher

user_id(PK)	email	subject	created_at
1	alice@xxx.xxx	English	2018-03-01
2	bob@yyy.yyy	Mathematics	2018-03-01
3	carol@zzz.zzz	English	2018-03-05

このテーブルに対して、

SELECT user_id, subject, MAX(created_at)
FROM Teacher
GROUP BY subject

というクエリを実行した場合、

user_id(PK)	subject	created_at
2	Mathematics	2018-03-01
3	English	2018-03-05

直感的には、上のような結果が返ってくると思いきや、

user_id(PK)	email	subject	created_at
1★	alice@xxx.xxx	English	2018-03-05
2	bob@yyy.yyy	Mathematics	2018-03-01

という結果になる可能性があります。★のuser_idがずれてしまっています。（前述の通り、エラーとなる場合もあります。）

グッドパターン例

　上記のような検索を行う場合は、

• 要件を満たすために、関数従属性のある列にのみクエリを実行するのではダメかを検討する
  • 上の例で言うと、 subject が決まれば created_at が一意に定まる場合
  • この場合は、user_id は一意に定まらないので、user_id が必要でない場合のみに限る
• サブクエリを使って中間テーブルを作る方法
• 外部結合 OUTER JOIN を使用する

などが紹介されています。どのように解決すべきかはケースバイケースですが、１）機能要件を満たしつつ、２）性能を悪化させないクエリ作成を心がけます。

15. ランダムセレクション：ランダムにレコードを取得する場合、性能劣化しない方式を採用できているか

アンチパターン例

　テーブルからランダムに値を取る場合に、

SELECT * 
FROM Teacher
ORDER BY RAND() LIMIT 1;

のように、RAND 関数を用いてレコードを取得すると、テーブルのレコード数が多い場合に性能が悪化してしまいます。RAND 関数でソートした結果、インデックスが効かず、フルスキャンしてしまっているからです。

グッドパターン例

　このアンチパターンを回避する方法として、

• 1から主キーの最大値の間から値をランダムに取得する
  • 主キーが1から連続している場合のみ使える
• 1から主キーの最大値の間から値をランダムに取得し、その値が欠損している場合はそれに一番近い（次の）値を採用する
  • 主キーが連続していなくても使えるが、均等にはならない（自分の前の値がたくさん欠損している方が選ばれやすい）
• アプリケーション側で選択する
  • データが多い場合のメモリ枯渇に注意

が紹介されています。

16. プアマンズ・サーチエンジン（貧者のサーチエンジン）：データ量が増える可能性のあるテーブルに、あいまい検索していないか

アンチパターン例

　下記のようなあいまい検索を含む、データ量が増えた時に性能劣化するクエリを作っていないか。

SELECT user_id, subject
FROM Teacher
WHERE subject LIKE '%ng%'

グッドパターン例

　全文検索エンジンを利用する。

• 各データベースエンジンに備わっている機能を使う
• Solr、ElasticSearch のような全文検索エンジンを利用する設計に変更する。

17. スパゲッティクエリ：複雑すぎるクエリを作っていないか

アンチパターン例

　なんでもかんでもひとつのクエリで解決しようとして、

• バグを生みやすい
• 作った人にしかわからないような
• メンテのしにくい

クエリを書いてしまうのはアンチパターンです。

グッドパターン例

　ひとつのクエリで解決する必要性がない場合は、クエリを分割します。複数のクエリをひとつにする必要が有る場合は UNION を使いましょう。

　アプリケーションプログラムと同じで、保守性の高いクエリを心がけたいものです。

18. インプリシットカラム（暗黙の列）：必要のない列まで SELECT していないか

アンチパターン例

SELECT *
FROM Teacher
WHERE ...

　このように、ワイルドカード * を用いてレコードを取得した場合、不要な（アプリケーション側で使わない）カラムまで取ってくるので、パフォーマンスに影響が出る可能性があります。

グッドパターン例

　列名を明示的に指定するし、必要な列だけを取得するようにします。（ただし、調査などで１度しか使わないクエリの場合は許容）

まとめ

　クエリ設計についてまとめました。16や18あたりは有名な話かと思いますが、14の単一値の原則の話、15のランダムの話は意識したことがなかった＆いつか地雷を踏みそうな話だったので、事前にアンチパターンを知れてよかったです。

　また、ここで紹介されているアンチパターンの多くが、データ量が少ないときは問題ないが、増えた時に初めて問題に気付けるパターンだと思います。よくあるアンチパターンは設計で避けつつも、最終的には性能テストをしっかりやることで担保するしかないのかな、と感じました。

　「SQLアンチパターン」の自分なりの整理の第三弾でした。

第１弾「DB論理設計」編はこちらです。

www.ketancho.net

第２弾「DB物理設計」編はこちらです。

www.ketancho.net

第４弾「アプリケーション設計」編はこちらです。

www.ketancho.net

　冒頭にも書きましたが、この記事の内容は個人的サマリになります。特に、今回取り扱った章ではグッドパターンの紹介が多かったので、私なりによく使うであろうパターンに絞ってしまっています。幅広く理解を深めたい方は、ぜひ本をご覧いただくとよいかと思います。

SQLアンチパターン

*1:SQL モードに ONLY_FULL_GROUP_BY を指定した場合