Ognacの雑感

木漏れ日々

目次

Blog 利用状況

書庫

ギャラリ

OJTでの知識は全能ではありません

以前にも書いたのですがデータペースの主キーに対して誤解している設計者を見かけます。自己学習しないで、OJT(サンプルや雛型含む)が正しいと認識している人に多く居る気がします。
データベースの主キーは十分条件であって必要条件ではありません。キーや索引を張らないほうがコストが安い場合も多々あります。
例) 顧客の住所マスタの変更に対して変更履歴を保持するのは良くあることでする。
    マスターに対して追加/変更/削除のトリガーで履歴データを作成するのが一般的でしょう。
  単純な項目構成で考えて見ますコード、氏名、住所 の三項目とします。履歴テーブルは (追加or変更or削除識別)、コード、氏名、住所、日時という構成にします。
   (少し手を抜いて、マスターのレイアウトと履歴レイアウトを同一にする時もあるのは内緒です..www)
ここで変更履歴に主キー/索引の設定の是非問題がでてきます。もちろん変更履歴が実運用上頻繁に参照されるケースもあります。原紙伝票発生時点での顧客の住所を印刷する仕様などはそうなります。其の際には索引の設定は必要です。ここではこのような使用はしないものとします。
変更履歴の使用目的が、単純な履歴の保持だけに限定される場合は、主キー、索引の設定はなくてもいいですね。ところが、顧客コート+日時で主キーを張っているテーブルを見かけます。 「なぜ張るの?」と効くと、「なんでそんなこと突付かれるの。主キーは必須なので、この項目でユニークになる組み合わせはこれしかないから当然こうなるでしょ。」と不満が顔に出てます。
変更履歴の参照頻度と索引が有ることによる、追加・変更コストを考えると結論は直ぐでます。
OJTはノウハウの習得には効率的なんですが、基礎知識の一部が欠落しがちです、欠落部分を補う仕組みが要りますね。何が欠落しているか知るのも難しいし。私も欠落している知識が多いし。なにが欠落しているか通知してくれるEducationSoftが欲しい。あれば買いたい。

 

投稿日時 : 2007年6月19日 11:54

Feedback

# re: OJTでの知識は全能ではありません 2007/06/19 12:11 シャノン

# 主キーは必須だと思っていたクチですorz

索引はパフォーマンス向上が目的なので、パフォーマンスが要求されなければ無くてもいいですが、主キーはあってほしいです。
パフォーマンスではなくセマンティック的にということと、プログラムのバグを検出できるという望みからです。
DB素人だからこの際聞いてしまいますが、主キーがあることによるデメリットって何でしょう?

# re: OJTでの知識は全能ではありません 2007/06/19 12:40 かずくん

主キーは他のテーブルとのリレーションを張るためのものなので、単一テーブルだけで完結するのならいらないかもしれない。
でも、将来どう転ぶかわからないので、サロゲートキーぐらいはもっててもいいと思う。

# re: OJTでの知識は全能ではありません 2007/06/19 13:00 Ognac

私もサロゲート派なのでサロゲート項目はつけてます。キーを張るか否かはケースバイケースです。
他からリレーションが張られてなくても、使用頻度の高いテーブル次第で索引は張りますね。
デメリットは? と訊かれると返事に困るものですね。索引域の保守コストとIOコストが高くつくという意外に理由はあるかしら?
テーブルのレコード件数が1000件程度のオーダーならは張らないことが多いです。
それとログ類のデーブル(JobLog、DB_AccessLog 等)にも張りません。蓄積データですし、異常時に参照するので参照頻度が低い。参照するときは要素を切り出して、必要なときに一時的に索引を張れば済む。というのが理由かな

# re: OJTでの知識は全能ではありません 2007/06/19 13:01 Ognac

追記:其のときは、サロゲートも設定しません。

# re: OJTでの知識は全能ではありません 2007/06/19 13:06 シャノン

