複合キーを貼っている以下のようなテーブルで、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