MySQLの複合主キーを持つテーブルに対するクエリにin句を利用するときに注意すべきこと

こんにちは、以前は朝ごはんは食べずに、昼と夜を食べていたのですが、最近は朝ごはんを食べて、昼休みはジムに行くか、ジョギングをしてプロテインだけで済ませて、夜ご飯を食べる暮らしに切り替えたら、体重がより減ってきて調子がいいです。

さて、先日仕事でハマった落とし穴についてブログにしてみます。結論としては複合主キーにin句を使うときはちょっと工夫しないとパフォーマンスの問題が起こる場合があります。なお、この記事で扱ったときに利用したMySQLのバージョンは5.6です。(手元のMacOSに入れていたバージョンで深い意味はありません。RDSの延長ありがとうございます。)

テーブル定義

CREATE TABLE `joke` (
  `id` bigint(20) unsigned NOT NULL,
  `sub_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`, `sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

id と sub_id からなる複合主キーを持つテーブルを作成します。テストデータは下記のようにぺろっと2万件ほど作成します。

seq 1 20000 | xargs -P10 -I{} mysql test -e "insert into joke (id, sub_id) values(1, {});"

事象の再現

さて、では準備したテーブルにSelectのExplainを打ってみます。

explain select
	*
from
	joke
where
	(id, sub_id) in ((1, 1))

結果はこちらです。

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	joke	const	PRIMARY	PRIMARY	16	const,const	1	Using index

主キーが検索に利用され、rows が 1となり、一意にレコードが選択されています。

次に2行選択してみます。

explain select
	*
from
	joke
where
	(id, sub_id) in ((1, 1), (1, 2))

結果はこちらです。

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	joke	index	NULL	PRIMARY	16	NULL	19813	Using where; Using index

おや、先ほどと違い rows が 19813 とほぼ全件検索なファンタスティックな結果になってしまいました。これだとデータ量が多くなってしまうと、主キー検索のメリットが全く活かせなくなってしまいます。これを解消するには下記のようなクエリにする必要があります。

explain select
	*
from
	joke
where
	id = 1
	and
	sub_id in(1, 2)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	joke	range	PRIMARY	PRIMARY	16	NULL	2	Using where; Using index

このように複合主キーの片方のカラム(idカラム)はin句から外に出してあげて、検索すると想定通り2行を無駄なく選択することができます。

最後に

in句に複合主キー指定してるから、普通に速いっしょと高を括っていたらと意外な落とし穴に落ちてしまいました。昨今あまりはまらないケースにも思いますが、同じ穴の前のいる誰かの参考になると嬉しいです。

テーブル定義

事象の再現

最後に

共有:

いいね: