0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@hitobussi(hito bussi)

達人に学ぶDB設計指南書要約 3

0
Posted at

はじめに

この記事は達人に学ぶDB設計指南書の第4章からの要約です。
達人に学ぶDB設計指南書

ER図〜複数のテーブルの関係を表現する

正規化によってテーブルを整理していくと、どんどんテーブルの数が増えていく。こうして増えたテーブルを、そのままの状態で管理することは、人間にはできない作業である。こうした多数のテーブルを管理するために、それぞれのテーブルがどういう意味を持っていて、テーブル同士が互いにどういう関係にあるのかということを明示するために作る図をER図と呼ぶ。
ER図には流派があるが、ここでは記載しない。読み方、書き方は本で学んでほしい。

テーブル同士の関連を見抜く

ER図を書くときに最初に着目するポイントは、あるテーブルの主キーが他のテーブルに列として含まれているかどうかという点である。なぜなら、もしその場合、2つのテーブル間には意味的な関連があることになるからである。
前の記事の第3正規化で使用したテーブルを見ると、「会社」テーブルと「社員」テーブルの場合、「会社」テーブルの主キーである会社コードが、「社員」テーブルにも含まれている。このような場合、2つのテーブルの間には1対多の関係が成立している、という。例えば、「会社」テーブルには、1つの会社は1行しか含まれませんが、「社員」テーブルには1つの会社が複数行に現れる。つまり、「会社:1」に対して「社員：多」という関係があるわけだ。
個人的には以下のように考えている。

親と子の関係は1対多である。

この場合、会社が「親」、社員が「子」である。(親、子に関しては過去の記事に記載。)
より厳密にいうと、このテーブルの関係を見ると、「会社」テーブルには社員のいない会社「C建設」も登録可能であるため、「1つの会社には0〜n人の社員が働いている」という言い方が適切である。

1対1、1対多、多対多

このように、同じ意味の列を持っているテーブル同士の間では、一般に次の3パターンの関連があり得る。

  1. 1対1
  2. 1対多
  3. 多対多

このうち、1対1はあまり見かけない。というのも、2つのテーブルのレコードが1対1に対応するということは、要するに2つのテーブルの主キーが一致するケースであり、そうであれば普通は1つのテーブルにまとめてしまっても問題ないからである。
1対多は最もよくある関連のタイプである。基本的に正規化によって生まれる関連はこのカテゴリに属す。
最後に多対多だが、これは少し特殊なカテゴリである。というのも、最初に業務要件からテーブルを作っていくと、この多対多の関連を持ったテーブル群が出来上がることがある。しかし、リレーショナルデータベースの「お約束」として、この多対多の関連は作ってはならないということになっている。
結合する場合に多大な処理コストがかかってしますからである。

多対多のデメリットと防ぐ方法

現実世界に存在するエンティティを、リレーショナルデータベース内のER図として記述しようとすると、多対多の関連を持ったエンティティができることは珍しいことではない。
この問題を解決するための方法が関連実体である。多対多の2つのエンティティの間にもう1つエンティティを作ることで解決する方法である。
この方法に関しては本にわかりやすい図があるので、詳しくは本で学んでほしいが、文字で起こすと以下のようなイメージである。

A -< B >-C

上記ではAとCが多対多の関係であったのを、Bを作ることで解決できる。

正規化のデメリット

ここまでで、正規化について学んできた。なぜ正規化する必要があるかをまとめると、「データの整合性を保持するため」である。
しかし一方で、正規化にもデメリットはある。それは、「正規化されたテーブル群に対するSQLが非常に遅くなってしまい、しばしばシステムとして実用に耐えないぐらい、パフォーマンス劣化を招いてしまうこと」である。
正規化はテーブルを分割するので、その分SQLで結合することも多くなる。結合は非常に処理コストが重いので、このデメリットは正規化の特性上当然である。
このデメリットの実際の例と解決法は本で学んでほしい。本では、あえて非正規化することでパフォーマンス改善を図る事例が載っている。

冗長性とパフォーマンスのトレードオフ

パフォーマンスを求め、あえて非正規化することに対するリスクは以下のとおりである。

  1. 非正規化により更新不整合が起きる
  2. 非正規化は、検索のパフォーマンスは向上させるが、更新のパフォーマンスを低下させる
  3. データのリアルタイム性を低下させる
  4. 後続の工程で設計変更すると、手戻りが大きい

正規理論を熟知し、美しい論理モデルを考えられる人が物理レベルについて全くの無知、ということもしばしばある。しかし、本当の論理設計は、正規化によって起きる物理レベルのメリットデメリットを理解する、つまり論理と物理のトレードオフを理解して初めて可能になる。

データベースのパフォーマンスを決める要因

ここでは、データベースのパフォーマンス設計という観点において重要なポイントをもう2つ紹介する。

インデックス

インデックスはSQLチューニングの手段として非常にポピュラーで、これを利用しないシステムはない、というぐらいよく使う。
インデックスとは、プログラミング言語的な表現をすると(x, α)という形式の配列である。
これがどういう仕組みで使われるかは後述するが、本の索引をイメージするとわかりやすい。

統計情報

統計情報が重要な理由は、これがSQLのアクセスパス(データを取得する経路)を決める最大の要因だからである。これはカーナビをイメージするとわかりやすい。カーナビは目的地を決めると勝手に道を決めてくれるが、DBMSは統計情報をもとに最適なアクセスパスを勝手に決めて、SQLを実行してくれる。

インデックス設計

インデックスはSQLのパフォーマンス改善のための非常にポピュラーな手段である。その理由を以下に記載する。

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

