概要
stackoverflow で気になる記事を見つけました
Understanding performance impacts for mysql tuple search
MySQLでカラムA, カラムBの複合インデックス が張られているときに、
WHERE (`A`,`B`) IN ((a1, b1), (a2, b2))
のようにタプル指定をするとインデックスが効かないそうです。
かなり起きうるケースかと思いますので、これが効かないとなかなか不便そうです。
1個ずつ取得してUNIONで結合するのも冗長な気がします。
こちらが、本当に効かないのか検証してみます。
ちなみに、上記のWHERE句はこちらの指定と同等です。
WHERE (`A` = a1 AND `B` = b1) OR (`A` = a2 AND `B` = b2)
ダミーデータ準備
このようなテーブルを想定します。
カテゴリ - サブカテゴリ が複合インデックスとします。
商品テーブル
| id | カテゴリ | サブカテゴリ | 商品名 |
|---|---|---|---|
| 1 | 食品 | 果物 | りんご |
| 2 | 食品 | お菓子 | うまい棒 |
| 3 | 家具 | 収納 | カラーBOX |
↑のテーブル構成で、念のため件数をかさ増ししてダミーデータを作り、実行計画を取得します。
カテゴリが100件あり各カテゴリ内にサブカテゴリが100件あるという設定にして、100×100×=10,000件のダミーデータを作ります
| id | カテゴリ | サブカテゴリ | 商品名 |
|---|---|---|---|
| 1 | 1 | 1 | 商品1 |
| 2 | 1 | 2 | 商品2 |
| ... | ... | ... | ... |
| 100 | 1 | 100 | 商品100 |
| 101 | 2 | 1 | 商品101 |
| 102 | 2 | 2 | 商品102 |
| ... | ... | ... | ... |
| 10000 | 100 | 100 | 商品10000 |
CREATE TABLE products (
id INT NOT NULL AUTO_INCREMENT,
category INT NOT NULL,
subcategory INT NOT NULL,
name VARCHAR(20),
PRIMARY KEY (id),
INDEX category_index (category, subcategory)
);
DELIMITER $$
DROP PROCEDURE IF EXISTS insert_range$$
CREATE PROCEDURE insert_range()
BEGIN
DECLARE cnt INT DEFAULT 1;
DECLARE i INT DEFAULT 1;
DECLARE j INT DEFAULT 1;
WHILE i <= 100 DO
WHILE j <= 100 DO
INSERT products (category, subcategory, name) VALUES (i, j, CONCAT('商品',cnt));
SET j = j + 1;
SET cnt = cnt + 1;
END WHILE;
SET i = i + 1;
SET j = 1;
END WHILE;
END$$
DELIMITER ;
実行環境： MySQL 8.0.37
実行
作成したダミーテーブルに対して以下のクエリを実行して実行計画を取得してみます
EXPLAIN SELECT *
FROM products
WHERE (category, subcategory) IN ((1, 1),(2, 2));
結果はこのようになりました。
| select_type | table | partitions | possible_keys | key | key_len | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|
| SIMPLE | products | range | category_index | category_index | 8 | 2 | 100.00 | Using where |
int型は4バイトなので、key_lenが8ということは複合インデックスがサブカテゴリまで含めて効いていることになります。
stackoverflow の結果と違いますね。
WHERE句の書き方を↓のように変えてみてもやはりインデックスが効きます。
WHERE (category = 1 AND subcategory = 1) OR (category = 2 AND subcategory = 2)
さらに詳しく調べてみたところ、どうやらインデックスが効かないのはバグとして起票されて修正済みのようです
https://bugs.mysql.com/bug.php?id=31188
https://dev.mysql.com/worklog/task/?id=7019
おそらく、stackoverflowの質問者さんは修正前のバージョンを使っていたのかと思います。
結論
タプル指定に対してもインデックスは効きます。
ただしバージョンが古いと効かない可能性があるので、必ずEXPLAINで確認しましょう。
おまけ：3カラムの複合インデックスのタプル指定
2カラムのタプル指定にはインデックスが効くことが分かりました。
では3カラムだとどうなるか見てみましょう。
ダミーデータ準備
| id | カテゴリ | サブカテゴリ | サブカテゴリ内連番 | 商品名 |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 商品1 |
| 2 | 1 | 1 | 2 | 商品2 |
| ... | ... | ... | ... | ... |
| 100 | 1 | 1 | 100 | 商品100 |
| 101 | 1 | 2 | 1 | 商品101 |
| 102 | 1 | 2 | 2 | 商品102 |
| ... | ... | ... | ... | ... |
| 10000 | 1 | 100 | 100 | 商品10000 |
| 10001 | 2 | 1 | 1 | 商品10001 |
| 10001 | 2 | 1 | 2 | 商品10002 |
| ... | ... | ... | ... | ... |
| 1000000 | 100 | 100 | 100 | 商品1000000 |
-- テーブル作成
CREATE TABLE products (
id INT NOT NULL AUTO_INCREMENT,
category INT NOT NULL,
subcategory INT NOT NULL,
sequence INT NOT NULL,
name VARCHAR(20),
PRIMARY KEY (id),
INDEX category_index (category, subcategory, sequence)
);
-- データ投入
DELIMITER $$
DROP PROCEDURE IF EXISTS insert_range$$
CREATE PROCEDURE insert_range()
BEGIN
DECLARE cnt INT DEFAULT 1;
DECLARE i INT DEFAULT 1;
DECLARE j INT DEFAULT 1;
DECLARE k INT DEFAULT 1;
WHILE i <= 100 DO
WHILE j <= 100 DO
WHILE k <= 100 DO
INSERT products (category, subcategory, sequence, name) VALUES (i, j, k, CONCAT('商品',cnt));
SET k = k + 1;
SET cnt = cnt + 1;
END WHILE;
SET j = j + 1;
SET k = 1;
END WHILE;
SET i = i + 1;
SET j = 1;
END WHILE;
END$$
DELIMITER ;
CALL `insert_range`();
実行
-- 3番目まで含めたタプル指定
EXPLAIN SELECT *
FROM products
WHERE (category, subcategory, sequence) IN ((1, 1, 1),(2, 2, 2));
⇒3つ目まで効く(key_len=12)
-- 1,2番目をタプル指定、3番目を普通に指定
EXPLAIN SELECT *
FROM products
WHERE
(category, subcategory) IN ((1, 1),(2, 2))
AND sequence = 1;
⇒3つ目まで効く(key_len=12)
-- 1番目を普通に指定、2,3番目をタプル指定
EXPLAIN SELECT *
FROM products
WHERE
category = 1
AND (subcategory, sequence) IN ((1, 1),(2, 2));
⇒3つ目まで効く(key_len=12)
-- 3番目まで指定と2番目まで指定をORで結合
EXPLAIN SELECT *
FROM products
WHERE
(category = 1 AND subcategory = 1 AND sequence = 1) OR (category = 2 AND subcategory = 2);
⇒3つ目まで効く(key_len=12)
-- 1番目,2番目だけ指定
EXPLAIN SELECT *
FROM products
WHERE
(category, subcategory) IN ((1, 1),(2, 2));
⇒2つ目まで効く(key_len=8)
-- 2番目,3番目だけ指定
EXPLAIN SELECT *
FROM products
WHERE
(subcategory, sequence) IN ((1, 1),(2, 2));
⇒効かない(possible_keys=NULL)
以上から、3カラムであっても、特に意識しなくとも適用可能な範囲で適用してくれるように見えます。
最後まで読んでいただきありがとうございました。