達人に学ぶDB設計徹底指南書 読書メモ

はじめに

今回は、前回の記事スッキリわかるSQL入門 読書メモに引き続き、達人に学ぶDB設計徹底指南書を読みましたので、読書メモを作成しました

正直、この記事を読んでもらえれば、本書を読むのがかなり楽になるんじゃないかっていうくらい要点をしっかり抜き出したので、本書を買おうか迷っている人や、読んでみたいけどとっつきにくいなと思っている人が、隙間時間で効率よく要点を押さえてもらえたら幸いだと思っています

学んだこと

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

システムとデータベース

• データ処理としてのシステム
  • データベース(Database) : データを整合的に保持して、いつでも簡単に利用可能な状態にしておく為のシステム
  • DBMS(Database Management System) : データベースを管理する為のシステム
  • データベースを使わないシステムは、この世に存在しない
• データと情報
  • データとは、ある形式(フォーマット)に揃えられた事実のことである
  • 情報はデータと文脈を合成して生まれる
    • 情報という概念はデータとは異なり、データからある文脈なり観点なりに従って集約したり加工したりしたものを指す

データベースあれこれ

• データベースの代表的なモデル
  • リレーショナルデータベース(Relational Database/RDB)
    • 関係データベースとも呼ばれ、現在最も広く利用されているデータベース
    • IT業界では、特に断りがなければ「データベース」といえばRDBを指す
  • データベースのモデルが異なれば、データフォーマットや設計技法も異なる
• DBMSの違いは設計に影響するか？
  • 主なDBMSとして、Oracle Database, SQL Server, DB2, PostgreSQL, MySQLなどがある
  • DBMSは、データベースのモデルを具体的に表現したものに過ぎない
    • モデルを実際に表現することを実装(implementation)と呼ぶ
  • そのため、DBMSが異なっても、基本的には設計の方法は影響を受けない
  • リレーショナルデータベース(RDB)を管理するシステムを、RDBMS(Relational Database Management System)と呼ぶ

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

• システム開発の設計工程
  • ①要件定義
    • システムが満たすべき機能やサービスの水準、すなわち要件を決める工程
  • ②設計
    • 定義された要件を満たすために必要なシステムを作るための設計を行う工程
  • ③開発(実装)
    • 設計書に従ってシステムを実際に作る工程
  • ④テスト
    • 実装によって組み上がったシステムが、本当に実用にたえる品質であるかを試験(テスト)する工程
  • システム開発は、要件定義 → 設計 → 開発 → テストの順で行われる
• 設計工程と開発モデル
  • システム開発の進め方(開発モデル)は大きく2通りの方法がある
  • ①ウォーターフォールモデル
    • 滝の流れのように、要件定義 → 設計 → 開発 → テストを上流から下流に一方向に進む開発サイクル
    • 基本的に逆戻りができない
    • 大規模なシステム開発で採用されることが多い
  • ②プロトタイピングモデル
    • 最初に試作品を作り、ユーザーにフィードバックをもらい、それを取り入れ再度改良するを繰り返す開発サイクル
    • 小規模のシステムに対して適用される
    • このモデルを応用したアジャイル開発はWeb系システムの開発で利用される

設計工程とデータベース

• DOAとPOA

  • システムにおいて大半のデータ(少なくとも永続的に使用されるデータ)はデータベース内に保持される
    • そのため、データ設計とはデータベース設計とほぼ同義である
  • データ設計がシステムの品質を大きく左右する
    • ソフトウェアというのは「データの流通機構」であり、どのようなプログラムが必要になるかは、どのデータをどういうフォーマットで設計するかに左右される
  • DOA(Data Oriented Approach)
    • データ中心アプローチ
    • システムを作る際に、プログラムよりも前にデータの設計から始める方法論のこと
    • データを1箇所で管理するので、データがあまり変化しない(永続的)
    • よって、データの意味や形式が先に決まっていれば、複数のプログラムで共用することも容易になり、要件の仕様変更にも柔軟に対応できる
    • 近年のソフトウェア開発では主流の考え方
  • POA(Process Oriented Approach)
    • プロセス(プログラムと同義)中心アプローチ
    • かつてのシステム開発の主流の考え方(時代遅れな考え方)
  • 最初にデータがある。プログラムはその次にできる
    • データ設計(データベース設計)は、システムの品質を決める最も重要な要因といっても過言ではない
    • データ設計においてセオリーを踏み外した設計(バッドノウハウもしくはアンチパターン)を行うと、システムの機能/非機能の品質を致命的に損なうことになる(7,8章参考)

• 3層スキーマ

  • スキーマ(schema)
    • データベース設計において、データベースのデータ構造やフォーマットという意味で使われる
    • スキーマは、一般的に3つのレベルに分けられる
  • ①外部スキーマ(外部モデル)
    • システムの利用者であるユーザーから見て、データベースがどのような機能とインターフェースを持っているかを定義するスキーマ
    • いわば、ユーザーから見たデータベースのこと
  • ②概念スキーマ(論理データモデル)
    • データベースに保持するデータの要素および、データ同士の関係を記述するスキーマ
    • 外部スキーマがユーザーから見たデータベースだとすると、概念スキーマは開発者から見たデータベースのこと
    • 概念スキーマの設計を論理設計と呼ぶ
  • ③内部スキーマ(物理データモデル)
    • 概念スキーマで定義された論理データモデルを、具体的にどのようにDBMS内部に格納するかを定義するスキーマ
    • いわば、DBMSから見たデータベースのこと
    • 内部スキーマの設計を物理設計と呼ぶ
    

