22. RDBに関する知識 II

シラバス: 

1. 科目の概要

関係データベース(RDB)に関して、インデックスの取り扱いやSQLによるデータベースの操作など、実際の活用に際して必須の知識について解説する。またオープンソースRDBMSの紹介や企業DB設計、DBアプリケーションの例など、RDBに関する応用的な知識を紹介する。

2. 習得ポイント

本科目の学習により習得することが期待されるポイントは以下の通り。

3. IT知識体系との対応関係

「22. RDBに関する知識Ⅱ」とIT知識体系との対応関係は以下の通り。

[シラバス:http://www.ipa.go.jp/software/open/ossc/download/Model_Curriculum_05_02.pdf]

<IT知識体系上の関連部分>

4. OSSモデルカリキュラム固有の知識

OSS モデルカリキュラム固有の知識はデータベースのOSS実装に関する知識であり、他の内容はIT知識体系と共通したRDB に関する内容を扱う。

(網掛け部分はIT知識体系で学習できる知識を示し、それ以外はOSSモデルカリキュラム固有の知識を示している)

II-22-1. インデックスの概念

データベース検索の高速化に必須の技術であるインデックスの概念について解説する。探索のアルゴリズムなどインデックスの原理について説明し、インデックスの物理的な構造や性能、リカバリ特性など、インデックスに関する基本的な考え方を示す。

【学習の要点】

* データベースに保存されたデータは、二分検索、B木検索、B+木検索などの検索アルゴリズムを利用したインデックスを作成して検索が行われる。

* インデックスは物理的な配置方法の違いにより、クラスタードインデックスと非クラスタードインデックスがある。

図II-22-1. 木構造の例

【解説】

1) インデックスとは

インデックスはデータベースのレコードを効率よくアクセスするために用いられる。

インデックスは本の巻末に記載されている索引と同様の役割を持つ。例えば、本に索引が存在しない場合は、目的の語を見つけるためには、全文を最初から読んで探していかなければならない。本にアルファベット順に並べられた索引があれば、語句の一覧に示されたページを見て、そのページから目的の語句をすばやく探し出すことができる。その反面、本の索引を作成するには相応の労力とページ数が必要でコストがかかる。本の内容によっては索引の有用性が低いこともある。

2) インデックスの構造

データベースにおいても本の索引と同様に、インデックスによって、検索時にレコードに対してより効率よくアクセスすることができる。

本の索引と同様の考え方で、コンピュータ上でファイルに索引を持たせる方法を、ISAM(索引表付き順編成ファイル)と呼ぶ。このような構造を持たせたインデックスを利用しても、検索効率は向上するが、データの更新を繰り返すと、性能が低下していく問題がある。

そのため、よりも高いパフォーマンスを得るために、木構造と呼ばれるインデックスを採用する場合も多い。

3) インデックスの特徴

インデックスはDBの性能のみに関係する。インデックスの設定によって、検索やソートのパフォーマンスを上げることができる。言い換えれば、インデックスを設定しなければ、目的のレコードを抽出するために、全レコードを読み込んで検索することが必要になることがある。

主キーやユニークキーはデータベースの行を一意に特定するもので、インデックスを必ずしも内包するわけではなく、異なる概念であるが、キーは行の追加時に重複の確認をする必要があるので、パフォーマンス向上のためにキーを設定するとインデックスも自動的に設定されるRDMBSも存在する。重複がないインデックスをユニークインデックスという。

4) インデックスの設定

インデックスは全体的に張り巡らせれば良いというわけではない。インデックスの設定が効果的な場合と、逆効果になりがちな場合がある。効果的な場合とそうでない場合の例を以下に挙げる。

インデックスは、インデックス用にデータ量が増大するためディスク必要量が増大することにも注意が必要である。

また、データの更新が多いテーブルでは、レコードの登録、削除と同時にインデックスを更新する処理が必要になるため慢性的に負荷が増大しパフォーマンスが低下することがある。

* 効果的な場合

- 検索条件に使われることが多い

- 結合に使われることが多い

- ソートやグループ化で使われることが多い

* 非効果的な場合

- レコード数が少ない

- カーディナリティが低い(重複した値が多く値の分布が少ない)

