Hatena::ブログ(Diary)

Ynishi Bussiness Logs このページをアンテナに追加 RSSフィード

2013-04-29 SQLアンチパターン2

IDリクワイアド(とりあえずID)

|  IDリクワイアド(とりあえずID)を含むブックマーク  IDリクワイアド(とりあえずID)のブックマークコメント

すべてのテーブルの主キーをidという名前の属性(列)にし、ドメイン整数にしてしまうというもの。これを擬似キーともよび、本来のキーを自然なキーとよぶ。Railsなどのフレームワークなどが採用している設計指針に対する反論ともいうべきだろうか。本書では、そういうことを一律に強制することをアンチパターンとしている。

なぜアンチパターンなのか

冗長なキーが作成されてしまう

自然なキーとは別に主キーを作るのは冗長だという。

冗長なのはなぜダメなのだろうか?いくつか理由が考えられるが、

  1. スペースが無駄
  2. システムの複雑性が増える

といったところだろうか。スペースが無駄というのはその通り。ただし、速度に影響が出るほどの大きさではない。システムの複雑性が増えるというのは、ID属性で全て統一するのならそうともいえない。Railsでは「設定よりも規約」といっているように、一定のルールに従っていれば、コード量の記述が減り、システムの把握も簡単になる部分がある。ただ、私が問題だと思うのは別にある。詳しくは後に述べる。

重複行を許可してしまう

id列を主キーに設定するとUNIQUE制約がかかるため、自然なキーでればUNIQUE制約がそのまま適用されるが、ID列を主キーにしてしまうと自然なキーの一意性が担保されなくなるという指摘。IDを主キーとした場合は自然なキーをUNIQUE制約とする方法もあるが、その場合はid列はただの無駄だといっている。これは先の冗長であるということと同じ意味だろう。これだけで否定する価値が有るほど大きな問題とも思えない。

キーの意味がわかりにくくなる

idという属性名がすべてのテーブルに存在するので、異なるテーブルのIDの結果を出力するときに困るではないかという指摘。そこで、属性名をIDではなく「テーブル名+ID」という属性名にすればよいという。特に異論はないが、フレームワークを使っている人には関係がない。また、自然なキーを使うべきだという主張と関係はない。

USINGを使用する

上記の指摘と似ている。INNER JOIN句などで使用されるUSINGが使えないという指摘だが、これも「テーブル名+ID」という属性名にすれば解決する。

複合キーは使いにくい

自然なキーを使うと複合キーになり、自然なキーで結合するのが大変だという人がいるが、それを使いにくいというのは、現実を正確に表すことを拒否するようなものだという主張。数学者が2次元や3次元の座標を使うことを拒否し、物体が1次元に存在するかのように計算を行うようなものらしい。

しかし、この主張はなんだか怪しい。1次元のデータに変換しているだけなので、数学的には等価であり、むしろSQLの記述量を減らしているという点では優れている。それだけではなく、フレームワークを使う上ではとても扱いやすい。また、RDBから取得したオブジェクトプログラミングする場合はID属性が主キーになっているといろいろ共通化ができて便利である。

そもそも、これまで2つの主張がSQLの記述量に対する批判であるのに、今度は立場が逆転すると、記述は増えてもいいというのはどうだろう。単に、どういう場合に記述量を減らしたいか、ということのトレードオフにすぎないと思うのだが。

解決策について

わかりやすい列名

具体的には2つあげている。

一つ目は、IDではなく「テーブル名+ID」にしようという主張。

二つ目は、外部キーの列名も同じように定義し、主キー名が被らないようにしようという主張。

いずれも結合時のSQLの記述量を減らすためのTIPSともいえる。よって、SQLの記述量を減らすためであり、O/Rマッパーを使うときは別だ。

規約に縛られない

