2. データベースを作る

データベース記述言語 (DDL) や問い合わせ言語 (SQL) を知らない方でも、概念的に読んでみてください。

DBMS のお話の題材としては、10,000件のサンプルデータはやや不満ではあります。
できればもう1〜2桁多いデータ量の方が面白いのですが、それでも市販の教科書・入門書等のサンプルよりは、かなりマシです。

データベースシステムは大量のデータを扱えることに意味があります。少量のサンプルデータで論じても、なかなか見えてこない部分が多いのです。

2.1. 実体と関連 ( Entity Relation )

2.1.1 論理表 ERD

非常に単純な且つ純粋な階層的データなので、設計は簡単です。

[ 実体 ]と-関連-
         1-目名-n        1-科名-n        1-属名-n
[ 目名表 ]----->[ 科名表 ]----->[ 属名表 ]----->[ 種名表 ]
  目名 *--------> 目名      /---> 科名      /---> 属名
  目和名          科名 *---/      属名 *---/      種名
                  科和名          属和名          種和名
                                                  種英名

2.2. 物理表 ( CREATE TABLE )

2.2.1. CREATE TABLE

4個のテーブルを作りました。

• テーブル定義の詳細

論理的にはちょっと変なのですが、各テーブルの行にはシーケンス番号の項目を持たせています。

単に分類を表すためだけなら不要ですが、じつは分類の並びには意味があって、進化の過程での分岐が古いものが先に、そして新しいものが後に来るような、緩い表示の慣習があります。
もっとも早く分岐したダチョウ目を先頭に、進化の最終形である鳴禽類(スズメ目)が最後に来るようなことです。

なので、元のリストの順番を保持して表示したいことが多くあるので、連番を振っています。

2.2.2. 連番の採り方

さて、連番を振るためには、どうするか？

新規で行を INSERT する場合、シーケンスの採番の手段はいくつかあります。

簡単なのは MAX() 関数を使うことですが。
MAX()で現在の最大値を取得し、インクリメントして新規行の番号にします。

じつは、この 10,000件程度のデータベースでは MAX() 関数でも問題ないのですが、もっとデータ件数が増えた場合には、かなりのオーバーヘッドになります。

手抜きせずに、番号を保持するテーブルを作りました。
トリガーでテーブル更新してみたいという意図もあったりします。

• 採番カウンター用テーブル定義の詳細

2.2.3 CREATE TABLE のSQL

と言うわけで、出来上がった CREATE TABLE, CREATE INDEX の SQL 文は、以下のとおり。

• [ CreateTable スクリプト ]
• [ CreateIndex スクリプト ]

2.3. トリガー

前述のとおり、行を INSERT したときに自動的にユニークな連番を付加しています。

2.3.1. トリガーの例

今回は、これをトリガーでやってみました。

プログラム側では意識することがなくなって、トランザクション処理も確実なので楽になります。

トリガーの定義記述はこんな感じです。

• トリガー定義例の詳細

2.3.2. CREATE TRIGGER の SQL

出来上がった CREATE TRIGGER の SQL スクリプト

• [ CreateTrigger スクリプト ]

2.4. ビュー

このような正規化された階層データの場合、TABLE を JOIN して読むことがよく行われます。

2.4.1. VIEW の例

科-目-属-種の4つのテーブルを結合した VIEW を作成してみました。

• ビュー定義の詳細

ごく普通です。

普通ではあるんですが、さて？どうなんでしょう。

もしかして、私の感覚が DBMS 屋さんのそれとは少しずれているのかもしれません。
JOIN って、あまり好きじゃないんです。

なぜかって？

2.4.2. 森の妖精

じつは、2.1.2. の CREATE TABLE 文ですが、さらっと出来たわけではありません。
ちょっと悩んで、紆余曲折がありました。

データ設計、意外と悩む

最初動かしてみたときに、各テーブルの主キーとなる「学名」の項目に UNIQUE 制約を書いていませんでした。 というのも、稀な例として同じ学名に複数の和名を割り当てたい状況があるかも、と頭を過りました。

