INDEX の効果を確認する

DB高速化で INDEX を貼るという選択肢がありますが、初心者にはどれくらい効果があるかなんてわからないですよね。なので一度確認方法含めて紹介します。

◆DB&テーブル作る

mysql> create database test;
mysql> use test;
mysql> create table index_test (
    -> id int(10) unsigned not null auto_increment,
    -> school int(10) unsigned not null,
    -> data varchar(100) not null,
    -> updated_at int(10) unsigned not null,
    -> primary key(id));

◆ダミーデータ入れる

適当なデータをまず入れます。

mysql> insert into index_test (school, data, updated_at)  values(1, 'hoge', UNIX_TIMESTAMP(NOW()));
mysql> insert into index_test (school, data, updated_at)  values(2, 'fuga', UNIX_TIMESTAMP(NOW()));
mysql> insert into index_test (school, data, updated_at)  values(3, 'piyo', UNIX_TIMESTAMP(NOW()));
mysql> insert into index_test (school, data, updated_at)  values(4, 'hoge', UNIX_TIMESTAMP(NOW()));
mysql> insert into index_test (school, data, updated_at)  values(5, 'hoge', UNIX_TIMESTAMP(NOW()));
mysql> insert into index_test (school, data, updated_at)  values(6, 'hoge', UNIX_TIMESTAMP(NOW()));

次に入れたデータを元に倍々に増やしていきます。

mysql> insert into index_test (school, data, updated_at) select school, data, updated_at from index_test;

上のコードを実行するとすでに持っているデータを丸々新規で入れてくれるので倍々になっていきます。何度か繰り返せばすぐに数百万件のデータになります。

◆状態確認

まずはINDEXの状態を確認します。

mysql> show index in index_test;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_test |          0 | PRIMARY  |            1 | id          | A         |     3139059 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

当然ながらプライマリーキーしかないです。ここでチェックする項目は

・Cardinality

です。これが大きいほどINDEXの効果があると考えてOKです。

さて、DB のどこに INDEX 貼るかを理解する上で必要なのが「explain」です。 explain を使うとその SQL のチューニングポイントを把握できます。プライマリーキーである id と、なんの INDEX もない school で比較してみましょう。

mysql> explain select * from index_test where id=1100;
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | index_test | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from index_test where school=1100;
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | index_test | ALL  | NULL          | NULL | NULL    | NULL | 3139059 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

ここでチェックする項目は主に

・type

・rows

です。typeは

・const：一意。一番早い。

・ref：インデックスあるし、まぁ普通

・INDEX：フルインデックススキャン。めっちゃ重いです。

・ALL：全部検索するのでめっちゃ遅い

って感じです。プライマリーキーのほうは当然 const ですね。一方 school の方は INDEX 等ないので ALL です。 rows は検索対象のカラム数です。ただこれ正確な値ではないので、目安として見る程度で。では実際にSQLを実行して比較してみましょう。

mysql> select * from index_test where id=1100;
+------+--------+------+------------+
| id   | school | data | updated_at |
+------+--------+------+------------+
| 1100 |      1 | hoge | 1408496807 |
+------+--------+------+------------+
1 row in set (0.00 sec)

一意なので瞬殺ですね。

mysql> select count(*) from index_test where school=3;
+----------+
| count(*) |
+----------+
|   524288 |
+----------+
1 row in set (0.63 sec)

遅いですね。

◆INDEX貼って効果確認

mysql> alter table index_test add index school_index(school);
Query OK, 0 rows affected (6.09 sec)

INDEX はデータが多いとその分時間がかかります。開発中 or サービスメンテ中とかにやっちゃいましょう。まず explain で確認しましょう。

mysql> explain select count(*) from index_test where school=1;
+----+-------------+------------+------+---------------+--------------+---------+-------+---------+-------------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref   | rows    | Extra       |
+----+-------------+------------+------+---------------+--------------+---------+-------+---------+-------------+
|  1 | SIMPLE      | index_test | ref  | school_index  | school_index | 4       | const | 1029744 | Using index |
+----+-------------+------------+------+---------------+--------------+---------+-------+---------+-------------+

お、type が ref になり rows も下がりましたね。では実際に実行。

mysql> select count(*) from index_test where school=3;
+----------+
| count(*) |
+----------+
|   524288 |
+----------+
1 row in set (0.10 sec)

速くなりました！

◆文字列の方も試してみましょう

INDEX 貼る前は以下。

mysql> select count(*) from index_test where data='hoge';
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+
1 row in set (0.88 sec)

INDEX 貼ります。

mysql> alter table index_test add index data_index(data);
Query OK, 0 rows affected (8.21 sec)

こちらもやはり時間かかりますね。ではまず explain

mysql> explain select count(*) from index_test where data='hoge';
+----+-------------+------------+------+---------------+------------+---------+-------+---------+--------------------------+
| id | select_type | table      | type | possible_keys | key        | key_len | ref   | rows    | Extra                    |
+----+-------------+------------+------+---------------+------------+---------+-------+---------+--------------------------+
|  1 | SIMPLE      | index_test | ref  | data_index    | data_index | 302     | const | 1569529 | Using where; Using index |
+----+-------------+------------+------+---------------+------------+---------+-------+---------+--------------------------+
1 row in set (0.00 sec)

きちんと type や rows が変化していますね。では実行。

mysql> select count(*) from index_test where data='hoge';
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+
1 row in set (0.70 sec)

ありゃりゃ、そこまで劇的に変わってないですね。文字列だとやはり効果は薄いようです。以上、簡単でしたが MySQL の INDEX の動作確認でした。

Carpe Diem

備忘録

INDEX の効果を確認する