II-22-2. インデックスの種類と特徴

バイナリツリー(B木)、索引構成表、ビットマップ・インデックス、逆キー・インデックス、ハッシュ・クラスタ(ハッシュ・インデックス)など、様々なインデックスの種類を紹介し、それぞれの特徴と利点・欠点について解説する。

【学習の要点】

* B木インデックスは、木構造のページ群にアドレス情報を格納し、インデックス値を比較しながら二分検索法で該当する行の検索を行う。

* ハッシュ・インデックスはハッシュ関数を使って直接アドレス計算を行って該当する行検索を行う。

* ビットマップ・インデックスは1行に1ビットを割り当てたビットマップデータの相対位置から該当する行検索を行う。

図II-22-2. B+木インデックス

【解説】

1) インデックスの種類

インデックスには様々な種類があり、それぞれ利点と欠点を持っている。B+木インデックスが、RDBMSのインデックスで最も多く使われる木構造といわれている。

* 二分木インデックス

木構造は木のように広がっていく構造をもつ。二分木は木構造のなかで最も基本的なもので、ノード(節)の下にある子のノードが最高で2つであるという構造を持つ。

検索方法は、検索したい値をルート(根)から分岐(枝)へたどっていき、目的のキー値がノードのキー値よりも小さければその前の枝へ向かい、大きければその次の枝へ向かう。その次のノードでもその動作を繰り返す。等しいときに完了となる。

データの追加により二分木のバランスが崩れ片側だけに集中するようになると、パフォーマンスが低下するという欠点がある。

* B木インデックス

B木(バランス木)は、二分木インデックスの欠点を解消するために考えられたもので、バランス木の名前の通り、データの分岐の先がすべて同一の階層に属した多分木の構造を持つ。ノード内にもソートされた複数のキーが含まれる。

検索方法は、ノード内のキーを検索し、目的のキーが、ノードのキー値よりも小さければその前の枝へ向かい、大きければその次の枝へ向かい、その次のノードでもその動作を繰り返す。リーフノードにキーがあったときに完了となる。

利点は、レコード追加後の検索性能が保たれること、欠点は、レコード削除での検索性能が低下することである。

* B+木インデックス

B木はノードにもデータを記録するが、B+木ではデータは木の最下層にあるノード(葉ノード、リーフレベルのノード)に格納され、内部ノードにはキーのみが記録される。

また隣のノード同士をポインタで結合してシーケンシャルアクセスの性能向上が図られている。

B木インデックスとB+木インデックスともに区別なくB木インデックスと呼ぶこともある。

* ハッシュインデックス

B木インデックスを使った場合には、目的のレコードのアドレスを得るためには、数回のアクセスが必要になることがある。これをハッシュ関数と呼ばれるものを使って、一回で目的のレコードのアドレスを取得できるようにしたものである。

B木とは異なり、BETWEENなどの範囲検索には利用できない欠点がある。

* ビットマップインデックス

キーとなる値をビット列で用意するものである。ビット列はキーの値の数だけ用意される。

利点は、通常ではインデックスの設定に不向きとされる、カーディナリティが低い場合に検索性能が高く、インデックスの容量を少なくできる。欠点は、キーの値の数が多い場合にはビット列が増えるため不向きであることと、レコード追加がある場合に性能の低下が大きいことである。

* 逆キーインデックス

キーの値を逆にしてインデックスを格納する方式で、連番(シーケンス番号)などに付加する索引では同じノードに更新が集中するのを避け更新を分散させることができる。範囲検索ができない欠点がある。

II-22-3. データベースの物理設計

関係データベースを実装する際に必要なデータベースの物理構造について説明する。データベース物理設計の目的や、物理設計時に留意すべきトレードオフ、データとインデックス、ログをどのように配置するか、データベース記憶領域の確保など、物理設計に必要な具体的な手順を示す。

【学習の要点】

* RDBにおけるデータベース物理設計の目的は、表内と表間の整合性制約の定義、性能向上のための構造定義、セキュリティの設定を含んでいる。

* データベースの物理設計では、データの非正規化、インデックスの利用、問合せ処理の最適化などを行う。

* システムで必要となる容量や性能を想定して、I/Oの分散化や容量設計を実施する。

