Wyszukiwanie pełnotekstowe w SQLite: tabele wirtual…

Q: Czym MATCH różni się od LIKE w SQLite?

LIKE liniowo przeszukuje podciągi i ignoruje granice słów. MATCH korzysta z indeksu odwróconego FTS5, więc jest szybki w dużych tabelach i rozumie tokeny, zapytania po prefiksie (term*), operatory logiczne (AND, OR, NOT) i wyszukiwanie fraz ("exact phrase"). MATCH działa tylko na wirtualnych tabelach FTS.

Q: Jak ustalić ranking wyników wyszukiwania pełnotekstowego w SQLite?

FTS5 udostępnia ukrytą kolumnę rank, która zwraca wynik BM25 (im niższy, tym lepiej). Sortuj bezpośrednio po niej: ORDER BY rank. Możesz też wywołać bm25(table), żeby jawnie dostać wynik, albo podać wagi kolumn, np. bm25(posts, 10.0, 1.0), żeby tytuł ważył więcej niż treść.

`LIKE` się nie skaluje

Przy wyszukiwaniu tekstu w SQLite pierwszym odruchem jest zwykle LIKE '%word%'. Działa w małych tabelach i rozpada się w dużych. Żaden indeks tu nie pomoże: SQLite musi przejrzeć każdy wiersz, zamienić go na małe litery i sprawdzić, czy zawiera podciąg. Granice słów, ranking, zapytania z wieloma słowami i dopasowanie po prefiksie musisz zaimplementować sam.

FTS5 to wbudowana odpowiedź. To typ tabeli wirtualnej, który utrzymuje indeks odwrócony nad kolumnami tekstowymi, rozumie mały język zapytań i ustala ranking wyników algorytmem BM25. Jest domyślnie dostępny w SQLite, nie trzeba instalować żadnego rozszerzenia.

Tworzenie tabeli FTS5

Tabelę FTS5 tworzysz przez CREATE VIRTUAL TABLE ... USING fts5(...), wymieniając kolumny tekstowe, które mają być indeksowane:

CREATE VIRTUAL TABLE posts USING fts5(title, body);

