データベースにおける、論理設計、物理設計、SQL設計、DBを用いたアプリケーション設計における、業務に起こりがちなシステム案件を例に、エンジニアが陥ってしまう本来のデータベース機能を活かせなくしてしまうアンチパターンを紹介してくれている。
開発現場での過去からの技術推移による積もり積もった負債による問題など逃れられず迫り来る重いものや、安易な先入観によって一気に深い奈落へと落とされる袋小路への罠など、システム開発における生きた現場感ある事例から解説を導入してくれているので非常に読みやすい。
データベースを利用した開発経験者、およびこれから開発を経験する人たちを対象に価値が出てくる著作だと感じた。
以下、内容メモ抜粋。
------------------------
１部 データベース論理設計のアンチパターン
■１章：ジェイウォーク：信号無視
多対多の関連を表現する交差テーブルの作成を避けるために、
属性内でカンマ区切りのリストを使うパターン
デメリット：
カンマ区切りのリストの値を使ってテーブル結合するのが手間
インデックスを使う機会も失われる
COUNT, SUM, AVGで細工が必要
リストへの追記方法の細工が必要
リストの区切り文字を含む文字列の挿入に注意が必要
varchar(30)などで定義した場合、リストが文字列数以内に収まるための制御が必要
リストのソート管理が複雑
解決策：
・交差テーブルを作成する。
多対多 → １対多：多対１ とするためのクッションのためのテーブル
インデックスを活用できるようにある。
挿入されるIDなどの妥当性を、参照生合性などで保証させることができる。
＞存在しているIDのみ登録させるようにする、など。
リストでないので、区切り文字を気にする必要なし
リストの長さ制限がある場合は、COUNT関数などで制限するようアプリ側で実装する
■２章：ナイーブツリー：素朴な木
親コメント、子コメントの対応ができるメッセージフォーラム掲示板の作成
親コメントIDをもたせたコメントテーブル
コメントの取得
＞親子関係の階層が深いと、単一のSQLで取得するのが難しくなる。
＞階層数だけテーブルを自己結合でjoinする必要あり
削除は、子孫特定に複数クエリが必要
サブツリー全体削除は、外部キーの整合性制約を満たすために、最下層から順番に子孫を削除する必要あり。
アンチパターンの見つけ方。
・ツリーの深さを考慮しなければならない
・解決策を講じても他の作業が犠牲になり難しくなる
・定期的に生合成を保つスクリプトでメンテが必要
解決策；代替ツリーモデル
・経路列挙モデル
属性pathに、親子関係階層を記載 1/, 1/2/, 1/4/6/7/ ...など
参照生合成を保証できず、冗長な情報を格納するという脆弱面がある。
・入れ子集合モデル
各ノードに nsleft, nsrightのポインタを持たす
ツリー構造の深さ優先順にノードをなぞってnsleft, nsrightに順に値を割り振る
参照生合成を保証できない。ツリーの変更より、検索のみの場合に有利
・閉包テーブルモデル
ツリーは、自分と自分以下のサブノード全てのリンクを持つ構造
＞ルートは、全てのノード、サブノードは、サブノード以下の全てのノードの情報を持つ
コメントと、ツリー構造のテーブルを分けている。階層の計算が楽な分、多くのスペースが必要（それぞれが、自分のノード以下の全てのリンクを保有するため）
＞コメントテーブルが別れているので、階層構造を削除してもコメントを定期技削除する必要がある。
■３章：IDリクワイアド（とりあえずID)
とりあえず主キーとしてID列を用意（サロゲートキーのような扱い）してしまうアンチパターン。
サロゲートキーを用意したが、本来は、bugIDとproductIDのそれぞれ多対多の組み合わせを表現する必要があるテーブルであった。そのままではbugIDとproductIDの値が重複する行を許してしまう。
そこで、bugIDとproductIDの複合ユニーク設定をすることにする。しかし、そうなっては主キーのID列の存在意義とはなんなのか。bugIDとproductIDの複合主キーでいいのではないかという話になる。
解決策：
・主キーはわかりやすい属性名にする。
・とりあえずＩＤではなく、自然キーと複合キーを活用する。
■４章：キーレスエントリ（外部キー嫌い）
ストレージエンジン自体がサポートしていない（例：MySQL:MyISAM 外部キー制約をサポートしていないストレージエンジン）場合もあるが、エンジニア自体が外部キーの設計を横着してしまうアンチパターン。
外部キー制約を使えないために、ユーザが無効なデータを入力した時に即座にエラーを返せない。品質管理用スクリプトという本来不要な処理も必要になってしまった。
解決策：外部キー制約を宣言する
メリット：
参照生合成：外部キー：親テーブルの主キーやユニークキー列に存在する値のみ許可する事ができる
参照制約にはカスケード更新削除処理が使える。アプリケーションコードでは真似できない。
foreign key(status) references BugStatus(status)
on update ascade
on delete set defaut
外部キー制約でオーバヘッドは発生しない。むしろメリットがある。
・挿入、更新、削除のさいに、チェック用SQL文が不要
・複数テーブル変更を防ぐためのテーブルロックが不要
・データ品質管理用スクリプトを独自で作って管理する必要なし
■５章：EAV(エンディティ・アトリビュート・バリュー)
日付ごとのカウント：日付でgroup化しcount(*)
しかし、日付が行によって異なる列に格納されているケースの問題
エンティティ・アトリビュート・バリュー（オープンスキーマ、スキーマレス、名前/値ペア）と呼ばれる。キー・バリュー方式とも？
・列数を減らせる
・新たな属性をサポートするのに列数を増やす必要なし
create table Issues(
issue_id SERIAL PRIMARY KEY
);
create table IssueAttributes(
issue_id BEIGINT UNSIGNED NUT NULL,
att_name VARCHAR(100) NOT NULL,
attr_value VARCHAR(!00),
PRIMARY KEY(issue_id, attr_name),
FOREING KEY(issue_id) REFERENCES issue(issue_id)
);
この場合、列値を取得するのが元来のSQLと比較して冗長になる。
select issue_id, data_reported from issues;
->
select issue_id, attr_values as date_reported
from IssueAttrivutes
where attr_name = 'date_reported';
・一つのissueはissue_id,attr_nameにより複数行に別れ、行ごとに属性、属性値のペアが格納されているので、特定属性をNULLにできない。
・存在が必須の属性も、行で格納されているので制約で設定することができない。
・IssueAttributeの行ごとに、属性、属性値を管理しているため、列のSQLデータ型を選択
できない。
＞個別のデータ型の列を追加すると、さらにクエリは複雑化してしまう。
・参照制約も使うことができない。＞attr_value列が、全て参照制約の条件に合わないといけなくなる。
・属性名に一貫性がないケースが起こりうる、その時のSQLも複雑になる。
・同じissue_idの情報を一行の情報として取得したい場合、joinが属性数必要。
アンチパターンの見つけ方・
・メタデータの変更なしで拡張可能。実行時に新しい属性を定義できる
・導入時の初期設定が不要で裏でDBを使っているパッケージの多くはカスタマイズしやすいようにEAV設計を採用している
＞非リレーショナルな管理なら、それらに特化したDBを使うべき
・BerkleyDB
・Cassandra
・CorchDB
・Hadoop/HBase
・MongoDB
・Redis
・Tokyo Cabinet
解決策：サブタイプのモデリングを行う
・シングレテーブル継承
関連するすべてのサブタイプを一つのテーブルに格納
・具象テーブル継承
サブタイプごとにテーブルを作成
・クラステーブル継承
継承を活用して、スーパータイプ、サブタイプを作成
・半構造化データ
サブタイプが多い場合や、頻繁に新しい属性を追加したい場合
LOB列を追加し、XML,JSONなどで属性名、属性値を共に格納する。
どうしても、EAVを使わざるを得ない場合。
＞行の集合として取得する。
同じissue_idの複数行を、ループで取得し、オブジェクトのプロパティとして設定するPHPの例＞p101
「メタデータは、メタデータのために用いましょう」
■６章：ポリモーフィック関連
複数の親テーブル参照をする問題
行ごとに異なるテーブルとjoinして結合することはできない。
解決策
・参照を逆にする
交差テーブルを作成
複数の親テーブルと、対象テーブルの多対多を表現するテーブルを、複数必要な親テーブルの数だけ作り関連づける。
・道を合流させる
unionを用いて結合する：列の数とデータ型が同じである場合のみ
・共通の親テーブルを作成
■７章：マルチカラムアトリビュート（複数列属性）
複数の値を持つ属性を格納する：１章のJAYWALKと同じテーマの問題
バグの特性情報として、複数タグをもたす＞３つの属性で管理
・値の検索でタグを検索する場合は、３列すべて取得が必要になる。
一つのタグを検索するごとに、３つのorでの比較、またはin句の式が必要
・3つのタグで同じ値を格納したくない
しかしマルチカラムアトリビュート・アンチパターンでは、防ぐことができない。
＞普通にinsert文で挿入できてしまう
アンチパターンを使用しても良い例：
3つのタグの列が、列ごとにタグを入力するロール（ユーザ、エンジニア、管理者）など目的が異なる場合。
解決策：従属テーブルを作成
バグIDとタグの多対多を表現できるテーブル
＞空いているタグの列を探すことも不要で、削除、挿入が可能
■8章：メタデータトリブル（トリブル：大増殖）
年度ごとのデータ格納用列が、毎年追加される
＞しかも、一部の顧客でしかデータが埋まらない、ほかはNULL
行数が多いより少ないテーブルのクエリ実行の方が速いという考えが、誤った考えを導く危険性。
・行数の多いテーブルを複数のテーブルに分割
＞年度ごとにテーブを分ける
新たなテーブル作成管理が増える
日付などが異なった年度に入らないようにcheck制約をテーブルごとに設定を変えて作成
＞日付などが、去年の情報の誤ったレコードを発見。しかし、行の更新だけだとcheck制約違反になってしまう。
・列を複数列に分割
分割されたテーブルだと、複数テーブル間の主キー、ユニーク列の一意性の保証ができ>ない。
主キー値を作るためだけにテーブルを新たに一つ定義が必要
複数テーブルをまたいだクエリにunionを使用
＞メタデータ列の追加がある場合は、全テーブルで列を同期が必要
参照整合性の管理
年度ごとにテーブルが分割されているため、従属テーブルで外部キー定義ができない
＞一つしか定義できないため
解決策：
・パーティショニングと正規化
水平パーティショニング（シャーディング）をつかう
行で分割。
行を分割するルールを決めて、物理的に分割。
物理分割されるが、一つのテーブルを扱うようにSQLステートメントを実行できる。
・垂直パーティショニング：列で分割
・従属テーブルの導入
マルチカラムアトリビュートの解決法と同じ
------------------------
２部 データベース物理設計のアンチパターン
■９章：ラインディングエラー（丸め誤差）
float型：実数を２進数形式でエンコードする
浮動小数点の特徴を理解する必要あり
＞１０進数で記述できる全てを２進数で格納できるわけでない＞丸め誤差
無限小数となってしまうものは、有限小数による近似値となる
解決策：NUMERICデータ型を使用
NUMERIC, DECIMAL
■１０章：サーティワンフレーバー
列値に規定の選択肢を複数指定する。
＞check制約 in句でチェックするようにする
列のデータ型は ENUMが列挙で使用できる。
選択肢が増えるたびに、check制約、ドメイン、ユーザー定義型を扱うクエリは複雑化する
。プログラマはアプリコード側にも値のリストを定義してメンテナンスできるように設計したがる。
＞双方の同期がずれたら大きな不具合になる。
選択肢の追加による構造変化には、ALTER TABLE句で既存の選択肢の値も列挙しなければな
らない。
＞変更の際、テーブルへのアクセスをブロックし、テーブル内容をダンプし、テーブル再定義して、保存したデータを読み込まなければならない場合もある。
選択肢が廃止されたり、他に統合されたり、分割された場合、選択済みの値を参照しているレコードはどうすればいいのか。
解決策：限定する値をデータで指定する。
参照テーブルを作成し、許可する選択肢の値を一行に一つずつ格納する。
元のテーブルに外部キー制約を制限し、この参照テーブルを参照させる。
＞参照テーブルの値の更新、挿入で、選択肢の編集が可能になる。
■11章：ファントムファイル（幻のファイル）
DBで画像ファイルパスを格納して、アプリで表示するのに使用していた。
＞DBが物理クラッシュ
＞画像ファイルの格納場所はDBサーバの/var以下
＞DBのバックアップはあるが、/var以下は一時ファイルなのでバックアップはなし。
＞復旧に大量の時間を浪費する事になる。
画像の格納方法
・blob型に入れる
・ファイルパスで管理
DBでレコードを削除しても、画像ファイル自体も消さない限り残る
ファイルを削除すると、すぐにファイルにアクセスできなくなる。
ファイル内容を変更すると、トランザクションがコミットされる前に、他のクライアントはその変更されたファイルを目にすることになる
＞ロールバックの問題
ロールバックでレコードが回復されても、ファイルはロールバックの対象ではない。
SQLアクセス権は、外部ファイルには適用できない
SQLはファイルが存在することを検証できない。
解決策：blobで画像を管理する
データベースごとに以下のBLOBをサポート
・MySQL MEDIUMBLOB 16MB
・Oravle BLOB v9.2 4GB, v10.1 128TB
■１２章：インデックスショットガン（闇雲インデックス）
・インデックスを全く定義しないか、少ししかインデックスを定義しない
・インデックスを多く定義しすぎるか、役に立たないインデックスを定義
・インデックスを活用しないクエリを実行
インデックスを定義すると、行の更新、挿入で、インデックスの構造も更新するためオーバヘッドが生まれる
＞そのオーバヘッドをなくすためインデックスを排除する人がいる。
複合インデックスの勘違い
インデックス a,b
の場合、b,aでソートすると、インデックスを活用できない
解決策：MENTORの原則に基づいて効果的なインデックス管理を行う
M:Measure 測定 SQLの実行時間を測定
E:Espkain 解析 QEPの分析結果レポートからボトルネックを調査
N:Nominae 指名 インデックスを必要な箇所を特定
T:Test テスト クエリプロファイリングで確認
O:Optimize 最適化 キャッシュメモリのチューニング
R:Rebuild 再構築 定期メンテナンス：断片化対応など
-----------------------------------------------
３部：クエリのアンチパターン
■１３章：フィア・オブ・ジ・アンノウン（恐怖のunknown）
NULLの表現
・無：null
・不明：unknown
・適用不能：inapplicable
アンチパターン：NULLを一般値として使う、または一般値をNULLとして使う
式でNULLを扱う
hours列がNULLの場合> select hours + 10 from bugsのselect 結果はNULL
不明(unknown)より１０大きな値を持つ数は、ひき続き結果は不明(unknown)
検索において、nullは除外される
〜以外の検索においても、nullは対象外
特定したい場合ｈ、IS NULL, IS NOT NULL で指定する
prepared statement でnullは使えない
れい：select * from bugs where assinged_to = ?(ここにnullを指定);
sum,avg関数なのでnullが入ると式が複雑になるので、
nullをなくし、値を-1などで表現
＞しかし、-1などもsum,avg関数を混乱させてしまう
外部キー設定で、nullを-1表現した場合は、
参照がない(null)を表す-1の行を参照先テーブルで作らなければならない。
解決策：nullを一意的な値として使う
データ型を問わず、欠けている値にはnullを用いる
■14章：アンビギュアスグループ（曖昧なグループ）
GROUP BYと、集計関数による列表示のSQL文中に、集計化してないユニークのIDが格納されている列をSELECT句で指定してしまう。
＞同一グループの集計範囲には複数のユニークＩＤが含まれるため、一つに絞れない。
＞クエリがエラーになるか、不正確なデータが表示されてしまう
アンチパターンの見つけ方：
単一値の原則に反するクエリを作成すると、エラーメッセージが表示される
解決策：
・曖昧でない列を使用する。
関数従属性のある列のみにクエリを実行する。
・相関サブクエリを使用
サブクエリの結果がない場合は、外部クエリの結果が適用される
・導出テーブルを使用
・外部結合を使用する：ない項目にはNULLが入る
・他の列に対しても、集約関数を使用
■１５章：ランダムセレクション
ランダム選択するクエリが、選択行数が増えることによる性能劣化
従来のソート：
インデックスを使用したランダムソート
列の値などでソートする。
非決定性をもつRAND関数などのソート：
select * from bugs order by rand() limit 1;
selsect * from bugs order by date_reported
インデックスからメリットを得られない。
テーブルスキャンによるソートとなる。
>小さなデータセットなら用いても良い
解決策；特定の順番に依存しない
・１から主キーの最大値までの値をランダムに選択する
・全てのキー値のリストをうけとり、ランダムに一つ選択する
・オフセットを用いた乱数（データセットの行数をカウントし、０と行数までの乱数）で選択する。
■１６章：プアマンズ・サーチエンジン（貧者のサーチエンジン）
パターンマッチ述語を使用してしまう
%LIKE%
後方一致はインデックスを活用できない。
解決策：適切なツールを使用する
全文検索エンジンなどを利用すること
MySQL,Oracleなどのテキストインデックス
■17章：スパゲッティクエリ
複数抽出項目に対して、SQLクエリを一つにまとめる横着をしてしまう
複雑な問題をワンステップで解決しようとすてしまう。
>クエリにデカルト積が発生
デバッグなどの難易度が高くなる
解決策：分割統治を行う。
ワンステップずつ着実にＳＱＬ文を作成する。
■１８章；インプリシットカラム（暗黙の列）
属性名が競合したりしてSQLクエリに影響を及ぼす、予期せぬテーブル間のカラムの問題
ワイルドカードの多用、暗黙列の多用
select文などで、列指定をワイルドカードで記述し列を省略する問題
＞他の修正で、列が追加されたり、削除されたらどうなるか？
インサート時も、列名を省略する＞暗黙列の問題
＞インサート時には、全ての列を順番に値を指定しなければならない
解決策：
・列名を明示的に指定する。
必要な列だけを指定する。
-----------------------------------------
４部：アプリケーション開発のアンチパターン
■１９章：リーダブルパスワード（読み取り可能パスワード）
パスワードを平文で格納してしまう。
>セキュリティリスク
IDとパスワードの比較
2つの条件をまとめない
＞アカウントが問題なのか、パスワードが問題なのか問題を区別できない。
アカウント名のブルートフォース攻撃なのか同一アカウントのパスワードのブルートフォース攻撃なのか判断できない
パスワード再取得機能で、パスワードを平文で送信するのは深刻なセキュリティリスク
＞暗号化、登録メールにリセットと新パスワード設定のページへの誘導すべき
解決策：
・ソルトをつけてパスワードでハッシュする
＞網羅的なハッシュマップによる元データを紐付ける攻撃を阻止できるソルト
・SQLクエリでパスワードを平文で扱わない
■２０章：SQLインジェクション
動的SQLの代入部分に脆弱が混入する
;(セミコロン）で区切って悪意のあるSQLの注入
select * from bugs where bug_id = [1234; delte from bugs]
'(クォート）などで、SQLを分割して悪意あるSQLの注入
$sql = "select * from projects where project_name = '$project_name'";
※$project_nameに'を含んで好きなＳＱＬを注入できてしまう。
同じく動的SQL代入時に末尾にor〜などの付与
~where account_id = [123 OR TRUE]
対処法：
・値のエスケープ
後続文字のエスケープ：バックスラッシュ
・プリペアドステートメントの利用
クエリの前処理（プリペア）時にパラメータのプレースホルダーを文字列内に残す
そして、準備したクエリを実行する前にクエリパラメータとして値を渡す
＞最も強力な防御手段
値のリストを一つのパラメータにできない
テーブル識別子もパラメータとして扱えない。（テーブルを指定できない）
列名もパラメータとして使用できない
SQL予約語もパラメータにならない
・ストアドプロシージャの活用
・データアクセスフレームワーク(ORMなど)の活用
解決策：
・入力のフィルタリング
・動的値のパラメータ化＞プリペアドステートメント
・ユーザの入力をコードから隔離
■２１章：シュードキー・ニートフリーク（擬似キー潔癖症）
番号に欠番がある場合、欠番を埋めようとしておこるアンチパターン
外部キー属性を持っている場合は、ON UPDATE CASCADEを設定していれば連鎖的に変更され
るが、ない場合は手動で調整が必要＞サービス中断も視野に入れる必要あり
欠番３を埋める作業中に４から移行していたら、他作業で行追加されたさいには５移行の連番が入るため、３に移行後は、４が欠番となる・・・
違反などで取り消されたID番号を謝って再利用してしまう問題＞業務支障が起こる
解決策：擬似キーの欠番は埋めない
■２２章：シー・ノー・エビル（臭いものに蓋）
データベースの戻り値を無視してしまうアプリケーションコードの問題
アプリケーションに点在するSQLしか読まない問題
＞例：コードの分割記述されているSQL部を連結するとスペースが埋まってしまっている
解決策：エラーから回復する処理にする
try～catch文
API戻り値の検証判定処理の追加
■23章：ディプロマティック・イミュニティ(外交特権)
通常のシステム開発における、ソースコード管理、設計ドキュメント管理、テスト確認コードやドキュメントにおけるベストプラクティスから、SQLの管理を除外してしまう。
＞データベースを特別扱いしてしまう傾向がある問題
解決策：包括的に品質問題に取り組む
・文書化：ER図、テーブル設計、トリガ、ストアドプロシージャ、
・バージョン管理：DDLによる定義スクリプト、トリガ、プロスージャ、初期投入データ、
ER図やドキュメントのバージョン管理
・テスティング：自動化テストユニットコード
テーブル定義、制約、トリガ、ストアドプロシージャ、初期投入データ、クエリ、ORM
■２４章：マジックビーンズ（魔法の豆）
MVCアーキテクチャにおけるM(モデル)が、アクティブレコードそのものであるパターン
＞アクティブレコード：オブジェクトのフィールドとテーブル列のマッピングをサポートするパターンのこと
・アクティブレコードはモデルをデータベーススキーマに強く依存する
１６テーブル＞１６のモデルが必要
テーブルの修正＞モデルの修正必要＞モデルを呼び出すコントローラも修正必要
・ドメインモデル貧血症
多くのコントローラから、必要となる全てのモデルに網の目のように関連が生まれてし>まう
＞テスト確認の複雑さ、困難さが上がる
解決策：モデルがアクティブレコードを持つようにする
＞model, daoの分離
■２５章；砂の城
想定不足
・サービスの成長速度における想定性能の見積もり
・障害時の対応、ポリシーの策定
解決策：
・ベンチマークで事前性能測定
・テスト、検証環境の構築
・例外処理によるトレースの整備
・バックアップ
・高可用性設計
・ディザスタリカバリ
・運用ポリシ策定
----------------------------
５部：付録
■付録A:正規化のルール
ORDER BYがないと、予期せぬ順番を返す
列指定がないと、よきせぬ列順を返す
正規化すれば性能が上がるというわけではない
＞データ不整合、冗長の排除、整合性制約の整備
第１〜６正規形（ボイスコッド、ドメインキー正規形含む）
解説
以上。
無料のKindleアプリをダウンロードして、スマートフォン、タブレット、またはコンピューターで今すぐKindle本を読むことができます。Kindleデバイスは必要ありません。
ウェブ版Kindleなら、お使いのブラウザですぐにお読みいただけます。
携帯電話のカメラを使用する - 以下のコードをスキャンし、Kindleアプリをダウンロードしてください。
よく一緒に購入されている商品
+
+
総額:
当社の価格を見るには、これら商品をカートに追加してください。
ポイントの合計:
pt
一緒に購入する商品を選択してください。
この商品を見た後に買っているのは？
ページ 1 以下のうち 1 最初から観るページ 1 以下のうち 1
著者について
著者をフォローして、新作のアップデートや改善されたおすすめを入手してください。
