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