『失敗から学ぶRDBの正しい歩き方』を読んでいく（前編）

後編は こちら

概要

1. データベースの迷宮

事前知識

• アンチパターンの事前知識としての、RDBにおける制約
  • PRIMARY KEY制約
    • 重複とNULLがなく、そのテーブルで一意な行であることを確定させる
  • NOT NULL制約
    • NULLがないことを確定させる
  • UNIQUE制約
    • その値がテーブルで一意であることを確定させる（NULLは許容される）
  • CHECK制約
    • 指定した条件の値のみが保存されていることを確定させる
  • DEFAULT制約
    • 値が指定されない時に保存される値を決める。それにより初期値を確定させる。
  • FOREIGN KEY制約（外部キー制約）
    • 別テーブルの主キーと参照整合性が保たれていることを確定させる

アンチパターン

• わかりづらい設計や名前はデータベースの破綻の始まり

まとめ

• テーブルやレコードの中身がわかる適切な名前をつける
• 外部キー制約やCHECK制約を利用してデータを適切に防ぐ
  • MySQLにおいては8.0.16以降からCHECK制約に対応。
• リレーショナルモデルに基づいた設計を心がける
• 何らかの理由で課題の残る設計をした場合、早めに改修する
  • カラム名の変更などでは、RDBのINSERTやUPDATEのactionに対するトリガーなどを利用して少しずつ切り替えていく。

2. 失われた事実

アンチパターン

• 今ある事実のみを保存してしまうと過去の事実を失ってしまうので、例外処理を行うときやトラブル時に状況把握する場合に、情報が不足する。
  • 消費税率がからむEC系、管理画面の作業ログなど

まとめ

• 「打ち消しのINSERT」として保存する。
• 払い戻しなどの取り消し処理に対応できるかに気を付ける。
• 配送状況などステータス変化を終えるかに気を付ける。
• トラブル対応時、欲しい情報が失われていないかに気を付ける。
  別手段として、
• 遅延レプリケーションを使う。
• アプリケーションログとしてElasticsearchなどの分析ツールに保存する。

RDBへの履歴の保存のデメリット

• レコードの保存量が増えるためテーブルサイズが増える
• 集計が単純な主キー検索ではなくなるため、テーブルサイズが肥大化した際に検索速度が劣化する

遅延レプリケーションについて

• 指定した時間分、スレーブDBに対して、マスタDBからのレプリケーションを遅延させることができる。
  • 1日遅れや2時間遅れのスレーブDBを作るなど。
• マスタDB上で行われた誤った作業から保護する
• システムのデバッグ時の再現手法として使う
  ※DBの複製なので物理的なコストは高い。
  ※バックアップは別に必ず取るようにする。

3. やり過ぎたJOIN

アンチパターン

• パフォーマンスに直結するJOINに対する不理解
  • 多段JOINと不要なJOIN
  • JOINの内部表にINDEXがない

JOINについて

JOINの特性

• 集合の和の結果なので、ベン図で理解する。

  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

• MySQLは、FULL OUTER JOINをサポートしていないため、RIGHT JOINの結果をLEFT JOINの結果とUNIONすることで表現する。

SELECT
  *
FROM
  会員
  LEFT OUTER JOIN 都道府県
    ON 会員.出身県id = 都道府県.県id
UNION
SELECT
  *
FROM
  会員
  RIGHT OUTER JOIN 都道府県
    ON 会員.出身県id = 都道府県.県id

JOINの問題点

• 「JOINは掛け算」であり、JOINの回数が増えると急激に重くなる
  • SQLの処理の中で最も重い処理の1つ。

JOINアルゴリズムの種類

• Nested Loop Join（NLJ）

  • JOIN元のテーブルが先のテーブルに対し1行ずつループして処理する

• Hash Join

  • 一度に全件を読み込んで処理する
    1. 小さい票を全件読み取ってHash表を作成
    2. 大きい表の結合列をHash表の値と比較して結合
    3. 両テーブルを1回ずつ全件読み取り

• Sort Merge Join

  • 全件を（2つの表の結合キーで）ソートして上から順に比較する

