Większość wyszukiwarek w serwisach, które przejmuję, działa na zapytaniu LIKE '%fraza%'. Przy kilkuset rekordach to wystarcza,
ale przy dziesiątkach tysięcy produktów czy artykułów każde wyszukiwanie przeszukuje całą tabelę, a wyniki i tak są słabe: brak odmiany
słów, brak oceny trafności, brak tolerancji na literówki. Zanim zaproponuję osobny silnik wyszukiwania, sprawdzam, ile da się wycisnąć
z wyszukiwania pełnotekstowego wbudowanego w bazę. W tym wpisie pokazuję, jak wdrażam je w PostgreSQL i MySQL, z uwzględnieniem języka
polskiego, i kiedy przestaje wystarczać.
Dlaczego LIKE nie wystarcza
Zwykły indeks B-tree pomaga tylko wtedy, gdy znany jest początek tekstu. Przy wzorcu zaczynającym się od % baza musi sprawdzić
każdy wiersz. Do tego LIKE porównuje znaki, a nie słowa: nie znajdzie „kawę”, gdy szukamy „kawa”, i nie oceni, który wynik
pasuje lepiej. Wyszukiwanie pełnotekstowe rozwiązuje oba problemy: dzieli tekst na słowa, sprowadza je do wspólnej postaci i zapisuje
w indeksie odwróconym, który dla każdego słowa przechowuje listę rekordów, w których występuje.
PostgreSQL i język polski
PostgreSQL ma najbardziej rozbudowane wyszukiwanie pełnotekstowe wśród popularnych baz, ale jeden szczegół zaskakuje wielu programistów:
w standardowej instalacji nie ma konfiguracji dla języka polskiego. Zapytanie z to_tsvector('polish', ...) zakończy się błędem.
Mam dwie drogi:
- Konfiguracja
simplez rozszerzeniemunaccent, która dzieli tekst na słowa i usuwa polskie znaki, ale nie sprowadza słów do formy podstawowej. Wystarcza przy nazwach produktów i kodach. - Własny słownik polski zbudowany z plików Hunspell lub Ispell dla języka polskiego, umieszczonych w katalogu
tsearch_dataserwera. Wymaga dostępu do serwera bazy, więc na zarządzanych usługach chmurowych bywa niedostępny.
CREATE TEXT SEARCH DICTIONARY polish_ispell (
TEMPLATE = ispell, DictFile = polish, AffFile = polish, StopWords = polish
);
CREATE TEXT SEARCH CONFIGURATION polish (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION polish
ALTER MAPPING FOR asciiword, word, hword, hword_part
WITH polish_ispell, simple;
Tekst do wyszukiwania trzymam w osobnej, wyliczanej kolumnie, z wagami: trafienie w nazwie jest ważniejsze niż w opisie. Indeks GIN na tej kolumnie sprawia, że baza nie przelicza tekstu przy każdym zapytaniu:
ALTER TABLE products ADD COLUMN search tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('polish', coalesce(name, '')), 'A') ||
setweight(to_tsvector('polish', coalesce(description, '')), 'B')
) STORED;
CREATE INDEX products_search_idx ON products USING GIN (search);
Frazę od użytkownika przekazuję przez websearch_to_tsquery, która bezpiecznie obsługuje cudzysłowy, minus czy zwykły tekst
i nie zwraca błędu przy nietypowych znakach. Wyniki sortuję według trafności:
SELECT id, name, ts_rank(search, q) AS rank
FROM products, websearch_to_tsquery('polish', $1) AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;
Literówki i fragmenty słów
Wyszukiwanie pełnotekstowe porównuje całe słowa, więc „ekspers” nie znajdzie „ekspresu”. Do tego służy rozszerzenie pg_trgm, które
dzieli tekst na trzyznakowe fragmenty i ocenia podobieństwo. Indeks trigramowy przyspiesza też zwykłe LIKE '%fraza%', więc często
łączę oba podejścia: pełnotekstowe dla trafności, trigramy dla podpowiedzi i poprawiania literówek.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm ON products USING GIN (name gin_trgm_ops);
SELECT name FROM products
WHERE name % 'ekspers'
ORDER BY similarity(name, 'ekspers') DESC
LIMIT 5;
MySQL i MariaDB
W MySQL tworzę indeks FULLTEXT na kolumnach tekstowych i wyszukuję przez MATCH ... AGAINST. Tryb logiczny pozwala
wymagać słów, wykluczać je i szukać po początku słowa:
ALTER TABLE products ADD FULLTEXT idx_search (name, description);
SELECT id, name, MATCH(name, description) AGAINST (? IN BOOLEAN MODE) AS score
FROM products
WHERE MATCH(name, description) AGAINST (? IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 20;
Ograniczenia, o których trzeba wiedzieć przy polskich treściach:
- MySQL nie sprowadza polskich słów do formy podstawowej, więc „kawa” i „kawie” to dla niego różne słowa. Częściowo pomaga wyszukiwanie
po początku słowa z gwiazdką, np.
kaw*. - Domyślnie pomijane są słowa krótsze niż 3 znaki w InnoDB, co dotyczy np. rozmiarów czy oznaczeń modeli. Ustawienie
innodb_ft_min_token_sizewymaga zmiany konfiguracji serwera i przebudowy indeksu. - Domyślna lista pomijanych słów jest angielska, więc w polskim tekście nie odrzuca spójników i przyimków.
Kiedy przechodzę na osobny silnik wyszukiwania
Wyszukiwanie w bazie przestaje wystarczać, gdy potrzebuję jednocześnie dobrej obsługi polskiej odmiany, tolerancji literówek, synonimów, podpowiedzi w czasie pisania i fasetowych filtrów z licznikami. Wtedy przenoszę wyszukiwanie do Meilisearch, Typesense albo Elasticsearch, a baza zostaje źródłem danych, które synchronizuję z indeksem przy każdej zmianie produktu. Wybór silnika to jednak tylko część pracy, bo wyszukiwarka i filtry w sklepie internetowym muszą też dobrze działać od strony użytkownika.
Zanim jednak dołożę kolejną usługę, sprawdzam w EXPLAIN ANALYZE, czy zapytanie faktycznie korzysta z indeksu, i czy wolno nie
działa coś innego, np. dociąganie danych produktów osobnym zapytaniem dla każdego wyniku. Takie problemy rozwiązuje
wydajna komunikacja z bazą danych, a publiczny endpoint
wyszukiwania i tak zabezpieczam limitem zapytań, bo jest częstym celem botów. To ten sam mechanizm, który stosuję przy
ochronie formularzy i API przed botami.