Carpe Diem

  Top > スポンサー広告> MySQL > INDEX の効果を確認する  

スポンサーサイト

-- - --/-- [--] - --:--

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

INDEX の効果を確認する

2014 - 08/20 [Wed] - 12:58

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

コメントの投稿





管理者にだけ表示を許可する

 | ホーム | 

プロフィール

Cicatrice

Author:Cicatrice
備忘録

検索フォーム

カテゴリ

最新記事

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。