AdMe.ru: Тэги, полнотекстовый поиск и всё такое…

В этой статье я открою секреты того, как работает adme.ru, а именно я расскажу о том как устроено хранение статей и тэгов, как работает полнотекстовый поиск, поиск по тэгам и всё такое…

В статье приведён пример структуры таблиц, рабочий конфиг Sphinx и немного php кода с примерами выборок.

Немного истории

Когда-то давным давно, тэги на adme.ru хранились способом, который описан в этом посте (способ номер два), а поиск осуществлялся способом, который описан в этом посте (match against).
Реализовано это было следующим образом: В таблице, где хранятся статьи было текстовое поле tags — в этом поле хранились айдишники тэгов вот в таком виде: «3a10 s3 1zog s7 4eus s8 vx rt 4lcd 4gv4 3c4i 3c3q 3a09 39za» — это есть ничто иное как id тэгов в 36-чной системе счисления (была выбрана из-за компактности представления больших чисел) перечисленные через пробел. Как раз этот способ хранения позволял получать из таблицы статьи по определенному тэгу или тэгам. Конечно же, для функционирования такой схемы необходимо было держать таблицу в MyISAM, по полю tags сделать fulltext индекс, а в my.cnf указать ft_min_word_len=0, чтобы полнотекстовый поиск работал и для тэгов чей айдишник в 36чной системе будет меньше 3-х знаков (или 2-х, уже не помню). Теперь про поиск: Полнотекстовый поиск работал не по исходной таблице а по своей собственной, которая пополнялась и изменялась в те моменты, когда редактора добавляют, удаляют или изменяют статьи. В этой таблице для каждой статьи было текстовое поле, которое содержало словоформы. Сами словоформы строились не только из контента, заголовка и анонса статьи но и из тэгов принадлежащих этой статье. И опять, наш не любимый MyISAM, fulltext индекс и match against.

К сожалению, такое хранение тэгов и такой механизм поиска имеют несколько важных недостатков:

1. При добавлении или изменении записи в таблице (MyISAM) со статьями лочится вся таблица, сама процедура добавления/изменения записи длится достаточно долго и как следствие все остальные клиенты, которые хотят что-то из таблицы получить встают в очередь. Статьи редактируются и добавляются постоянно, что приводит к постоянным тормозам сайта.
2. Полнотекстовый поиск с использованием match against очень и очень тормозная штука и дает значительную нагрузку на базу данных.
3. Это работает медленно.
4. Это работает очень медленно.
5. Это работает пипец как медленно.

Все эти недостатки заставили нас задуматься и принять правильное решение.

Какое такое решение

Связи тэгов и материалов хранить в виде отдельной таблицы. Выборки материалов по тэгам, по временному диапазону и полнотекстовый поиск отдать Sphinx'у.
В результате Sphinx избавил нас от всех недостатков предыдущей схемы и не добавил ни одного нового. В этой статье я не буду останавливаться на моментах связанных с установкой Sphinx и не буду рассказывать про sphinx-api — будем считать что всё это читателю уже знакомо.

Хранение тэгов и статей

Требования к тэгам у нас такие:

1. Должна быть возможность задавать тэгам синонимы, например: Ролики, ТВ-ролики и так далее. Так же нужна возможность назначить главный из этих синонимов.
2. Должна быть возможность задавать тэгам их значения на разных языках, например на русском тэг будет представлен как «Ролики», а на английском как «TV-Spots» или «Reels», для других языков тоже должно соблюдаться правило из п.1
3. Тэги должны быть организованы в многоуровневые деревья, чтобы можно было классифицировать с их помощью обьекты по нескольким признакам. Например тэг «Россия» является дочерним по отношению к тэгу «География».

Из этих требований вырисовывается примерно следующая структура таблиц:
tags
id
path — URL тэга, строка из латинских букв, например для тэга «Креативный обзор» это будет «kreativnyj_obzor»
left_key — дерево
right_key — дерево
gr_id — идентификатор группы тэгов (дерева). необходим для хранения нескольких деревьев в одной таблице и для определения принадлежности конкретного тэга к конкретному дереву.
tags_names
tree_id — идентификатор тэга из таблицы tags
name — представление тэга на естественном языке, например Россия, Russia
primary — флаг основного значения name для текущего tree_id
lang — код языка
Структура таблиц статей (упрощенный вариант):
articles
id
header — заголовок
anounce — анонс
published_at — дата публикации
modified_at — дата последней модификации
published — флаг опубликованности материала
articles_content
id — id статьи из articles
content — большой кусок текста — контент статьи
articles_sphinx — эта таблица всегда содержит только одну запись.
ts — время последней полной индексации
Связи тэгов и статей:
articles_tags
id — идентификатор статьи
tag_id — идентификатор тэга
order — порядок тэга (это поле для нас важно, так как мы строим полный URL статьи по её первому тэгу, вот пример)

Как индексировать real-time

Для real-time индексации мы используем два индекса — основной индекс содержит в себе все статьи и обновляется по ночам, а delta индекс содержит в себе статьи добавленные или модифицированные сегодня.

