研究でSQLiteを使ってる。ファイル1つでポータブル、受け渡しも楽だしバージョン管理もできるし割りと気に入ってる。
日付のデータを大量に扱うのが遅くて困ったので速くする方法を調べた。ちゃんとインデックスが効くようにしたい。
SQLite3 には、SQL99 の DATE や DATETIME に対応する日時を表す型は存在しません。SQLite3 で日時を扱う場合、Date And Time Functions で説明されている日付処理関数を使用し、TEXT 型や NUMERIC 型の列に日付データを作成します。
http://www.tamandua-webtools.net/sqlite3-date.html
TEXTを使う場合では、YYYY-MM-DD HH:MM:SSみたいな書式の文字列を日付とみなす。
NUMERIC(INTEGER or REAL)はよくあるUnixエポックからの差で表す。
すでにあるDBのスキーマと合わせるためにTEXT型でやることにする。
日本時間しか使わないし生クエリを書きやすいのはいいけど、実際使う時に文字列をパースしてTime型みたいなのにするんだから最初から数字のほうがいいと思う…。
実際使うデータを模してこんなスキーマでやる。文字列とそれを記録した時刻が大量に並んでる。
CREATE TABLE text_datetime ( message text, date_time text ); CREATE INDEX text_datetime_idx on text_datetime(date_time);
今回はSQLite3.7.7.1、messageカラムはTimeをto_sしたやつで代用した。
100万件レコード生成に使ったやつ。
インデックスが効いてるかどうか確認する
EXPLAIN [query] だとかろうじで雰囲気は伝わってくるけどよく分からない、勘で読む感じになる。
sqlite> explain select * from text_datetime where '2011-11-11 00:00:00' < date_time limit 100; 0|Trace|0|0|0||00| 1|Integer|100|1|0||00| 2|String8|0|2|0|2011-11-11 00:00:00|00| 3|Goto|0|20|0||00| 4|OpenRead|0|2|0|2|00| 5|OpenRead|1|3|0|keyinfo(1,BINARY)|00| 6|Copy|2|3|0||00| 7|SeekGt|1|17|3|1|00| 8|Column|1|0|4||00| 9|IsNull|4|16|0||00| 10|IdxRowid|1|4|0||00| 11|Seek|0|4|0||00| 12|Column|0|0|5||00| 13|Column|1|0|6||00| 14|ResultRow|5|2|0||00| 15|IfZero|1|17|-1||00| 16|Next|1|8|0||00| 17|Close|0|0|0||00| 18|Close|1|0|0||00| 19|Halt|0|0|0||00| 20|Transaction|0|0|0||00| 21|VerifyCookie|0|2|0||00| 22|TableLock|0|2|0|text_datetime|00| 23|Goto|0|4|0||00|
単にSQLiteで投げたクエリにインデックスが使われてるかどうかを確認するにはEXPLAIN QUERY PLAN*1を使う。
sqlite> explain query plan select * from text_datetime where '2011-11-11 00:00:00' <= date_time; 0|0|0|SEARCH TABLE text_datetime USING INDEX text_datetime_idx (date_time>?) (~250000 rows)
USING INDEX とか言ってるので使われてるんだと思います。
LIKEで探す
柔軟にパターンが指定できるけど普通インデックスが効かない*2。
便利な雰囲気を出してるけど遅いから騙されてはいけない。
LIKEでインデックスを効かせるにはコンパイルオプション&PRAGMAが要る、Macでbrew install sqliteしたやつなら使えるけどWindowsの人に渡して使うのでよく分からないし頼らないほうがいいと思う。
sqlite> explain query plan select * from text_datetime where date_time like '2011-11-11 %'; 0|0|0|SCAN TABLE text_datetime (~500000 rows) sqlite> explain query plan select * from text_datetime where date_time like '20__-11-11 15:%%'; 0|0|0|SCAN TABLE text_datetime (~500000 rows)
GLOBで探す
インデックス効くことが多い。
各年の11月11日のおやつの時間の記録を取ろうとしてもLIKEはインデックスが効かないけど、GLOBなら高速に取ってこれる。
sqlite> explain query plan select * from text_datetime where date_time glob '2011-11-11 *'; 0|0|0|SEARCH TABLE text_datetime USING INDEX text_datetime_idx (date_time>? AND date_time<?) (~30000 rows) sqlite> explain query plan select * from text_datetime where date_time glob '20??-11-11 15:*'; 0|0|0|SEARCH TABLE text_datetime USING INDEX text_datetime_idx (date_time>? AND date_time<?) (~30000 rows) sqlite> explain query plan select * from text_datetime where date_time glob '20*-11-11 15:*'; 0|0|0|SEARCH TABLE text_datetime USING INDEX text_datetime_idx (date_time>? AND date_time<?) (~30000 rows) sqlite> explain query plan select * from text_datetime where date_time glob '*-11-11 15:*'; 0|0|0|SCAN TABLE text_datetime (~500000 rows) sqlite> explain query plan select * from text_datetime where date_time not glob '2011-11-11 *'; 0|0|0|SCAN TABLE text_datetime (~500000 rows)
前方一致じゃなきゃ効かないと思ってたけど、最初にワイルドカードがこなければ効いてるみたい。
notとか使うと効かなくなる。20*-11-11 15:*に効く理屈がよく分からない、気になる。
Betweenで探す
特定の範囲の時間、たとえばおやつを食べた前後15分とかを取ってくるのに使う。
インデックスが効く。
sqlite> explain query plan select * from text_datetime where date_time between '2011-11-11 14:45:00' and '2011-11-11 15:15:00'; 0|0|0|SEARCH TABLE text_datetime USING INDEX text_datetime_idx (date_time>? AND date_time<?) (~30000 rows) sqlite> explain query plan select * from text_datetime where '2011-11-11 14:45:00' <= date_time and date_time <= '2011-11-11 15:15:00' 0|0|0|SEARCH TABLE text_datetime USING INDEX text_datetime_idx (date_time>? AND date_time<?) (~60000 rows)
<, <=, >, >= で探してもインデックスが効く、当然かな
計測
LIKE GLOB BETWEENである日付中のレコードを全部とってくる * 10回をやる。
意図したクエリが吐かれるように生クエリを書く。
リハーサル的なの消して見やすく改行した結果がこちら
### case_sensitive_like なし
user system total real
LIKE 5.300000 1.320000 6.620000 ( 6.740396)
GLOB 0.050000 0.020000 0.070000 ( 0.064181)
BETWEEN 0.050000 0.010000 0.060000 ( 0.058485)
### case_sensitive_like あり
user system total real
LIKE 0.050000 0.010000 0.060000 ( 0.062666)
GLOB 0.050000 0.020000 0.070000 ( 0.064416)
BETWEEN 0.050000 0.010000 0.060000 ( 0.060358)
### インデックスなし
user system total real
LIKE 5.330000 1.330000 6.660000 ( 7.124761)
GLOB 5.330000 1.320000 6.650000 ( 6.787246)
BETWEEN 3.790000 1.250000 5.040000 ( 5.283088)
まとめ
時刻を範囲検索したいならインデックス張ってBETWEENとか <, <=, >, >= で探す。
パターンマッチしたいならLIKEよりGLOBを使ったほうがよくわからない他人の環境でもパフォーマンスを保ちやすい。
この本、大規模じゃなくても異常にためになる。インターン前に読むべきだった。
おまけ
大量にINSERTする
1万件INSERTする。
transactionを使ってまとめてINSERTしたほうが速い、しないと目を疑うぐらい遅い。
transaction あり
user system total real
2.060000 0.020000 2.080000 ( 2.161490)
transaction なし
user system total real
2.720000 4.490000 7.210000 ( 15.599953)
ruby insert_datetime.rb 5.12s user 4.55s system 53% cpu 18.159 total
transaction使わずに最初100万件やろうとして異常に時間かかって10万件にしたけど1時間ぐらいかかった。
transaction使ったら100万件4分ぐらいでできた。
あと、後でインデックス張るほうがトータルで早いようです。
