SQLで複合主キーにすべきかどうか？

Question

###知りたいこと
記事テーブルpostsの作成にあたり、主キーを複合主キーにした方がいいのか悩んでいます。

状況の説明

削除済みであることを示すis_trashというカラムがあります。

ユーザーが削除済みの記事を取得することはありません。
つまり必ずWHEREにはis_trash=0と指定したSQLを発行します。
(「0」は「削除済みでない」の意味)

このとき、主キーは(ID)でなく(is_trash,ID)という複合主キーにした方がいいでしょうか？

###考えていること
仮に以下のようにIDのみで指定するとき（削除済みとそうでないものとをミックスして取得するとき）があるならば、複合主キーにすべきでないと考えています。

SELECT * FROM posts WHERE ID = 10

なぜなら、複合主キーは必ず最初の指定でインデックスがされているために、(is_trash,ID)という複合主キーにした場合、WHERE ID = 10 の取得でインデックスが利かなくなってしまうためです。

しかし上記した状況のように、削除済みのものは必ず取得せず、以下のようなWHEREになるのであれば、複合主キーにした方がいいだろうという考えです。

SELECT * FROM posts WHERE is_trash = 0 AND ID = 10

この考えはあっているでしょうか？

Comments

[m.ts10806]

id 1 is_trash 0
id 1 is_trash 1
というデータを許すんですか？

[St.]

コメントありがとうございます。

いいえ、許さないです。削除済と未削除は同じpostsテーブルに入っていて、IDは一意です。

ではなぜ(is_trash,ID)という複合主キーにした方が良さそうだと考えたのかといいますと、こうです。

この複合キーによって削除済のインデックスと未削除のインデックスという風に大きく分割されるので、未削除を取得するときその検索対象のインデックスが減ることになり、未削除を取得する速度向上に効果的かも？と考えました。

[St.]

ああ、そうか。複合主キーにしたとたん、同じIDについてis_trah0と1がありえますね。失礼いたしました。ご指摘ありがとうございます。複合主キーはやめておきます。

Answer 1

主キーは(ID)でなく(is_trash,ID)という複合主キーにした方がいいでしょうか？
複合主キーは必ず最初の指定でインデックスがされているために、(is_trash,ID)という複合主キーにした場合、WHERE ID = 10 の取得でインデックスが利かなくなってしまうためです。

主キーはそのままです。
後はインデックスを追加するかどうかですね。

それは、is_trash の(0:1)の比率がどの程度かに依ります
A.比率が大きい時　→　(is_trash, ID)が効果的。
IDが主キーである以上ランダムアクセスになるので、その回数を減らす事が目的
B.比率が少ない時 → Filter動作になるようなら(ID, is_trash)
ただし、インデックスのみスキャンを期待して、実際に使用されているインデックスに項目を追加するのが殆ど。
C.比率が極端に少ない時 → インデックスは不要。

概ね効率的には上記になると思います。
但し、想定するデータ件数のサンプルデータを準備して、実行計画により計測するのが確実です。

Comments

[St.]

なるほど、なるほど…どうもありがとうございます。考えたことの間違いだけでなく、目的に寄り添ったご回答に感謝いたします。現状ではBを想定しておりますので試してみたいと思います。
ベストアンサーは決定してしまいましたが、大変参考になりました。

Answer 2

いいえ、許さないです。

では、複合主キーは不適切です。一意になるidだけを主キーにするのが適切です。

経昨速度を上げたい場合、(is_trash,ID)のような複合インデックスを作るのはありですが、もう片方がid列であることを考えると、よほど特殊なパターンで検索しない限り効果は見込めないかと思います。

（というより、MySQLではレコードがID順に並ぶので、暗黙のうちにIDとの複合インデックスとなっているのに等しい状態です）

Comments

[maisumakun]

> SELECT * FROM posts WHERE is_trash = 0 AND ID = 10

このようなSQL文であれば、IDで列を一致に絞れますので、is_trashのインデックス云々を考える必要性がほぼありません。

[St.]

なるほどありがとうございます。勘違いしていました。

Answer 3

しかし上記した状況のように、削除済みのものは必ず取得せず、以下のようなWHEREになるのであれば、複合主キーにした方がいいだろうという考えです。
SELECT * FROM posts WHERE is_trash = 0 AND ID = 10
この考えはあっているでしょうか？

間違ってます。
is_trash=0とis_trash=1の混在を許すことになり、一意性が崩れます。

なぜなら、複合主キーは必ず最初の指定でインデックスがされているために、(is_trash,ID)という複合主キーにした場合、WHERE ID = 10 の取得でインデックスが利かなくなってしまうためです。

この理由であれば、別途インデックスを作成すればいいだけです。

「主キー」と「インデックス」は概念的に別物である事をまず意識しましょう。
主キー制約の副産物としてインデックスが付くからと行って、インデックスを効かせたいものを主キーに含める、という話にはなりません。それは目的と手段が逆転しています。

Comments

[St.]

なるほどご説明ありがとうございます。理解できました。複合主キーはやめておきます。