失敗から学ぶRDBの正しい歩き方 その1 / learn-from-failure-1

オープンセミナー香川の登壇資料です。

■イベント
- https://osk.connpass.com/event/116302/

■書籍のリンク
- https://amzn.to/2ty79Ne
- https://amzn.to/2TbotGl

■資料内の参考URL
- https://soudai.hatenablog.com/entry/2019/01/15/014228
- https://soudai.hatenablog.com/entry/2018/08/31/222107
- https://builderscon.io/tokyo/2018/session/ddba9bd5-819e-489e-9123-04d2291d506e
- https://builderscon.io/tokyo/2017/session/c8f36693-32aa-4bf1-816a-4966f3859926

Transcript

1. 失敗から学ぶRDBの正しい歩き方 オープンセミナー2018@香川
2. What is it? みなさん失敗したことありますか？
3. What is it? みなさん失敗したことありますか？ ↓ 私は沢山あります
4. What is it? あの時、どうすればよかったか…
5. What is it? “愚者は経験に学び、賢者は歴史に学ぶ。” --オットー・ビスマルク
6. What is it? 他人の失敗を知ることで 自分の失敗を未然に防ぐことが出来る
7. 本を書きました https://www.amazon.co.jp/exec/obidos/ASIN/4297104083/maple036-22/
8. What is it? RDBMSの失敗例集
9. What is it? 今日はこの本の中から 厳選された内容をお伝えします
10. あじぇんだ 1 自己紹介 2 失われた事実 3 簡単すぎる不整合 4 隠された状態 5 まとめ
11. あじぇんだ 1 自己紹介 2 失われた事実 3 簡単すぎる不整合 4 隠された状態 5 まとめ
12. 自己紹介 曽根 壮大（34歳) 株式会社オミカレ 副社長/CTO • 日本PostgreSQLユーザ会 勉強会分科会 座長 • 3人の子供がいます • 技術的にはWeb/LL言語/RDBが好きです そ ね た け と も
13. 自己紹介 曽根 壮大（34歳) 株式会社オミカレ 副社長/CTO • 日本PostgreSQLユーザ会 勉強会分科会 座長 • 3人の子供がいます • 技術的にはWeb/LL言語/RDBが好きです そ ね た け と も
14. 婚活といえばオミカレ https://party-calendar.net/
15. あじぇんだ 1 自己紹介 2 失われた事実 3 簡単すぎる不整合 4 隠された状態 5 まとめ
17. 失われた事実 RDBは、“時間軸と直行するような設計”が大切です。 ですがそれを使ったサービスとしては、 時間軸と直行しないデータ＝履歴を保存することが同じくらい 重要です。履歴の保存を怠ると、 • このデータがどのようにして今の値になったかわからない • ある日を境に売上データと商品マスタの単価データが合わない • 払い戻しの処理が特別対応となる このようなケースと戦うことになります -- 失敗から学ぶRDBの正しい歩き方 – 第2章 失われた事実
18. 失われた事実 失われた事実の例
19. 売上id 売上金額 売上日 配送状態 1 29,522 2014-03-31 23:59:59 配送済み 2 6,480 2014-04-01 00:00:00 発注中 ︙ ︙ ︙ ︙ 名前 値 消費税率 0.05 売上id 商品id 個数 購入者 1 1 3 sone 1 2 3 sone 1 3 3 sone 2 4 3 sone ︙ ︙ ︙ ︙ 商品id 商品名 価格 1 SQL実践入門 2,580 2 リーダブルコード 2,592 3 プログラマのためのSQL 4,200 4 データベースリファクタリング 3,000 日付が変わるタイミングで消費税率 を5%から8%に変えることで対応 消費税率*商品.価格*カート.個数=売上金額 カート 売上 設定マスタ 商品
20. 失われた事実 4/3に返品があったら？
21. 失われた事実 4/3に返品があったら？ ↓ 該当の商品の個数を減らして、 再計算を実施して売上を更新する
22. 売上id 売上金額 売上日 配送状態 1 27,579 2014-03-31 23:59:59 配送済み 2 6,480 2014-04-01 00:00:00 発注中 ︙ ︙ ︙ ︙ 名前 値 消費税率 0.08 売上id 商品id 個数 購入者 1 1 2 sone 1 2 3 sone 1 3 3 sone 2 4 3 sone ︙ ︙ ︙ ︙ 商品id 商品名 価格 1 SQL実践入門 2,580 2 リーダブルコード 2,592 3 プログラマのためのSQL 4,200 4 データベースリファクタリング 3,000 商品id=1の個数を3から2へ カート 売上 設定マスタ 商品 再計算
23. 失われた事実 サポート担当者 「数日前にsoneさんという方が購入された 『SQL実践入門』、1冊だけ返品処理したら 売上がズレるんですけど。」
24. 売上id 売上金額 売上日 配送状態 1 27,579 2014-03-31 23:59:59 配送済み 2 6,480 2014-04-01 00:00:00 発注中 ︙ ︙ ︙ ︙ 名前 値 消費税率 0.08 売上id 商品id 個数 購入者 1 1 2 sone 1 2 3 sone 1 3 3 sone 2 4 3 sone ︙ ︙ ︙ ︙ 商品id 商品名 価格 1 SQL実践入門 2,580 2 リーダブルコード 2,592 3 プログラマのためのSQL 4,200 4 データベースリファクタリング 3,000 カート 売上 設定マスタ 商品 売上id=1の返品処理をする場合、 カートの値のみを変更して再計算 すると、本来有るべき値から誤差 が生まれる
25. 失われた事実 • 最初の計算 (2,580 × 3 + 2,592 × 3 + 4,200 × 3) × 1.05 = 29,522円 • 返品で個数が、日付で消費税率が変わる (2,580 × 2 + 2,592 × 3 + 4,200 × 3) × 1.08 = 27,579円 • 本来あるべき計算 (2,580 × 2 + 2,592 × 3 + 4,200 × 3) × 1.05 = 26,813円
26. 失われた事実 • 商品名が変わったら？ • 商品の価格が変わったら？ • 購入日と支払日が違ったら？ • 配送状況の過程は？ …etc
27. 商品id 商品名 価格 1 SQL実践入門 2,580 2 リーダブルコード 2,592 3 プログラマのためのSQL 4,200 過去の事実と不整合が生まれてしまう 商品id 商品名 価格 1 SQL実践入門 第二版 2,480 2 リーダブルコード 2,592 3 プログラマのためのSQL 4,200 過去の事実(値)が失われる
28. 過去の事実(過程)が失われる 配送状況の履歴が無いため 下記の区別がつかない 1. 発注済 → キャンセル → 再発注 → 配送済 2. 発注済 → 配送済み
29. 失われた事実 過去の事実は非常時に必要になる
30. アンチパターンを防ぐには？ 履歴を保存する
31. アンチパターンを防ぐには？ 消費税率 有効日 失効日 0.05 1997-04-01 2014-03-31 0.08 2014-04-01 null 消費税率に履歴を持たせる ・消費税率テーブルを新規に作る ・有効期限をもたせることで、 売上日から消費税率を遡ることができる ・自動切り替えにも対応出来る
32. アンチパターンを防ぐには？ 購入時の消費税率の履歴を持たせる 売上id 売上金額 消費税率 売上日 配送状態 1 29,522 0.05 2014-03-31 23:59:59 配送済み 2 6,480 0.08 2014-04-01 00:00:00 発注中 ︙ ︙ ︙ ︙ ︙ ・売上テーブルに購入時の消費税率を持たせる ・消費税率テーブルがない場合、 この列が無いと返品処理が出来ない
33. アンチパターンのポイント 履歴の保存はトレードオフ
34. アンチパターンのポイント 履歴の保存はトレードオフ ↓ パフォーマンスが落ちる データの保存とパフォーマンスのトレードオフ
35. アンチパターンのポイント 後からデータを遡りたいときに 事実が失われている設計をしてしまう
36. アンチパターンのポイント 知らない事実を読み解くことは出来ない
37. アンチパターンのポイント 知らない事実を読み解くことは出来ない ↓ 失った事実は取り戻せない
38. アンチパターンのポイント 初期設計から事実の履歴については しっかりと検討すること
39. アンチパターンのポイント その他の対策方法 1. 最新のレコードを有効なレコードとする 2. 更新、削除もINSERTして、集計する (イベントソーシング) 詳しくは書籍で！
40. あじぇんだ 1 自己紹介 2 失われた事実 3 簡単すぎる不整合 4 隠された状態 5 まとめ
42. 簡単すぎる不整合 本書では度々データを守る重要性を説いていますが、 逆に最も簡単にデータを壊す方法は非正規化を行うことです。 しかしながら、パフォーマンスの問題などで 非正規化を利用したくなるケースは実際にあります。 非正規化は劇薬であるということを理解したうえで利用する のであればいいのですが、最初から検討するのは間違いです。 -- 失敗から学ぶRDBの正しい歩き方 – 第15章 簡単すぎる不整合
43. 簡単すぎる不整合 非正規化したいタイミング
44. 簡単すぎる不整合 • テーブルを作って正規化をするのが面倒な時 • 外部キー制約によってデッドロックなどが発生してる時 • 正規化によってJOINのコストが高くなり、 パフォーマンスに問題が出ている時
45. 簡単すぎる不整合 • テーブルを作って正規化をするのが面倒な時 • 外部キー制約によってデッドロックなどが発生してる時 • 正規化によってJOINのコストが高くなり、 パフォーマンスに問題が出ている時 ダメ！絶対！！ 手間を惜しんで大きな技術的負債を産む
46. 簡単すぎる不整合 • テーブルを作って正規化をするのが面倒な時 • 外部キー制約によってデッドロックなどが発生してる時 • 正規化によってJOINのコストが高くなり、 パフォーマンスに問題が出ている時 適切に親テーブルにロックを取れば防げる 外部キー制約を外したり、非正規化するのはアンチパターン
47. 簡単すぎる不整合 • テーブルを作って正規化をするのが面倒な時 • 外部キー制約によってデッドロックなどが発生してる時 • 正規化によってJOINのコストが高くなり、 パフォーマンスに問題が出ている時 ケース・バイ・ケースといえる難しい問題 しかし多くの場合、正規化したほうがパフォーマンスは有利
48. 非正規化の例 postgres=# SELECT * FROM reserve; id | party_di | 申込者 | 同伴者1 | 同伴者2 | 同伴者3 | 予約日 ----+----------+--------+---------+---------+---------+---------------------------- 1 | 1 | hoge | fuga | | | 2019-01-14 13:54:14.517217 2 | 2 | foo | bar | hoge1 | hoge2 | 2019-01-14 13:54:14.517217 3 | 1 | hoge2 | fuga | | | 2019-01-14 13:54:14.517217 4 | 1 | test | | test | | 2019-01-14 13:54:14.517217 (4 rows) -- 同伴者fugaを検索するクエリ postgres=# SELECT * FROM reserve WHERE ("同伴者1"='fuga' OR "同伴者2"='fuga' OR "同伴者3"='fuga’); id | party_di | 申込者 | 同伴者1 | 同伴者2 | 同伴者3 | 予約日 ----+----------+--------+---------+---------+---------+---------------------------- 1 | 1 | hoge | fuga | | | 2019-01-14 13:54:14.517217 3 | 1 | hoge2 | fuga | | | 2019-01-14 13:54:14.517217 (2 rows)
49. 非正規化の例 postgres=# SELECT * FROM reserve; id | party_di | 申込者 | 同伴者1 | 同伴者2 | 同伴者3 | 予約日 ----+----------+--------+---------+---------+---------+---------------------------- 1 | 1 | hoge | fuga | | | 2019-01-14 13:54:14.517217 2 | 2 | foo | bar | hoge1 | hoge2 | 2019-01-14 13:54:14.517217 3 | 1 | hoge2 | fuga | | | 2019-01-14 13:54:14.517217 4 | 1 | test | | test | | 2019-01-14 13:54:14.517217 (4 rows) -- 同伴者fugaを検索するクエリ postgres=# SELECT * FROM reserve WHERE ("同伴者1"='fuga' OR "同伴者2"='fuga' OR "同伴者3"='fuga’); id | party_di | 申込者 | 同伴者1 | 同伴者2 | 同伴者3 | 予約日 ----+----------+--------+---------+---------+---------+---------------------------- 1 | 1 | hoge | fuga | | | 2019-01-14 13:54:14.517217 3 | 1 | hoge2 | fuga | | | 2019-01-14 13:54:14.517217 (2 rows) 更新の場合など、バグなどでデータが壊 れることが容易に想像できる
50. 簡単すぎる不整合 データは壊れる
51. 簡単すぎる不整合 データは壊れる ↓ 非正規化はそれをより簡単にする
52. アンチパターンを防ぐには？ データの不整合と速度のトレードオフ
53. アンチパターンを防ぐには？ データの不整合と速度のトレードオフ ↓ 非正規化するくらいなら キャッシュを活用すべき
54. アンチパターンを防ぐには？ RDBMSの責務はデータを守ること 速度の課題はアーキテクチャで解決できる
55. アンチパターンを防ぐには？ キャッシュの例 1. マテリアライズド・ビュー 2. アプリケーションキャッシュ 3. NoSQLなど高速なデータストアの活用 …etc 書籍では解説しています 今日は時間の都合で割愛
56. アンチパターンを防ぐには？ キャッシュの活用は 参照整合性とのバランスで決まる
57. アンチパターンを防ぐには？ しかし多用は禁物 第16章で「キャッシュ中毒」があるので詳しくは書籍で
58. アンチパターンを防ぐには？ 非正規化がそれでも必要な時
59. アンチパターンを防ぐには？ 非正規化がそれでも必要な時 ↓ 制約を使う
60. 非正規化の例 postgres=# SELECT * FROM アンケート; id | 回答者 | 好きなデータベース | その他 ----+----------+--------------------+-------- 1 | soudai | PostgreSQL | 2 | sone | MySQL | 3 | taketomo | OracleDB | SQLite ←不整合 4 | test | SQL Server | 5 | hoge | その他 | Db2 id=3の「その他」列に不正な値 データが壊れるのは正規化出来ていない証拠
61. 正規化の例 postgres=# SELECT * FROM アンケート; id | 回答者 | 好きなデータベース ----+----------+------------------- 1 | soudai | PostgreSQL 2 | sone | MySQL 3 | taketomo | OracleDB 4 | test | SQL Server 5 | hoge | Db2 postgres=# SELECT * FROM データベースの種類; id | DBの種類 | 回答 ----+------------+------------------- 1 | PostgreSQL | PostgreSQL 2 | MySQL | MySQL 3 | OracleDB | OracleDB 4 | SQL Server | SQL Server 5 | DB2 | その他 6 | SQLite | その他 正規化の例 アンケートテーブルの好きなデータベース列は、 データベースの種類テーブルを親とした 外部キー制約を作る
62. アンチパターンを防ぐには？ 非正規化したまま CHECK制約を使ってデータを守る
63. CHECK制約の活用例 -- 想定したデータの登録 postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース", "その他") VALUES (1, 'soudai', 'PostgreSQL', ''),(2, 'sone', 'MySQL', ''); INSERT 0 2 -- 正しくその他を登録する postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース", "その他") VALUES (3, 'taketomo', 'その他', 'Db2'); INSERT 0 1 postgres=# SELECT * FROM enquete; id | 回答者 | 好きなデータベース | その他 ----+----------+--------------------+-------- 1 | soudai | PostgreSQL | 2 | sone | MySQL | 3 | taketomo | その他 | Db2 (3 rows) 非正規化されたテーブル
64. CHECK制約の活用例 postgres=# CREATE TABLE enquete postgres-# ( postgres(# id serial NOT NULL , postgres(# "回答者" text NOT NULL, postgres(# "好きなデータベース" text NOT NULL, postgres(# "その他" text NOT NULL CHECK postgres(# (CASE postgres(# WHEN "好きなデータベース"!='その他' AND "その他"='' THEN TRUE postgres(# WHEN "好きなデータベース"='その他' AND "その他"!='' THEN TRUE postgres(# ELSE FALSE postgres(# END) postgres(# ); CREATE TABLE 「好きなデータベースの列」に”その他”が設定された時のみ、 「その他の列」にデータを投入できる
65. CHECK制約の活用例 -- 好きなデータベースがその他の時以外はエラーになる postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース", "その他") VALUES (3, 'taketomo', 'OracleDB', 'SQLite’); ERROR: new row for relation "enquete" violates check constraint "enquete_check" DETAIL: Failing row contains (3, taketomo, OracleDB, SQLite). -- 好きなデータベースがその他の時に空白でもエラーになる postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース", "その他") VALUES (3, 'taketomo', 'その他', ''); ERROR: new row for relation "enquete" violates check constraint "enquete_check" DETAIL: Failing row contains (3, taketomo, その他, ). CHECK制約で守られる
66. https://soudai.hatenablog.com/entry/2019/01/15/014228
67. アンチパターンを防ぐには？ CHECK制約で確実にデータを守りながら テーブルを一つ減らせる
68. アンチパターンを防ぐには？ 類似例にENUM型がある
69. アンチパターンを防ぐには？ しかしCASE式を使ったCHECK制約は メンテナンス性が低い
70. アンチパターンを防ぐには？ 正規化出来るなら正規化したほうが良い
72. Tips なんとMySQL 8.0.16から CHECK制約が来ます！
73. アンチパターンのポイント 正規化出来るなら正規化したほうが良い
74. アンチパターンのポイント 正規化出来るなら正規化したほうが良い ↓ RDBは正規化に始まり、正規化に終る
75. アンチパターンのポイント 非正規化の技術的負債の利子は重い
76. アンチパターンのポイント 非正規化の技術的負債の利子は重い ↓ 代替手段があるのでそれを念頭に置き 論理設計を行うことが大切
77. https://soudai.hatenablog.com/entry/2018/08/31/222107 正規化の話
78. https://builderscon.io/tokyo/2018/session/ddba9bd5-819e-489e-9123-04d2291d506e 動画もあるよ
79. 非正規化と履歴データ 非正規化と履歴データは違うの？
80. 非正規化と履歴データ 非正規化と履歴データは違うの？ ↓ 履歴データは非正規化に見えるが 非正規化ではない
81. 非正規化と履歴データ 別々の事実の積み重ねが履歴データ 非正規化は事実の重複
82. あじぇんだ 1 自己紹介 2 失われた事実 3 簡単すぎる不整合 4 隠された状態 5 まとめ
84. 隠された状態 RDBに状態を持たせるのはたいへん危険です。 第2章「失われた事実」や第5章「フラグの闇」でも、RDB に事実のみを保存することの大切さと難しさをお話しました。 なぜ複数の章に渡って強調してきたかというと、 隠された状態には多くの罠が潜んでいるからです。 本章ではその理由と、それによって引き起こされる問題に ついて説明します。 -- 失敗から学ぶRDBの正しい歩き方 – 第7章 簡単すぎる不整合
85. 隠された状態 データに状態を持たせることは危険
86. user_idを見てユーザを判断 if($this->is_admin($user->user_id)) { // 管理者用の処理へ } // user_id の先頭が 9 だった場合は管理者 function is_admin($user_id) { $role_id = mb_substr($user_id, 0, 1); return ($role_id == 9) ; } idに状態が隠れている
87. 閲覧のみユーザ用の処理を追加 $role_id = $this->get_role_id($user->user_id); if ($role_id == 9) { // 管理者用の処理へ } elseif ($role_id == 8) { // 閲覧のみユーザの処理へ } function get_role_id($user_id) { return $role_id = mb_substr($user_id, 0, 1); } 仕様追加の度に複雑に
88. 隠された状態 県番号(2桁) 部門ID(3桁) 支店ID(4桁) （例：330010012） よく見る例
89. 隠された状態 今の会員数と管理者の人数って何人？
90. 隠された状態 今の会員数と管理者の人数って何人？ ↓ 検索時にuser_idを分割するSQLが必要 複雑+INDEXも効かないので即答できない
91. 隠された状態 意味を含んだID データベースリファクタリングでは スマートカラム呼ばれるアンチパターン
92. 隠された状態 ソースコードやクエリからは user_idだけでは意味がわからない
93. 似たようなアンチパターン • EAV （エンティティ・アトリビュート・バリュー） • Polymorphic Associations （ポリモーフィック関連） SQLアンチパターン 本著でも説明している
94. まだ読んでないなら絶対読んだほうがいい 本著はSQLアンチパターンを読んでる前提の説明も多い
95. 隠された状態 コードやデータから見えない状態の辛さ
96. 隠された状態 コードやデータから見えない状態の辛さ ↓ 運用コストが激増する
97. アンチパターンを防ぐには？ 正規化をする ＋ 事実を保存する
98. アンチパターンを防ぐには？ 事実のみを保存する
99. アンチパターンを防ぐには？ IDとはidentification
100. アンチパターンを防ぐには？ IDとはidentification ↓ 識別できる一意の値 以上の意味を持たせてはいけない
101. 会員id 名前 900001 曽根 壮大 900002 曽根 徠楽 100003 曽根 煌楽 100004 曽根 朔楽 idから権限の責務をカラムに分割する 会員id 名前 権限 1 曽根 壮大 管理者 2 曽根 徠楽 管理者 3 曽根 煌楽 一般ユーザ 4 曽根 朔楽 一般ユーザ 意味を含んだIDの改善例
102. アンチパターンを防ぐには？ 正しく正規化をすること
103. アンチパターンのポイント • データに複数の意味を持たせない • 1つのデータの責務を小さくする • 常に状態が見えるようにするために 事実のみを保存する
104. アンチパターンのポイント 交差テーブルをちゃんと作る
105. アンチパターンのポイント 交差テーブルをちゃんと作る ↓ JOINのコストを最初から危惧しない 失われた事実と同じく、パフォーマンスの問題は 別の方法で対応する
106. アンチパターンのポイント 正規化出来るなら正規化したほうが良い めちゃくちゃ大切なことなので何度も言います
107. おまけ
108. おまけ
109. あじぇんだ 1 自己紹介 2 失われた事実 3 簡単すぎる不整合 4 隠された状態 5 まとめ
110. まとめ “愚者は経験に学び、賢者は歴史に学ぶ。” --オットー・ビスマルク
111. まとめ データベースの寿命は アプリケーションよりも長い
112. まとめ データは失うと取り戻せないし、 情報はデータが無いと作れない
113. まとめ 他人の失敗から学び、 正しくRDBを使いこなす
114. まとめ 今、あるデータベースと向き合うには？
115. まとめ 今、あるデータベースと向き合うには？ ↓ 自分たちで直すしかない
116. https://builderscon.io/tokyo/2017/session/c8f36693-32aa-4bf1-816a-4966f3859926
117. まとめ 諦めず、仕組みで解決していく
118. まとめ 諦めず、仕組みで解決していく ↓ 仕組みは技術力で解決できる
119. まとめ 数冊の本を読むだけで 充分な知識がつくのがRDB
120. まとめ 技術で問題を解決していく
121. まとめ 技術で問題を解決する ↓ 解決までの歩んだ轍が道になる
122. まとめ RDB THE Right Way
123. まとめ RDB THE Right Way ↓ あなたの道を歩くのはあなた自身
124. まとめ 自分自身で改善のサイクルを回していく
125. まとめ 技術で解決した問題の価値が エンジニアの価値
126. まとめ 自分自身で問題を解決していきましょう
127. ご清聴ありがとうございました