• 概念スキーマとデータ独立性

  • 概念スキーマを定義せず、外部スキーマや内部スキーマに吸収させる「2層スキーマ」にすることもある
  • システムが大きくなると2層スキーマは、変更に対する柔軟性がないため非効率な部分が多い
  • 概念スキーマは、外部スキーマと内部スキーマの間に位置することで、両者の変更が互いに影響し合わないようにする緩衝材の役割を果たす
  • また、このようなスキーマの独立性をデータ独立性と呼ぶ
  • 概念スキーマは、データ独立性を保証するためにある

第2章 論理設計と物理設計

概念スキーマと論理設計

• 概念スキーマを定義する設計を、論理設計と呼ぶ
  • システム世界の論理とは、「物理層の制約にとらわれない」という意味で使われる
  • 論理設計が物理設計よりも前に位置しているのは、料理で言うところの「まずどのような料理を作るか決めて、それに合わせて器を決める」ようなイメージ
• 論理設計のステップ
  • 論理設計で行うことは、現実世界に存在する数多くのデータから、RDBにおいて「何をどのようなフォーマットで保存するか」を決めること
  • エンティティの抽出→エンティティの定義→正規化→ER図の作成の4ステップで行う
• エンティティの抽出
  • エンティティ(entity)
    • 日本語で「実体」の意
    • 現実世界に存在するデータの集合体を指す言葉で、物理的実体を伴ったもの(社員や車など)や、物理的実体を伴わないもの(税や注文履歴など)も含まれる
• エンティティの定義
  • エンティティは、データを属性という形で保持する(二次元表における「列」と同義)
  • テーブルにおいて、「どのような列を持つか」を定義する
• 正規化(normalization)
  • エンティティ(テーブル)について、システムでの利用がスムーズに行えるよう整理する作業のこと
  • 特に、更新(データの登録、変更、削除)が整合的に行えるように、エンティティのフォーマットを整理することが重要な目的
  • 単にエンティティを抽出し、属性を定義しただけの状態では、まだそのエンティティはシステムでの利用に耐える状態にはなっていないので正規化が必要
  • 正規化は、データベースの論理設計を理解する鍵と言っても過言ではない
• ER図の作成
  • ER図 : Entity-Relationship Diagramの略
  • 正規化を行うと、大量の分割されたエンティティがうまれ、こうした大量のエンティティ同士の関係を何の助けもなしに理解することは困難
  • その問題を解決するために考案された方法で、いわば「エンティティの見取り図」

内部スキーマと物理設計

• 物理設計のステップ
  • 物理設計は、論理設計の結果を受けて、データを格納するための物理的な領域や格納方法を決める工程のこと
  • ハードウェアやDBMSの個々の製品についての知識が必要になる
  • 物理設計は、テーブル定義→インデックス定義→ハードウェアのサイジング→ストレージの冗長構成決定→ファイルの物理配置決定の5ステップで行う
• テーブル定義
  • 論理設計で定義された概念スキーマをもとに、それをDBMS内部に格納するための「テーブル」の単位に変更していく作業のこと
  • 論理設計で作られるERモデルを「論理モデル」と呼ぶのに対して、このフェーズで作られるモデルを「物理モデル」と呼ぶ
• インデックス定義
  • インデックス(索引)は、RDBにおいてテーブルと並んで重要な概念
  • 本の索引のイメージで、ある単語を探す際に直接該当ページに飛んでくれるので、探す手間を省略してくれるので、パフォーマンスの向上に繋がる
• ハードウェアのサイジング
  • 「サイジング」という言葉は、「大きさを決める」という意味で、システム開発では2種類の意味で使われる
    • 一つは、データの規模(キャパシティの見積もり) についてで、システムで利用するデータを見積り、それに十分な容量のストレージ(記憶装置)を選定する
    • もう一つは、パフォーマンスに関してで、サイズを測る対象はサーバーのCPUやメモリ。システムが十分な性能を発揮できるだけのスペックのCPUやメモリを持ったサーバーを選定する
  • サイジングはキャパシティとパフォーマンスの2つの観点から行う
  • データベースの性能問題の8割はディスクI/Oによって起きる
  • キャパシティのサイジング
    • 物理的なテーブル定義やインデックス定義が終わった後に、データベース内に格納するデータ量を算出する。ここでのデータ量は、テーブル以外にも、テキストや画像、HTMLといった様々なファイル分も加算する必要がある
    • また、システムのサービス終了時のデータ増加率も見積もる必要があるが、実際の話、正確に見積るのが難しい場合が多いので、以下の2つのアプローチをとる
      • ①安全率を大きくとって、余裕を持たせたサイジングを行う
      • ②もし後で容量が不足した場合に、簡単に記憶装置を追加できるような構成にしておく(スケーラビリティが高い構成)
  • パフォーマンスのサイジング
    • 「どれだけ速いか」を示す処理時間と、「どれだけ多いか」を示すスループットの二つの指標を用いて性能要件を定義する
    • また、「どの程度の処理を行うと、どの程度のハードウェアリソースを消費するのか」の基礎数値を、以下の二つの方法で測定する
      • ①類似の稼働中システムのデータを流用する
      • ②開発の初期段階でプロトタイプシステムを構築して、性能検証を実施する
    • 精度の高いサイジングは難しいので、実施時には必ず安全率をかけて、スケーラビリティの高い構成を組むようにする
  • サイジングは物理設計の中でも難易度の高いタスクであり、これがこなせるとDBエンジニアとしてはエース級
