厂蚕尝アンチパターン読书会 4章 キーレスエンエントリ（外部キー嫌い）
?
4 likes?11,157 views
2013/6/13(木) SQLアンチパターン読書会のアジェンダ資料です。
厂蚕尝アンチパターン読书会 4章 キーレスエンエントリ（外部キー嫌い）
- 2. 2 4.1 目的： DBのアーキテクチャを単純化する 外部キーを使用しない主な理由 1. データの更新が、参照整合性制約と衝突する。 2.DB設計の柔軟性が極めて高いので、参照整合性制約 をサポートできない 3. データベースが外部キーのために作成するインデックス が、パフォーマンスに影響すると考えている。 4. 外部キーをサポートしないデータベース製品を使ってい る 5. 外部キーを宣言する構文を調べなくてはならない。
- 3. 3 4.2 アンチパターン：外部キー制約を使用しない 外部キー制約を使用しないと ? データベース設計がシンプルになる．．．？ ? 柔軟性が高まる．．．？ ? 実行速度が早くなる．．．？ 代償（トレードオフ） ? 開発者が、参照整合性を保証するためのコードを書く 責任が生じる。
- 4. 4 4.2.1 完璧なコードを前提にしている (1/2) ● 参照整合性を保証するための解決策 ? データの関連付けを常に維持するためのコードを書く。 ? 変更を適用する前に追加のSELECTクエリを実行し、 変更によって参照が壊れないことを確認する。 ? 例１： 行を挿入する前に、親の行が存在することを確認する。 ? 例２： 行を削除する前に、子の行が存在しないことを確認する。
- 5. 5 4.2.1 完璧なコードを前提にしている (2/2) ● この解決策の欠点 ? 確認クエリを実行した直後に、別の場所で誰かにDBを 更新された場合に不具合が生じる可能性が出る。 ? 「確認＋更新」の間、テーブルロックをかければ上記の 問題は解決するが、性能問題など別の問題に直面す る。
- 6. 6 4.2.2 ミスを調べなければならない ● 間違った解決策 ? 壊れた参照をチェックするスクリプトを書く ● なぜダメなのか ? すべての参照に対してスクリプトが必要となる ? 数百ものチェックを毎日、何回も実行しなくてはならない ? 壊れた参照を検出したとしても、修正できるとは限らな い
- 7. 7 4.2.3 「私のミスではありません！」 ● データベースに関するすべてのコードが完璧であ ることは、まずありえない。 ? すべての更新ロジックに対し、参照整合性を担保するた めの対処を施したか確認するのは、簡単ではない。 （複数のアプリが同一のDBにアクセスすることもある） ? あるユーザが、SQL実行ツールや自ら書いたスクリプト によって直接DBを変更し、参照を壊してしまう可能性が ある。 DBDBにアクセスするすべてのアプリとスクリプトがにアクセスするすべてのアプリとスクリプトが 正しく変更を行ったかどうかは、信頼できない！正しく変更を行ったかどうかは、信頼できない！
- 8. 8 4.2.4 「キャッチ=22」なUPDATE ● 子の行が依存する列をUPDATEする際のジレンマ ? 親の行を更新するまで、子の行の更新はできない ? 親の更新も、参照する子の値を更新する前に実行でき ない ● 親と子を両方同時に変更する必要があるが、２つ の異なる更新処理を同時に実行することは不可能
- 9. 9 4.3 アンチパターンの見つけ方 次のような言葉を耳にしたらアンチパターンの兆候 があることを示している。 ? あるテーブルには存在するけど、もう１つのテーブルに は存在しない値を調べるクエリは、どう書けばいい？ ? あるテーブルの値が、別のテーブルへの挿入に使われ ていることを簡単にチェックする方法はある？ ? 外部キー？データベースの実行速度を遅くするから、 使ってはならないと教わったのだけど。
- 10. 10 4.4 アンチパターンを用いても良い場合 ● 外部キーをサポートしていないデータベース製品を 使わざるを得ない場合 ? MySQLのMyISAMストレージエンジン ? バージョン3.6.19未満のSQLite ● 外部キーを用いた関連付けを行えない、極端に柔 軟なデータベース設計を扱わなければならない場 合
- 11. 11 4.5 解決策：外部キー制約を宣言する 参照整合性の強制の利点 ? データの不整合を検出してから修正するのではなく、 DBへの登録時点でミスを阻止できる。 ? 既存のコードも、その場限りのクエリも、同じ外部キー に従うようになる。 ? うっかり見逃していたコードが、この制約の強制を逃れ ることがなくなる。 ? 不要なコードを書く手間を省く。 ? DBを変更した場合でも、すべてのコードが同じように 適切に動作することを保証する。 ? コードの作成だけでなく、修正や保守の時間も大幅に 削減してくれる。
- 12. 12 4.5.1 複数テーブルの変更をサポートする カスケード更新 CREATE TABLE Bugs ( -- 他の列. . . reported_by BIGINT UNSIGNED NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'NEW', FOREIGN KEY (reported_by) REFERENCES Accounts(account_id) ON UPDATE CASCADEON UPDATE CASCADE ON DELETE RESTRICT,ON DELETE RESTRICT, FOREIGN KEY (status) REFERENCES BugStatus(status) ON UPDATE CASCADE ON DELETE SET DEFAULTON DELETE SET DEFAULT);
- 16. 16 データベースリファクタリング：第８章 遅延制約チェック 制約の妥当性のチェックは、トランザクション終了時まで 遅延できる。 – 遅延制約： 制約が満たされているかどうかのチェックがコミット時にし か実行されない。遅延制約違反があると、コミット時にそ のトランザクションは取り消される。 – 即時制約： チェックはそれぞれの文が終わった時点で実行される。 制約違反があると、その文は即時にロールバックされる。 Oracle 11g： http://otndnld.oracle.co.jp/document/products/oracle11g/1 11/doc_dvd/server.111/E05765-03/data_int.htm#66501