Разработка иерархической структуры данных (вложенные наборы)

Я работаю над дизайном для иерархической структуры базы данных, которая моделирует каталог, содержащий продукты (это похоже на этот вопрос ). Платформа базы данных - SQL Server 2005, и каталог довольно большой (750 000 продуктов, 8 500 разделов каталога на 4 уровнях), но относительно статичен (перезагружается один раз в день), поэтому нас интересует только производительность READ.

Общая структура иерархии каталогов:

  • Раздел 1 уровня
    • Раздел 2 уровня
      • Раздел 3 уровня
        • Раздел 4 уровня (продукты связаны с здесь)

Мы используем шаблон «Вложенные наборы» для хранения уровней иерархии и хранения продуктов, существующих на этом уровне, в отдельной связанной таблице. Таким образом, упрощенная структура базы данных будет

CREATE TABLE CatalogueSection
(
    SectionID INTEGER,
    ParentID INTEGER,
    LeftExtent INTEGER,
    RightExtent INTEGER
)

CREATE TABLE CatalogueProduct
(
    ProductID INTEGER,
    SectionID INTEGER
)

У нас есть дополнительное осложнение в том, что у нас есть около 1000 отдельных групп клиентов, которые могут видеть или не видеть все продукты в каталоге. В связи с этим нам необходимо поддерживать отдельную «копию» иерархии каталогов для каждой группы клиентов, чтобы при просмотре каталога они видели только свои продукты и не видели пустых разделов.

Чтобы облегчить это, мы поддерживаем таблицу количества продуктов на каждом уровне иерархии, «свернутую» из раздела ниже. Таким образом, несмотря на то, что продукты напрямую связаны только с самым низким уровнем иерархии, они учитываются вплоть до самого дерева. Структура этой таблицы

CREATE TABLE CatalogueSectionCount
(
    SectionID INTEGER,
    CustomerGroupID INTEGER,
    SubSectionCount INTEGER,
    ProductCount INTEGER
)

Итак, на проблему Производительность очень низка на верхних уровнях иерархии. Общий запрос для отображения «10 лучших» товаров в выбранном разделе каталога (и во всех дочерних разделах) занимает где-то около 1 минуты. На более низких уровнях в иерархии это быстрее, но все еще недостаточно хорошо.

Я поместил индексы (включая охватывающие индексы, где это применимо) во все ключевые таблицы, запустил их через анализатор запросов, мастер настройки индексов и т. Д., Но все еще не могу заставить его работать достаточно быстро.

Мне интересно, является ли дизайн в корне ошибочным или это потому, что у нас такой большой набор данных? У нас есть разумный сервер разработки (3,8 ГГц Xeon, 4 ГБ ОЗУ), но он просто не работает :)

Спасибо за любую помощь

Джеймс

10.12.2008 10:28:10
Возможно, было бы полезно показать нам медленный SQL? Мы могли бы обнаружить что-то, что могло бы стать узким местом.
Jonathan 10.12.2008 10:53:09
3 ОТВЕТА
РЕШЕНИЕ

Используйте закрывающий стол. Если ваша базовая структура является родительским-дочерним с полями ID и ParentID, то структура таблицы замыкания - это ID и DescendantID. Другими словами, таблица замыканий - это таблица предков-потомков, где каждый возможный предок связан со всеми потомками. Вы можете включить поле LevelsBetween, если вам нужно. Реализации таблицы замыкания обычно включают в себя записи с самоссылкой, т. Е. Идентификатор 1 является предком дочернего идентификатора 1 с LevelsBetween 0.

Пример: Parent / Child
ParentID - ID
1 - 2
1 - 3
3 - 4
3 - 5
4 - 6


ID предка / потомка - DescendantID - Уровни между
1 - 1 - 0
1 - 2 - 1
1 - 3 - 1
1 - 4 - 2
1 - 6 - 3
2 - 2 - 0
3 - 3 - 0
3 - 4 - 1
3 - 5 - 1
3 - 6 - 2
4 - 4 - 0
4 - 6 - 1
5 - 5 - 0

Таблица предназначена для устранения рекурсивных объединений. Вы загружаете нагрузку рекурсивного объединения в цикл ETL, который вы выполняете, когда загружаете данные один раз в день. Это сдвигает это от запроса.

Кроме того, это позволяет иерархии переменного уровня. Вы не застрянете в 4.

Наконец, он позволяет размещать продукты в неконечных узлах. Многие каталоги создают «Разные» сегменты на более высоких уровнях иерархии, чтобы создать конечный узел для присоединения продуктов. Вам не нужно этого делать, поскольку промежуточные узлы включены в замыкание.

Что касается индексации, я бы сделал кластерный индекс по ID / DescendantID.

Теперь для вашего запроса производительности. Это берет кусок, но не все. Вы упомянули «Топ 10». Это подразумевает ранжирование по ряду фактов, которые вы не упомянули. Нам нужны детали, чтобы помочь настроить их. Плюс, это получает только листовые разделы, а не продукты. По крайней мере, у вас должен быть индекс по вашему Каталогу, который заказывается по SectionID / ProductID. Я бы сделал так, чтобы соединения между Разделом и Продуктом были соединениями из циклов на основе предоставленной вами мощности. Отчет по разделу каталога должен идти в таблицу закрытия, чтобы получить потомков (используя поиск по кластерному индексу). Этот список потомков затем будет использоваться для получения продуктов из CatalogueProduct с использованием индекса по циклическому поиску по индексу. Затем с этими продуктами вы получите факты, необходимые для ранжирования.

6
10.12.2008 16:55:27
Отлично, это именно то, что мне было нужно и действительно улучшило производительность. Спасибо
James 11.12.2008 11:28:18

Возможно, вам удастся решить проблему групп клиентов с помощью ролей и treeId, но вам придется предоставить нам запрос.

0
10.12.2008 11:24:57

Можно ли рассчитывать ProductCount и SubSectionCount после загрузки каждый день?
Если данные меняются только один раз в день, то, безусловно, стоит рассчитать эти цифры, даже если требуется некоторая денормализация.

0
10.12.2008 15:18:04
Да, мы уже предварительно рассчитываем это ежедневно. Проблема заключается не столько в подсчете продуктов, сколько в фактическом списке товаров в выбранном разделе, а в медленном темпе.
James 10.12.2008 15:23:26
Обновляете ли вы статистику после перезагрузки ваших данных? Если ваши индексы в порядке (настроены только для чтения), то может быть, вы возвращаете слишком много данных? Это та область, на которую я мог бы взглянуть дальше. TBH, помочь больше будет довольно сложно, не видя схемы и / или хранимых процедур.
Bravax 10.12.2008 15:32:39