サロゲートって何? っていうレベルww
DB知識は本当にOJT、しかも入門レベルしか持ってないのですよ(=入門レベルの仕事しかしていなかった、ということ)。

# re: OJTでの知識は全能ではありません 2007/06/19 13:17 囚人

>DB素人だからこの際聞いてしまいますが、主キーがあることによるデメリットって何でしょう?

私も訊きたい。とりあえず、主キーを連番にしたりするので。
逆に主キーが無い方がデメリット多そうな。

SQL Server に限って話をすると(他知らないので)、
・主キー(クラスタ化インデックス)がないと検索時に無駄な I/O が発生。
・主キー(クラスタ化インデックス)がなく、非クラスタ化インデックスだけだとページ分割が起こったときにウザイ。
・検索したときの Order by にしたい。Select したときに順序不定だとウザイ。
・参照しないテーブルなんて存在しない、という前提。参照しないなら、そのデータは必要ない。

みたいな。

>主キーは他のテーブルとのリレーションを張るためのものなので、単一テーブルだけで完結するのならいらないかもしれない。

ちょっと乱暴な気がします。
単一テーブルでデータを一意にしたい場合もあるし、先で挙げたデメリットが発生するような気がします。

# re: OJTでの知識は全能ではありません 2007/06/19 14:46 かずくん

シャノンさん>
>サロゲートって何? っていうレベルww

それ自体に特に意味をもたない値。

アドレス帳作って、シャノンさんに1番振ったとする。
何で俺、一番な訳?ってきかれても、おいら困っちゃう。そんな感じ。
でも、1番で検索したら、シャノンさんにヒット。

逆に、業務の都合で決めたものや、それ自体に意味をもった値がキーになる場合、自然キーっていったような希ガス。
例えば、顧客コードとか、社員コードとか。
業務の都合で決めた値を主キーにしちゃうと、業務が変わるとそのままじゃ使えないので、コンバートとかしたりして、でーれー面倒。


囚人さん>
>>主キーは他のテーブルとのリレーションを張るためのものなので、単一テーブルだけで完結するのならいらないかもしれない。
>ちょっと乱暴な気がします。

2行でワンセットなのに、一行だけ取り出すなんて、ヒドス。OTL

# re: OJTでの知識は全能ではありません 2007/06/19 14:58 シャノン

>> サロゲートって何?
> それ自体に特に意味をもたない値。

なるほどなるほど。

> 2行でワンセットなのに

2行でワンセットの時にしか主キーって使っちゃいけないの?
それって外部キーじゃない? と乏しい知識を総動員してみる。

# re: OJTでの知識は全能ではありません 2007/06/19 14:59 シャノン

せんせー、こんなんみっけた。
http://e-words.jp/w/E4B8BBE382ADE383BC.html

> 主キーを持たないレコードが存在してもならない。

# re: OJTでの知識は全能ではありません 2007/06/19 15:05 シャノン

> 主キーを持たないレコードが存在してもならない。

これは、「主キーが NULL であるレコードが存在してはならない」ってことかな? だとしたら分かりずらー。

# re: OJTでの知識は全能ではありません 2007/06/19 16:02 Ognac

改めて資料を繰ると無いものですね。主キーを設定しないメリットどころか、主キーを張らない使用法
って。
正規化考えて「テーブルを設計しましょう」から始めるんですね。
売上げ伝票から商品コードと顧客コードを切り出して1次正規化しましょう解説すると主キーの存在は必然になるのは当然と理解しちゃいますよね。

シャノン さん、囚人さんの両巨匠から突っ込みがはいると緊張して自身がぐら付いていまいす。< オイ

>これは、「主キーが NULL であるレコードが存在してはならない」ってことかな? だとしたら分かりずらー。 そうですね、「主キーはNULLに出来ません」 の意味です。DB関係の解説書はこの辺の表現が下手だったりしますね。

