flint>flint blog>2013>Jan>14>でかいテーブルの恐怖

でかいテーブルの恐怖

あけましておめでとうございます。 1月も既に半ばではありますが。 昨年10月の下旬から始まった仕事のスケジュールがこれまでと較べてかなりタイトなもので、ブログの更新もすっかり滞っておりました。

今回の仕事を含め、大学時代から、何故か他人が作ったシステムの分析・改修を手掛けることが多いのですが、それらの中には動作しているのが不思議なほどに設計・実装がグダグダなものが少なくありません。 特に、データベースを利用するような比較的規模の大きなシステムにおいては、そうした出来の悪さは「手の施しようのない」悲惨な稼動状態 (加えて、にも関わらずなんとかしなければならない凄惨な作戦状況) を生み出すことに。

このエントリでは、その要因の一つである巨大な (カラム数の多い) テーブルについて述べていくことにします。 テーブル定義が肥大化する要因を個別に検討する前に、私のおおまかな判断基準を示すと、

  • カラム数20超: 脳内で黄色ランプが点灯
  • カラム数60超: 脳内で赤ランプが点灯
  • カラム数100超: 赤ランプが回転

といった具合になるでしょうか。

ちなみに、これまでに遭遇したことのあるテーブルのカラム数の最大値は376。 定義をコンソールで確認しようとしても、テキストが一瞬で画面上方にスクロールして飛び去ってしまいます。(泣)

必要な数だけ?

最も典型的なのが、レコードに同種のデータを複数含める、要するに配列を持たせるために、次のようなテーブル定義を行う設計です。

カラム名 制約 解説
tel TEXT NOT NULL 連絡先電話番号
tel2 TEXT - 連絡先電話番号 (2)
tel3 TEXT - 連絡先電話番号 (3)
tel4 TEXT - 連絡先電話番号 (4)
...以下繰り返し...

最初のカラム名が "tel1" となっていないところから推測するに、このテーブルが定義された当初は連絡先として登録する電話番号は一つだけだったのでしょう。 ところが、運用開始後にユーザから「携帯電話の番号も登録できるようにしたい」などのリクエストが出て、これに対応するためにカラム "tel2" を追加。 その後、担当者が複数人になるなどして、登録できる電話番号の最大数が徐々に増えていった......ありがちな光景です。 経験的にいって、この手のカラム群の要素数はだいたい 4 ~ 7 ですが、20に達する ("tel20" まである) ことも希ではありません。

ただし、これはまだ「配列」の要素がスカラ型なのでまだマシな方。 この設計手法が構造をもつデータに適用されるとどうなるか。

カラム名 制約 解説
charge TEXT NOT NULL 担当者氏名
charge_tel TEXT NOT NULL 担当者電話番号
charge_tel2 TEXT - 担当者電話番号 (ケータイ)
charge2 TEXT - 担当者 (2) 氏名
charge2_tel TEXT - 担当者 (2) 電話番号
charge2_tel2 TEXT - 担当者 (2) 電話番号 (ケータイ)
charge3 TEXT - 担当者 (3) 氏名
charge3_tel TEXT - 担当者 (3) 電話番号
charge3_tel2 TEXT - 担当者 (3) 電話番号 (ケータイ)
charge4 TEXT - 担当者 (4) 氏名
charge4_tel TEXT - 担当者 (4) 電話番号
charge4_tel2 TEXT - 担当者 (4) 電話番号 (ケータイ)
...以下繰り返し...

この手法に潜む狂気の片鱗が見えてきたのではないでしょうか。 この場合、4件反復しただけでカラム数は12も増加。 このテーブルには他にも色々な種類のカラムが存在することも考え合わせれば、イエロー/レッドゾーンへの突入は時間の問題です。

こうした要求への正しい対処法については、以前のエントリで解説したので、そちらを参照してください。

flint blog: 関係データベースにおける配列の表現
http://www.flint.jp/blog/?entry=23

手元にあれば便利?

データベース内のテーブルは大抵の場合、他のテーブルを参照しています。 (これは裏を返せば、他のテーブルから参照されている、ということでもあります。) こうした参照は通常、テーブル内のレコードを一意に特定する「キー」と呼ばれる値を保持することで行われます。 例えば、「発注 (order)」テーブルは、下図に示すように「顧客 (customer)」テーブルと「製品 (product)」テーブルを参照します。

order
id customer product num date
3121 460 3910 1 2012-12-16
3122 456 2203 5 2012-12-16
customer
id name mail address
456 土方 護 samurai@prizeneck.com 東京都新宿区...
457 遠山 遥 second-sighted@prizeneck.com 東京都杉並区...
460 井川 良太郎 mechanic@prizeneck.com 東京都墨田区...
product
id name description
2203 サングラス 超音波ソナー搭載で夜間の使用にも対応。
3910 チョコレートケーキ 本格的な甘党をも唸らせる逸品。

