MySQL のインデックスを試してみる

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

データは以下のスクリプトで作りました。

```python: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.|

参考
[SHOW構文：テーブル情報を表示する](http://rfs.jp/sb/sql/s03/07-8.html)

```
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のインデックスの効果を理解する](http://memo.sugyan.com/entry/20100926/1285508863)
[MySQLコマンドやSQL文に関する各種メモ書き](http://logic.moo.jp/data/archives/176.html)
[MySQL初級者を脱するために勉強してること -INDEX編-](http://qiita.com/kkyouhei/items/e3502ef632c48d94541d)
[MySQLのExplainを確認する](http://woshidan.hatenablog.com/entry/2015/06/20/165817)