コンポジット一意インデックスとは？

質問

趣味でPHPとMYSQLをいじってる大学生です。
先日はこちらで助けて頂いてとても助かりました。
ご返答いただきました皆様ありがとうございました。

単発の質問で申し訳ないのですが
インデックスの指定をする際に疑問点がでてきたので
質問させて下さい。

タイトルにもあげたのですが、
コンポジット一意インデックスというのは

インデックス(インデックスの名前はkeyの値)
で複数カラムにインデックス指定するということだと思いますが

一意はユニークというのは
任意の挿入されるレコードは、２つの場合に限定すると
２のカラムを見ると他のレコードとかぶらないというか
要するに２つのフィールドをあわせて考えて、
ユニークであるという理解でよいでしょうか？

言葉がおもいつきませんが
例えば宝くじの
　組　　番号　購入者　...etc
　A組　0001
B組 0001
A組 0023
C組　・・・

のようなデータを扱う際に
組と番号にコンポジット一意インデックスを割り振るといいというという理解でいいでしょうか？

その理解が正しいか間違っているか？
教えていただけると幸いです。

そして、この理解で正しいのならば
もしも　番号=0002 など、２つのフィールドのうち１つで検索した場合だとインデックスは役割を果たすのでしょうか？

コンポじゃないと機能しないのか？ということです。
コンポじゃないと機能しないのであれば３つのインデックス
つまり、（組,番号[コンポ]),(組),(番号)
を作成するのが正しいのでしょうか？

よろしくお願い致します。

回答No.2

chukenkenkou

検索条件の指定順は、主要なRDBMSでは意識する必要はありません。

>select ～ from 表名 where 番号='0002' and 組='A'
>というwhere句の順序を変えたケースもインデックスが
>有効利用できないという理解でよいでしょうか？

といった検索条件を指定した場合、（組、番号）で構成されるインデクスがあれば、利用してくれます。

SQLの記述から、アクセス計画（パス）を作成するRDBMSの機構は、「オプティマイザ」と呼ばれます。オプティマイザがアクセス計画を作る方法は、大きく分けてルール（構文）ベースとコストベースの二種類があります。

ルールベースは、条件式の種類で、有効利用できるインデクスを選択します。
例えば、「c1=値1 and c2=値2 and c3 between 値3 and 値4」という検索条件を指定したとします。
c1、c2、c3にそれぞれ単一列で構成されるインデクスがあった場合、RDBMSは、between条件より、=条件の方が、「より絞込みができる」と判断します。
c1とc2に関しては、uniqueなインデクスか非uniqueかで優先順位が違ってきますが、両方がunique、あるいは両方が非uniqueであった場合、「先に条件式を指定した方」、「インデクス定義が古い方」、「両方」といったインデクスが選択され、これは各RDBMSのオプティマイザの内部仕様によって違ってきます。
また、複数列インデクスがある場合は、先頭列からより多くの列をインデクスで活用できる方を優先するといった制御をします。

一方、コストベースは、事前に各キー列の重複度合い、値の種類を取得しておき、その情報を元にアクセス計画を作ります。
例えば、先に挙げた「c1=値1 and c2=値2 and c3 between 値3 and 値4」という検索条件で、c1とc2は重複禁止でなかった場合、c3は範囲条件ですが重複が少なく絞込み効果が高いということもあり得ます。コストベースでは、こういったケースで、c3のインデクスを優先して使ってくれます。
その一方で、コスト情報の収集に時間が掛かったり、追加・更新・削除が多く、コスト情報取得時点と、実際に検索する時点でデータベースの状態が違えば違うほど、最適なアクセス計画は生成されなくなるという留意事項もあります。

お礼

ありがとうございます。
RDBMSのつっこんだお話をしていただきありがとうございます。

オプティマイザというのが
最適なアクセス計画を作成してくれていたんですね・・・
おかげさまでRDBMSの検索についても勉強できました。
WHERE句の順序については、オプティマイザという仕組みで最適な（と思われる）インデックスが選ばれるということで安心しました。

データベースの専門の技術者もいるくらいですし、詳細なところまで勉強していくときわめて奥が深そうだということがよく分かりました。
改めてRDBMSのすごさも実感しました。

大変勉強になりました。
ありがとうございました＾＾

回答No.1

chukenkenkou

>組と番号にコンポジット一意インデックスを割り振るといいというという理解でいいでしょうか？

yes

>もしも　番号=0002 など、２つのフィールドのうち１つで検索した場合だとインデックスは役割を果たすのでしょうか？

インデクスを構成する先頭列に対し、インデクスを有効利用できる操作を行っている必要があります。なお、インデクスは、検索条件による絞込みだけでなく、order by、group by、distinct、集合（集計）関数などでも活用されます。

＜有効利用できるケース例＞
(1)先頭列でインデクスを利用可能な検索条件
select ～ from 表名 where 組='A'

(2)先頭列を含むインデクスを利用できる条件
select ～ from 表名 where 組='A' and 番号 between '0001' and '0010'

(3)先頭列からインデクス構成列と同じ順にソート
select ～ from 表名 order by 組
select ～ from 表名 order by 組,番号

(3)先頭列で条件検索した上で、2番目の構成列の集合（集計）関数
select max(番号) from 表名 where 組='A'

(4)先頭列でのグループ化
select 組,max(番号) from 表名 group by 組

＜有効利用できないケース例＞
(1)先頭列でインデクス有効利用の条件なし
select ～ from 表名 where 番号='0001'

(2)ソート指定がインデクス構成順と合っていない
select ～ from 表名 order by 番号,組

>コンポじゃないと機能しないのであれば３つのインデックス
>つまり、（組,番号[コンポ]),(組),(番号)
>を作成するのが正しいのでしょうか？

「組を条件に指定せず、番号だけを条件指定することがあるか？」によって違ってきます。
（組,番号)のインデクスがあれば、組だけでのインデクス有効利用、組＋番号でのインデクス有効利用が可能です。
もし、「番号」だけの検索もありえるなら、(番号)だけのインデクスを定義する必要が出て来ます。

お礼

前回に引き続き、分かりやすいご返答を頂き感謝しております。
つまり、コンポジットインデックスはカラムの順序も含めてインデックス化されるということと理解しました。

select ～ from 表名 order by 組,番号
select ～ from 表名 where 組='A' and 番号='0002'

はコンポジットが有効利用できるケースであり

select ～ from 表名 order by 番号,組

は有効利用できないと教えていただいたのですが
ということは

select ～ from 表名 where 番号='0002' and 組='A'

というwhere句の順序を変えたケースもインデックスが
有効利用できないという理解でよいでしょうか？

それともこのケースはORDER句とすこし並び替えの順序がはいっていないのでSQL側でちゃんと解釈（理解）してくれるのでしょうか？

そこだけ気になりました。

組、番号の順で作成すれば組のインデックスは別途作成しなくても
コンポジットが有効利用できるということ・・・
番号だけで検索する要求があるなら、番号のインデックスを作成したほうがいいということで理解できました。

ありがとうございます＾＾