このような関連付けにより、例えば order.id = 3121 は、「井川 良太郎」による、「サングラス」の発注であることが示されるという寸法。 しかし、これらの情報を得るには、単一のテーブルを見るだけでは不十分で、キー値で対応付けられた複数のテーブルを参照する必要があります。 そして、そのためには適切な結合操作を施すためのSQLや、参照外し (dereference) を行うためのコードを記述しなければなりません。

その手間を省くために用いられるのが、テーブルにカラムを追加して、必要な値をマスタレコードからコピーするという手法。 先の例で言えば、テーブル order を次のように変更すれば、このテーブルひとつを見るだけで、発注情報の概要が分かるようになります。

order
id customer customer_name product product_name num date
3121 460 井川 良太郎 3910 サングラス 1 2012-12-16
3122 456 土方 護 2203 チョコレートケーキ 5 2012-12-16

ですが、このような設計にしてしまうと、情報の一貫性を保つのが困難になります。 例えば、発注が行われた後に、ある顧客の名前の記入に誤りがあることが判明したときのことを考えてみてください。 これを修正するには、最初の設計では customer.name の値を更新するだけでよいのですが、後の設計ではそれに加えて order.customer_name の値にも更新をかけなければなりません。 もし、この同期操作が何らかの理由で正しく行われなかった場合、データの不整合が生じ、システムの動作にも支障をきたすことに。 さらに、データの整合性チェックや、発生したデータ不整合の解消には、システムの運用や動作ログなどの調査が必要となるため、結果として保守・運用コストの増大も招くことにもなります。

システムによっては、一部のデータについて、マスタレコードからのコピーが必要になることもあるので、こうした手法の採用を一律に否定することはできません。 しかしながら、経験的にいって、設計上の必要性のない、ただ実装の手間を省くための横着であることが殆どです。

スイッチがいっぱい

殆どのデータベースシステムでは、False (/Off/No/0) または True (/On/Yes/1) のいずれかの値を取る Boolean 型のフィールドがサポートされています。 この機能を利用すること自体には何の問題もないのですが、いかんせん Boolean 型は表現できる情報量が少ない (1bit) ため、きちんと考えて設計を行わないと、カラム数の増大に繋がりやすくなります。

まず問題となるのは、複数の Boolean フィールドが独立していない場合。 例えば、以下の記事で指摘したように「無効 (disabled)」と「削除 (deleted)」の状態をそれぞれ個別の Boolean 値で管理する場合などがそれに当たります。

flint blog: データの削除
http://www.flint.jp/blog/?entry=8

レコードが「削除」された状態 (deleted = 1) においては、「有効/無効」をあらわすフラグ (disabled) の値は意味を持ちません。 値の組み合わせは 2×2 = 4 通りあるのに、実際に区別しうる状態は 3 種類しかないため、そこにデータの「自由度」が生じ、システムの保守性を妨げる潜在的な要因となります。 このような場合は、上記事内で示した通り、状態を統合して、3つの状態を表現することが可能な整数型の単一カラムとして定義するべきでしょう。

互いに独立はしていても、同様の機能をもつカラムが複数ある場合は、それらをまとめること検討してみると良いかも知れません。 例えば、食堂のランチメニューを表すテーブルについて、それぞれの曜日によってリストに載せるかどうかを設定できるようにしたい場合。 深く考えずに、愚直にテーブル定義を書くと、次のようになるでしょう。

lunch
カラム名 制約 解説
id INTEGER PRIMARY KEY 識別子
name INTEGER PRIMARY KEY 名前
price INTEGER NOT NULL 価格: 単位 (円)
available_sun BOOLEAN NOT NULL リストに載せるかどうか (日曜)
available_mon BOOLEAN NOT NULL リストに載せるかどうか (月曜)
available_tue BOOLEAN NOT NULL リストに載せるかどうか (火曜)
available_wed BOOLEAN NOT NULL リストに載せるかどうか (水曜)
available_thu BOOLEAN NOT NULL リストに載せるかどうか (木曜)
available_fri BOOLEAN NOT NULL リストに載せるかどうか (金曜)
available_sat BOOLEAN NOT NULL リストに載せるかどうか (土曜)

とても無駄な感じがしますね。 曜日の種類が増えるということはまずないでしょうから、これらのフィールドはまとめてしまうのが良さそう。 というわけで、定義を以下のように変更します。

lunch
カラム名 制約 解説
id INTEGER PRIMARY KEY 識別子
name INTEGER PRIMARY KEY 名前
price INTEGER NOT NULL 価格: 単位 (円)
available_wdays INTEGER NOT NULL 各曜日にリストに載せるかどうか (ビットフィールド)
[ 0: 日, 1: 月, 2: 火, 3: 水, 4: 木, 5: 金, 6: 土 ]

