Поиск с помощью индекса FULLTEXT

Задача
Вы хотите выполнить поиск в тексте большого объема.

Решение
Используйте индекс FULLTEXT.

Обсуждение
Поиск по образцу может работать с любым количеством строк, но чем их больше, тем медленнее выполняется эта операция. Кроме того, часто приходится искать один и тот же текст в нескольких строковых столбцах, что приводит к созданию громоздких запросов:

SELECT * from имя_таблицы
WHERE столбец1 LIKE 'шаблон' OR столбец2 LIKE 'шаблон' OR столбец3 LIKE 'шаблон' ...

Полезной альтернативой (доступной начиная с версии MySQL 3.23.23) является использование FULLTEXTпоиска, предназначенного для просмотра больших объемов текста c одновременным просмотром нескольких столбцов. Добавьте в таблицу индекс FULLTEXT, затем используйте оператор MATCH для поиска строк индексированного столбца или столбцов. Индексирование FULL TEXT может применяться в таблицах MyISAM для столбцов типа CHAR, VARCHAR или TEXT.

FULLTEXTпоиск лучше всего продемонстрировать на тексте подходящего размера. Если у вас нет тестового набора данных, можно воспользоваться одним из свободно доступных хранилищ электронных текстов, имеющихся в Интернете.


В примерах данного раздела использован текст Библии в версии King James Version (KJV) – достаточно большой и очень хорошо структурированный текст: книга, глава, стих. Изза своего объема этот набор данных не включен в дистрибутив recipes, но для него на вебсайте книги «MySQL
Сookbook» создан собственный дистрибутив mcb kjv1

Дистрибутив включает файл kjv.txt, который содержит записи стихов. Записи выглядят так:

O Genesis 1 1 1 In the beginning God created the heaven and the earth.
O Exodus 2 20 13 Thou shalt not kill.
N Luke 42 17 32 Remember Lot's wife.

Каждая запись содержит поля:
• Раздел книги. Это или O, или N, что означает Ветхий (Old) и Новый (New) Завет.
• Название книги и соответствующий номер, от 1 до 66.
• Номер главы и стиха.
• Текст стиха.

Чтобы импортировать записи в MySQL, создайте такую таблицу kjv:
CREATE TABLE kjv
(
bsect ENUM('O','N') NOT NULL, # раздел книги (Завет)
bname VARCHAR(20) NOT NULL, # название книги
bnum TINYINT UNSIGNED NOT NULL, # номер книги
cnum TINYINT UNSIGNED NOT NULL, # номер главы
vnum TINYINT UNSIGNED NOT NULL, # номер стиха
vtext TEXT NOT NULL # текст стиха
) TYPE = MyISAM;

Затем загрузите файл kjv.txt в таблицу, выполнив такое предложение:

mysql> LOAD DATA LOCAL INFILE 'kjv.txt' INTO TABLE kjv;

Таблица kjv содержит столбцы как для названий книг (Genesis – Книга Бытия, Exodus – Исход, ...), так и для их номеров (1, 2, ...).


Названия и номера книг четко соответствуют друг другу, и одни могут быть однозначно получены из других. Налицо избыточность данных, то есть таблица не приведена к нормальной форме. Чтобы избавиться от такой избыточности, можно хранить только номера книг (они занимают меньше места, чем названия) и при необходимости выводить названия книг как результаты запроса, используя
соединение (join) с простой вспомогательной таблицей, сопоставляющей номерам книг их названия. Но пока я хочу избежать соединения. Поэтому таблица будет содержать названия книг для удобства восприятия результатов поиска и номера книг для удобства сортировки результатов по книгам.

После заполнения таблицы данными подготовим ее к полнотекстовому поиску, добавив индекс FULLTEXT. Для этого выполним предложение ALTER TABLE:1

mysql> ALTER TABLE kjv ADD FULLTEXT (vtext);

Чтобы выполнить поиск по индексу, используем MATCH() для указания индексированного столбца и AGAINST() для определения того, какой текст следует искать. Например, чтобы ответить на вопрос «Как часто встречается имя Mizraim» (ведь вас это всегда интересовало, не так ли?), будем просматривать столбец vtext при помощи такого запроса:

mysql> SELECT COUNT(*) from kjv WHERE MATCH(vtext) AGAINST('Mizraim');

+------------ +
| COUNT(*) |
+ ------------+
| 4 |
+ ------------+

Чтобы найти соответствующие стихи, выберем столбцы, которые вы хотели бы видеть (для того чтобы результат поместился на странице, в примере используется \G):

mysql> SELECT bname, cnum, vnum, vtext
> FROM kjv WHERE MATCH(vtext) AGAINST('Mizraim')\G
*************************** 1.