• PostgreSQLは3種類のJOINをサポートしているが、MySQLはNJLしかサポートしていない。

  • PostgreSQLは、大きな2つの表のJOINや不等号を使ったJOINが得意。
  • MySQLも、「内部表に適切なINDEX」があり、「小さな外部表をもとに等号で結合」する場合は、非常に高速に処理できる。

まとめ

• JOINは必要最低限
• JOINするテーブルは小さくしてからJOINする。
• 複雑なクエリになった場合はViewを活用する。

  • ビューとはテーブルから取得したいデータの条件を定義してあたかも独立したテーブルのように扱えるようにしたものです。 （参照: ビューの作成）

• INDEXを適切に活用する。
  • （参照： WHERE狙いのキー、ORDER BY狙いのキー）

マテリアライズド・ビュー

• SQLの結果を実体のあるViewにする機能
  • クエリの結果のテーブルを作ること。
  • 再作成の時にテーブルの作り直しが不要で、共有ロックのリフレッシュのみで良い。
  • 例）単価表の更新が1日1回で良い場合などは、集計結果を別テーブルとして保存することで、高速に参照できる。

4. 効かないINDEX

INDEXの種類

• 最も一般的なのが、BTree INDEX

INDEXを利用するために必要な条件

• 検索結果がテーブル全体の20%未満（実務レベルでは10%未満）
• 検索対象のテーブルが十分に大きい（数万〜数十万行）
  • 1,000行程度のテーブルではINDEXを参照するよりもテーブルスキャン（インデックスが存在しない場合、表の先頭から最後まで検索すること）が効率的なケースが多い。
    - 例）47行しかない都道府県マスタ。

アンチパターン

設定したINDEXが効かない（使われない）ケース

1. 条件にその列を使っていない

• 検索対象の列がWHERE句やJOINの際のON句などで利用されていない場合、利用できない。

/* bad
 比較する検索の対象は age * 10 の計算結果となるため、
 全ての行に対して計算・比較してしまう */
SELECT * FROM users WHERE age * 10 > 100;
/* good */
SELECT * FROM users WHERE age > 100/10;

2. カーディナリティの低い列に対する検索

• カーディナリティとは？
  • 「列に格納されるデータの値にどのくらいの種類があるのか」
    • 種類が多い：カーディナリティが高い（シーケンシャルなidなど）
    • 種類が少ない；カーディナリティが低い（性別など）
• カーディナリティが低い列に対して絞り込む場合は検索結果が多くなりやすいため、INDEXをうまく利用することが難しい

3. あいまいな検索

• 前方一致
  • 標準でINDEXを利用。
• 後方一致
  • INDEXを利用するには、reverse()などの関数で対象の列をひっくり返して別の列に保存したり、PostgreSQLの式INDEXを利用したりする必要がある。
• 部分一致
  • INDEXを利用するには、全文検索インデックスなどを利用する必要がある。
    • 例） MySQL：Mroonga, PostgreSQL：PGroonga

4. 統計情報と実際のテーブルで乖離がある場合

• INDEXを利用するかどうかは、クエリの実行時にオプティマイザが判断して決めている。
• この時の判断材料となるのが統計情報。
  • 統計情報は定期的にテーブルから一定数のサンプリングを行い、それをもとに作られる。
• 次のような場合、「実際のデータ分布から乖離した統計情報」が作られることがある
  1. サンプリングの前に大量のデータ更新が行われた
  2. サンプリングで偏ったデータを収集した
• これらのような場合は、オプティマイザによる統計情報の更新を行う。
• また、利用するINDEXや統計情報を固定する手法もある。

まとめ

• INDEX（とくにBTree INDEX）の特性をしっかりと把握して適切なINDEXを設定する
• INDEXを利用できるクエリを実行する
• INDEXを活用できるテーブル設計をする
• スロークエリログやデータの状態などをしっかりとモニタリングする

インデックスショットガン

• 「SQLアンチパターン」に出てくる、闇雲にINDEXを設定しまくるというアンチパターン。
• INDEXを設定することで INSERT/UPDATE/DELETE が遅くなる。
• 複雑な複合INDEX（複数の列に対するINDEX）を設定し過ぎると、オプティマイザが不適切なINDEXを選ぶことがある。

MENTORの原則

• Measure（測定）

  • スロークエリログやDBのパフォーマンスなどをモニタリング

