Търсене в текст

Накратко: Пълнотекстовото търсене намира думи в текста през обърнат индекс, който пази за всяка дума в кои редове се среща, вместо да чете всичко с LIKE.

Проблем

Полето за търсене

Сайтът получава поле за търсене в отзивите. Първата идея е LIKE:

SQLбаза: магазин

SELECT id, body FROM reviews WHERE body LIKE '%кафе%';

SELECT id, body FROM reviews WHERE body LIKE '%кафе%';

▶ ИзпълниВърни заявкатаНова база

Отзивът „Кафето беше с изтекъл срок.“ липсва. В SQLite LIKE не различава главни от малки букви само за латиницата, а К и к са различни. Друго търсене:

SQLбаза: магазин

SELECT id, body FROM reviews WHERE body LIKE '%нов%';

SELECT id, body FROM reviews WHERE body LIKE '%нов%';

▶ ИзпълниВърни заявкатаНова база

„нов“ хваща и „Обикновена“, защото LIKE търси парче от текста, а не дума. А % в началото не позволява индекс: при милион отзива базата чете милион текста за всяко търсене.

Идеята

Обърнатият индекс

Пълнотекстовото търсене прави обратното на таблицата. Таблицата казва „отзив 3 съдържа думите най-доброто, кафе, в, квартала“. Обърнатият индекс (inverted index) казва „думата кафе е в отзиви 3 и 6“. Търсенето на дума е едно четене в индекса, а не обхождане на текстовете.

Текстът първо се разбива на думи (tokenizer). unicode61 реже по интервали и препинателни знаци и прави всички букви малки, включително кирилицата. SQLite пази индекса във виртуална таблица с fts4:

SQLбаза: магазин

CREATE VIRTUAL TABLE review_search USING fts4(body, tokenize=unicode61);
INSERT INTO review_search (docid, body) SELECT id, body FROM reviews;

SELECT docid, body FROM review_search WHERE review_search MATCH 'кафе';

CREATE VIRTUAL TABLE review_search USING fts4(body, tokenize=unicode61); INSERT INTO review_search (docid, body) SELECT id, body FROM reviews; SELECT docid, body FROM review_search WHERE review_search MATCH 'кафе';

▶ ИзпълниВърни заявкатаНова база

docid е номерът на реда в индекса. Тук той е номерът на отзива, за да се свързва с reviews. MATCH е търсенето в индекса, а не = или LIKE.

Така изглеждат думите в индекса (fts4aux е таблица, която показва думите и в колко документа е всяка):

SQLбаза: магазин

CREATE VIRTUAL TABLE review_terms USING fts4aux(review_search);

SELECT term, documents
FROM review_terms
WHERE col = '*' AND term LIKE 'к%'
ORDER BY term;

CREATE VIRTUAL TABLE review_terms USING fts4aux(review_search); SELECT term, documents FROM review_terms WHERE col = '*' AND term LIKE 'к%' ORDER BY term;

▶ ИзпълниВърни заявкатаНова база

Идеята

Окончанията

В индекса „кафе“ и „кафето“ са две различни думи. Затова MATCH 'кафе' не намери отзив 13. Най-простата помощ е представка: кафе* значи всяка дума, която започва с „кафе“:

SQLбаза: магазин

SELECT docid, body FROM review_search WHERE review_search MATCH 'кафе*';

SELECT docid, body FROM review_search WHERE review_search MATCH 'кафе*';

▶ ИзпълниВърни заявкатаНова база

Представката е груба: кафе* би намерила и „кафене“. Истинските търсачки имат анализатори за всеки език, които свеждат думите до корена им (stemming): „кафето“ и „кафета“ стават „кафе“. SQLite има такъв само за английския (tokenize=porter), а Elasticsearch и OpenSearch имат и за българския.

Пробвай

Тиганът

Намери всички отзиви за тигана, в каквато и да е форма на думата (колони docid и body).

SQLбаза: магазин

SELECT docid, body FROM review_search LIMIT 3;

SELECT docid, body FROM review_search LIMIT 3;

Провери▶ Изпълни

Идеята

Повече от една дума

Две думи една след друга значат И: отзивът трябва да има и двете. OR значи поне едната:

SQLбаза: магазин

-- И двете думи (AND се подразбира)
SELECT docid, body FROM review_search WHERE review_search MATCH 'кафе* доставка';
-- Едната или другата
SELECT docid, body FROM review_search WHERE review_search MATCH 'ябълки OR домати';

-- И двете думи (AND се подразбира) SELECT docid, body FROM review_search WHERE review_search MATCH 'кафе* доставка'; -- Едната или другата SELECT docid, body FROM review_search WHERE review_search MATCH 'ябълки OR домати';

▶ ИзпълниВърни заявкатаНова база

Фраза в кавички търси думите една до друга и в този ред. NEAR/3 търси двете думи на най-много 3 думи разстояние:

SQLбаза: магазин

-- Точно тази фраза, думите една до друга
SELECT docid, body FROM review_search WHERE review_search MATCH '"като нов"';
-- Двете думи на най-много 3 думи разстояние
SELECT docid, body FROM review_search WHERE review_search MATCH 'тиган* NEAR/3 добър';

-- Точно тази фраза, думите една до друга SELECT docid, body FROM review_search WHERE review_search MATCH '"като нов"'; -- Двете думи на най-много 3 думи разстояние SELECT docid, body FROM review_search WHERE review_search MATCH 'тиган* NEAR/3 добър';

▶ ИзпълниВърни заявкатаНова база

NOT маха документите с дадена дума: кафе* NOT срок.

