Help us understand the problem. What is going on with this article?

達人に学ぶDB設計 | 正規化と非正規化、インデックスについて

達人に学ぶDB設計 徹底指南書 ミック(2012)

こちらの書籍を学習して、特に参考になった以下の2点を中心に、まとめたいと思います。

  • 正規化と非正規化
  • インデックスの利用

要点

DB設計において、整合性とパフォーマンスのトレードオフを理解することの重要性が強調されています。

DOA

はじめに、システム設計におけるDB設計の重要性が説かれています。システム設計には2つの対になるアプローチがあります。

  • データ中心アプローチ(Data Oriented Approach: DOA)
  • プロセス中心アプローチ(Process Oriented Approach: POA)

DOAは、プログラムより前にデータの設計をすることで、POAはその反対です。

プログラムは短期間で大きく変わっていくことに対し、データは永続的であることから、現在はDOAが主流になっています。

DB設計から行うということは、次のことを意味します。

  • DB設計を後から変更するコストが大きい。
  • DB設計の不備をアプリケーションの実装で補うことはできない。

したがって、最初の段階から、手戻りがないように万全な設計をしておく必要があります。

論理設計と物理設計

DB設計には、論理設計と物理設計の2種類があります。

論理設計

論理設計は、データの要素および、データ同士の関係を決める設計で、以下のステップで行います。

  1. エンティティの抽出・定義
  2. 正規化
  3. ER図の作成

1の エンティティ とは、「顧客」や「注文履歴」など現実世界に存在するデータの集合体を指す言葉です。1は、最終的にテーブルに格納するエンティティと、各エンティティが保持するデータ(属性)を決める工程です。

2の 正規化 は、データの更新が整合的に行えるように、エンティティのフォーマットを整理する工程です。論理設計において重要な土台であり、この記事では、正規化について詳しく扱います。

3の ER図 (Entity-Relationship Diagram)は、正規化によって細かく分割されたエンティティ同士の関係を分かりやすく表現した見取り図です。

物理設計

物理設計は、データを格納するための物理的な領域や格納方法を決める工程です。大きく以下の5つのタスクが含まれます。

  1. テーブル定義
  2. インデックス定義
  3. ハードウェアのサイジング
  4. ストレージの冗長構成決定
  5. ファイルの物理配置決定

この記事では、2の インデックス定義 について後ほど詳しく扱います。

正規化

正規化 とは、データの冗長性を排除し、一貫性と効率性を持たせる(= 正規形 に整える)ことです。

冗長性とは、1つの情報が複数のテーブルに存在している状態です。冗長性があると、1つのテーブルを更新した際に、別のテーブルの同じ情報が更新されるまで、不整合が発生する可能性があります。

正規形は、第1〜第5正規形があり、数字が上がるほど正規化のレベルが上がっていきます。通常の業務では、第3正規形までを考えることが多いとのことです。

第1正規形

第1正規形はとても単純で、 「テーブルの1つのマスには、1つの値(スカラ値)のみを含む」 というものです。以下は第1正規形を満たさないテーブルです。

社員ID 社員名
000A 加藤 達夫 信二
000B 藤本
000F 三島 敦 陽子 清美

「子」カラムに2つ以上の値が入っていることが違反しています。これを直すと、以下のようになります。

社員ID 社員名
000A 加藤 達夫
000A 加藤 信二
000B 藤本
000F 三島
000F 三島 陽子
000F 三島 清美

これで第1正規形は満たすのですが、これでは主キーを決められないという問題があります(NULLを持つカラムはキーにできないため)。

また、「社員」と「子」という2つのエンティティを含んでしまっているため、テーブルの意味やレコードの単位をすぐに理解できません。

この2つの問題を解決するには、以下のようにテーブルを2つに分ける必要があります。

社員テーブル

社員ID 社員名
000A 加藤
000B 藤本
000F 三島

扶養者テーブル

社員ID
000A 達夫
000A 信二
000F
000F 陽子
000F 清美

※赤字が主キー

関数従属性

1つのマスに複数の値を入れてはいけない理由は、主キーが各列の値を一意に決定できなくなるからです。

第1正規形を満たした時のように、1つの列(複合キーの場合は複数の列)の値を決めると、別の列の値が1つに決まることを、 関数従属性 といいます。

この関数従属性が正規化を理解する鍵になります。

第2正規形

以下は第2正規形を満たしていないテーブルです。

