Нечеткий запрос MySQL больше не требует +%

задняя часть MySQL
Нечеткий запрос MySQL больше не требует +%

предисловие

Все мы знаем, что использование «%xx» в InnoDB при нечетких данных запроса приведет к сбою индекса, но иногда это так, и есть много подобных требований.Например, поисковым системам нужны ключевые слова на основе пользовательских данных для полной -текстовый поиск, электронная коммерция Веб-сайт должен искать в подробном представлении продукта в соответствии с условиями запроса пользователя Это не те задачи, с которыми хорошо справляется индекс дерева B+.

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

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

На заре MySQL InnoDB не поддерживала технологию полнотекстового поиска, а начиная с версии MySQL 5.6 InnoDB стала поддерживать полнотекстовый поиск.

Перевернутый индекс

Полнотекстовый поиск обычно реализуется с помощью инвертированного индекса.Как и B+Tree, инвертированный индекс также является индексной структурой. Сопоставление между словом и позицией самого слова в одном или нескольких документах он хранит во вспомогательной таблице, которая обычно реализуется с помощью ассоциативного массива и имеет два представления:

  • инвертированный индекс файла: {слово, идентификатор документа, в котором находится слово}
  • полный инвертированный индекс: {слово, (идентификатор документа, в котором находится слово, и позиция в конкретном документе)}

对于 inverted file index 的关联数组

На приведенном выше рисунке показан ассоциативный массив индекса инвертированного файла.Видно, что слово «код» присутствует в документах 1 и 4, поэтому его легко сохранить, а затем выполнить полнотекстовый запрос.Вы можете напрямую получить документы, содержащие запрос ключевые слова в соответствии с документами и полный инвертированный Индекс хранит право, то есть (DocumentId, Position), поэтому хранящийся в нем инвертированный индекс выглядит так, как показано на рисунке ниже. Например, ключевое слово «код» существует в 6-м слове документа 1 и 8-е слово документа 4. Напротив, полный инвертированный индекс занимает больше места, но может лучше находить данные и дополнять некоторые другие функции поиска.

image.png

Полнотекстовый поиск

Создать полнотекстовый индекс

1. Синтаксис создания полнотекстового индекса при создании таблицы следующий:

CREATE TABLE table_name ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, author VARCHAR(200), 
title VARCHAR(200), content TEXT(500), FULLTEXT full_index_name (col_name) ) ENGINE=InnoDB;

Введите оператор запроса:

SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES
WHERE name LIKE 'test/%';

image.png

Вышеупомянутые шесть индексных таблиц составляют инвертированный индекс, который называется вспомогательной индексной таблицей. Когда входящий документ токенизируется, отдельные слова полностью сортируются и разбиваются на шесть индексных таблиц с позиционной информацией и соответствующим DOC_ID в соответствии с весовым коэффициентом сортировки набора символов первого символа слова.

2. Синтаксис создания полнотекстового индекса для уже созданной таблицы следующий:

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

Использовать полнотекстовое индексирование

База данных MySQL поддерживает запросы полнотекстового поиска.Полнотекстовые индексы могут использоваться только в таблицах InnoDB или MyISAM и могут использоваться только для создания столбцов типа char, varchar и text.

Его синтаксис следующий:

MATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
    IN NATURAL LANGUAGE MODE
    | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
    | IN BOOLEAN MODE
    | WITH QUERY EXPANSION
}

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

Natural Language

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

Затем объедините демонстрацию, чтобы лучше понять естественный язык

SELECT
    count(*) AS count 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL' );

image.png

Приведенный выше оператор запрашивает количество строк, содержащих ключевое слово «MySQL» в столбцах заголовка и основного текста. Приведенное выше утверждение также можно записать так:

SELECT
    count(IF(MATCH ( title, body ) 
    against ( 'MySQL' ), 1, NULL )) AS count 
FROM
    `fts_articles`;

Хотя результаты, полученные этими двумя операторами, одинаковы, с точки зрения внутренней операции скорость выполнения второго оператора SQL выше, поскольку первый оператор SQL (на основе метода запроса индекса where) также должен быть сортируется по корреляции Статистика, тогда как второй способ не нужен.

Корреляции также можно запрашивать с помощью операторов SQL:

SELECT
    *,
    MATCH ( title, body ) against ( 'MySQL' ) AS Relevance 
FROM
    fts_articles;

image.png

Корреляция рассчитывается на основе следующих четырех условий:

  • появляется ли слово в документе
  • Сколько раз слово появляется в документе
  • количество слов в столбце индекса
  • сколько документов содержат слово

Для полнотекстового поиска механизма хранения InnoDB также необходимо учитывать следующие факторы:

  • Слово запроса находится в столбце стоп-слов, запросы для этой строки игнорируются.
  • Находится ли длина символа запрошенного слова в интервале [innodb_ft_min_token_size,innodb_ft_max_token_size]

Если слово находится в стоп-слове, не запрашивайте слово, например, запрос слова «для», результат будет следующим:

SELECT
    *,
    MATCH ( title, body ) against ( 'for' ) AS Relevance 
FROM
    fts_articles;

image.png

Можно видеть, что, хотя в документах 2 и 4 встречается слово for, его релевантность равна 0, поскольку это стоп-слово.