id列の強制というRails規約に縛られないようもできる。列名の明示的な指定は、実用的な主キー名になる、とのことらしいが、実用的とは何を意味しているのだろうか?もう少しきっちり説明すべきだと思うが、以下の様な事が考えられる。

  1. 自然なキーにしておけばUNIQUE制約が自動でついてくるので便利
  2. 自然なキーであれば外部キーにも自然なキーがくるので、場合によっては結合しなくてもそのまま使える

1は先に述べた。2については次項の最後に記述されていた。たとえば自然なキーであれば、住所録のテーブルに社員コードがくるが、IDであれば、社員IDという内部的な連番が来るので、別途結合が必要になる*1。ただ、SQLで全て記述するならともかく、Railsなどのフレームワーク規約に背くほどの内容ではない。ただし、本当の自然なキーにUNIQUE制約を別途設定することはおすすめしたい。

自然キーと複合キーの活用

IDのような擬似キーを使うという義務はないよ、という内容と、擬似キーは変更に強いよという主張。こういう記述があると、このアンチパターンの主張とは逆に、擬似キーのほうが断然いいようにみえる。つまり、自然キーは変更を受けやすいが、擬似キーはたんなる連番であるがゆえにほぼ変更されることがない。ということである。しかも、この本ではこの問題に対する唯一の解決策だといっているが、そうすると、とりあえず擬似キーを使っておくのが正しいと思えるのでは?

規約は、役立つと思える場合のみ従いましょう」、と最後に書いているが、この本の読者が、このアンチパターンのタイトルだけを読んで、このアンチパターン自体を規約としてしまうと、とてもひどいことになる。

擬似キーを推進する理由

擬似キーを推進する理由をもう少し考えてみる。IDリクワイアドの設計指針を推奨している別の本がある。

楽々ERDレッスン (CodeZine BOOKS)

楽々ERDレッスン (CodeZine BOOKS)

この本では「自然キーと複合キーの活用」で述べた「変更に強い」ことを推進の大きな根拠にしている。また、実際にありそうな問題を使って説明しているところが面白い。以下、少し取り上げてみる。

コードの洗い替え問題

通常のコードをキーにしていたが、次期から別のコード体系にしたいので洗替したいという場合、コードを完全に入れ替えてしまうと過去のトランザクションデータを参照できなくなる。しかし、もしIDをキーにしていれば、コードを書き換えてもIDは不変なので、問題ない。

私が前回述べた外部的なコードと内部的な実装を分けるべきという考え方に近い。

諸口問題

顧客マスタをわざわざ登録するまでもないような一時的な顧客を入力するために、コードを付けなくていいような顧客を追加できるようにする。継続的な顧客になった時点であらためてコードを設定する。ということができるというもの。これには少し私には疑問がある。*2

  1. 一時的な顧客が多いと顧客マスタが大きくなるにもかかわらず、ほとんどのデータは使用されない。このため、過去データを切り離してバックアップを取るといった措置がやや面倒。
  2. 継続的な顧客になった時点で改めてコードを設定するとあるが、どうやってコードのないデータから顧客を探すのだろうか?名前だと同名や紛らわしい会社と認識を誤ってしまわないか?
  3. 基本的に諸口の顧客は使い回しを想定していないため、同名のデータが大量にできてしまわないか?
計画系コードの扱い

計画系のコードの場合はコード自体にステータスが存在し、コードの変更履歴を持たなければいけなくなる。こういう場合に使えるという指摘。ステータスが明確であれば、ステータスごとに持てば良いという設計方針もあるが、特に関係なく履歴を持つというのであれば、最もよい設計だと思う。トランザクションデータも同様で、自然なキー+履歴Noなんてもつのであれば、IDのみの方がより単純でよい。

擬似キーの問題について

一方、私が擬似キーに対して問題を感じるのは、以下のとおりである。この本ではSQLアンチパターンなので、SQLに関するデメリットの指摘になるのは仕方がないかもしれないが、この問題はSQLなどの実装上のものというよりは純粋に設計上の問題が大きいと思う。この本はRDBにとって、より自然設計を誘導したいのだろうと思うので、これをアンチパターンとしたい気持ちはわからないでもない。

キーがわかりにくくなる

ひと目でテーブルの何がキーなのかがわからなくなるという点である。IDを主キーにしたERモデルをみてもぱっと見でシステム全体がどういうテーブルの構造になっているのかが分かりにくい。

自然なキーを意識しなくなる

先程は自然なキーをUNIQUEにすればよいとは述べたが、これは同時にしなくてもよいという意味でもある。つまり、自然なキーがなくてもよいので、該当テーブルの定義・性質がはっきりしなくなり、実装上の都合でテーブルが肥大化、あるいは分割してしまうことになりかねない。このため、後から自然なキーを設定したくてもできなくなったり、キーがどんどん追加されるといった問題が生じる。主キーというのは該当テーブルの性質を決定づける重要な要素である。変更が簡単にできるのはとてもいいことであるが、変更しすぎて一体何のテーブルだったのかわからなくなるのは問題である。

私の結論

単純に実装だけを考えれば、むしろリクワイアドIDのほうがいいような気がする。これは、Beautiful Codeの17章「もうひとつの間接参照」(Another level of indirection)という考え方に則っている。IDとはポインタであり、参照だと私は思うからである。

英語版のwikipediaには、

A famous aphorism of David Wheeler goes: "All problems in computer science can be solved by another level of indirection"

(拙訳)

David Weelereが言う有名な格言にはこうある「コンピュータサイエンスのすべての問題はもう一つの間接参照で解決できる」

とある。

では、変更に強いデータベース設計をするためにはすべてIDを設定しよう、なのだろうか?確かに、変更に強いテーブル設計になる。ただし、変更に強いというのが無条件にいいか?ということをいいたい。それはやはり、先に上げた冗長性・設計上のわかりにくさと引き換えに得ているのであり、無条件になんでもいいというわけではない。つまり、変更が起きそうなテーブルには主キーにIDを使った設計を入れ、そうでないところはそのままでいい。そうすると、設計担当者がIDを入れているということのメッセージが他の人に伝わる。

もちろん、すべての変更を予測することは難しい。ではそのときはどうするか?それは、設計者の裁量で決めるべきだ。置かれたシステムの状況・規模・使用する言語、それらによって全て違うだろう。それを一律何も考えずに規約でIDを主キーに縛るのはおかしい。そういう意味では本書と同じ結論とは言える。

ちなみに、Railsで主キーがIDでない設計は絶対にやるべきではないと思う。それを破ることによるメリットがあるとは思えない。

キーレスエントリ(外部キー嫌い)

|  キーレスエントリ(外部キー嫌い)を含むブックマーク  キーレスエントリ(外部キー嫌い)のブックマークコメント

「参照整合性制約を設定しない。」という設計指針に対するアンチパターン。比較的このような設計をしているところを多く見る。

そもそも参照整合性制約を設定しないということは、ERモデルも記述する必要がない。テーブル定義はすべてExcelで定義すればよい*3。リレーショナルモデルを採用する気はなく、RDBSQLが使える単に永続的なデバイスとして使おうという狙いである。テーブルを「COBOL的なファイル」とみなした設計とも言えるだろう。本書は出来る限りリレーショナルモデルからみて自然設計をへ誘導しようとしている以上、こういう設計は批判の対象となるのは明らかである。

なぜアンチパターンなのか

完璧なコードを前提としている

参照整合性制約を設定しないということは、ミスしないということを宣言することに近い。しかし、それはあまりにも無謀だ。

ミスを調べなければならない

バグを治すことよりも、データの整合性が壊れていることを検知し、その整合性を保つように変更することはとても大変な作業である。長大な日次バッチで整合性検証をする時間などないので、たいていは顧客の指摘で発見される。最悪の場合は、もう直せないということさえ考えられる。

私のミスではありません

アプリケーションではなく、メンテナンスアドホックSQLで簡単に整合性を壊すことができる。

解決策