INSERT INTO posts (title, body) VALUES
  ('SQLite full-text search', 'FTS5 is fast and built in.'),
  ('Indexes in SQLite', 'B-trees power most lookups in SQLite.'),
  ('Why use SQLite', 'It runs in-process and stores data in one file.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

Warto zauważyć trzy rzeczy. Kolumny nie mają typów: FTS5 traktuje wszystko jak tekst. Operator MATCH stosuje się do nazwy tabeli (posts MATCH ...), a nie do kolumny. A zapytanie nie rozróżnia wielkości liter i jest tokenizowane, więc 'sqlite' znajduje SQLite w każdym z wierszy.

Język zapytań `MATCH`

MATCH przyjmuje więcej niż jedno słowo. Ciąg zapytania ma własną, prostą gramatykę:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Full-text search', 'FTS5 supports phrase and prefix queries.'),
  ('Indexes', 'A B-tree index speeds up equality lookups.'),
  ('Triggers', 'Use triggers to keep FTS in sync with the source table.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

Co robi każde z nich:

'fts5 AND prefix': oba tokeny muszą wystąpić (w dowolnej kolejności, w dowolnym miejscu wiersza).
'"keep fts"': dokładna fraza, w tej kolejności.
'trig*': wyszukiwanie po prefiksie, pasuje do trigger, triggers, trigonometry...
'index NOT trigger': zawiera index, nie zawiera trigger.

Możesz też zawęzić wyszukiwanie do jednej kolumny przez column:term, na przykład 'title:sqlite'. Pełna gramatyka obejmuje nawiasy do grupowania i OR dla alternatyw, czyli to, czego można się spodziewać po wyszukiwarce.

Ranking z BM25

FTS5 domyślnie dołącza do każdego wiersza ukrytą kolumnę rank. To wynik trafności BM25: niższe liczby oznaczają lepsze dopasowanie. Sortuj po niej, żeby najtrafniejsze wyniki były na początku:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite tutorial', 'Learn SQLite from scratch.'),
  ('SQLite vs Postgres', 'Comparing SQLite to Postgres for small apps.'),
  ('SQLite SQLite SQLite', 'A post that mentions SQLite many times.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

Chcesz, żeby niektóre kolumny ważyły więcej niż inne? Wywołaj bm25() z wagami, po jednej na kolumnę w kolejności deklaracji:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite indexes', 'A short note about B-trees.'),
  ('Postgres notes', 'Includes a paragraph about sqlite for comparison.');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

Pierwszy post wygrywa, bo sqlite występuje w title (waga 10×), a nie tylko w body (waga 1×). Dobierz wagi tak, jak twoja aplikacja naprawdę chce ustalać ranking.

Synchronizacja indeksu

Najprostsza tabela FTS5 przechowuje własną kopię tekstu. To wystarcza dla danych w stylu logów, do których tylko dopisujesz, ale większość aplikacji ma już prawdziwą tabelę i chce, żeby FTS ją śledziło. Czysty wzorzec to tabela FTS z treścią zewnętrzną i trzy wyzwalacze.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, body,
    content='articles',
    content_rowid='id'
);

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO articles (title, body) VALUES
  ('SQLite triggers', 'How to use AFTER INSERT triggers.'),
  ('FTS5 patterns', 'External content keeps the data in one place.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' mówi FTS5, żeby nie przechowywało samego tekstu, tylko pobierało go w razie potrzeby z tabeli articles. Wyzwalacze odzwierciedlają zapisy w indeksie FTS. Teraz articles jest źródłem prawdy, a articles_fts to tylko struktura wyszukiwania obok niej.

Dziwnie wyglądające INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) to składnia poleceń FTS5, która każe indeksowi usunąć wiersz.

Fragmenty i wyróżnianie

Wyniki wyszukiwania zwykle potrzebują podglądu z wyróżnionymi dopasowanymi słowami. FTS5 ma do tego dwie funkcje:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Getting started', 'SQLite is a small, fast, embedded database engine that stores data in a single file on disk.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite database';

highlight(table, column_index, open, close) zwraca pełny tekst kolumny z dopasowanymi tokenami otoczonymi znacznikami.
snippet(table, column_index, open, close, ellipsis, token_count) zwraca krótki fragment wokół dopasowania.

Indeksy kolumn liczone są od zera w kolejności deklaracji. To elementy, z których buduje się „dopasowane słowa na żółto”, potrzebne w każdym interfejsie wyszukiwania.

Pułapki, o których warto wiedzieć

Kilka rzeczy, na które ludzie się łapią:

MATCH działa tylko na tabelach FTS. Nie możesz użyć MATCH na zwykłej kolumnie. Jeśli potrzebujesz wyszukiwania w istniejącej tabeli, użyj opisanego wyżej wzorca z treścią zewnętrzną.
Nie zapomnij sortować po rank. Bez tego FTS5 zwraca wiersze w kolejności przechowywania, która nie ma nic wspólnego z trafnością.
Tokenizery mają znaczenie. Domyślny tokenizer (unicode61) dzieli tekst na granicach słów Unicode i zamienia na małe litery. Do sprowadzania słów do rdzenia (run pasuje do running) użyj tokenizera porter: USING fts5(body, tokenize='porter').
FTS5 nie toleruje literówek. Dopasowuje po prefiksie, a nie w sposób przybliżony. Jeśli potrzebujesz podpowiedzi w stylu „czy chodziło ci o...”, to osobna warstwa nad FTS5.
Tabele bez treści (content='') są mniejsze, ale tracą dane. Możesz je przeszukiwać, ale nie odzyskasz z nich oryginalnego tekstu, tylko rowid. Przydają się, gdy tekst przechowujesz gdzie indziej.

Dalej: funkcje okna

FTS5 zajmuje się wyszukiwaniem tekstu. Następna strona omawia inny rodzaj zaawansowanych zapytań: funkcje okna, które pozwalają liczyć sumy narastające, rankingi i analizy w grupach bez zwijania wierszy w agregaty.

Najczęściej zadawane pytania

Czym jest FTS5 w SQLite?

FTS5 to wbudowane rozszerzenie SQLite do wyszukiwania pełnotekstowego. Tworzysz specjalną tabelę wirtualną przez CREATE VIRTUAL TABLE ... USING fts5(...) i odpytujesz ją operatorem MATCH. Przy wstawianiu dzieli tekst na tokeny, przechowuje indeks odwrócony i domyślnie ustala ranking wyników algorytmem BM25.

Czym MATCH różni się od LIKE w SQLite?

LIKE liniowo przeszukuje podciągi i ignoruje granice słów. MATCH korzysta z indeksu odwróconego FTS5, więc jest szybki w dużych tabelach i rozumie tokeny, zapytania po prefiksie (term*), operatory logiczne (AND, OR, NOT) i wyszukiwanie fraz ("exact phrase"). MATCH działa tylko na wirtualnych tabelach FTS.

Jak utrzymać indeks FTS5 zsynchronizowany ze zwykłą tabelą?

Użyj tabeli FTS5 bez treści albo z treścią zewnętrzną, która wskazuje na prawdziwą tabelę, albo utwórz wyzwalacze AFTER INSERT, AFTER UPDATE i AFTER DELETE, które odzwierciedlają zmiany w tabeli FTS. Wzorzec z treścią zewnętrzną (content='posts') pozwala uniknąć przechowywania tekstu dwa razy.

Jak ustalić ranking wyników wyszukiwania pełnotekstowego w SQLite?

FTS5 udostępnia ukrytą kolumnę rank, która zwraca wynik BM25 (im niższy, tym lepiej). Sortuj bezpośrednio po niej: ORDER BY rank. Możesz też wywołać bm25(table), żeby jawnie dostać wynik, albo podać wagi kolumn, np. bm25(posts, 10.0, 1.0), żeby tytuł ważył więcej niż treść.

Powiązane zagadnienia