Поиск в MySQL. Часть 1 «Что такое хорошо, и что такое плохо?»

Каждый программист PHP в свое время сталкивается с организацией поиска на сайте по какому-то набору информации или по всему сайту. Хороший программист все данные хранит в базе данных, следовательно, и искать будем там же. Речь в статье будет идти только о СУБД MySQL. Именно на ней работает подавляющее большинство сайтов в Internet, и она доступна на любом хостинге.

Отвлекаясь от темы, замечу, что у некоторых других СУБД существуют технологии поиска (в частности FULLTEXT) производительнее и мощнее, чем в MySQL. К тому же в MySQL FULLTEXT-поиск есть только в MyISAM engine и отсутствует в более скоростном и надежном InnoDB. И это конечно играет не в пользу MySQL. Но малые и средние системы вполне под силу обслужить  и MySQL. Причем, если поиск на малых сайтах можно реализовать даже на базе оператора LIKE, то для сайтов среднего уровня этот подход не подойдет из-за медленности и «грузности» выполнения запросов. В этом случае используется так называемый FULLTEXT-поиск, т.е. поиск на базе индексов FULLTEXT.

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

С изучением данной темы на практике я столкнулся еще давно при создании поискового механизма для сайта со средним уровнем посещаемости, но с большим архивом многосимвольных статей. Это был сайт издательства журналов. Мой старый движок многолетней давности перестал справляться со своими задачами. Как результат, после запроса на поиск посетитель мог ждать по 30-60 секунд результатов поиска в исключительных случаях, что недопустимо. Старый поиск базировался на сложном запросе с «ручным» вычислением релевантности. Да еще и с учетом морфологии (на regexp). Конечно, такой запрос вводил сервер в ступор при поиске по большому количеству записей. Вот тогда мне и пришлось осваивать технологию полнотекстового поиска.

Основное преимущество FULLTEXT-поиска – скорость выполнения. Т.к. он базируется на индексации, скорость выполнения запросов может в 10 раз превышать скорость выполнения запросов на базе LIKE. Важное преимущество так же в том, что результаты автоматически сортируются по релевантности (с помощью ORDER BY можно «заказать» другую сортировку).

Основная проблема FULLTEXT-поиска в MySQL – отсутствие поддержки морфологии русского языка. Он был «заточен» для поиска на англоязычных ресурсах. Даже список «общеупотребительных» (подробнее в руководствах, ссылки выше) слов был составлен на английском. Поэтому русский язык был, да и сейчас остается в полном пролете :) (во второй части статьи описано, как создать в MySQL FULLTEXT-поиск с учетом русской морфологии).

Итак, создаем нашу табличку, определяем в ней для поля, по которому будем искать, индекс FULLTEXT. Заполняем данными. Пример (в индексе сразу два поля):

CREATE TABLE table1 ( field1 VARCHAR (255), field2 TEXT, FULLTEXT (field1, field2) )

Теперь, что бы осуществить поиск в таблице, нужно выполнить запрос. Например:

SELECT * FROM table WHERE MATCH field1, field2 AGAINST (строка для поиска);

По этому запросу будут возвращены строки, где встречаются слова «строка» и «поиска» (слово «для» будет опущено из-за длины; подробнее в руководствах).

Входящий поисковый запрос в PHP не забудьте обработать. Например, так (источник):

$search = substr($search, 0, 64);

$search = preg_replace(”/[^\w\x7F-\xFF\s]/”, ” “, $search);

$good = trim(preg_replace(”/\s(\S{1,2})\s/”, ” “, ereg_replace(” +”, “  “,”
$search “)));

$good = ereg_eplace(” +”, ” “, $good);

Способ не из лучших, но я думаю, что Вы и сами сможете его качественно улучшить.

Теперь о практической стороне вопроса. Вернемся к моему сайту, на котором я изучал FULLTEXT-поиск. Именно на нем в свое время я замерял скорости выполнения запросов. Измерения производились на локальном сервере без нагрузки. Поэтому учтите, что при серьезной нагрузке на сервер, разрыв в значениях увеличивается пропорционально, в разы.

В таблице «articles» (MyISAM) находились статьи. Текст лежал в поле «art_full» (mediumtext). Средний размер статей я не считал, но их содержимое было в среднем примерно как эта статья. А самих записей было порядка 1000. Сервер, обслуживающий БД, был достаточно медленный. Но нам это даже на руку: четче видны различия во времени и меньше влияние сторонних факторов. Все запросы выполнялись несколько раз, и высчитывалось среднее время выполнения.

Итак, запросы:

SELECT * FROM `articles` WHERE `art_full` like ‘%рынок%’ or `art_short` like ‘%рынок%’ or `art_name` like ‘%рынок%’ order by art_id;

