論理設計とパフォーマンス 〜正規化の欠点と非正規化
達人に学ぶDB設計 徹底指南書を読み返して。
まず、
データの整合性を保つ為、正規化を行う事は大原則。しかし、正規化によってパフォーマンスに少なからず影響があるということも理解しておく必要がある。
正規化とパフォーマンスはトレードオフの関係
正規化を進めていくと、複数のテーブルに分けて管理し、それぞれのデータを参照しながらデータを抽出するが、その分コストが高くなってしまうという。特にテーブル同士の結合はSQLの処理の中でもコストが高い。
正規形のデメリット
主なデメリットは、正規化されたテーブルにおいて、単独のテーブルだけからでは必ずしも欲しいデータの全てをカバーできないということ。
以下、テーブル、SQLは書籍から引用
例：
第３正規形のテーブル
会社
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
| C0003 | C建設 |
社員
| 会社コード | 社員ID | 社員名 | 年齢 | 部署 |
|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 |
| C0001 | 000B | 藤本 | 32 | D02 |
| C0001 | 001F | 三島 | 47 | D03 |
| C0002 | 000A | 斎藤 | 47 | D03 |
| C0002 | 009F | 田島 | 25 | D01 |
| C0002 | 010A | 渋谷 | 33 | D04 |
部署
| 部署コード | 部署名 |
|---|---|
| D01 | 開発 |
| D02 | 人事 |
| D03 | 営業 |
| D04 | 総務 |
1、ある社員の勤めている会社を知りたい
SELECT
会社.会社名
, 社員.社員名
FROM 社員 INNER JOIN 会社
ON 社員.社員コード = 会社.会社コード
WHERE 社員.社員コード = '田島';
会社名 社員名
----- -----
B化学 田島
２、会社に加えて部署名も知りたい
SELECT
会社.会社名
, 社員.社員名
, 部署.部署名
FROM 社員 INNER JOIN 会社
ON 社員.社員コード = 会社.会社コード
INNER JOIN 部署
ON 社員.部署コード = 部署.部署コード
WHERE 社員.社員コード = '田島';
会社名 社員名 部署名
----- ----- -----
B化学 田島 開発
３、会社ごとに社員数をまとめたい
※社員が登録されていない会社があるので外部結合(OUTER JOIN)を使っている。
SELECT
会社.会社コード
, COUNT(社員.社員名) AS 社員数
FROM 会社 LEFT OUTER JOIN 社員
ON 社員.会社コード = 会社.会社コード
GROUP BY 会社.会社コード
会社コード 社員数
----- -----
C0001 3
C0002 3
C0003 0
いずれもテーブル同士を結合し、外部キーを参照して欲しいテータを抽出している。
非正規形でパフォーマンスを追求するデメリット
一方、非正規化することにより、正規化のデメリットであったパフォーマンスは解決できる。
「ある社員の勤めている会社を知りたい」を非正規化
例えば、テーブルを分けず、１つのテーブルで全ての情報を管理していた場合、以下のSELECT文でデータは抽出できる。ここでは、結合をしていない為、シンプルなSQLになっておりパフォーマンスも改善できる。
| 会社コード | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| C0001 | A商事 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | A商事 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | A商事 | 001F | 三島 | 47 | D03 | 営業 |
| C0002 | B化学 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | B化学 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | B化学 | 010A | 渋谷 | 33 | D04 | 総務 |
SELECT
会社名
, 社員名
, 部署名
FROM 社員
WHERE 社員名 = '田島'
会社名 社員名
----- -----
B化学 田島
問題点
大きな問題点として、データの更新が非常に非効率になるなど保守性が良くない。
例えば、ある会社が買収され、会社の名前が変更になった場合、社員テーブルの会社名のレコードを一つ一つ更新する必要がある。
非正規化の場合：
UPDATE 社員
SET 会社名 = 'E物産'
WHERE 会社コード = 'C0001'
対し、正規形の場合、会社テーブルの１レコードのみ更新すればいい。
UPDATE 会社
SET 会社名 = 'E物産'
WHERE 会社コード = 'C0001'
ではどちらがいいか
原則的には非正規化は認められない。
引用の引用：
クリス・デイト
十分に正規化された設計を諦めてもよいのは、パフォーマンスを向上させるためのその他全ての戦略が要件を満たさないだけである。
まとめ
・正規化は、パフォーマンスが悪くなることも
・非正規化は、パフォーマンスは改善されるが、保守性に難あり
・原則的には正規化を行うこと。
・非正規化は最後の手段。
参考文献：
達人に学ぶDB設計 徹底指南書