Соответственно при каждом изменении или добавлении статьи мы запускаем переиндексацию delta индекса.
Так как сам delta индекс содержит не много материалов — время индексации очень короткое. Основной же индекс мы переиндексируем по ночам.

При этом поиск всегда осуществляется сразу по двум индексам — по основному и delta.

Рабочий конфиг Sphinx для real-time индексации и поиска по тэгам

source articles
{	
	type			= mysql
	sql_host		= *********
	sql_user		= *********
	sql_pass		= *********
	sql_db			= *********
	sql_port		= 3306  

	sql_query_pre		= SET NAMES utf8
	sql_query		= SELECT article.id, article.id AS idt, article.header, article.anounce, article_content.content, article.published_at, 0 AS deleted,\
					(SELECT concat(concat('_tag', replace(group_concat(tags_names.tree_id), ',', ' _tag'), ' '), replace(group_concat(tags_names.name), ',', ' '))\
					FROM tags_names AS tags_names\
					JOIN articles_tags AS articles_tags ON (tags_names.tree_id = articles_tags.tag_id)\
					WHERE articles_tags.id = article.id\
					) AS `tags_names`\
				  FROM articles AS article\
				  LEFT JOIN articles_content AS article_content on (article.id = article_content.id)\
				  WHERE article.published = 1;
	sql_query_post_index	= UPDATE articles_sphinx set ts = (select max(modyfied_at) from articles)
	sql_attr_timestamp	= published_at
	sql_attr_uint		= idt
	sql_attr_uint		= deleted
	sql_attr_multi		= uint tags from query; SELECT id, tag_id FROM articles_tags order by `order` asc
	sql_ranged_throttle	= 0
}

source articles_delta : articles
{
	sql_query		= SELECT article.id, article.id AS idt, article.header, article.anounce, article_content.content, article.published_at, 0 AS deleted,\
					(SELECT concat(concat('_tag', replace(group_concat(tags_names.tree_id), ',', ' _tag'), ' '), replace(group_concat(tags_names.name), ',', ' '))\
					FROM tags_names AS tags_names\
					JOIN articles_tags AS articles_tags ON (tags_names.tree_id = articles_tags.tag_id)\
					WHERE articles_tags.id = article.id\
					) AS `tags_names`\
				  FROM articles AS article\
				  LEFT JOIN articles_content AS article_content on (article.id = article_content.id)\
				  WHERE article.published = 1 AND modyfied_at > (SELECT ts FROM articles_sphinx limit 1)

	sql_query_post_index	= select 1; 

	sql_attr_multi		= uint tags from query; SELECT id, tag_id FROM articles_tags\
							WHERE id>=(SELECT min(id) FROM articles WHERE modyfied_at > (SELECT ts FROM articles_sphinx limit 1))\
							AND id<=(SELECT max(id) FROM articles WHERE modyfied_at > (SELECT ts FROM articles_sphinx limit 1))
}

index articles_index
{
	source			= articles
	path			= /usr/local/sphinx/var/data/articles
	docinfo			= extern
	mlock			= 0
	morphology		= stem_enru
	min_word_len		= 1
	charset_type		= utf-8
	html_strip		= 1
}

index articles_index_delta : articles_index
{
	source		= articles_delta
	path		= /usr/local/sphinx/var/data/articles_delta
}

Некоторые строки конфига Sphinx'а с комментариями

source articles
{	
	sql_query_post_index = UPDATE articles_sphinx set ts = (select max(modyfied_at) from articles)
}

Каждый раз после полной индексации статей мы устанавливаем время в таблице articles_sphix как максимальное время модификации статей — это время у нас
используется в source articles_delta для того, чтобы индексировать только материалы которые изменились с момента последней полной индексации.

source articles
{	
	sql_attr_multi = uint tags from query; SELECT id, tag_id FROM articles_tags order by `order` asc
}

Это просто multi-value аттрибут — фактически для каждой статьи мы получаем поле со списком айдишников тэгов по значениям айдишников мы можем фильтровать результаты поиска.

Часть основного запроса из обих source:

(SELECT concat(concat('_tag', replace(group_concat(tags_names.tree_id), ',', ' _tag'), ' '), replace(group_concat(tags_names.name), ',', ' '))\
 FROM tags_names AS tags_names\
 JOIN articles_tags AS articles_tags ON (tags_names.tree_id = articles_tags.tag_id)\
 WHERE articles_tags.id = article.id\
) AS `tags_names`

Этот запрос для каждой статьи собирает строку вида: "_tag1020 _tag1342 _tag1243 Россия Франция" — эта строка содержит представления тэгов на естественном языке и в виде строк _tagN, где N — айдишник тэга. Как вы уже поняли тэги в индексе представлены в трех видах: в виде multi-value аттрибута, в виде строк "_tag1020" и на естественном языке, чуть ниже по тексту рассказано почему сделано именно так.

source articles_delta : articles
{
	sql_query_post_index = select 1; 
}

Здесь всё просто — нам нужно перекрыть запрос из source от которого мы наследовались.

