Привет!
Полнотекстовый поиск данных в InnoDB – это известная головная боль многих разработчиков под MySQL / InnoDB. Для тех, кто не в курсе дела я объясню. В типе таблиц MyISAM есть полноценный полнотекстовый поиск данных, однако сама таблица исторически имеет ограничения, которые являются принципиальными в отдельных проектах. В более «продвинутом» типе таблиц InnoDB полнотекстового поиска нет. Вот и приходится мириться бедным разработчикам либо с ограничениями MyISAM, либо с отсутствием поиска в InnoDB. Я хочу рассказать о том, какие есть способы организовать полноценный поиск в InnoDB без магии и исключительно штатными средствами. Также будет интересно сравнить скоростные характеристики каждого способа.
Для примера возьмем небольшую таблицу с 10000 записями.

В этой таблице мы храним данные пользователей сайта. На самом сайте есть форма поиска пользователей, в которую можно ввести произвольный запрос вида «Толстой Ясная Поляна». Для обработки такого запроса поиск должен осуществляться сразу по нескольким полям. Нам нужен поиск для полей
login ,
name ,
surname ,
city ,
country . Запрос может быть как одиночным словом (имя или город) или же в виде набора слов, разделенных пробелом. Проблема в том, что нам необходимо искать этот набор слов сразу по нескольким полям, что сложно сделать в InnoDB без использования дополнительных функций.
Рассмотрим каждый из них подробнее.
Первый предлагаемый способ заключается в создании дополнительной таблицы в MyISAM. Как известно MyISAM достаточно неплохо поддерживает полнотекстовый поиск и это можно использовать. В эту дополнительную таблицу будут копироваться все данные из основной таблицы (
users ). Синхронизация будет обеспечиваться за счет триггеров. В новой таблице добавим поля
login ,
name ,
surname ,
city ,
country . Таким образом, мы создадим «зеркало» основной таблицы, и работать будем с ним. Для возможности полнотекстового поиска добавим туда FULLTEXT индекс по всем 5 полям вместе:

Для синхронизации данных между основной таблицей и таблицей-«зеркалом» на users установим триггеры на запись, изменение и чтение:

Здесь поиск данных происходит в таблице search , результат сортируется по релевантности, и на выходе мы получаем соответствующие записи из таблицы
users .
Главный плюс такого подхода — это гибкость поиска за счет добавления дополнительных индексов и составления новых комбинаций поиска (страна+город или логин + имя + фамилия). Таким образом, мы можем свободно формировать новые наборы для поиска и правила релевантности.
Минусы этого способа (как и всех способов с созданием «зеркала») – это избыточное хранение данных. Поэтому его целесообразно использовать при небольших объемах данных как в нашем примере.
Второй способ также заключается в создании зеркала данных, однако здесь мы будем хранить данные только в одном поле. В поставленной задаче поиск осуществляется сразу по группе полей и мы попробуем объединить их в одно текстовое поле, разделив пробелами. Таким образом, целому набору данных в таблице
users будет соответствовать одно единственное поле. Создадим таблицу
search с двумя полями id и text .
Id – будет соответствовать id основной таблицы ( users ),
text – это наши «кэшированные» данные.

Этот способ не такой гибкий как предыдущий, однако как мы увидим дальше он выигрывает в скорости при большом числе разнообразных запросов.
Третий способ основан на создании списка «ключевых слов» — поисковых тегов. Ключевые слова – это поля в таблице
users . Например, для пользователя с полями
(id=2144; login= leo; name=Лев;surname=Толстой;city=’Ясная Поляна’;country=Россия;email=leo@tolstoy.ru;password=
. Все эти слова мы будем записывать в отдельную таблицу MyISAM, в которой будут два поля
id и text . Id соответствует id основной таблицы (
users ). А
text – это поле, в которое будут записываться ключевые слова-теги. Каждому пользователю из таблицы
users будут соответствовать 5 записей в новой таблице search . Таким образом, мы получили таблицу тегов каждого пользователя.

Обратите внимание, что если раньше релевантность определялась встроенным механизмом поиска MyISAM, то в этом случае ее определяем сами. В результате поиска мы получили только те теги, которые соответствуют запросу. И чем больше тегов одного пользователя, тем выше он в выборке.
Приведенный пример имеет недостаток: при равном числе тегов у нескольких записей происходит естественная сортировка, что не всегда верно с точки зрения релевантности.
Однако у этого метода есть высокий потенциал для дальнейшего развития. Во-первых, мы можем добавить в сортировку
. Тем самым указанный выше недостаток будет устранен. Во-вторых, мы можем добавить в эту таблицу дополнительное поле веса тега
weight , и каждому полю основной таблицы поставить в соответствие значение этого веса. Таким образом, мы можем добавить сортировку с учетом значимости (веса) отдельных полей. Это дает нам возможность делать акцент на каких то полях без ухудшения качества поиска.
Четвертый способ суров и не использует MyISAM как предыдущие. В нем также нет дополнительных таблиц и триггеров. Мы будем просто искать по существующей таблице. Для начала нам необходимо проиндексировать все поля, в которых будет осуществляться поиск.

В InnoDB поиск можем осуществлять только с помощью оператора LIKE, но для его эффективной работы необходимо разбить запрос на слова, иначе запросы, состоящие из нескольких слов, останутся без результата. Для разбиения на слова и составления запроса напишем функцию:
"CONCAT_WS(' ',`name`,`surname`,`login`,`country`,`city`) LIKE '%"
!

output !

Функция возвращает нам фрагмент сформированного поискового запроса, который просто нужно подставить и выполнить:
,
Также можно использовать временные таблицы, они дадут ощутимое удобство при обработке результатов запроса.
Существует ряд сторонних решений для полнотекстового поиска. Наиболее популярные платформы это
и проекты на базе . Их использование лишено смысла при небольших объемах данных (таких как в нашем примере), а иногда просто невозможно в связи с ограничениями (хостер, злой админ, кривые руки и т. д.).
Сравним показанные способы полнотекстового поиска (кроме сторонних решений) на скорость выполнения типовых запросов. Сравнивать будем на примере выполнения 50 запросов различной сложности. Для этого напишем PHP-скрипт, который будет объективно подсчитывать среднюю скорость выполнения поиска каждым из приведенных методов. Для того чтобы приблизить измерения к реальным условиям проведем второе контрольное измерение, в котором будут использованы те же самые поисковые запросы. Здесь можно будет оценить, насколько хорошо в каждом методе используются кэширующие механизмы MySQL.
Как и ожидалось, прямой LIKE поиск в InnoDB оказался самым медленным и существенно проигрывает всем остальным. Конечно этот способ еще можно оптимизировать, однако это вряд ли даст существенный выигрыш в скорости.
Три оставшихся метода поиска показали себя примерно на одном уровне. Как показала практика, при большом количестве одинаковых запросов ощутимое преимущество дает использование ключевых слов (тегов) в MyISAM. При большом количестве разнообразных поисковых запросов выигрыш дает второй способ – создание кэшированного зеркала. Если какие-то поля сильно отличаются по размеру от других(содержимое статьи, текст новости), то эффективнее показывает себя первый способ — создание таблицы-зеркала.
Создание MyISAM зеркал стоит применять для небольших таблиц (10-50 тыс записей в таблице), если записей в таблице больше, и позволяют технические возможности используйте сторонние механизмы (Sphinx, Apache Lucene).