Hatena::ブログ(Diary)

yk5656 diary

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

2014-02-03(月)

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

MySQLで大量のデータを追加してみる
の続き

インデックスを作成してみる。


インデックスの作成/削除の構文

インデックスの作成は、

CREATE INDEX インデックス名 ON テーブル名 (カラム名[, カラム名…])

または

ALTER TABLE テーブル名 ADD INDEX [インデックス名] (カラム名[, カラム名…])

インデックスの削除は、

DROP INDEX インデックス名 ON テーブル名

または

ALTER TABLE テーブル名 DROP INDEX インデックス名

ALTER TABLEはまとめて書くこともできる

ALTER TABLE tbl1 ADD INDEX idx1 (c1, c2), ADD INDEX idx2 (c3);
ALTER TABLE tbl1 DROP INDEX idx1, DROP INDEX idx2;

インデックスの効果

MySQLで大量のデータを追加してみる
で作成した1000万件のテーブルに、インデックスが無い状態で検索してみる。

point=100を条件に指定して検索すると、約2.5秒かかる。

mysql> select count(*) from sample where point = 100;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (2.55 sec)

インデックスを追加する。

mysql> alter table sample add index (point);
Query OK, 10000000 rows affected (1 min 44.39 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

再度検索すると、0.01秒で終わった。

mysql> select count(*) from sample where point = 100;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

選択性(カーディナリティ)とは、

カラムの値の種類がレコード数に比べて多く、よりユニークに近いカラムは、
選択性が高い(カーディナリティが高い)
という。

逆に、「性別」などように、
カラムの値の種類がレコード数に比べて少く、あまり絞り込むことのできないカラムは
選択性が低い(カーディナリティが低い)
という。

選択性の低いカラムにインデックスにしても効果は少ない

flag=1を条件に指定して検索すると、約2.6秒かかる。
point=100の場合は、count(*)の結果が1だったが、
flag=1の場合は、count(*)の結果が約500万件。
つまり、flagは選択性が低い(あまり絞り込むことができない)。

mysql> select count(*) from sample where flag = 1;
+----------+
| count(*) |
+----------+
|  5003220 |
+----------+
1 row in set (2.60 sec)

pointの時と同じようにインデックスを作成してみる。

mysql> alter table sample add index (flag);
Query OK, 10000000 rows affected (2 min 9.44 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

再度検索すると、約1.5秒かかった。
選択性の低いカラムをインデックスにしても効果は少ない。

mysql> select count(*) from sample where flag = 1;
+----------+
| count(*) |
+----------+
|  5003220 |
+----------+
1 row in set (1.44 sec)

インデックスの確認

show indexでインデックスを確認できる。

SHOW INDEX FROM テーブル名

sampleテーブルのindexを見てみると、下記のように表示される。

mysql> show index from sample;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sample |          0 | PRIMARY  |            1 | id          | A         |    10000461 |     NULL | NULL   |      | BTREE      |         |
| sample |          1 | point    |            1 | point       | A         |    10000461 |     NULL | NULL   |      | BTREE      |         |
| sample |          1 | flag     |            1 | flag        | A         |          18 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

show create tableでも一応インデックスを確認できる。

SHOW CREATE TABLE テーブル名

sampleテーブルのcreate文を見てみると、下記のように表示される。

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`),
  KEY `point` (`point`),
  KEY `flag` (`flag`)
) ENGINE=InnoDB AUTO_INCREMENT=10026856 DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------+

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


画像認証

トラックバック - http://d.hatena.ne.jp/yk5656/20140203/1391912932