MySQLのインデックスマージを試してみる
インデックスのマージを試してみる。
今回使用するテーブル
今回は下記の構造のテーブルを使用する。
mysql> create table sample ( id int NOT NULL AUTO_INCREMENT, c1 int NOT NULL, c2 int NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;
結果を先にまとめると
長くなるので、先に結果をまとめると、下記の通り。
カラムの選択性によって、インデックスマージの効果が違いました。
選択性が高い場合
| パターン | index(c1)のみ | index(c1)とindex(c2) |
|---|---|---|
| c1 = N and c2 = N | 約0秒 | 約0秒 |
| c1 = N or c2 = N | 約3秒 | 約0秒 |
選択性が低い場合
| パターン | index(c1)のみ | index(c1)とindex(c2) |
|---|---|---|
| c1 = N and c2 = N | 約22秒 | 約1秒 |
| c1 = N or c2 = N | 約3秒 | 約25秒 |
選択性が高い場合は、インデックスマージはorの検索に有効だが、
選択性が低い場合は、インデックスマージはorの検索に逆効果っぽい。
とりあえず、よく分からん。
以下、確認した手順と結果を載せておきます。
選択性が高い場合
まずは、sampleテーブルに下記のSQLで1000万件のデータをINSERTする。
mysql> insert into sample (c1, c2) select FLOOR(RAND()*10000000 + 1), FLOOR(RAND()*10000000 + 1) from tmp t1, tmp t2, tmp t3, tmp t4, tmp t5, tmp t6, tmp t7;
c1,c2には1〜1000万の値が入り、選択性が高い状態になっている。
index(c1)のみ
まずは、c1のインデックスを作成する。
mysql> alter table sample add idx1 (c1);
この状態で、c1とc2をandで検索する。結果は下記の通り。
mysql> select count(*) from sample where c1 = 100 and c2 = 100; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
mysql> explain select count(*) from sample where c1 = 100 and c2 = 100; +----+-------------+--------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | sample | ref | idx1 | idx1 | 4 | const | 3 | Using where | +----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
次に、c1とc2をorで検索する。結果は下記の通り。
mysql> select count(*) from sample where c1 = 100 or c2 = 100; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (3.70 sec)
mysql> explain select count(*) from sample where c1 = 100 or c2 = 100; +----+-------------+--------+------+---------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | sample | ALL | idx1 | NULL | NULL | NULL | 10000597 | Using where | +----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
index(c1)+index(c2)
次に、c2のインデックスを追加して、index(c1)とindex(c2)がある状態にする。
mysql> alter table sample add idx2 (c2);
この状態で、c1とc2をandで検索する。結果は下記の通り。
mysql> select count(*) from sample where c1 = 100 and c2 = 100; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
mysql> explain select count(*) from sample where c1 = 100 and c2 = 100; +----+-------------+--------+-------------+---------------+-----------+---------+------+------+------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------------+---------------+-----------+---------+------+------+------------------------------------------------------+ | 1 | SIMPLE | sample | index_merge | idx1,idx2 | idx2,idx1 | 4,4 | NULL | 1 | Using intersect(idx2,idx1); Using where; Using index | +----+-------------+--------+-------------+---------------+-----------+---------+------+------+------------------------------------------------------+
次に、c1とc2をorで検索する。結果は下記の通り。
mysql> select count(*) from sample where c1 = 100 or c2 = 100; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec)
mysql> explain select count(*) from sample where c1 = 100 or c2 = 100; +----+-------------+--------+-------------+---------------+-----------+---------+------+------+-------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------------+---------------+-----------+---------+------+------+-------------------------------------+ | 1 | SIMPLE | sample | index_merge | idx1,idx2 | idx1,idx2 | 4,4 | NULL | 5 | Using union(idx1,idx2); Using where | +----+-------------+--------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
テーブルをリセット
一旦テーブルを再作成する。
mysql> drop table sample; mysql> create table sample (・・・);
選択性が低い場合
まずは、sampleテーブルに下記のSQLで1000万件のデータをINSERTする。
mysql> insert into sample (c1, c2) select FLOOR(RAND()*10 + 1), FLOOR(RAND()*10 + 1) from tmp t1, tmp t2, tmp t3, tmp t4, tmp t5, tmp t6, tmp t7;
c1,c2には1〜10の値が入り、選択性が低い状態になっている。
index(c1)のみ
まずは、c1のインデックスを作成する。
mysql> alter table sample add idx1 (c1);
この状態で、c1とc2をandで検索する。結果は下記の通り。
mysql> select count(*) from sample where c1 = 10 and c2 = 10; +----------+ | count(*) | +----------+ | 99686 | +----------+ 1 row in set (22.03 sec) >|mysql| mysql> explain select count(*) from sample where c1 = 10 and c2 = 10; +----+-------------+--------+------+---------------+------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+-------+--------+-------------+ | 1 | SIMPLE | sample | ref | idx1 | idx1 | 4 | const | 968448 | Using where | +----+-------------+--------+------+---------------+------+---------+-------+--------+-------------+
次に、c1とc2をorで検索する。結果は下記の通り。
mysql> select count(*) from sample where c1 = 10 or c2 = 10; +----------+ | count(*) | +----------+ | 1900349 | +----------+ 1 row in set (3.54 sec)
mysql> explain select count(*) from sample where c1 = 10 or c2 = 10; +----+-------------+--------+------+---------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | sample | ALL | idx1 | NULL | NULL | NULL | 10000673 | Using where | +----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
index(c1)+index(c2)
次に、c2のインデックスを追加して、index(c1)とindex(c2)がある状態にする。
mysql> alter table sample add idx2 (c2);
この状態で、c1とc2をandで検索する。結果は下記の通り。
mysql> select count(*) from sample where c1 = 10 and c2 = 10; +----------+ | count(*) | +----------+ | 99686 | +----------+ 1 row in set (0.70 sec)
mysql> explain select count(*) from sample where c1 = 10 and c2 = 10; +----+-------------+--------+-------------+---------------+-----------+---------+------+-------+------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------------+---------------+-----------+---------+------+-------+------------------------------------------------------+ | 1 | SIMPLE | sample | index_merge | idx1,idx2 | idx2,idx1 | 4,4 | NULL | 39561 | Using intersect(idx2,idx1); Using where; Using index | +----+-------------+--------+-------------+---------------+-----------+---------+------+-------+------------------------------------------------------+
次に、c1とc2をorで検索する。結果は下記の通り。
mysql> select count(*) from sample where c1 = 10 or c2 = 10; +----------+ | count(*) | +----------+ | 1900349 | +----------+ 1 row in set (25.09 sec)
mysql> explain select count(*) from sample where c1 = 10 or c2 = 10; +----+-------------+--------+-------------+---------------+-----------+---------+------+---------+-------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------------+---------------+-----------+---------+------+---------+-------------------------------------+ | 1 | SIMPLE | sample | index_merge | idx1,idx2 | idx1,idx2 | 4,4 | NULL | 1376960 | Using union(idx1,idx2); Using where | +----+-------------+--------+-------------+---------------+-----------+---------+------+---------+-------------------------------------+
【参考】 MySQL INDEX+EXPLAIN入門 http://www.slideshare.net/infinite_loop/mysql-indexexplain
