データベース アーキテクチャ
Categories: MCA Database
1.物理アーキテクチャ
(1)データファイルとトランザクションログファイル
SQLServerではすべてのデータベースがデータファイルとトランザクションログファイルから構成されています。データファイルはデータベースのすべてのオブジェクトを格納するファイル、トランザクションログファイルはデータベースにどのような変更が加えられたかを記録するファイルです。データファイルはmdf、トランザクションログファイルの拡張子はldfとなる。またセカンダリデータファイルを作成する場合は拡張子はndfです。
(2)ページとエクステント
SQLServerではデータは8KBごとにブロックに分けて格納され、このブロックをページと呼び8つのページの集合をエクステントと呼ばれています。
(3)データアクセス方法
SQLServerではデータをアクセスするにはテーブルスキャンという方法とインデックスを利用する方法があります。テーブルスキャンはテーブルのデータから直接必要なデータを検索し、抽出する方法、インデックスは書籍の目次から索引するような方法でデータに指標をつけ取り出す方法です。
テーブルスキャンはテーブルのすべてのページのすべての行を順に検索し、指定の条件に合致する行を取り出します。インデックスを地用したアクセスの場合はインデックスを検索し、その中から指定の条件に合致したデータを取り出します。
2.インデックス設計
(1)インデックス
インデックスとはテーブルのデータを効率よく検索できるよう作成するデータのことです。データベースのインデックスはテーブル中の特定の列のデータを抽出してファイルにまとめたものです。インデックスの作成された列のことをインデックス列と呼び、インデックス列内のデータをキー値と呼び、複数列をまとめてインデックスにしたものを複合インデックスと呼んでいます。
(2)インデックスのメリットとデメリット
インデックスを作成すればデータへのアクセスを効率化できるが、大量のディスクが必要になり、データ更新時のオーバーヘッドが大きくなるデメリットが生じてきます。インデックスを作成すべきかどうかは各列についてメリットとデメリットを考慮して決めていきます。
①インデックス作成でメリットがある列
・主キーと外部キー
・頻繁に検索される列
・行がソートされた状態で利用されることが多い列
②インデックス作成でメリットがない列
・ほとんど参照されない列
・データの重複が多い列
・頻繁に行う検索で大部分の行が抽出される列
(3)インデックスの種類
SQL Serverのインデックスにはクラスタ化インデックスと非クラスタ化インデックスの2種類があります。
①クラスタ化インデックス
クラスタ化インデックスはインデックスの階層構造の再開レベルがデータベースになるインデックス方式であり、インデックスは一つのテーブルにつき1つしか作成できません。
②非クラスタ化インデックス
非クラスタ化インデックスはインデックスの階層構造とデータページが完全に分離したインデックスであり、非クラスタ化インデックスのリーフレベルにある各行のデータにはどのデータファイルでどのデータページのどの行に格納されているのか識別子が必要です。このインデックスを使用した場合はデータページのデータはソートされません。非クラスタインデックスはテーブル1つ249個まで作成が可能です。
3.逆正規化
逆正規化とはデータベースをあえて正規化にしないこと、一般に正規化を行えばデータベースは重複のない扱いやすいものになりますが、正規化することでテーブルが複数になり検索が非効率になってしまうということもあり得るため、正規化を行わないケースがあります。
(1)テーブルの変更時の整合性に問題
逆正規化を行ったテーブルの場合、データを変更することによって矛盾が生じるデータがあります。細心の注意を払ってデータの変更を行う必要があります。
(2)検索の仕方によっては非効率
正規化によって2つに分割されるテーブルの両方を対象に行わなければならない検索、逆正規化によって効率があがりますが、逆正規化によってテーブルが大きくなってしまうため、非効率になるケースもあります。
4.まとめ
①データファイルとは？
データベースの全てのオブジェクトを格納するファイル
②トランザクションログファイルとは？
データベースがどのような変更が加えられたか記録するファイル
③ページとは？
データは8KBごとにブロックに分けて格納されるがこのブロック１単位のこと
④エクステントとは？
ページが8つ集合した単位をエクステントと呼びます。
⑤テーブルスキャンとは？
テーブルのデータから直接必要なデータを検索し抽出する方法
⑥インデックスキーとは？
インデックスとはテーブルのデータを効率よく検索できるよう作成するデータであり、データベースのインデックスはテーブル中の特定の列のデータを抽出してファイルにまとめたもの。
⑦B-Tree
行の数が増えてルートページに入りきらなくなった場合は新たなページが割り当てられ残りの行はそのページに格納される。インデックスページが増えると階層構造になりこのようにしてページを増やしていく方式をB-Treeという。
⑧クラスタ化インデックス
クラスタ化インデックスはインデックスの階層構造の最下位レベルがデータページになるインデックスである。
⑨非クラスタ化インデックス
インデックスの階層構造とデータページが完全に分離したインデックスのこと。
⑩逆正規化
データベースをあえて正規形にしないことであり、正規化をしないことでテーブルを増やす必要がなくなる。正規化による検索効率が下がるのを防ぐ。
⑪リーフレベル
階層構造の再開のレベルにあるページのことでそれ以外のページを非リーフレベルと呼びます。