外部キー制約を宣言する

ポカヨケの原則を適用し、不整合が起こるような変更を変更を加える前に阻止するために、参照整合性制約を設定しようとするもの。異論はありません。

複数変更テーブルの変更をサポートする

外部キーにカスケード更新を設定することで、親側のデータを変更した際に連鎖的に変更することが可能になるということ。

とても便利な仕組みなのだが、レスポンス的な問題が起きやすい。大量データを処理するときは、極端に処理が遅くなる可能性があり、注意して使用しなければならない。

オーバーヘッドにはなりません

多少のオーバーヘッドは起きるが無視していいレベルだという主張。そもそも、整合性確認の仕組みをアプリで実装するのであれば、結局一緒だろうということは確かに言える。

ただ、私は更新時のアプリ側の実装に差が出るとは思わない。アプリ側でも結局、参照整合性制約を前提にせずにチェックを行い更新処理を行う。つまり、参照整合性制約を利用したエラーチェックはしない。これは、例えば、整合性制約のチェックをアプリでやる際に、DBが返す参照整合性制約の違反エラーから取得をしない。これをやると結構大変である。エラーの内容をシステムエラーコードから判断しつつ、エラーとなった制約から、対象の列を取ってくるなどの仕組みが必要となるが、それをやるくらいなら、普通に書く。

また、参照整合性制約は移行など大量データを更新する場合はやはり大きな差が出る。そこは後述するが、一旦整合性制約を無効化し、後で有効化するのがよい。

参照整合性制約を嫌う理由

本書では以下の様な参照整合性制約を嫌う理由が述べられている。

データの更新が参照整合性制約と衝突してしまう

複数テーブルの変更が面倒だという主張である。面倒だからといって、整合性が壊れることよりも優れているという理由にはならないと思うが。むしろテーブルを更新(ロック)するする順序が決まるほうが、デッドロックが起こりにくいという素晴らしいメリットもある。テーブルAをロックしながらテーブルBをロックしようとするトランザクションとテーブルBをロックしながらテーブルAをロックしようとするとデッドロックが起こるのであり、順番が統一されていたほうが良い。

データベースの柔軟性が極めて高いので、サポートできない

このあとのアンチパターンで詳述したい。

データベースが外部キーのために作成するインデックスが、パフォーマンスに影響すると考えている

インデックスを作成すると更新系の処理が遅くなり、スペースも使うが、まあそんなことを気にできるパフォーマンスチューニングは相当レアな状況だろう。むしろ参照整合性制約自身のレスポンスの問題がある。先述の「オーバーヘッドにはなりません」をにも記述した。

外部キーをサポートしないデータベース製品を使っている

どうしようもない。アンチパターンでもつかっていい理由になっている。逆に言えば、参照整合性制約を使う気がないのなら、積極的にそういうデータベース製品を使うべきだ。

外部キーを宣言する構文を調べなければならない

調べたらいいではないか。

その他の参照整合性制約を嫌う理由

参照整合性制約を設定することへの反対意見には、その他にこのような主張がある。この主張では、以下の様な点が指摘されているので、順番に考えていく。

データを移行する順番が必要になって煩雑

外部キーがあるテーブルよりも先に主キー側のテーブルを移行しないとデータが入らないという問題。テーブルの以降の順番を考えないといけないというもの。

これを回避するのは極めて簡単で、参照整合性制約を一時的に無効にすれば良い。入れた後に、もう一度有効化する。不整合があれば有効化する際にエラーが出て、データの移行を止めてくれる。

この文書でよくわからないのが次のところ。

この処理性能の問題は、一時的に参照整合性制約を解除する(または、チェックを行わないモードを使用する)などの方法で回避できる。しかし、一時的とはいえ制約を解除すると、誤ったデータが混入される可能性がある。別の方法でデータのチェックを行うくらいなら、RDBMSの参照整合性制約機能を利用する意味はほとんどなくなってしまう。