会社コード 会社名 社員ID 社員名 年齢 部署コード 部署名
C0001 A商事 000A 加藤 40 D01 開発
C0001 A商事 000B 藤本 32 D02 人事
C0001 A商事 001F 三島 50 D03 営業
C0002 B化学 000A 斎藤 47 D03 営業
C0002 B化学 009F 田島 25 D01 開発
C0002 B化学 010A 渋谷 33 D04 総務

このテーブルの主キーは、{ 会社コード, 社員ID }です。したがって、他の全ての列はこのキーに従属するのですが、「会社名」だけは、主キーの一部である「会社コード」に従属しています。つまり、「会社コード」が決まれば、「会社名」が決まります。

このように主キーの一部の列に対して従属する列がある関係を、 部分関数従属 といいます。第2正規形では、この部分関数従属を解消する必要があります。

上のテーブルを第2正規形にするには、以下のようにテーブルを分割します。

社員テーブル

会社コード 社員ID 社員名 年齢 部署コード 部署名
C0001 000A 加藤 40 D01 開発
C0001 000B 藤本 32 D02 人事
C0001 001F 三島 50 D03 営業
C0002 000A 斎藤 47 D03 営業
C0002 009F 田島 25 D01 開発
C0002 010A 渋谷 33 D04 総務

会社テーブル

会社コード 会社名
C0001 A商事
C0002 B化学

第2正規形にすることで、正規化前のテーブルで生じる次のような問題を解消できます。

  • 新しい会社(C建設)を登録したい場合、社員情報が1つでも存在しないと、「社員ID(主キー)」がNULLになるため、登録できない。
  • { C0001, A商事 }という会社名を、誤って{ C0001, A商社 }と登録してしまうことを防ぐことができない。

第2正規化をまとめると、 「異なるレベルのエンティティを、きちんとテーブルとして分離する」 作業だと言えます。

第3正規形

第2正規形化した上の社員テーブルは、まだ第3正規形は満たしていません。「部署コード」と「部署名」を見ると、明らかに2つの間に関数従属性があります。

つまり、主キーである{ 会社コード, 社員ID }が決まれば「部署コード」が決まり、「部署コード」が決まれば「部署名」が決まる、という段階的な従属関係があります。

このような関係を 推移的関数従属 と呼び、第3正規形ではこれを解消します。そのために、上の社員テーブルを以下のように分割します。

社員テーブル

会社コード 社員ID 社員名 年齢 部署コード
C0001 000A 加藤 40 D01
C0001 000B 藤本 32 D02
C0001 001F 三島 50 D03
C0002 000A 斎藤 47 D03
C0002 009F 田島 25 D01
C0002 010A 渋谷 33 D04

部署テーブル

部署コード 部署名
D01 開発
D02 人事
D03 営業
D04 総務

第3正規形を満たしたことで、第2正規形と同じように、社員情報がない部署を新しく登録できないという問題などが解消されます。

第3正規化も、同じように 「異なるレベルのエンティティを、きちんとテーブルとして分離する」 作業だと言えます。

より高次の正規化

一般的な業務の設計では、ほとんどのケースで第3正規形までを意識すれば事足ります。より高次の正規化としては、以下があります。

  • ボイス-コッド正規形(通称 第3.5正規形)
  • 第4正規形
  • 第5正規形

ここでは詳しく扱いませんが、いずれも 「異なるレベルのエンティティを、きちんとテーブルとして分離する」 ことで、自然に満たしていることが多いと言えます。

無損失分解

正規化によるテーブルの分割は、情報を完全に保存したまま分割することから、無損失分解 と呼ばれます。

したがって、正規化によって分割されたテーブルは、結合によって、いつでも非正規化テーブルに復元することができます。

正規化は常にするべきか?

この問題に対する回答は、 「第3正規形までは、原則として行う」 というものです。

正規化には、以下のような利点があります。

  • データの冗長性が排除され、更新時の不整合を防止できる。
  • テーブルの持つ意味が明確になり、開発者が理解しやすい。

一方で、以下のような欠点もあります。

  • テーブルの数が増えるため、SQL文で結合を多用することになり、パフォーマンスが悪化する。

そのため、あえて正規形を低次なものにとどめる設計が採用されることがあります。この「非正規化」について、次節で扱います。

非正規化

正規化による検索パフォーマンスの問題

