SQLite3最適化 like, glob

　インデックスを使って検索する方法。

成果物

• github

情報源

• https://www.sqlite.org/optoverview.html#the_like_optimization

方法

　likeやglobはbetweenのようにインデックスを使って範囲検索できる。だが、それは限らた条件のときだけ。詳細は上記参照。注目すべきは以下。

1. パターンがワイルドカードで始まらないこと（前方一致のときのみインデックスを使える）
2. glob演算子の場合、組込のbinary照合シーケンスを使用して列にインデックスを付ける必要がある
3. like演算子の場合
   • case_sensitive_like
     • true: 組込のbinary照合シーケンスを使用して列のインデックスを作成する必要がある
     • false: 組込のnocase照合シーケンスを使用して列のインデックスを作成する必要がある
   • escapeオプションを使用する場合、エスケープ文字はASCII、またはUTF-8のシングルバイト文字でなければならない

やってみる

　最初に検索の要件を考える必要がある。たとえば大文字・小文字の区別をしない検索だけを要件にするなら以下。

create table T(A text collate nocase);
create index T_A on T(A);
insert into T values('1A');
insert into T values('11A');
insert into T values('11A1');
insert into T values('2A');
insert into T values('2B');
pragma case_sensitive_like = fasel;
select A from T where A like '_A';

　もし区別するパターンでも高速にしたいなら、そのインデックスを作成することになる。ただし、ほぼ同じデータが量産されることになる。必要なディスク容量が増えてしまう。よってそれは省略する。

　検索方法はlike句を使う。globは使わない。区別する検索しかできないためインデックスが使えないから。

　区別しないのでcollateをnocaseにする。また、like句も区別しないよう以下のように設定する（デフォルト値だが念の為）。

PRAGMA case_sensitive_like = false;

　インデックスが使われているか確認する。前方一致のときのみ使える。

explain query plan select A from T where A like 'A_';

QUERY PLAN
`--SEARCH TABLE T USING COVERING INDEX T_A (A>? AND A<?)

　もちろん完全一致でも使える。だがlike句を使う意味がない。=比較と同じ。

explain query plan select A from T where A like 'A';

QUERY PLAN
`--SEARCH TABLE T USING COVERING INDEX T_A (A>? AND A<?)

　だが、後方一致、部分一致のときはインデックスが使われない。全表スキャンになってしまう。

explain query plan select A from T where A like '_A';

QUERY PLAN
`--SCAN TABLE T

explain query plan select A from T where A like '_A_';

QUERY PLAN
`--SCAN TABLE T

　なお、globはインデックスが一切使われない。インデックスのソート順がnocaseに準じているから。globはbinary順しかサポートされておらず、その順に並んでいないインデックスは役に立たないため。

explain query plan select A from T where A glob 'A?';

QUERY PLAN
`--SEARCH TABLE T USING COVERING INDEX T_A (A>? AND A<?)

explain query plan select A from T where A glob '?A';

QUERY PLAN
`--SCAN TABLE T

explain query plan select A from T where A glob '?A?';

QUERY PLAN
`--SEARCH TABLE T USING COVERING INDEX T_A (A>? AND A<?)

　ところで、どう考えても最も使うのは「部分一致」だろう。それなのにインデックスが使えない。これは痛い。likeやglobのためにインデックスを作っても費用対効果は低いかもしれない。どうせ遅いならもっと汎用性のあるregexpか、キーワードを増やすFTSでも使ったほうが良いかも？

対象環境

• 2019-09-04
• Raspbierry pi 3 Model B+
• Raspbian stretch 9.0 2018-11-13
• bash 4.4.12(1)-release
• SQLite 3.29.0
• MeCab 0.996ユーザ辞書

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで

• SQLite3学習　俯瞰まとめ
• SQLite3学習　環境構築まとめ
• SQLite3学習　インタフェースまとめ（C言語、CLI、対話モード、Tcl...）
• SQLite3学習　ドットコマンドまとめ
• SQLite3学習　JSON拡張まとめ
• SQLite3学習　FTSまとめ（ICU, MeCab）
• SQLite3学習　再帰クエリ（WITH RECURSIVE）
• SQLite3学習　R-Treeモジュール
• SQLite3学習　Geopoly（2次元ベクタ画像の生成）
• SQLite3学習　拡張関数（generate_series）
• SQLite3学習　拡張ライブラリ数学関数（extension-functions.c）
• SQLite3学習　謎と名前
• SQL文の分類（DDL,DML,TCL,DCL）
• SQL構文　alter（rename）
• SQL構文　alter（add column）概要
• SQL構文　alter（add column）制約
• SQL構文　alter（add column）sqlite_master変更しても反映されない
• SQL構文　alter（add column）スキーマ再定義（テーブル再作成による定義変更）
• SQL構文　analyze
• SQL構文　attach/detach
• SQLite3構文　begin,end,commit,rollback,savepoint（deferred,immediate,exclusive）
• SQLite3構文　コメント
• SQLite3構文　create/drop
• SQLite3構文　index（create/drop）
• SQLite3構文　table（create/drop）
• SQLite3構文　列制約（default）
• SQLite3構文　列制約（collate）
• SQLite3構文　列制約（primary key）
• SQLite3構文　列制約（primary key）ベストプラクティス
• SQLite3構文　列制約（unique）
• SQLite3構文　列制約（not null）
• SQLite3構文　列制約（check）
• SQLite3構文　列制約（foreign key references）
• SQLite3構文　表制約（primary key, unique, check, foreign key）
• SQLite3でメタデータを取得する方法（DB名(スキーマ名)、テーブル名、列名、制約）
• SQLite3でTEMPの保存先を指定する
• SQLite3構文　delete
• SQLite3ビルド失敗（SQLITE_ENABLE_UPDATE_DELETE_LIMIT）
• SQLite3をソースからビルドする（SQLITE_ENABLE_UPDATE_DELETE_LIMIT）
• SQLite3構文　delete（limit offset, order by）
• SQLite3クエリプランニング（インデックスの働き）
• SQLite3構文　explain
• SQLite構文　expression
• SQLite構文　expression（リテラル）
• SQLite構文　expression（パラメータ）
• SQLite構文　expression（演算子）
• SQLite構文　expression（in 句）
• SQLite構文　expression（like 句）
• SQLite構文　expression（glob 句）