Высокопроизводительная многоуровневая фильтрация тегов
У меня есть большая база данных исполнителей, альбомов и треков. Каждый из этих элементов может иметь один или несколько тегов, назначенных с помощью таблиц склеивания (track_attributes, album_attributes, artist_attributes). Существует несколько тысяч (или даже сотен тысяч) тегов, применимых к каждому типу товаров.
Я пытаюсь выполнить две задачи, и мне очень трудно добиться приемлемого выполнения запросов.
Задача 1) Получить все треки с любыми заданными тегами (если они предоставлены) от исполнителей, которые имейте любые заданные теги (если они предусмотрены) в альбомах с любыми заданными тегами (если они предусмотрены). Любой набор тегов может отсутствовать (т.е. активен только тег трека, нет тегов исполнителя или альбома)
Вариация: Результаты также представляются исполнителем или альбомом, а не треком
Задача 2) Получите список тегов, которые применяются к результатам предыдущего фильтра, вместе с подсчетом количества треков с каждым заданным тегом.
То, что мне нужно, - это некоторые общие рекомендации в подход. Я пробовал временные таблицы, внутренние соединения, В(), все мои усилия до сих пор приводят к медленным ответам. Хороший пример результатов, к которым я стремлюсь, можно увидеть здесь: http://www.yachtworld.com/core/listing/advancedSearch.jsp, за исключением, у них есть только один уровень тегов, я имею дело с тремя.
Структуры таблиц:
Table: attribute_tag_groups
Column | Type |
------------+-----------------------------+
id | integer |
name | character varying(255) |
type | enum (track, album, artist) |
Table: attribute_tags
Column | Type |
--------------------------------+-----------------------------+
id | integer |
attribute_tag_group_id | integer |
name | character varying(255) |
Table: track_attribute_tags
Column | Type |
------------+-----------------------------+
track_id | integer |
tag_id | integer |
Table: artist_attribute_tags
Column | Type |
------------+-----------------------------+
artist_id | integer |
tag_id | integer |
Table: album_attribute_tags
Column | Type |
------------+-----------------------------+
album_id | integer |
tag_id | integer |
Table: artists
Column | Type |
------------+-----------------------------+
id | integer |
name | varchar(350) |
Table: albums
Column | Type |
------------+-----------------------------+
id | integer |
artist_id | integer |
name | varchar(300) |
Table: tracks
Column | Type |
-------------+-----------------------------+
id | integer |
artist_id | integer |
album_id | integer |
compilation | boolean |
name | varchar(300) |
РЕДАКТИРОВАТЬ Я использую PHP, и я не против делать какую-либо сортировку или другие хиджины в скрипте, моя проблема №1 - скорость вернуть.
6 answers
Вероятно, вам следует попытаться денормализовать свои данные. Ваша структура оптимизирована для загрузки вставки/обновления, но не для запросов. Как я понял, у вас будет гораздо больше запросов на выбор, чем запросов на вставку/обновление.
Например, вы можете сделать что-то вроде этого:
Храните свои данные в нормализованной структуре.
Создайте сводную таблицу, подобную этой
track_id, artist_tags, album_tags, track_tags
1 , jazz/pop/, jazz/rock, /heavy-metal/
or
track_id, artist_tags, album_tags, track_tags
1 , 1/2/, 1/3, 4/
Чтобы ускорить поиск, вам, вероятно, следует создать ПОЛНОТЕКСТОВЫЙ индекс в столбцах *_tags
Запросите эту таблицу с помощью sql, подобный
select * from aggregate where album_tags MATCH (track_tags) AGAINST ('rock')
Перестраивайте эту таблицу постепенно один раз в день.
Если вам нужна скорость, я бы посоветовал вам заглянуть в Solr/Lucene. Вы можете хранить свои данные и выполнять очень быстрый поиск, вызывая Solr и анализируя результат с помощью PHP. И в качестве дополнительного преимущества вы также получаете фасетный поиск (что является задачей 2 вашего вопроса, если я правильно его интерпретирую). Недостатком, конечно, является то, что у вас может быть избыточная информация (один раз сохраненная в БД, один раз в хранилище документов Solr). И это действительно займет некоторое время для настройки (ну, вы могли бы многому научиться у Drupal Интеграция Solr).
Просто ознакомьтесь со справочными документами PHP для Solr.
Вот статья о том, как использовать Solr с PHP, на всякий случай: http://www.ibm.com/developerworks/opensource/library/os-php-apachesolr/.
Я думаю, что ответ во многом зависит от того, сколько денег вы хотите потратить на свой проект - есть некоторые задачи, которые даже теоретически невозможно выполнить при соблюдении строгих условий (например, вы должны использовать только один слабый сервер). Я буду считать, что вы готовы обновить свою систему.
Прежде всего - ваша структура таблиц заставляет ОБЪЕДИНЯТЬСЯ - я думаю, вам следует избегать их, если это возможно, при написании высокопроизводительных приложений. Я не знаю, что "attribute_tag_groups" - это, поэтому я предлагаю структуру таблицы: тег (varchar 255), идентификатор (int), id_type (перечисление (трек, альбом, исполнитель)). Идентификатор может быть artist_id, track_id или album_id в зависимости от id_type. Таким образом, вы тоже сможете собрать все свои данные в одной таблице, но, конечно, для этого потребуется гораздо больше памяти.
Далее - вам следует рассмотреть возможность использования нескольких баз данных. Это поможет еще больше, если каждая база данных будет содержать только часть ваших данных (каждый поиск будет выполняться быстрее). Решение о том, как распределить ваши данные между базами данных, как правило, довольно сложная задача: я предлагаю вам составить некоторую статистику о длине тегов, найти диапазоны длины, которые позволят подсчитать аналогичные результаты trac/исполнителей, и жестко закодировать их в свой код поиска.
Конечно, вам следует подумать о настройке MySQL (я уверен, что вы это сделали, но на всякий случай) - все ваши таблицы должны находиться в оперативной памяти - если это невозможно, попробуйте получить SSD-диски, рейды и т. Д. Правильная индексация и типы/настройки баз данных тоже очень важны (MySQL может даже показать некоторые узкие места во внутренних статистика).
Это предложение может показаться безумным, но иногда полезно позволить PHP выполнять некоторые вычисления, которые MySQL может выполнить сам. Базы данных MySQL намного сложнее масштабировать, в то время как сервер для обработки PHP может быть добавлен в считанные минуты. И разные потоки PHP могут работать на разных ядрах процессора - у MySQL с этим проблемы. Вы можете повысить производительность PHP, используя некоторые продвинутые модули (вы даже можете написать их самостоятельно - профилируйте свои PHP-скрипты и узкие места жесткого кода в быстром коде на языке Си).
Последнее, но я думаю, что самое важное - вы должны использовать какой-то тип кэширования. Я знаю, что это действительно сложно, но я не думаю, что был какой-то большой проект без действительно хорошей системы кэширования. В вашем случае некоторые теги наверняка будут намного популярнее других, поэтому это должно значительно повысить производительность. Кэширование - это своего рода искусство. В зависимости от того, сколько времени вы можете потратить на это и сколько ресурсов доступно, вы можете использовать 99 % всех запросов кэш.
Использование других баз данных/инструментов индексирования может помочь вам, но вы всегда должны учитывать теоретическое сравнение скорости запросов (O(n), O(nlog(n))...), чтобы понять, действительно ли они могут вам помочь - использование этих инструментов иногда дает вам низкий прирост производительности (например, постоянные 20%), но они могут усложнить дизайн вашего приложения, и в большинстве случаев это того не стоит.
По моему опыту, в большинстве "медленных" баз данных MySQL нет правильного индекса и/или запросов. Поэтому я бы сначала проверил это:
- Убедитесь, что все поля идентификаторов данных talbes являются первичными индексами. На всякий случай.
- Для всех таблиц данных создайте индекс для полей внешнего идентификатора, а затем идентификатор, чтобы MySQL мог использовать его в поиске.
- Для ваших таблиц склеивания задайте первичный ключ для двух полей, сначала тему, затем тег. Это для обычного просмотра. Затем создайте обычный индекс на идентификаторе тега. Это для поиска.
- Все еще медленно? Вы используете MyISAM для своих столов? Он предназначен для быстрых запросов.
- Если все еще медленно, запустите ОБЪЯСНЕНИЕ для медленного запроса и опубликуйте как запрос, так и результат в вопросе. Предпочтительно с импортируемым sql-дампом вашей полной структуры базы данных.
Вещи, которые вы можете попробовать:
Используйте Анализатор запросов для изучения узких мест ваших запросов. (В большинстве случаев базовые базы данных выполняют потрясающую работу по оптимизации)
Структура вашей таблицы хорошо нормализована, но личный опыт показал мне, что вы можете архивировать гораздо более высокие уровни производительности с помощью структур, которые позволяют избежать соединений и вложенных запросов. В вашем случае я бы предложил хранить информацию о теге в одном поле. (Это требуется поддержка со стороны базовых баз данных)
До сих пор.
Проверьте свои индексы и правильно ли они используются. Возможно, MySQL не справляется с этой задачей. PostgreSQL должен быть похож на используемый, но имеет лучшую производительность в сложных ситуациях.
На совершенно другом пути, уменьшите карту Google и используйте одну из этих новых причудливых баз данных без SQL для действительно очень больших наборов данных. Это позволяет выполнять распределенный поиск на нескольких серверах параллельно.