Обзор индексации по полному тексту

MariaDB поддерживает индексацию и поиск по полному тексту:

• Индекс по полному тексту в MariaDB — это индекс типа FULLTEXT, и он предоставляет больше возможностей при поиске фрагментов текста в поле.
• Индексы по полному тексту могут использоваться только с таблицами MyISAM, Aria, InnoDB и Mroonga, и могут быть созданы только для столбцов типа CHAR, VARCHAR или TEXT.
• Разделенные таблицы не могут содержать индексы по полному тексту, даже если движок хранилища их поддерживает.
• Определение индекса FULLTEXT можно указать в операторе CREATE TABLE при создании таблицы, или добавить его позже с помощью ALTER TABLE или CREATE INDEX.
• Для больших наборов данных намного быстрее загрузить данные в таблицу без индекса FULLTEXT, а затем создать индекс, чем загрузить данные в таблицу с уже существующим индексом FULLTEXT.

Поиск по полному тексту выполняется с использованием синтаксиса MATCH() ... AGAINST. MATCH() принимает список значений через запятую, в котором указаны столбцы, которые будут проиндексированы. AGAINST принимает строку для поиска и необязательный модификатор, который указывает тип поиска. Строка поиска должна быть литеральной строкой, а не переменной или именем столбца.

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

Исключенные результаты

• Исключаются части слов.
• Слова длиной менее 4 (MyISAM) или 3 (InnoDB) символов не будут храниться в индексе по полному тексту. Это значение можно настроить, изменив системную переменную ft_min_word_length (или, для InnoDB, innodb_ft_min_token_size).
• Слова длиной более 84 символов также не будут храниться в индексе по полному тексту. Это значение можно настроить, изменив системную переменную ft_max_word_length (или, для InnoDB, innodb_ft_max_token_size).
• Стоп-слова — это список общих слов, таких как «once» или «then», которые не отражаются в результатах поиска, если не используется режим BOOLEAN MODE. Список стоп-слов для таблиц MyISAM/Aria и InnoDB может отличаться. Подробности и полный список см. в stopwords, а также информацию о том, как изменить стандартный список.
• Только для индексов MyISAM/Aria по полному тексту, если слово встречается более чем в половине строк, оно также исключается из результатов поиска по полному тексту.
• Для индексов InnoDB отображаются только подтвержденные строки — изменения из текущей транзакции не применяются.

Релевантность

MariaDB рассчитывает релевантность каждого результата на основе нескольких факторов, включая количество слов в индексе, количество уникальных слов в строке, общее количество слов как в индексе, так и в результатах, а также вес слова. В английском языке слово «cool» будет иметь меньший вес, чем «dandy», по крайней мере, на данный момент! Релевантность может быть возвращена в рамках запроса, просто используя функцию MATCH в списке полей.

Типы поиска по полному тексту

В РЕЖИМЕ ЕСТЕСТВЕННОГО ЯЗЫКА

РЕЖИМ ЕСТЕСТВЕННОГО ЯЗЫКА — это тип поиска по полному тексту по умолчанию, и ключевые слова можно опустить. Нет специальных операторов, и поиски состоят из одного или нескольких ключевых слов, разделенных запятыми.

Результаты возвращаются в порядке убывания релевантности.

В РЕЖИМЕ БУЛЕВЫХ ОПЕРАТОРОВ

Поиск с использованием булевых операторов позволяет использовать ряд специальных операторов:

Результаты не возвращаются в порядке релевантности, и лимит в 50% не применяется. Стоп-слова и минимальная и максимальная длина слов по-прежнему применяются как обычно.

С РАСШИРЕНИЕМ ЗАПРОСА

Поиск с расширением запроса — это модификация поиска в режиме естественного языка. Строка поиска используется для выполнения обычного поиска в режиме естественного языка. Затем слова из наиболее релевантных строк, возвращенных результатом поиска, добавляются в строку поиска, и поиск выполняется снова. Запрос возвращает строки из второго поиска. Модификатор IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION или WITH QUERY EXPANSION указывает поиск с расширением запроса. Он может быть полезен, когда вы полагаетесь на подразумеваемое знание в данных, например, что MariaDB — это база данных.

Примеры

Создание таблицы и выполнение базового поиска:

CREATE TABLE ft_myisam(copy TEXT,FULLTEXT(copy)) ENGINE=MyISAM;

INSERT INTO ft_myisam(copy) VALUES ('Once upon a time'),
  ('There was a wicked witch'), ('Who ate everybody up');

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked');
+--------------------------+
| copy                     |
+--------------------------+
| There was a wicked witch |
+--------------------------+

Несколько слов:

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked,witch');
+---------------------------------+
| copy                            |
+---------------------------------+
| There was a wicked witch        |
+---------------------------------+

Поскольку «Once» — стоп-слово, результат не возвращается:

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('Once');
Empty set (0.00 sec)

Вставка слова «wicked» в более чем половину строк исключает его из результатов:

INSERT INTO ft_myisam(copy) VALUES ('Once upon a wicked time'),
  ('There was a wicked wicked witch'), ('Who ate everybody wicked up');

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked');
Empty set (0.00 sec)

Использование РЕЖИМА БУЛЕВЫХ ОПЕРАТОРОВ для преодоления ограничения в 50%:

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked' IN BOOLEAN MODE);
+---------------------------------+
| copy                            |
+---------------------------------+
| There was a wicked witch        |
| Once upon a wicked time         |
| There was a wicked wicked witch |
| Who ate everybody wicked up     |
+---------------------------------+

Возвращение релевантности:

SELECT copy,MATCH(copy) AGAINST('witch') AS relevance 
  FROM ft_myisam WHERE MATCH(copy) AGAINST('witch');
+---------------------------------+--------------------+
| copy                            | relevance          |
+---------------------------------+--------------------+
| There was a wicked witch        | 0.6775632500648499 |
| There was a wicked wicked witch | 0.5031757950782776 |
+---------------------------------+--------------------+

С РАСШИРЕНИЕМ ЗАПРОСА. В следующем примере слово «MariaDB» всегда ассоциируется со словом «база данных», поэтому оно возвращается при использовании расширения запроса, даже если оно не запрашивается явно.

CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)) ENGINE=MyISAM;

INSERT INTO ft2(copy) VALUES
 ('MySQL vs MariaDB database'),
 ('Oracle vs MariaDB database'), 
 ('PostgreSQL vs MariaDB database'),
 ('MariaDB overview'),
 ('Foreign keys'),
 ('Primary keys'),
 ('Indexes'),
 ('Transactions'),
 ('Triggers');

SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
+--------------------------------+
| copy                           |
+--------------------------------+
| MySQL vs MariaDB database      |
| Oracle vs MariaDB database     |
| PostgreSQL vs MariaDB database |
+--------------------------------+
3 rows in set (0.00 sec)

SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database' WITH QUERY EXPANSION);
+--------------------------------+
| copy                           |
+--------------------------------+
| MySQL vs MariaDB database      |
| Oracle vs MariaDB database     |
| PostgreSQL vs MariaDB database |
| MariaDB overview               |
+--------------------------------+
4 rows in set (0.00 sec)

Сопоставление частичных слов с использованием РЕЖИМА БУЛЕВЫХ ОПЕРАТОРОВ:

SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('Maria*' IN BOOLEAN MODE);
+--------------------------------+
| copy                           |
+--------------------------------+
| MySQL vs MariaDB database      |
| Oracle vs MariaDB database     |
| PostgreSQL vs MariaDB database |
| MariaDB overview               |
+--------------------------------+

Использование булевых операторов

SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('+MariaDB -database' 
  IN BOOLEAN MODE);
+------------------+
| copy             |
+------------------+
| MariaDB overview |
+------------------+

См. также

• Для более простых поисков подстроки в текстовых столбцах см. оператор LIKE.