とあるが、主キー制約を複合キーからID列に移動しても「対象レコードを正確に指示する」ことはできるので、これは比喩が噛み合っていない。
そもそも開発者が複合キーに拒否反応を示す理由は、それをアプリケーションで扱うのが面倒くさいから＝余計な工数が発生するからでしょう。
実際に工数がかさんでいるのなら、拒否反応を無視してよいわけがない。

■結局、アンチパターンの追認になっていないか

「自然キーの一意性・不変性があてにならない場合、サロゲートキーを導入してよい」とあるが、「IDリクワイアド」アンチパターンを積極的に使う開発者は、もともと自然キーの一意性・不変性を信用していないのだから(そうですよね？)、このガイドは「IDリクワイアドは常時使用可能」と解釈されるのではないか。

■サロゲーキーを使う場合、過去の事実をどうやって復元するのか

サロゲートキーを外部キーにして、自然キーを他のテーブルに転記しない場合、過去の事実をどうやって復元するかが問題になる。
例えば、商品マスタの自然キーである[商品コード]を受注明細に記載せず、代わりにサロゲートキーである[商品ID]を載せた場合、受注時点の商品コードを復元できる保証がなくなる。
商品コードの不変性があてにならないと考えて商品IDを導入したのだから、受注明細と商品マスタを結合して商品コードを取得したのでは、前提とやってることが矛盾してしまうからな。
これについては、トランザクションに自然キーも転記する(ただし外部キー制約は付けない)か、商品マスタを履歴化(各行に適用開始日・終了日を付ける)するなどの処置が必要だと思うが、私はサロゲートキーを使わないので、どうするのが常識なのかわからない。
著者は「こういう場合はサロゲートキーを使っていい」とは書くが、上記の問題を含めて、サロゲートキー導入時の考慮事項をあまり検討してくれない。

■IDリクワイアドのその他の問題点

個人的には、IDリクワイアドについては「DBMS実装の事情を考えずにID列を必ず主キーにする」点が問題だと思う。
主キーなんてあってもなくてもいいものなのだから、わざわざDBMSが困るような形で付与すべきでない。
例えば

主キーを勝手にclustered indexにしてしまうDBMS
主キーを構成する列でなくてはパーティショニングのキーにできないDBMS

などを使うのであれば、自然キーを主キーにしておかないと、パフォーマンス問題に対する打ち手を失うことになるだろう*1。

主キーはあってもなくてもよいと書いたが、これは本書の主張ではない。著者は逆に、

すべてのテーブルには主キーが必要である
外部キー参照されるものは主キーでなくてはならない

と考えている模様。

すべてのテーブルには、重複行を防ぎ、個々の行を識別するための主キー制約が必要です。

主キーは外部キーから参照されることで、テーブルの関連付けを行います。

主キー制約が重要になるのは、次のような必要があるときです。
・行の重複を避けたい
・クエリで個別の行を参照したい
・外部キー参照をサポートしたい

大した話ではないが、ここに挙げられているのは「主キーが必要な理由」になっていない(ぜんぶUNIQUEインデックスで実現できるから)とは言わせていただきたい。
細けえなぁと思われるかもしれないが、前述の通り主キーにはDBMSによって変な制約が付くことがあるので、ある程度注意して使わなくてはならない。
主キー重視派の著者は「このテーブルには主キーは必要ない」という開発者の発言に対して「間違っている」「勘違いしている」と書くが、その人「UNIQUEインデックスがあるから主キーは要らない」と言ってたんじゃないの、とちょっと思った。

*1:前者は自然キーでの効果的なindex range scanができない。後者は例えば「日付でパーティショニング」といったことができない

2012-11-20

年間の生活費を60万円減らす方法

life

去年3LDKから1DKに引っ越したら家賃が5万円安くなった。
前の家では日当たりの悪い2部屋は物置、和室は洗濯物を干す場所にして、居間に万年床を敷き、夫婦二人でほとんど布団の上だけで生活していた。
テーブルには読みさしの本や仕事の資料が山積し、ソファは服やバッグの置き場所と化して座ることもできず、布団の上にちゃぶ台を置いて飯を食っていたら、ある時気付いたのだ。
俺たち、半分ぐらいの広さの家に住めるんじゃないのかっ...！
ちょうど向かいに新築のアパートが建ったのでさっそく内見して寸法を測り、持ち込めそうにないものを狂ったように捨てたり売ったりして引っ越した。
1DKに相変わらず万年床を敷いてその上だけで生活している。前の家とまったく変わらない。エアコンが最新型になったのでかえって快適になったぐらいだ。
猛烈に捨てたり売ったりしたものは小林よしのりの本とか(妻がコヴァなのだ)15インチのCRTディスプレイとか、2人暮らしなのになぜか3つあるスーツケースとか、誰が買ったのかわからない鉄アレイとか、こんなもののために年間60万払っていたのか俺は...と愕然とする面子だ。