• ストレージの冗長構成
  • ストレージは、データベースのデータを保持する媒体で、一般的にHDD(ハードディスク)を使用する
  • データベースに保管されるデータは、業務の基幹データなので、ここを失うことは絶対に許されない
  • そのため、RAIDを用いて、可能な限り高い耐障害性を持つようにシステムを構築する必要がある
  • RAID(Redundant Array of Independent Disks)
    • 直訳すると「独立したディスクの冗長配列」で、複数のディスクを束ねて仮想的に一つのストレージとする技術のことで、この単位でまとめられたディスクをRAIDグループと呼ぶ
    • 基本的な考え方は、複数のディスクに同じデータを書き込んで冗長化すること
    • RAIDはシステムの信頼性と性能を共に改善できる技術
  • 代表的なRAIDの種類
    • RAID0
      • 別名ストライピング
      • データを異なるディスクに分散(冗長性はない)
    • RAID1
      • 別名ミラーリング
      • 2本のディスクに全く同じデータを持つ(冗長性は1本の時に比べ2倍になり、性能は1本の時と変わらない)
    • RAID5
      • パリティ分散と呼ばれ、最低3本で構成し、データとともにパリティと呼ばれるエラーチェック情報も各ディスクに均等に分散して格納する
      • ディスクが壊れたとしても、パリティから実データを復元することが可能
    • RAID6
      • RAID5の上位版で、P+Q方式を採用
      • RAID5が1本のパリティディスクを使用することで耐障害性を上げていたのに対して、RAID6は、2つの独立したパリティ情報を持つ
      • よって、RAID6は2つのディスクが同時に故障してもデータを保護・復旧することができる
    • RAID10
      • 別名「RAID1+0」で、RAID1とRAID0を組み合わせたもの
      • RAID1の高信頼性とRAID0の高速性を両立させた良いとこどりな方法
      • 欠点としては、必要になるディスクの本数が多いためコストが高い(最低4本必要)
  • データベースのRAIDは少なくともRAID5で構成する。お金に余裕があればRAID10。RAID0は論外
• ファイルの物理配置
  • データベースのファイルをどのディスク(またはRAIDグループ)に配置するかを考える
  • 最近のDBMSでは自動化が進んでおり、エンジニアが意識しなくてもある程度はDBMSが自動的に配置してくれるが、基本的な考え方を理解していないと問題が起きた時の対処が困難になる
  • データベースに格納されるファイルは以下の5種類に大別できる
    • 1.データファイル
      • ユーザーがデータベースに格納するデータを保持するためのファイル
      • 業務アプリケーションがSQLを通じて参照および更新を行うファイルでもある
    • 2.インデックスファイル
      • テーブルに作成されたインデックスが格納されるファイル
      • 開発者が意識する必要がなくインデックスを使うかどうかは、DBMSが内部で勝手に判断する
    • 3.システムファイル
      • DBMSの内部管理用に使われるデータを格納されるファイル
      • 基本的に業務アプリケーションやユーザーがアクセスすることはない
    • 4.一時ファイル
      • DBMS内部での一時的なデータを格納するためのファイル
      • 1~3のファイルと異なり、継続的にサイズが増加することはない
    • 5.ログファイル
      • テーブルのデータに対する変更を受け付けた場合に一旦変更を保存するためのファイル
      • MySQLでは「バイナリログ」、PostgreSQLでは「トランザクションログ」と、DBMSによって呼び方が変わる

バックアップ設計

• バックアップの基本分類
  • バックアップは、基本的にはファイルのコピーで行う
• 完全/差分/増分
  • データのバックアップ設計は、以下の3つの方式を組み合わせて行う
  • ①フルバックアップ(full backup)
    • ある時点でそのシステムで保持されている全てのデータをバックアップする方式
    • 以下の3つの欠点がある
      • 1.常に全てのデータのバックアップを取得するので時間がかかる
      • 2.バックアップするデータ量が多いため、ハードウェアリソースへの負荷が高い
      • 3.サービスの停止が必要(DBMSやオンライン処理を止めて行うため)
  • ②差分バックアップ(differential backup)
    • 最後のフルバックアップ以降に変更された全てのデータをバックアップする
    • 例えば、月曜日にフルバックアップをとり、日曜日に障害が起きた場合は、リカバリの際に月曜日と前日の土曜日のファイルが必要になる
    • バックアップコスト(バックアップデータ量)とリカバリコスト共に3つのうちの中間的な位置にある
  • ③増分バックアップ(incremental backup)
    • 最後のバックアップ(フル、差分、または増分)以降に変更されたデータのみをバックアップする方式
    • 例えば、月曜日にフルバックアップをとり、日曜日に障害が起きた場合は、リカバリの際に月曜日から土曜日までのファイルが必要になる
    • バックアップコストが3つのうち最小になるが、リカバリコストが最も高い
• バックアップ方式は、フルバックアップ+差分バックアップまたは、フルバックアップ+増分バックアップが一般的

リカバリ設計

• リカバリとリストア
  • バックアップファイルを戻す作業をリストア、そのファイルに対してトランザクションログを適用して変更分を反映する作業をリカバリと呼ぶ
  • リストアおよびリカバリの手順は以下の3段階になる
    • 1.フルバックアップのファイルをデータベースに戻す(リストア)
    • 2.差分または増分バックアップしていたトランザクションログを適用する(リカバリ)
    • 3.データベースサーバーに残っているトランザクションログを適用する(ロールフォワード)

第3章 論理設計と正規化〜なぜテーブルは分割する必要があるのか？

テーブルとは何か？