以下のような1対多の関連を持つ「受注」テーブルと「受注明細」テーブルがあります。いずれも十分に正規化されています。

受注テーブル

受注ID 受注日 注文者名義
0001 2020-06-23 岡野 徹
0002 2020-06-23 浜田 健一
0003 2020-06-24 石井 恵子
0004 2020-06-25 若山 みどり
0005 2020-06-25 庄野 弘一
0006 2020-06-28 若山 みどり
0007 2020-06-29 岡野 徹

受注明細テーブル

受注ID 受注明細連番 商品名
0001 1 マカロン
0001 2 紅茶
0001 3 オリーブオイル
0001 4 チョコ詰め合わせ
0002 1 紅茶
0002 2 日本茶
0002 3 ティーセット
0003 1
0004 1 アイロン
0004 2 ネクタイ
0005 1 チョコ詰め合わせ
0005 2 紅茶
0005 3 クッキーセット
0006 1 牛肉
0006 2 鍋セット
0007 1

このテーブルを使って、「受注日ごとに何個の商品が注文されているかを調べる」という処理を考えます。

発行するSQLは、以下のようになります。(分かりやすさのために日本語で記載しています)

SELECT 受注.受注日, COUNT(*) AS 商品数
FROM 受注 INNER JOIN 受注明細
                 ON 受注.受注ID = 受注明細.受注ID
GROUP BY 受注.受注日;

結果

受注日 商品数
2020-06-23 7
2020-06-24 1
2020-06-25 5
2020-06-28 2
2020-06-29 1

サンプルでは数行のレコードですが、実際の業務では非常に多くのレコードが格納されます。したがって、二つのテーブルを結合するという高コストな処理は、パフォーマンス上の問題を引き起こす可能性があります。

(2020/6/25 追記)
こちらの書籍は初版が2012年で、当時のDBMSの性能に基づいています。現在は、後述するインデックスも併用することで、結合のパフォーマンスはかなり改善されるということをコメントで教えていただきました。

非正規化によるパフォーマンスの改善

そこで、結合を利用せずに処理するために、「受注」テーブルに「商品数」カラムを追加します。

受注テーブル

受注ID 受注日 注文者名義 商品数
0001 2020-06-23 岡野 徹 7
0002 2020-06-23 浜田 健一 7
0003 2020-06-24 石井 恵子 1
0004 2020-06-25 若山 みどり 5
0005 2020-06-25 庄野 弘一 5
0006 2020-06-28 若山 みどり 2
0007 2020-06-29 岡野 徹 1

このテーブルを使えば、以下のシンプルなSQLで上と同じ結果が得られます。

SELECT DISTINCT 受注日, 商品数
FROM 受注;

テーブルに集計データを持たせることで、パフォーマンスを改善することができました。

変更したテーブルを見ると、「商品数」は「受注日」という非キー列に従属しています。したがって、第3正規形ではなくなっています。

このように非正規化することで、後述する問題が発生することになりますが、その トレードオフ として、検索処理が改善されていると言えます。

(2020/6/27 追記)
実際にこのケースでどのくらいパフォーマンスが改善されるのか、検証してみました。

【SQLのパフォーマンス検証】結合のコスト、インデックスの効果

非正規化によるリスク

非正規化によるリスクに以下の2つが挙げられます。
1. 更新のパフォーマンスが低下する。
2. データのリアルタイム性が低下する。

更新時のパフォーマンス

上の例では、「受注」テーブルに「商品数」データを追加したことで、注文内容の変更があった場合に反映するために、定期的に「商品数」カラムを更新する処理が必要になります。

データのリアルタイム性

上記の「商品数」カラムに注文内容の変更が反映されるまで、古いデータが格納されることになります。反映する周期が短いほどリアルタイム性は向上しますが、その分システムへかかる負荷は高くなります。

(2020/6/25 追記)
現在は、定時更新するケースはまれで、一連の更新処理をトランザクションに入れる方法が一般的だと、コメントで教えていただきました。その際にデータベース全体の一貫性が壊れないように細心の注意が必要とのことです。

また、集計データを元のテーブルに追加するのではなく、別のテーブルを作成し、そのテーブルにのみアクセスさせるという方法もあるようです。(いわゆるマテリアライズド・ビューのようなもの)

参考 データベース設計の際に気をつけていること 食べチョク開発者ブログ

正規化と非正規化のバランス