努力せずに生活費を下げたいなら、狭い家に引っ越すという手は非常によい。
何も節約しないで今まで通りの生活ができるからだ。思い切って面積が半分ぐらいの物件に移っても意外と困らないからやってみるといい。

1DKに二人で住むなんてあり得ない、という反応をされることもあるが、そういう時は妻の仕事上の大先生(先生の先生)の話をして認識を改めてもらう。
この人は新婚当時三畳一間の物件に夫婦2人で住んで、なおかつ一人居候の男を住まわせていたのだそうだ。
この話には感動するポイントが2つあって、それは三畳間に居候を迎える大先生の度量の広さと、もう一つは居候の精神力だ。
普通ものすごく居ずらいだろ。新婚夫婦と川の字になって寝る根性が貴方にはあるか。

2012-09-18

「等しい」と「重複している」の違い。それらとUNIQUE制約の関係

SQL

SQLを使っていると、あたかもNULLがNULLに等しいかのように見える場面が多々ある。
例えば DISTINCT や GROUP BY で複数のNULLが1個に集約されるとか。あるいは集合演算子(UNION, EXCEPT, INTERSECT)でのNULLの扱いとか。
SQL92の解説書

標準SQLガイド (アスキーアジソンウェスレイシリーズ―Ascii Addison Wesley programming series)

作者: C.J.Date,Hugh Darwen,Quipu LLC
出版社/メーカー: アスキー
発売日: 1998/12
メディア: 単行本
購入: 1人クリック: 10回
この商品を含むブログ (2件) を見る

によれば、NULLとNULLは等しくはないが「重複」はするのだそうだ。
列col1とcol2が等しいか、ともにNULLならば、それらは重複していると判定される。
DISTINCTやGROUP BY, 集合演算子は重複を排除するものであり、GROUP BYが複数のNULLを1行に集約するからといって、それは複数のNULLが「等しい」からではない。...ということらしい。

ではSQLの規格は、「等しいこと」と「重複」のどちらに基づいて一意性を判断するよう求めているか。
SQL92には、UNIQUE制約は「いかなる2行も、nullではない同じ値を持たないとき」に満足される、と書いてある。
つまり「重複」ではなく「等しいこと」に基づいて一意かどうかを判断せよ、ということで、やはり前回見た「NULLがUNIQUE制約に縛られない」というMySQLの挙動は、SQLの規格に適合しているようだ。

2012-09-13

「NULLがUNIQUE制約に縛られないことを利用する」のは、正当なNULLの使い方

SQL

リンク先は「UNIQUE INDEXを振った列に複数のNULLを投入できること利用して、ユニークであるべきユーザIDの使い回し(=退会したユーザのIDを新規ユーザに開放する)を実現する」という話。

アクティブなユーザ名はユニークにしたいけど削除されたユーザの情報は残したい。でも削除済みユーザテーブルは作りたくない
とかいうワガママを発揮したい時にdeleteフラグに使えないかなーなんてだめですかそうですか。なんか他にまともな方法無いですか…。
MySQLのこういうのっていかがなもんか - 桝原翔市的博客

いやーこれはまともな方法じゃないでしょうか。
「NULLはNULLに一致しない」のが絶対の原則なのだから、NULLを使ってUNIQUE制約を回避するのは裏技でもwork-aroundでもない、正当なテクニックでしょう。
私はTM派なので実表上にnullを発生させる設計はしないが、Nullableな列を使ってもよい流派の方であれば、特に避ける理由はないんじゃなかろうか。

「NULLがUNIQUE制約に縛られない」ことに違和感がある方は、NULLについて「対象にはその値があるが、システムはその値を知らない状態」ではなく、単に「値が無い状態」をイメージしているのではないかと推測する。後者のイメージを持っていると

NULL+1がNULLになる
1=NULLがUnknownになる
UNIQUE制約のある列に複数のNULLを設定できる

といったSQLの挙動について、なぜそうなるのか理解できないはずだ。「値が無い」なら、