• テーブルとは、共通点を持ったレコードの集合のこと
• テーブル名は、英語なら複数形もしくは複数名詞で書く

テーブルの構成要素

• 行と列
  • 行のことをレコード、列のことをカラム(または属性)という
• キー
  • 主キー(primary key)
    • テーブルにおいて必ず一つ存在しなければならず、かつ一つしか存在しない
    • レコードを一意に識別するためのもの
  • 外部キー(foreign key)
    • 2つのテーブル間の列同士で設定する
    • 外部キーの役割は、親テーブルに存在しないデータが、間違って子テーブルに登録されないように防止すること
    • 上記の子テーブルに課す制約を参照整合性制約と呼ぶ
    • 外部キーは人間の親子関係と同じ
    • 外部キーが設定されている場合、データの削除は子から順に行うのが吉
• 制約
  • テーブルに対しても制約をつけることができ、以下の3つが代表的
  • ①NOT NULL制約
    • NULLのデータを登録しようとしたり、NULLに更新しようとするとエラーとなる制約
    • NULLというのはSQL上で扱うには色々と問題を引き起こす厄介なもの
    • テーブル定義において、列には可能な限りNOT NULL制約を設定する
  • ②一意制約
    • ある列の組について一意性を求める制約
    • 主キーがテーブルにつき一つしか設定できないのに対して、一意制約は複数設定が可能
  • ③CHECK制約
    • ある列の取りうる値の範囲を制限するための制約
• テーブルと列の名前
  • テーブルや列の名前に日本語はご法度

正規化とは何か？

• 正規形の定義
  • 正規形(normal form)とは、データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式のこと
  • 正規形のレベルは第5まであるが、普通は第3正規形まで理解すれば十分
• 正規化とは、テーブルの全ての列が、関数従属性を満たすように整理していくこと
• 正規化とは、現実世界の実体間にある階層の差を反映する手段でもある

第1正規形〜スカラ値の原則

• 第1正規形の定義
  • 第1正規形の定義とは、一つのセルに一つの値しか含まれないというもの
  • 一つのセルに一つだけの値が含まれている時、この値のことをスカラ値と呼ぶ
  • 主キーは一部であってもNULLを含んではならない
• なぜ一つのセルに複数の値を入れてはダメなのか？〜関数従属性
  • セルに複数の値を許してしまうと、主キーが各列の値を一意に決定できない
  • 関数従属性(functional dependency)
    • Y = f(X) : 入力(X)に対して出力(Y)を一つに決める
    • YはXに従属する : {X} -> {Y}
    • 1つのセルに1つの値の方針も関数従属性
    • テーブル設計では全ての列が関数従属性を満たすように整理していく

第2正規形〜部分関数従属

• 第2正規形の定義
  • 第2正規形とは、複合主キーの一部の列に対して従属する列(部分関数従属)がある時に、それらを解消し、主キーを構成する全ての列に従属性がある状態(完全関数従属)のみのテーブルを作ること
• 第2正規化とは、異なるレベルの実体(エンティティ)を、きちんとテーブルとしても分離してやる作業のこと
• 無損失分解と情報の保存
  • 正規化とは元に戻すことができる可逆的な操作
  • 情報を完全に保存したままテーブルを分割する操作のことを、無損失分解と呼ぶ
  • 正規化の逆操作は結合

第3正規形〜推移的関数従属

• 推移的関数従属
  • {会社コード、社員ID} → {部署コード} → {部署名}のように、主キーに従属している列に従属しているといった段階的な従属関係を推移的関数従属という({部署コード} → {部署名}の部分)
• 第3正規形の定義
  • 第3正規形も第2正規形と同様に、上記の推移的関数従属を解消するためテーブルを分割して、全てのテーブルについて非キー列はキー列に対してのみ従属するようにすること

ボイスーコッド正規形(BCNF / Boyce-Codd normalform)

• 3次と4次の狭間
  • ボイスーコッド正規形は、第3正規形をより厳密にしたものと考えられており、第3.5正規形とも呼ばれ、非キーからキーへの関数従属をなくした状態にすること

第4正規形

• 第4正規形は、独立な多値従属性(キーと集合の対応)が複数存在するテーブルを分割することで作られる

第5正規形

• 第5正規形とは、関連と関連エンティティを一対一対応になるようテーブル分割することで作られる
  • 第5正規形の適用によるテーブル分割と、多対多の関係を解消するための中間テーブルの作成は、異なる問題を解決する為のものだが、結果として似たようなテーブル構造をもたらすことがある

正規化についてのまとめ

• 正規化の三つのポイント
  • 正規化とは、更新時の不都合/不整合を排除するために行う
  • 正規化は、従属性を見抜くことで可能になる
  • 正規形はいつでも非正規形に戻せる
• 正規化は常にするべきか？
  • 第3正規形までは原則として行う
  • 関連エンティティが存在する場合は、関連とエンティティが1対1に対応するよう注意する
• 正規化を行う利点
  • データの冗長性が排除され、更新時の不整合を防止できる
  • テーブルの持つ意味が明確になり、開発者が理解しやすい
• 正規化を行う欠点
  • テーブルの数が増えるため、SQL文で結合を多用することになり、パフォーマンスが悪化する

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

• 正規化によって、テーブルを整理していくと、どんどんテーブルの数が増えて管理しきれなくなる
  • 実際の業務システムの開発とかだと何百という数のテーブルが作られる
