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

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

www.ketancho.net

本題に入る前に、ふたつ。嬉しかったこととお詫び（？）を。

t_wada さんからコメントを頂けた😂

素晴らしいエントリをありがとうございます。『SQLアンチパターン』は名著であると胸を張って言えます。ご興味をお持ちの方はこの機会にぜひ。 / “「SQLアンチパターン」を避けるためのチェックリスト①（DB論理設計編） - log…” https://t.co/Vjj0Yh2cqU
— Takuto Wada (@t_wada) 2018年3月8日

　スーパーなエンジニアの方からコメントをいただけるなんてと、帰り道ニヤニヤしてましたｗ色々拙い部分があると思いますが、自分の理解のために拙くてもいいので言語化を続けていこうと思っています。引き続きよろしくお願いします。

少しお詫び？

　t_wada さんはじめ、多くの方に拡散して頂いたおかげで、かなりの数の方に「SQLアンチパターン」を避けるためのチェックリスト①（DB論理設計編） - log4ketancho を読んでいただけました。コメントもたくさん頂けたのですが、そのなかで「常にグッドパターンがいいとは限らない」というコメントがありました。おっしゃる通りです。というか、本の中にも「アンチパターンを用いてもいい場合」という記載があるんです。私がそれを省いてしまっているんです。なので、少なくともこの本が「常にグッドパターンがいい」と言っている本ではないということだけご理解ください。

　自分の言葉でまとめている意図としては、

DB設計する際のインデックスとして役立てたい
似た状況があれば、記事を参考にした上で、該当する本の章に立ち返りたい
最終的にはこの本の知識を、本を参照せずとも息を吸うように考えられるようになりたい（＝エンジニアとしての血肉にしたい）

だったりします。あくまでインデックスなので、本の内容をサマライズして、かつ、自分の理解に置き換えてしまっています。気になる章があった方は、ぜひ本を読んでみてください。間違いなく学びにつながると思います。

SQLアンチパターン

作者: Bill Karwin,和田卓人,和田省二,児島修
出版社/メーカー: オライリージャパン
発売日: 2013/01/26
メディア: 大型本
購入: 9人クリック: 698回
この商品を含むブログ (46件) を見る

　それでは、本題の内容です。前回は論理設計でしたが、今回は物理設計の話です。

DB物理設計をする際のチェックポイント

□ 精度が求められる値の型に FLOAT 型を採用していないか
□ CHECK や ENUM などの列定義の制約で、列に入る値を限定してしまっていないか
□ 「何が何でもファイルはDBの外側で保持しなくちゃ！」と考えていないか
□ インデックスは正しく定義されているか、あるいは、インデックスを活かした検索をしているか

9. ラウンディングエラー（丸め誤差）：精度が求められる値の型に FLOAT 型を採用していないか

アンチパターン例

　高い精度が求められる列の型として FLOAT 型を定義してしまうのはアンチパターンです。例えば、時給を表す hourly_rate を FLOAT 型で下記のように定義したとします。

Teacher

user_id(PK)	email	hourly_rate
2	bob@xxx.xxx	15.53
4	dave@xxx.xxx	13.29

この場合、月末に働いた時間にhourly_rateを掛けたときに、計算結果が微妙にズレてしまいます。微妙なズレであれば許容される場合はFLOATで構いませんが、そうでない場合はFLOATの利用は避けるべきです。

グッドパターン例

　正確な値を求めたい場合は、NUMERICやDECIMALといった精度を設定できるデータ型を利用します。例えば、上の例であれば、NUMERIC(4,2)という型で定義することで、丸め誤差が発生しなくなります。

10. サーティワンフレーバー（31のフレーバー）：CHECK や ENUM などの列定義の制約で、列に入る値を限定してしまっていないか

アンチパターン例

　例えば、ある都立高校で生徒情報を管理するときに、住んでいる区を列に保存するとします。当時、私が入学する少し前までは学区制があり、私の通っていた高校は第４学区だったので、生徒は文京区、豊島区、板橋区、北区のいずれかの区に住んでいました。（この表現には嘘があります。あくまで設定です。*1 ）

Student

student_id(PK)	name	ward
1	Alice	BUNKYO
2	Bob	KITA

　列 ward には、BUNKYO, TOSHIMA, ITABASHI, KITA 以外は挿入したくないため、テーブル定義時に、下記のような制約を定義したとします。

CREATE TABLE Student (
　...
　ward VARCHAR(10)
　　CHECK (ward IN ('BUNKYO', 'TOSHIMA', 'ITABASHI', 'KITA')),
);

MySQL の場合は、下記のようにも定義できます。

