HOME»データベーススペシャリスト掲示板»H16 午後1 問4 複合インデックスについて
投稿する

H16 午後1 問4 複合インデックスについて [0757]

 まんぼうさん(No.1) 
SELECT  *  FROM  会員テーブル
WHERE  業種コード  =  'G1'  AND  年収ランク  =  'A'
AND  ポイント数合計  >  900000
ORDER  BY  地域コード

上記SQLに下記索引4・5を利用したときの索引リーフページの物理入出力回数を問われています。

索引4  :  ポイント数合計、業種コード
索引5  :  業種コード、ポイント数合計

諸条件は以下
・「業種コード  =  'G1'」の絞り込み率は5%
・「ポイント数合計  >  900000」の絞り込み率は1%
なお、ポイント数合計は特定の値に集中せず、均等に出現する
・各索引の索引リーフページ数はすべて50000ページ

回答・解説はおおむね以下の通りでした。
・索引4  :  50000  *  0.01  =  500回
→索引4はポイント数合計で1%にまで絞れるが、ポイント数合計は均等に出現するため業種コードをG1と仮定しても500ページすべて読む必要がある。

・索引5  :  50000  *  0.05  *  0.01  =  25回
→索引5は業種コードで絞ったのち、索引右側のポイント数合計は昇順に並んでいるためさらにその分絞ることが可能

ここで、索引4で業種コードでの絞りができないことが理解できません。
私のイメージではインデックスは指定列でソートされているようなイメージを持っており、
解説の中の「ポイント数合計が均等に出現する」ことがインデックスに影響がある意味が分かりません(ばらばらだからこそインデックスを定義してソートした状態にしておく意義がある)。

どなたか、初学者の私に上記のような計算の相違が生じる理由をご教示いただけませんでしょうか。
なお、過去同様の質問があったことを確認しましたが、内容を確認しても理解が追いつきませんでした。
2024.09.18 00:21
 まんぼうさん(No.2) 
この投稿は投稿者により削除されました。(2024.09.18 00:31)
2024.09.18 00:31
 まんぼうさん(No.3) 
同様の質問のリンクは以下です。
https://www.db-siken.com/bbs/0489.html
2024.09.18 00:32
Ayaさん(No.4) 
私も全く同じ疑問を抱いていたのですが、「業種コード」は文字列のため、ORDER BYで並び替え不可なだけだという結論に至りました。

一方で、「ポイント数合計」はInteger型で並び替え可能なので、そこの差ですね。
2024.09.21 23:24
 まんぼうさん(No.5) 
Ayaさん
ご回答ありがとうございます!
同じ疑問を抱く方がいらっしゃって少し安心しました。

>「業種コード」は文字列のため、ORDER BYで並び替え不可

上記についてですが、索引5は業者コードでのインデックスが効いているので、
索引4のように指定列2つめ以後に文字列が指定された場合のみインデックスが効かなくなるということなのでしょうか。
2024.09.22 00:21
Ayaさん(No.6) 
まんぼうさん

まずは申し訳ないです。初めの私の回答は的外れだったことに気づきまして、訂正させてください。

ですので、まんぼうさんの更問に対する回答と少しそれますが、問題文の表1の注1に記載の通り、ポイント数は特定の値に集中しない、かつレンジが0~99,999,999というかなり幅広い値を取ることから、今回のポイント数を複合インデックスの1列目の属性に充てるのは効果が出ないってのが私の改めての回答です。
一方で、業種コードは値の種類がそれほど多くないので、効果が出ます。


実際に表を描いてみるとイメージ湧きやすいと思いますので、以下は例になります。

<索引4で絞った場合>
ポイント数合計      業種コード
100000        G1
99000          G3
98000          G2
97000          G3
95000          G1
93000          G1
92000          G2
90000          G1

<索引5で絞った場合>
業種コード          ポイント数合計
G1                100000
G1                95000
G1                93000
G1                90000
G2                98000
G2                92000
G3                99000
G3                97000

索引4を見て貰えれば分かる通り、業種コードはソートされていない(正確にはソートされているのだが、ポイント数のレンジが広いかつ実質ユニークになっているため、ソートされているように見えない)ので、業種コードは実質全検索が求められることが分かると思います。

よって、私の結論としては、複合インデックスを使用する場合、1つ目に指定する属性は値の重複が多いものにすべき、です。
2024.09.22 02:17
 まんぼうさん(No.7) 
Ayaさん

Ayaさんのご説明をふまえながら、自分の考えも織り交ぜ以下のように解釈しました。

・索引4
→ ポイント数合計が90,000 > 0のものは全体の1%であり、
ポイント数合計が90,000 > 0の索引リーフページは 50,000 * 0.01 = 500ページである。
また、ポイント数合計は900,000〜100,000,000(≒99,999,999)の間で均等に出現することから
索引リーフページ1ページあたりのポイント数の幅は
(100,000,000−900,000) / 500 = 198,200 ≒ 200,000となる。
(例えば、ある索引リーフページは、ポイント数合計が900,000 〜 1,100,000の幅を持つ。)
そのページ内でG1は散らばって存在するため、この全リーフページを読む必要がある。
(上の例で言うと、(ポイント数合計, 業者コード) が(900,010 , G1)と(900,110, G3)が同一ページにある。
業種コードでの縛りはできていない)
そのため、G1の絞り込み率は索引リーフページの入出力回数に影響を与えない。

•索引5
→ G1の絞り込み率が5%のため、G1のリーフページは50,000 * 0.05 = 2,500ページとなる。
またその中でポイント数合計はソートされているので、2,500 * 0.01 = 25ページとなる。


> よって、私の結論としては、複合インデックスを使用する場合、1つ目に指定する属性は値の重複が多いものにすべき、です。

私もこの問題の回答を見た時そのように考えましたが、検索してみると逆にカーディナリティが高い列を先に指定すべきだという記載もあり…。ただ今回の試験対策に限っては解答のとおりカーディナリティが低いものを先に指定した方が絞れると認識しておきます。

大変ご丁寧な説明ありがとうございました。
自分のなかでは腑に落ちたので、解決済みといたします。
ただ、なにか自分の書いてることで気になる点があればぜひご指摘いただければと思います。
2024.09.22 12:30
みかんさん(No.8) 
DB ブロンズマイスター
他所のホームページの図例(※この試験とは無関係)を参考にすると理解が深まります。「大なり、小なり、BETWEEN、インデックス」でWEB検索して下さい。
2024.09.22 13:31
 まんぼうさん(No.9) 
みかんさん

情報の提供ありがとうございます。
リスのイラストがヘッダにあるページでしょうか。
参考にいたします。
2024.09.22 14:13
返信投稿用フォームスパム防止のためにスレッド作成日から30日経過したスレッドへの投稿はできません。
© 2016-2024 データベーススペシャリストドットコム All Rights Reserved.

Pagetop