MySQL で 複合キーを貼る時は explain の key_len をちゃんと見る

複合キーを貼っている以下のようなテーブルで、index が効かなくてクエリがめちゃ重くなる現象が発生してしまった。

CREATE TABLE `example` (
    `id` BIGINT UNSIGNED NOT NULL,
    `column1` BIGINT UNSIGNED NOT NULL,
    `column2` BIGINT UNSIGNED NOT NULL,
    `column3` VARCHAR(36) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `index1` (column1, column2),
    INDEX `index2` (column1, column3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

実際に発行したクエリは以下のような感じ。

SELECT * FROM example
WHERE column1 = 1
AND (column2 = 1 OR column3 = "hoge");

OR 使ってるので index 効かないのはそれはそうという感じなんだけど、explain 結果を見た時possible_keys, key に index 名が入っているので効いてるじゃんと思ってしまった。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	example		ref	index1,index2	index1	8	const	2	75	Using where

しかし、key_len が 8 になっているので、これははちゃんと複合 index が効いていない状態。ちゃんと効いてるのであれば、key_len は BIGINT の 8byte *2 = 16 または BIGINT + VARCHAR(36) の 146 = 154 *1 になるはず。実際に index が効くようにクエリを発行すると、そのような結果になる。

index1 が効くクエリ

EXPLAIN  SELECT * FROM example
WHERE column1 = 1
AND column2 = 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	example		ref	index1,index2	index1	16	const,const	2	100

index2 が効くクエリ

EXPLAIN  SELECT * FROM example
WHERE column1 = 1
AND column3 = "hoge";

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	example		ref	index1,index2	index2	154	const,const	1	100

結局上記のようにクエリを2つに分割することで解決した。

key_len がおかしければ複合 index が効いてないことがわかるので、explain するときは key_len をちゃんと見ましょうという教訓。

そしてこれらのクエリを試すために SQL の Playground であるところの DB Fiddle 使ったけど手元に環境を用意する必要がなくて便利。Makdown で出力できる機能もあるので、ブログとかに貼りやすくて助かる。 https://www.db-fiddle.com/f/4yd3igqfkESZBG6dtT85hL/0

*1:VARCHAR の byte 数は最大長。utf8mb4 なので 36 * 4 byte に加えて、さらに varstring だとさらに2バイト常に確保される様子