達人DB

第1章 データベースを制するものはシステムを制す

1-1システムとデータベース

データベースを使わないシステムはこの世に存在しない。
→全てのシステムは1つだけ共通点を持っていてそれはデータを取り扱うこと

情報はデータと文脈を合成して生まれる
→データと情報の定義

1-2データベースのあれこれ

  • データベースの代表的なモデル
    • リレーショナルデータベース
    • オブジェクト指向データベース
    • XMLデータベース
    • キー・バリュー型ストア

データベースのモデルが異なれば、データフォーマットも異なる。モデルが異なれば設計技法も異なる。
→例えば、RDBにおける重要な設計技法「正規化」はRDB以外では使わない

  • 主なDBMS
    • Oracle Database(省略してOracleと呼ばれることが多い)
    • SQL Server
    • DB2
    • PostgreSQL
    • MySQL
      →DBMSは違っても設計技法に影響はない。各DBMSが独自機能を発展させてきたことによる違いはある

1-3 システム開発の工程と設計

システム開発いくつかの工程

  • 要件定義
    システムが満たすべき機能やサービスの水準(すなわち要件)を決める工程。
  • 設計
    定義された要件を満たすシステムを作るための設計を行う工程。
  • 開発(実装)
    設計書に従って、システムを実際に作る(実装する)工程です。開発と聞くとコードを書くと思いがちですが、 サーバーやネットワークの調達や構築といった作業も含む。
  • テスト
    本番の負荷に耐えられるか、バグがないかをテストする工程です。

設計工程と開発モデル

  • ウォーターフォールモデル
    →上流から下流へ一方方向の開発サイクル
  • プロトタイピングモデル
    →工程を循環する開発サイクル

設計工程とデータベース

設計工程の種類

  • ソフトウェアの提供する機能を決めるアプリケーション(AP)設計
  • ユーザーが使用する画面などのユーザーインターフェース設計
  • 「データ設計」特に、データベースに保持するデータの設計(データベース設計)

データベース設計とは

  • データベース設計とは、データベースに保持するデータの設計のことです。

DB設計がなぜ重要なのか

  • DB設計がなぜ重要なのかというと、DB設計(データ設計)はシステムの品質を最も大きく左右するからです。どんなデータをどのようなフォーマットで持つかによって、システムの品質も変わってきます。DB設計にバッドノウハウが適用されたり、適切な設計がされていないと、プログラムのコードをどれだけきれいに書いても、機能を実現するために、無駄にテーブルを結合したり検索する必要が出て、パフォーマンスを悪化させます。また、クエリが冗長化する可能性がある為、クエリを発行するアプリケーション側のコードも冗長になり、アプリケーション側のコードの可読性が落ちる可能性があります。設計が適切ではないことによって、データを冗長に保持してしまい、サーバーのリソースを無駄に消費してしまったり、カラムを気軽に追加できなかったり等、スケール面での問題もあります。
    そして、既に運用されているシステムのDB設計を後から変更するのは、かなり大変です。DB設計を変更することでアプリケーション側のコードも変更しないとならない為、高い改修コストがかかります。
    そのため、DB設計はシステムの品質を大きく左右するので、とても重要。

データ中心アプローチとは(DOA)

  • DOAとは、システムを作る際に、プログラムよりも前にデータの設計から始める方法論です。 プログラムから先に作り、業務処理単位でデータ設計を行うPOAより、DOAは業務要件の仕様変更に強く、複数のプログラムでデータを共有するのも容易です。

DOA：データ → プログラム
POA：プログラム → データ
現在はPOAが採用されることは普通ない。

3層スキーマモデル

  • データベース設計を考える際は、データベースを外部スキーマ、概念スキーマ、内部スキーマの3層に分けて考えます(DBは3層のスキーマからなる)。
概要
外部スキーマ外部スキーマとは、システムの利用者であるユーザーから見て、データベースがどのような機能とインタフェースを持っているかを定義するスキーマです。「ユーザーから見たデータベース」を表します。ビューが相当します。自社の管理画面で見る購買履歴のインターフェースの定義等が外部スキーマです。ビュー
概念スキーマデータベースに保持するデータの要素および、データ同士の関係を記述するスキーマです。概念スキーマは「開発者から見たデータベース」です。必然的に、データベース設計において重要な位置を占めることになります。概念スキーマの設計を「論理設計」と呼びます。テーブル定義
内部スキーマ概念スキーマで定義された論理データモデルを、具体的にどのようにDBMS内部に格納するかを定義するスキーマです。いわば「DBMSから見たデータベース」です。テーブルやインデックス（索引）の物理的定義を含みます。RDBもコンピュータ上で動く以上は、あらゆるデータは最終的に「ファイル」の形で管理されます。内部スキーマの設計を、論理設計との対比で「物理設計」と呼びます。データの物理的な配置

