達人に学ぶDB設計を読んだので簡単にメモ

感想

主に二つ。

正規化は大事！
オプティマイザすごいな〜

正規化

正規化に関しては何も考えずにやっていた節がある。
なぜ正規化をするのか？　＝＞　保守性や速度が上がるから
なぜ保守性や速度が上がるのか　＝＞　？？？
と、最も大事な部分で考えるのをやめていたところを例つきで丁寧に教えてくれる。

正規化にもメリット・デメリットあって必ずしも正義ではない。
ただ基本的には
正規化＝＞非正規化への変更は容易だが
非正規化＝＞　正規化への変更は難しいので迷ったら正規化した方が良さそう。

メリット
- 変更が容易になる
- アップデートの速度が上がる
デメリット
- 検索速度が落ちる
- *初めて知ったけど読み取り用のView作れるみたいなのでそっちで解消できそう

第1正規化

一つのセルに複数の値を登録しない。(配列型も含めて)
＝＞複数の値が入るとデータを一意に特定できないから。
ちなみにRDBMSを使っているなら、基本的に複数の値を入れれないからあまり考えなくて良い。
*データが一意に特定される制約を主キー制約という。
*主キー制約からデータが一意に定まることを関数従属ともいう。

第2正規化

部分関数従属（主キーが複数あるが、一つの主キーに対して関数従属になっている）を完全関数従属にすること。
簡単に言えばエンティティ（実体）ごとにテーブルを分けよう！
主キー制約を守れていないと更新でデータの整合性を担保できないので必須。

第3正規化

推移関数従属を解消する。
簡単に言えば一対多のテーブル同士を混ぜると危険！ちゃんと一対一対応で分離させよう。
ざっくりとは第2正規化と同じと感じた。

その他の正規化

基本的に第三正規化までで大丈夫！
それ以外に第3.5正規化や第4正規化。第5正規化がある。
4,5に関しては多対多のテーブルを中間テーブル使って分離してれば問題ないと思います。

オプティマイザ

SQLを発行したらどうやってデータを取るかをオプティマイザが考えてくれています。
感謝！

オプティマイザが正しい(＝速度が速い)判断をするには統計情報とインデックスが必要
統計情報に関しては夜間に勝手に更新しているらしいから特に考えなくて大丈夫！

インデックス

基本B-treeを知っていれば問題ない。
B-treeは平衡木のデータ構造を持つインデックス。
どの根から葉への高さが等しいからO(log(N))の爆速で検索できます。
*長期運用で木が崩れることがあるからその時は木を直してあげる必要あり。

どんなデータにインデックスを作るべきか

大規模テーブル（１万件以上）
- O(log(N))だから小規模にはあまり効果が出ない。
カーディナリティ（カラムに含まれるデータの種類）が多い
- インデックスによって5%ほどのデータに絞れるのが理想
- 範囲が広すぎるとインデックスの意味があまりない。

*インデックスで演算を行わない => 演算後のデータはインデックスに存在しないから。
*データ型合わせる　例'10'と10

意外なグレーノウハウ

代理キー(MySQLでいうID列)は使わない方が良い！？
主キーにできる自然キーがあるならそっちを使う。
~~市区町村データの主キーに代理キーを使ってしまった自戒を込めて~~
理由：意味を持たないものだから

ちなみにIDはオートインクリメントなのでロックしている。つまり大量アクセスでは遅くなる。

チューニング案

暗黙的にソートを実行しているクエリを見直す
重複削除(DISTINCT, UNION)
集約関数(COUNT, SUM, AVG, MAX, MIN)
ORDER BY

インデックスを適切に作る
NULLを入れない
IN => EXISTS
サブクエリを使わない