図II-22-3. 非正規化による性能向上

【解説】

1) 整合性制約

関係データベースでは、整合性制約を定義することにより、整合性制約と異なるデータの更新が禁止され、矛盾しない状態を保つことができる。制約の種類として、以下の項目がある。

* 主キー制約(一意性制約)

レコードを一意に特定するために、テーブル内の項目での値の重複を禁止する。

* 参照制約

外部キーによって、関連テーブル間での依存関係を定義する。

* ドメイン制約

値の制限値の範囲を事前に設定することで、それ以外の値の入力を禁止する。

* NOT NULL制約(非ナル制約)

NULL値の入力を禁止する。NULL値とは0(ゼロ)や文字列の” ”とは異なり、値が入っていない空の状態のことを言う。

2) 性能向上のための構造定義

* インデックス

インデックスの設定によって、検索やソートのパフォーマンスを上げることができる。

* 非正規化

正規化を行うと、データの整合性が高く保たれるが、数多いテーブルに分解され、結合条件が多くなりパフォーマンスが低下する。そのため、データベースのパフォーマンス向上のために、非正規化という、正規化後に故意に正規化を冗長化する構造定義の方法が用いられる。

非正規化は、重複してデータを持つことになるため、整合性が低くなる欠点がある。これは、正規化とはトレードオフの関係になるため、物理設計時に留意すべきである。

3) セキュリティ

データベースは共有して利用することが多い性質上、不正利用を防止するための機能がある。

* アクセス制限管理

多くのRDBMSでは、特定の表の参照のみ可能なユーザを設けるなど、ユーザの属性毎に参照や更新の許可設定をすることにより、きめ細かいセキュリティ管理をすることができる。また、全てのユーザにパスワードを設定することで、なりすましでのログインを防止できる。更に、外部のIPアドレスからの接続を拒否できる設定を持つものもある。

* ログ

ログは、データベースに対して発行したクエリの監視結果を保存する機能である。不正アクセスの監視やデータベースの障害時復旧の情報として使われる。また、長時間を要したクエリを記録する機能を持つRDBMSもあり、性能向上のための情報としても役立つ。

4) 容量設計とI/O分散

運用中に容量不足が発生した場合には、データベースに障害が発生し、停止してしまう場合がある。それを避けるために、あらかじめシステムで必要となるデータ量を想定した上で、十分なものとなるように、容量設計を実施する。

処理性能が必要となる用途では、ディスクの性能を向上させるべく、ファイルを複数のディスクに分散配置することや、ディスクアレイシステムを構築するなど、I/O分散の工夫を図る。

II-22-4. リレーショナルデータベースとSQL

関係データベースをアクセスするための基本的な技術であるStructured Query Language (SQL)の構造、原理、動作仕様、種類、特徴などについて解説する。またSQLの標準化といった関連情報も紹介する。

【学習の要点】

* SQLはRDBのデータ定義や操作を行うために開発された非手続き型のデータ言語である。

* SQLにはデータ定義、問合せ、データ更新、埋め込み型SQLなどがある。

* ANSI及びISOで言語仕様の標準化が行われており、制定年によりSQL92、SQL99、SQL2003などがある。

図II-22-4.SQL製品と標準化規格の発展

【解説】

1) SQLとは

SQLとは、Structured Query Language の頭文字を取ったもので、RDBのデータ定義や操作を行うために開発された非手続き型のデータ言語である。RDBMSのほとんどが、データ操作言語としてSQLを採用している。

SQLは、E.F.Codd氏の論文をもとに、IBMのSanJose研究所で開発されたデータベースシステムSystem Rに実装されたSEQUELという言語を発展させたものといわれている。SQLは、使いやすさに優れていたため、それに倣ったRDMBS製品が多数リリースされるようになった。

SQLは各RDBMS間で記述方法の共通点は多いものの、独自拡張などの異なる点も多くあるため、互換性向上を目的としてSQL92、SQL99などの標準化規格が制定されている。オープンソースRDBMSでもこれらの仕様の一部が取り入れられている。

最新の規格はSQL2003で、デーブルXMLのデータに変換するXML連携等の仕様が追加されている。この仕様の一部を取り入れたオープンソースRDBMSも存在する。

