SQLite3 のインデックスと検索速度
Rails + SQLite3 を使って、 DB のレコードを検索する場合の速度を測った。 調べたのは、インデックスを張る前後でどれくらい速度が変わるかということ。
インデックスを張らない場合
まず、普通にモデルを作り、インデックスを張らない状態で検索する。
- データは10万件程度。
- 検索回数は100回。
- 検索は完全一致と前方一致 (LIKE 演算) の2種類。
- 実行環境は VMware 上の Debian sarge 。 SQLite は 3.2.1 。
前方一致検索のために、 search_by_name メソッドを作成している。
class Item def self.search_by_name(name) if name.blank? [] else find(:all, :conditions => ["name like ?", name + '%']) end end end
時間の計測には、 Rails の benchmarker を使った。
$ script/performance/benchmarker 100 'Item.find_by_name(Item.find(:first).name)' user system total real #1 8.910000 6.420000 15.330000 ( 15.448537) $ script/performance/benchmarker 100 'Item.search_by_name(Item.find(:first).name)' user system total real #1 10.730000 6.520000 17.250000 ( 39.949587)
結果、完全一致で15秒、前方一致で40秒だった。
インデックスを張った場合
次に、検索対象のレコードにインデックスを張る。 マイグレートファイルを以下のように作る。
class AddItemsNameIndex < ActiveRecord::Migration def self.up add_index :Items, :name end def self.down remove_index :Items, :name end end
マイグレートを実行し、インデックスを張る。
$ rake migrate
完全一致と前方一致でもう一度計測する。
$ script/performance/benchmarker 100 'Item.find_by_name(Item.find(:first).name)' user system total real #1 0.030000 0.140000 0.170000 ( 0.164136) $ script/performance/benchmarker 100 'Item.search_by_name(Item.find(:first).name)' user system total real #1 11.320000 6.780000 18.100000 ( 36.684580)
完全一致は0.16秒と、約1/100になった。 一方、前方一致の場合は36秒と、ほとんど変わらない。
とりあえずのまとめ
こんな結果になった。
インデックス | 完全一致 | 前方一致 |
無し | 15.4秒 | 39.9秒 |
有り | 0.16秒 | 36.7秒 |
調べてみると、sqliteのLIKE演算でインデックスを使う方法 に情報があった。 「LIKE '/foo/bar/%'のように%が最後 」の LIKE 演算には、デフォルトではインデックスは使われないらしい。 コンパイル時に -DSQLITE_CASE_SENSITIVE_LIKE を指定すればいいらしいけど、実行時に指定する方法は無いのかな。 The SQLite Query Optimizer Overviewを見ると、 PRAGMA case_sensitive_like=ON; とすればいいらしいけど、どこでやればいいか分からない(普通に実行しても結果は変わらなかった)。