/* 1. Result : “Query OK, 70 rows affected (0,38 sec)” */

SELECT SQL_CALC_FOUND_ROWS * FROM `articles`
WHERE `art_full` like ‘%рынок%’ or `art_short` like ‘%рынок%’ or `art_name` like
‘%рынок%’ order by art_id limit 10;

/* 2. Result : “Query OK, 10 rows affected (0,28 sec)” */

SELECT SQL_CALC_FOUND_ROWS * FROM `articles`
WHERE `art_full` like ‘%рынок%’ or `art_short` like ‘%рынок%’ or `art_name` like
‘%рынок%’ order by art_id limit 60, 10;

/* 3. Result : “Query OK, 10 rows affected (0,28 sec)” */

SELECT * FROM `articles` WHERE MATCH(`art_full`,`art_short`, `art_name`)
AGAINST(’рынок’) order by art_id;

/* 4. Result : “Query OK, 70 rows affected (0,13 sec)” */

SELECT SQL_CALC_FOUND_ROWS * FROM `articles`
WHERE MATCH(`art_full`,`art_short`, `art_name`) AGAINST(’рынок’) order by art_id
limit 10;

/* 5. Result : “Query OK, 10 rows affected (0,03 sec)” */

SELECT SQL_CALC_FOUND_ROWS * FROM `articles`
WHERE MATCH(`art_full`,`art_short`, `art_name`) AGAINST(’рынок’) order by art_id
limit 60, 10;

/* 6. Result : “10 rows affected (0,06 sec)” */

SELECT * FROM `pg_articles` WHERE MATCH(`art_full`,`art_short`, `art_name`)
AGAINST(’рыно*’ IN BOOLEAN MODE) order by art_id;

/* 7. Result : “Query OK, 125 rows affected (0,17 sec)” */

SELECT SQL_CALC_FOUND_ROWS * FROM `pg_articles` WHERE MATCH(`art_full`,`art_short`,
`art_name`) AGAINST(’рыно*’ IN BOOLEAN MODE) order
by art_id limit 10;

/* 8. Result : “Query OK, 10 rows affected (0,05 sec)” */

SELECT SQL_CALC_FOUND_ROWS * FROM `pg_articles` WHERE MATCH(`art_full`,`art_short`,
`art_name`) AGAINST(’рыно*’ IN BOOLEAN MODE) order
by art_id limit 60, 10;

/* 9. Result : “10 rows affected (0,08 sec)” */

Проанализировать запросы детально Вы можете самостоятельно, отличия выделены цветом. Но обратите внимание, какой выигрыш во времени получается при использовании FULLTEXT вместо регулярных выражений (LIKE). Это видно при сравнении времени выполнения 1 и 4 запросов. А вот и тот нюанс, который упоминался выше. Это SQL_CALC_FOUND_ROWS. Использование этой конструкции в запросах MySQL так же дает немалый прирост в скорости. Об этом подробнее.

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

1) Просто получить все найденные строки от MySQL целиком (те поля, которые нужно вывести). Т.к. в поиске обычно кроме заголовка выводится кусочек текста, где были найдены слова поиска, придется получать от MySQL самое объемное поле. Учитывая, что мы выбираем все найденные записи, это сильно скажется на скорости передачи данных в PHP и скорости их обработки, а так же на количество потребленной PHP памяти. Последнее в некоторых случаях может вообще прервать обработку скрипта из-за отсутствия свободной памяти. Конечно, такой подход вовсе недопустим.

2) Выполнить два запроса. В первом получить только ID записей, которые были найдены. Затем в PHP высчитать, какие записи нам нужны (учитывая, какая страница запрошена и сколько записей на одной странице). Затем, выполнить второй запрос и получить именно эти записи. Такой подход исключает нехватку памяти (если количество записей на странице в пределах разумного) и работает быстрее первого при большом количестве объемных записей. Он допустим, но это не лучший путь.

3) А лучший путь – это, конечно, использование SQL_CALC_FOUND_ROWS. В этом случае так же выполняется два запроса, но от MySQL принимается меньшее количество записей. Выгоду от применения SQL_CALC_FOUND_ROWS можно увидеть на примерах 1 и 2 запросов, а так же 3, 4 и 5. Но стоит отметить, что чем ближе запрашиваемая страница к последней странице в результатах поиска, тем больше требуется времени на выполнение запроса с SQL_CALC_FOUND_ROWS.

Подробнее описанию возможностей MySQL, связанных с SQL_CALC_FOUND_ROWS, посвящена отдельная статья.

Поиск в MySQL. Часть 2 «Поиск с учетом русской морфологии»