失敗から学ぶRDBの正しい歩き方
https://images-na.ssl-images-amazon.com/images/I/51B4ihagImL._SX349_BO1,204,203,200_.jpg
2019/3/6
正誤表
全然関係ないけど表紙の絵とこれがにてる
https://i0.wp.com/m.signalvnoise.com/wp-content/uploads/2015/11/1dWBPQGOykyq-Xsyylq1ziw.png?w=640&ssl=1 https://m.signalvnoise.com/reconsider/
1章
アンチパターンが生まれるとき
DB自体の制約でおこることもある
サービスをとめたくない=DBを止めたくない（オンラインでスキーマを変えられない）ので、予備のカラムを用意しておく
kadoyau.icon 話に聞くところによると、これはWeb以外のサービスでもやられているようだ
スキーマのリファクタリング（alter）
2章
履歴（時間軸と直行しないデータ）がないと困る
例
消費税率の払い戻し
発送履歴を1つのrowで行うと、過程が失われる
正常系のときには問題ないので気づきづらい
RDB以外での担保
実世界でのユースケースがいまいちよくわからないkadoyau.icon
例題に出ているのを遅延レプリケーションでカバーできるの？
バックアップは前提であるとして
タイムマシン的な使い方？
ログデータからいい感じのテーブルを作るのはRDBMSだけではできない
3章 やりすぎたJOIN
MySQLはFULL OUTER JOINをサポートしていないのでLEFTとRIGHTをUNION
JOINは重い
JOINのベン図による説明
JOINは増えると指数関数的に増加する
テーブルの組合せ爆発
kadoyau.icon でも重なりをしらべたいならAとBを調べて、それとCを調べればいいのではないか？
100行と100行のJOINなら10000行のテーブルスキャン相当
重いのを解消する仕組みが実装されている
uniqueなindexがあれば 100 + (100x1)行になる
アルゴリズム
https://youtu.be/59C8c7p_hII?t=56
kadoyau.icon Hash表を作成するメモリはどれだけ必要？
https://youtu.be/IFUB8iw46RI?t=66
外部表（ループの元になるテーブル）・内部表のソートして上から比較
ソートに使うindexが生成されていると高速化できる
ループなし
8.0.18 から使えるらしい
JOINは減らす
JOINするテーブルを小さくする
materialized view
クエリの結果のテーブルを作るのと一緒
再作成のときにテーブルの作り直しが不要で、共有ロックのリフレッシュのみでいい
JOINにおけるINDEX
prodとstagingで実行計画が違う。なぜ？
考え方は一緒なので本章ではindexといったらb-tree
B-tree indexの構造の説明
図だと1ブロックに20行ぐらい入っていることになっているが現実もそんな感じなのかな？kadoyau.icon
indexを使うとシーケンシャルに読むより早くなる
INDEXが効かないケース
1. 検索結果が多い・全体件数が少ない（クエリ特性・データ特性）
INDEXをつかうよりテーブルスキャンのほうが早いときはINDEXを使わない
1. 検索結果がテーブル全体の20%以上だとindexがきかない
実務的には全体の10%未満を指標にする
2. テーブルが小さいとindexがきかない
1000行程度のテーブルはテーブルスキャンが効率的
数万〜数十万recordが（index利用の？）目安
罠：ageカラムに対するindexはサービスの長期化でユーザ人口が変わるってindexがきかなくなったりする（20代が50%以上ならage BETWEEN 20 AND 29はきかない）
INDEXをはったときに、クエリの検索結果の多寡によってはきかないときがある（データ特性）
2. 条件にその列を使っていない
WHERE age * 10 > 100はすべてのageを計算する必要があるのでage * 10 > 100/10としないときかない
MySQLでも8からはいった
MySQLの
「検索結果が多い」のいいかえだとおもったが、INDEXを貼る意味が薄いという話のようだ
kadoyau.icon1はintegerだけど（cardinalityは高いけど）クエリによってきかない、というはなし。これは、ENUMみたいなcardinalityが低いときには貼る意味が薄い
cardinalityが低いと検索結果が多くなるというのは正しいが…
男/女/未回答のカラムがあったとしてもし女が90%だったら女での絞り込みはできない（他は割合が低いのでできる）
4. あいまい検索
ふつう前方一致しかINDEXは利用されない
後方一致がしたいときは
対象の列をひっくり返して別の列に保存
部分一致がしたいときは
全文検索INDEXを使う（個別実装依存）
5. 統計情報と実際が乖離してるケース
optimizerは統計情報をみてindexの利用を決める
統計情報は定期的にデータをサンプリングして行われる
このため、以下のケースでは実行計画が変化する
1. サンプリング前に大きくデータが変わった
2. サンプリングで偏ったデータを収集した
基本optimizerに任せるのが良い結果になることが多い
例外的に情報更新で実行計画を変えないように固定する方法もある
MySQL インデックスヒントの構文
PostgreSQL pg_hint_plan, pg_dbms_stats
kadoyau.icon これを使いたい状況は？
経験則
5章 フラグの闇
"とりあえず削除フラグ"パターン
問題が小さいうちは十分早いので気づかないが、問題が顕在化すると手がつけられない厄介な問題
関連
論理削除のためのフラグのカラムを持つ
問題
対策
テーブルを分ける
トリガーで別テーブルに削除済テーブルに移動
viewを使って複雑なクエリを何度も書くのを回避
当然、高速化はできない
削除フラグにindexはりたいならパーティションを切ったほうがいいのでは？
リスト5.6
送信中はずっとロックするようなやつ
6章 ソートの依存
重複なし
nullなし
順序なし
実際のデータはこれではカバーできないことがあるのでRDBMSはnullを許容したりしている。これは「relational modelの外」
SQLの実行順序
indexどっち狙うか
ORDER BYを利用したクエリがメモリで処理できなくなると急に重くなる
メモリ状ならクイックソート
メモリか溢れたら、ソート結果をファイルに書き出すファイルアクセスが発生する（外部ソート）
Sorded Setを使うと
高速にノート済データを検索/取得できる
ORDER BYがINDEXから高速にデータを取得できるのと似てる
7章 隠された状態
JSONにもデメリットはある（8章で解説）
子テーブルが複数の親テーブルを持つ
JOINする対象のテーブルが取り出してみないとわからない
トリガーも隠された状態なので、大きなメリットがある場合に使う パフォーマンスの大幅UP
アプリの工数が大幅に減る
複数のアプリから参照されるDBのリファクタリング
8章 JSONの甘い罠
EAV
JSON型の欠点
ORMがサポートしていない
TEXT型にJSONぶっこむとかある
データの整合性が保てない
必須属性の指定がむずい（既知の情報しかCHECKできない）
データ型のサポートがない
外部キー制約が使えない
JSON型の利点
JSONが使えるので次のようなとき便利
Web APIそのまま入れる（生データ入れる）
設定ファイルをJSONにしてそれを入れる
こんなときは使わないほうがいい
検索したい
特定の値を更新したい
制約したい
9章 強すぎる制約
RDBMSをつかうのはACIDを担保したいからだがやりすぎは困る ACCESS SHAREDとも競合する
FOREIGN KEYは子テーブル更新で親テーブルの共有ロックを自動的に取る
ただし正しく順番をもたせるので、パフォーマンスのボトルネックになる
MySQL固有：ギャップロックがあるので、アプリケーションが正しく更新していない場合にデッドロックが頻発する 正規化がちゃんとできていない
13章でギャップロックの説明が更にある
制約と規約
規約（この本の用語）
アプリケーションでデータを担保
変更が容易
バグとヒューマンエラーに弱い
制約
規約を補完する
データは成長する
遅延制約
強すぎる制約：ビジネスロジックが入っている
https://comiblo.com/wp-content/uploads/2017/08/5d90bf92492795ff8449b8d80dac9d01.jpg
殆どの制約は適切な正規化と弱い制約で十分設計できる
弱い制約：データ構造の必要最低限の制約
NOT NULL, UNIQUE, FORIEN KEY
10 ころんだ後のバックアップ
論理バックアップ
pros SQLとしてバックアップすると、DDLをGitHubで管理できる
cons
時間がかかる
データサイズが大きくないときに有効
バックアップ時点にしか戻せない（当然）
物理バックアップ
rsyncとかで物理ファイルまるごとバックアップ
pros
運用が楽
cons
停止が必要（無停止ツールもある）
互換性がない事が多い
バックアップ時点にしか戻せない
特定の日時にデータをリストアできる手法のこと
考慮すべきこと
年間停止時間/難易度がかわる
バックアップおすすめ資料
MySQL
yotayamasaki MySQLバックアップの基本
PosngreSQL
satock PostgreSQLバックアップ入門
https://youtu.be/MCgWUyKQ6YM?t=862
Q. レプリがあるからバックアップ不要では？
必要です
WHERE句をつけ忘れたUPDATE文をうってしまったら？
バグ・ヒューマンエラー
システムが壊れたら？
バックアップ（保存）の運用見直しタイミング
データベースが増えた
ミドルウェアのバージョンアップ
更新情報を持ったログを削除していない
リストアできないケース
手順しょうがない
システム設計した人がいない
チームで定期的に訓練していない
エラーログの種類
MySQLのエラーログ
PostgreSQLのエラーログ
エラーログの出力
設計
運用
いつ
だれが
どのDBに
監査
どこから（クライアントIP）
何をした（クエリ）
どうなった（エラーコード、エラーメッセージ）
通知
Slack
可視化
"XXというwarningがこの時間帯にn回起こっている"
対策
ログレベルLOGのエラーログを集計する
ディスクサイズを監視する
MySQLではlock_wait_timeoutはエラーログに出力されない
MySQLでは、mysqldとして正常ならエラーログに出ない
12 監視されないデータベース
モニタリングのシナリオ
ディスクI/Oに問題がある
RDBMSのテンポラリファイルができてる→ SQLの実行数は同じだけどレコードが増えてる→フルスキャンが問題！
モニタリングのツール
MySQL
PostgreSQL
13 知らないロック
MySQLは外部キー制約のロックがむずい
14
トランザクションとACID
並列にしながら一貫性を担保するにはどうすればいい？
https://gyazo.com/e390499efeae953763d415b4b3b6db0d
本文では
他のトランザクションがコミットした追加・削除が見えてしまう現象です
とCOMMITしたという前提の説明になっている
2022-03-29現在、正誤表には乗っていなかった
15 簡単すぎる不整合
非正規化が誘惑してくる
テーブル作るの面倒だな…
これはつくれという結論
外部キー制約でデッドロックが発生するな…外部キー制約をはずして非正規化するか
親テーブルに対して正しく共有ロックを取れ
→13, 14章
正規化でJOINが増えてパフォーマンスが増えるな…
重複がなくなるので正規化したほうが有利になるはず
非正規化したテーブルのデータ整合性はアプリケーションで担保するしかない
アプリケーションがバグったらデータが簡単に壊れる
キャッシュを活用しよう
厳密な生
それでも正規化できない場合
順序がある
一見非正規化だが非正規化でないデータ
16 キャッシュ中毒
キャッシュはRDBMSと相性はいい
アーキテクチャが複雑になる
データがおかしいの？キャッシュがおかしいの？
クエリキャッシュ
RDBMSが同じクエリなら同じ結果を返す
最新なのかわからない
頻繁に更新されるならパフォーマンスが落ちる
全く更新されないならデータストアやアプリにのせたほうがいい
基本使われていない
MySQL 8.0からは削除
発展途上
「マテリアライズド・ビューの多段化」は筆者が最も公開したRDBの設計の一つ
アプリケーションキャッシュ
キャッシュヒット率や更新頻度を推測する
17 複雑なクエリ
無知ゆえの豪腕
腐ったテーブルの腐ったクエリ：設計がまずいので、ちゃんとやってもクエリが良くない状態になってしまう
複雑なクエリが出てしまう
解決したい問題はなにか？
テーブル設計はまずくないか？
書きにくいクエリは設計に問題を抱えていることが多い
SQLの構文評価順序
FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT
この順序でパーツごとに分けて理解すると理解しやすい
18 ノーチェンジ・コンフィグ
正しくされていないコンフィグ設定がパフォーマンス・セキュリティに影響することがある
コンフィグを疑うのは難しい（知らないから）
多くの場合、RDBMSの再起動が必要
max_connectionやshared_buffersがデフォルトのまま
IaaC
RDBMSのメジャーバージョンが変わると同じコンフィグでも動かないことがよくある
Configのチューニングサポートツール
PgTune
たたき台として利用できる
DBaaSを使う
managed services
19 塩漬けのバージョン
「修正コストがかかる」という理由で運用が放棄される
わからん（アプリケーションの影響があるかも）
DB停止が嫌だ
ビジネスサイドにメリットがないと思われている
バージョンアップの実績を作って、小さなシステム停止はサービスに問題ないことを証明する
kadoyau.iconコールドスタート問題だ
積み重ねてバージョンアップは好影響を与えることを理解してもらう
新規案件では、最初から定期的なバージョンアップを考慮する
アップデートの意義
「セキュリティアップデートを行わずにRDBMSを使うことは、車検を受けていない車を運転するようなものです」
DBの寿命はアプリケーションより長いので
こまめにバージョンアップをするべき
一気に数バージョン上げる場合、難易度は圧倒的に上がる
いつかはバージョンアップをする必要がある
HW故障
致命的な脆弱性
マイナーバージョンアップは基本的にご簡易が変わらない
MySQL8は結構変わっているらしい
バージョンアップの流れ
1. バージョンアップ方法の決定
マイナーバージョンアップはかんたん
メジャーは色々方法がある（比較表あり）
ダンプ・リストア
専用ツール（RDBMSによってやり方が違う）
レプリケーション（RDBMS、バージョンによってやり方が違う）
アプリケーションからの二重書き込み
工数が増えるが、設計次第で一部書き込みも可能
2. コンフィグ確認
3. リハーサル
サービス復旧できずに死ぬパターンが有る
これに加えてバックアップもなかったらサービス終了もありうる
4. バージョンアップ作業
最悪旧データベースに戻せば復旧できるから恐れずバージョンアップする
20 フレームワーク依存症
STI
インピーダンスミスマッチ
ポリモーフィック関連
1フォーム1列
モデルがビューに依存
スロークエリがどこで発行されているのかgrepではわからない
Active Recordの間違った利用
table - model - view が1:1対応