データベース・スキーマ

3種のデータベース・スキーマ

外部スキーマ

  • 概要: ユーザーやアプリケーションから見たデータベースの見え方を定義します。
  • 目的: ユーザーやプログラムが必要とするデータだけを提供することで、使いやすさを高め、セキュリティを強化します。
  • 特徴:
    • 各ユーザーやアプリケーションごとに異なるビューを作成可能。
    • データの一部だけを見せることができる（例: 社員情報データベースから、一般社員には名前と部署だけを見せ、経営陣には給与情報も見せるなど）。

例:
社員データベースにおいて、

  • 営業部門の人には「社員ID」「名前」「部署」だけを表示。
  • 人事部門の人には「社員ID」「名前」「給与」「評価」を表示。

概念スキーマ

  • 概要: データベース全体の論理的な設計を定義します。
  • 目的: データ全体の構造を統一して管理し、異なる外部スキーマをまとめて関連付ける。
  • 特徴:
    • データの内容、構造、関係を論理的に記述する。
    • 全てのデータを統一的に把握できる一元化された視点。
    • エンティティ（テーブル）や属性（カラム）、リレーション（関係）をモデル化する。
    • 通常、ER図（エンティティ-リレーションシップ図）などで表現される。

例:
社員データベースの概念スキーマは以下のように構成されるかもしれません：

  • テーブル: 「社員」「部署」「プロジェクト」
  • 属性: 「社員」テーブルには「社員ID」「名前」「給与」「所属部署ID」などが含まれる。

内部スキーマ

  • 概要: データベースを記録媒体にどのように格納するかを定義します。
  • 目的: ストレージ効率を高め、データ処理を最適化する。
  • 特徴:
    • 実際にデータが保存される構造や技術的な詳細（インデックス、データ圧縮、ファイル構造など）を管理。
    • ハードウェアやストレージに依存する部分。
    • ユーザーには直接見えない。

例:

  • 社員データベースの物理設計として、
    • 「社員ID」を主キーとしてB木インデックスを使用する。
    • データを特定のファイルフォーマットで保存する。

補足１：内部スキーマで使われるインデックスについて

インデックスは、データベースにおける検索を高速化するための仕組みです。
通常、テーブルのデータを検索する際は、すべてのデータを順番に調べる（全件検索）必要があります。しかし、インデックスを作成すると、検索対象を効率的に絞り込むことができ、データアクセスの速度が大幅に向上します。

インデックスの仕組み

インデックスは本の「索引」のようなものと考えるとわかりやすいです。本の中で特定の単語を探すとき、1ページ目から順番にすべてのページを読む必要があると非常に時間がかかりますよね。索引があれば、特定の単語がどこにあるかすぐに分かり、そのページに直接飛ぶことができます。

データベースでも同様に、インデックスを使うことで次のようなことが可能になります：

  1. 特定のデータの検索が速くなる:
    • 例: 「社員ID = 12345」のデータを探す場合、インデックスを使うとすぐに該当データにアクセスできる。
  2. ソートやフィルタリングが効率的になる:
    • 例: 「社員名をアルファベット順に並べる」といった処理も、インデックスがあると高速化される。

テーブルにインデックスがない場合

以下のようなデータが「社員」テーブルに保存されているとします：

ここで、社員IDが12345の人を検索するクエリを実行するとします：

SELECT * FROM 社員 WHERE 社員ID = 12345;

インデックスがない場合、データベースは次のような動きをします：

  1. テーブルの先頭から、1行ずつ社員IDを確認。
  2. 「社員ID = 12345」が見つかるまで、100万行すべてをチェックする可能性がある。
  3. 時間がかかる（データ量が多いほど処理が遅くなる）。

テーブルにインデックスがある場合

インデックスを「社員ID」に作成したとします：

CREATE INDEX idx_社員ID ON 社員(社員ID);

インデックスがある場合、次のような仕組みで検索が行われます：

  1. データベースは「社員ID」のインデックスを参照する。
  2. インデックスは「社員ID」の値を効率的に並べた目次のようなもの（例: B木構造）。
  3. インデックスが「社員ID = 12345」の行がどこに保存されているかを即座に特定。
  4. データベースはその場所に直接アクセスし、結果を返す。
  5. 時間が大幅に短縮される（例: ミリ秒単位で完了）。

インデックスが高速化を実現する仕組み

インデックスはデータを効率的に整理している

インデックスは、通常「B木（バランス木）」や「ハッシュテーブル」と呼ばれるデータ構造で実装されます。これにより、データベースは検索対象をすばやく絞り込むことができます。

例: B木のイメージ

「社員ID」を基にしたB木インデックスは、次のように構造化されます：

          [500000]
         /        \
    [250000]     [750000]
   /      \      /      \
[125000][375000][625000][875000]
  • 探索の流れ：「社員ID = 12345」の場合：
    1. まず、500000と比較し、左側に進む（12345 < 500000）。
    2. 次に、250000と比較し、さらに左側に進む。
    3. こうして、数回の比較で目的のデータにたどり着く。

B木では、データ数が増えても検索に必要な比較回数はデータ量の対数（log）に比例します。
つまり、100万件でも20回以下の比較で目的のデータにアクセスできるのです。

インデックス利用が有効なケース

・データが大量にあり、特定の行や範囲を検索する場合。

・頻繁に検索条件で使われる列（例: 主キーや外部キー）。

・ソートや結合に使われる列。

インデックス利用が有効でないケース

・小規模なテーブル（インデックスを使わずに全件検索しても十分速い）。

・更新が頻繁な列（インデックスの更新コストが増える）。

テクニカル

Posted by MITSUAKI TSURUTA