Ускоренный REGEX или поиск как в google

Не так давно появилась необходимость написать более или менее универсальный поиск в MySQL. По нескольким, точнее сказать, скольким угодно, ключевым словам поиска. Сразу же оговорюсь, что под словами “сколько угодно” подразумеваю возможность самому определять максимально допустимое количество ключевых слов, а не легкомысленный авось, что никто не попытается записать в строку поиска большое количество ключевых слов, чтоб тем самым вызвать пиковую нагрузку.

Проще говоря, в этой статье пойдет речь не о том, как написать свой поисковик от и до. А о еще одном методе организации поиска в БД в частности в MySQL.

Методе, которому с моей точки зрения интернет сообщество не уделило достаточного внимания, по крайней мере просмотрев несколько статей по данной тематике не нашел более подробного описания о том как организовать быстрый поиск по нескольким ключевым словам. За исключением конечно описания FULLTEXT search. Но так как одним из основных условий было хорошая переносимость, чтобы результаты поиск не зависели от версии MySQL и можно было бы использовать алгоритм начиная с версии MySQL 3.23.xx и до самых новых версий.

Так что приступил к написанию и естественно хотелось пойти по пути наименьшего сопротивления с меньшими затратами времени, на разработку, и решил первоначально использовать LIKE. Но вот незадача, по крайней мере, лично мне не удалось в MySQL 3.23.43 одним LIKE организовать полноценный поиск в БД, да он на это и не рассчитан. Имею ввиду, что-то типа LIKE ‘%Иван%Стас%Николай%’ и слова могут находиться где угодно в тексте. Так что на первых парах пришлось оформить поиск в виде:
/*
Входные данные:
$searchquery – сторока поиска, состоящая из ключевых слов разделенных пробелами
$TABLE – имя таблицы
$field_name – массив содержащий имена столбцов в таблице
($field_name[0] – содержит ID таблицы)
$field_total – полей всего

$CONDITION="1=1" – Переменная для указания первичного условия.
Например произвести поиск там где YOUR_ID = …

Выход: функция возвращает ID строк, в которых были найдены соответствия.
*/
function DBsearch ($searchquery, $TABLE, $field_name, $field_total, $CONDITION="1=1") {
global $dbObj;
$dbSet=new xxDataset($dbObj);

$search = array("/[\'|\"]/", "/[%|_]/", "/(\\\\\\\\)/", "/^(\s*)|(\s*)$/");
$replace = array("\$0" , "\\\\$0" , "\\\\\\\\\$0", "");

$UniqKeyFieldNum=0;

// === Экранируем служебные символы MySQL
$searchquery=preg_replace($search, $replace, $searchquery);

// === разбиваем строку по произвольному числу пробельных символов,
// === которые включают в себя " ", \r, \t, \n и \f
$searchfor=preg_split("/[\s]+/", $searchquery);

$outIds = array();
$findedIds = array();

$row = array();
// === Перебираем имена столбцов в таблице, $fieldIndex=1 – исключаем из поиска столбец ID.
for ($x=1, $fieldIndex=1; $fieldIndex<$field_total; $fieldIndex++) {
$cndtn = "";
// === Считаем сколько слов в строке поиска
$WordsTotal=count($searchfor);
// === Динамически создаем запрос из заданных слов.
for ($WordNum=0; $WordNum<$WordsTotal; $WordNum++) {
$cndtn .= " $field_name[$fieldIndex] LIKE '%$searchfor[$WordNum]%' ";
if ($WordNum<$WordsTotal-1) $cndtn .= "or";
}
// === Выбираем соответствия из текущего столбца
$dbSet->open("SELECT $field_name[$UniqKeyFieldNum]
FROM $TABLE WHERE $CONDITION AND ($cndtn)");

while ($row=$dbSet->fetchArray()) {
$findedIds[] = $row[$UniqKeyFieldNum];
}
$dbSet->close(); // ---> Очистка результирующего набора
//отработанного запроса
}

unset($row); // ---> Очистка результирующего набора отработанного запроса
$outIds=array_unique($findedIds); // ===> Удаляем дублирующиеся ID

return $outIds;
}

Если в строке поиска было задано: 1 2 3. То запрос внутри функции будет выглядеть следующим образом:
SELECT wares_id FROM wares WHERE wares_cat_id=1 AND
( wares_price LIKE '%1%' or wares_price LIKE '%2%' or wares_price LIKE '%3%' )

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

Но действенный. Если искать одно ключевое слово в цикле зараз, да еще и в нескольких столбцах. Скорость существенно снизится. И как оговаривалось выше, один LIKE может за один раз находить только одно соответствие шаблону и не хочет принимать сразу несколько шаблонов в одном.

Как бы там ни было, пользовался этим до тех пор, пока не доработал алгоритм поиска с использованием регулярных выражений. Вся загвоздка была в генерации этого самого регулярного выражения. Да, возможно, REGEX отрабатывает медленней, чем LIKE, но кто сказал, что и REGEX обязательно надо и можно использовать только в цикле, конечно же, получится еще медленней. И скорость поиска значительно снизится. Но REGEX хоть и медленный, но при умелом обращении очень мощный инструмент. И в данном случае очень важен тот факт, что в REGEX можно за один раз задать все ключевые слова. И это дает существенный прирост скорости. На Pentium 233 разница в скорости была LIKE 0,110 секунд и REGEXP 0,105 секунд - приведены средние величины времени ответа из 10-ти замеров.

Следует также оговориться о том, что функция, приведенная ниже “затачивалась” для использования в “связке”, с функцией описанной в статье “формула выделения текста или поиск как в “google”. В той статье описана функция, которая возвращает сгенерированное регулярное выражение для дальнейшей работы с текстом и естественно, чтобы не выполнять лишней работы будем использовать результаты работы функции (generate_regexp_from_query()). Преимущество в том, что generate_regexp_from_query(), как видно из ее названия генерирует генерирует регулярное выражение из строки поиска. И резултат ее работы используется для “подсветки”, в выводе, искомых ключевых слов в тексте. При этом результат работы generate_regexp_from_query() применим, после небольшой доработки и для поиска в БД. Более подробно, о том откуда берется и что делает функция generate_regexp_from_query() вы можете прочесть в вышеуказанной статье. Итак, идея, ради которой была написана статья:

Входные данные почти такие же как и у предыдущей функции за исключением параметра $searchquery – в который передается результат работы функции generate_regexp_from_query() – заранее сформированное регулярное выражение.
function MySQL_search_by_regex
($searchquery, $TABLE, $field_name, $field_total, $CONDITION="1=1")
{
global $dbObj;
$dbSet=new xxDataset($dbObj);

$UniqKeyFieldNum=0;

$outIds = array();
$findedIds = array();

$search = array(
"/(\|)/",
"/(\\\\\|\\\\\))/",
"/'/",
'/"/',
"/^(.*)$/"
);

$replace = array(
"|",
")",
"\'",
"\"",
"\$1"
);

$searchquery=preg_replace($search, $replace, preg_quote($searchquery));

$row = array();
for ($x=1, $fieldIndex=1; $fieldIndex<$field_total; $fieldIndex++) {
$dbSet->open("SELECT $field_name[$UniqKeyFieldNum] FROM $TABLE WHERE $CONDITION AND
$field_name[$fieldIndex] REGEXP '$searchquery'");

while ($row=$dbSet->fetchArray()) {
$findedIds[] = $row[$UniqKeyFieldNum];
}
$dbSet->close(); // ---> Очистка результирующего набора отработанного запроса
}

unset($row); // ---> Очистка результирующего набора отработанного запроса
$outIds=array_unique($findedIds); // ===> Удаляем дублирующиеся ID

return $outIds;
}

Надеюсь на то, что вам уже должно быть понятно то, что происходит внутри функции. Разве что может возникнуть вопрос зачем искать "/(\\\\\|\\\\\))/" – на человеческом языке искать “\|)”, и заменять на “)”. Дело в том, что если в строке поиска последним был введен пробел например “1 2 3 ” то функция generate_regexp_from_query() вернет (3|2|1|), но такое регулярное выражение не примет MySQL. Все дело в заключающем “|” за которым ничего не стоит. PHP нормально к этому относится, но не MySQL. Так как предыдущая статья, на момент написания этой, была уже написана и отправлена то пришлось исходить из тех условий, в которые сам и создал. Так что после всех подстановок регулярное выражение для MySQL будет выглядеть следующим образом: \(3\|2\|1). Также замечу, что ординарный “\” MySQL отбрасывает не принимая его во внимание. Так что в этом случае это не во вред. А в других, не приходится вылавливать спецсимволы, чтобы лишний раз их экранировать.