概念スキーマが必要な理由

  • 外部、内部スキーマの2層だけの場合だとスキーマ同士の独立性が低く（依存性が高い）、変更に弱いシステムになってしまう。両者の変更が影響し合わないようにするための緩衝材としての役割を果たしている。

2章　概念スキーマと論理設計

  • 論理設計と物理設計
    データベース設計は、論理設計(概念スキーマの設計)と物理設計(内部スキーマの設計)に分けられ、論理設計は物理設計より前に位置している。
  • 論理設計とは
    論理設計とは、概念スキーマを定義する設計のことです。論理設計における「論理」とは、物理層の制約にとらわれないという意味です。物理層の制約とは、CPUパワーやストレージのデータ格納場所、データ型等、具体的な実装レベルの条件のことです。最初にDB設計するときは、物理層の制約は考えずに、論理設計を始める。
  • 論理設計のステップ
    論理設計では、現実のデータから、RDBにおいて、何をどのようなフォーマットで保存するかを決めることを行います。
    論理設計は以下のステップで実行
    • エンティティの抽出
    • エンティティの定義
    • 正規化
    • ER図の作成
  1. エンティティの抽出
    エンティティとは、現実世界に存在するデータの集合体のことです。物理的実体を伴うものと、伴わないものも。
    →エンティティの例
    顧客、社員、店舗、車、税、会社、注文履歴等
    →RDBでは、このような現実世界のエンティティを、最終的には「テーブル」という物理的単位で格納していきます。 そのため、RDBにおけるエンティティは、テーブルで表現される。 システムでどんなエンティティ(データの集合体)を必要になるかを抽出することが、論理設計で一番初めに行う。
  2. エンティティの定義
    エンティティを抽出した後は、各エンティティがどのようなデータを保持するかを決める必要があります。エンティティはデータを「属性」という形で保持します(属性はテーブルの列と同義)。 RDBにおいて、エンティティを定義することは、テーブルを定義することと同義です。 テーブルを定義するときに気をつけることは、キーという列を必ず定義することです。キーとは、ある特定の列の値を決定するための列のことです。キーは複数列で構成されることもあります。
  3. 正規化
    正規化とは、テーブル(エンティティ)を細かく分割して、テーブルのフォーマットを整理する作業のことです。テーブルを分割することで、データの冗長的な保持を解消したり、データの整合性(ズレや矛盾がない、前後が揃っている)を向上させられる。
  4. ER図の作成
    ER図とは、エンティティ(テーブル)の関係性をわかりやすく図にしたものです。 正規化すると、エンティティの数が増えて、エンティティ同士の関係性が分かりづらくなる為、このプロセスは必須。
  • 物理設計とは
    物理設計とは、論理設計の結果を受けて、データを格納するための物理的な領域や格納方法を決める工程のことです。物理設計は内部スキーマを定義する設計のこと。
  • 物理設計のステップ
    • テーブル定義
    • インデックス定義
    • ハードウェアのサイジング
    • ストレージ冗長構成決定
    • ファイルの物理的配置決定
  1. テーブル定義
    テーブル定義とは、論理設計で定義した概念スキーマを元に、DBMS内部格納するためのテーブルを実装することです。SQLのDDL(データ定義言語)等を用いてテーブルを実装。
  2. インデックス定義
    インデックスを設定しなくても機能的には問題ないが、非機能の部分、パフォーマンスが大きく変わる。
  3. ハードウェアのサイジング
    ハードウェアのサイジングとは、ざっくりいうとストレージやサーバの大きさを決めることです。サイジングはキャパシティとパフォーマンスの2つの観点で行います。DBにおいて、データの整合性とパフォーマンスの間に強いトレードオフが存在します。つまり、整合性を高くすると、パフォーマンスが犠牲になり、パフォーマンスを追求すると、整合性を犠牲にします。例えば、高次の正規化をすればするほど、テーブルの数が増え、データの整合性は上がるが、joinで何度も結合する必要が出て、パフォーマンスが悪化します。
    キャパシティのサイジング
    データ量はシステムの運用開始から基本的には増えていくので、システムの運用終了時にデータ量がどの程度増えるかを見越しておく必要があります。そうしないとストレージの容量が足りなくなってしまいます。 サービス終了時のデータ量を正確に見積もることは難しいため、以下の2つの対策をします。
    ・最初から余裕を持たせたサイジングを行う。
    ・後で容量が不足した場合に、簡単に記憶装置が追加できるような構成にしておく(スケーラビリティが高い構成)。
    パフォーマンスのサイジング
    ■性能要件
    →システム開発おける性能要件は、2つの指標を使って定義します。
    ・処理時間(特定の処理をどれだけは早く処理できるか)
    ・スループット(単位時間あたりにどれだけの処理をシステムがこなせるか)
    スループットの単位には、「1秒あたりの仕事量」を表すTPS(Transaction Per Second)という指標を使います。 この2つの要件は、要件定義のフェーズで必ず決めます。
    ■リソース使用量の基礎数値
    新規システムを作る場合、どのくらいハードウェアリソースを消費するかの根拠を表す数値を得る方法は2つあります。
    ・類似の稼動中システムのデータを流用する。
    ・開発の初期段階でプロトタイプシステムを構築して、性能検証を実施する。
    →しかし、方法1は類似したシステムがない、方法2は検証するのに開発コストがかかったり等、現実的ではないです。そのため、パフォーマンスのサイジングでも、安全性を高めたり、スケーラビリティの高い構成を組むようにします。
  4. ストレージ冗長構成決定
    データベースに保管されるデータを失うことは、絶対に許されない。RAIDを使うことで、可能な限り高い耐障害性を持つシステムを実現できます。
    RAIDとは
    RAIDとは、複数のディスクを束ねて仮想的に一つのストレージとする技術のことです。この単位でまとめられたディスクをRAIDグループと言います。 RAIDのもともとの目的は、十分な信頼性の得られない安価なディスクで、なんとかシステムの保持するデータの安全を図ろうとすることでした。RAIDには何段階かレベルがありますが、基本的な考え方は、複数のディスクに同じデータを書き込んで冗長化することで、そのうちの一本が壊れても残りのディスクが生きていればデータを修復できるようにする、というものです。RAIDでは冗長性が良いことだと捉えるのに対し、正規化では極力冗長性をなくすようにしています。
    RAIDのメリット
    RAIDを使うことで、システムの信頼性(可用性)を高めることができます。しかし、それだけではなく、システムの性能も向上させます。理由は、ほとんどのレベルのRAIDでは、複数のディスクにデータを分散して保持するため、システムにおいて最も性能的にボトルネックとなるディスクI/O(外部記憶装置に対する読み書き)を分散することができるからです。
    RAIDを採用するにあたって、以下のことを考える必要があります。
    ・データには信頼性が求められるのか、それとも性能が求められるのか(RAIDのレベルやディスク本数が決まる)
    ・どのようなレベルのRAIDを採用するか
    ・何本のディスクでRAIDを構成するか
    予算が許すならRAID10、無理ならRAID5を推奨
  5. ファイルの物理的配置決定
    この工程では、データベースのファイルをどのディスク(またはRAIDグループ)に配置するかを考えます。このファイル配置に関しては、DBMSが自動で配置してくれます。とはいえ、どんなファイルが配置されるかは知っておいた方が良いです。データベースには以下の5つのファイルが格納されます。
    1.データファイル
    2.インデックスファイル
    3.システムファイル
    4.一時ファイル
    5.ログファイル
    このうち開発者が存在を意識するのは、データファイルとインデックスファイルだけで, これらのファイルには、以下のようなデータが格納されます
    データファイル →「テーブル」のデータ
    インデックスファイル → テーブルに付与されたインクデックスのデータ
    データファイルについて
    データファイルとは、ユーザーがデータベースに格納するデータを保持するためのファイルです。アプリケーション側で発行したSQLを通じて、参照及び更新を行うファイルでもあります。しかし、アプリケーションから見えるのはあくまで「テーブル」という論理的単位であって、「ファイル」が直接見えるわけではないです。
    インデックスファイルについて
    インデックスファイルとは、テーブルに作成したインデックスが格納されるファイルです。DBMSではテーブルとインデックスは異なるファイルで管理されます。 SQLではテーブルへのアクセスを記述することはあっても、特定のインデックスに対するアクセスを記述することはないです。インデックスを使うかどうかは、DBMSが内部で勝手に判断してくれます。
    DBMSはこうしたファイル群をテーブル等の上位の論理的な概念でラップする為、ユーザーはファイルを意識しなくてもデータを扱えます。
    その他
    システムファイル
    　DBMSの内部管理用のデータ
    一時ファイル(影響大)
    　サブクエリ、GROUP BY、DISTINCTなどの処理に必要なデータ
    　SQL処理が終わったら削除される
    ログファイル
    　トランザクションログ
    　COMMITされていないデータの更新履歴
    　COMMITしてデータファイルに反映したら、用済み

