Поиск

Функции полнотекстового поиска

  • MATCH {столбец!, столбец2,...) AGAINTS {выражение[IN BOOLEAN MODE | WITH QUERY EXPANSION])
  • Начиная с версии 3.23.23, MySQL поддерживает полнотекстовую индексацию и поиск. Полнотекстовый индекс в MySQL - это индекс типа FULLTEXT. Такие ин­дексы применяются только в таблицах MyISAM, и создаваться могут только на столбцах типа CHAR, VARCHAR или TEXT во время выполнения оператора CREATE TABLE, либо добавляться позже с помощью операторов ALTER TABLE или CREATE INDEX. Для больших наборов данных гораздо быстрее будет сначала загрузить данные в таблицу, которая не имеет индекса FULLTEXT, а затем создать его с по­мощью ALTER TABLE или CREATE INDEX. Загрузка данных в таблицу, у которой уже есть такой индекс, выполняется ощутимо медленнее. Ограничения на полнотекстовый поиск перечислены в разделе 5.6.3. Полнотекстовый поиск выполняется с применением функции MATCH ().
mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTOJENCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO articles (title,body) VALUES
-> ('MySQL Tutorial1 /DBMS stands for DataBase ...'),
-> ('How To Use MySQL Well','After you went through a ...'),
-> ('Optimizing MySQL','In this tutorial we will show ...'),
-> ('1001 MySQL Tricks' ,'1. Never run mysqld as root. 2. ...'),
-> ('MySQL vs. YourSQL','In the following database comparison ...'),
-> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: б Duplicates: 0 Warnings: 0
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');

Функция MATCH() осуществляет естественный языковый поиск строки в текстовой коллекции. Коллекция - это набор из одного или более столбцов, включенных в индекс

FULLTEXT. Искомая строка задается аргументом AGAINST (). Поиск осуществляется в ре­жиме, нечувствительном к регистру. Для каждой строки в таблице MATCH () возвращает релевантное значение, то есть измеренную степень сходства между искомой строкой и текстом в строке таблицы, состоящим из столбцов, перечисленных в списке MATCH ().
Когда MATCH () применяется в конструкции WHERE, как в предыдущем примере, воз­вращаемые строки автоматически сортируются в порядке убывания релевантного значе­ния (степени сходства). Релевантные значения - это неотрицательные числа с плаваю­щей точкой. Нулевая релевантность означает отсутствие сходства. Оно вычисляется на базе количества слов в строке, количества уникальных слов в строке, общего числа слов в коллекции и количества документов (строк таблицы), содержащих конкретное слово.
Для естественного языкового полнотекстового поиска существует требование, что столбцы, перечисленные в аргументах функции MATCH(), были теми же, по которым по­строен индекс FULLTEXT вашей таблицы. Заметьте, что в предшествующем запросе столбцы, перечисленные в аргументах функции MATCH() (title и body), - те же самые, что и в определении FULLTEXT-индекса таблицы articles. Если вы хотите искать раз­дельно по столбцам title и body, то должны создать отдельный FULLTEXT-индекс для каждого столбца.
Существует также возможность булевского поиска или поиска с расширением запро­са. Эти типы поиска описаны в разделах 5.6.1 и 5.6.2.
Предыдущий пример - это базовая иллюстрация, показывающая как использовать функцию MATCH (), в которой строки возвращаются в порядке убывания релевантности. Следующий пример демонстрирует, как явно извлекать значения релевантности. Воз­вращаемые строки не упорядочены, потому что оператор SELECT не содержит ни конст­рукции ORDER BY, ни конструкции WHERE:
mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial') -> FROM articles;

Представленный ниже пример более сложный. Запрос возвращает значения реле­вантности, а также строки в порядке его убывания. Чтобы достичь этого результата, вы должны указать MATCH () дважды: один раз в списке столбцов SELECT и второй - в конст­рукции WHERE. Это не приводит к дополнительной нагрузке, поскольку оптимизатор MySQL определяет, что два вызова MATCH () идентичны и выполняет код полнотекстово-го поиска только один раз.
mysql> SELECT id, body, MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root1) AS score
-> FROM articles WHERE MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root');



MySQL применяет очень простой анализатор для разделения текста на слова. "Слово" - это любая последовательность букв, цифр, "'" или "_". Некоторые слова при полнотекстовом поиске игнорируются:

  1. Любые слишком короткие слова игнорируются. Минимальная длина по умолчанию слова, которое может быть найдено при полнотекстовом поиске, - четыре символа.
  2. Слова из списка "стоп-слов" игнорируются. Стоп-слова - это слова вроде "the",
    "some", которые настолько часто встречаются, что рассматриваются как имеющие нулевое семантическое значение. Существует встроенный список стоп-слов.

Минимальная длина слова по умолчанию и список стоп-слов могут быть изменены, как описано в разделе .Тонкая настройка полнотекстового поиска MySQL.
Каждое правильное слово в коллекции и в запросе "взвешивается" в соответствии с его важностью в коллекции или запросе. Таким образом, слово, представленное во мно­гих документах, имеет меньший вес (может даже иметь нулевой вес), поскольку имеет меньшее семантическое значение в данной коллекции. И наоборот, если слово редкое, то оно получает больший вес. Показатели веса слов затем комбинируются для вычисления релевантности строки.
Такая техника работает наилучшим образом с большими коллекциями (фактически, она специально настроена на это). Для очень маленьких таблиц статистическое распреде­ление слов не отражает адекватно их семантические значения, что иногда может приво­дить к причудливым результатам. Например, несмотря на то, что слово "MySQL" пред­ставлено в каждой строке таблицы articles, поиск по этому слову не дает результата:
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)
Результат поиска пуст, потому что слово "MySQL" содержится, по меньшей мере, в 50% всех строк. В связи с этим, оно рассматривается как стоп-слово. Для больших набо­ров данных это наиболее желательное поведение - запрос на естественном языке не должен ежесекундно возвращать строку из таблицы размером, скажем, в 1 Гбайт. Для малых наборов, это, конечно же, менее желательно.
Слово, которому соответствует половина строк таблицы, менее полезно для поиска релевантных документов. Фактически, вероятно, что оно найдет большое число нереле­вантных документов. Мы все знаем, что такое случается слишком часто, когда пытаемся найти что-то в Internet с помощью поисковых систем. Это происходит по той причине, что строки содержат слово, имеющее низкое семантическое значение в конкретном на­боре данных, в котором оно встречается. Определенное слово может превысить порог "попаданий" в 50% в одном наборе данных, а в другом - нет.
Этот 50%-ный порог, скорее всего, будет достигнут, когда вы впервые пытаетесь вы­полнить полнотекстовый поиск, чтобы посмотреть, как он работает. Если вы создаете таблицу и вставляете в нее только одну или две строки текста, то получается, что каждое слово текста встречается как минимум в 50% строк. В результате поиск не вернет ника­кого результата. Для уверенности введите минимум три строки, а лучше - намного больше.