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 句に指定する件数が多いとオプティマイザはフルスキャンを選択するため大幅に遅い結果になりました。