「達人から学ぶDB設計徹底指南書」を読んでインデックスと正規化について学んだ
以前、id:sairoutine さんからおすすめして頂いた「達人から学ぶDB設計徹底指南書」を読んでインデックスと正規化について学びました。
DB設計に関する基本的なテクニックの中でも、インデックスと正規化についてはわかった気になったまま全くわかってない状態が長く続いていたので、今日こそはちゃんと理解したいという気持ちでこのブログ記事を書いています。
一旦、雑に自分の印象をまとめて、まずい表現があったら直します。間違っているところは指摘してもらえると助かります。
正規化
RDBのテーブルをきれいにしていくこと。その段階によって第２正規形だったり第３正規形だったり名前がついています。テーブルをきれいにするとデータの不整合が起きにくくなります。
正規形には第１正規形から第５正規形まであり、第３と第４の間にボイスーコッド正規形という正規形もあります。本書では通常の業務なら第３正規形までの理解で事足りると書かれているので、ここでは第3正規形までをじっくり見ていきます。その先とか、より深い理解なんかは、別の本を読んだときに改めて書こうかと思っています。
また、本書そのままではブログ記事にする意味がないので、ここでは自分なりの例を作りながら理解していきます。
第１正規形
その内容を見ると、第１正規形の原則は、それほど意識しなくても自然に満たせるものなのではないかと感じます。一方で、それを知らずに犯してしまう可能性もないわけではありません。
スカラ値（単一の値）のみにする
テーブルのある行のある列（本書では表計算ソフトで馴染み深い「セル」という単語が使われている）を取り出してみた時、それは単一の値であるべき。
そんなことは考えなくても分かりそうなものだけど、自分の場合はまんまとこの原則からはみ出たことをしたことがあります。ブログ記事にタグ付け機能を実装しようとなったときに、こんな設計にしてしまったのです。
Post
| id | title | body | tags |
|---|---|---|---|
| 1 | 釣り | くじらが釣れたので、その場で捌いて食べた。 | 釣り,レジャー |
タグ付けという機能、そしてtagsという複数形の名前を使っていることから、tagsの中身として期待しているデータは、明らかにスカラ値ではありません。そのため、これは第１正規形ではない。
実はブログとタグという関係をテーブルに落としこむのは、正規形に触れたことがないとなかなか難しいようです。これについてはエントリの最後の方もしくは別エントリで第４正規系以降を紹介した時に改めて触れたいと思います。
関数従属性
{X} -> {Y}
「XによってYが得られる」というのを「YはXに従属している」と言って、上のように表現するようです。従属なんて日常的に使わないので、初めて見たときはやたら難しく感じました。
たとえば47都道府県に番号を与えたとき、以下のように番号から都道府県名が得られます。
- 1 -> "北海道"
- 2 -> "青森"
- 3 -> "宮城"
この例で左がid、右が都道府県名という見出しだとすると、「都道府県名はidに従属している」と言えます。もしもidにあたる部分が重複だらけだと、お目当てのデータを引っ張ってこれないことになってしまいます。
idを持っていて従属した値を引っ張ってこれるというのは当然のことのような気がしますが、リレーショナルデータベースではこの性質を関数従属性と呼び、やたら意識することになります。
第２正規形
部分関数従属
第２正規形のキーワードは部分関数従属です。部分関数従属があるテーブルは第２正規形ではありません。
では、部分関数従属とはなんでしょうか。本ではこう定義されていました。
主キーの一部の列に対して従属する列がある場合、この関係を部分関数従属と呼びます。
うーん。この一文だけ引用してもよくわからないので、良い例を示したいですね。
Player
| no | team_id | name | team_name |
|---|---|---|---|
| 1 | 1 | 小早川瀬那 | 泥門 |
| 1 | 2 | 雷門太郎 | 泥門 |
| 2 | 1 | 進清十朗 | 王城 |
| 2 | 2 | 桜庭くん | 王城 |
(選手名、高校名はアイシールド21より）
あまり良い例が思いつかないのですが、スポーツの大会における選手のテーブルを例に見ていきます。
このテーブルだと、選手番号(no)がチーム内での番号になってるので、チームidと合わせて複合キーとなっています。ここでの選手番号は試合開始前の整列順くらいに考えてください。普通こういう使い方することないと思うんですけど、第２正規化前のダメな設計例なのでとりあえずこれでいきます。
この例で何を示したいかというと、複合キーを持ったテーブルで片方の列にしか従属してない列があったら「おいおいそれ部分関数従属だよ」とツッコミを入れることができるということです。この例だと、選手名は複合キーに従属してるけど、チーム名はチーム番号に従属しています。この team_id -> team_name という従属が部分関数従属です。
常識的に見ても違う実体が同じテーブル内にあるような形になるので、じゃあテーブル分けようという話になり、以下のように正規化されると思います。
Player
| no | team_id | name |
|---|---|---|
| 1 | 1 | 小早川瀬那 |
| 1 | 2 | 雷門太郎 |
| 2 | 1 | 進清十朗 |
| 2 | 2 | 桜庭くん |
Team
| id | name |
|---|---|
| 1 | 泥門 |
| 2 | 王城 |
例がちょっと強引なので、「なんで複合キーにしてるんだ。選手にもid与えろよ」という不満は残っています。
しかし何はともあれ、こうして全ての非キーがキーに従属することになりました。この状態を完全関数従属 と呼ぶそうです。
余談
アイシールド21で好きな選手は雪光です。
第３正規系
ようやく第３正規系です。キーワードは推移関数従属性です。
再びブログ記事のテーブルを例に考えてみます。
Post
| id | title | body | user_id | user_name |
|---|---|---|---|---|
| 1 | 金がない | ここ１週間はチキンラーメンのおやつサイズしか食ってない | 1 | gaaamii |
どの列に入るのもスカラ値なので、第１正規系は満たしているようです。キーが一つ(id)で、非キーはidに従属してるため、第２正規系の条件も満たしています。
しかし、このテーブルは第３正規系ではありません。推移関数従属性があるからです。
id -> user_id -> user_name
「user_name は user_id に従属していて、その user_id は id に従属している」
という関係が推移関数従属性です。なので、「推移関数従属性残ってんじゃん。テーブル分けようよ」と言えます。分けるとこんな感じになります。
Post
| id | title | body | user_id |
|---|---|---|---|
| 1 | 金がない | ここ１週間はチキンラーメンのおやつサイズしか食ってない | 1 |
User
| id | name |
|---|---|
| 1 | gaaamii |
なぜテーブルを分けるの？
なぜテーブルを分ける必要があるのでしょうか。正規化にはどういったメリットがあるのでしょうか。本書にはこう書かれています。
正規化とは更新時の不都合/不整合を排除するために行う
本書はこの点にしっかり配慮して書かれていました。正規化の各段階において、「更新時の不都合/不整合」にあたる部分を述べてから説明に入っています。
たとえばブログの例のように、キーとなる列をひとつのテーブルに入れ込んでしまうと、登録するときにそのテーブルの他の値が必要になり、ユーザーを単体で登録することができません。こういうのを１つずつ理解していると、正規化の際に迷いがなくなるのかもれません。
インデックス
本書を読む前は「インデックスをはる」ということがなんとなくパフォーマンスよくなるんでしょ的な認識でした。本書を読んでみて最低限のことはわかった気がするので、これまたざっくりまとめておきます。
キーとそれに結びつく情報を保持する
たしか初めてインデックスという言葉を見たのは Rails Tutorial を進めていて以下のコマンドを叩いた時です。
rails generate migration add_index_to_users_email
実はチュートリアルのコラムに簡潔でわかりやすい説明が載っていたんですが、当時はちゃんと読んでいませんでした。
残念なことに、(インデックスなどの機能を持たない) 素朴なデータモデルにおいてユーザーをメールアドレスで検索するには、データベースのひとりひとりのユーザーの行を端から順に読み出し、そのemail属性と与えられたメールアドレスを比較するという非効率的な方法しかありません。これは、データベースの世界では全表スキャンとして知られており、数千のユーザーがいる実際のサイトでは極めて不都合です。 emailカラムにインデックスを追加することで、この問題を解決することができます。
使うだけであればだいたいこの説明を理解すれば事足りるような気がします。 メールアドレスでユーザーを検索することが多いなら、毎回関係のない列をスキャンしているのはよくないよねという話です。
全表スキャンの問題を、テーブルとは別にキーとそれに結びつく情報だけ保持するオブジェクトを作っとくことで解決するのがインデックス、という感じでしょうか。テーブルのスキーマを変更するわけではないので、アプリケーションのコードを直したり設計を考え直したりすることもなく使えます。
カーディナリティ
ただ、「じゃあぜんぶの列にインデックス追加しようぜ」とはならないようです。それを考える上で大事な概念がカーディナリティ。本書ではこう書かれています。
これは、特定の列の値が、どのぐらいの種類の多さを持つか、ということを表す概念です。
性別（男性・女性・不詳）よりも口座番号（同一の銀行内でかぶることはない）の方がカーディナリティが高い、という例が示されています。
インデックスを追加するかどうかについては、以下の２つの注意点が挙げられています。
- カーディナリティが高い列に対してインデックスを追加するべき
- 値が平均的に分散しているのがベスト
そもそもなぜそうなるのかという疑問もありますけど、本書ではそこまで深く掘り下げられていないのでこれはまた別の機会にちゃんと理解を深めようと思います。
というわけで、正規化とインデックスについて本書の解説を咀嚼してきました。DB設計の入門はできたような気がします。今後はゴツい本に挑戦して、今回カバーしなかったボイスーコッド正規化以降やインデックスの仕組みなどについて学んでいきます。
間違ったことは、その都度書き直していきたいです。
