Построение индекса по иерархии записей в реляционной БД
•Download as PPTX, PDF•
0 likes•614 views
Доклад на SEF-2009
![Добавляем связь parent - child
Появляется следующий набор путей:
foreach
x in descendants( child ), dx in distances( x, child ),
y in ancestors( parent ), dy in distances( parent, y )
добавляется count(x,child,dx)*count(parent,y,dy)
путей между x и y с длиной dx+dy+1.
• count(x,y,d) – количество различных путей из x в y с
длиной d
• ancestors(x) и descendants(x) содержат x
• distances(x, x) == [ ]](https://image.slidesharecdn.com/amayorovhindex-090527015412-phpapp01/85/-24-320.jpg)
Построение индекса по иерархии записей в реляционной БД
- 1. Построение индекса по иерархии записей в реляционной БД Андрей Майоров. BYTE-force
- 2. Дано • Реляционная база данных. Object * • Данные логически образуют id иерархическую структуру. name • Каждая запись может иметь более одного предка, но в иерархии не должно быть циклов. Другими словами, данные Relation * образуют направленный parent ациклический граф. child
- 3. Задача • Максимально быстро выбирать всех потомков или всех предков заданной записи или группы записей. • Выбирать записи, удаленные от заданной на нужное число шагов. • Быстро добавлять и удалять записи из иерархии.
- 4. Зачем? • Выбирать сотрудников из отдела. • Товары из раздела каталога. • Статьи из раздела с подразделами. • ...
- 5. Photo by http://www.flickr.com/photos/shannonpatrick17/ Стандартные средства
- 6. CONNECT BY в Oracle employee SELECT name employee_id FROM employee name START WITH name = 'John' manager CONNECT BY PRIOR employee_id = manager • Выбирает работника по имени John, его подчиненных, подчиненных его подчиненных, etc. • Псевдоколонка LEVEL позволяет ограничить удаленность от стартовой записи.
- 7. Недостатки CONNECT BY • Выполняется столько запросов, сколько существует уровней иерархии. • Довольно сложно делать иерархии, содержащие записи из разных таблиц. • Подход работает только в Oracle.
- 8. Common Table Expressions WITH t( employee, name ) AS ( SELECT employee_id, name FROM employee WHERE name = 'John' UNION ALL SELECT next.employee_id, next.name FROM employee as next INNER JOIN t ON t.employee_id = next.manager ) SELECT name FROM t • Первый SELECT внутри CTE заменяет START WITH, • второй – CONNECT BY PRIOR.
- 9. Сравним CTE с CONNECT BY • Скорость работы должна быть сравнимой. • Гетерогенные иерархии строятся проще. При помощи оператора UNION ALL можно присоединять к иерархии разные таблицы, каждый раз используя специфический критерий связи. • CTE поддерживаются основными коммерческими СУБД, но не популярными СУБД с открытым кодом (MySQL, Firebird, PostgreSQL и др.).
- 10. Построение иерархии вручную • Поддержку рекурсивных запросов можно эмулировать вручную, используя специальную временную таблицу. • Такой подход похож на CTE. Может быть реализован в любой базе данных. • Ручной метод не позволяет надеятся на оптимизацию со стороны СУБД, которая может существовать для штатных средств.
- 11. Стандартные средства... Хороши Плохи • Не требуют • Сопряжены с дополнительных таблиц итеративной выборкой • Очень просто вставлять связанных записей. и удалять записи Даже с индексом по полю связи, это требует столько же выборок, сколько есть уровней иерархии.
- 12. Как ускорить выборку? • Решение – самостоятельно сформировать «индекс» во вспомогательном поле или таблице. • Варианты хранения индекса: – Lineage (materialized path) – Левые и правые индексы – Карта связей
- 13. Lineage (линидж) Источник - http://www.sqlteam.com/article/more-trees-hierarchies-in-sql Данные EmployeeID Name BossID Denis Eaton-Hogg 1001 Denis Eaton-Hogg NULL Bobbi Flekman Ian Faith 1002 Bobbi Flekman 1001 David St. Hubbins 1003 Ian Faith 1002 Nigel Tufnel Derek Smalls 1004 David St. Hubbins 1003 1005 Nigel Tufnel 1003 1006 Derek Smalls 1003 Индекс Node ParentNode EmployeeID Depth Lineage 100 NULL 1001 0 / 101 100 1002 1 /100/ 102 101 1003 2 /100/101/ 103 102 1004 3 /100/101/102/ 104 102 1005 3 /100/101/102/ 105 102 1006 3 /100/101/102/
- 14. Lineage За Против • Все просто и понятно. • Достаточно сложно • Легко добавлять детей к «перевесить» узел – любому узлу. надо пересчитывать пути для всей ветки. • Годится только для деревьев. • Выборки сопряжены со строковыми операциями.
- 15. Левые и правые индексы Данные ID Name ParentID 1 Предприятие 1 Предприятие NULL 2 Управление 2 Управление 1 3 Инфраструктура 5 Энергия 3 Инфраструктура 1 6 Сервисные услуги 4 Производство 1 4 Производство 5 Энергия 3 7 Месторождение А 8 Месторождение Б 6 Сервисные услуги 3 Источник - http://www.osp.ru/os/2003/04/182942/ 7 Месторождение А 4 8 Месторождение Б 4 1 1 16 Индекс ID Left Right Level IsLeaf 1 1 16 1 N 2 2 3 2 Y 2 2 3 4 3 9 10 4 15 3 4 9 2 N 4 10 15 2 N 5 5 6 3 Y 6 7 8 3 Y 5 5 6 7 6 8 11 7 12 13 8 14 7 11 12 3 Y 8 13 14 3 Y
- 16. Левые и правые индексы За Против • Выборки используют • Очень сложно вставить сравнения целых чисел. узел в середину. • Годится только для деревьев.
- 17. Карта связей Image by http://www.flickr.com/photos/36041246@N00/3344
- 18. Карта связей Object RelationMap id parent name child distance count Relation parent child
- 19. Карта связей За Против • Несколько родителей у узла. • При большой • Выборка проводится вложенности «просто по индексу». индексная таблица • Данные можно хранить в может быть очень covering index. большой. • Позволяет делать гетерогенные иерархии Object RelationMap id parent • Есть методика name child эффективного обновления distance индекса. count
- 20. Рассмотрим граф 1 • Выше – родители, ниже – дети. 2 3 • Пути считаем идущими 4 5 снизу вверх. • Длина пути равна 6 7 количеству ребер. • Есть пути с одинаковой длиной.
- 21. Новая связь между 7 и 8 A 1 Появляются новые пути: • Прямой путь из 8 в 7 (длина 1). 3 • Пути из 8 через 7 во все объекты, в 4 5 которые можно попасть из 7. Длина всех путей будет на единицу 7 больше, чем из 7. 8
- 22. Новая связь между 7 и 8 • Прямой путь из 8 в 7 (длина 1). B 1 • Из 8 ко всем предкам объекта 7 (длина + 1). 3 • Из 7 ко всем потомкам объекта 8 4 5 (длина + 1). • Отовсюду, докуда есть пути из 7, 7 мы теперь можем попасть в 9 и A, и наоборот. 8 Длина пути между 9 и 3 будет равна сумме длин путей до 8 и от 9 A 7 соответственно плюс 1.
- 23. Количество одинаковых путей B x 1 • Если из объекта 7 в объект x можно попасть cx путями с 3 длиной dx, 4 5 • а из объекта y в объект 8 – cy путями с длиной dy, 7 • то из y в x можно будет попасть cx*cy путями с длиной dx+dy+1. 8 Путей: 1 * 2 = 2 9 A Длина: 1 + 2 +1 = 4 y
- 24. Добавляем связь parent - child Появляется следующий набор путей: foreach x in descendants( child ), dx in distances( x, child ), y in ancestors( parent ), dy in distances( parent, y ) добавляется count(x,child,dx)*count(parent,y,dy) путей между x и y с длиной dx+dy+1. • count(x,y,d) – количество различных путей из x в y с длиной d • ancestors(x) и descendants(x) содержат x • distances(x, x) == [ ]
- 25. В нашей схеме данных … Object RelationMap id parent name child distance count Relation parent … RelationMap содержит все связи child между всеми объектами на данный момент времени. Значит ее можно использовать для построения множества новых путей.
- 26. SQL: множество новых путей SELECT d.child, a.parent, a.distance + d.distance + 1, sum( a.count * d.count ) FROM ( SELECT * FROM RelationMap WHERE child = @iParent UNION SELECT @iParent, @iParent, 0, 1 ) AS a, ( SELECT * FROM RelationMap WHERE parent = @iChild UNION SELECT @iChild, @iChild, 0, 1 ) AS d GROUP BY d.object, a.ancestor, a.distance + d.distance + 1
- 27. Принципы работы запроса • Выбираются все пути, в которых @iParent – потомок. К выборке добавляется путь от этого объекта к самому себе с длиной 0 и количеством повторений 1. • Выбираются все пути, в которых @iChild – предок. Также добавляется путь к самому себе. • Делается декартово произведение этих двух выборок, и получается множество всех возможных путей от объектов «сверху» к объектам «снизу». Длины путей складываются, количество повторений перемножается. • В результате складывания длин путей, мы можем получить новые наборы путей с одинаковой длиной. Поэтому мы группируем набор записей и суммируем количество повторений внутри групп.
- 28. SQL: добавление путей UPDATE RelationMap SET count = om.count + st.count FROM @tSubTree st JOIN RelationMap om ON st.child = om.child AND st.parent = om.parent AND st.distance = om.distance INSERT INTO RelationMap SELECT st.child, st.parent, st.distance, st.count FROM @tSubTree st LEFT JOIN RelationMap om ON st.child = om.child AND st.parent = om.parent AND st.distance = om.distance WHERE om.child IS NULL
- 29. SQL: удаление путей UPDATE RelationMap SET count = om.count - st.count FROM @tSubTree st JOIN RelationMap om ON st.child = om.child AND st.parent = om.parent AND st.distance = om.distance DELETE FROM RelationMap WHERE count = 0
- 30. Особенности алгоритма • Алгоритм не позволяет надежно вставлять несколько связей одновременно. Каждую вставку нужно просчитать последовательно. Порядок выполнения вставок не важен. • Карта путей помогает избежать появления циклов в графе объектов. Для этого достаточно проверить, не существует ли уже путей, ведущих от @iParent к @iChild (т.е. в обратном направлении). • Все это очень удобно делать в триггерах на таблице связей.
- 31. Гетерогенный случай • Возможны случаи, когда логическая иерархия покрывает несколько таблиц с данными. • Основной сложностью в этом случае является построение такой таблицы RelationMap, которая могла бы хранить ссылки на объекты разных типов. • Обобщающую таблицу с путями, можно поддерживать тем же способом – при помощи триггеров на таблицах со связями.
- 32. В заключение • Придумали сами. • Используем уже несколько лет. • И вам советуем. • Подробная статья – на сайте: http://blogs.byte-force.com/files/folders/articles_ru/entry1148.aspx