Logo David Burdelak
Webdevelopment

Full-Text Search w PostgreSQL i MySQL – jak przyspieszyć wyszukiwanie w bazie danych

Optymalizacja zapytań i mechanizm Full-Text Search w relacyjnych bazach danych

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 simple z rozszerzeniem unaccent, 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_data serwera. 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_size wymaga 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.