• こうした多数のテーブルを管理するために、「それぞれのテーブルがどういう意味を持っていて、テーブル同士が互いにどういう関係にあるのか」ということを明らかにするために、ER図(Entity-Relationship Diagram : 実体関連図) を用いる
• ER図の代表的な書き方に、IE(Information Engineering)表記法とIDEFIXがある
• 相手のエンティティと対応するレコード数をカーディナリティという(要は2つのエンティティ間の関係の数を示すもの)
• 1対1
  • あまり見かけない
  • 二つのテーブルのレコードが1対1に対応するということは、二つのテーブルの主キーが一致するということなので、その場合は一つのテーブルにまとめても問題ない
  • 正規化の過程で1対1のテーブルが作られることはない
• 1対多
  • 最もよくある関連のタイプ
  • 基本的に正規化によって生まれる関連はこのカテゴリに属する
  • 厳密には「1対多」と「0または1対多」に分かれるが、二つのサブカテゴリをまとめて1対多と呼ぶ
• 多対多
  • 業務要件からテーブルを作っていくとこのテーブル群ができる
  • RDBのお約束として、基本的に多対多の関連は作ってはならない
• 独立エンティティ
  • 他のテーブルのデータに依存することなく、データを保持できるエンティティのこと
• 従属エンティティ
  • 他のテーブルにデータが存在しなければ、データを保持できないエンティティのこと
• 依存リレーションシップ
  • 外部キーにNULLを許さない
  • 例えば、会社テーブル(親エンティティ)と社員テーブル(子エンティティ)の関係で、社員が必ず会社に属さなければならない場合など
• 非依存リレーションシップ
  • 外部キーにNULLを許す
  • 例えば、部署テーブル(親エンティティ)と社員テーブル(子エンティティ)の関係で、必ずしも社員が部署に所属してなくてもいい場合など
• 多対多の問題を解決するには、関連実体(associative entity)、いわゆる中間テーブルを作成して1対多の関係を作る

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

正規化の功罪

• 正規化とSQL(検索)
  • 検索の場合、非正規化テーブルならばSQLで結合を使わなくても済む
• 正規化とSQL(更新)
  • 更新の場合、正規化を行なっていると変更箇所が1箇所で済むため、更新処理のコストが低く、かつ一定である
• 正規化と非正規化、どちらが正解なのか？
  • 正規化と検索SQLのパフォーマンスは強いトレードオフの関係にある
  • 正規化の次数が低いほど検索SQLのパフォーマンスは良くなるがデータ整合性は低く、正規化していくほどパフォーマンスが低下する代わりにデータ整合性が高くなる
  • 著者の意見としては、原則として正規化は許さないという考え
    • 非正規化は、いよいよ切羽詰まった時の最後の手段であり、基本的に正規化の次数は高ければ高い方が良い

非正規化とパフォーマンス

• 正規化(冗長性排除)によって引き起こされる性能悪化問題は、SQL構文の観点からみて大きく次の2種類のパターンに分類される
  • サマリデータの冗長性排除によるパターン
  • 選択条件の冗長性排除によるパターン
• サマリデータ(集計データ)や選択条件を冗長に保持すると正規形に違反するが、検索を高速化できる
• 非正規化することによって、SQLのパフォーマンスは向上するが、可能な限り高次に正規化を行うのが大原則
• しかし、それでもなお、実務における論理設計では性能のために非正規化が必要になる時がある
  • いかにSQLのパフォーマンスチューニングを行なったとしてもテーブル構成が正規化された状態では限界がある
  • データ構造がプログラムのコードを決定するのであって、その逆はない
• 正規化は、教科書的/機械的に実施できるのに対して、非正規化は、様々なトレードオフを考慮しながら慎重に実施する必要がある難しい仕事である

冗長性とパフォーマンスのトレードオフ

• 更新時のパフォーマンス
  • 例えば、「商品数」というサマリデータを追加した「受注テーブル」があるとすると、定期的に商品数の列の値を最新にするための更新処理が必要になる(負荷がかかってしまう)
• データのリアルタイム性
  • 上記の「商品数」というサマリデータにどの程度の最新性が求められているのかといった要件について、反映周期が短ければ短いほど嬉しいユーザーと、性能問題を考慮して、両者のバランスが取れる平衡点を見つける必要がある
• 改修コストの大きさ
  • DOAの原則のため、データモデルの変更は、コードベースの変更に比べて、非常に改修コストが大きくなる
  • 論理設計をする際には、「システムの品質は(開発が成功するかどうかは)今ここで決まる！」という気概を持って望む必要がある
  • かつ、正規形の理論を理解しており、それによって生じる様々なトレードオフを知り尽くした上で、あらゆる要件を同時に満たせる平衡点を探し出せる能力が必要とされる
  • よって、論理設計を行う人間は、開発チームのエースでなければならない
• 残念なことに、論理設計には物理設計の知識が必要である

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

データベースのパフォーマンスを決める要因

• インデックスは、SQLチューニングの手段として非常にポピュラーでよく使う
  • インデックスとは、プログラミング言語的な表現だと(x, α)という形式の配列
    • xはキー値、αはそれに結びつく情報(実データか、あるいはそれへのポインタ)
  • また、インデックスは、DBMS内にテーブルとは独立に保持されるオブジェクトである
• 統計情報は、SQLの最適なアクセスを見つけるために必要な地図情報のこと
  • 車のカーナビと同じイメージ
  • これが最新でなければ、DBMSは最短のアクセスパスを選択できない
  • 最近のDBMSではコストベースといって、DBMSに経路選択を一任するアーキテクチャが主流になっている

インデックス設計