詳しくは本を読んでほしいが、アプリケーションのコード変更をせず、テーブルの変更もしないのに性能改善が大きいならそりゃ使うわな。
普通パフォーマンスを上げるならコードを変えるかデータベースを変えるかしないといけないし
ただ、作れば作るほどいいというわけではない。無駄なインデックスを作成することで、パフォーマンスが大幅に下がる場合もある。

B-treeインデックス

インデックスにはいくつかの種類があり、またDBMSによっても使用できる種類に差がある。しかし、頻繁に利用するインデックスは1種類しかない。それがB-treeインデックスである。
しかし、B-treeインデックスのパフォーマンスが非常に優れているというわけではない。
B-treeインデックスが優れているのは、あらゆる場面で平均点高くパフォーマンスを改善してくれる点である。具体的には、均一性、持続性、処理汎用性、非等値性、親ソート性が全て優れている。

  1. 均一性: 各キー値の間で検索速度にばらつきが少ない
  2. 持続性: データ量の増加に比べてパフォーマンス低下が少ない
  3. 処理汎用性: 検索、挿入、更新、削除、のいずれの処理もそこそこ早い
  4. 非等値性: 等号(=)に限らず、不等号(<、>、<=、>=)を使ってもそこそこ速い
  5. 親ソート性: GROUP BY、ORDER BY、COUNT、MAX、MINなどなどソートが必要な処理を高速化できる

この全ての平均点が高いのがB-treeインデックスの利点である。
B-treeインデックスの仕組みは本で学んでほしい。

Be-treeインデックスの設計方針

B-treeインデックスはもちろん、闇雲に作ればいいわけではなく、そこにはいくつかの指針がある。

  1. 大規模なテーブルに対する作成する。
  2. カーディナリティの高い列に作成する
  3. SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する

B-treeインデックスとテーブルの規模

テーブルのデータ量が少ない場合、フルスキャンに任せた場合の方が高速な領域がある。目安として、レコード数が10万件以下の場合はB-treeインデックスはほとんど効果はない。

B-treeインデックスとカーディナリティ

B-treeインデックスを作成する列として敵不敵を判断するための最も重要な情報がカーティナリティである。これは、特定の列の値が、どのぐらいの種類の多さを持つかということを表す概念である。
例えば、性別だと「男」、「女」「その他」その3種類が思い浮かぶが、この場合カーディナリティは3となる。一方「口座番号」や「マイナンバー」となるとカーディナリはとてつもない数字になる。この場合、B-treeインデックスは効果を発揮する。
しかし、カーディナリティにも注意点がある。
例えば、カーディナリティが100の列があったとしても、全体の99%が100で1%が1〜99だった場合、つまりカーディナリティが多くてもデータのばらつきがない場合はB-treeインデックスは効果は発揮しない。また、(a, b, c)という列があり、それぞれのカーディナリティが5, 10, 2にだった場合、カーディナリティが高いものを最初におく、つまり、(b, a, c)とした場合が1番効果を発揮する。(なるべく先頭に近いキーのカーディナリティが高いほど、効率的に絞り込めるため性能的に有利である。)

インデックスとSQL

インデックスが使用されるには、SQLの記述方法としていくつか気を付けるべきポイントがあるので、代表的なものを以下に挙げる。以下では、SomeTableのcol_1という列にインデックスを適用しているとする。以下のようなSQLはいずれもインデックスを利用できない。

インデックス列に演算を行っている

SELECT * 
    FROM SomeTable
    WHERE col_1 * 1.1 > 100;

この場合、以下のように同値の式変形を行うことで回避可能

WHERE col_1 > 100/1.1

インデックス列に対してSQL関数を適用している

SELECT *
    FROM SomeTable
    WHERE SUBSTR(col_1, 1, 1) = 'a';

IS NULL述語を使っている

SELECT *
    FROM SomeTable
    WHERE col_1 Is NULL;

否定形を使っている

SELECT * 
    FROM SomeTable
    WHERE col_1 <> 100;

後方一致、または中間一致のLIKE述語を使っている

SELECT * FROM SomeTable WHERE col_1 LIKE '%a';
SELECT * FROM SomeTable WHERE col_1 LIKE '%a%';

なお、前方一致では使用できる。

SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';

暗黙の型変換を行っている
固定長文字列型で定義されたcol_1に対する条件を書く場合の例を示す。

SELECT * FROM SomeTable WHERE col_1 = 10;

型変換をしていなかったり、明示的に型変換をした場合はインデックスが適用される。

SELECT * FROM SomeTable WHERE col_1 = '10';
SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

B-treeインデックスに関するその他注意事項

B-treeインデックスを使用する際のその他注意事項を以下に記載する。

  1. 主キーおよび一意制約の列には作成不要
  2. B-treeインデックスは更新性能を劣化させる
  3. 定期的なメンテナンスを行うことが好ましい

この中で個人的に気になったのが更新性能を劣化させること。
インデックスは一般的にテーブルとは独立のオブジェクトとしてDBMS内部に保持されている。したがって、インデックスが作成されている対象の列値が変更されると、インデックスないに保持している値も変更しなければならない。したがって、インデックス分も更新処理が行われる分更新性能が劣化する。
インデックスを作成するたびに更新性能が劣化するので、インデックスを作れば作るだけ良いというわけではないのだ。

まとめ

このほかにも統計情報など、まだまだ本の内容を書ききれていないが、知りたいところは書き終えたので一旦アウトプットはここまでとする。
また機会があれば続きを書く

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?