制約は有効化する際に整合性をチェックされる。別の方法は使わなくてもよい。

参照整合性制約を設定していると遅い

参照整合性制約が設定されているデータを移行するととても遅くなるので使うべきではないという主張。これは移行時にかぎらず、通常時でも大量にデータを更新する場合は同じである。この場合、設定をしない選択をする会社が多いようだ。しかし、大量データを更新するようなバッチ処理の場合は、まず制約を無効化して、バッチ処理終了後に有効化すれば良い。あるいはOracleの場合はチェックを遅延させることもでき、コミット時にまとめてチェックするというのもよい。

親側にないデータを外部キーの属性に仮追加するということができない

マスタデータに存在しないコードを外部キーの属性に追加したいという問題。これは先程の擬似キーで解決するのが最も易しい解決方法だろう。

アプリケーションで確実にやればよい

すごい自信ですね。としかいえません。

私の結論

やはり基本的には参照整合性制約は設定すべきであると考える。ここで語られていない理由以外では、アプリ側が整合性が保たれているという条件で検索処理を記述できる点がある。不整合があるデータを考慮した検索をする必要がない。また、参照整合性制約だけでなく、NOT NULL制約や、UNIQUE制約、CHECK制約などは積極的に使用すべきである。例えば、年月を表現するのに日付型を用いているのであれば、CHECK制約で日がかならず1日で時間分秒はすべて0であることを保証していれば、アプリ側にいらぬ前提が不必要になり、負担がかなり緩和される。

ただし、設定しなくても良い場合もあると思う。

参照整合性制約が失われる可能性があるのはどういう場合だろうか?それは基本的には更新時である。不整合とは更新時異常が発生する場合におきる。よって、更新をしないテーブル、つまりアプリケーションが追加のみを行うテーブルには設定が不要といえる。削除は外部キーのみ存在し、自身が親になるテーブルがなければ、更新時異常は起きない。こういうテーブルは一般的にマスタでもトランザクションテーブルでもない。トランザクションデータを単に集計しただけのテーブルなど検索スピードを向上させるために使用する集計テーブルや、データの過去の変更履歴を管理する履歴テーブル等である。ただし、この場合でもひとつ問題が残っている。それは保守などによりアドホックSQLを実行されると、それを防げないという点である。これは権限管理や監査ログを行う以外に防ぐ手段がない。

制約を使用するということは属性のドメインを明確にするという目的があり、リレーショナルモデルとしてもとても重要な要素の一つである。このような制約を軽視している人はやはりCOBOLなどの影響が残っているのかもしれない。

なお、契約による設計DbC)のように、テスト中は制約を設定しておくが、速度低下を気にするあまり運用後は除くという方針の会社もあるらしい。それでも構わないが、通常のオンライン処理ではそこまで影響があるとは思わない。

また、整合性制約を設定していると、ERモデルの線が多すぎて見えなくなるのがいやだということもある。これには同意したい面もあるが、こういう設計は冗長な項目を持っている非正規化テーブルを多く抱えていることに現れやすいので、注意すべきである。また、先程述べたように追加のみのテーブルには参照整合性制約は不要であると考える。

*1:もちろん、社員ID=社員コードとするという設計があるが、キーに整数の単なる連番を使うというIDリクワイアドの設計指針の意味合いからは異なるので、ここでは違うものとする。また、いわゆる擬似キーを生成する機能はでないので、抜け番がおきやすいが、人間が扱うコードはたいてい抜番を許さないはず。

*2:でも、この章のこの部分は私はとても納得してしまった。"データ中心アプローチ標榜する人たちの殆どが「正しいコード体系を整備しなければならない」と声高に叫びます。しかし、ユーザーが欲しているのは論理的に正しいコード体系ではなく、「実務において便利なコード体系」なのです。"

*3ERモデルをみると四角形のエンティティだけがなんの関係もなく作られていく。そんな図になんの意味があるのだろう

トラックバック - http://d.hatena.ne.jp/yuuntim/20130429