InnoDBテーブルでインデックスを拡張するとパフォーマンスが落ちるかも from MySQL Performance Blog

2010/06/16 | Written by rust | under Blog

Extending Index for Innodb tables can hurt performance in a surprising way

多くのキーを使うクエリーのときに、よく最適化するときにインデックスを拡張する。通常は問題なく、インデックスの長さが劇的に増加しない限り、インデックスを使うクエリーは新しいインデックスの先頭を使うことができる。では、そうではない場合を見てみよう。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE TABLE `idxitest` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`a` INT(11) NOT NULL,
`b` INT(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1

mysql> SELECT COUNT(*) FROM idxitest WHERE a=5 AND b=5;
+----------+
| COUNT(*) |
+----------+
| 60434 |
+----------+
1 ROW IN SET (0.69 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM idxitest WHERE a=5 AND b=5;
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxitest | REF | a | a | 4 | const | 707820 | USING WHERE |
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
1 ROW IN SET (0.00 sec)

さて、カラム(a)から(a, b)にインデックスを拡張することで、高速化して他に影響を与えない最適化ができる。いいかな？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

mysql> ALTER TABLE idxitest DROP KEY a,ADD KEY(a,b);
Query OK, 0 ROWS affected (24.84 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT COUNT(*) FROM idxitest WHERE a=5 AND b=5;
+----------+
| COUNT(*) |
+----------+
| 60434 |
+----------+
1 ROW IN SET (0.02 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM idxitest WHERE a=5 AND b=5;
+----+-------------+----------+------+---------------+------+---------+-------------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+------+---------------+------+---------+-------------+--------+-------------+
| 1 | SIMPLE | idxitest | REF | a | a | 8 | const,const | 120640 | USING INDEX |
+----+-------------+----------+------+---------------+------+---------+-------------+--------+-------------+
1 ROW IN SET (0.00 sec)

素晴らしい。クエリーは30倍も速くなった。スキャンする行が減っただけではなく、インデックスですべてまかなえるようになった。これだとデータにアクセスする必要がない。

でも喜ぶには早すぎる。ほとんど気がついていないようなそれまでは高速に実行されていたクエリーが他にもあった。しかしそれが遅くなるのだ。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

# 古いスキーマで実行

mysql> SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+---------+-----+---+
| id | a | b |
+---------+-----+---+
| 3000000 | 100 | 7 |
+---------+-----+---+
1 ROW IN SET (0.00 sec)

mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
| 1 | SIMPLE | idxitest | REF | a | a | 4 | const | 126074 | USING WHERE |
+----+-------------+----------+------+---------------+------+---------+-------+--------+-------------+
1 ROW IN SET (0.00 sec)

# 新しいスキーマで実行(インデックスを拡張)

mysql> SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+---------+-----+---+
| id | a | b |
+---------+-----+---+
| 3000000 | 100 | 7 |
+---------+-----+---+
1 ROW IN SET (1.01 sec)

mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | idxitest | INDEX | a | PRIMARY | 4 | NULL | 36 | USING WHERE |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 ROW IN SET (0.00 sec)

# 実行計画は以下のようになることもある

mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+----+-------------+----------+------+---------------+------+---------+-------+------+------------------------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+------+---------------+------+---------+-------+------+------------------------------------------+
| 1 | SIMPLE | idxitest | REF | a | a | 4 | const | 1 | USING WHERE; USING INDEX; USING filesort |
+----+-------------+----------+------+---------------+------+---------+-------+------+------------------------------------------+
1 ROW IN SET (0.01 sec)

なぜこんなに遅くなってしまうのか。理由はInnoDBの特性に有利な実行計画にある。つまり、インデックスが各キーの値に対する主キーでソートされているということ。なのでインデックス(a)があってidが主キーであるとき、実際のインデックスは(a, id)となり、拡張されたインデックスが(a, b)であるときには実際のインデックスは(a, b, id)になるということ。なので、元にインデックスで(a, id)を使っていたようなクエリーでは、新しいインデックスを使うことができない。

じゃあどうすれば？こんなときは「冗長」なインデックスを(a)と(a, b)に同時に張ることができる。だが、これをちゃんと動くと仮定する仮定しがちだが、しばしば動作しない。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE `idxitest` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`a` INT(11) NOT NULL,
`b` INT(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `a_2` (`a`,`b`)
) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1

mysql> SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+---------+-----+---+
| id | a | b |
+---------+-----+---+
| 3000000 | 100 | 7 |
+---------+-----+---+
1 ROW IN SET (1.03 sec)

mysql> EXPLAIN SELECT * FROM idxitest WHERE a=100 ORDER BY id DESC LIMIT 1;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | idxitest | INDEX | a,a_2 | PRIMARY | 4 | NULL | 2247 | USING WHERE |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 ROW IN SET (0.00 sec)

MySQLのオプティマイザーは(a)と(a, b)のインデックスの利用を考えた後、最後にどちらも使わずにフルインデックススキャンすることにしたようだ。(a)のインデックスを使えば1行だけでいいことが保証されるのに、2247行もスキャンするような計画を見積もったこのケースでは、オプティマイザーの誤動作のように見える。

正しい計画をMySQLオプティマイザーに使わせるためには、FORCE INDEX(a)を使う必要がある。

これは冗長なインデックスを調べるツールであるmk-duplicate-key-checkerの結果から、インデックスを変更するときには十分気をつけなければならないことを意味する。インデックスの中で主キーによってデータがソートされているInnoDBに依存するようなクエリーがあるのなら、変更が重大な影響を及ぼすことになるからだ。

オプティマイザーの振る舞いはMySQLのバージョンによって異なる。今回は5.1.45で行ったが、5.0でも同じ振る舞いだったことを記しておく。