【第一章~第三章】失敗から学ぶRDBの正しい歩き方
1-1アンチパターン
- 不適切な名前(テーブルの意味・カラムの意味が分からないなど)
- リレーションモデルに基づかない設計(pk,not null,チェック制約)
->保存されたデータがどれがどう正しいかが分からない (≒バグと仕様が分からない)
1.2アンチパターンを生まないために
命名ミスは初期に対応 ->技術負債になってしまうため
対応例
- 変更後の名前カラムを追加
- 変更前のものと変更後が同じになるようにトリガーで定義するなどをする
- サービス単位やモデル単位で参考や更新を追加したカラムに設定し直す
- 切り替え完了で動確が問題なければトリガーと古いカラムをdrop
cf:『データベースリファクタリング』
1.3わかりずらい設計や名前はDB破局の始まり
メモ * 対応例でカラムを追加の際にpostgresは位置での追加ができないため、間違ったカラム名(綴りミス)で pushしたものを結局修正せずに直さずスルーしたことを思い出した (ポスグレ様々な気持ちはあるが、ともかくそこは変わってほしい気持ちがある)
- 1.3の「わかりずらい設計や名前は~」に関して 一般的に一人で作っている際には、「わかりずらい」が分からなくなる傾向があると思っていて (作っている間は分かっているため)、問題だとは感じる。 (やはりDB設計などでレビューをもらっておくのが、正しいのかもしれない。)
2-1過去の履歴を持っていないテーブルは危険
ex)消費税5%->8%の切り替え対応
2-3対応について
- 対応について
- 消費税率に履歴を持たせる
購入時の消費税率を持たせる
別対応方法
- 配送状況テーブルの作成 ->配送状態をinsert し、最新日時のレコードを現在の状況として扱う ex 配送状態テーブル
| id | 配送状態 | 作成日時 |
|---|---|---|
| 1 | 発注 | 2020/12/22 |
| 1 | 発送 | 2020/12/23 |
| 1 | 納品 | 2021/1/7 |
| 1 | 返品 | 2021/1/8 |
など
- 「履歴の保存」について ->レコードの保存量が増えるため、テーブルが肥大化する。 ->主キーでの検索でなくなるため、肥大化した場合検索速度が劣化する
2.4「後からデータを遡りたい時に消えている設計」の危険性
- 敢えて履歴を保存しない設計を取るケース
->RDBの責務で履歴を持たない場合 * 遅延レプリケーションを使う * アプリケーションログとしてelasticsearchなどの分析ツールに保存する
cf 『理論から学ぶデータベース実践入門』
- 遅延レプリケーションについて ->一日遅れのスレーブDBを作るなどができる (マスタDB上で行った誤った作業から保護する & システムのデバック時の再現手法として使う) 行っていることはDBの複製なので物理的コストがかかる。
3.1アンチパターンの解説(1つのクエリにまとめた結果の多数のjoin)
3.2JOINの特性(問題点)
- ex)3つの重なりの場合 AとB/ BとC / AとC ->指数関数的に増加する。(joinの回数が増えると急激に重くなる)
このように、joinはSQLで重い処理に入る。
対応->indexを貼るなどで計算コストが減ることもある。
joinのアルゴリズムの特徴
NLJ ・内部表の結合キーの列に利用できるindexがある場合、 ループ数を省略できるため外部表が小さいほど高速になる
Hash Join ・「外部表が大きい場合、または内部表の対象件数が多い場合」と 「結合条件の検索がなく、テーブルのフルスキャンが必要な場合」ではNLJ より有利 ・Hash表を作成さえすれば結合は非常に高速だが、Hash表の作成と保存が できるだけの十分のメモリが必要
Sort Merge Join ・ソートに用いる索引が作成されていると高速化できる ・Hash Join と同様に表の大部分を結合する場合に有効 (Hash Joinと違い等値結合だけでなく不等号(<,>,<=,>=)を使った結合も可能) ・Indexが活用できる場合はHash Joinより早い場合がある。
※ * postgresは3種類のjoinをサポートしている * Mysql はNLJしかサポートしていない(内部に適切なindexを貼る必要がある)
3.3アンチパターンを生まないためには
- joinの特性を知り、生かす(SQL文の修正)。
- viewを作成する。 cf:マテリアルズド・ビュー->再作成の時テーブルの作り直しが不要(postgre)
3.4アンチパターンのポイント
- joinは必要最低限
- indexを適切に利用する
- joinするテーブルは小さくしてからjoinする
- 複雑なクエリになった場合はviewを利用する
