MySQLでdatetime型にもインデックスを使用して高速化を図る方法

ログ関連のデータを格納するテーブルがあり、集計を行う際に非常に時間がかかった。レコード数は1億ほどあったので、これはもう仕方がないんじゃないかと思ったんだけど他者が作成したテーブルで3億ほどレコード数があるのに高速で動いていた。色々調べてみた結果のメモ。

違いを調べてみた結果

両者のテーブル構造やインデックス構造を確認したところ、高速で動く方はdatetime型のカラムにインデックスが貼られていた。またパーティショニングされていた。

パーティショニング

パーティショニングというのは初めて聞いたので調べてみたところ、テーブルを分割して高速化を図る機能みたい。ただ、パーティショニングは途中(2016年とか)で切れていたので今回はあまり関係なさそう。

https://liginc.co.jp/programmer/archives/3832

datetime型にインデックス

datetime型にインデックスを張ってもフルスキャンされると思い込んでいたがどうやら違うみたい。試しに以下テーブルを作成し400万ほどランダムなデータを入れた。created_dataの範囲は2014-01-01から2014-06-30まで。

mysql> show columns from item;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(11)          | NO   | PRI | NULL    | auto_increment |
| key          | varchar(30)      | YES  |     | NULL    |                |
| point        | int(10) unsigned | YES  |     | NULL    |                |
| created_date | datetime         | YES  | MUL | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

#インデックスなし
SELECT *
FROM item 
WHERE created_date BETWEEN '2014-01-01 00:00:00' AND '2014-01-01 23:59:59'
#実行結果1.004 秒

#インデックスあり
SELECT *
FROM item 
WHERE created_date BETWEEN '2014-01-01 00:00:00' AND '2014-01-01 23:59:59'
#実行結果0.044 秒

EXPLAINで調べたところtypeはrangeになっていたのでインデックスはきいていることが確認でき、20倍ほど速度が改善された。

datetime型のデータを格納する際の小技

他者が作成したログを見てみるとdatetime型のデータが分単位で格納されていた。具体的には「12時00分00秒～12時00分59秒」のデータは全て「12時00分00秒」として格納されていた。

上記方法を取ることでインデックスの効果を高めていると思われる(インデックス数が減るため)。

1秒単位で正確にログを残す必要がある場合はダメだけど、そこまでシビアじゃない場合は上記のような小技が使えそう。

注意点

大量データに後からインデックスを張る場合はサイトはメンテナンスモードにしてMySQLにクエリが入らない状態にして行った方が良さそう。テーブルロックがかかるようで更新ができないし、killで強制終了した場合はデータが破損する可能性がある。