囚人さんの示されたうざい点
>・主キー(クラスタ化インデックス)がないと検索時に無駄な I/O が発生。
>・主キー(クラスタ化インデックス)がなく、非クラスタ化インデックスだけだとページ分割が起こったときにウザイ。
>・検索したときの Order by にしたい。Select したときに順序不定だとウザイ。
>・参照しないテーブルなんて存在しない、という前提。参照しないなら、そのデータは必要ない。
このうち上位の3点は、大量データで頻繁に更新されるデータには当てはまります。実業務で使われるテーブルには少量データで更新頻度の少ないデータも結構あります。
会社内の部署マスタ、都道府県マスタ、配達可能町名マスターやアプリケーションメッセージなど多くても数百件程度のテーブル
性別区分、締日区分、端数扱い区分、税種類区分など数件から数十件しか存在しなくて且つ変更が発生しないテーブル
これらのテーブルは、クラスオブジェクト化して使うこともありますが、DBに格納することがあります。この類のテーブルは索引を設定して引用すると、索引のIOの分コスト増になります。
これらのテーブルの場合、SQLServer Optimizeをみると索引をはっていても使われなかった記憶があります(違っていたらゴメン)。コストも安いです。
Order byや複数のSelectの結果を用いる使い方はあまりないのでデメリットにはなりません。

逆に、ユニークキーのチェックやデータの整合性の部分でコーディング量が増えるのでソース的には醜くなるという面はあります。トレードオフなんでしょうね。

なんか拙い解説になってしまいました。Help Me。

# re: OJTでの知識は全能ではありません 2007/06/19 16:44 シャノン

うーむ。
データ件数が少なく更新頻度が低いテーブルにはインデックスは要らんというのはわかりますが、主キーを作ると勝手にインデックスが作られてしまうのですね。
だからインデックス要らん=主キー要らんになる?
主キーの主な用途は、そのキーを使ってレコードを検索することだと考えれば、インデックスが作られるのは合理的に思われますが、個人的に主キーは「データを一意に識別するもの」だけであってほしかった。
主キーの意味とインデックスの意味は直交するものだと思うんですけどね。

# re: OJTでの知識は全能ではありません 2007/06/19 17:22 シャノン

> シャノン さん、囚人さんの両巨匠

囚人さんは巨匠ですが、俺には誇称ですよ。

# re: OJTでの知識は全能ではありません 2007/06/19 17:25 Chuki

>主キーの意味とインデックスの意味は直交するものだと思うんですけどね。

ですね。あくまでDBMSの実装の問題です。SQLの規格に索引を張れって書いてるわけではないっす。
#Oracle君もインデックス張ってくれたはずです。

部署マスタとか、結構全表走査しちゃってくれるのも多いんですよね。おんなじ論理区域に収まるんだったら、索引の入出力コストのほうが高いという話ですかね^^;

# re: OJTでの知識は全能ではありません 2007/06/19 19:36 黒龍

えっと誤解を恐れずに書くのであれば主キーは全く重要じゃありません。なくてもナンとでもなります。
一意かどうかを要求するためにはユニークで有る保証(ユニーク属性)が必要で多くの実装ではインデックスを用いて保証します。ユニークであればリレーションも張れるので。
なので主キーを張るとインデックスというよりは主キーである条件(UNIQUEかつNULL不可)のためやむなくインデックスが張られると言う流れです。
じゃ主キーいらないジャンって話ですが私もそう思ったり。ただ、SQLサーバのクラスタ化インデックスの場合はユニークかつNULL不可の特性とマッチした仕組みで属性をぶら下げているので余分なインデックスともならず属性値も格納されるというナイスな仕組みだと思います。(クラスタ化でなく同じ事をしようと思うと元のテーブルが使われない無駄な領域になるため)
読み返すとややこしいですね。伝わりますでしょうか?

# re: OJTでの知識は全能ではありません 2007/06/19 20:00 シャノン

インデックスってユニーク保証するためにあるんですか? 検索を高速化するためじゃないの? ユニーク保証はユニーク制約でするんじゃないの?

ということでググってみると、ユニーク制約とは別に、ユニーク制約の機能を兼ね備えたユニークインデックスというものがあるらしい。なんじゃそりゃ。

