#79　【MySQL】複合インデックスのタプル指定(2カラム指定のOR条件)にインデックスは効くのか

概要

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

SQL文

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

SQL文

-- テーブル作成
 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カラムであっても、特に意識しなくとも適用可能な範囲で適用してくれるように見えます。
最後まで読んでいただきありがとうございました。

参考

• https://stackoverflow.com/questions/44199208/understanding-performance-impacts-for-mysql-tuple-search
• https://stackoverflow.com/questions/68508415/how-to-create-a-procedure-to-generate-a-range-of-numbers-in-mysql
• https://bugs.mysql.com/bug.php?id=31188
• https://dev.mysql.com/worklog/task/?id=7019