2) SQLの命令の種類

主なSQL命令にはデータ定義命令(DDL)、データ操作命令(DML)、データ制御命令(DCL)の三種類に分類できる。

* データ定義命令(DDL)

データベースやテーブルの削除、登録、変更等、データベースの構造や整合性制約を定義する。

- CREATE データベースやテーブルを作成する

- DROP データベースやテーブルを削除する

- ALTER データベースやテーブルを変更する

* データ操作命令(DML)

データ行の削除、登録、変更等、データの操作を行う。

- SELECT テーブルからデータ行を取得する

- INSERT テーブルにデータ行を追加する

- DELETE テーブルからデータ行を削除する

- UPDATE テーブルの既存データ行を更新する

* データ制御命令(DCL)

データベースのユーザ権限の管理やデータのトランザクション処理を行う。

- GRANT 権限を付加する

- REVOKE 権限を剥奪する

- COMMIT 変更内容を確定する

- ROLLBACK 変更内容を取消する

3) 埋め込みSQL

プログラミング言語でSQLを用いるための方法の一つとして、親となるプログラム中にSQL(DML)を直接記述して使用できる、埋め込み型SQLがある。

II-22-5. SQLによる表の作成

SQLを用いて表を作成する手順を示す。データ型の指定やNULLの取り扱い、主キーと外部キーの指定方法といった表の作成に関する基本的な項目を具体的に説明する。

【学習の要点】

* RDBにSQLで表(テーブル)を作成には「CREATE TABLE」文を使用する。

* 列のデータ型には大きく分けて文字列型、数値型、日時型が指定できる。

* 表の行の一意性制約のためにNULL値を持たない主キーとなる列を作成する必要がある。

図II-22-5. データ型とテーブルの作成

【解説】

1) 表の作成

データベースに格納されるデータは「テーブル」で管理される。SQLを用いてRDBに表(テーブル)を作成するには「CREATE TABLE」文を使用し、以下の構文で記述する。

 CREATE TABLE テーブル名 ( フィールド名 データ型 [ , フィールド名 データ型] … ) ;

記述例: CREATE TABLE `hinmoku` ( `shinacode` INT ,

`shinamei` CHAR( 30 ) ,

`tanka` INT ,

`tourokubi` DATE) ;

2) データ型

列のデータ型には大きく分けて文字列型(char、varchar、text)、数値型(int、float、double)、日時型(date、datetime、timestamp)等があり、適切な定義により、データの整合性を高く保つことができる。使用できるデータ型や記述内容はRDMBSによって一部異なる箇所がある。ここではMySQLを例とする。

3) NOT NULL

必須の項目を定義するには、NOT NULL制約のオプションを指定する。テーブルを変更する場合は「ALTER TABLE」を使用する。

記述例: ALTER TABLE `hinmoku` MODIFY COLUMN `shinamei` CHAR( 30 ) NOT NULL ;

4) 主キーと外部キー

* 主キー(プライマリキー)

主キーを作成するには、CREATE TABLEまたはALTER TABLEで「PRIMARY KEY」を指定する。

記述例: ALTER TABLE `hinmoku` ADD PRIMARY KEY (`shinacode`) ;

* 外部キー

外部キーを作成するには、CREATE TABLEまたはALTER TABLEで「FOREIGN KEY」を指定する。なお、MySQLでは外部キーの機能を使用するには、ストレージエンジンにInnoDBを指定する必要がある。外部キーを使用する場合は、あらかじめ既に作成した参照先テーブルのストレージエンジンもInnoDBに変更する。

記述例: CREATE TABLE `hacchuu` ( `denpyoubangou` INT ,

`shinacode` INT ,

`suuryou` INT ,

`shiiresakimei` CHAR( 30 ) NOT NULL,

PRIMARY KEY (`denpyoubangou`) ,

FOREIGN KEY (`shinacode`)

REFERENCES `hinmoku`(`shinacode`)

) ENGINE = InnoDB;

II-22-6. SQLを用いた問い合わせ処理

SQLを用いたデータの検索、問合せ処理の手順を示す。検索の基本となるSELECT文について説明し、式や数値、文字列、日付といったデータの処理や、WHERE句による演算、集合関数などについて解説する。