В программе все это может выглядеть следующим образом:
…
if (get_magic_quotes_gpc()) {
$searchquery = $_POST['searchq'];
} else {
$searchquery = addslashes($_POST['searchq']);
}
….

$searchquery_regexp=generate_regexp_from_query($searchquery);
….
$findedIds="";
$successearch=0;
// === Ищем соответствие фильтру - после редактирования записей.
if ((!extEmpty($searchquery))||($searchquery=="0")) {

$searchquery_mysql=$searchquery_regexp;
$outIds = MySQL_search_by_regex
($searchquery_mysql, $TABLE, $fields_where_search,
$field_total_for_search, $CONDITION);
unset($field_total_for_search,
$max_col_name_len_srch, $fields_where_search, $field_srch_length);
$tpl->assign("searchquery_mysql", $searchquery_mysql);

$findedIds = implode(", ", $outIds);
if (!extEmpty($findedIds)) $successearch=1;
}
...
// === Если фильтр активен, тогда запрашиваем отфильтрованные записи
// === иначе $CONDITION="1=1" ни на что не влияющее в MySQL условие

$CONDITION.=(!extEmpty($findedIds)) ? "AND $field_name[0] in ($findedIds)" : "AND 1=1" ;
….

// === Названия категорий
$dbSet->open("SELECT $FIELDS_TO_SEL_CAT FROM $TABLE_CAT
WHERE $CONDITION_CAT ORDER BY $sortby_cat ");
// === $dbSet->open("SELECT $FIELDS_TO_SEL_CAT FROM $TABLE_CAT
WHERE $CONDITION_CAT ORDER BY $sortby $order[$direction]
LIMIT $from, $rec_per_page");

$categories=array();
while ($row=$dbSet->fetchArray()) {
$categories[] = $row;
}

Для уменьшения количеств обращений к БД можно модифицировать функцию таким образом, что за один запрос к БД будет произведен поиск по всем ключевым словам и по всем столбцам. Как это выглядит приведено ниже. Естественно, если необходимо организовать сложносоставной поиск по многим столбцам и ключевым словам длина запроса может быть довольно таки длинной.
function MySQL_search_by_regex2
(&$searchquery, $TABLE, $field_name, $field_total, $CONDITION="1=1")
{
global $dbObj;
$dbSet=new xxDataset($dbObj);

$UniqKeyFieldNum=0;

$outIds = array();
$findedIds = array();

$search = array(
"/(\|)/",
"/(\\\\\|\\\\\))/",
"/'/",
'/"/',
"/^(.*)$/"
);

$replace = array(
"|",
")",
"\'",
"\"",
"\$1"
);

$searchquery=preg_replace($search, $replace, preg_quote($searchquery));

$cndtn = "";
$row = array();
for ($x=1, $fieldIndex=1; $fieldIndex<$field_total; $fieldIndex++) {
$cndtn .= " $field_name[$fieldIndex] REGEXP '$searchquery' ";
if ($fieldIndex<$field_total-1) $cndtn .= "OR";
}

$dbSet->open("SELECT $field_name[$UniqKeyFieldNum]
FROM $TABLE WHERE $CONDITION AND ($cndtn)");

while ($row=$dbSet->fetchArray()) {
$findedIds[] = $row[$UniqKeyFieldNum];
}
$dbSet->close(); // ---> Очистка результирующего набора отработанного запроса

unset($row); // ---> Очистка результирующего набора отработанного запроса
$outIds=array_unique($findedIds); // ===> Удаляем дублирующиеся ID

return $outIds;
}

В заключение добавлю, что в этой статье приведены примеры того, как можно организовать свой поиск. И одно из главных достоинств такого поиска – это гибкость и управляемость как входными данными, что будет передано на обработку в MySQL так и выходными. Например, можно усложнить поиск, добавив свой алгоритм вычисления релевантности. Хотя естественно придется тратить дополнительные ресурсы и время на такую обработку. Все конечно зависит от задачи, которую необходимо решить.