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 の動作確認でした。