例えば、月, 水, 木用だけリストに載るメニューの場合、最初の定義では7つのフィールド (available_sun, available_mon, ...) にそれぞれ False, True, False, True, True, False, False という具合に値が格納されていました。 後の定義では、available_wdays に 26 (= 21 + 23 + 24) という値を格納します。 一般の方は計算が煩雑だと感じるかもしれませんが、この種のビット演算は、コンピュータ技術者であれば誰もが常識として知っている (はず/べき) もの。 まともなプログラマやシステムエンジニアであれば、アルファベットの大文字と小文字の区別をするのと同じ程度に造作もなくこなせるものなので、これによって保守性が下がることを懸念する必要はありません。

システム開発は複雑さとの戦い

どんなに上手く設計を行ったとしても、実際の業務を不足なくこなせるだけの規模のシステムはそれなりに複雑になるもの。 ましてや、設計を軽視して場当たり的に拡張され続けたシステムの構造の複雑さは想像を絶するものであり、人間の理解力・注意力では取り扱うことのできないレベルに達しているものも少なくありません。 そして、この複雑さは、まず開発および保守を行う者の肩の上に負担としてのしかかってきます。 いかに熟達したエンジニアであっても、そうしたシステムに携わり続ければ、疲弊・磨耗し、その仕事の質は低下の一途を辿ることでしょう。

その時の仕事はかなり大きな仕事だった。
大きすぎた。
データベースのテーブル数は1000を超え、それぞれのリレーションを把握するのがギリギリな仕事だった。

事態は一向に良くならない。
1000あるテーブルはどんどん増えていく。
これを使ってSQLを作るが、そのSQLの大半が自動生成のSQLで、誤ったテーブルのまま自動生成されていく。

俺の次の仕事は、この誤ったSQLを見つける事だった。

地獄だった。
毎日生成し、毎日誤りを発見しなければならない。
山のような小豆の中から、腐った小豆を見つけ出せ、と言われているようなものだった。

見つけ出すツールはあったが、精度が悪過ぎて人間の目で見る方が良いという酷い有様だ。

テーブル数が1000を超えているというのは、それだけで失敗が約束されているも同然の状況。 ここに至ってなおプロジェクトの中止を決定できないような業者には、システム開発を任せてはならないのです。

そして、そのような現場で開発されたシステムは、当然のことながら、多数の不具合や脆弱性を含む低品質なものにならざるを得ません。 たとえ表面上は正常に稼動しているように見えても、その内部では不吉な「捩れ」が確実に進行していきます。 そしてそれは、トラブルやシステム改修の発生時に、開発者とユーザの両方に、莫大な工数およびコストとして襲い掛かってくることでしょう。 個人的には、IT業界に蔓延するデスマーチや、メンタルヘルス問題の原因の一端は、こうした技術的なファクタを軽視するビジネススタイルにあるのではないかと考えています。

データベース・リファクタリング

2.4 データベースの不吉な臭い

Fowler (1997) は「コードの不吉な臭い」という概念を持ち込んだ。 それはリファクタリングの必要性を感じされる, コードによく見られる問題のことである。 一般的なコードの臭いには, スイッチ文, 長すぎるメソッド, 重複したコード, 属性/操作の横恋慕などがある。 同じように, データベースにも, リファクタリングの必要性を感じさせる不吉な臭いがある (Ambler 2003)。 例えば次のようなものである。

冗長なデータ
冗長なデータは, 運用データベースにおいては重大な問題である。 データが複数の場所に格納されていると, 矛盾が起こる可能性があるからである。 例えば, 顧客情報が組織のさまざまな場所に格納されているのは珍しくもない。 実際に多くの会社では, 誰が自社の本当の顧客なのか, 正確なリストをまとめられずにいる。
カラムの多すぎるテーブル
テーブルのカラムが多すぎるのは, テーブルの凝集度が低いしるしである。 複数のエンティティのデータを格納しようとしているのではないだろうか。 例えば, Customer テーブルに, 3つの住所 (送付先住所, 請求先住所, 季節限定住所) や複数の電話番号 (自宅, 勤め先, 携帯電話など) が含まれている場合が考えられる。 AddressPhoneNumer というテーブルを追加して構造を正規化する必要があるかもしれない。
『データベース・リファクタリング』/ 著: スコット W. アンブラー, ピラモド・サダラージ

私がこの事業のコンセプトとして掲げている "firm and compact software" は、こうした状況を打破する堅牢かつ保守性の高いソフトウェアを提供していこうというスタンスを表わしたもの。 「うちのシステム、機能追加するごとになんだかおかしくなっていくんだよな。」 といった悩みを抱えている方がいらっしゃいましたら、開発者側・ユーザ側を問わず、当方へご相談ください。

Narita (竹を割ったようなシステムが理想)
このエントリーをはてなブックマークに追加

Comments

Author
URI
Mail
Title
Content