MySQL の複合主キーテーブルのレコードを何件かピックアップして主キー検索したい機会があったので、いくつかのパターンの SQL で検索してパフォーマンス比較をしてみました。

実験の内容

• それぞれ適当にピックアップした15レコードを1クエリで検索する
• 実行時間は同じクエリを5回実行したときの最小と最大の時間を計測
• レコードは適当に100,000件投入

実験環境

• Ubuntu 13.10 (on Vagrant)
• MySQL 5.5.37
• InnoDB

使用したテーブル

CREATE TABLE `myTable` (
  `player_id` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `body` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`player_id`,`id`)
) ENGINE=InnoDB;

各検索方法の実行時間とインデックス使用状況

1. OR検索の場合

SQL例

SELECT * FROM `myTable`
WHERE  (`player_id` = 50 AND `id` = 4856)
  OR (`player_id` = 61 AND `id` = 4274);

※実験時は15個のORを繋げて検索しましたが長くなるのでここでは省略しています。

実行時間

1.2ms ~ 1.5ms

EXPLAIN結果

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	myTable	range	PRIMARY	PRIMARY	8	NULL	15	Using where

2. UNION ALL検索の場合

SQL例

SELECT * FROM `myTable` WHERE (`player_id` = 50 AND `id` = 4856)
UNION ALL
SELECT * FROM `myTable` WHERE (`player_id` = 61 AND `id` = 4274);

実行時間

1.5ms ~ 2.0ms

EXPLAIN結果

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	myTable	const	PRIMARY	PRIMARY	8	const,const	1
2	UNION	myTable	const	PRIMARY	PRIMARY	8	const,const	1
…	…	…	…	…	…	…	…	…	…
15	UNION	myTable	const	PRIMARY	PRIMARY	8	const,const	1
NULL	UNION RESULT		ALL	NULL	NULL	NULL	NULL	NULL

3. WHERE IN の複合カラム指定による検索の場合

SQL例

SELECT * FROM `myTable`
WHERE (player_id, id) IN ((50, 4856),(61, 4274));

実行時間

40.9ms ~ 49.7ms

EXPLAIN結果

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	myTable	ALL	NULL	NULL	NULL	NULL	100714	Using where

コメント

最も高速なのは普通に OR で繋げる検索でした。range スキャンになるんですね。次点で UNION ALL 検索。ちなみに ALL 無しの UNION に変えてもほぼ同じ速度でした。

そして最も遅かったのは WHERE IN 検索でした。IN 句に指定する件数が多いとオプティマイザはフルスキャンを選択するため大幅に遅い結果になりました。