NULL+1は1でいいんじゃないか... 実際SUM関数で集約すれば1になるのだから...
1=NULLはFalseでいいんじゃないか...「値が無い状態」が「1という値」に一致するわけがないのだから...

みたいな疑念が沸くだろうから。
NULLは「値がある(が、システムはその値を知らない)状態」だと思えば、これらの疑問は解消する。

「NULLがUNIQUE制約にひっかかるDBMS」は矛盾しているのか？

リンク先のブックマークコメントによると、SQL Server等、複数のNULLがUNIQUE制約にひっかかる実装も存在するらしい。
そういうDBMSは「NULLはNULLに一致する」「NULLはNULLに一致しない」を同時に言っていることにならないか。つまり矛盾しているのではないか。大丈夫か。
... と思ってSQL標準の解説書を読んでみたら、SQLでは「一致」と「重複」が別の概念で、NULLについては「重複するが一致しない」のだとわかった。
UNIQUE制約を一致の概念に基づいて動作させるのがSQL標準で、重複の概念に基づいて動作させているのがSQL Server等ということで、後者にも矛盾はないようです。

2012-09-10

データモデル自体はアジャイルなのだが...

モデリング

全体に与える影響が極めて大きく、後戻りしにくい「スポンジ層」というのが存在する。そのひとつが渡辺さんの言われているデータモデルである。
データモデリングなきアジャイル開発は危ういか？：An Agile Way

平鍋さんが「(業務システムの)データモデルの変更コストは非常に高い」という意味のことを書かれている。これについて。

データモデル自体の変更コストは非常に低く、後戻りし放題である。新しいスキーマでcreate tableして、データをロードして、drop table/rename tableするだけだからな。
しかし、データモデルに手を入れるためには、同時に既存アプリケーションへの影響を調査して、漏れなく修正しなければならない。
プロジェクトが進行するほど、データモデル改変の影響範囲は広がり、修正点も増えていくだろう(元記事でデータモデルを「高リスク制約」つまり「プロジェクト開始からの時間が経過するほど変更コストが高まるもの」に分類しているのは、このことを指しているはずだ)。
この手間をデータモデルの変更コストとしてカウントすれば、元記事にあるように「データモデルの変更コストは高い」という結論になる。

私のようなデータベース中心主義者から見れば、データモデルは非常に俊敏なシステムの構成要素であって、それを参照するアプリケーションこそがアジャイルではない、ということになる。
問題はデータモデルではなくアプリケーションなのだから、

将来の変化に対応しやすいデータモデルをプロジェクトの最初に作成する
将来の仕様変更時にスキーマを変更しなくて済むよう、スキーマレスなDBを採用する

といったことでは、元記事で言う「データモデルの変更コスト」は下がらない。
代わりに

データモデル変更の影響範囲を極力狭めるようアプリケーションをレイヤリングする

ことが重要(というか、不十分なんだけどそれぐらいしか打ち手がない)、と考える。

現状、「データモデルをアプリケーションに極力漏らさないようにする」ことが当たり前になっているとは思えない。
例えば、割とメジャーなO/Rマッパーが持つ「クラスをそのまま基底テーブルにする」という発想。JPAの @Table みたいな考え方。
これ使ってしまうと「社員と部門の関係を1:NからM:Nに変更」といったデータモデルの変更が、アプリケーションの各レイヤに筒抜けになる。
もちろん「社員マスタにマップされた Employee クラスのインスタンスはDAO層でのみ参照する。より上位の層には Employee のデータを転記したDTOを参照させる」とかすれば既存アプリ内の影響範囲を限定できるが、やらないでしょう。

あるいは「スキーマレスなDBは、仕様変更があってもいちいちスキーマを変更しなくてよいので、外部環境の変化に強い」みたいな話。
スキーマの変更自体は、参照元アプリケーションの調査・変更に比べれば大した作業ではない。スキーマの変更コストをゼロにしたところで、アプリケーションの変更量が変わらないか、逆に増大するならば意味はない。
ところが、スキーマレスDBを採用したら「社員と部門の関係はM:Nである」といったデータ間の関係(=要するにデータモデル)はプログラムで表現するしかなくなる。つまりデータモデルがアプリケーション全体の中に溶け込んで拡散する。よって論理的なデータモデルの変更が、即アプリケーション全体の調査・修正につながってしまう。

元記事で言う「データモデルの変更コスト」を下げるためには、アプリケーションの中でデータモデルを参照する範囲をぎりぎりまで絞り込まなくてはならない。
RDBを前提にすれば、「データアクセス層を作ってその中だけでSQLを発行する。データアクセス層から出てくるものはデータモデルを反映しない、単なる二次元の結果表にすればよい」ということで、そんなの当然じゃないかと思われた方はおめでとうございます。他にも打てる手があるという方は教えてください。

2012-02-24

プロとアマの境目について語るな。一流と二流の違いを語れ

仕事

勝手ながら補足させていただくと、

手塚貴晴さんという建築家がいる。一種の天才なのだと思うのだが、5年ほど前、TVで見た光景が忘れられない。
部下の設計者が徹夜で作ってきた建築模型を一目見るなり「これはゴミだね」と言って、ガシャリと壊してしまったのだ。
僕はそのころ駆け出しコンサルタントになったばかりで、部下の人の痛みがよく分かった。
それと同時に、本当にたくさんのことも学んだ。

あなたの作ったものはゴミである、あるいはプロとアマの分岐点

これをやっていいのは一流だけだ。「私は一流だ」と言い切れない程度の人間は絶対にやってはならない。
理由は書かないが、書かなくても同意してもらえるのではないかと思う。

そして問題のプロ論。

そして、「頑張ること」それ自体に価値があるのはアマチュアの世界、価値がないのがプロフェッショナルの世界である。
全ての人がプロフェッショナル的に仕事をするべきだ、とは僕は思わない。
でももしプロフェッショナルになりたいのであれば、「僕、努力したもん」は封印しなければならない。そんなこと、他の人は知ったこっちゃないのだ。

プロとアマの境目について語る、という行為は甘美だ。俺も「プロのSEとは何か」とか若手に向かって思いっきり説教してみたい。してみたいが、絶対の禁忌にしている。
なぜか。「プロとは何か」なんて客観的には自明だからだ。
SE稼業で報酬をもらっている人は全員プロのSEだ。腕が無かろうが顧客の評価が低かろうが関係ない。
プロ野球選手が全員プロであるのと同じ意味で、金もらって仕事しているSEは全員プロのSEだ。

にもかかわらず、我々が「プロなら最低限この程度できてもらわないと」という主観的なラインを捏造して、レベルの低い同業者を非プロ扱いしたくなるのはなぜか。
それは、はっきり言って大変に気持ちよいからです。
横軸に人数、縦軸にレベルを取ると、どんな世界もつぶれたピラミッドの形になるが、プロ・アマのラインを恣意的に捏造すれば、自分を一流と同じ高位のグループに、相手を劣位に置くことができて大変気持ちいいのです。

プロ論をぶっている人を観察すれば、例外なく(本当に、一人の例外もなく！)自分をプロの側に含めていることに気付くだろう。
自分がアマ側に転落しないように、恣意的に線を引いているから、誰でも必ずプロになれる。
これは本当に危険な行為で、中年期の職業人の精神を腐らせる元凶みたいなものだ。
お勧めはプロ・アマの代わりに一流と二流の境目について考えることだ。こうするとさっきまで馬鹿にしていた人と自分が同じグループに入ることを発見して、大変恥ずかしくなるわけです。

俺にも恣意的な線引きをさせてもらえれば、「一流の(あるいは師の)目から見れば、俺も新人も似たようなものだろうなあ」という自覚が無い中年はダメです。下ばっか見てないで上見ないと。

まとめ

成果物をゴミ扱いしても、自分が一流なら感謝されるだろうし、大した人間でなければ「何だこいつ」と思われて終わりだ。
「何だこいつ」と思われた原因を、自分ではなく相手に求めているうちは一流にはなれないと思うがどうか。ちょっと強引か。

2012-01-04

InnoDBのclustered indexはあまり役に立ってないんじゃないのか

MySQL よくわからないこと

縁あって仕事でMySQLを使いそうなので、いまのMySQLがどうなっているのか少しずつ調べている。
で、現在のデフォルトストレージエンジンであるInnoDBの設計思想に困惑している。
InnoDBは主キーを強制的にclustered indexにするとのことだが、それって何の役に立つのだろうか？

何のためのclustered indexか？

clustered indexの利点は

一般のb-tree indexに比べて、range scanが圧倒的に速い*1
大量データ同士を最速で結合する「ソートなしMerge Join」が使える

の2点だ*2。
これらの利点の代償として

行長を拡大するような更新が多発するとスキャンが徐々に遅くなっていく
主キー値が昇順になるようにinsertしないとスキャンが徐々に遅くなっていく
ROWIDが存在しないので、セカンダリインデックスを経由するデータアクセスの効率が悪い*3

