Способы хранения иерархических структур данных

Денис Иванов (Инженер-разработчик Python, Rambler&Co) @ Moscow Python Meetup № 41

"В докладе рассматриваются методы хранения иерархических структур в реляционных базах данных. Приводятся примеры их использования. Перечисляются плюсы и минусы каждого метода. Приводятся примеры библиотек для Django, которые реализуют каждый из рассмотренных методов".

Видео: http://www.moscowpython.ru/meetup/41/sposoby-hranenija-ierarhicheskih-struktur-dannyh/

Transcript

1. Способы хранения иерархических структур данных
3. 40 50+ 700 1700+ млн человек суммарная аудитория группы количество изданий, сервисов и проектов разработчиков человек в хорошей компании
5. Языки и технологии
6. Контакты В группе компаний Rambler&Co всегда есть открытые вакансии для тех, кто хочет профессионально расти и развиваться, занимаясь тем, что по-настоящему нравится hr@rambler-co.ru www.rambler-co.ru/jobs
7. Применение • Каталог интернет-магазина • Вложенные страницы сайта • Комментарии • Данные о предприятии • ...
8. Основные операции • Выбрать всех детей элемента • Выбрать всех потомков элемента • Выбрать цепочку предков элемента • Переместить элемент (и его потомков) из одной группы в другую • Удалить элемент из таблицы (со всеми потомками)
9. Типы иерархических структур • Adjacency List • Nested Set • Materialized Path • Closure Table
10. Adjacency List Comment 1 1 null Comment 2 2 1 Comment 4 3 2 Comment 5 4 2 id title parent_id depth 1 Comment 1 null 1 2 Comment 2 1 2 3 Comment 3 2 3 4 Comment 4 2 3 ... ... ...
11. INSERT INTO comment VALUES (1, 12, ‘comment 12’); DELETE FROM comment WHERE comment.id = 12; SELECT * FROM comment ORDER BY id; SELECT * FROM comment AS c WHERE c.parent_id = {parent_id}; SELECT * FROM comment AS c WHERE c.parent_id = {children_ids}; SELECT * FROM comment AS c WHERE c.parent_id = {children2_ids}; Основные операции
12. Adjacency List Преимущества: • Возможность хранения больших деревьев • Легко вносить изменения • Высокая скорость доступа к наследникам
13. Adjacency List Недостатки : • Выборка веток дерева на всю глубину • Выборка потомков или предков элемента
14. Nested Set Comment 1 1 12 Comment 2 2 7 Comment 3 3 4 Comment 4 5 6 Comment 5 8 11 Comment 6 9 10 id title lft rgt depth 1 Comment 1 1 12 1 2 Comment 2 2 7 2 3 Comment 3 3 4 3 4 Comment 4 5 6 3 ... ... ... ...
15. Основные операции Вставка нового узла: BEGIN; UPDATE comment SET rgt = rgt + 2 WHERE rgt > {parent_comment.rgt}; UPDATE comment SET lft = lft + 2 WHERE lft > {parent_comment.rgt}; INSERT INTO comment VALUES( 0, 'comment 12', {parent_comment.rgt} + 1, {parent_comment.rgt} + 2, 1 ); COMMIT;
16. Основные операции Выборка всех дочерних элементов: SELECT * FROM comment AS node, comment AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 1 ORDER BY node.lft;
17. Nested Set Преимущества: • Хорошо оптимизирован для чтения произвольных частей дерева
18. Nested Set Недостатки: • Сложности при изменении структуры дерева
19. Materialized Path Comment 1 1 Comment 2 1.1 Comment 3 1.1.1 Comment 4 1.1.2 Comment 5 1.2 Comment 6 1.2.1 id title path depth 1 Comment 1 1 1 2 Comment 2 1.1 2 3 Comment 3 1.1.1 3 4 Comment 4 1.1.2 3 5 Comment 5 1.2 2
20. Основные операции Поиск предков: SELECT t1.name FROM comment t1, comment t2 WHERE t2.path LIKE CONCAT( t1.path, '.%') AND t2.id = 3;
21. Основные операции Выборка ветки: SELECT comment, strfind(path, '.') AS level FROM comment WHERE path LIKE '1.1%';
22. Materialized Path Преимущества: • Удобство чтения и изменения дерева • Подходит для больших объемов данных
23. Materialized Path Недостатки: • Вставка узла в середину существующей структуры • Тяжелый перенос одной ветки в другую
24. Closure Table id title ... 1 Node 1 ... 2 Node 2 ... 5 Node 5 ... id ancestor descendant depth 1 1 1 1 2 1 2 2 3 2 2 2 4 1 5 3 5 2 5 3 6 5 5 3
25. Closure Table Comment 1 Comment 2 Comment 3 Comment Comment 5
26. Основные операции Выборка всех потомков: SELECT * FROM comment AS c JOIN tree_paths ON (c.id = tree_paths.descendant_id) WHERE tree_paths.ancestor_id = 2;
27. Основные операции Выборка всех предков: SELECT * FROM comment AS c JOIN tree_paths ON (c.id = tree_paths.ancestor_id) WHERE tree_paths.descendant_id = 2;
28. Основные операции Вставка нового узла: INSERT INTO comment VALUES (8, ‘Comment 8’, ...) INSERT INTO tree_paths (ancestor_id, descendant_id) SELECT ancestor_id, 8 FROM tree_path WHERE descendant_id = 5 UNION ALL SELECT 8, 8;
29. Основные операции Удаление вложенного дерева: DELETE FROM comment WHERE id IN ( SELECT descendant FROM ( SELECT descendant FROM comment p JOIN tree_paths t ON p.id = t.descendant WHERE t.ancestor = 7 ) AS tmptable );
30. Основные операции DELETE FROM tree_paths WHERE descendant IN ( SELECT descendant FROM ( SELECT descendant FROM tree_paths WHERE ancestor = 7 ) AS tmptable )
31. Closure Table Преимущества: • Простота чтения произвольных частей дерева
32. Closure Table Недостатки: • Добавление новых элементов • Удаление элементов
33. • django-treebeard • django-mptt • django-polymorphic-tree • django-closuretree
34. Полезные ссылки • django-mptt: https://github.com/django-mptt/django-mptt • django-treebeard: https://bitbucket.org/tabo/django-treebeard • django-polymorphic-tree: https://github.com/django-polymorphic/django-polymorphic-tree • django-closuretree: https://github.com/ocadotechnology/django-closuretree
35. Вопросы?