CREATE TABLE Student (
　...
　ward ENUM('BUNKYO', 'TOSHIMA', 'ITABASHI', 'KITA')
);

しかし、このように列定義で列に入る値を縛る方法は下記のような問題を発生させるため、アンチパターンと言えます。

取りうる値を調べるのが難しい

　新入生のデータを追加する画面を作成するときに、区（ward）として選択できる項目をドロップダウンで表示したいところです。しかし、その列に取りうる値をメタ情報として管理してしまっており、候補値のリストを簡単に取得することができません。フロントサイドかバックエンドサイドで何らかの方法でリスト管理する必要があり、テーブル定義を変更したら、もう一方も変更しなくてはならず、リスト二重管理になってしまっているといえます。バグの要因となる可能性があります。

新しい値を追加するのも難しい（削除も）

　私の入学する年から学区が撤廃されました。そのため、葛飾（KASTUSHIKA）といった値が入りうることになりました。（私の地元は葛飾です！！）

　このような追加作業は、（データベースエンジンの種類によりますが、）一筋縄にはいかないことが多いです。最悪の場合、一度テーブルの値を空にする必要があるエンジンもあり、毎年の制度変更の都度、テーブル移行という心臓がドキドキする作業をする必要がでてきます。（私はやりたくありません。）

グッドパターン例

　別に参照テーブルを用意するのが正解と言えます。

Student

student_id(PK)	name	ward(FK)
1	Alice	BUNKYO
2	Bob	KITA

Ward

ward(PK)
BUNKYO
TOSHIMA
ITABASHI
KITA

この設計であれば、取りうる値を取得することも容易ですし、新しい値を追加するのも簡単です。

11. ファントムファイル（幻のファイル）：「何が何でもファイルはDBの外側で保持しなくちゃ！」と考えていないか

　※このパターンについては、アンチパターンとグッドパターンという明確な分け方はありません。強いて言うのであれば、「何が何でもファイルをDBの外側で」と思考停止してしまうことがアンチパターンと言えます。

　利用者のプロフィール写真といった、静的データをシステムで保持する要件があったとします。物理ディスクやS3といった外部ストレージにファイルの実体を保存し、DBにはそのファイルの保管先のみを保存する、というのがよくある設計パターンです。

　このように外部で管理する場合、

DBの容量を減らせる
容量が減らせるので（静的ファイル以外の）バックアップ時間を短く抑えられる
静的ファイルへの参照が用意になる（クエリを投げなくてもプレビューできる）

といった利点があります。しかしながら、

システムからファイルを削除する際に、DBへのクエリを投げるとともに、静的ファイルを削除する必要がある
その場合、トランザクション管理することができないので、ロールバックもできない
DBのバックアップとは別に、静的ファイルのバックアップ方式を検討する必要がある
静的ファイルへのアクセス権限はDBとは別に考える必要がある

といったデメリットもあります。BLOB 型としてDBに保存する設計とした場合、このような問題（手間）は発生しません。

　つまり、これらふたつの設計はメリデメがある、ということになります。どちらが適しているかは要件次第といえます。厳密なトランザクション管理が必要であればDB側に保存する、といったように設計時に検討してください。

　この章のアンチパターンは「絶対にDB以外の場所に静的ファイルを置かなくてはならない」と決めつけること、とのことです。「常にふたつの設計を検討候補に加えること」を心がけたいと思います。個人的に、まさにこのアンチパターン的考えに陥っていました。（外部に置くポリシーのプロダクトにしか関わったことがなく、かつ私自身そういうものだと思考停止していたと思います。反省。）

12. インデックスショットガン（闇雲インデックス）：インデックスは正しく定義されているか、あるいは、インデックスを活かした検索をしているか

アンチパターン例

なんでもかんでも（本来不要な）インデックスを定義している

　「インデックスを定義すれば検索が速くなるんですよね？！」と無邪気に定義したにも関わらず、有効活用されないケースです。有効活用するならいいんです。使われないのであれば、むしろ INSERT や DELETE 時の INDEX を再構成するコストが増えてしまい、パフォーマンスを悪化させてしまいます。

インデックスを毛嫌いする

　少しインデックスに関して知見がある人が「インデックスを定義すると、INSERT や DELETE の際に余計なコストがかかるんでしょ？」と心配して、インデックスを毛嫌いするパターンです。本の中にもありますが、データベースのクエリの大半は参照系です（参照系であることが多いです。一般論。）。ですので、大半のクエリのためになるのであれば、つまり、必要なインデックスなのであれば、積極的にインデックスを張るべきです。バランスが大事です。