По-точно, за любопитните

FTS4 има два синтаксиса на търсенето. В стандартния „без думата“ се пише с минус (кафе* -срок), а в разширения с NOT. sql.js, който пуска SQLite в браузъра, е с разширения, и там -срок е просто думата „срок“. Разширеният позволява и скоби: (ябълки OR домати) NOT скъсан. В Elasticsearch същото е заявка bool с must, should и must_not.

Пробвай

Кафе без оплаквания

Намери отзивите за кафе (в каквато и да е форма на думата), в които не се споменава срок (колони docid и body).

SQLбаза: магазин

SELECT docid, body FROM review_search WHERE review_search MATCH 'кафе*';

SELECT docid, body FROM review_search WHERE review_search MATCH 'кафе*';

Провери▶ Изпълни

Идеята

Откъс и подреждане

Търсачката показва откъс с маркирана дума. snippet го прави сам:

SQLбаза: магазин

SELECT docid, snippet(review_search, '[', ']', '…') AS found
FROM review_search
WHERE review_search MATCH 'хляб*';

SELECT docid, snippet(review_search, '[', ']', '…') AS found FROM review_search WHERE review_search MATCH 'хляб*';

▶ ИзпълниВърни заявкатаНова база

Първите резултати трябва да са най-подходящите. Истинските търсачки ги подреждат по BM25: колкото по-рядка е думата във всички документи и колкото по-често е в този, толкова по-високо, а късият текст с думата бие дългия. Таблицата fts4aux от картата за индекса дава числата за това: в колко документа е всяка дума. FTS4 не подрежда сам (FTS5 и Elasticsearch имат BM25 наготово), затова тук редът идва от ORDER BY.

Упражнение

Търсене с продуктите

1. Индексът пази само текста. Името на продукта и оценката са в таблиците.

Намери отзивите за кафе (в каквато и да е форма на думата) с името на продукта и оценката (колони name, rating и body), от най-високата оценка надолу.

SQLбаза: магазин

SELECT docid FROM review_search WHERE review_search MATCH 'кафе*';

SELECT docid FROM review_search WHERE review_search MATCH 'кафе*';

Провери▶ Изпълни

2. Индексът е отделна таблица и не знае сам, когато reviews се промени. Тригер го обновява при всеки нов отзив:

SQLбаза: магазин

CREATE TRIGGER review_added AFTER INSERT ON reviews
BEGIN
  INSERT INTO review_search (docid, body) VALUES (new.id, new.body);
END;

INSERT INTO reviews (product_id, customer_id, rating, body, created_at)
VALUES (17, 4, 5, 'Кафето пристигна прясно опечено.', '2026-10-25');

SELECT docid, body FROM review_search WHERE review_search MATCH 'кафе*';

CREATE TRIGGER review_added AFTER INSERT ON reviews BEGIN INSERT INTO review_search (docid, body) VALUES (new.id, new.body); END; INSERT INTO reviews (product_id, customer_id, rating, body, created_at) VALUES (17, 4, 5, 'Кафето пристигна прясно опечено.', '2026-10-25'); SELECT docid, body FROM review_search WHERE review_search MATCH 'кафе*';

▶ ИзпълниВърни заявкатаНова база

Напиши тригер review_removed, който маха отзива от review_search, когато той се изтрие от reviews.

SQLбаза: магазин

SELECT COUNT(*) FROM review_search;

SELECT COUNT(*) FROM review_search;

Провери▶ Изпълни

Идеята

Кога отделна търсачка

За търсене в няколко хиляди или милиона текста пълнотекстовият индекс на базата стига: FTS в SQLite, tsvector и GIN индекс в PostgreSQL, FULLTEXT в MySQL. Отделна търсачка (Elasticsearch, OpenSearch, Meilisearch, Typesense) си струва, когато търсенето е главната работа на сайта: анализатори за много езици, поправка на правописни грешки („кафу“ → „кафе“), филтри по категории с броячи до тях, подсказки, докато пишеш, и много машини. Тогава данните се копират в нея, обикновено от поток със събития, а истината остава в базата.

По-точно, за любопитните

В PostgreSQL същото търсене е WHERE to_tsvector('simple', body) @@ to_tsquery('simple', 'кафе:*') с индекс CREATE INDEX ON reviews USING gin (to_tsvector('simple', body)). Конфигурация bulgarian PostgreSQL няма по подразбиране. В Elasticsearch полето получава "analyzer": "bulgarian" и търсенето е {"query": {"match": {"body": "кафе"}}}.

Тест

Провери се

1. Защо LIKE '%кафе%' е бавен при милион отзива?

Защото LIKE е стара командаЗащото кафето е популярноЗнакът % в началото не позволява индекс и базата чете всеки текст отначало докрай

2. В пълнотекстовия индекс MATCH 'кафе' не намира „Кафето беше с изтекъл срок“. Защо?

„кафето“ е друга дума в индекса, а не „кафе“Главното КОтзивът е лош

3. Кое прави обърнатият индекс?

Сортира отзивите по оценкаЗа всяка дума пази в кои документи (и къде в тях) се срещаПази текстовете отзад напред

4. Отзивите се пазят в reviews, а търсенето в review_search. Какво става, ако някой изтрие отзив и няма тригер?

Търсенето още намира изтрития отзивНищо, индексът сам разбираТаблицата reviews се връща

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: INSERT: нови редове · DELETE: изтриване на редове · Тригери · Индекси
• Продължава в: Търсене по смисъл · Как се избира база
• Задачи по темата: Видове бази данни