• Explain（解析）

  • 実行計画を見てクエリが遅くなっている原因を追求

• Nominate（指名）

  • ボトルネックの原因（インデックス未定義など）を特定

• Test（試験）

  • ボトルネック改善（インデックス追加など）を実施し、処理時間を測定。改善後の全体的なパフォーマンスを確認

• Optimize（最適化）

  • DBパラメータの最適化を定期的に実施し、インデックスがキャッシュメモリに載るように最適化

• Rebuild（再構築）

  • 統計情報やインデックスを定期的に再構築

• INDEXを設計する際の3つの問い

  1. このテーブルは1年後、3年後、5年後、何行くらいになるだろうか
  2. このINDEXは複合INDEXでまとめる、または単一のINDEXで十分絞り込めるのではないだろうか
  3. 今このINDEXを張るべきか

5. フラグの闇

アンチパターン

• テーブルに削除という「状態」を持たせてしまっている。
  • 削除フラグ以外にも、課金状態やユーザー状態などがある

『とりあえず削除フラグ』

• 『とりあえず削除フラグ』は以下の問題を含む。

1. クエリの複雑化

• 仕様変更時などに影響範囲が広がってしまう。

2. UNIQUE制約が使えない

• UNIQUE制約が使えないデメリット
  • データの重複を防げない
  • 該当列に対して外部キー制約を利用できない
  • 外部キー制約を利用できないことでデータの関連性を担保できない

3. カーディナリティが低くなる

• 重複が少ない（カーディナリティが高い）データであればINDEXをうまく利用できる。
• 削除フラグのようなカーディナリティが低いデータは、検索時に必ず削除フラグを含めなければならず、ボトルネックの理由になる。

まとめ

事実のみを保存する

• トリガーを使う

  • トリガーとは、テーブルに対するある操作に反応して、別の操作を実行する機能。
  • 「削除済み」のためのテーブルを作り、データを移す。

• Viewを使う

  • 有効なデータだけの表を作る。
    • 高速化にはつながらない。
      • 高速化を意識するなら、PostgreSQLならばマテリアライズド・ビュー、MySQLならサマリーテーブルを生成する。

「状態」を持たせるのは絶対にだめか

• 持たせる場合は以下に注意

  • 対象のテーブルが小さく、INDEXが不要
  • そのテーブルに関連するテーブルの親になることがなく、データを取得する際に頻繁にJOINの対象になることがない
  • UNIQUE制約が不要で、外部キーでデータの整合性を担保する必要がない

削除フラグを利用したくなるケース

• エンドユーザーから見えなくしたいが、データは消したくない

• 削除したデータを検索したい

• データを消さずにログに残したい

• 操作を誤ってもなかったことにしたい

• 削除してもすぐに元に戻したい

• 以上は削除フラグ以外の設計でも対応できるため、安易にテーブルに状態を持たせることなく設計することを心がける。

削除フラグ以外のフラグの闇のケース

• statusカラム
  • 取り出す際にWHERE句を利用したり、View側で表示のバグを防ぐためにif文でcheckを入れる必要が発生する。
• 送信ステータス
  • メルマガの送信ステータスには、2つの問題がある。
    1. テーブルが肥大化していき、将来的にパフォーマンスがボトルネックになる。
    2. トランザクションの問題。配信が重複しないように、メールの送信リストに対して排他的な行ロックを取って管理しなければならない。
       • テーブルに状態を持たせている場合に大量のメルマガを送信すると、長時間ロックを取ることになる。
       • データが小さい場合や並列処理が少ない場合には、問題が顕在化しにくいため、初期の段階で早めに対策することが大切。

6. ソートの依存

アンチパターン

• ソートはパフォーマンス面で考えると、RDBが苦手とする分野。
  • ページが昔のものになればなるほど、表示に時間がかかる。

リレーショナルモデルとソートのしくみ

リレーショナルモデル

• リレーショナルモデルは集合を扱うデータモデル
  • 次のような性質がある。
    • 重複がない
    • 実在する要素しかない（NULLがない）
    • 要素に順序がない
  • ソートはリレーショナルモデルの外の世界の話なので、パフォーマンスのボトルネックになりやすいのは必然。

ORDER BYのしくみ