分かりやすい例としては学名「Columba livia」。皆さんよくご存じの公園にいる、豆を撒くとやってくる鳩ぽっぽです。
正式な和名は「カワラバト」ですが、俗称の「ドバト」もよく使います。専門家の文章でも、図鑑の表記でも、混在したり併記したりすることがよくあります。

手抜きと妥協はどこにでもあるけど

厳格には別名格納用の表を作って正規化するべきでしょう。

でも、そこまでしなくてもなー……と。もし重複データがあったらどういう設計がいいかなぁ？
など、いろいろ考えながらテストしていて、たまたま UNIQUE 制約を外していたのです。

それでもシステムは動く

その結果どうなったでしょう？

＊＊＊ いやぁ、何の問題もありませんよ。 ＊＊＊

UNIQUE 制約て、アフォなプログラムが事前のデータ重複検査を手抜きしたときに、エラーで叩き落とすぐらいのもんでしょう。というのが、あまり項目属性の制約を使わない私の感覚ですが
実際にはデータ更新の時に重複チェックはやっていますし。
というか、データの整合性チェックを手抜きして DB 更新で落ちるアプリケーションなんて、あってはならない。( よく見かけますけどね;-p )

プログラムは全て予定通り動きます。

しかし、後述の処理時間測定をやっていて、「？？？」と思いました。

JOIN 使用時だけときどき妙に時間が掛かることがあります。通常の数倍〜10倍程度。
4.1.4. 結果の検証

コンピュータの気持ちになって考える

あ、気がつきましたね。

こういうときは、コンピュータの中の小人さんになったつもりで、JOIN を SELECT のシーケンスに展開する際の『SQL パーサの気持ちになって考えてみよう！』ってわけです。
当たり前ですよね〜。もしかしたら N : N の関連があるかもしれないと疑いながら SELECT を組み立てていくわけですから。

これ、バグですか？

通常の 10倍遅いとはいっても、ミリ秒単位の計測をしてはじめて分かります。

普通にパソコンの画面で応答を見ていたって、全く分かりません。
意識して、そういうテストルーチンを計画しない限り、ほぼ 100% 見逃します。

こういう『バグ？』って、じつは大規模システム開発でも結構あります。気がつかないだけで。

論理的に考えうる全ての要素を網羅しようとするDB設計者と、あくまで現実に即して考えるアプリケーション設計者の意志疎通が充分でないと、設計の意図とは異なる使われ方をします。

動いてるじゃないですか。何が問題か？

この種の『バグ？』は、『バグ』として認識されることは滅多にありません。

だって、仕様通り完璧に動くんですもの。外部仕様を満たしているんですから、絶対にバグではない！と言い張るかもしれません。

テスト工程でも問題なし。本番に移行して初期稼働でも大丈夫です。

ユーザーさんは間違いなく検収書にサインしてくれます。

みんな幸せに見えます

でも、システムが成功してそのユーザーのビジネスが成長したときになって、初めて『なんか遅くね？』、そのうち『動かねー！』となります。

O( logN ) のはずが、O( N )やO( NlogN )になってしまっていたのです。

SI営業『これだけ御社の事業がご繁盛してデータ量が増えたんですから』
ハード営業『もっと高性能なマシーンもご用意できます』
ユーザーCIO『よし、営業利益も出てきたし新プラットフォームへマイグレーション。GO！』

商売繁盛、みんな幸せになれます。

幸せをもたらしてくれて誰にも気付かれなかった森の妖精のように、このバグは役目を終えます。
なに、心配ありません。システム開発の深い森が続く限り、また妖精は現れますよ。

でも、それでいいの？

いったい何をテストしたのか

本番環境と開発・テスト環境は同じものであるべきです。
でも、現実はなかなかそうはいきません。
営業ベースの開発費の折衝のなかで、まず開発・テスト機の予算が削られていきます。

予算が足りなくて、グレードを下げなければならなかったら、本番機、テスト機とも同じレベルでダウングレードした方が良い結果を期待できるのですが、そのような例は稀です。

テストデータも、数年後のデータ増大を見越して、想定される最大量のデータを投入してテストするべきですが、それもあまり見たことがありません。