といった問題を受け入れるわけだ。
しかし、以下の理由によりInnoDBではrange scanや結合の高速化はあまり期待できない。

1. InnoDBではrange scanしたいキーにclustered indexを張ることができない

受注ヘッダテーブル

{ 受注ID(PK), 受注日, 顧客番号, 合計金額 }

がInnoDB上にあって、受注IDには整数の連番が設定されているとする。
InnoDBは主キーである受注IDについてclustered indexを構築するが、無意味な連番である受注IDを範囲検索(=range scan)するアプリケーションは多分ないだろう。
いっぽう「1月1日から12月31日までの顧客ごとの合計金額を算出する」など、受注日でrange scanしたいケースは多々あるが、clustered indexは主キーである受注IDが占有済みなので、受注日にはセカンダリインデックスを張ることしかできない。よって、セカンダリインデックスのrange scan＆主キー値による低速なランダムアクセスをするか、clustered indexの全件スキャンをするか、どちらかで処理せざるを得ない*4。

2. MySQLはソートなしMerge Joinを実行できない

SQL ServerもInnoDB同様、主キーをデフォルトでclustered indexにする仕様だ。
SQL ServerのサンプルDB AdventureWorks の受注ヘッダ・受注明細テーブル

SalesOrderHeader　{ SalesOrderID(PK), ... }
SalesOrderDetail  { SalesOrderID(PK), SalesOrderDetailID(PK), ... }

をSalesOrderIDで結合するときの実行計画を見ると、結合方式はMerge Joinだが、マージの前にソートをしていない。これは、ヘッダ・明細ともに主キーがclustered indexになっているため、両テーブルをclustered index scanすればソートなしで即結合できるためだ。

この「ソートなしMerge Join」は、大型のテーブル同士を結合するための最速の結合方式だ。両方のテーブルにclustered indexがあるなら、結合はぜひ「ソートなしMerge Join」で行いたい。
ところがMySQLはマージ結合アルゴリズムを搭載していないので、テーブルにclustered indexがあっても「ソートなしMerge Join」することができない。
MySQLが持っている結合アルゴリズムはNested Loop JoinとBlock Nested Loop Joinであり、前者は大量のランダムアクセス、後者は内部表の複数回のスキャンが足かせとなって、ソートなしMerge Joinほどのパフォーマンスを出すことはできない。

InnoDBに適した用途とは？

以上のことから、InnoDBを採用しているがclustered indexを有効利用できず、そのコストだけ負担しているシステムが多々あるものと推測する。
InnoDBが広く使われている理由は他に選択肢がなかったからで、インデックス/テーブル分離型のMVCCなストレージエンジンの開発が進めば(PBXTとかFalconがそうなのですよね)、InnoDBの出番は減って行くだろう。

...と考える前提として、

clustered indexはrange scanと結合を高速化するための特殊なインデックスであって、諸々のデメリットのために汎用性に欠ける
range scanや結合を高速化できないのであれば、clustered indexは使わないほうがよい

と思っているのだがどうか。SQL Serverなどは何でもclustered indexにしようとするが、あっちの陣営が正しいのか。

仮に「clustered indexの汎用性に問題はない。主キーは常にclustered indexにすべきだ」ということであれば、clustered indexをサポートする他のDBMS(Oracleなど)でも、主キーをclustered indexにするのがベストプラクティスということになる。そうでないならば、主キーがclustered indexになってしまうInnoDBは用途を限定すべしということになる。その限定された用途とは何か？MySQLユーザ界隈ではどのように言われているのか？

*1:「一般のb-tree index」がcovering indexになっている場合を除く

*2:この他に「一般のb-tree indexに比べて、ROWIDでの行のlookupを省略できるので、index seekが速い」という利点があるが、これは「もともと非常に速いものが、もっと速くなる」話なので、clustered indexのおかげで性能問題をクリアできた！という経験をすることはないだろう。そんな経験があったらおめでとうございます

*3:これは実装による。Oracleの場合、clustered indexにもPhysical Guessと呼ばれるROWID的なものが格納されているのだそうだ

*4:逃げ道がないわけではない。{ 受注日, 受注ID }で複合インデックスを作成してうまいことやれば受注IDのrange scanに誘導できるだろう。別にエントリを立てて検証する予定

「SQLアンチパターン」の主張

「IDリクワイアド」アンチパターン

何がいけないのか

どうすべきか

感想

■複合主キーを推す理由が書かれていない