【第七章~第九章】失敗から学ぶRDBの正しい歩き方
7.1意味を含んだID(cf:論理ID,スマートカラム)
cf.
- 『男のコンピュータ道』(ブログ-> http://nippondanji.blogspot.com/2013/12/id.html)
7.2類似
「EAV」、「Polymorphic Associations」->『SQLアンチパターン』
- 「EAV」（エンティティ・アトリビュート・バリュー）
会員table
| id | name |
|---|---|
| 1 | neko |
会員情報テーブル
| 会員id | 属性名 | 値 |
|---|---|---|
| 1 | age | 24 |
| 1 | hobby | game |
| 1 | business | CRE |
このような二つのtableが設計されている場合、
- どのような属性・値の組み合わせのデータでも保存できる という利点がある
→
その属性名に対する値があるのか
その属性名があるのか
属性名と値の組み合わせは正しいのか
属性名の一覧
などは実際に取り出すまでわからない。
また、
必須属性やデータ型が指定できない
正規化されていないために外部キー制約を強制できない
(ex:属性名「都道府県」には「東京」と「東京都」が入る可能性がある。)
- 属性名を補う必要がある
メモ:
EAVの代価案になりうるjson型
Mysql5.7 PostgreSQL9.3以降ではjson型を保存できる という問題が生じる。 ->
- 汎用性を高めた結果、RDBが本来持っているメリット失っている状態といえる
- Polymorphic Associations(ポリモーフィック関連)
propertiesテーブル(子)
| id | name | adress | 参照先 |
|---|---|---|---|
| 1 | 株式会社neko | 東京都 | shop |
| 2 | tako | 東京都 | user |
shopテーブル(親)
| shop_id | properties_id | 従業員 |
|---|---|---|
| 1 | 1 | 2 |
Userテーブル(親)
| user_id | properties_id | 年齢 |
|---|---|---|
| 1 | 2 | 25 |
- 外部制約キー制約が使えないため、参照整合性は担保できない
7.3仕様変更の際に明らかになる隠された状態について
アプリ側では一見して状態を判断できず、仕様変更の際に手間取る
バグが起きて不正なデータが生まれた場合には整理が難しい
アプリ側で防げないため、DBが無防備(check項目などの制約が入れられない)
ドキュメントがない場合は、どのような法則があるのかをコードを追うしかなくなる
idの制約を変える場合には大規模なアプリ側の改修を入れる必要が出て来る
7.4RDBには事実のみを保存する->責務が複数ある場合は保存先を分けていく
-> 交差テーブルを用意するのが妥当
propertiesテーブル
| id | name | adress | 参照先 |
|---|---|---|---|
| 1 | 株式会社neko | 東京都 | shop |
| 2 | tako | 東京都 | user |
(properties->shop)中間テーブル
| shop_id | properties_id |
|---|---|
| 1 | 1 |
shopテーブル
| shop_id | 従業員 |
|---|---|
| 1 | 2 |
| 2 | 500 |
(properties->user)中間テーブル
| user_id | properties_id |
|---|---|
| 2 | 2 |
Userテーブル
| user_id | properties_id | 年齢 |
|---|---|---|
| 2 | 2 | 32 |
7.5アンチパターンのポイント
今回のアンチパターンの状態は隠された状態であることが問題だった
中間テーブルを用意することは作ることが億劫なため忌避する人も多いが、「外部キー制約、トランザクションのパフォーマンスの問題」などが顕在化して対策では遅く、元のテーブル設計の際に解決できることが多い(かつ良い)
アンチパターンを防ぐコツ
データに複数の意味を持たせない
1つのデータの責務を小さくする
常に状態が見れるように、事実のみを記載する
隠された状態が存在することは、アプリ側にとっても影響があるアンチパターンのため、問題として大きくなりやすい。
早めのリファクタリングを心がける。
メモ: 問題が顕在化してリファクタリングするやつに多い問題のように感じた。
現場で知っていること(設計時に気が付けるか)、(例示リファクタのようなきれいな案を提案)できるようになっていることが
鍵のように思えた。
- トリガー
隠された状態に近い機能が用意されておりそれがトリガーである。
デメリット
アプリ設計者や運用者から見えず、振る舞いが予想できない。(永続的に影響を与える場所でトリガーを使う->隠された状態同等の問題を発生させる)
メリット(使ってもいい場面)
パフォーマンス的メリット
アプリ側の実装が大幅に削減できる
既存のアプリの振る舞いを維持したまま、仕様を変更できる
メリットと隠された状態を生むデメリットがある場合は、採用を見送る。
8.1jsonデータ型のアンチパターン
- 全ての値をjsonデータ型で保存したこと
メモ: 「一番ミニマムな形からできるサービス展開をしたい時」(https://qiita.com/yuno_miyako/items/fad33456d9c32d8f4483)でのコメント欄で目立ったように感じたのが確か「nosqlを学ぶコストが高い」で同じような問題に感じる。 mysqlにも5.7.8以降(?)json型が加えられたそうだが、indexできないなどやはり難しいように思える。
8.2「なんでもjson」の危険性
データの整合性が取れない(EAVの代案でありながら、問題点がある)
必須属性の指定が難しい
データの中身を指定できない
ex>yyyy/mm/dd と yyyy-mm-ddは何もしないと取り込まれてしまう。(型を揃えてくれない。)
ex>"123456"(文字列)と 123456 は、型違いでエラーが出る。
->データ型で守られていたことが失われる
参照整合性制約を強制できない。上の「データの中身を指定できない」と似ているが、外部キー制約を使うことができない
都道府県というkeyに対して東京と東京都が入る可能性がある
8.3 RDBの責務を失わないためには
- json型は汎用性を高められる一方でデータを守るができなくなる
それらを踏まえたうえでjson型のメリットを見る
- jsonそのものに対応している
->「web apiの戻り値」「phpのcomposer(設定ファイルが.json)」の場合など
- スキーマレスに値を保存できる
->データ構造を無視して保存できること
(※RDBではできない設計ではあるが、この条件が必要な場合はRDBMS以外の選択肢も考える。
パフォーマンスやスケーリングに課題が残ってしまいがちなため。->無視できるデータサイズ場合は問題ない)
webapiの戻り値
twitter_account
| id | screen_name | token | account |
|---|---|---|---|
| 1 | akino | hoge | fallakiakino |
twitter_account_detail
| twitter_account_id | setteings | created_at |
|---|---|---|
| 1 | {protected:false,.......} | 2021-3-15 |
twitter側は予告なくapiの変更をする可能性があるため戻り値がerrorにならずに保存されている時も対応できるように
必要なデータは正規化し、その他も含まれるjsonは別テーブルに分けることでパフォーマンスの向上と、必要なデータが取れない際にerrorで気が付ける
メモ: webapiの返した値をログのようにjson型で持っておくことで、障害時などに見返せる利便性があるとういう理解でいいのだろうか?
- os情報
| host_id | host_name | ip | os | detail |
|---|---|---|---|---|
| 1 | host1 | 192.168.0.1 | CentOS | {...} |
補足
os情報にはディストリビューションによって特有のものがあり、detailカラムをjsonすることでディストリビューションごとの違いを吸収している
この設計にした理由
レコード数が少なく数千程度だった
一度保存したレコードに対してupdateをすることはほとんどない
取り出すときはjsonを丸ごと取得
8.4アンチパターンのポイント
EAVの代替案として出されることが多い(EAVの代替案として優秀である一方でEAVと同じような問題を解決できていない)
EAVからjsonデータ型に置き換えるときはどのような点に気を付けるべきか
->1つでも該当する場合はjsonデータ型を採用すべきではない
(jsonデータ型はRDBMSの機能と引き換えに柔軟性を与える切り札のように思うべきである)
アプリ側でループを回すことなく、テーブルの情報を取り出せる
- json->レコード
アプリ側から渡されたjsonをストアドなしに適切に扱うことができるようになる
なんとなくだけども2の方法は一度jsonを取得してキレイなテーブルをサクッと作りたい時には便利そうな気がする (1は使いどころがパフォーマンス向上なのでいまいちわかってない。)
9.1 制約に関して
ex: email を保存する型にdomainで生成した(email_address型)を使用した ->RFCに準拠してないアドレス(ex:test.@example.com)を弾いてしまった
リファクタリングの例 alter文を流して、email_address型からtext型に変更する。 (仮にこの場合のテーブルのindexがemail_addressに貼られていた場合、再構築が実施される。 -> ロックがかかり、長時間メンテナンスになってしまう恐れがある)
- cf 「早すぎる最適化は諸悪の根源である」 https://ja.wikipedia.org/wiki/UNIX%E5%93%B2%E5%AD%A6
9.2似たアンチパターン
外部キー制約(mysql)
- 子テーブルの更新すると、親テーブルの共有ロックを自動的にとる
- 対策 排他ロックを取る (排他ロックは正しく順番を待たせるため、パフォーマンスのボトルネックになる)
- cf>ギャップロック
- index値を持つ行と行の間にあるギャップ
- 先頭のindex値を持つ行の前のギャップ
- 末尾のindex値を持つ行の後のギャップ にもロックをかけることがある(アプリが正しく更新していない時に起きる。パフォーマンスが落ちる)
-
- メリット 表記の揺れを防ぐ
- デメリット データ型の変更にはalter文が必要 仕様変更に弱い 制約されいる文字列を知る方法が限られている。
状態を持つcheck制約
alter table emails add column update at timestamp with out time zone check(update >=now());
テストデータ作成時 テスト用のデータは常に「update_atが最新日付・時刻のもの」になってしまう。 そのため、update_at を利用した where 句のテストなどに使うテストデータを定期的に作り直す、 が難しくなる
論理バックアップからデータをリストアするとき 元々入っていたデータは更新日が突然過去の状態でdumpされる。 そのため、insertで失敗する。 バックアップからデータを戻す際には緊急事態で理由がわからず慌ててしまうため、弊害といえる。
9.3過剰に制約を掛けないためには
制約と規約
データは成長する生き物なので変化に対応する必要があり、製薬は枷になりやすい。 (規約は変化には強いのでアプリ側に持たせる)
制約の段階 |段階 | 説明| |-------------|------| |制約なし | 何でも自由にデータが入れられる状態 |弱い制約 | not null、unique制約、外部キー制約などデータ構造を守るための最低限の規約 |強い制約 | check制約、exclude制約などRDBMSの機能に依存するが、適切に使えばデータを的確に守ることができる 強すぎる制約 | 制約の内容が「システムの仕様」や「ビジネスルール」に基づいて記述されている状態
表の「強すぎる制約」になっていないかを今一度確認する必要がある。
- DBの設計にビジネスロジックやシステム仕様を混ぜると、アプリケーション改修時に影響が出る。
9.4 制約を「正しく扱う」とは
強すぎる制約はRDBMSの問題点であり、安易に避けると大きな問題を起こす。この制約のバランスについて日々考えていくことが必要
postgreSQLの遅延制約
postgreSQLには少しだけ楽をするための方法として、外部キーの制約を遅延して評価させる仕組みがある。
外部キーをチェックしない
set constraints all deffrred;
外部キーをチェックする
set constraints all immediate;
tabel作成時等に
- DEFERRABLE INITIALLY DEFERRED (-トランザクション開始時からDEFERRABLEモードになる)
- DEFERRABLE INITIALLY IMMEDIATE (-set constraintsを使用すると振る舞いを変更できる )
- NOT DEFERRABLE (-set コマンドの影響を受けない)
を宣言する必要がある(宣言しない場合はデフォルトで「NOT DEFERRABLE」になる)
* 遅延制約を使いたい場合は(1),(2)をあらかじめ指定しておく必要がある。
* mysql 外部キー制約のみ > set foreign_key_checks=0;