• インデックスがSQLのパフォーマンス改善においてポピュラーな手段である理由3つ
  • ①アプリケーションのコードに影響を与えない(アプリケーション透過的)
    • インデックスを使うかどうかは、DBMSが自動的に判断する
    • よって、インデックスを使う場合、単にデータベース側にインデックスを作成すれば良いだけで、アプリケーションプログラムの変更が必要ない
  • ②テーブルのデータに影響を与えない(データ透過的)
    • インデックスを作成することでテーブルに格納されているデータの中身が影響を受けることはない
    • また、テーブルの構造も変化しない
  • ③大きな性能改善効果
    • インデックスの性能が、データ量に対して線形よりも緩くしか劣化しないので、性能改善効果は劇的である
• インデックスには色々あるが、まずはB-treeインデックスを覚えておくとよい
• B-treeインデックスの長所
  • B-treeインデックスの長所は、平均点の高さである
  • この特徴は他のインデックスにはない特徴で、他のインデックスはいずれも一長一短なものがほとんどで、汎用性に欠ける
  • B-treeインデックスはオール4の秀才で、総合評価で1位を取るタイプである
• B-treeインデックスの構造
  • B-treeは、木構造でデータを保持し、最上位のレベルをルート、最下位のレベルをリーフ、それ以外の中間レベルを内部ノードという
  • ①均一性
    • B-treeインデックスは、どのリーフもルートからの高さ(距離)が一定な平衡木であるため、探索を同じ計算量で行える
  • ②持続性
    • B-treeといえども、更新が繰り返されることによって性能劣化は発生するが、B-treeの性能劣化は長期的にみても非常に緩やか
    • B-treeインデックスの性能は、O(log n) : nはデータ量となる
  • ③処理汎用性
    • B-treeインデックスは、挿入、更新、削除のコストも、検索と同じくらいの探索速度となり、かつデータ量が増えても性能劣化の度合いが緩やかである
  • ④非等置性
    • B-treeインデックスは、等号(=)による検索のみならず、不等号(<, >, <=, >=)やBETWEENといった範囲検索の条件に対しても高速化を可能にする
    • 否定条件(<>, !=)を用いた検索条件に対しては、特定のノード以外の全てのノードが該当してしまうので、B-treeは効果を持たない
  • ⑤親ソート性
    • B-treeインデックスは、構築時にキー値をソートして保持する
    • そのため、B-treeインデックスが存在する列をORDER BY句のキーとして指定すると、ソート処理をスキップできる(データベースのパフォーマンスにとって鬼門の一つであるソート処理をチューニングする大きな助けになる)

B-treeインデックスの設計方針

• B-treeインデックスはどの列に作れば良いか？
  • 1.大規模なテーブルに対して作成する
  • 2.カーディナリティの高い列に作成する
  • 3.SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する
• B-treeインデックスとテーブルの規模
  • データ量が少ない場合は、フルスキャンの方が高速になる
  • 目安としてはレコード数が1万件以下の場合は、B-treeインデックスの効果はほぼない
• B-treeインデックスとカーディナリティ
  • B-treeインデックスを作成する列として適不適を判断するのに最も重要な情報が、特定の列の値がどのくらいの種類の多さを持つかということを表す概念であるカーディナリティ
  • B-treeインデックスを作る時は、カーディナリティの高い列を選ぶのが基本
  • 目安は、特定のキー列を指定したときに、全体のレコード数の5%程度に絞り込めるだけのカーディナリティがあること
  • カーディナリティの注意点2つ
    • 1.複合列に対してインデックスを作成する場合、カーディナリティは対象の複合列の組み合わせで考える
    • 2.カーディナリティが高くても、特定の値にデータが集中しているような列は向いていない
  • カーディナリティが高い列ほどインデックス効果は高い。ただし、値が平均的に分散しているのがベスト
• B-treeインデックスとSQL
  • SQLで検索条件や結合条件として使用されない列にインデックスを使用しても無意味になってしまうので、代表的な以下のパターンは気を付ける
    • 1.インデックス列に演算を行なっている
      • インデックスを作成した列はSQLにおいて裸で用いるのが原則
    • 2.索引列に対してSQL関数を適用している
      • 1と同じ理由
    • 3.IS NULL述語を使っている
      • B-treeインデックスは一般的にNULLについてはデータの値とはみなさずに保持していない
    • 4.否定形を用いている
      • 否定形はインデックスを利用できない
    • 5.ORを用いている
      • ORを用いた場合もインデックスを利用できない
      • INで書き換えることで回避できる
    • 6.後方一致、または中間一致のLIKE述語を用いている
      • LIKE述語を使うときは、前方一致検索の場合のみ検索が使用される
    • 7.暗黙の型変換を行なっている
      • 列とデータ型の異なる値を条件に指定した場合、DBMSは内部的に暗黙の型変換を行うが、その場合はインデックスは使用されなくなる
      • 上記を回避するには、明示的に条件に使用する値のデータ型を列のデータ型に合わせてやる必要がある
• B-treeインデックスに関するその他の注意事項
  • 主キーおよび一意制約の列には、作成されるときに内部的にDBMSによってB-treeインデックスが作成されている為、作成不要
  • B-treeインデックスは更新性能を劣化させる
  • 定期的なメンテナンスを行うことが望ましい

統計情報

• オプティマイザと実行計画
  • 統計情報は、「テーブルやインデックスなどのデータ」についてのデータ、すなわちメタデータである
  • DBMSがSQL文を受け取ると、パーサ→オプティマイザ→カタログマネージャ→オプティマイザ→テーブルの順で処理が進む
    • パーサ(parser) : SQL文が適法な構文であるかチェックする
    • オプティマイザ(optimizer) : DBMSの頭脳的な役割で、実行計画を決める
    • カタログマネージャ : 統計情報を管理するモジュールで、図書館の司書のような役割
  • SQLの実行計画は、DBMSがお任せで選ぶ