【学習の要点】

* 表から指定した条件を満たす行の集合列を取り出すには「SELECT」文を使用する。

* FROM句で指定した表からSELECT句で指定した列名の列だけを取り出し、WHERE句で指定した検索条件を満たす行を取り出す。

* 指定した列で同じ値を持つ行の集計結果を取り出すには、GROUP BY句とCOUNT、MAXなどの集計関数を使用する。

図II-22-6. SQLを用いた問い合わせ結果

【解説】

1) データ取得(SELECT)

テーブルのデータを取得するには、「SELECT」文を使用する。SELECTの次に抽出したい参照フィールド名を記述する。ここに参照したいフィールドを列挙して必要なフィールドを取得することができる。「*」は指定したテーブルに存在する全てのフィールドを取得することができる。「FROM」句の次に抽出対象のテーブルを記述する。

記述例:

 SELECT `shinamei`, `tanka` FROM `hinmoku`;

2) 検索条件(WHERE)

検索条件を指定し特定のレコードを取得する場合には、SELECTに続いて「WHERE」句を用いる。条件に用いる演算子として「=」(等しい)、「!=」(等しくない)、「IN」(いずれかが含まれる)などを使用できる。

記述例:

SELECT * FROM `hinmoku` WHERE `tanka` > 890;

3) 集合関数(集計関数)

集合関数を用いることにより、抽出した結果を集計したり検索条件に該当するものの中から、最大値や平均値等を取得することができる。集合関数として、AVG(平均値)、SUM(合計値)、MAX(最大値)、MIN(最小値)等がある。

記述例:

 SELECT MAX(`tanka`) FROM `hinmoku` WHERE `tanka` < 1000;

4) 文字列関数 日付関数

文字列関数や日付関数を用いることにより日付や文字列を操作することができる。例えば、「DAYOFMONTH」関数は日を得る関数である。RDBMSの種類によって関数の有無や名称が異なる場合が多い点に留意するべきである。

記述例:

SELECT DAYOFMONTH(`tourokubi`) FROM `hinmoku`;

5) グループ化

「GROUP BY」句を使うと、同フィールド内の値が同一である場合に複数のレコードをグループ化して取得することができる。また、集合関数をあわせて使うことにより、グループ化された項目のグループ内を集計することができる。

記述例:

SELECT `tourokubi` , COUNT(`tourokubi`) FROM `hinmoku` GROUP BY `tourokubi`;

6) 並べ替え

「ORDER BY」句を使うとレコードを指定したフィールドでソートして取得できる。 「ASC」句は昇順でソートし、「DESC」句は降順でソートする。

記述例:

SELECT * FROM `hinmoku` WHERE `tanka` < 1000 ORDER BY `tanka` DESC;

II-22-7. 表の結合、複雑な処理、テーブルの更新

外部結合や自己結合、副問合せといった表の結合(ジョイン)に関するSQLによる操作方法や、SQLを用いた複雑な検索・照会の方法、テーブルの更新、削除など、SQLによる様々なテーブル操作について説明する。

【学習の要点】

* 表の結合により正規化で分割した表を一つの表として扱う事ができる。

* 表の結合を行う方法としては、他の表と結合する外部結合、同じ表で結合する自己結合、他のSQL文の実行結果を使用する副問合せがある。

* 表の更新処理を行うSQL文としては、行の挿入を行うINSERT文、行の更新を行うUPDATE文、行の削除を行うDELETE文がある。

図II-22-7. 表の結合

【解説】

1) 表の結合

* 外部結合と内部結合

表の結合(JOIN)により、正規化で分割したいくつかの表を一つの表として扱う事ができる。「ON」句の後ろに結合フィールドを指定する。

結合方法には内部結合と外部結合がある。内部結合(INNER JOIN)は結合したフィールドの両方に合致したレコードを抽出する方法である。外部結合(OUTER JOIN)は結合したフィールドの両方に合致するレコードが存在しなかった場合でも抽出する方法で、左外部結合(LEFT OUTER JOIN)は、結合条件の左側のテーブルのレコードを全て抽出する方法である。

記述例: SELECT * FROM `hinmoku` AS A LEFT OUTER JOIN `hacchuu` AS B

