読書メモ「達人に学ぶ DB 設計徹底指南書」

一年ほど前にデータベーススペシャリストを取得しました。

最近業務でデータベース設計をすることもあり、再確認の意味で本書を読みました。

2012 年に出版された本ということで最新の DB 事情が載っている訳ではないでしょうが、設計における勘所というのはリレーショナルデータベースを使うかぎり、時代に左右されるものではないでしょう。

読んだときに線を引いた部分などを気になった点をまとめています。

本そのものの要約ではありませんし、単なる「個人メモ」ですので悪しからず。

ストレージの冗長化

• データベースの RAID は少なくとも RAID5（3 つ以上のディスク、分散、パリティ）構成で。予算次第で RAID10(1+0)（ストライピングしたそれぞれのディスクをミラーリング）

※ストライピング・・・データを複数ディスクに分散（壊れると復元不可）

※ミラーリング・・・ディスクの複製（速度は速くはならない）

ログファイル（トランザクションログ）

DBMS はデータベースの変更を一旦ログファイルとして保存し、のちに一括更新する。

データベーススペシャリストを勉強した時のトランザクションの説明に出てくる WAL とかのログですね。

DBMS のファイルの種類とディスクへの配置

データファイル、インデックスファイル、システムファイル、一時ファイル、ログファイルをどのように物理的にディスクに配置するか検討する必要がある。

IO の多いデータファイルを他と分けるとか Blob はデータサイズが膨らむので分けるとか。

バックアップ

• フルバックアップ
• 差分バックアップ（フルバックアップからの差分を累積）
• 増分バックアップ（前回のフル or 増分バックアップからの増分）

論理設計

• 可変長文字列はキーにしない

非正規化

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

1. サマリデータの冗長性排除による問題

   例：受注と受注明細がある場合に受注内の注文数の合計を出す場合
   → 正規化されたテーブルでは受注ごとの受注明細を count するクエリとなるが、膨大なレコードを持つテーブル同士の結合となってしまう 2.選択条件の冗長性排除による問題

   例：ある期間内に受注した商品の一覧
   　 → 正規化されたテーブルでは明細に受注日を持たないためこちらも結合が必要になる。

あえて、注文数を受注テーブルに持たせたり明細に受注日を持たせる冗長化（非正規化）することもできるが、

更新時のパフォーマンスやデータのリアルタイム性の要否なども検討すること。

インデックス設計

B-tree インデックス

B-tree は平衡木のためインデックスの検索性能は O(log n)となるが、挿入・更新・削除を繰り返していくうちに、平衡性が失われていく。

これを解消するのがインデックスの再構成。

B-tree インデックスの使い所

• データ件数の多いテーブル（1 万件以下の場合は検索性能も効果なし）
• カーディナリティ（値の種類の多さ）の高い列（また、それらがなるべく均一に分布している）
• where 句や結合に使う列

インデックスが使用される SQL を書く

アンチパターンは、

• インデックス列に演算
• インデックス列に SQL 関数を利用
• インデックス列に IS NULL を使う
• インデックス列に否定(<>)を利用
• OR を用いる
• 文字列のインデックス列について、前方一致の LIKE 以外を用いる
• 暗黙の型変更を用いる

解決法は、本で。

代理キー

Web 界隈では特によく見られますね。また、自然キーとの宗教戦争が行われることも。。。

自然キーと代理キーはどちらがいいのかという話にもなりがちですが、自然キーでは不自然にならざるを得ないことがあったり、

無意味に代理キーを追加してもディスクの肥やしになるだけだったり。それぞれ向き不向きを考えて使いなさいってことでしょうね。

代理キーの代わりに

一意キーはあるがそのコード体系が使い回しを前提としている場合。例えば市町村コードなど合併などで使いまわしたりする場合。自然キーでの解決法は、

1. タイムスタンプをキーとする
2. インターバルを列に追加して、開始日をキーとする

代理キーの自動採番

• データベースの機能で採番（シーケンスオブジェクト or ID 列）
• アプリケーション側で採番（採番テーブルなどを使って）

これは本書に載ってはないですが、

• UUID をアプリケーション側で（DB を介さず）生成する方法

も見たことがあります。この場合は衝突した場合の対処方法を用意しておく必要があります。きっと他にもたくさんありますね。

データベースでの採番は採番機能がデータベースにロックインされるという問題点があります。一方アプリケーションでの採番はいわゆる「車輪の再発明」になります。

以下、私見ですが、

ベンダーロックインをやたらと強調するのは個人的には好きではありません。もちろん、他への移行しやすさを担保することはシステムを長く運用していく上で重要です。

一方で、開発当初から別ベンダーに移る可能性を酷く心配しないといけない状況なら、そもそも現在の選択が正しいのか考え直すべきです。

「自分で作ったから安心」とはならないことの方が結果的には多いのではないでしょうか？

RDB での木構造

• 隣接リストモデル
• 入れ子集合モデル
• 入れ子区間モデル
• 経路列挙モデル

まとめ

データベーススペシャリスト試験は個人的な印象としては論理設計にかなりの比重がかかった試験に思いました。

物理設計やバックアップなど他に考えなければならないことは山ほどあります。

それらはデータベーススペシャリスト試験に受かったからと言って身についたかというと。。。

本書を足がかりに、さらに DB 設計について勉強していきたいです。