• 統計情報の設計指針
  • 統計情報収集のタイミングの基本的指針は、データが大きく更新された後、なるべく早くではあるが、データの更新量が少ない場合などは、システムの使用者が少ない夜間帯に実施するのが原則

第7章 論理設計のバッドノウハウ

論理設計の「やってはいけない」

• システムの品質を決めるのは設計であり、プログラミングというのは、設計をプログラミング言語に翻訳する作業であって、プログラミング自身がシステムの品質を左右することは相対的に稀である
• 「戦略の失敗を戦術で取り戻すことができない」というように、システム開発においては、設計が戦略で、プログラミングが戦術にあたる

非スカラ値(第1正規形未満)

• 配列型による非スカラ値は原則利用せず、第1正規化を守るようにする
• 情報は可能な限り分割して保存するのが良い。ただし、意味を壊してはいけない

ダブルミーニング

• 列は変数ではない。一度意味を決めたら変更不可

単一参照テーブル

• 構造的に同じ別々のテーブルを一つのマスタテーブルにまとめられたものを、単一参照テーブルと呼ぶ
• 単一参照テーブルは、メリットよりもデメリットが多いので、基本的には利用しないようにする
• テーブルにポリモルフィズムは要らない

テーブル分割

• テーブル分割は、以下の２種類に分かれる
  • ①水平分割
    • レコード単位にテーブルを分割する
    • 分割する意味的な理由が無かったり、拡張性に乏しかったり、他の代替手段(パーティション機能)があったりするので、RDBでは原則禁止とされる
  • ②垂直分割
    • 列単位にテーブルを分割する
    • 水平分割同様に、分割する意味がないので原則利用しない(以下の集約を代わりに利用)
• 集約は、テーブル分割の代替案になる方法で、以下の２種類に分かれる
  • ①列の絞り込み
    • 保持する列を絞った小規模なデータマート(Data Mart) と呼ばれるテーブルを作成する
    • 実際の開発現場でよく利用されるが、ストレージ容量の圧迫やデータ同期の問題を考慮する必要がある
  • ②サマリテーブル
    • 集約関数によってレコードを集約した状態で保持する
    • 列の絞り込み同様、ストレージ容量の圧迫やデータの同期問題の考慮が必要

不適切なキー

• 可変長文字列は不変性がないためキーには不向き
• 同じデータを意味するキーは同じデータ型にすべきで、キーには固定長文字列の「コード」列が望ましい

ダブルマスタ

• ダブルマスタとは、名前の通り、同じ役割を果たすはずのマスタテーブルが二つ存在するようなケースのこと
• ダブルマスタは、SQLを複雑にしパフォーマンスを悪化させる
• ダブルマスタが生じる理由は、もともと別のシステムで利用されていたマスタ同士が、システム統合によって同じドメインに存在するようになったから

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

違法すれすれの「ライン上」に位置する設計

• バッドノウハウとはっきり断定はできないが、無神経に使うと開発や運用に支障をきたすような、毒を含んだ設計であるグレーノウハウも存在する

代理キー〜主キーが役に立たないとき

• 主キーが決められない、または主キーとして不十分なケースは大きく以下の3パターンが存在する
  • 1.そもそも入力データに主キーにできるような一意キーが存在しない
    • 言語道断なケースで、前段階のアプリケーションできちんとデータクレンジング(データの掃除)を行なってからデータを投入するようにする
  • 2.一意キーはあるが、サイクリックに使いまわされる
    • 例えば、市町村の情報を管理するテーブルで、市の統合により市町村コードが再利用されるなど
    • 市町村コードを使った履歴管理などを行えず、主キーの役割を果たせない
  • 3.一意キーはあるが、途中で指す対象が変化する
    • 例えば、C町にD村が統合される場合で、市町村コードはC町を指しているが、人口(人)の列のデータがC町とD村の合計に変わっている
    • 2のパターンと似ており、履歴管理ができなくなる
• 代理キーによる解決
  • 代理キー(サロゲートキー)とは、入力データに最初から存在している自然キーの「代理」として新たに追加するキーのこと
  • 代理キーはそもそも論理的には不要なキーであるため、一般的な原則としては、極力代理キーの使用は避けて、自然キーによる解決策を図るべきである
• オートナンバリングとは、1レコードに一意な(通常は整数型の)数値を自動的に割り振る方法で、オートナンバリングをデータベースの機能に頼らずアプリケーションで実装するのは車輪の再発明である

列持ちテーブル

• 列持ちテーブルとは、配列型を使わずに配列を模倣する論理設計のやり方
  • JavaやCといったプログラミング言語のitem[0], item[1], item[2]のようにテーブルの列を表す
  • メリットとしては、シンプルな設計になりやすかったり、アプリケーションサイドとのインターフェース設計も非常に簡単になる
  • デメリットとしては、列の増減が激しかったり、RDBにおいて非常に厄介な存在であるNULLを無用に使わなくてはならなくなる
• 原則として、列待ちテーブではなく、行待ちテーブルを採用する

アドホックな集計キー

• 例えば、都道府県別の人口を保持するテーブルがあるとして、地方別の人口を求める場合に、「地方コード」列を追加した場合、こういったキーをアドホックな(場当たり的な)キーと呼ぶ
• サイズの大きなテーブルにアドホックキーを次から次へと追加すると、パフォーマンスを劣化させる恐れがある
• 解決策として以下の3つが挙げられる
  • 1.キーを別テーブルに分離する
  • 2.ビューを使用する
  • 3.GROUP BY句の中でアドホックキーを作る