バックアップ設計
システムにおいて、データが失われるのは絶対に許されません。そのため、極力データを失わないようなRAIDの設計をしたり、バックアップとリカバリができるようにしておきます。

バックアップとは
バックアップとは、基本的にはファイルのコピーのことです。

ログファイルとは
バックアップを理解する上で、ログファイルの知識は欠かせません。ログファイルとは、テーブルのデータに対する変更履歴を記録するファイルです。DBMSは、ユーザーから受け付けた変更を、すぐにデータファイルに反映するのではなく、ログファイルに溜め込みます。ログファイルには、DB内のデータに対するあらゆる変更操作が残っているので、このファイルをバックアップしておけば、DBに対する変更操作をもう一度再現することができます。

バックアップの3つの方式
バックアップには3つの方式があります。 これらの方式は、バックアップデータをどのような単位で分割するか、という基準に基づいています。

フルバックアップ(完全バックアップ)
　データファイルだけバックアップを取る方式です。
差分バックアップ
　フルバックアップを取った後に、フルバックアップからの差分だけ累計的にバックアップを取る方式です。差分バックアップは差分データをバックアップしているわけではなく、フルバックアップを取った後の、操作の履歴を表すログファイルをバックアップしてます。差分バックアップをするためには、データファイルとログファイルが正常にバックアップされている必要があります。最新のログファイルとフルバックアップした時のデータファイルがあれば、データを修復できます。
増分バックアップ
　増分バックアップは、前回の任意のバックアップからの増分データのみをバックアップする方式です。フルバックアップした時のデータファイルと、すべての増分に関するログファイルがあれば、データを修復できます。
以下のトレードオフ関係がある
・ 完全バックアップ: バックアップコスト大、リカバリコスト小
・差分バックアップ: バックアップコスト中、リカバリコスト中
・増分バックアップ: バックアップコスト小、リカバリコスト大
完全バックアップ + (差分バックアップ or 増分バックアップ)が一般的
バックアップには、「バックアップコストが低いほど、リカバリコストが高い」というトレードオフの関係が存在します。 DBのバックアップ設計では、基本的にはこれら3つを組み合わせていきます。どれか一つだけということは普通ないです。「フルバックアップ + 差分バックアップ」 or 「フルバックアップ + 増分バックアップ」が一般的です。

