ずっと前から積ん読状態だった「SQLアンチパターン」を読みました。
何年積んでたか分からないSQLアンチパターン読み終わったー。噂に違わずいい本ですねー。もっと早く読むべきだった。
— @ketancho (@ketancho) 2018年3月6日
噂通りとても良い本で、まさに「エンジニアとしての血肉本」と言えます。1, 2年目の頃に読んでおくべきだったなーと少し後悔しています。
- 作者: Bill Karwin,和田卓人,和田省二,児島修
- 出版社/メーカー: オライリージャパン
- 発売日: 2013/01/26
- メディア: 大型本
- 購入: 9人 クリック: 698回
- この商品を含むブログ (46件) を見る
読んで終わりだと身につかないと思うので、自分なりのチェックポイントを言語化しておこうと思います。長くなってしまったので、章ごとに4つに分けたいと思います。この記事はその第一弾となります。
- 「SQLアンチパターン」を避けるためのチェックリスト①(DB論理設計編) - log4ketancho 【本記事】
- 「SQLアンチパターン」を避けるためのチェックリスト②(DB物理設計編) - log4ketancho
- 「SQLアンチパターン」を避けるためのチェックリスト③(SQLクエリ設計編) - log4ketancho
- 「SQLアンチパターン」を避けるためのチェックリスト④(アプリケーション設計編) - log4ketancho
※ アンチパターンごとに Bad ポイントが幾つか紹介されていますが、その中の一部(自分が重要だと思ったもの)のみを記載しています。良い設計方法についても同様で、代表的なもののみ書いている部分があります。あくまで自分用のチェックポイント、インデックスにしたくて作成したので、選択が良くないものがあれば申し訳ありません。
データベース論理設計のアンチパターンチェックリスト
データベース論理設計についてこの記事では整理します。
論理設計をしくじったアプリケーションをメンテすると、生産性が非常に落ちることは多くのエンジニアの方が経験していると思います。そして、それに気付いたときには、既にデータが大量に追加されており、最適化したくても手が出しにくい状況になっていることも頻繁にあるのではないでしょうか?
そうならないために、最低限押さえておくべきポイントが8つ紹介されていました。詳細はこのあと記載しますが、チェックすべきポイントのサマリは下記の通りです。(ただし、各項目ごとに「許容してもいい場合」という紹介もあるので、詳しくは本を参照してみてください。)
□ カンマ区切りフォーマットのリストを格納していないか? □ 3世代以上の階層があるデータ構造を扱う場合に、直接の親子関係のみを参照する形にしていないか? □ 主キー名を何も考えず `id` にしていないか? □ 必要な外部キーは設定しているか? □ 柔軟に「列」を増やすために、Attribute とその Value を表す列を作成してしまっていないか? □ 複数の異なるテーブルに紐付く可能性があるテーブルを作成する場合に、xx_type といった列を見て紐付く先のテーブルを判断する設計になっていないか? □ 1対Nで保持したいデータがある場合に、列を row1、row2、、、rowN のように増殖させる設計にしていないか? □ 時間経過によってテーブルや列を増やすことを前提とした設計になっていないか?
1. ジェイウォーク(信号無視):カンマ区切りフォーマットのリストを格納していないか?
アンチパターン例
複数個の要素を持つ場合のテーブル設計をすることを考える。例えば、学生とその学生に割り当てられている役割(係)を表現する場合に、下記のようなカンマ区切りフォーマットのリストを格納していないか?
User
user_id(PK) | name | role_ids |
---|---|---|
1 | Alice | 1, 3, 6 |
2 | Bob | 2, 3 |
Role
role_id(PK) | name |
---|---|
1 | 図書係 |
2 | 保健係 |
3 | xx係 |
6 | yy係 |
グッドパターン例
交差テーブルを用いるようにする。
User
user_id(PK) | name |
---|---|
1 | Alice |
2 | Bob |
UserRole
user_id(PK) | role_id(PK) |
---|---|
1 | 1 |
1 | 3 |
1 | 6 |
2 | 2 |
2 | 3 |
Role(変更なし)
role_id(PK) | name |
---|---|
1 | 図書係 |
2 | 保健係 |
3 | xx係 |
6 | yy係 |
2. ナイーブツリー(素朴な木):3世代以上の階層があるデータ構造を扱う場合に、直接の親子関係のみを参照する形にしていないか?
アンチパターン例
このような階層構造を表したい場合(例えば、連絡網など)に、下記のように直接の親子関係をのみを表現する形にしてしまうと、幾つか問題が発生する。
Students
student_id(PK) | parent_id | tel |
---|---|---|
1 | NULL | 090-xxxx-xxxx |
2 | 1 | 090-xxxx-xxxx |
3 | 1 | 080-xxxx-xxxx |
4 | 2 | 090-xxxx-xxxx |
5 | 2 | 080-xxxx-xxxx |
6 | 3 | 090-xxxx-xxxx |
例えば「2を消したい」場合、繋ぎ直しのコストが非常に大きくなってしまう。「2以下を全部消したい」場合、まずは子供を辿った上で、子供から削除していくような処理が必要になる。今回のような小さい例であれば問題ないが、階層が大きくなると非常に辛い処理になる。
グッドパターン例
閉包テーブルという 直接の親子関係だけではなく、木全体のパスを格納する方式
を用いる。親子を辿る際に、JOIN が必要になるが、ノードを削除する場合(そのノードが関連するレコードを全て削除すれば良い)も、サブツリーを削除する場合(シンプルに上から順に消していくでいい)もコストが低くメンテナンス性が高い。
Students
student_id(PK) | tel |
---|---|
1 | 090-xxxx-xxxx |
2 | 090-xxxx-xxxx |
3 | 080-xxxx-xxxx |
4 | 090-xxxx-xxxx |
5 | 080-xxxx-xxxx |
6 | 090-xxxx-xxxx |
StudentsTree
parent_id(PK) | child_id(PK) |
---|---|
1 | 1 |
1 | 2 |
1 | 3 |
1 | 4 |
1 | 5 |
1 | 6 |
2 | 4 |
2 | 5 |
3 | 6 |
3. ID リクワイアド(とりあえず ID):主キー名を何も考えず id にしていないか?
アンチパターン例
- なんでもかんでも無邪気に主キーとして
id
を定義してしまうパターン。
グッドパターン例
- 分かりやすい名前にする。
id
ではなくstudent_id
とか。 - 複合主キーにできないか考える。
id(PK)
,student_id
,role_id
ではなくstudent_id(PK)
,role_id(PK)
4. キーレスエントリ(外部キー嫌い):必要な外部制約は設定しているか?
アンチパターン例
- 外部キーを張らない
- 結果としてアプリケーションプログラム側で付け焼き刃な対応が必要になり、ぐちゃぐちゃなコードができあがる
- あくまで付け焼き刃なので、不具合の要因、遠因になる
グッドパターン例
- 外部キーを正しく貼る
CASCADE
定義もしましょう
5. EAV(エンティティ・アトリビュート・バリュー):柔軟に「列」を増やすために、Attribute とその Value を表す列を作成してしまっていないか?
あるテーブルで可変属性(レコードによってある/なしのある属性)を扱いたい場合の設計。例えば、教師(Teacher)と生徒(Student)を管理する User テーブルを作成することを考える。このとき、
- 教師は、名前、電話番号、メールアドレスを管理する
- 生徒は、名前、電話番号、生年月日を管理する
といったように、レコードのタイプによって、入力される列が異なる場合がある。
アンチパターン例
柔軟に仮想「列」を増やすために attr_name
と attr_value
という列を追加してしまうパターン。
User
user_id(PK) | attr_name(PK) | attr_value |
---|---|---|
1 | name | Alice |
1 | tel | 090-xxxx-xxxx |
1 | birthday | 2005/1/1 |
2 | name | Bob |
2 | bob@xxx.xxx | |
2 | telephone | 080-xxxx-xxxx |
パット見、柔軟に対応できそうに見えるが、
- 同じ要素でも、格納されている名前が異なる場合がある(例:
tel
とtelephone
) - 必須にできない
- 何が入るかわからないので適切な型も設定できない
など、負債になる原因をいくつも生み出してしまう。
グッドパターン例
継承の考え方を用いる。
- シングルテーブル継承
- 名前、電話番号、メールアドレス、生年月日の全てを列として定義する
- 不要な列(教師の生年月日など)は
NULL
とする
- 具象テーブル継承
- 教師テーブルと生徒テーブルを分ける
- クラステーブル継承
- 共通部分と個別部分に分けて考える
- 下記が一例
User
user_id(PK) | name | tel |
---|---|---|
1 | Alice | 090-xxxx-xxxx |
2 | Bob | 080-xxxx-xxxx |
Teacher
user_id(PK) | |
---|---|
2 | bob@xxx.xxx |
Student
user_id(PK) | birthday |
---|---|
1 | 2005/1/1 |
6. ポリモーフィック関連:複数の異なるテーブルに紐付く可能性があるテーブルを作成する場合に、xx_type などで紐付き先のテーブルを定義する設計になっていないか?
(いい例か微妙なのですが)例えば、所属している(た)クラスを管理するテーブル ClassInfo を作成することを考える。
アンチパターン例
ClassInfo のレコードが、教師と生徒のどちらに紐付くかは user_type
列で表すことにし、 user_id
には対応する (Teacher|Student)の主キーの値が入る「想定」という設計とする。
ClassInfo
id(PK) | year | class | user_type | user_id |
---|---|---|---|---|
1 | 2016 | 2-2 | teacher | 2 |
2 | 2017 | 1-1 | teacher | 2 |
3 | 2017 | 1-1 | student | 1 |
4 | 2018 | 3-1 | teacher | 2 |
5 | 2018 | 2-3 | student | 1 |
しかし、このパターンは、幾つかの問題をはらんでおり、
user_id
は Teacher テーブル、あるいは Student テーブルを外部キー参照したいができない。- テーブルを一意に決めることができないため
- 「想定」と書いたのはそういう理由から
- JOIN が難しい
user_id
を見て JOIN するテーブルを決定する形にするので、クエリが複雑になります
といった負債につながってしまう。
グッドパターン例
- 参照を逆にする
- Teacher や Student から ClassInfo を参照する形にする。
- 交差テーブルを用意し、Teacher - ClassInfo 間、Student - ClassInfo の多対多を表現する。
- 交差テーブルにおいて ClassInfo 側の主キーに UNIQUE 制約を貼ることで、同一の ClassInfo が複数の(Teacher|Student)に紐付かないようにする。
- ただし、この対応をしても、ひとつの ClassInfo がある Teacher とある Student に同時に紐付いてしまう可能性はある。
- これらのテーブルを一つのテーブルとして見たい場合は
UNION
あるいはCOALESCE
関数を用いる。 - のように Teacher テーブルと Student テーブルの基底クラスを作れる場合は、【基底クラスに対して】外部参照するようにする。
7. マルチカラムアトリビュート(複数列属性):1対Nで保持したいデータがある場合に、列を row1、row2、、、rowN のように増殖させる設計にしていないか?
生徒が興味を持っている分野(宇宙、芸術、などなど)を登録できるように Student テーブルを拡張することを考える。
アンチパターン例
エイヤで一定数の列を追加してしまうパターン。
Student
student_id(PK) | Name | fav1 | fav2 | fav3 |
---|---|---|---|---|
1 | Alice | Design | NULL | NULL |
2 | Bob | NULL | NULL | NULL |
3 | Charlie | Dance | Design | Art |
イケてない理由として、
Design
に興味がある生徒 を探すクエリが複雑になります。- UPDATE, DELETE も難解です。
- fav1 と fav2 に同じ値が入ってしまう可能性があります。
- そして何より3つまでしか、興味のある分野を登録できません。
などが挙げられる。
グッドパターン例
下記のように従属テーブルを作成するのが、クエリも簡単に書け、拡張性も高い設計になる。
Student
student_id(PK) | Name |
---|---|
1 | Alice |
2 | Bob |
3 | Charlie |
Favorite
student_id(PK) | fav(PK) |
---|---|
1 | Design |
3 | Dance |
3 | Design |
3 | Art |
8. メタデータトリプル(メタデータ大増殖):時間経過によってテーブルや列を増やすことを前提とした設計になっていないか?
学校において「購入した備品を年ごとに効率的に参照したい」という要件があったとする。
アンチパターン例
要件を満たすために、【年ごとに】購入した備品を格納するテーブルを作る(例:Equipment_2017)としてしまうのは、様々な問題に繋がる。
- 年が変わるたびにテーブルを増やし、アプリケーションに修正を加える必要が出てくる。
- 誤った年のテーブルにレコードが追加された場合に、それを移す際に一手間かかる。
- 異なるテーブル間で UNIQUE 性を担保するのが難しく、同じ備品が複数のテーブルに挿入されることをDB側では防ぐことができない。
(同様の問題は「テーブル」ではなく「列」でも発生する。)
グッドパターン例
- 参照性能を高めることだけ考えるのであれば、同一のテーブルで管理しつつも、年を表す列でパーティショニングする方法を用いる。
- また、保守性を向上させるには
student_id
とyear
が主キーの従属テーブルを新たに定義する。
まとめ
この記事では、「SQLアンチパターン」の論理設計についてまとめました。
第2弾「DB物理設計」編はこちらです。
第3弾「SQL クエリ」編はこちらです。
第4弾「アプリケーション設計」編はこちらです。
また、著者の方のお焚きあげ(今となってはこのパターンは少し違うよね)情報もあるそうなので、こちらについても最後にまとめられれば、と考えています。
この記事は「私の理解はこうです」という内容になっています。気になる項目が見つかった方は、ぜひ本を読んでみてください。とても勉強になる良書です。集中して読めば5時間程度で読み終わる内容だと思います。
- 作者: Bill Karwin,和田卓人,和田省二,児島修
- 出版社/メーカー: オライリージャパン
- 発売日: 2013/01/26
- メディア: 大型本
- 購入: 9人 クリック: 698回
- この商品を含むブログ (46件) を見る