Параметры innodb_ft_min_token_size и innodb_ft_max_token_size контролируют длину символа запроса механизма InnoDB.Когда длина меньше, чем innodb_ft_min_token_size, или длина больше, чем innodb_ft_max_token_size, поиск этого слова будет игнорироваться. В движке InnoDB значение параметра innodb_ft_min_token_size по умолчанию равно 3, а значение по умолчанию innodb_ft_max_token_size равно 84.

Boolean

Логический поиск использует правила специального языка запросов для интерпретации строки поиска, содержащей искомое слово, а также может содержать операторы, определяющие требования, например, слово должно присутствовать или отсутствовать в соответствующей строке или его вес. должно быть выше или ниже обычного. Например, следующий оператор запрашивает документы со строкой "Pease", но не "hot", где + и - указывают, что слово должно или не должно существовать соответственно.

select * from fts_test where MATCH(content) AGAINST('+Pease -hot' IN BOOLEAN MODE);

Типы, поддерживаемые логическим полнотекстовым поиском, включают:

  • +: указывает, что слово должно существовать
  • -: Указывает, что слово не должно существовать
  • (без оператора) означает, что слово является необязательным, но если оно присутствует, его релевантность выше
  • @distance указывает, находится ли расстояние между несколькими словами в запросе в пределах расстояния. Единицей измерения расстояния является байт. Этот тип запроса полнотекстового поиска также называетсяProximity Search,какMATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)Оператор указывает, что расстояние между строками Pease и hot должно быть в пределах 30 байт.
  • >: указывает на возрастающую релевантность при появлении слова
  • <:>
  • ~: Указывает, что слово разрешено, но корреляция отрицательна, когда оно встречается.
  • * : Обозначает слова, начинающиеся с этого слова, напримерlik*, указывая на то, что это может бытьlik,like,likes
  • " : указывает на фразу

Ниже приведены некоторые демонстрации, чтобы увидеть, как используется логический режим.

демо1: +-

SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( '+MySQL -YourSQL' IN BOOLEAN MODE );

Приведенный выше оператор запрашивает информацию, которая содержит «MySQL», но не содержит «YourSQL».

image.png

demo2: без оператора

SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL IBM' IN BOOLEAN MODE );

В приведенном выше утверждении «MySQL IBM» запроса не имеет знаков «+», «-», что означает, что слово является необязательным, если оно появится, его релевантность будет выше.

image.png

демо3: @

SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( '"DB2 IBM"@3' IN BOOLEAN MODE );

В приведенном выше выражении, представляющем «DB2», расстояние между двумя словами «IBM» находится в пределах 3 байтов.

image.png

демо4:>

SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( '+MySQL +(>database <DBMS)' IN BOOLEAN MODE );

В приведенном выше выражении запрос одновременно содержит информацию о строках «MySQL», «база данных» и «СУБД», но корреляция строк, не содержащих «СУБД», выше, чем корреляция строк, содержащих «СУБД».

image.png

demo5: ~

SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL ~database' IN BOOLEAN MODE );

В приведенном выше утверждении запрос содержит строку «MySQL», но если строка также содержит «базу данных», релевантность снижается.

image.png

демо6:*

SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( 'My*' IN BOOLEAN MODE );

В приведенном выше выражении ключевое слово запроса содержит информацию о строке «My».

image.png

демо7: "

SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( '"MySQL Security"' IN BOOLEAN MODE );

Для приведенного выше оператора запросите информацию о строках, содержащих точную фразу «Безопасность MySQL».

image.png

Query Expansion

Расширенный поиск по запросу является модификацией поиска на естественном языке. Этот тип запроса обычно выполняется, когда ключевые слова запроса слишком короткие и пользователю нужны неявные знания. Например, для запроса базы данных слов пользователь может захотеть запрос больше, чем Это документ, содержащий базу данных, а также может относиться к словам, содержащим MySQL, Oracle и RDBMS.В настоящее время вы можете использовать режим расширения запроса, чтобы открыть подразумеваемые знания полнотекстового поиска.

Слепое расширение запроса (также известное как автоматическая обратная связь по релевантности) можно включить, добавив в запрос запрос WITH QUERY EXPANSION / IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION.Запрос разделен на два этапа.

  • Этап 1: Запрос полнотекстового индекса на основе искомых слов
  • Второй этап: выполнить еще один запрос полнотекстового поиска на основе сегментации слов, сгенерированной на первом этапе.

Давайте рассмотрим пример, чтобы увидеть, как используется расширение запроса.

-- 创建索引
create FULLTEXT INDEX title_body_index on fts_articles(title,body);

-- 使用 Natural Language 模式查询
SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH(title,body) AGAINST('database');

Результаты запроса до использования Query Expansion следующие:

image.png

-- 当使用 Query Expansion 模式查询
SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH(title,body) AGAINST('database' WITH QUERY expansion);

После использования Query Expansion результаты запроса выглядят следующим образом:

image.png

Поскольку полнотекстовый поиск Query Expansion может привести к множеству нерелевантных запросов, пользователям, возможно, придется быть очень осторожными при его использовании.

удалить полнотекстовый индекс

1. Синтаксис прямого удаления полнотекстового индекса следующий:

DROP INDEX full_idx_name ON db_name.table_name;

2. Используйте alter table для удаления синтаксиса полнотекстового индекса следующим образом:

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

резюме

Эта статья представляет полнотекстовый индекс с точки зрения сочетания теории и практики.Если вы интересуетесь MySQL, вы можете продолжать уделять внимание колонке MySQL.

Категории