ON A.`shinacode` = B.`shinacode`

WHERE A.`tanka` < 800 ;

* 自己結合

自己結合とはテーブルに別名をつけて、同じテーブル同士を結合することである。下記の例では「,」で結合し、結合条件をWHERE句で記述しているが、INNER JOINで記述しても同じ結果になる。同一要素の組み合わせを排除する条件を入れることで、重複行の抽出が防止される。

記述例: SELECT A.`shinamei`,A.`tanka` FROM `hinmoku` AS A, `hinmoku` AS B

WHERE A.`tanka` = B.`tanka`

AND A.`shinacode` <> B.`shinacode` ;

* 副問い合わせ

副問い合わせ(サブクエリー)とは、SELECT文中に更に囲うことのできるSELECT文である。問い合わせ結果の一部として使用できる他、WHERE句などでの条件式としても使用できる。

記述例: SELECT `shinamei` , (SELECT MAX(`suuryou`) FROM `hacchuu`

WHERE `shinacode` = A.`shinacode`) AS `suuryou` FROM `hinmoku` AS A;

2) データ操作命令

* INSERT(追加)

「INSERT」はテーブルにレコードを追加する。INTOの後ろにテーブル名と項目名を、VALUESの後ろに追加する値を記述する。

記述例: INSERT INTO `hacchuu`

(`denpyoubangou` ,`shinacode` ,`suuryou` ,`shiiresakimei` )

VALUES ('1', '1', '100', '仕入商店');

* UPDATE(更新)

「UPDATE」はテーブルの既存レコードを更新する。SETの後ろに更新する項目と値を「=」で続けて記述する。WHERE句の後ろはSELECT文と同様に更新対象となる条件を指定する。

記述例: UPDATE `hacchuu` SET `shiiresakimei` = '山田商店'

WHERE `denpyoubangou` = 1 ;

* DELETE(削除)

「DELETE」はテーブルのレコードを削除する。FROMの後ろにテーブル名を記述する。

記述例: DELETE FROM `hacchuu`

WHERE `denpyoubangou` = 2 ;

II-22-8. オープンソースRDBMSの種類と特徴

PostgreSQL、Firebird、MySQLといった代表的なオープンソースRDBMSについて、開発の歴史や主たる機能、特徴やライセンスなどについて解説する。さらに商用RDBMSとの比較やオープンソースRDBMSを利用する理由についても説明する。

【学習の要点】

* 代表的なオープンソースRDBMSとしては、企業が開発しオープンソース化したMySQL、大学の研究プロジェクトで開発されたPostgreSQL、商用RDBMSから分岐したFirebird等がある。

* オープンソースRDBMSは機能、性能共に商用RDBMSと遜色ないものとなってきており、企業システムで使えるレベルになっている。

図II-22-8. オープンソースRDBMSの種類と特徴

【解説】

1) オープンソースRDBMSの普及

昨今のオープンソースRDBMSは、商用RDBMSと比較しても機能、性能共に遜色ないものとなっており、オープンソースOSやWebサーバの普及に伴って広く利用されている。

オープンソースRDBMSを利用する理由となるのは、通常のオープンソースソフトウェアと同様に改変可能、ライセンス料金不要という点である。特に注目されるのは、ライセンス料金不要の側面である。Webシステムでは利用ユーザ数が予測しづらく、コストも抑制されがちである。かかる利用目的において、商用RDBMSはコストが高く、ユーザ数でライセンス料金が変動する形式のものもあり、採用しにくい面があった。そこで、代用としてオープンソースRDBMSが急速に普及していった。

2) オープンソースRDBMSの種類

オープンソースRDBMSは様々な製品が登場している。主たるものとして以下の3製品がある。

* PostgreSQL

PostgreSQLは、カリフォルニア大学バークレー校のプロジェクトにより開発され、その後インターネットコミュニティに引き継がれ開発が継続されているRDBMSである。

PostgreSQLは商用RDBMSに匹敵するほど高機能なRDBMSで、企業システム用途での利用を意識して機能強化が進められており、比較的早期にストアドファンクションやサブクエリーをサポートするなど機能が充実している。