リカバリ設計
バックアップ設計とリカバリ設計はセットで実施することが一般的です。リカバリ手順はバックアップ方式によって左右されるため、リカバリ設計はバッカップ方式が決まれば、自動的に決まります。

リカバリとリストア
障害復旧をする上で、リストアとリカバリという手順にを知る必要があります。リストアを実行してから、リカバリを実行します。

リストアとは
リストアとは、バックアップファイルを使ってデータベースを復旧する作業のことです

リカバリとは
リカバリとは、バックアップファイルでリストアしたデータベースに対して、ログファイルの内容を適用させて変更分を反映する作業のことです。

データベースの復旧手順
DBMS内部にはバックアップされていないログファイルが残っています。そのため、リストアとリカバリをした後に、このログファイルを適用することで、障害直前のデータベースに復旧できます。

フルバックアップのファイルをデータベースに戻す。→ リストア
差分(または増分)バックアップしていたログファイルを適用する。→ リカバリ
DBMSに残っているログファイルを適用する。→ ロールフォワード

3章: 論理設計と正規化

二次元表と”テーブル”は違う
テーブルは、見た目上は「二次元表」と似ている
しかし、以下の点で違う

  • テーブル: すべての行が、同じ構造を持つ <=> 表: 行によって構造を変えてもOK
  • テーブル: セル1つに複数の値を入れてはならない <=> 表: 複数入れてOK
  • テーブル: 重複レコードが存在してはならない(=主キーがある) <=> 表: 重複OK
  • テーブル: レコードは上下の順序を持たない 　 <=> 表: 上下の順序あり
  • テーブル: 列は左右の順序を持たない <=> 表: 左右の順序あり

つまり、テーブルとは共通の構造を持つ情報の「集合」
→テーブル名はすべて複数形または複数名詞で書ける

テーブルの構成要素

  • 行と列
    • キー
      • 主キー
      • 外部キー
  • 制約
    • NOT NULL制約
    • 一意制約
    • CHECK制約

正規化(normalization)とは
定義
テーブル構造を正規形(normal form)にすること
正規形とは、データの冗長性を排し、一貫性と効率性を保持するためのデータ形式
第1正規形〜第5正規形まである
通常は、第3正規形まで正規化できればOK
(第3正規形まで分割した時点で、自然と第4, 第5正規形になっているケースが大半)

正規化とは、テーブルのすべての列が関数従属性を満たすように整理していく作業
直感的にいうと「異なる実体(エンティティ)を1つのテーブルに押し込めず、テーブルレベルで分割する」ということ

  • 第1正規形