多段ビュー

• ビューとは、「クエリの缶詰」であり、保存がきく上に、ひらけば常に新鮮なデータを取り出せる
• ビューの短所は、パフォーマンスに影響を与えることと、濫用するとかえって設計と実装を複雑なものにしてしまう
• ビューに対してSQL文によってアクセスが行われたときは、ビュー定義のSELECT文を実行して、オリジナルのテーブル(基底テーブル)にアクセスしている
  • 2段階でSQLが発行されているイメージだが、厳密には、DBMSが2回のSQLをマージして効率良くしようとしている。が、それでも高コストな処理になる
• ビューの背後にあるテーブルの存在は常に意識するようにする
• 階層の深いビュー定義(多段ビュー)は、テーブルとビューの依存関係をわかりにくくし、仕様が複雑になり管理が困難になるので、原則として1段にとどめるようにする
• 「過度に複雑な作りはシステムをダメにする」という思想を表現するスローガンとして、KISSの原則がある
  • Keep It Simple, Stupid. (単純にしておけ、このバカ)

データクレンジングの重要性

• データベースを構築するにあたって、それまでの業務で利用されていたデータをデータベースに登録できる容態にすることを、データクレンジングという
• データクレンジングは設計に先立って行う
• 代表的なデータクレンジングで行う処理として、一意キーの特定と名寄せがある
• 一意キーの存在しないデータは、バッドノウハウ「不適切なキー」をも生み出す
• 名寄せとは、「似通った名前を寄せ集めて統合する」という意味で、人名や企業名の表記揺れを解消して名称を統一すること
• 名寄せをサボると、バッドノウハウ「ダブルマスタ」を生み出す

第9章 一歩進んだ論理設計〜SQLで木構造を扱う

リレーショナルデータベースのアキレス腱

• 木構造とは、名前の通り、データが「木」の形をしているような階層上の構造
• 用語の定義
  • ノード(node)
    • 木の結節点のこと
    • ノードはデータをもち、他のノードへのリンク(子ノードへの参照)も持つことができる
  • ルートノード(root node)
    • 木が始まるトップのノード
    • 木は、このルートノードを定義上一つしか持たない
  • リーフノード(leaf node)
    • 自分よりも下位のノードを持たない「終着点」のノード
  • 内部ノード(inner node)
    • ルートでもリーフでもない中間ノードで、少なくとも一つの子ノードを持つ
  • 経路(path)
    • あるノードから別のノードへ辿る道筋のこと
    • 下図で、AからDへの経路は、A→B→Dとなる

    A (ルートノード)
   / \
  B   C (内部ノード)
 / \
D   E (リーフノード)

伝統的な解法〜隣接リストモデル

• 隣接リストモデル(Adjacency List Model)
  • 最も古典的なモデルで、検索/更新共に複雑な処理を必要とする
  • ノードのレコードに親ノードの情報(ポインタ)も持たせようとするもの
    • ポインタとは、あるデータの物理的な格納場所(アドレス、番地)を示す情報のこと(C言語を学習する人にとって躓きの石となる箇所として有名)
  • DBMS依存の機能を使うことで、上記の欠点を軽減できるが、あくまで独自機能に頼ることになるので汎用性がない

新しい解法〜入れ子集合モデル

• 入れ子集合モデル(Nested Sets Model)
  • 木を円の包含関係によって表現するモデル
  • ノードを点ではなく、面積を持った「円」として捉える
  • 検索のSQLが簡単になるが、更新処理のパフォーマンスに問題を抱えている

もしも無限の資源があったなら〜入れ子区間モデル

• 入れ子区間モデル(Nested Intervals Model)
  • 入れ子集合モデルの拡張版
  • 入れ子集合モデルの欠点だった更新処理のパフォーマンスを克服できる
  • ただし、実数型の有効桁数が十分に確保されていなければ実用的ではない

ノードをフォルダだと思え〜経路列挙モデル

• 経路列挙モデル(Path Enumeration Model)
  • ノードをディレクトリ（フォルダ）とみなし、各ノードまでの経路(path)を記述する
  • ルートから各ノードまでの経路を保持するモデル
  • 検索に強く、更新に弱いのが特徴

各モデルのまとめ

• 木構造とフラットな二次元表の相性は悪く、RDBでは表現するに向かない構造のため、木構造を表現するための専用のデータベース(XMLデータベース)も研究されている
• 隣接リストモデル以外の3つのモデルは、いずれも本格的な応用が考えられ始めたのが2000年代以降に入ってから
• 入れ子区間モデルは、データ型の精度という物理的制約がなければ、RDBで階層構造を扱う方法論としては、検索/更新のパフォーマンスモデルの簡潔さの双方において最も優れており、近い将来において主流となるモデルだと予想される

良かったところ

論理設計についてただ「どういうやり方で、どういう手順を踏んだらいいか」が書いてあるだけでなく、実際の業務で行う際のバッドノウハウ・グレーノウハウがきちんと言語化されているのがイメージしやすく理解しやすかったです

難しかったところ

やはり全体的に内容が難しかったです。ある程度業務で実際にやってみないとイメージがつきにくい部分があると思うので、都度繰り返し読むようにするといいなと感じました

特に、RDBの論理設計といえば、正規化と非正規化のせめぎあいというほどに、パフォーマンスとデータ整合性の二律背反を考慮することが大切であり、難しい問題なのだと思いました