RDBMSのクエリ実行部分『エグゼキュータ』

• 次の順にSQLを評価する。

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. HAVING
7. SELECT
8. DISTINCT
9. ORDER BY
10. LIMIT

• 全てのデータを取り出してからORDER BYで並び替え、最後にLIMITで必要なデータを切り分ける。

SELECT ← 3番目
  *
FROM ← 1番目
  users
WHERE ← 2番目
  id < 10000
ORDER BY name ← 4番目
LIMIT 100 ← 5番目

WHERE句狙いのINDEX

• ORDER BYは、データ大きくなればなるほど重い処理になるが、事前にWHERE句を使うことで、対象を絞り込むことができる。

  • 1億件のレコードを並び替えるのと、それを1,000件に絞り込んでから並び替えるのとでは、同じORDER BY後に100件を取り出すとしても、処理時間が雲泥の差になる。

• WHERE句がINDEXを活用でき、データを充分に小さくできるのであれば、パフォーマンスは劇的に向上する。

SELECT * FROM users WHERE 性別 = '男性' ORDER BY 出身県 id LIMIT 5

1. 性別のINDEXを利用して検索
2. 該当の結果を取り出し、それをソート
3. ソート中に5件が確定したら結果を返す

ORDER BY句狙いのINDEX

• WHERE句が必ずしも最適解ではない。

  • WHERE句でINDEXを利用する列は、カーディナリティが少なく、データの値が偏っているとINDEXが有効に活用されない。

• PostgreSQL、MySQLの標準的なINDEXの実装であるBtree INDEXは、データを「ソート済み」の状態で保存している。

  • 対象のソート結果とINDEXが同じならば、INDEXから取り出せば良い。
    • これを『ORDER BY句狙いのINDEx』と言う。

SELECT * FROM users WHERE 性別 = '男性' ORDER BY 出身県id LIMIT 5

1. 出身県idのINDEXを利用して検索
2. ソートされた結果を取り出しながら性別を評価
3. 5件が確定したら結果を返す

• ORDER BYでINDEXを利用した場合の強み。
  1. ソートの処理が不要になる
  2. 評価数がLIMITの件数に達した時点で結果を返せる

MySQLでのORDER BY句狙い

• MySQLでも度々、INDEXを利用する際にORDER BYを狙うことがある。

  • （参照： WHERE狙いのキー、ORDER BY狙いのキー）

まとめ

• ORDER BYを速くするには

  • データを小さくする
    　- INDEXを活用する

• 実際に業務でうまく活用するためには「しっかりと実行計画を見る」

  • ORDER BY句狙いが良いか、WHERE句狙いが良いか

idを指定してソートを高速化

• WHERE句で絞る値を追加する
  • ページャでは、「次のページ」として渡すのは「最後に表示された行のid」にする
    • データ量が増えてもINDEXを活用できるため高速
    • ページ数が深くなってもOFFSETを利用しないため取得行が肥大化しない
• SELECTでINDEXに含まれているデータのみが必要な場合は、実際のテーブルデータにアクセせずに結果を返せる
  • MySQLの場合は「カバリングインデックス」、PostgreSQLの場合は「インデックスオンリースキャン」が期待できる。

idを指定できないケース

• ORDER BYやINDEXの仕組みを知り、SQLを工夫することでパフォーマンスを何十倍にもすることができる。

1.ORDER BYの結果がidの順番と関係なく、またUNIQUEな値でない

ORDER BY name, id DESC

のようにidでもソートすることで改善する場合もある。

2.UNION/GROUP BY/HAVINGが使いたい

• UNION/GROUP BY/HAVINGを利用した場合、結果にidが使えないケースがある。
• UNION/GROUP BY/HAVINGとORDER BYは相性が良くない。
  • GROUP BYとORDER BYが組み合わさったケースでは、集計結果のサマリーテーブルを作り、そこにINDEXを貼ることを検討する。

大きなデータをソートしたいとき

1. アプリ側でソート

   • フロントエンド側からデータの一覧をREST APIで取得し、それをJavaScript側でソートさせて表示させる方法。
     • データ一覧をアプリに全て渡す必要があり、データサイズが大き過ぎると通信がボトルネックになることもある。