http://blogs.wankuma.com/naka/archive/2004/06/26/3319.aspx
http://blogs.sqlpassj.org/yoneda/archive/2004/06/26/2957.aspx
http://blogs.wankuma.com/naka/archive/2004/06/27/3350.aspx

まとめ。
Q:主キーと候補キー(ユニークキー)の違いは?
A:どちらもユニークであるという点では同じ。主キーはNULLを含まず、検索で多用する(=リレーションのためにある?)
主キーは基本的に変更しない(できないことはない)。

「一意性の保証」と「行を識別すること」は区別して考えるべき?

Q:インデックスって何?
A:検索を高速化するための索引。ユニークインデックスとユニークでないインデックスがある。
小さなテーブルでは、使うとかえってパフォーマンスが落ちることもある?

Q:ユニークインデックスとユニークキーの違いは?
A:わかんねorz

# re: OJTでの知識は全能ではありません 2007/06/19 20:45 黒龍

> インデックスってユニーク保証するためにあるんですか? 検索を高速化するためじゃないの? ユニーク保証はユニーク制約でするんじゃないの?
ユニーク制約でユニークである保証をするんですが結局のところユニークかどうか全部見る必要があるので対象列をインデックスとしてくくりだしているんだと思います。インデックス無しのチェック制約もやれないことはないと思いますが速度的なメリットを得るためインデックス化することになると思います。<この辺想像
インデックスを用いてというのは飛躍して書いちゃいましたね。現実的なチェックの為にインデックスを用いるという実装都合なお話です。

>「一意性の保証」と「行を識別すること」は区別して考えるべき?
一意であるということは識別できるということに他ならないので同意と思われ。

> 小さなテーブルでは、使うとかえってパフォーマンスが落ちることもある?
完全正規化された状態ではインデックスとテーブルに違いはないことになります。小さなテーブルでパフォーマンスがというのはインデックス領域+実テーブル領域という形で重複したデータによるキャッシュ圧迫が原因です。

>Q:ユニークインデックスとユニークキーの違いは?
インデックスにユニークであるというチェック制約をつけたものがユニークインデックス。
SQLサーバではユニークであるという保証をするためにはインデックスを作成してユニーク制約をつけるので今回は同意かな?
他の実装ではどうなんでしょ?>ユニーク制約

# re: OJTでの知識は全能ではありません 2007/06/19 21:06 Ognac

私的見解

>SQLサーバのクラスタ化インデックスの場合(略)ナイスな仕組みだと思います。
おお! GoodJob。勉強不足 << コラ

>Q:主キーと候補キー(ユニークキー)の違いは?
>A:どちらもユニークであるという点では同じ。主キーはNULLを含まず、検索で多用する

>主キーは基本的に変更しない(できないことはない)。
主キーの変更は他のTableとのリレーション関係が多いので、リレーションを維持しながらの変更はできないと考えたほうが楽。請求書の鏡と請求明細データなどの親子関係を結んだ時などは特に。

個人的な私の認識は、主キーとはクラスター管理が可能なユニークキーという認識です。

>「一意性の保証」と「行を識別すること」は区別して考えるべき?
「一意性の保証」はinsert時の動作で 「行を識別すること」は select / 条件句の動作なので、異なる動作Objectと認識してます。

>Q:インデックスって何?
>A:検索を高速化するための索引。ユニークインデックスとユニークでないインデックスがある。
もちろん、ユニーク指定がない一般索引は重複可能です

>小さなテーブルでは、使うとかえってパフォーマンスが落ちることもある?
そうそう。これを問題にしたかったわけでして.....www)

>ユニーク制約とは別に、ユニーク制約の機能を兼ね備えたユニークインデックスというものがあるらしい。
>Q:ユニークインデックスとユニークキーの違いは?
>A:わかんねorz

私的には内部的にインデックスを張るので広義に同じと認識してます。

始めほうのコメントにも記しましたが、JobLog的(テキスト順次File的な物)な一方的に吐き出すテーブルも索引を張らない主義です。

