Hatena::ブログ(Diary)

yk5656 diary

記事一覧: Linux | Ansible | Swift | Ruby | PHP | JavaScript | MySQL | Git | Vim

2014-02-05(水)

MySQLで複合インデックスを作成してみる

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 |
+----+-----------+-------+------+------+

このテーブルを使って、複合インデックスを試してみる。

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)

1項目に対してインデックスを作成すると、

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 [インデックス名] (カラム名[, カラム名…])

とりあえず、前回作成した1項目に対するインデックスを削除しておく。

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

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/yk5656/20140205/1392080263