2. ソート済みの結果をキャッシュして利用

• ソートの処理が決まっており、結果が変更されにくいデータなどはキャッシュが有効。
  • 郵便番号や市町村の住所など。
  • 更新頻度が高いデータでは使い物にならない。

1. NoSQLなどを利用してソート

• 近年では一般的な方法。

RDBを補う存在、Redis

• RedisとRDBMSは非常に相性が良い。
  • Sorted Set（ソート済みセット型）を使うと、高速にソート済みデータを検索したり、取得したりすることができる。
    • トランザクションが必要な課金系のデータはRDBMSとの住み分けになっており、処理の結果はRedisに保存して参照はRedisから行うといった構成はよく取られる。

7. 隠された状態

アンチパターン

意味を含んだID（論理ID、スマートカラム）

• 識別できる一意の値以上の意味を持ったID
  • 例）330010012（県番号2桁、部門ID3桁、支店ID4桁）

複数の目的に使われるテーブル

• 似たような属性のデータの場合、1つのテーブルに保存してしまいがちだが、パフォーマンスの面を考えても、テーブルを分けた方が良いケースが圧倒的に多い。
  • 例）管理者テーブルと一般ユーザーテーブル
    • アプリ側は参照するテーブルを分けることで、問題を局所化することができる。
    • ドメインとしても分けやすくなるので、モデルとしてシンプルになる。
    • 管理者の場合は値が入るが、一般ユーザーの場合はNULLになる、といったカラムの出現も防げる。

EAV（エンティティ・アトリビュート・バリュー）

• 『SQLアンチパターン』でも紹介されている。1つのカラムに状態を詰め込んでレコード単位で状態を隠す手法。

EAVとは？

名称	主な役割
Entity	親テーブルに対応する外部キーを格納する。
Attribute	列の名前に相当する。行ごとに格納したい属性名を指定する。
Value	属性の値

• 次のような設計上の問題がある。

必須属性が設定できない

データ型が指定できない

正規化されていないため外部キー制約（参照整合性制約）が強制できない

属性名を補う必要がある

Polymorphic Associations（ポリモーフィック関連）

• 『SQLアンチパターン』でも紹介されている。テーブル単位で状態を隠す手法。

• 子テーブルの属性によって紐づく親テーブルが変わってしまうため、外部キー制約は使えず、アプリ側や運用者としてはJOINする対象がデータを取り出すまでわからない。

  • その結果、親テーブルの両方をJOINしてからNULLの場所によって対象データを判断するような運用が行われるようになり、非効率的なクエリが実行されることになる。
  • EAVと違い、テーブルをそれぞれ用意することで必須属性やデータ型を利用できるが、EAVと同じく外部キー制約は使えないため、参照整合性は担保できない。

隠された状態が生む問題

• コードやデータから見えない状態の辛さ
• 失われた制約
  • アプリ側で判断できないため不正なデータが投入されやすい。
  • 隠された状態の設計ではCHECK制約や外部キー制約が使えないため、RDBの制約で防ぐことができない。

まとめ

• テーブルに複数の意味を持たせない
• 1つのデータ（テーブル・カラム・レコードなど）の責務を小さくする
• 常に状態が見えるようにするために事実のみを保存する
• Polymorphic Associationsの場合は、交差テーブルを用意するのが良い。

トリガー

• RDBMSに用意された隠された状態に近い機能と言える。

トリガーを使っても良い場面

• 次のようなメリットがある場合のみ。

1. パフォーマンス的メリット
2. アプリ側の実装が大幅に削減できる
3. 既存のアプリの振る舞いを維持したまま、仕様を変更できる

8. JSONの甘い罠

アンチパターン

• JSON型の値を入れることで、検索の複雑性が上がったり、更新のコストが上がったりする。

「なんでもJSON」の危険性

ORMが使えない

• JSONを取り出すためのSQLを表現することが大変難しいため、将来的な開発工数が激増することがある。

データの整合性が保てない

• JSONデータ型はEAVの代替案でありながら、EAVと同じような問題がある。

必須属性の指定が難しい

• 素直にDBにカラムを増やす方が良い場合がある。

データの中身を指定できない

• JSONは値の型を制限できないため、型違いのエラーを引き起こす場合がある。