本文では他にも正規形の性能面での難点がクローズアップされていますが、「 正規化は、可能な限り高次にすることが大原則 」だと強調されています。

それでも性能のために非正規化が必要な時は、トレードオフを知り尽くした上で、平衡点を探し出す能力が必要とされます。

この時、パフォーマンスの問題を考えるためには、ファイルやハードウェアといった物理層に踏み込まざるを得ないため、論理設計には、物理設計の知識も必要だと意識しておかなければなりません。

インデックスによるパフォーマンス向上

インデックスとは、(x, α)という形式の配列であり、xはキー値、αはそれに結びつく情報(実データか、それへのポインタ)です。テーブルの特定レコードに狙い撃ちでアクセスするためのもので、テーブルとは別に保持されます。

言葉だけでは分かりにくいですが、以下のページのB木のイメージなどが参考になります。

データベース性能を向上させる「インデックス」を理解する

インデックスの利点

  1. アプリケーションのコードに影響を与えない。
  2. テーブルのデータに影響を与えない。
  3. それでいて性能改善の効果が大きい。

インデックスを作成する際に、アプリケーションのプログラムを変更する必要はなく、データの中身やテーブル構造が変化することもありません。

このように使う際のハードルが低いにもかかわらず、使い所が正しければ大きな性能改善が見られます。

いくつかの種類があるインデックスのうち、汎用性が高く頻繁に利用されるのが B-treeインデックス です。

B-treeインデックスはどの列に作れば良いか?

  1. 大規模なテーブル(レコード数1万件以上が目安)
  2. カーディナリティが高い列
  3. WHERE句やORDER_BY句、GROUP_BY句、または結合条件に使用されている列

カーディナリティ

カーディナリティとは、特定のカラムの値の種類の多さのことです。

例えば、「性別」を表すカラムの場合、「男性」「女性」「不詳」の3種類があります。このとき、カーディナリティは「3」であり、非常に小さい例です。

「口座番号」のように、一意の値を持つものや、1年で365種類ある「受付日」など、大きなカーディナリティを持つカラムほどインデックス作成の効果が高いです。特定のキー値を指定したとき、全体のレコードの5%程度に絞り込めることが目安になります。

複合カラムに対してインデックスを作成する場合は、1つのカラムの種類ではなく、組み合わせによってカーディナリティを計算します。

B-treeインデックスの注意点

主キーおよび一意制約の列には不要

主キー制約や一意制約を作成する際には、内部的にB-treeインデックスを作成して、重複値をチェックしているため、そのカラムに二重にインデックスを作成する必要はありません。

更新性能は劣化する

インデックスはテーブルとは独立のオブジェクトとして保持されます。したがって、対象のカラムの値が変更されると、インデックス内部に保持している値も変更しなければなりません。つまり、インデックスを作成すればするほど、更新性能が劣化するため、必要なインデックスのみを作成する必要があります。

本から学んだこと

データベースの設計において、論理設計(整合性)と物理設計(パフォーマンス)のトレードオフを意識することの大切さが分かりました。

それを踏まえてしっかりと設計した上で、インデックスなどのDBの機能を適切に使用したいです。

その他の内容

本記事で紹介した他にも、以下のような内容がとても参考になりました。

  • ストレージの分散配置
  • バックアップとリカバリ
  • ER図の詳細
  • SQLにデータへのアクセスの仕方を教える「統計情報」

後半では、DB設計のバッドノウハウやグレーノウハウが紹介されています。

最初に取り上げた正規化を満たしていないテーブルなど、良くない設計がどのようにして生まれてしまうのか。その背景から、開発現場の大変さも想像することができ、読み物としても面白かったです。

その次に、明らかに悪いとは言えないグレーゾーンの設計例の数々が紹介されています。どこが問題なのか、そして、その解決策として「ビュー」を使うことなど、様々なテクニックを知ることができ、大変参考になりました。

達人に学ぶDB設計 徹底指南書 ミック(2012)

その他の参考記事

データベース設計の際に気をつけていること 食べチョク開発者ブログ

実際の開発で気をつけるべき点について分かり、とても参考になりました。本記事で紹介した書籍の内容にも通じる点が多かったです。

k_kind
Webサービスの開発職を目指して日々勉強中です! 関心のある技術 Ruby, Rails, JavaScript, jQuery, Vue.js, Docker, AWS, CircleCI
https://memorizer.tech
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした