ファイルサイズと検索速度のトレードオフ設定方法。
成果物
情報源
FTS5用テーブル作成
ふつうのテーブルは以下のように定義する。
CREATE TABLE posts (id int primary key, title text, content text);
FTS5用のテーブルは以下のように定義する。
CREATE VIRTUAL TABLE posts USING fts5(title, content);
特徴は以下の[]
でくくった部分。
CREATE [VIRTUAL] TABLE posts [USING fts5](title, content);
また、以下のような特徴もある。
- 型は定義できない(すべて
text
型となる) - 列名として定義できない名前:
rowid
,rank
, テーブル名
列オプション
列の動作を変更するオプション。今の所UNINDEXED
のみ。
UNINDEXED
UNINDEXED
列オプションで修飾された列の内容は、FTSインデックスに追加されない。これは、MATCHクエリとFTS5補助関数の目的で、列に一致するトークンが含まれていないことを意味する。
たとえばuuid
列の内容をFTSインデックスに追加せぬようにするには以下コード。
CREATE VIRTUAL TABLE customers USING fts5(name, addr, uuid UNINDEXED);
使いどころは? おそらくFTSクエリによる検索ではなくSQLクエリによる検索をする列に対してつけるものだと思われる。
設定オプション
prefix index
デフォルトでは、FTS5はドキュメントセット内の各トークンインスタンスの場所を記録する単一のインデックスを保持する。つまり、1回のルックアップが必要なため、完全なトークンのクエリは高速だが、範囲スキャンが必要なため、プレフィックストークンのクエリは低速になる可能性がある。たとえば、プレフィックストークンabc*
を照会するにはabc
以上abd
未満のすべてのトークンの範囲スキャンが必要。
プレフィックスインデックスは、プレフィックストークンのクエリを高速化するために使用される文字で特定の長さのプレフィックストークンのすべてのインスタンスの場所を記録する個別のインデックスである。たとえば、プレフィックストークンabc*
のクエリを最適化するには、3文字のプレフィックスのプレフィックスインデックスが必要。
FTS5テーブルにプレフィックスインデックスを追加するにはprefix
オプションを単一の正の整数または1つ以上の正の整数値の空白区切りリストを含むテキスト値に設定する。指定された整数ごとにプレフィックスインデックスが作成される。単一のCREATE VIRTUAL TABLE
ステートメントの一部として複数の「プレフィックス」オプションが指定されている場合、すべてが適用される。
-- Two ways to create an FTS5 table that maintains prefix indexes for -- two and three character prefix tokens. CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3'); CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix=2, prefix=3);
使いどころは? おそらくプレフィクス検索を頻繁にするテーブルに対して設定する。ただしインデックス増大によりファイルサイズも増えるだろう。
tokenizer
トークナイザを指定する。トークナイザはテーブル内のテキストをトークンに分解する。
-- The following are all equivalent CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter ascii'); CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = "porter ascii"); CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = "'porter' 'ascii'"); CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = '''porter'' ''ascii'''); -- But this will fail: CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = '"porter" "ascii"'); -- This will fail too: CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter' 'ascii');
組込のトークナイザは以下3つ。
unicode61
: デフォルト。ascii
: ASCIIコードポイント(0〜127)以外をトークンとするporter
: ポーターステミングアルゴリズムに基づく
使いどころは? むしろFTSはトークナイザ次第で決まる。MeCabのように日本語を区切りトークンにしてくれると嬉しいのだが、組込には存在しない。組込トークナイザは英語用だったり1文字単位だったりで、その用途にしか使えない。
unicode61
トークナイザ
オプション | デフォルト値 | 値の範囲 | 概要 |
---|---|---|---|
remove_diacritics |
1 |
0 ,1 ,2 |
発音記号の削除。0 :しない。1 :バグにより一部削除せず。2 :正しく削除 |
categories |
L* N* Co |
Unicode character category | トークン文字に対応するUnicodeカテゴリを設定する。 |
tokenchars |
空白文字や句読点であってもトークンとみなす文字の設定。 | ||
separators |
トークン文字であっても区切り文字とみなす文字の設定。 |
設定コード例は以下。
-- Create an FTS5 table that does not remove diacritics from Latin -- script characters, and that considers hyphens and underscore characters -- to be part of tokens. CREATE VIRTUAL TABLE ft USING fts5(a, b, tokenize = "unicode61 remove_diacritics 0 tokenchars '-_'" );
-- Create an FTS5 table that, as well as the default token character classes, -- considers characters in class "Mn" to be token characters. CREATE VIRTUAL TABLE ft USING fts5(a, b, tokenize = "unicode61 categories 'L* N* Co Mn'" );
使いどころは? 1文字単位で区切りたいときにtokenchars
,separators
を使えそう? だがそれは全文検索として価値ある使い方なのかわからない。
ascii
トークナイザ
以下をのぞきunicode61
と似ている。
- すべての非ASCII文字は、常にトークン文字と見なされる
- 大文字と小文字の区別は、ASCII文字に対してのみ実行される。
A
=a
,Ã
≠ã
- remove_diacriticsオプションはサポートせず
以下は数字をトークンとするコード例。
-- Create an FTS5 table that uses the ascii tokenizer, but does not -- consider numeric characters to be part of tokens. CREATE VIRTUAL TABLE ft USING fts5(a, b, tokenize = "ascii separators '0123456789'" );
使いどころは? 英文なら使える?
porter
トークナイザ
英語用。correction
などの検索ワードをcorrected
やcorrecting
などの類語に一致させることができる。
-- Two ways to create an FTS5 table that uses the porter tokenizer to -- stem the output of the default tokenizer (unicode61). CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = porter); CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter unicode61'); -- A porter tokenizer used to stem the output of the unicode61 tokenizer, -- with diacritics removed before stemming. CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter unicode61 remove_diacritics 1');
使いどころは? 英文なら使える?
外部コンテンツとコンテンツレステーブル
通常、行がFTS5テーブルに挿入されると、さまざまなフルテキストインデックスエントリおよびその他のデータとともに、行のコピーがFTS5モジュールによって管理されるプライベートテーブルに保存される。ユーザーまたは補助関数の実装によってFTS5テーブルから列の値が要求されると、このプライベートテーブルから読み取られる。content
オプションを使用して、FTSフルテキストインデックスエントリのみを格納するFTS5テーブルを作成できる。列の値自体は通常、関連するフルテキストインデックスエントリよりもはるかに大きいため、これによりデータベースのスペースを大幅に節約できる。
content
オプションを使用するには2つの方法がある。
- 空の文字列に設定して、コンテンツのないFTS5テーブルを作成する。この場合、FTS5は、クエリを処理するときに元の列の値が利用できないと想定する。フルテキストクエリと一部の補助関数は引き続き使用できるが、rowid以外の列値をテーブルから読み取ることはできない。
- FTS5が列の値を取得するためにいつでも照会できるデータベースオブジェクト(テーブル、仮想テーブル、またはビュー)の名前に設定する。これは「外部コンテンツ」テーブルとして知られている。この場合、すべてのFTS5機能を使用できるが、フルテキストインデックスの内容が指定されたデータベースオブジェクトと一致することを確認するのはユーザーの責任。そうでない場合、クエリ結果は予測できない場合がある。
コンテンツレステーブル
コンテンツのないテーブルを作成する。設定オプションcontent=''
とすることで。
CREATE VIRTUAL TABLE f1 USING fts5(a, b, c, content='');
UPDATE
、DELETE
、またはROWID
フィールドにNULL
以外の値を提供しないINSERT
ステートメントをサポートせずREPLACE
競合処理をサポートせずREPLACE
およびINSERT OR REPLACE
ステートメントは、通常のINSERT
ステートメントとして扱われる- FTS5 deleteコマンドを使用して、コンテンツのないテーブルから行を削除できる
ROWID
以外の列値を読み取ろうとするとNULL
値が返る
以上、説明おわり。以下、検証。
レコードを挿入し、検索してみると、データが一切入っていないことがわかる。
sqlite> insert into f1 values('AAA','BBB','CCC'); sqlite> select * from f1; || sqlite> select * from f1('AAA'); || sqlite> select * from f1 where f1 match 'AAA'; || sqlite> select * from f1 where f1 match 'a:AAA'; ||
他の自動作成されるテーブルも見てみる。
sqlite> select * from sqlite_master; table|f1|f1|0|CREATE VIRTUAL TABLE f1 USING fts5(a, b, c, content='') table|f1_data|f1_data|7|CREATE TABLE 'f1_data'(id INTEGER PRIMARY KEY, block BLOB) table|f1_idx|f1_idx|8|CREATE TABLE 'f1_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID table|f1_docsize|f1_docsize|9|CREATE TABLE 'f1_docsize'(id INTEGER PRIMARY KEY, sz BLOB) table|f1_config|f1_config|10|CREATE TABLE 'f1_config'(k PRIMARY KEY, v) WITHOUT ROWID
sqlite> select * from f1; || sqlite> select * from f1_data; 1| 10| 137438953473| sqlite> select * from f1_idx; 1||2 sqlite> select * from f1_docsize; 1| sqlite> select * from f1_config; version|4
挿入したコンテンツAAA
,BBB
,CCC
はどこにもない。
使いどころは? わからない。インデックスデータだけあって何の意味がある? 検索でヒットしてもデータが無いならどうしようもない。肝心のデータが削除されてしまってはスペースの節約もクソもないと思うのだが。
外部コンテンツテーブル
外部コンテンツFTS5テーブルは、コンテンツオプションを同じデータベース内のテーブル、仮想テーブル、またはビュー(以下「コンテンツテーブル」)の名前に設定することによって作成される。FTS5で列の値が必要になると、値が必要な行のROWIDをSQL変数にバインドして、次のようにコンテンツテーブルを照会する。
SELECT <content_rowid>, <cols> FROM <content> WHERE <content_rowid> = ?;
上記では、<content>
はコンテンツテーブルの名前に置き換えられます。デフォルトでは、<content_rowid>
はリテラルテキストrowid
に置き換えられる。または、content_rowid
オプションがCREATE VIRTUAL TABLE
ステートメント内で、そのオプションの値によって設定されている場合。<cols>
は、FTS5テーブルの列名のコンマ区切りリストに置き換えられる。
-- If the database schema is: CREATE TABLE tbl (a, b, c, d INTEGER PRIMARY KEY); CREATE VIRTUAL TABLE fts USING fts5(a, c, content=tbl, content_rowid=d); -- Fts5 may issue queries such as: SELECT d, a, c FROM tbl WHERE d = ?;
コンテンツテーブルは、次のようにクエリすることもできます。
SELECT <content_rowid>, <cols> FROM <content> ORDER BY <content_rowid> ASC; SELECT <content_rowid>, <cols> FROM <content> ORDER BY <content_rowid> DESC;
外部コンテンツFTS5テーブルのコンテンツがコンテンツテーブルで最新の状態に保たれるようにすることは、依然としてユーザーの責任です。これを行う1つの方法は、トリガーを使用することです。例えば:
-- Create a table. And an external content fts5 table to index it. CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c); CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a'); -- Triggers to keep the FTS index up to date. CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c); END; CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c); END; CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c); INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c); END;
- REPLACE競合処理をサポートせず
- REPLACE競合処理を指定する操作は、
ABORT
を使用する
以上、説明おわり。以下、検証。
上記テーブルとトリガーを作成したあと、以下のようにデータ挿入してみる。
insert into tbl(b,c) values('BBB', 'CCC');
ちゃんとデータが入っていることを以下で確認。
sqlite> select * from tbl; 1|BBB|CCC
tbl
はFTS5テーブルではないため、FTSクエリを使ってもエラーになる。
sqlite> select * from tbl('BBB'); Error: 'tbl' is not a function sqlite> select * from tbl where tbl = 'BBB'; Error: no such column: tbl
トリガーによってFTSテーブルに追加されたはず。確認してみる。
table|tbl|tbl|7|CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c) table|fts_idx|fts_idx|0|CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a') table|fts_idx_data|fts_idx_data|8|CREATE TABLE 'fts_idx_data'(id INTEGER PRIMARY KEY, block BLOB) table|fts_idx_idx|fts_idx_idx|9|CREATE TABLE 'fts_idx_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID table|fts_idx_docsize|fts_idx_docsize|10|CREATE TABLE 'fts_idx_docsize'(id INTEGER PRIMARY KEY, sz BLOB) table|fts_idx_config|fts_idx_config|11|CREATE TABLE 'fts_idx_config'(k PRIMARY KEY, v) WITHOUT ROWID
sqlite> select * from fts_idx; BBB|CCC sqlite> select * from fts_idx_data; 1| 10| 137438953473| sqlite> select * from fts_idx_idx; 1||2 sqlite> select * from fts_idx_docsize; 1| sqlite> select * from fts_idx_config; version|4
使いどころは? デフォルトのFTS用テーブル名を変更するってことかな?
columnsize
通常、FTS5は、個別のテーブルのメインFTS5テーブルに挿入されたトークンの各列値のサイズを格納する特別なバッキングテーブルをデータベース内に保持する。このバッキングテーブルは、 xColumnSize API関数によって使用され、組み込みのbm25ランキング関数によって使用される(他のランキング関数にも役立つ可能性がある)。
スペースを節約するために、columnsizeオプションをゼロに設定することにより、このバッキングテーブルを省略できる。
-- ディスクに保存されたxColumnSize()値のないテーブル: CREATE VIRTUAL TABLE ft USING fts5(a, b, c, columnsize=0); -- ディスク上にxColumnSize()値を格納するテーブルを作成する3つの同等の方法: CREATE VIRTUAL TABLE ft USING fts5(a, b, c); CREATE VIRTUAL TABLE ft USING fts5(a, b, c, columnsize=1); CREATE VIRTUAL TABLE ft USING fts5(a, b, columnsize='1', c);
columnsize
オプションを0
または1
以外の値に設定するとエラーになる
FTS5テーブルがcolumnsize=0
で構成されているが、コンテンツのないテーブルではない 場合、xColumnSize API関数は引き続き機能するが、実行速度ははるかに遅くなる。この場合、値を読み取ってデータベースから直接返すのではなく、テキスト値自体を読み取り、オンデマンドでその中のトークンをカウントする。
または、テーブルもコンテンツのないテーブルである場合、以下が適用されます。
- xColumnSize APIは常に-1を返す。
columnsize=0
で構成されたコンテンツのないFTS5テーブル内に格納されている値のトークンの数を決定する方法はない。 - 挿入された各行には、明示的に指定された
ROWID
値が必要。contentsize
テーブルがcolumnsize=0
で構成されている場合、ROWID
にNULL
値を挿入しようとすると、SQLITE_MISMATCH
エラーになる - テーブルのすべてのクエリは、フルテキストクエリである必要がある。つまり、テーブル名列を左オペランドとして
MATCH
または=
演算子を使用するか、テーブル値関数構文を使用する必要がある。フルテキストクエリではないクエリはエラーになる。
(columnsize=0
が指定されていない限り)xColumnSize
値が格納されるテーブルの名前は<name> _docsize
である。ここで、<name>
はFTS5テーブル自体の名前である。sqlite3_analyzer
のツールは、columnsize=0
を使用してFTS5テーブルを再作成することによって保存される可能性がある。スペースを決定するために、既存のデータベースで使用することができる。
使いどころは? 関連性順にソートしないときファイルサイズ減量したければ使う。
detail
ドキュメント内の各用語について、FTS5によって維持されるFTSインデックスには、ドキュメントのROWID
、用語を含む列の列番号、および列値内の用語のオフセットが格納される。detail
オプションを使用して、この情報の一部を省略できる。これにより、データベースファイル内でインデックスが消費するスペースが削減されるが、システムの機能と効率も低下する。
詳細オプションはfull
(デフォルト)、column
、none
。
-次の2行は同等。「detail」のデフォルト値が「full」であるため CREATE VIRTUAL TABLE ft1 USING fts5(a, b, c); CREATE VIRTUAL TABLE ft1 USING fts5(a, b, c, detail=full); CREATE VIRTUAL TABLE ft2 USING fts5(a, b, c, detail=column); CREATE VIRTUAL TABLE ft3 USING fts5(a, b, c, detail=none);
detail=column
のとき、FTSインデックスは各用語に対してROWID
と列番号のみを記録し、用語オフセット情報を省略する。これにより、次の制限が生じる。
NEAR
クエリは使用不可- フレーズクエリは使用不可
- テーブルもないと仮定すると、contentless
テーブル、
xInstCount、
xInst、
xPhraseFirstと
xPhraseNext`は通常より遅くなる。FTSインデックスから必要なデータを直接読み取るのではなく、オンデマンドでドキュメントテキストを読み込んでトークン化する必要があるため - テーブルもコンテンツのないテーブルである場合、
xInstCount
、xInst
、xPhraseFirst
、およびxPhraseNext
APIは、現在の行に一致するフレーズがまったく含まれていないように動作する(xInstCount()
は0
を返す)
詳細オプションがnone
に設定されている場合、各用語に対してFTSインデックスはROWID
のみを記録します。列とオフセットの両方の情報は省略されます。detail=column
モードの上記の制限に加えて、これには次の追加の制限があります。
- 列フィルタークエリは使用できません。
- テーブルもコンテンツのないテーブルではないと仮定すると、 xPhraseFirstColumnと xPhraseNextColumnは通常より遅くなります。
- テーブルもコンテンツのないテーブルである場合、xPhraseFirstColumnおよびxPhraseNextColumn APIは、現在の行に一致するフレーズがまったく含まれていないように動作します(つまり、xPhraseFirstColumn()は反復子をEOFに設定します)。
電子メールの大規模なセット(ディスク上で1636 MiB)のインデックスを作成した1つのテストでは、FTSインデックスはdetail=full
のディスクで743 MiB、detail=column
で340 MiB、detail=none
で134 MiBだった。
使いどころは? わからない。トークンに完全一致したらヒットさせるだけでいいときはファイルサイズを減らすことができる、ということだろう。AND
やOR
も使えないとなると、かなり厳しい。
所感
有効なトークナイザを作らねばFTSは役に立たない。仮にできたとしても、本当にそれが最善であるかはわからない。無駄なキーワードをインデックスとして作りまくれば、無駄にファイルサイズを圧迫する。
FTSのトークナイザは検索ワードの自動抽出処理である。
もっと厳選した意味付けをすることで、ファイルサイズを削減しつつ、理解しやすく探せる気がする。それは全文検索ではなくカテゴライズやタギング、メタデータによる検索になるだろう。
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13
- bash 4.4.12(1)-release
- SQLite 3.29.0
$ uname -a Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux
前回まで
- SQLite3学習をはじめよう
- SQLite3学習 SQLiteについて
- SQLite3学習 SQLiteの適切な用途
- SQLite3学習 SQLiteの特徴
- SQLite3学習 SQLiteのクセ
- SQLite3学習 データ型とアフィニティ
- SQLite3学習 演算子の一覧
- SQLite3学習 よくある質問
- SQLite3学習 SQLiteダウンロード&コンパイル
- SQLite3学習 Tclで操作する
- SQLite3学習 ビルドオプション動作確認(SQLITE_ALLOW_URI_AUTHORITY)
- SQLite3学習 面白そうなコンパイルオプション
- SQLite3学習 SQLiteの拡張について
- SQLite3学習 JSON拡張
- SQLite3学習 JSON拡張(json_extract)
- SQLite3学習 JSON拡張(json_each)
- SQLite3学習 JSON拡張(json_tree オブジェクト→行)
- SQLite3学習 JSON拡張(json_tree オブジェクトツリー→行)
- SQLite3学習 JSON拡張(json_tree オブジェクト配列→行)
- SQLite3学習 JSON拡張(json_group_array 行→配列)
- SQLite3学習 JSON拡張(json_group_object 行→オブジェクト)
- SQLite3学習 JSON拡張(json_array_length)
- SQLite3学習 JSON拡張(json_type)
- SQLite3学習 JSON拡張(json_valid)
- SQLite3学習 JSON拡張(json_quote)
- SQLite3学習 JSON拡張(json_array)
- SQLite3学習 JSON拡張(json_object)
- SQLite3学習 JSON拡張(json_patch)
- SQLite3学習 JSON拡張(json_insert)
- SQLite3学習 JSON拡張(json_replace)
- SQLite3学習 JSON拡張(json_set)
- SQLite3学習 JSON拡張(json_remove)
- SQLite3学習 全文検索(FTS5)
- SQLite3学習 全文検索FTSを日本語で使う方法を調べてみた
- 形態素解析MeCabをインストールする
- SQLite3学習 全文検索FTS5のMeCab用トークナイザを実装する
- SQLite3学習 FTS5+MeCabでクエリ構文