第1正規形を満たす例
[社員テーブル#1]

会社ID(pk)会社名社員ID(pk)社員名年齢部署ID部署名
C0001A商事10山田31D01開発
C0001A商事20田中41D02人事
C0001A商事30中村53D03営業
C0002B商事10村上24D03営業
C0002B商事11上島35D04総務
C0002B商事12島田46D01開発

定義
1つのセルに1つの値しか含まれない形
どうやれば正規化できる?
1つの列に複数の値を放り込まないようにする
↑の例で部署名を”総務、経理”にするような運用はやめよう
配列型やJSON型を使うのもアウト
正規化しないとなにがまずいの?
主キーを指定しても、各列の値を一意に決定できなくなる
(=関数従属性がなくなる)

  • 第2正規形

第2正規形を満たす例
[社員テーブル#2]

会社ID(pk)社員ID(pk)社員名年齢部署ID部署名
C000110山田31D01開発
C000120田中41D02人事
C000130中村53D03営業
C000210村上24D03営業
C000211上島35D04総務
C000212島田46D01開発

[会社テーブル]

会社ID(pk)会社名
C0001A商事
C0001A商事
C0001A商事
C0002B商事
C0002B商事
C0002B商事

定義
部分関数従属がない形(=完全関数従属だけの形)
主キー(複合キーを想定)の一部の列に対して従属する列がある場合、両者の関係を部分関数従属という
逆に、主キーを構成する全ての列に対して従属する列がある場合、両者の関係を完全関数従属という
社員テーブル#1を例にすると、
{会社ID} -> {会社名} は部分関数従属
{会社ID, 社員ID} -> {社員名} は完全関数従属

どうやれば正規化できる?
部分関数従属を見つけて、解消する
(=異なる実体(エンティティ)を見つけて、別テーブルに切り出す)
正規化しないとなにがまずいの?
社員テーブル#1を例にすると、以下の問題が起きる
社員情報が不明の会社をシステムに登録できない
会社IDと会社名の対応が行によってまちまちになる
その他補足
第1正規形->第2正規形への正規化は、可逆的な操作(無損失分解→テーブルを結合すれば元に戻せる)

  • 第3正規形

第3正規形を満たす例
[社員テーブル#3]

会社ID(pk)社員ID(pk)社員名年齢部署ID
C000110山田31D01
C000120田中41D02
C000130中村53D03
C000210村上24D03
C000211上島35D04
C000212島田46D01

[会社テーブル]

会社ID(pk)会社名
C0001A商事
C0001A商事
C0001A商事
C0002B商事
C0002B商事
C0002B商事

[部署テーブル]

部署ID(pk))部署名
D01開発
D02人事
D03営業
D04総務

定義
推移的関数従属がない形
主キーじゃない列同士に関数従属性がある場合、両者の関係を推移的関数従属と呼ぶ
社員テーブル#2を例にすると、{部署ID} -> {部署名}が推移的関数従属

どうやれば正規化できる?
推移的関数従属を見つけて、解消する
(=異なる実体(エンティティ)を見つけて、別テーブルに切り出す)

正規化しないとなにがまずいの?
社員テーブル#2を例にすると、以下の問題が起きる
所属部署が決まっていない社員をシステムに登録できない
部署IDと部署名の対応が行によってまちまちになる

その他補足
第2正規形と第3正規形は、主キーが絡むかどうかの違いしかない
可逆的な操作(無損失分解)

  • ボイス-コッド正規形
    非キーからキーへの関数従属をなくした形を指す
    第3正規形をより厳密に定義した正規形で、非公式には第3.5正規形とも呼ばれる
    第3正規形を満たせば、大抵はボイス-コッド正規形も満たしている

例.
「チーム補佐」から「チームコード」に対する関数従属がある仮定
{ 社員ID(pk), チームコード(pk) }　→ { チーム補佐(非キー) } は関数従属性あり
{ チーム補佐(非キー) }　→ { チームコード(pk) } →部分および推移的関数従属はない

第三正規化を満たしているが、ボイス-コッド正規形は満たしていない。
→非キーからキーへの従属性があるため
ボイス-コッド正規形分解時には非可逆な分解を行ってしまうことがあるので気をつけないといけない。

  • 第4正規形

第3正規形を満たすが、第4正規形を満たさない例
[社員-部署-製品テーブル]

社員ID(pk) 部署ID(pk) 製品ID(pk)
--------------------------------
001        D01       P0001
001        D01       P0002
002        D01       P0001
002        D02       P0001
003        D01       P0002
003        D02       P0002

第4正規形を満たす例
[社員-部署-製品テーブル]

社員ID(pk) 部署ID(pk)
    ---------------------
    001        D01
    002        D01
    002        D02
    003        D01
    003        D02

[社員-製品テーブル]

    社員ID(pk) 製品ID(pk)
    --------------------------------
    001        P0001
    001        P0002
    002        P0001
    003        P0002

定義
関連エンティティ(エンティティ同士の関連性を表現する)に含まれる関連を1つだけにした形

どうやれば正規化できる?
関連エンティティに含まれる関連を1つだけにする
(A-B-Cテーブル => A-Bテーブル、A-Cテーブル とする)

正規化しないとなにがまずいの?
社員-部署-製品テーブルを例にすると、以下の問題が起きる
社員001が部署を移動した場合、複数行を更新する必要がある(=冗長)
社員、部署、製品すべてが決まらないとシステムに登録できない

第5正規形
第5正規形を満たす例
上記、第4正規形を満たす例に次のテーブルを足す
[部署-製品テーブル]

部署ID(pk)製品ID(pk)
D01P0001
D01P0002
D02P0001
D02P0002

定義
関連すべてについて、関連エンティティを作った状態

どうやれば正規化できる?
存在する関連すべてについて、関連エンティティを作る
(もちろん、業務上関連がないのに関連エンティティを作る必要はない)
正規化しないとなにがまずいの?
関連エンティティがない関連は、エンティティ同士の対応関係がわからなくなる

正規化のまとめ
3つのポイント
正規化は、更新時の不都合/不整合を排除するために行う
正規化は、従属性を見抜くことで可能になる
正規化は、いつでも非正規系に戻せる

正規化は常にするべきか?

  • 第3正規形までは原則として行う
  • 関連エンティティが存在する場合は、関連とエンティティが1対1で対応するように注意する

正規化のメリット、デメリット

  • メリット
    • データの冗長性が排除され、更新時の不整合を防止できる
    • テーブルの持つ意味が明確になり、開発者が理解しやすくなる
  • デメリット
    • テーブルの数が増えるため、SQLで結合を多様することになり、パフォーマンスが悪化する

議論＆聞きたいこと
■check制約の話
mysqlのcheck制約
5系の時代はなかった機能
jsonの制約
　設定するブール式の評価がFALSEの場合に制約違反を発生させられる
window関数使える

ポスグレは機能的には一歩先を行っている
jsonは検索しなくて保持だけしたい場合に使うといい。
→ 座標のデータなど

■外部キー制約の話
外部キー制約は何でもかんでも貼らないほうがいい
→deleteの時に想定される問題がなかったらつける。あるならつけない。
バックアップのスナップショットはRDSと違うところに置かれている

■正規化について

  • 基本的に第三正規化まで行う
    → 正規化しないデメリットがある場合のみ正規化しない（アプリゲームではおそらくjoinすらコスト高）
    → 適切なテーブル設計なら認知負荷は少ない（wordpressでpostで色々な意味があると負荷が高い。）
  • 業務要件によっては第一正規化すら崩したいいことはある。
  • システムの複雑性とビジネスの複雑性は別
  • しっかり正規化されているとされているとER図を起こすのが楽
  • 別口での正規化の話(例.awsは複雑だけどサブシステムに切り出せば単純)
  • 設計による
  • 正規化するしない
    適切なテーブル設計なら認知負荷は少ない（wordpressでpostで色々な意味があると負荷が高い。）
    例.ログイン　プロファイル
    　　属性によってプロファイルが異なる場合は切り離すべき
    クエリチューニングの時そもそもEXPLAINしろって話
  • 受注テーブル完全に正規化してはダメ
    その時点のデータを保持したいから。
    顧客情報が変わってデータが変わってしまってはだめ。
    データを保持するのはプログラム
    税率を正規化すると地獄だった話

物理設計の話

  • サイジング
    オンプレの時と違って今はクラウドだから、とりあえず立てて足りなければ増やす。
  • パフォーマンス(クエリのメモリ使用量やディスクI/O発生)
    メトリクス見て調整。ディスクI/Oが発生しているかを確認
    →mysqlの場合だと inodb_buffer_pool_size メモリで処理できているか。越えるとディスクI/Oが発生するので、メモリを増やすなどして対応。
    (ChatGPT先生)
    “SELECT *” は、クエリの実行結果にテーブルのすべての列を含めるため、データベースが大規模なテーブルを扱っている場合、大量のメモリが必要になることがあります。メモリ不足の場合、クエリの実行が遅くなります。特に、データベースサーバーのメモリ容量が制限されている場合に問題が発生しやすいです。
    ディスクアクセスと一時ファイル:
    データベースサーバーは、結果セットを生成する際にディスクに一時ファイルを書き込む場合があります。これは大きなテーブルからデータを読み取る場合や、テーブルにインデックスが適切に設定されていない場合によく発生します。ディスクアクセスは通常メモリアクセスよりも遅いため、遅延の原因となります。

第4章　ER図〜複数のテーブルの関係を表現する〜

ER図

IE表記法とIDEF1X

関連エンティティ

    • データベース設計なのか
    • 作成するときは手書きが多い
    • この前、tableplusでER図作成する方法教えてもらった。手書きで作成したりすることはもうないのかな。ER図が読めればいいのかな。
    • ぽんち絵としてのER図的な作成は残っていく
    • link
    • ドメインエキスパートと話した時に出てくる言葉が大切
    • chatGPTが作ってくれそう

第5章　論理設計とパフォーマンス〜正規化の欠点と非正規化〜

結合はコストが高い操作

非正規化による解決

正規化と検索SQLのパフォーマンスはトレードオフ

非正規化はあくまで最後の手段

正規化は可能な限り高次にすることが大原則

    • 先週話した通りまずは正規化しよ
    • 環境によって全然違う
    • データベースの問題は忘れた頃にやってくる（サービス開始から終了まで）
    • データベースはデータを入れる場所（情報ではない）
    • 冗長化されてるから起こる問題もある（記事がダブったり）
    • データのフォーマットがプログラムを決めるDOAはtenpuで感じるところがあった。
    • 論理設計の担当者、大変。
    • SQLのJOINには3種類のアルゴリズムあるらしい。DBMSによって対応状況が違うらしい。
    • そうだな
    • 違う方法に任せる（OMSのランキング）
    • 設計されたものを見ることが
    • 非正規→正規は地獄

第6章　データベースとパフォーマンス

インデックス

大規模なテーブルに対して作成

カーディナリティの高い列に作成

SQLで検索条件や結合条件として使用されてる列

統計情報

    • インデックスが自動生成される時もあるらしいが、パフォーマンス向上のためではないらしい。
    • 統計情報収集は基本デフォルト設定？
    • 統計情報を凍結したいケースのデータ量がサービス開始時と終了時でほとんど変化しない特性のシステムってどんなのだろう。（社内利用する業務システム）
インデックスショットガンで書き込み性能はどれくらい劣化するのか - Qiita
この記事はミライトデザイン Advent Calendar 2021 の10日目の記事です。本日も懲りずに乱入させていただきました。昨日は@tkek321 さんのDockerについての記事でした。１つ１つ丁寧に解説してくれている記事なので、ぜひご一読をおすすめします…
qiita.com

堀越さんのキャッチアップタイム（４−６章）

 5章の「SQLのJOINには3種類ある」の話が気になる
 低レイヤの話なのであまり気にしなくて良いって話だった気がする
 Nested Loopとかの話だった気がする？

 INDEX更新のコスト、INDEX貼らない方がいい？
 使われないINDEXとかもあるので、INDEXを貼る時はパフォーマンスを考えようという話
 SQLアンチパターンの「インデックスショットガン」の話、必要ないなら貼らない

 EXPLAINは大丈夫？
 使われるINDEXを見たりするやつ

7章 論理設計のバッドノウハウ（アンチパターン）

システムの品質を決めるのは設計
戦略の失敗を先述で取り返すことはできない

主なトピック

  • 非スカラ値（配列型）→基本的に利用しない（第一正規形を守る）
  • 情報は可能な限り分割して保存
    分割しすぎたものを統合するほうが、分割しそこねたものを分割するより難易度が低い
  • ダブルミーニング
  • 単一参照テーブル
    • One True Lookup Table（OTLT）
  • テーブル分割
    • 水平分割
      例：年度ごとに分割
      →パーティショニングでおｋ
    • 垂直分割
      例：使用する特定のカラムだけ別テーブル
  • 不適切なキー
  • ダブルマスタ
    • 一つのシステム内で複数のマスタ

所感

コラム「バッドノウハウがバッドである理由」

設計の失敗について

大半のケースにおいては駄目設計が生まれる理由は「何も考えていない」ことによるもの

「そもそも知らない」ということも含みそう
おかしいと思ったら方法がないか模索する癖をなくさないようにしたい

単一参照テーブル

  • SQLアンチパターンでいうところのEAVのことよね
  • 何度かお目にかかっているがSQLが割と面倒

テーブル分割とデータマート

  • データマートは同期の問題がある。
    • トリガーとかLaravelのイベントである程度は対応できそう
    • が、乱用されると追えなくなりそう
    • OMSの検索テーブルとか割と近しい問題がある気がする
  • 水平分割はパーティショニング、垂直分割はシャーディングが良さそう？
    • いずれも必要なほど大規模なデータを扱うシステムに関わったことがほぼないので分からない
      （本来パーティショニングで対応すべきものをテーブル分割したものは見たことはある）
    • 余談：GCPのSpannerはマルチマスタなのでシャーディングが得意らしい
  • 垂直分割の例で出ていた特定のカラムだけ〜ってのはインデックス貼るだけでは駄目なんだろうか？
    • 古いMySQLでは難しそうだけど、最近はPostgresみたいにインデックスを複数貼れるのは普通になってきているのでは？
    • それでも太刀打ちできないほどのレコード数（数千〜億以上）みたいな話？
      • OMSはそこまでのデータ扱わないけど、モールはもしかすると考えないといけなそう？
  • マテリアライズド・ビュー
    • まだ使ったこと無い
    • 便利そうであると同時に毒にもなりそう

キーについて

  • 最初からデータ型を固定長文字列型に揃えるべき
    • UUIDだったらアプリ側で払い出せるし、最近はRDBもUUID型をサポートしているので順序が重要でなければUUIDで良さそう
    • 固定長数値をint型で渡す設計をするようなAPIは滅ぼして欲しい（愚痴）

ダブルマスタ

  • 流石になくない？と思ったけど…
    • 某銀行案件とかだと普通にありそう
    • 無理やり複数属性の会員テーブルをUNION ALLしてログイン判定するみたいなシステムあったわ…
  • ビルメンの場合は一つのシステム内に複数のマスタはないけど、複数サービスでマスタを共有せざるを得ない設計はある
    • ↑はデータ設計のミスだと思うし、使っている文字列が2つのシステムで微妙に違ったりして非常に辛い

ディスカッション

    • あんまりDB触ったこと無いから実感が少ない
    • アンチパターンに気付けるようになりたい
    • ダブルマスタ→見覚えがある
      • CSVから取り込んでDBに保存する処理→商品テーブルが２つになったことがあった
    • 会員データのIDが数値に見えたけど、実はゼロ埋めされた文字列だったみたいなことはあった
      • 外部連携の際はフォーマットとか気にしないといけなそう
    • パーティションはモールで使うことになるかも？
      • カーディナリティが低いほうがパーティションキーに適している
    • シャーディングはいまいちイメージがわかない
    • 業務と照らし合わせて読むことはできていなかった
    • 営業さん向けの資料の作成時にダブルマスタ（マルチマスタ）的な事象はであったことがある
      -「 キーには固定長文字列」という発想はなかった
    • シャーディングとか実際あまり触ったこと無い
      • ソシャゲではよく聞く
    • SQLアンチパターン含め、書かれたタイミングの問題もありアプリの事情（必要悪みたいなもの）を考慮していないことがある
    • IJで扱う規模の範囲だとINDEXで大体解決するかも
    • JSON型と配列型の使いどころ：RDBMSの進化に合わせて読み替える

8章 論理設計のグレーノウハウ

主なトピック

  • 代理（サロゲート）キー
    • 必要な時点でそもそも設計が…？
    • オートナンバリングの欠点
  • 列持ちテーブル
    • メリット
      • シンプルな設計
      • 入出力のフォーマットに合わせやすい
    • デメリット
      • 列の増減が難しい
      • 無用のNULLを使わなくてはならない
    • 行持ちテーブルで保持して、パフォーマンスに問題が出たら検討する
  • アドホックな集計キー
  • 多段ビュー
  • データクレンジング

所感

サロゲートキー

  • 自分は「とりあえずサロゲートキー」やってしまいがち…
  • Laravelは「ID Required」がデフォルトだし、複合主キーは色々辛い
  • モールは結局どうしたんだっけ？
  • UUIDv4ならアプリ側で作っても良くない？

列持ちテーブル

  • こいつには個人的な恨みがめっちゃあるので全否定派

アドホックな集計キー

  • 別テーブル？View？SQLのCASE式？
    • Case式はちょっと辛そうなのでやめたい
    • 使うなら別テーブル or View
    • それこそマテビュー使えるなら使ったほうが良さそう

多段ビュー

  • これはマジで地獄だし、多段ビューの追加を制御できないのであればビューをそもそも禁止というのもまぁ納得
  • EasyではなくSimpleにしておく

データクレンジング

  • この考えを学んでから大分楽になった
  • どうしようもない駄目データをどうにかするより、1回クレンジングするなり、腐敗防止層を設けてモデリングしてから扱ったほうが実装が早く、保守性も上がるよね

ディスカッション

    • 列持ちテーブル
      • 拡張時にやったことがあるがモヤッとしていた
      • 正規化してから検討するように気をつけたい
    • モールはテナントIDとAUTO INCLIMENT（複合主キーはない）
    • 「AUTO INCLIMENTはデータモデリング不足」は大分マッチョだな
      • 原則は大事だけど、トレードオフは考えたほうが良いよね
    • 「IDが歯抜けになるのもったいないな」と思ったことがある
      • サイクリックにするデメリットも考慮しないといけない
    • 多段ビュー
      • Viewとテーブルの違い、確かに分かりづらい
        • 実データを保持しない？パフォーマンスが悪化するのは何故？
        •  多段ビューはINDEXが効かない、1段だけであれば大丈夫
        •  ビューはクエリで作成される一時テーブルみたいなものなのでINDEX効かないのはなんとなくイメージできる
      • ビュー、サブクエリの差ってなんだろう？
      • 列持ちは増えたり減ったりしないなら良いのでは？

9章　一歩進んだ論理設計

主なトピック

  • リレーショナルデータベースのアキレス腱（木構造）
    • 隣接リストモデル
      • 更新や検索のクエリが複雑になり、パフォーマンスも悪い
    • 入れ子集合モデル
      • 更新時のパフォーマンスが課題
    • 入れ子区間モデル
      • リソースと桁数の問題がある
    • 経路列挙モデル
      • 更新が少なく、大量データの高速な検索が必要なケースに向いている
      • 正規表現が使えるDBとの親和性が高い
  • 各モデルのまとめ

所感

  • RDBMSが数学的な知識に基づいているのを改めて感じた
  • 正直数学は高校レベルからやり直したほうがいいレベルなのでちょっと難しく感じたところがある

ディスカッション

コメント

タイトルとURLをコピーしました