row ***************************
bname: Genesis
cnum: 10
vnum: 6
vtext: And the sons of Ham; Cush, and Mizraim, and Phut, and Canaan.
*************************** 2. row ***************************
bname: Genesis
cnum: 10
vnum: 13
vtext: And Mizraim begat Ludim, and Anamim, and Lehabim, and Naphtuhim,
*************************** 3. row ***************************
bname: 1 Chronicles
cnum: 1
vnum: 8
vtext: The sons of Ham; Cush, and Mizraim, Put, and Canaan.
*************************** 4. row ***************************
bname: 1 Chronicles
cnum: 1
vnum: 11
vtext: And Mizraim begat Ludim, and Anamim, and Lehabim, and Naphtuhim,

В данном конкретном случае результаты выводятся по порядку номеров книг, глав и стихов, но это простая случайность. По умолчанию FULLTEXTпоиск вычисляет величину релевантности и сортирует результаты начиная с наиболее релевантных. Чтобы обеспечить сортировку результата в необходимом вам порядке, добавьте явную инструкцию ORDER BY:

SELECT bname, cnum, vnum, vtext
FROM kjv WHERE MATCH(vtext) AGAINST('строка_поиска')
ORDER BY bnum, cnum, vnum;

Для сужения области поиска можно задать дополнительные условия.


В следующем фрагменте выполняются постепенно уточняющиеся запросы для нахождения частоты упоминания имени Abraham во всем тексте KJV, в Новом Завете (New Testament), в книге «К евреям» (Hebrews) и в главе 11этой книги:

mysql> SELECT COUNT(*) from kjv WHERE MATCH(vtext) AGAINST('Abraham');

+------------ +
| COUNT(*) |
+ ------------+
| 216 |
+ ------------+

mysql> SELECT COUNT(*) from kjv
> WHERE MATCH(vtext) AGAINST('Abraham')
> AND bsect = 'N';

+------------ +
| COUNT(*) |
+------------ +
| 66 |
+ ------------+

mysql> SELECT COUNT(*) from kjv
> WHERE MATCH(vtext) AGAINST('Abraham')
> AND bname = 'Hebrews';

+------------ +
| COUNT(*) |
+ ------------+
| 10 |
+ ------------+

mysql> SELECT COUNT(*) from kjv
> WHERE MATCH(vtext) AGAINST('Abraham')
> AND bname = 'Hebrews' AND cnum = 11;

+------------ +
| COUNT(*) |
+ ------------+
| 2 |
+ ------------+

Если вы планируете использовать условия поиска, относящиеся к другим не FULLTEXT столбцам, то для повышения производительности таких запросов можно добавить обычные индексы для этих столбцов.


Например, можно проиндексировать столбцы номеров книги, главы и стиха:

mysql> ALTER TABLE kjv ADD INDEX (bnum), ADD INDEX (cnum), ADD INDEX (vnum);

Строка поиска в запросах FULLTEXT может представлять собой не только отдельное слово. Казалось бы, указание дополнительных слов в строке поиска должно делать поиск более точным. Но на самом деле поиск только расширяется, так как при полнотекстовом поиске возвращаются записи, содержащие любое из указанных слов (фактически выполняется поиск с логическим ИЛИ для всех указанных слов). Рассмотрим запросы, возвращающие все
большее количество стихов по мере добавления новых слов поиска:

mysql> SELECT COUNT(*) from kjv
> WHERE MATCH(vtext) AGAINST('Abraham');

+------------ +
| COUNT(*) |
+ ------------+
| 216 |
+ ------------+

mysql> SELECT COUNT(*) from kjv
> WHERE MATCH(vtext) AGAINST('Abraham Sarah');

+------------ +
| COUNT(*) |
+ ------------+
| 230 |
+ ------------+

mysql> SELECT COUNT(*) from kjv
> WHERE MATCH(vtext) AGAINST('Abraham Sarah Ishmael Isaac');

+ ------------ +
| COUNT(*) |
+ ------------+
| 317 |
+ ------------+

Если вы хотите использовать FULLTEXTпоиск для параллельного просмотра нескольких толбцов, укажите их имена при создании индекса:

ALTER TABLE имя_таблицы ADD FULLTEXT (столбец1, столбец2, столбец3);

Чтобы создать запрос, использующий такой индекс, укажите имена тех же самых столбцов в списке MATCH():

SELECT ... FROM имя_таблицы
WHERE MATCH(столбец1, столбец2, столбец3) AGAINST('строка_поиска');

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
FULLTEXT поиск и короткие слова
Буквальная интерпретация метасимволов в шаблонах
Включение и исключение слов из FULLTEXT - поиска
Поиск по образцу с помощью регулярных выражений
Поиск по образцу с помощью шаблонов SQL

Вернуться в раздел: MySQL / 4. Работа со строками