趣味でPHPとMYSQLをいじってる大学生です。
先日はこちらで助けて頂いてとても助かりました。
ご返答いただきました皆様ありがとうございました。
単発の質問で申し訳ないのですが
インデックスの指定をする際に疑問点がでてきたので
質問させて下さい。
タイトルにもあげたのですが、
コンポジット一意インデックスというのは
インデックス(インデックスの名前はkeyの値)
で複数カラムにインデックス指定するということだと思いますが
一意はユニークというのは
任意の挿入されるレコードは、2つの場合に限定すると
2のカラムを見ると他のレコードとかぶらないというか
要するに2つのフィールドをあわせて考えて、
ユニークであるという理解でよいでしょうか?
言葉がおもいつきませんが
例えば宝くじの
組 番号 購入者 ...etc
A組 0001
B組 0001
A組 0023
C組 ・・・
のようなデータを扱う際に
組と番号にコンポジット一意インデックスを割り振るといいというという理解でいいでしょうか?
その理解が正しいか間違っているか?
教えていただけると幸いです。
そして、この理解で正しいのならば
もしも 番号=0002 など、2つのフィールドのうち1つで検索した場合だとインデックスは役割を果たすのでしょうか?
コンポじゃないと機能しないのか?ということです。
コンポじゃないと機能しないのであれば3つのインデックス
つまり、(組,番号[コンポ]),(組),(番号)
を作成するのが正しいのでしょうか?
よろしくお願い致します。
A 回答 (2件)
- 最新から表示
- 回答順に表示
No.2
- 回答日時:
検索条件の指定順は、主要な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
- 回答日時:
>組と番号にコンポジット一意インデックスを割り振るといいというという理解でいいでしょうか?
yes
>もしも 番号=0002 など、2つのフィールドのうち1つで検索した場合だとインデックスは役割を果たすのでしょうか?
インデクスを構成する先頭列に対し、インデクスを有効利用できる操作を行っている必要があります。なお、インデクスは、検索条件による絞込みだけでなく、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 番号,組
>コンポじゃないと機能しないのであれば3つのインデックス
>つまり、(組,番号[コンポ]),(組),(番号)
>を作成するのが正しいのでしょうか?
「組を条件に指定せず、番号だけを条件指定することがあるか?」によって違ってきます。
(組,番号)のインデクスがあれば、組だけでのインデクス有効利用、組+番号でのインデクス有効利用が可能です。
もし、「番号」だけの検索もありえるなら、(番号)だけのインデクスを定義する必要が出て来ます。
前回に引き続き、分かりやすいご返答を頂き感謝しております。
つまり、コンポジットインデックスはカラムの順序も含めてインデックス化されるということと理解しました。
select ~ from 表名 order by 組,番号
select ~ from 表名 where 組='A' and 番号='0002'
はコンポジットが有効利用できるケースであり
select ~ from 表名 order by 番号,組
は有効利用できないと教えていただいたのですが
ということは
select ~ from 表名 where 番号='0002' and 組='A'
というwhere句の順序を変えたケースもインデックスが
有効利用できないという理解でよいでしょうか?
それともこのケースはORDER句とすこし並び替えの順序がはいっていないのでSQL側でちゃんと解釈(理解)してくれるのでしょうか?
そこだけ気になりました。
組、番号の順で作成すれば組のインデックスは別途作成しなくても
コンポジットが有効利用できるということ・・・
番号だけで検索する要求があるなら、番号のインデックスを作成したほうがいいということで理解できました。
ありがとうございます^^
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 外国株 インデックス投資は、実は罠がある、バンガード社が、ウオール街の ランダムウオーカーとか、宣伝活動した 2 2023/01/06 22:08
- SEO 検索エンジンにインデックスされない 3 2023/02/20 08:59
- 不動産投資・投資信託 投資信託を初めて購入しようと思っています。S&P500の下記3つのうちどれがおすすめでしょうか。 6 2023/07/28 16:00
- 外国株 お金を守るための3カ条 1. 安く買って、高く売る 2. 商品はまだ割安 3. 初心者はインデックス 1 2023/01/06 23:12
- その他(データベース) 更新クエリをリンクデータベーステーブルに実行し実行時エラー3362固有インデックスに重複する値が含ま 1 2022/09/21 11:44
- C言語・C++・C# C言語初心者 構造体 課題について 2 2023/03/10 19:48
- 不動産投資・投資信託 インデックス投資信託の信託報酬が低い物へのスイッチについて 5 2022/08/17 18:09
- SEO Googleで会社名を入れて検索するとホームページが表示されるようにしたい 4 2022/09/06 23:36
- 不動産投資・投資信託 eMAXIS Slim S&P500で、インデックスが下がったのに基準価額が上がる、又はその逆の挙動 1 2023/01/25 17:58
- 不動産投資・投資信託 投資信託のついて。 この画像は積み立て NISA 口座を開いてる事になりますよね? 以前、以下の質問 5 2022/11/11 10:08
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
DBの定義のサイズを大きくし過...
-
mySQL プライマリーキーのカラ...
-
インデックスキーを設定するとK...
-
一部のカラムでdistinctし全て...
-
BULK INSERT時のNull許容について
-
GREATESTで NULLをスルーする方...
-
SELECT文で、指定カラム以外の...
-
AUTO_INCREMENTに0はダメ?
-
group byで最後のレコードを抽...
-
MySQLで先頭にカラムを追加
-
SQLの書き方
-
LIKEの右側にカラムを指定でき...
-
INDIRECT関数の代替方法は?
-
SQLでカラムを追加し、条件に合...
-
WHERE `年月日` = '晴' OR `年...
-
MySQL AUTO_INCREMENTが最大に...
-
ユニーク制約とユニークインデ...
-
mysqlで50音順にorder byしたい。
-
SQLServerでNULLを挿入したいです
-
複数カラムに対するLIKE文の最適化
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
DBの定義のサイズを大きくし過...
-
mySQL プライマリーキーのカラ...
-
(phpMyadminの)インデックスサ...
-
属性?について
-
SQL、oracleにて文字列操作(連...
-
'PRIMARY KEY'と'UNIQUE()'の違...
-
PHPのforeachでSQL文を処理して...
-
インデックスとは?オートナン...
-
インデックスの一意な値の数に...
-
パフォーマンスとIN句とAND、実...
-
インデックスキーを設定するとK...
-
データベースのインデックスに...
-
コンポジット一意インデックス...
-
mysqlのインデックス
-
INDIRECT関数の代替方法は?
-
SELECT文で、指定カラム以外の...
-
一部のカラムでdistinctし全て...
-
LIKEの右側にカラムを指定でき...
-
UNIONする際、片方テーブルしか...
-
GREATESTで NULLをスルーする方...
おすすめ情報