今回は、10,000 件のデータで注意深く見れば問題が分かりました。でも 1,000件以下のデータならおそらく見落とします。そして、本番でデータが 100万件に増えたら、壊滅的に破綻します。
( どのように破綻するか？ ) 4.1.5. イスカンダルの鳥

システム作りの感覚？

JOIN はたしかに RDBMS の重要な機能です。

この例で言うと、テーブルの設計が悪いのです。と多くの人が言うでしょう。
アプリケーションにミスはありません。と皆さんも考えるかもしれません。

でも、テーブルの設計に問題があったとしても、その影響を受けないようなアプリケーションは書けないの？と私は思う。
DBMS 側にそこまで依存するアプリケーションの責任はどうなの？と私は考える。
もっとロバストな強靭な実装は出来ないのか？と疑問を持ちませんか。

冒頭に書いた『DBMS 屋さんの感覚と私のそれはちょっとズレがある』というのは、そういう部分です。

個人的な趣味ですが、抽出件数が少ないときは、1個1個のテーブルに個別にアクセスして明示的にプログラム側で繋げる処理の方が好きです。
プログラマの意図が明確に DBMS 側に伝わるからです。

という前置きで、次のストアード・プロシジャに進みます。

2.4.3. CREATE VIEW の SQL

CREATE VIEW の SQL スクリプト

• [ Create View スクリプト ]

2.5. ストアードプロシジャ

(ストアードプロシジャを「ストアド」と言われる度に凄い違和感)…
省略形なら「プロシジャ」と呼んでくれ

ストアードプロシジャは乱用するとロジックがあちこちに分散してしまい嫌いです。
テーブル定義とプロシジャとプログラムと 3つ見比べないと、なにやってるかわかんなくなる。
分からなくなるから、エイ面倒と別のプロシジャを書く。経験ありませんか？

システム設計やDBMSの教科書では逆のことを書いているけど、現実を見ろ！

部品化やブラックボックス化など、却ってシステムを弱くしバグを作り込む原因になっていたりする現実。
「ブラックボックス」なんて、本来の意味とは逆のネガティブな使われ方をする用語になっていたりする。

もちろん、しっかりと設計されたストアード・プロシジャは処理効率のみならず、ロジックのカプセル化においても有効なのはいうまでもありません。

少し、プロシジャーの例を

2.5.1. 単一行を返す例

多くの場合、これは前記の JOIN 方式より確実に速いです。
そして、UNIQUE 制約の有無にも影響を受けません。

• 単一行を返すストアードプロシジャ定義の例

2.5.2. 複数行を返す例

FireBird/InterBase のストアードプロシジャの特徴として、

 FOR SELECT .... DO SUSPEND;

という構文で、複数行を返すプロシジャが簡単に書けることが挙げられます。
これは、実にパワフル且つ便利です。
これがあるから、JOIN が嫌いともいえますが。
次のプロシジャは、比較的少量のデータセットの抽出においては、おそらく最速。

• 複数行を返すストアードプロシジャ定義の例

2.5.3. 小人さんが知らないこと

この PROCEDURE は、VIEW と同じような仕事をします。
じゃぁ、VIEW でいいんじゃないの？何が違うの？

違いは、SELECT のシーケンスをプログラマが明示的に書くか、DBMS にお任せするかの違いです。
でも、上に UNIQUE 制約で述べたようなこともままあります。
何故？

その前にちょっと考えましょう。プログラマは何故上記のプロシジャをさらりと書くことができたんでしょうか？最適なテーブルのアクセス順を何故知っていたのでしょうか？

そんなの ERD を見れば一目瞭然じゃん。

そうです。小人さんからは紙にかかれた ERD なんて見えないんです。プログラマは ERD を見ていたからさらりと分かったのです。
小人さんはそれをテーブル記述の情報から得て、なんとか効率的な検索ロジックを組み立てようとします。
それに失敗するとテーブルを全件検索するようなことになります。

2.5.4. いろいろな PROCEDURE

その他、今回書いた PROCEDURE は以下のようなものです

• [ Create Procedure ]