'PRIMARY KEY'と'UNIQUE()'の違いを教えて！

CREATE TABLE等で出てくる'PRIMARY KEY'と'UNIQUE()'の違いが分かりません。
どちらも他の行と同じ値を登録させないためのものと認識しています。
どのように使い分けるのでしょうか。

あとINDEX()について教えてください。
これは挿入や更新のたびに、INDEXにした列の値の順に整列され、探索が高速になるものと認識しています。
これは'PRIMARY KEY'や'UNIQUE()'の列に対してもやらないといけないのですか。
テーブルは'PRIMARY KEY'や'UNIQUE()'に設定した列の値を基に随時整列されていると思っていたのですが・・・。

あと複数の列へINDEX()を設定することはできるのでしょうか。
その場合、どの列の値が優先的に整列されるのでしょうか。
複数列へINDEX()を設定するCREATE TABLEの書き方を、よろしければ教えてください。

No.4 回答者： chukenkenkou 回答日時： 2009/05/16 17:54

追記です。

>CREATE TABLE test4(a INT, b INT, c INT, INDEX(a), INDEX(b, c));
>例えばこのようなテーブルを作成した場合、テーブル全体がどのように整列されるのか

テーブルの行データ自体は、どのような格納位置になるかは、RDBMSにより保証されません。検索時に、order by指定がなければ、検索時の並びも保証されません。
一方、a列単独と、(b,c)列には、それぞれ重複可のインデクスが定義されているので、それぞれのインデクス上では、キー値が整列された形で管理されています。

a単独のインデクスは、例えば次のような検索で、利用されます。
select * from test4 where a=100
select * from test4 where a between 101 and 200
select * from test4 where a between 101 and 200 order by a

(b,c)のインデクスは、例えば次のような検索で、利用されます。
select * from test4 where b=100
select * from test4 where b=100 and c between 1 and 10
select * from test4 where b=100 order by b,c

なお、母体データ件数が少ない場合や、重複値が多量に発生している場合は、RDBMS側が行データから探した方が早いと判断され、インデクスを使わない場合もあります。
この辺は、EXPLAINなどで確認しながら、チューニングすることになります。

この回答へのお礼

ORDER BYがないときは主キーの順に整列されると思っていました！！！
これからはORDER BYを使う癖をつけないとダメですね！
それぞれのインデクスがどのような場面で利用されるかも理解できました！
最後の興味深い話題については、時と場合によっては、インデクスを使用せずに検索されるということですね！
しかし、まあ、そんな事は気にせず、検索条件でよく使う列についてはINDEXを設定するのが吉だと思いました！

お礼日時：2009/05/17 02:36

No.3 ベストアンサー 回答者： chukenkenkou 回答日時： 2009/05/16 17:37

>自分で調べるのと、人に聞くのと、どっちが早いかは、人によって違うと思います。

それは私が判断します。

ここは、「自分なりに調べて、分からない部分を具体的に示して質問する」ことがルールになっています。
他人に調べてもらいたいなら、「はてな」の人力検索などを利用してみては？

>しかしINDEXについては色んな列に個別に設定できるのでまだ意味不明です
>CREATE TABLE test4(a INT, b INT, c INT, INDEX(a), INDEX(b, c));
>例えばこのようなテーブルを作成した場合、テーブル全体がどのように整列されるのか

テーブル自体は、整列なんてされませんよ？インデクス上で、キー値が整列されるだけです。
RDBMSによっては、primary keyにクラスタリングといった機能を持たせたりして、なるべくその近傍にデータを格納するといった機能を実装しているものもありますけど。

例えば、学生なり、社員などを管理するテーブルを考えてみましょう。
学生idや社員idは、一人に1個、その人を識別するためのものだから、primary keyにするでしょう。
また、それ以外に各人で一意にしたい情報があれば、uniqueを定義することになります。
また、氏名などは同姓同名がいる可能性があるので、もしそういったインデクスを定義する場合は、create table文のindex句、あるいはcreate index文で定義することになります。

なお、インデクスは、検索条件によるデータの絞込み、order by、group by、distinctなどで必要になるソートで「作業メモリやファイルを使用したソート抑止」などで活用できると、大きな性能向上ができます。その一方で、追加、削除、キー値の更新などでは、性能劣化になるため、無闇にインデクスを作成することは避ける必要があります。

次のリンクは、インデクスの構造、どういう操作で有効かについて、Oracleを例にした記事です。
http://itpro.nikkeibp.co.jp/article/COLUMN/20060 …

次のリンクは、MySQLでのインデクスの有効利用方法などについて、マニュアル記載箇所です。
http://dev.mysql.com/doc/refman/5.1/ja/query-spe …

参考URL：http://dev.mysql.com/doc/refman/5.1/ja/optimizat …

この回答へのお礼

サイトのルールをざっと見直したけどそんなルールは見あたりませんでしたよ。
多分そんな面倒臭いルールにはなってないと思いますよ・・・。
だって、日常の、ふと気になったこととかでも質問できますもん。
ちなみに、質問者の過ちをいろいろ指摘して傷つけることは禁止されてました
＼(^o^)／

まあ文句を言うのはここまでにして、
chukenkenkouさん、再返信ありがとうございます。
テーブル自体は、整列なんてされないとのことですが、これについては
No.2のchukenkenkouさんの回答文を読んだ時点でなんとなく話がかみ合わない気はしておりました。。。
とにかくINDEXを設定した列をWHEREやORDER BYで使えば早くなるということは理解できました。
逆に追加、削除などを行うと性能劣化につながることもなんとなく理解できました。
あとはテストを重ねて、どっちを取るかですね！