また、プログラム改変後のソースコードの公開の義務がなく、商用利用でも制限なく使用可とする、BSDライセンスに基づいて配布されており、ライセンス上の懸念が少なく利用できる。そのため、基幹業務システムで採用される場合も多い。

* MySQL

MySQLはコミュニティ主体ではなくMySQL AB社という企業が主体となり開発したRDBMSである。2008年にサン・マイクロシステムズが買収した。

MySQLは、無償のCommunity Serverと有償のEnterprise Serverという二つのライセンス形態に分けられている。両者ともオープンソースソフトウェアであるが、Enterprise Serverのバイナリプログラムは再配布はできない。Enterprise Serverは安定性を重視し、Community Serverは機能実装を重視する方針で開発が進められている。

MySQLは従来から処理速度が高速であることで知られていたが、機能面でも他のRDBMSと遜色がなくなってきている。

PostgreSQLと同様に、各種開発言語用のAPIが用意されており、WebシステムのバックエンドRDBMSとして多くの実績がある。また、ブログなどのオープンソースCMSでは、対応DBとしてMySQLが採用されている場合が多く、個人向けレンタルサーバで搭載されるDBとしての普及も進んでいるため、個人ユースとしても広く利用されている。

* Firebird

Firebirdは、商用のRDBMS「InterBase」を起源として約20年の歴史を持つRDBMSで、ボーランド(当時)がオープンソースとしてリリースしたInterBase 6.0を基として、開発が続けられている。Firebirdプロジェクトには過去にInterBaseの開発を行っていたメンバーが多く参加しており、商用製品に遜色ないプロジェクト体制を有している。

ストアドファンクション、オンラインバックアップなど企業システムを意識した機能を備え、また、商用のInterBaseから派生した経緯から、InterBaseユーザの代替としても広く利用されている。

II-22-9. RDBを用いた企業DB設計の例

業務分析としてエンティティを洗い出しER図を作成したのち、スキーマとインデクスの設計、分散データベースの設計という企業基幹データベースの設計手順を示す。また物理設計として性能設計や処理効率化検討といった項目についても解説する。

【学習の要点】

* データベース作成を行う業務のエンティティの洗い出しを行い、ER図の作成を行い概念データモデルの設計方法を習得する。

* 概念データモデルからスキーマやインデックスの設計を行い、論理データモデルの設計方法を習得する。

* 論理設計の結果からレコードの構造、順番、アクセスパスなどの設計を行い、物理設計の方法を習得する。

図II-22-9. ER図

【解説】

1) データモデリング

データベースシステムを構築する場合には、現実にある様々な情報から、システムの要件に基づき、構築に必要なデータ項目を探し出して、項目間の関係性をまとめ上げ、より無駄なく集合化して、論理的かつ意味的に正しいデータモデルを組み立てることが必要になる。データモデルを組み立てる行為のことをデータモデリングという。

データモデルの持つ機能としては、データ定義、データ操作、整合性維持の3つがある。

データモデルは、一般に次の3段階を経て完成し、段階を経る毎により詳細化していく手順となる。

* 概念データモデル

システムの要件をコンピュータでの処理を前提に分析し識別し、ER図などで定義した、特定のDBMSに依存しない大まかなデータモデルのことを「概念データモデル」という。

* 論理データモデル

概念データモデルに、システムで必要となるスキーマを作成して、コンピュータに実装可能な形に変換したデータモデルのことを「論理データモデル」という。

スキーマは、表と表内のフィールド、フィールドや表の関係などのデータベースの構造の定義のことである。

論理データモデルは、より詳細なER図などで作成され、DBMSの特性を加味して、それに依存したデータモデルになることが多い。

* 物理データモデル

実装を意識して、データ型や索引までを定義し、CREATE TABLE文などの、DBMSでサポートされている形式で記述可能となるレベルのデータモデルを「物理データモデル」という。

物理データモデルでのデータ定義は、実テーブルを設計することであり、整合性制約の定義、性能向上のための構造定義(インデックス、パラメータ)など、RDBの物理的な内部構造を決定する。

アクセスパスは、データの取得ルートのことをいう。インデックスや抽出条件、結合条件などを見直すことで、よりパフォーマンスの高いアクセスパスとなることを目指す。

2) 分散データベースの設計

