5-1 正規化の功罪
前章まで、リレーショナルデータベースにおける論理設計の基本を学習した。ここで、なぜ、正規化が重要なのか、メリットは何かをまとめる。ーひとえに、データの整合性を保つことである。データベースは**ユーザの利用する**データを一元保管できる倉庫。ーデータが間違っていることは**絶対あり得ない**。 正規化は、データの整合性を厳密に保つ手法である。正規化とSQL（検索）
内部結合を使うケース
ビジネスサイド側から、調査してほしい要望がきて、立て続けにやっぱり、他も調査してほしいとなった場合、データを保管しているデータベースにアクセスすることがあると思います。 その際に、正規化されたテーブルではデータが分散されているため、内部結合や外部結合を使って、調査しなければならない。外部結合を使うケース
外部結合を扱うケースでは、内部結合で要件を補えない時に活用する。商品の個数をカウントする際などである。現在、商品テーブルの在庫数カラムが0、つまり在庫が切れていたとしても扱っている商品を確認したい場合、外部結合を利用する。 内部結合では、在庫数が0の場合だと、漏れてしまう。従って、外部結合が適切である。このように、正規化されたテーブルであっても、SQL文で結合を使うことで、取得したい情報を取得できる。ー正規化が、無損失分解だからである。つまり情報を一切失わない操作であるから。しかし、他方でSQLにおける結合は非常にコストの高い操作であり、結合するテーブル数、およびテーブルのレコード数が増えれば増えるほど処理時間かかる。
正規化することでシステムパフォーマンスが劣化する原因の多くが、SQLの結合操作にある。
POINT
非正規化テーブルならば、結合を使わずに済む。
正規化とSQL（更新）
正規化と更新処理はどのような関係があるのだろうか?この場合は、正規化の方が軍牌が上がる。 Ex. A商事がB商事を回収したケース B商事社員の会社テーブルの会社名を更新する必要がある。 第二正規化社員テーブルだと、社員数のレコード分だけ更新する必要がある。当然、レコード数が多ければ、パフォーマンスは悪い。 これに対して、第三正規化テーブルだと、会社テーブルと社員テーブルが独立したテーブルであるため、会社テーブルのB商事のレコードのみ更新すれば可能である。正規化と非正規化、どちらが正解なのか？
エンジニアや理論家によって意見は分かれるそうである。ーとは言っても、「正規化」はDB設計において*原理原則-✴︎*である。正規化と検索SQLのパフォーマンスは**強いトレードオフ**の関係にある。*厳しく*、正規化すればパフォーマンスは悪くなり、パフォーマンスを求め、非正規化すれば*データの整合性*が悪くなる。 ✴︎・・・「クリス・デイトによれば、非正規化は最終初段である。正規化の次数は高ければ高いほど、良好である。」5-2 非正規化とパフォーマンス
非正規化を実施する際に考えるべきトレードオフについて説明する。非正規化はしばしばSQLのパフォーマンスを悪化させる論理設計となる。パフォーマンスが悪化するパターンを説明する。サマリデータの冗長性とパフォーマンス
正規化、冗長性排除によって引き起こされる性能問題は、SQL構文の観点から大きく2つある。 1. サマリーデータ の冗長性排除 1. 選択条件の冗長性排除 両方とも、必ず1vs多の関連を持つ2つテーブル間で発生する。受注テーブル
| 受注ID | 受注日 | 注文者名義 |
|---|---|---|
| 0001 | 2022-05-01 | AAAさん |
| 0002 | 2022-06-01 | BBBさん |
| 0003 | 2022-06-02 | CCCさん |
受注明細
| 受注ID | 受注明細連番 | 注文者名義 |
|---|---|---|
| 0001 | 1 | マカロン |
| 0002 | 2 | 紅茶 |
| 0003 | 3 | アメ |
| 0001 | 4 | 米 |
| 0004 | 1 | 牛肉 |
| 0003 | 2 | 鍋セット |
| 0001 | 3 | 日本茶 |
| 0003 | 3 | アーモンドミルク |
| 0005 | 1 | ドレッシング |
1対多の関係性
これらのテーブルは第3正規化のテーブルである。理由はん部分関数従属も、推移的関数従属もないからである。
例えば、以下のデータを取得したい場合どんなSQLで取得するだろう??
Q: 受注日ごとに何個の商品が注文されているかを調べよ。
A
SELECT 受注.受注日,
COUNT(*) AS 商品数
FROM 受注 INNER JOIN 受注明細
ON 受注.ID = 受注明細.受注ID
GROUP BY 受注.受注日;
これで取得はできるが、パフォーマンスが低くなる。理由は、結合しているからである。
結合しない手段はあるだろうか?
A 受注テーブルにカラム 商品数を追加すれば良いだろう。
一つのテーブルで取得したいデータが取れるので、結合しなくて済む。注文というエンティティに商品数というサマリーデータを持つことは冗長性を持たせた設計の一種で言える。このような変更は、「非正規化でもある」 。
理由は商品数は非キー列である商品日に従属しているから
つまり、第3正規形ではなくなる。更新処理時は「問題が起こるが、」検索処理時は非常に高いパフォーマンスを発揮できる。
選択条件の冗長性とパフォーマンス
選択条件の冗長性とパフォーマンスに与える影響についてである。 Q.下記の問題ではどのようなSQLになるでしょう??問題
受注日が 2022-06-01〜2022-06-02の期間に注文された商品の一覧を表示せよ
前提 テーブルは第3正規化テーブルであり、
受注テーブルと受注詳細テーブルがある。
この場合、上記の日付に紐づく商品詳細を確認するには、2つのテーブルを使う必要がある。
A.
SELECT 受注.受注ID,
受注明細.商品名
FROM 受注 INNER JOIN 受注明細
ON 受注.ID = 受注明細.受注ID
WHERE 受注.受注日 BETWEEN '2022-06-01' AND '2022-06-02';
このようなSQLになるだろう。しかし、結合している点からコストが高く、パフォーマンスはやや落ちる。この問題を解消するためには、「テーブル構成」を考える。
それは受注明細テーブルに**「受注日」**を追加し、受注テーブルを見る必要をなくす。
SELECT 受注ID,
商品名
FROM 受注明細
WHERE 受注日 BETWEEN '2022-06-01' AND '2022-06-02';
SQLシンプルになった。
列を1つ追加することでSQLから結合をなくすことが可能である。
選択条件を冗長 にすると第2正規形ではなくなる
*変更後「受注明細」テーブル*は先ほどのサマリーデータの追加した場合のテーブルと同じく、正規形に違反する。理由は主キーの一部である「受注ID」から非キー列「受注日」への部分関数従属が生まれてしまうからしたがって、第二正規形でもなくなる。
POINT
選択条件を冗長に保持すると正規形に違反するが、検索を高速化する。
5-3 冗長性とパフォーマンスのトレードオフ
非正規化が更新不整合のリスクを増やすことについて解説する。 1. リスク1: 非正規化は、検索のパフォーマンスは向上させるが、更新のパフォーマンスは低下させる。 1. リスク2: データのリアルタイム性(鮮度)を低下させる。 1. リスク3: 後続の工程で設計変更させると、手戻りが大きい。更新時のパフォーマンス
たとえば、「商品数」というサマリーデータを追加した「受注テーブル」を考える。このデータを受注テーブルに保つためには、「受注テーブル」に注文データを登録される際に商品数を計算しておく必要がある。また、注文内容は一度登録されたら「確定」ではなく、しばらく変更が可能である。従って、定期的に「受注テーブル」 カラム商品数を更新する必要がある。データのリアルタイム性
上記の結論から言えば、データのリアルタイム性がある。 「商品数」が受注受付後に変更されるという場合その変更はどのようなタイミングで変更されるだろうか?ー結論、商品数というデータにどの程度の最新情報を求めるかは**業務要件**と照らし合わせ、考える必要がある。 システム反映時期が短ければ短いほど、システムへの負荷は大きく、一方で、反映時期が長ければ長いほど、ユーザービリティは著しく低くなる。 ゆえに、両者のバランスが取れた平衡点を見つけ出さないといけない。改修コストの大きさ
最後の問題が改修コストの大きさである。データモデルの変更は、コードレベルの変更に比べて非常に改修コストがかかる。ーデータのフォーマットがプログラムを決めるDOAの原則であるから。ー性能が著しく悪く、テーブル構成を変更したいと言った場合にアプリケーションを大きく改修しなければならなかったとしても、なかなか受け止めてもらえない。 論理設計する際に、「システムの品質は、論理設計できまる!」気概を持つ必要があるからである。かつ、論理設計する担当者は**正規化の理論を理解しているだけでなく、それによって生じるさまざまなトレードオフを熟知した上で、あらゆる要件を満たせるバランス点を見つけ出さないといけない。**論理設計を行うには、論理層、概念スキーマだけでは無理がある。というのも、
パフォーマンス向上するためにはファイルやハードウェアを考える際は、「物理層」まで踏み込んで考えないといけないからである
POINT
論理設計には物理設計の知識が必要である。