ご紹介いただいたリンクについては・・・
ごめんなさい・・・また集中力が上がったときに・・・

お礼日時：2009/05/17 02:19

No.2 回答者： chukenkenkou 回答日時： 2009/05/15 22:51

すぐに自分でも、調べられる内容だと思うのですが？

>CREATE TABLE等で出てくる'PRIMARY KEY'と'UNIQUE()'の違い

primary keyは、表に0～1個しか定義できず、null値は許されません。
uniqueは、表に0個以上で複数定義でき、nullを許すかどうかはnot null指定をするかどうかにより異なります。

どちらも重複チェックを効率的に行うため、主要なRDBMSでは内部的にインデクスが作成されます。

unique指定時、「null値は、重複値とは扱われない」というのが主流なのですが、SQL Serverでは、重複値と扱うといった例もあります。

また、参照制約（外部キー制約）を定義する場合、被参照表（親）にはprimary keyを定義し、参照表（子）にはforeign keyを定義します。しかし、primary keyがなくても、uniqueでnot nullの定義があれば、それで代用してくれるといった形でRDBMSで実装されていたります。

>あとINDEX()について教えてください。

重複可能なインデクスであり、create index文と意味合いとしては同じであり、create table文内で行うか、create index文内で行うかといった違いがあります。

>これは挿入や更新のたびに、INDEXにした列の値の順に整列され、探索が高速になるものと認識

何を意味しているのか分かりませんが、インデクス定義時にインデクス情報が主にB-TREE構造で作成されます。追加、更新、削除などはB-TREE構造を維持した状態で行われます。そのため、インデクスを有効利用した検索での性能は一定に保たれます。一方で、キーの追加に備えた空き領域があるため、中間キー値や同じキー値の削除＆追加といった操作が殆どないような場合では、その領域はずっと使用されないことになります。また、キー値が頻繁に更新されるような場合も、無駄な領域が多くなります。
そのため、定期的にインデクスを再作成する必要があります。

この回答へのお礼

chukenkenkouさん、ご回答ありがとうございます。
お2人のご回答を参考にしつつ自分なりに調査し、色々知ることができました。
今の私の認識はNo.1のtest001さんのお礼欄へ記載したとおりです。
INDEXについてはもうちょっと情報を収集していく必要があります。

しかしそんなことよりも、あなたに特別に言いたいのは、

>すぐに自分でも、調べられる内容だと思うのですが？

このような発言はちょっと馬鹿にされた感じがして、傷つくのでやめてほしいということです。
自分で調べるのと、人に聞くのと、どっちが早いかは、人によって違うと思います。
それは私が判断します。

お礼日時：2009/05/16 13:20

No.1 回答者： test001 回答日時： 2009/05/15 21:47

マニュアルが日本語訳されているので参照してください。

CREATE TABLE 構文
http://dev.mysql.com/doc/refman/5.1/ja/create-ta …

以下、引用
************************************************************
PRIMARY KEY は、全てのキー カラムが NOT NULL として定義されなければいけないユニーク インデックスです。もしそれらが NOT NULL として明示的に宣言されなければ、MySQL はそれらを暗示的に(そして静かに)宣言します。1つのテーブルは1つの PRIMARY KEY しか持つ事ができません。もし PRIMARY KEY が無いのにアプリケーションがテーブル内で PRIMARY KEY を要求したら、MySQL は PRIMARY KEY として NULL カラムを持たない最初の UNIQUE インデックスを返します。
************************************************************

INDEX の作成は CREATE INDEX 文です。

CREATE INDEX 構文
http://dev.mysql.com/doc/refman/5.1/ja/create-in …

複数列への INDEX も作成可能です。
複合INDEXについて調べてみてください。
カラム指定の順番がキモです。
http://dev.mysql.com/doc/refman/5.1/ja/mysql-ind …

参考URL：http://dev.mysql.com/doc/refman/5.1/ja/create-table.html,http://dev.mysql.com/doc/refman/5.1/ja/mysql-ind …

この回答へのお礼

test001さん、ご回答ありがとうございます。
PRIMARY KEYにすればNOT NULLとしなくても自動的にNOT NULLになるのですね！

あと実験よりPRIMARY KEYやUNIQUEは自動的にインデックスになることを知りました。
ですので次の3つのCREATE文はどれもインデックスがつきます。

CREATE TABLE test1(a INT, b INT, c INT, PRIMARY KEY(a, b, c));
CREATE TABLE test2(a INT, b INT, c INT, UNIQUE(a, b, c));
CREATE TABLE test3(a INT, b INT, c INT, INDEX(a, b, c));

この例ではtest1、2、3の順で制御が厳しく、test3については特に制御はなく、どんな値でも入る
test1とtest2の違いはNULLがOKかどうか
さらにUNIQUEは1テーブルでいくつも定義できる
といったところでしょうか

しかしINDEXについては色んな列に個別に設定できるのでまだ意味不明です

CREATE TABLE test4(a INT, b INT, c INT, INDEX(a), INDEX(b, c));
例えばこのようなテーブルを作成した場合、テーブル全体がどのように整列されるのか、ちんぷんかんです・・・

お礼日時：2009/05/16 13:02