MySQLで複合インデックスを作成してみる
MySQLで複合インデックスを作成してみる。
今回使用するテーブル
サンプルのテーブルに、レコードが1000万件入った状態。
mysql> show create table sample; +--------+---------------------------------------------------+ | Table | Create Table | +--------+---------------------------------------------------+ | sample | CREATE TABLE `sample` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, `point` int(11) NOT NULL, `type` smallint(6) NOT NULL, `flag` tinyint(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10026856 DEFAULT CHARSET=utf8 | +--------+---------------------------------------------------+
mysql> select count(*) from sample; +----------+ | count(*) | +----------+ | 10000000 | +----------+
pointには1〜100、 typeには1〜10、flagには0,1が入っている。
下記のINSERTでデータを流し込んだ。 insert into sample (name, point, type, flag) select ELT(t1.id, 'sato', 'suzuki', 'takahashi', 'tanaka', 'ito', 'yamamoto', 'watanabe', 'nakamura', 'kobayashi', 'kato'), FLOOR(RAND()*100 + 1), FLOOR(RAND()*10 + 1), FLOOR(RAND() * 2) from tmp t1, tmp t2, tmp t3, tmp t4, tmp t5, tmp t6, tmp t7;
mysql> select * from sample limit 20; +----+-----------+-------+------+------+ | id | name | point | type | flag | +----+-----------+-------+------+------+ | 1 | sato | 85 | 8 | 0 | | 2 | suzuki | 44 | 8 | 1 | | 3 | takahashi | 2 | 1 | 0 | | 4 | tanaka | 73 | 2 | 0 | | 5 | ito | 61 | 9 | 0 | | 6 | yamamoto | 60 | 7 | 1 | | 7 | watanabe | 19 | 10 | 0 | | 8 | nakamura | 86 | 2 | 0 | | 9 | kobayashi | 37 | 7 | 0 | | 10 | kato | 84 | 7 | 1 | | 11 | sato | 67 | 6 | 1 | | 12 | suzuki | 2 | 6 | 1 | | 13 | takahashi | 3 | 10 | 0 | | 14 | tanaka | 54 | 4 | 1 | | 15 | ito | 90 | 6 | 0 | | 16 | yamamoto | 31 | 10 | 1 | | 17 | watanabe | 48 | 5 | 1 | | 18 | nakamura | 40 | 8 | 1 | | 19 | kobayashi | 91 | 7 | 0 | | 20 | kato | 87 | 4 | 1 | +----+-----------+-------+------+------+
このテーブルを使って、複合インデックスを試してみる。
１項目のインデックスだと
インデックスが何もない状態で、下記のように検索すると、約3.5秒ほどかかってたが、
mysql> select count(*) from sample where point = 100 and type = 10 and flag = 1; +----------+ | count(*) | +----------+ | 9717 | +----------+ 1 row in set (3.56 sec)
１項目に対してインデックスを作成すると、
mysql> alter table sample add index single_idx (point);
検索にかかる時間が約0.7秒くらいになった。
mysql> select count(*) from sample where point = 100 and type = 10 and flag = 1; +----------+ | count(*) | +----------+ | 9717 | +----------+ 1 row in set (0.72 sec)
複合インデックスを作成してみる
複合インデックスは、カラム名を複数指定すればOK。
CREATE INDEX インデックス名 ON テーブル名 (カラム名[, カラム名…]) or ALTER TABLE テーブル名 ADD INDEX [インデックス名] (カラム名[, カラム名…])
とりあえず、前回作成した１項目に対するインデックスを削除しておく。
mysql> alter table sample drop index single_idx;
複合インデックスを作成すると、
mysql> alter table sample add index multiple_idx (point, type, flag);
検索にかかる時間が0.1秒になった。
mysql> select count(*) from sample where point = 100 and type = 10 and flag = 1; +----------+ | count(*) | +----------+ | 9717 | +----------+ 1 row in set (0.01 sec)
where句に指定する順番
ちなみに、where句に指定する検索条件の順番が、
インデックスの順番と異なっていても、インデックスは効く。
mysql> select count(*) from sample where flag = 1 and type = 10 and point = 100; +----------+ | count(*) | +----------+ | 9717 | +----------+ 1 row in set (0.01 sec)
複合インデックスが効く場合と効かない場合
ALTER TABLE テーブル名 ADD INDEX (c1, c2, c3)
といった複合インデックスを作成した場合、
下記の検索にはインデックスが効くが、
- where c1 = 値 and c2 = 値 and c3 = 値
- where c1 = 値 and c2 = 値
- where c1 = 値
下記の検索にはインデックスが効かない。
- where c2 = 値 and c3 = 値
- where c2 = 値
- where c3 = 値
実際にsmapleテーブルで試した結果を以下に載せておく。
「point = 100 and type = 10」にはインデックスが効く
mysql> select count(*) from sample where point = 100 and type = 10; +----------+ | count(*) | +----------+ | 10034 | +----------+ 1 row in set (0.01 sec)
「point = 100」にはインデックスが効く
mysql> select count(*) from sample where point = 100; +----------+ | count(*) | +----------+ | 100966 | +----------+ 1 row in set (0.03 sec)
「type = 10 and flag = 1」にはインデックスが効かない
mysql> select count(*) from sample where type = 10 and flag = 1; +----------+ | count(*) | +----------+ | 499629 | +----------+ 1 row in set (3.11 sec)
「type = 10」や「flag = 1」にはインデックスが効かない
mysql> select count(*) from sample where type = 10; +----------+ | count(*) | +----------+ | 1000536 | +----------+ 1 row in set (2.24 sec)
mysql> select count(*) from sample where flag = 1; +----------+ | count(*) | +----------+ | 5000714 | +----------+ 1 row in set (2.30 sec)
複合インデックスの順番
複合インデックスの順番を変えて、選択性の低いカラムから順番に作成してみたが、
あまり変わらなかった。
（もっと件数が多いと差が出るのだろうか？）
mysql> alter table sample drop index multiple_idx; mysql> alter table sample add index multiple_idx2 (flag, type, point); mysql> select count(*) from sample where point = 100 and type = 10 and flag = 1; +----------+ | count(*) | +----------+ | 9717 | +----------+ 1 row in set (0.01 sec)
【参考】 複合インデックスによるMySQLのパフォーマンスチューニング — shogo's blog http://shogo-s.com/wordpress/?p=115
