MySQL はindex 貼ると早くなるってくらいの知識しかないのですが、どうすれば早くなるのか試してみました。
試した時の手順をメモとして残しておきます。
（index は貼るものか作るものか付けるものかよくわかりませんが、以下は貼るで統一しておきます。）
基本操作の確認
index の貼り方
ALTER TABLE <テーブル名> ADD INDEX <インデックス名>(<カラム名>);
または
CREATE INDEX <テーブル名> ON <インデックス名>(<カラム名>)
index の削除の仕方
ALTER TABLE <テーブル名> DROP INDEX <インデックス名>;
index の確認方法
SHOW INDEX FROM <テーブル名>;
また、EXPLAIN <調べたいクエリ> とコマンドを打つことで、クエリの実行方法を調査してくれます。
試してみる
確認用の適当なテーブルを作ります。
通し番号、カテゴリー、適当な数字の3つの行を持つテーブルです。
10000000行ほどデータを突っ込みます。
mysql> create table itest3 (id int, category varchar(5), num int);
Query OK, 0 rows affected (0.02 sec)
mysql> load data infile "<PATH_TO_FILE>" into table itest3 fields terminated by ","
Query OK, 10000000 rows affected (30.01 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0
データは以下のスクリプトで作りました。
create.py
#!/usr/bin/python
#-*- coding: utf-8 -*-
import random
row_number = 10000000
category = ["t1", "t2", "t3", "t4", "t5"]
for x in xrange(row_number):
line = str(x) + "," + category[int(random.random()*5)] + "," + str(int(random.random()*100))
print line
% python create.py > test.csv
適当なクエリを試してみます。
mysql> select count(*) from itest3 where category = "t2";
+----------+
| count(*) |
+----------+
| 1999825 |
+----------+
1 row in set (4.00 sec)
mysql> select count(*) from itest3 where category = "t2" and num > 80 ;
+----------+
| count(*) |
+----------+
| 380036 |
+----------+
1 row in set (4.32 sec)
category で検索、category とnum で検索どちらもだいたい4秒くらいかかりました。
index を貼ってみます。
mysql> alter table itest3 add index category_index(category);
Query OK, 0 rows affected (19.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from itest3;
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| itest3 | 1 | category_index | 1 | category | A | 4 | NULL | NULL | YES | BTREE | | |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
category にindex を貼ってみました。
SHOW INDEX コマンドで現在のidnex が確認できます。
| フィールド名 | 意味 |
|---|---|
| Table | テーブル名 |
| Non_unique | インデックスが重複を含まないなら 0 |
| Key_name | インデックス名 |
| Seq_in_index | インデックスのフィールド番号。1 から始まります。 |
| Column_name | index を貼っているカラムの名前 |
| Collation | インデックスでの ソート方法です。A (Ascending) か NULL (Not sorted) があります。 |
| Cardinality | インデックスのユニークな値の数。 |
| Sub_part | もしこのフィールドがインデックスに一部分だけ使用している場合、そのインデックスに使用しているキャラクター数をしめす。 もしキー全体がインデックスされているなら NULL 。 |
| Comment | Various remarks. For now, it tells whether index is FULLTEXT or not. |
mysql> select count(*) from itest3 where category = "t2";
+----------+
| count(*) |
+----------+
| 1999825 |
+----------+
1 row in set (0.75 sec)
同じクエリが4秒->0.75 秒とかなり高速化されていることがわかります。
mysql> select count(*) from itest3 where category = "t2" and num > 80 ;
+----------+
| count(*) |
+----------+
| 380036 |
+----------+
1 row in set (1 min 20.11 sec)
でもこちらのクエリは4.32秒->1分20秒に。。。
こちらはとんでもなく遅くなっていました。
次にcategory とnum に複合index を貼ってみました。
mysql> alter table itest3 add index category_and_num_index(category,num);
Query OK, 0 rows affected (27.99 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from itest3;
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| itest3 | 1 | category_index | 1 | category | A | 4 | NULL | NULL | YES | BTREE | | |
| itest3 | 1 | category_and_num_index | 1 | category | A | 4 | NULL | NULL | YES | BTREE | | |
| itest3 | 1 | category_and_num_index | 2 | num | A | 501 | NULL | NULL | YES | BTREE | | |
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
先ほど激遅だったクエリを実行してみると……
mysql> select count(*) from itest3 where category = "t2" and num > 80 ;
+----------+
| count(*) |
+----------+
| 380036 |
+----------+
1 row in set (0.21 sec)
超高速化されていました（約80秒->0.21秒）
最初のindex で早くなったクエリを実行してみると、
mysql> select count(*) from itest3 where category = "t2";
+----------+
| count(*) |
+----------+
| 1999825 |
+----------+
1 row in set (0.73 sec)
速いままでした。
以下にindex を貼る前と後で、同じクエリに対してEXPLAIN を実行した結果を載せておきます。
はじめ
mysql> explain select count(*) from itest3 where category = "t2";
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | itest3 | NULL | ALL | NULL | NULL | NULL | NULL | 10777272 | 10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(*) from itest3 where category = "t2" and num > 80;
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | itest3 | NULL | ALL | NULL | NULL | NULL | NULL | 10777272 | 3.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
alter table itest3 add index category_index(category); の後
mysql> explain select count(*) from itest3 where category = "t2";
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
| 1 | SIMPLE | itest3 | NULL | ref | category_index | category_index | 18 | const | 3690834 | 100.00 | Using index |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(*) from itest3 where category = "t2" and num > 80;
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
| 1 | SIMPLE | itest3 | NULL | ref | category_index | category_index | 18 | const | 3690834 | 33.33 | Using where |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
alter table itest3 add index category_and_num_index(category,num); の後
mysql> explain select count(*) from itest3 where category = "t2";
+----+-------------+--------+------------+------+---------------------------------------+----------------+---------+-------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------------------------------+----------------+---------+-------+---------+----------+-------------+
| 1 | SIMPLE | itest3 | NULL | ref | category_index,category_and_num_index | category_index | 18 | const | 3690834 | 100.00 | Using index |
+----+-------------+--------+------------+------+---------------------------------------+----------------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(*) from itest3 where category = "t2" and num > 80;
+----+-------------+--------+------------+-------+---------------------------------------+------------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------------------------------+------------------------+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | itest3 | NULL | range | category_index,category_and_num_index | category_and_num_index | 23 | NULL | 775046 | 100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------------------------------+------------------------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
(何故1つ目のindex で2番目のクエリがこんなに遅くなったのかがよく分からない……)
まとめ
- 行いたい検索に合わせて使うカラムを指定したindex を貼ると早くなる。
- 適当なindex では逆に遅くなるパターンも。
- index を貼る前に、そのテーブルに対してどのような検索を行いたいかを考える。
- 実際に使っているテーブルに対してindex を貼ったせいで遅くなったりしたら困るので、もうちょい勉強した方が良さげ。
参考
MySQLのインデックスの効果を理解する
MySQLコマンドやSQL文に関する各種メモ書き
MySQL初級者を脱するために勉強してること -INDEX編-
MySQLのExplainを確認する