参照整合性を強制できない。

• 中身がユニークでなく、正規化されていないため、外部キー制約を使うことができない。
  • 例）"都道府県”というkeyに対して、「東京都」と「東京」の両方が保存される可能性がある。

JSONデータ型を使うユースケース

Web APIの戻り値

• 必要なデータは正規化してその他が含まれるJSONは別テーブルに分けることで、パフォーマンスを向上させつつ、必要なデータが取得できなくなったときはErrorで気づける

OS情報

• OS情報のうち、ディストリビューションによって固有のデータは、JSONデータ型のカラムに吸収する。
  • 「JSONのkeyに対して検索したい」「JSONの特定の値を更新したい」「JSONの中の値に制約を設けたい」といった操作が必要な場合、正規化を行って個別にカラムを指定するべき。

まとめ

• JSONデータ型はEAVの代替案となるが、JSONデータ型に置き換えるときは以下の点に気をつける。

  • 正規化することはできないか
  • JSONに対して頻繁に更新を行いたいか
  • 検索条件としてJSON内の属性が固定できない場合

• JSONデータ型はRDBMSの機能と引き換えに柔軟性を与える、本当に最後の切り札

9. 強過ぎる制約

アンチパターン

• 「早過ぎる最適化」
• 過剰な制約の利用によって柔軟性を失う。
  • 例）独自の制約（DOMEIN機能）であるemail_address型を付与した結果、メールアドレス登録で不具合が発生する。
  • 例)ENUM型

外部キー制約が生み出すデッドロック

• MySQLの外部キー制約が嫌われる大きな理由の一つに、外部キー制約の小テーブルを更新すると、親テーブルの共有ロックを自動的に取ることが挙げられる。
  • デッドロックの温床となる。

制約の段階

段階	説明
制約なし	型自体が規定する値域であればOK
弱い制約	NOT NULL制約、UNIQUE制約、外部キー制約など
強い制約	CHECK制約やEXCLUDE制約など（都道府県は47個、消費税は3桁以上の整数）
強過ぎる制約	「システムの仕様」や「ビジネス・ルール」に基づく

まとめ

• 「強過ぎる制約」になっていないか？ということを考える
  • 多くのシステムは適切な正規化と弱い制約で十分に設計できる。

10. 転んだ後のバックアップ

アンチパターン

• リストアできないバックアップ
  • バックアップが失敗していることを知ることができず、それによってバックアップから復旧できない
    • バックアップ・リストアの手順書がない
    • 最初にシステム設計した人が既にいない
    • チームで定期的に訓練をしていない
    • システムの面倒を見る作業が属人化している

3つのバックアップ

論理バックアップ

• SQLやCSVとして、DBそのものを再構成できるようにバックアップを取ること。
  • MySQLの場合はmysqldump、PostgreSQLの場合はpg_dump

物理バックアップ

• データベースの物理ファイルを丸ごとバックアップする方法。

PITR

• 特定の日時の状態にデータをリストアできる手法のこと。
  • バックアップファイルと更新情報の入ったログが必要。
    • MySQLではバイナリログ、PostgreSQLではアーカイブログ

バックアップの設計

RPO（Recovery Point Objective）

• いつ時点のデータを復旧するか（目標復旧時点）

RTO（Recovery Time Objective）

• どれくらいの時間で復旧できるか（目標復旧時間）

RLO（Recovery Level Objective）

• どこまで復旧するか（目標復旧レベル）

まとめ

• バックアップが正しく行われていることを毎回確認し、失敗したときに必ず気付けるようにする
• リストアを定期的に行う
• 手順をまとめる。ベーシックな手順だけではなく、実際のユースケースに則したパターンをいくつか用意すると良い

バックアップとリストアの自動化

1. 本番から必要なデータをバックアップ
2. バックアップからステージングを生成
3. ステージングと本番の差分確認（本番用のシナリオテストを実行）
4. ステージングに不必要なデータをDROPしたり変更したりする

チーム内でリストアする機会を定期的に作る

• 「本番と同様のステージングを定期的に作り直す」など

クラウドサービスの利便性

• RDSでは、バックアップとしては1日1回のフルバックアップと5分おきの更新情報が含まれたログのバックアップが行われている。