MySQLのインデックスの気になる所を確認してみる

MySQL

CentOSにMySQLとMemcacheのインストールしてみる
でMySQLをインストールした状態。

MySQLのインデックスで下記の点が気になったので確認してみる。

・NULL可のカラムでもインデックスが効くのか
・日時(datetime)でもインデックスが効くのか
・不等号でもインデックスが効くのか
・ユニークインデックスでもインデックスとして効くのか

準備

testデータベースで試す。

msyql> use test

大量にレコードを追加するためのテーブルを用意しておく。

mysql> create table tmp (id int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
mysql>insert into tmp values (), (), (), (), (), (), (), (), (), ();

NULL可のdatetime型のカラムを持つテーブルを作成する。

mysql> create table sample (
  id      int         NOT NULL AUTO_INCREMENT,
  dt      datetime,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

データを10000万件入れる。

mysql> insert into sample (dt)
 select
  FROM_UNIXTIME(t7.id * 1000000 + t6.id * 100000 + t5.id * 10000 + t4.id * 1000 + t3.id * 100 + t2.id * 10 + t1.id - 1111110)
 from tmp t1, tmp t2, tmp t3, tmp t4, tmp t5, tmp t6, tmp t7;

下記のような感じでデータが入る。

mysql> select * from sample limit 10;
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | 1970-01-01 09:00:01 |
|  2 | 1970-01-01 09:00:02 |
|  3 | 1970-01-01 09:00:03 |
|  4 | 1970-01-01 09:00:04 |
|  5 | 1970-01-01 09:00:05 |
|  6 | 1970-01-01 09:00:06 |
|  7 | 1970-01-01 09:00:07 |
|  8 | 1970-01-01 09:00:08 |
|  9 | 1970-01-01 09:00:09 |
| 10 | 1970-01-01 09:00:10 |
+----+---------------------+

mysql> select count(*) from sample;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+

インデックスを設定していない場合

インデックスが無い状態で、日時のカラムを不等号で検索すると、
3秒ほどかかった。

mysql> select count(*) from sample where dt >= '1970-03-01 00:00:00' and dt <= '1970-03-01 23:59:59';
+----------+
| count(*) |
+----------+
|    86400 |
+----------+
1 row in set (2.96 sec)

当然インデックスは有効でない。

mysql> explain select count(*) from sample where dt >= '1970-03-01 00:00:00' and dt <= '1970-03-01 23:59:59';
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | sample | ALL  | NULL          | NULL | NULL    | NULL | 10000378 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+

インデックスを設定した場合

ユニークインデックスを設定する。

mysql> alter table sample add unique (dt);

再度、日時のカラムを不等号で検索すると、
一瞬で終わった。

mysql> select count(*) from sample where dt >= '1970-03-01 00:00:00' and dt <= '1970-03-01 23:59:59';
+----------+
| count(*) |
+----------+
|    86400 |
+----------+
1 row in set (0.03 sec)

インデックスが効いてるのが分かる。

mysql> explain select count(*) from sample where dt >= '1970-03-01 00:00:00' and dt <= '1970-03-01 23:59:59';
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | sample | range | dt            | dt   | 9       | NULL | 201710 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+