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. Тэги должны быть организованы в многоуровневые деревья, чтобы можно было классифицировать с их помощью обьекты по нескольким признакам. Например тэг «Россия» является дочерним по отношению к тэгу «География».

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

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

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

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

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

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

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

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

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. Условие ИЛИ: Выбрать все материалы по конкретному тэгу/тэгам так, чтобы хотя бы один из запрашиваемых тэгов был в материале

ИЛИ: Как видите разница лишь в том, что в первом случае мы получим несколько фильтров, каждому из которых должен соответствовать искомый материал, а во втором случае получим один фильтр. Цитата из документации 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).» — полностью раскрывает суть подхода.

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

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

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

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

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