異なる場所にデータベースを配置しネットワークで接続されたデータベースシステムのことを分散データベースという。この方法は、ネットワークの負荷低減のために有用である。分散データベースには、非同期型更新や2相コミットなどの方法がある。

* 非同期型更新

分散先のサイトに更新データを適宜送信してデータの同期を取る方法である。同じデータを複数のサイトで同時に更新する必要がある場合は、更新内容に競合が生じる可能性があるため、利用には注意が必要である。

* 2相コミット(2フェーズコミット)

主サイトから分散した処理先のサイトの更新処理がコミット可能か確認し、コミット処理待ちの指示を出すフェーズ(第1フェーズ)と、それぞれのサイトが更新をコミットする指示を出すフェーズ(第2フェーズ)に分けて処理がおこなわれ、第1フェーズの結果を確認し、コミットするかロールバックするかを決定して、処理先のサイトへ指示を出す。比較的競合を防止できるが、ネットワークの負荷は増大しがちになる。

II-22-10. RDBを用いたDBアプリケーションの例

テーブルの作成とデータのロードといった具体的な手順を示し、さらに検索結果の表示やビューの設定といった業務帳票や画面設計の手順、パフォーマンスの検討など、実際のDBアプリケーション開発に必要な項目を解説する。

【学習の要点】

* 企業で利用するために設計されたデータベースをSQLにて作成し、データの登録を行う。

* 必要なデータの検索を行うSQLを作成し、結果を表示させる。

* インデックスの作成等により作成したデータベースのパフォーマンスの改善を行う。

図II-22-10. 画面・帳票設計の例

【解説】

1) データのロード

企業でデータベースを利用するための移行データなどの投入には、CSVデータを利用することが広く行われている。

 LOAD DATA INFILE ‘[ファイル名]’ INTO TABLE ` [テーブル名] `<br />
FIELDS TERMINATED BY ",(セパレーター)"<br />
OPTIONALLY ENCLOSED BY '"(囲い文字)';

作成したテーブルにデータをロードするには、プログラムを開発して、データをINSERT文に変換する方法の他、RDBMSにロード用のユーティリティが用意されている場合がある。例えば、MySQLでは、以下の構文でCSVファイルをテーブルにロードすることができる。

2) ビューの設定

ビューは、既存の一つまたは複数のテーブルから抽出した結果を、仮想的な名称をつけて表示できるテーブルである。

ビューは利用側からは単一のテーブルのように見えるが、ビュー内で結合取得するなど複雑なクエリとなっている場合は、パフォーマンスが低下する。

* ビューの利用目的

- テーブルをカプセル化することで、同じ検索結果を得たいプログラムが複数存在した場合に、同じようなSQLが乱立しプログラム開発上の不具合を誘発することを防止して、開発効率を向上させる。

- テーブル全体を取得させないなどの管理の厳格化により、データのセキュリティを向上させる。

* 記述例

CREATE VIEW hacchuu_shinamei AS

( SELECT B.suuryou , B.shinacode , A.shinamei FROM `hinmoku` AS A INNER JOIN `hacchuu` AS B ON A.`shinacode` = B.`shinacode` WHERE A.`tanka` < 800 )

3) 業務帳票や画面設計の手順

帳票や画面の設計は、システム作成依頼先から要件確認を行い、業務フローの作成によって、システム化対象となった機能の洗い出し(画面一覧、帳票一覧)をする。

そして、各機能から必要となるエンティティを確認しながら、データモデルを作成していく。

データモデルの作成においては、複数の画面帳票が相互に関連しているものもあるので、機能別にテーブル関連図やCRUD図(各々の機能が、どのテーブルに対して、データの参照、作成、更新、削除を行うかを示した表)などを作成する。

この前後に、必要と判断された機能について、「帳票レイアウト」と「画面レイアウト」の作成を実施する。これらに機能関連表、項目一覧、機能概要説明などを付記したものが一般に「基本設計書」となる。

4) パフォーマンスの検討

要件確認においては、登録データの件数もできるだけヒアリングし、パフォーマンス上、ボトルネックとなりそうな箇所を検討しておく。これらの箇所について、SQLのチューニングやデータベースの非正規化、インデックスの設定などのパフォーマンス対策の実施を検討する。