Почему мы отправляем в Sphinx тэги в трех видах:

В аттрибутах:

1. Multi-value поле со списком айдишников тэгов для реализации выборок по одному или множеству тэгов с условиями И и ИЛИ при наличии дополнительной поисковой строки. Пример можно посмотреть вот тут. Этот пример с использованием условия И. Multi-value аттрибут не может быть использован для поиска похожих материалов, так как возвращает для всех материалов weight=1 — то есть мы не можем определить релевантность, а нам нужно делать по ней сортировку.

В полнотекстовом индексе:

1. Представление тэга на естественных языках, то есть к индексируемому тексту добавляются все представления тэга: «Россия Russia Раша» для полнотекстового поиска.
2. Строковое значение в виде "_tagN", где N — id тэга, для поиска похожих материалов — этот способ частично покрывает задачи Multi-value поля, но не подходит для выборок по тэгам по условию ИЛИ при наличии дополнительной подстроки поиска, так как в качестве поисковой строки уже используются те самые _tagN.

Индексация

'/usr/local/sphinx/bin/indexer articles_index_delta --rotate' — это запускаем при каждом сохранении статьи
'/usr/local/sphinx/bin/indexer articles_index --rotate' — а это запускаем по cron по ночам, или несколько раз в день — как удобно.

Выборка по одному и более тэгов

При выборке материалов по тэгам существует две задачи:

1. Условие И: Выбрать все материалы по конкретному тэгу/тэгам так, чтобы все запрашиваемые тэги были в материале
2. Условие ИЛИ: Выбрать все материалы по конкретному тэгу/тэгам так, чтобы хотя бы один из запрашиваемых тэгов был в материале

С использованием сфинкса это решается достаточно просто.
Условие И и ИЛИ с использованием фильтров
И:

$tags_ids = array(1,2,3,4,5,6);
foreach ($tags_ids as $tag_id) {
	// Добавляет новые фильтры в каждой итерации, в каждом фильтре 
	// по одному тэгу - в результате получаем условие И
	$sphinx_obj->SetFilter('tags', array($tag_id)); 
}
$search_res = $sphinx_obj->Query('','articles_index articles_index_delta');

ИЛИ:

$tags_ids = array(1,2,3,4,5,6);
$sphinx_obj->SetFilter('tags', $tags_ids); 
$search_res = $sphinx_obj->Query('','articles_index articles_index_delta');

Как видите разница лишь в том, что в первом случае мы получим несколько фильтров, каждому из которых должен соответствовать искомый материал, а во втором случае получим один фильтр. Цитата из документации Sphinx: «function SetFilter ( $attribute, $values, $exclude=false ) Only those documents where $attribute column value stored in the index matches any of the values from $values array will be matched (or rejected, if $exclude is true).» — полностью раскрывает суть подхода.

Условие И и ИЛИ без использования фильтров (работает быстрее чем фильтры)
И:
Для этого нужно все айдишники искомых тэгов привести к их псевдо-строкам.

$tags_query_string = '_tag1 _tag2 _tag3';
$sphinx_obj->SetMatchMode(SPH_MATCH_ALL);
$search_res = $sphinx_obj->Query($tags_query_string,'articles_index articles_index_delta');

ИЛИ:
Из предыдущего примера нужно заменить SPH_MATCH_ALL на SPH_MATCH_ANY

Выборка похожих материалов

В нашем случае похожесть материалов определяется похожестью их тэгов.

// выкидываем из выборки текущий материал
$sphinx_obj->SetFilter('idt', array($current_article_id), true); 
$sphinx_obj->SetMatchMode(SPH_MATCH_ANY);
// сортируем по весу и по дате публикации так, чтобы свежак был сверху
$sphinx_obj->SetSortMode(SPH_SORT_EXTENDED, '@weight DESC, published_at DESC'); 
$search_res = $sphinx_obj->Query('_tag1 _tag2 _tag3 _tag4', 'articles_index articles_index_delta'); 

Полнотекстовый поиск

Тут всё просто

$sphinx_obj->SetRankingMode(SPH_RANK_PROXIMITY_BM25);
$sphinx_obj->SetMatchMode(SPH_MATCH_ALL);
$sphinx_obj->Query('хочу слона', 'articles_index articles_index_delta');

Итоги

Схема описанная в данной статье стабильно работает на adme.ru уже достаточно долгое время при следующих условиях:
Тэгов: 87 402 штуки.
Статей: 22 069, Связей с тэгами: 132 935.
Изображений: 103 353, Связей с тэгами: 751 950.
Видео: 68 316, Связей с тэгами: 682 415.
Разумеется приведенный код и структуры таблиц значительно упрощены для наглядности, но тем не менее вполне могут быть использованы в реальных проектах.

btw

В конфиге sphinx есть секция searchd, в которой при большом количество возвращаемых результатов рекомендуется подкрутить параметр max_matches. У нас он стоит в 20 000, так как материалов по тэгу usa больше 15000, но это не в статьях, а в энциклопедии рекламы. По умолчанию же этот параметр равен 1000.

Вопросы, советы, критика — все приветствуется.