テーブルの検索について（カラムA,B）という方向も（カラムB,A）という方向もあるときの、主キーとインデックスの設定方法

Question

###質問事項
以下のように「曲とアーティスト」を示すテーブルがあります。

検索の方向として「あるアーティストの曲」だったり「ある曲のアーティスト」だったりという方向があるのですが、そのときの主キーおよびインデックスの設定方法について、次の案のどれが適当か知りたいです。

artist_ID	music_ID
2	20
2	30
3	10
1	80

###案
主キーを( music_ID, artist_ID )とし、逆にインデックスを( artist_ID, music_ID )の順序にするべきか…

$query = "CREATE TABLE $table (
	music_ID INT(10),
	artist_ID INT(10),
	PRIMARY KEY ( music_ID, artist_ID ),
	CONSTRAINT fk_" . $table . "_1 FOREIGN KEY (music_ID) REFERENCES music(ID),
	CONSTRAINT fk_" . $table . "_2 FOREIGN KEY (artist_ID) REFERENCES artists(ID),
	INDEX id_" . $table . "_1 ( artist_ID, music_ID )
);";

上とは逆に主キーを( artist_ID, music_ID )とし、インデックスを( music_ID, artist_ID )の順序にするべきか…

$query = "CREATE TABLE $table (
	music_ID INT(10),
	artist_ID INT(10),
	PRIMARY KEY ( artist_ID, music_ID ),
	CONSTRAINT fk_" . $table . "_1 FOREIGN KEY (music_ID) REFERENCES music(ID),
	CONSTRAINT fk_" . $table . "_2 FOREIGN KEY (artist_ID) REFERENCES artists(ID),
	INDEX id_" . $table . "_1 ( music_ID, artist_ID )
);";

はたまた、外部キー制約（によるインデックス）があるのだから、複合インデックスをなくして下のようにすべきか…

$query = "CREATE TABLE $table (
	music_ID INT(10),
	artist_ID INT(10),
	PRIMARY KEY ( artist_ID, music_ID ),
	CONSTRAINT fk_" . $table . "_1 FOREIGN KEY (music_ID) REFERENCES music(ID),
	CONSTRAINT fk_" . $table . "_2 FOREIGN KEY (artist_ID) REFERENCES artists(ID)
);";

ないしはもっと何かいい設計はないかと思案しているところでございます。

以上のようにいずれの方向からの検索もありえるとき、主キーとインデックスの設定はどうするのが適当でしょうか。

※ MariaDB 10.3です ※

Answer 1

そもそも、そのテーブル必要ですか？

musicにartists_idが外部キーとして設定されれば良いだけだと思います。

追記

複数のartistsによる合作のケースがあるので、その場合も含めるとテーブルは必要ですね。
そうなると(artist, music)の組合せで一意となります。

こういった場合の一意キーの並びは、カーディナリティの低い方から設定します。
artistでの検索ではこの一意キーが使用されます。

musicでの検索用には、データの分布が違うので(music, artist)のインデックスが別途必要です。

Comments

[nazo]

ご高察ありがとうございます。

＞(artist, music)の組合せ
にすべきという根拠としてカーディナリティというものを挙げられていらっしゃいますがお恥ずかしながら理解できませんで…もしよろしければ教えて頂けませんでしょうか。

1.
カーディナリティという言葉を調べましたところ「カラムとレコード割合」からくるもののようですので、「artistよりmusicの方がレコードが多いこと」を、「artistの方がmusicよりカーディナリティが低い」と呼び、だから(artist, music)の組合せにすべきということになるのでしょうか？

2.
そうであれば、なぜカーディナリティが低いartistを主キーの先頭に置くべきだということになるのでしょうか？どのような効果があるのですか？それとも慣習的な選択ですか？

誠に恐れ入りますが以上2点についてどうぞ宜しくお願い申し上げます。

[sazi]

「一意キーの並びは、カーディナリティの低い方から設定」は正しい表現ではありませんでした。
正しくは「インデックスの並びは、カーディナリティの低い方から設定」で、それを一意キーとするから結果的に、という事です。

では、「インデックスの並びは、カーディナリティの低い方から設定」については、前提として検索に使用されるからです。
マルチカラムインデックスの場合、例えるなら分類表(第一分類、第二分類）をイメージして下さい。
（第二分類、第一分類）の並びのインデックスとどちらが効率的であるかは自明です。
尤も、それは絞り込む方向に対してという前提です。
musictを見つけるならartist→musicですし、artisitを見つけるならmusic→artistです。

[nazo]

理解できました。丁寧なご返信をどうもありがとうございました！

Answer 2

artist_ID, music_ID で検索されるなら PRIMARY KEY が使われます。
music_ID, artist_ID でも検索されるなら INDEX があった方が検索が早いです。
実行計画を確認すると良いでしょう。
Oracle 統計情報の収集
Oracle EXPLAIN PLAN を使って実行計画を取得する

実行計画の確認方法やSQLの記述方法はデータベースによって異なりますから、どのデータベースを使うのかを質問のタグで示したり、バージョンも明記した方が適切なコメントが付き易いです。

100万件くらいデータを用意してSELECTしてみると実感できるかと。

Comments

[nazo]

ありがとうございます。順番は悩みますね。昨夜100万件のINSERTを実行し、いま起きたらサーバー止まっていました（笑）