わ! 黒龍 さんと殆ど被ってしまった。ごめんなさい

# re: OJTでの知識は全能ではありません 2007/06/19 21:22 黒龍

> わ! 黒龍 さんと殆ど被ってしまった。ごめんなさい
いえいえ。余りぶれてないようで安心しましたw

# 主キーを使うな 2007/06/19 22:06 中の技術日誌ブログ

主キーを使うな

# re: OJTでの知識は全能ではありません 2007/06/19 22:42 シャノン

> 「一意性の保証」と「行を識別すること」は区別して考えるべき?

主キーは NULL を含まないが、候補キーは許容することについて。
http://biz.rivus.jp/words/constraint.html
によると、ユニーク制約がかかっている列に NULL が入っている行が複数あることを許容するかどうかは DBMS によって異なるらしく、標準規格では許容するらしい。
一意性を保証するということは、同じデータを持つ行が複数無いことを保証するということ。だが、NULL はデータではなく、データが存在しないことを表す記号だとすると、有効なデータと NULL は比較できない(だから = NULL ではなく IS NULL を使うのだろうか)。
比較できなければ同じとも違うとも決められないから、NULL を持つ行が複数あっても「同じデータを持つ行が複数ある」ことにはならない。
よって、標準規格では、NULL の行が複数あってもユニーク制約には引っかからない=一意性は保たれている、と考えられる。
だが、それでは行を一意に識別することはできないから、「一意性の保証」と「行の一意な識別」は別のものである、と考えられる。

主キーとは何ぞ? というと、やはり「行を一意に識別するためのもの」であり「リレーションのためにあるもの」ではない。
だが、リレーションを張るには行を一意に識別できなければならないので、リレーションを張れるのは主キーだけということになる。

> インデックス無しのチェック制約もやれないことはないと思いますが速度的なメリットを得るためインデックス化することになると思います。

でも中さんの実験では有意な速度差は見られなかったらしい。
ま、結果論であって、ユニークインデックスと非ユニークインデックスのどっちが速いかと言ったら、非ユニークインデックスである「はず」なのだけど。

で、
「主キーは検索に使われることが多い」
「頻繁に検索に使われる列にはインデックスを張っておいたほうがよい」
「どうせ張るならユニークインデックスのほうが速い」
「ユニークインデックスと主キーに求められる条件は同じ」
ならば、主キーを張るついでにユニークインデックスも張ってしまえ、ということになるのだろうか。

クラスター云々はわかりませんw(おい

> リレーションを維持しながらの変更はできない

なるほどわかりやすい。

# re: OJTでの知識は全能ではありません 2007/06/19 22:44 シャノン

> ユニークインデックスと非ユニークインデックスのどっちが速いかと言ったら、非ユニークインデックスである「はず」

逆ー!!

# re: OJTでの知識は全能ではありません 2007/06/20 1:10 黒龍

> 主キーは NULL を含まないが、候補キーは許容することについて。
そう。ここが結構ポイントです。主キーとはいくつも作れるユニークキーのうちでNotNULLな項目が、主キーの候補になるわけですね。
> 主キーとは何ぞ? というと、やはり「行を一意に識別するためのもの」であり「リレーションのためにあるもの」ではない。
> だが、リレーションを張るには行を一意に識別できなければならないので、リレーションを張れるのは主キーだけということになる。
リレーションというのを外部キー制約とするならユニークキーだけでOKです。(ご指摘のようにNULL値は値を持っていないという扱いなので対象外)
制約でない関連となるととり方なりプログラムなりで勝手にやる話なので。

インデックスは必要項目をサブセット化することによるアクセスサイズの低減がメリットだと思いますのでユニーク、非ユニークはあまり違いがないと思います。

SQLServerだとクラスター化は避けれない話題なんですが一度やっちゃうと後からどうにかできるもんでもないので語られるのは非クラスタ化が多かったり・・・。

タイトル  
名前  
Url
コメント