単体インデックスと複合インデックスの貼り方について

Question

# テーブル構造
user_item: {
	user_id:int(11),
	item_id:int(11),
	count:int(11),
}

上記のようなテーブル構造で、

• user_id 単体で検索することがある
• item_id 単体で検索することはほとんどない
• count 単体で検索することはない
• user_id と item_id で検索することがある
• レコードは user_id と item_id の組み合わせでユニークである
• idの種類の数は user_id > item_id

といった想定の運用をする場合、インデックスの貼り方としては

• A：user_id
• B：usre_id,item_id
• C：uniq(user_id,item_id)
• D：uniq(item_id,user_id)
• E：user_id, uniq(user_id,item_id)
• F：user_id, uniq(item_id,user_id)

　※uniq() はユニークな複合インデックスを表しています。

といった構成が予想されるかと思いますがどれが一番良いのでしょうか。

　
個人的な見解としては

• 速度を求めるなら「F」
• 堅牢性を求めるなら「C」
• DBの容量を考慮するなら「A」

かなと考えており、B、D、E、にはメリットはないのかな想定しています。

Answer 1

本質的にはprimaryをauto_incrementかなにかで1カラムつくっておくとよいでしょう
その上で「user_id と item_id の組み合わせでユニーク」のなので複合unique
をつけておけば基本的には問題ないです
ただしcountが集計しないのに浮いている感じがしますが

Answer 2

インデックスはカーディナリティの低いものから並べた方が、単独の項目の検索と兼用して使用されることになるので効率的です。

user_id > item_id

上記が集合的にという事なら、Dでしょう。但し、user_idでの検索の場合は必ず2項目での検索です。
別な集合で単なるパターン数という事なら、Fです。

但しuniqe制約があるならuniqeインデックスですけど、制約が不要なら検索性能には影響しませんので敢えて指定する必要はありません。

※不要なのに使用する場合には、追加・更新時には制約の為のオーバーヘッドが生じます。

Comments

[oikake]

＞カーディナリティの低いものから並べた方が、単独の項目の検索と兼用して使用されることになるので効率的
これは初耳でした。
そのようなのがあるのですね。。。
お教えくださりありがとうございます。

Answer 3

まず、「ユニークになる組み合わせにUNIQUEインデックスを貼らない」という選択肢は除外していいでしょう。

そして、MySQLの場合、UNIQUE(col_a, col_b)というインデックスはcol_aの検索インデックスとしても使えますので、Eはuser_idのインデックスが全くの無駄です。

Comments

[oikake]

>「ユニークになる組み合わせにUNIQUEインデックスを貼らない」という選択肢は除外していいでしょう。
やはり基本的にそのようなパターンは良くないのですね。。。

＞SQLの場合、UNIQUE(col_a, col_b)というインデックスはcol_aの検索インデックスとしても使えますので、Eはuser_idのインデックスが全くの無駄です
なるほど、やはりEの構成は意味がないのですね。このあたりあやふやな認識だったので確証が得られてよかったです。