Прискорений REGEX або пошук як в google, частина 2.

Не так давно з'явилася необхідність написати більш-менш універсальний пошук в 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 так і вихідними. Наприклад, можна ускладнити пошук, додавши свій алгоритм обчислення релевантності. Хоча природно доведеться витрачати додаткові ресурси і час на таку обробку. Всі звичайно залежить від завдання, яку необхідно вирішити.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*