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

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

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

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

Transcript

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

   View Slide

2. View Slide

3. 40 50+ 700 1700+
   млн человек
   суммарная аудитория группы
   количество изданий,
   сервисов и проектов
   разработчиков человек в хорошей
   компании
   

   View Slide

4. View Slide

5. Языки и технологии
   

   View Slide

6. Контакты
   В группе компаний Rambler&Co всегда есть
   открытые вакансии для тех, кто хочет
   профессионально расти и развиваться,
   занимаясь тем, что по-настоящему нравится
   hr@rambler-co.ru
   www.rambler-co.ru/jobs
   

   View Slide

7. Применение
   ● Каталог интернет-магазина
   ● Вложенные страницы сайта
   ● Комментарии
   ● Данные о предприятии
   ● ...
   

   View Slide

8. Основные операции
   ● Выбрать всех детей элемента
   ● Выбрать всех потомков элемента
   ● Выбрать цепочку предков элемента
   ● Переместить элемент (и его потомков) из одной
   группы в другую
   ● Удалить элемент из таблицы (со всеми потомками)
   

   View Slide

9. Типы иерархических структур
   ● Adjacency List
   ● Nested Set
   ● Materialized Path
   ● Closure Table
   

   View Slide

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
    ... ... ...
    

    View Slide

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};
    Основные операции
    

    View Slide

12. Adjacency List
    Преимущества:
    ● Возможность хранения больших
    деревьев
    ● Легко вносить изменения
    ● Высокая скорость доступа к
    наследникам
    

    View Slide

13. Adjacency List
    Недостатки :
    ● Выборка веток дерева на всю глубину
    ● Выборка потомков или предков
    элемента
    

    View Slide

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
    ... ... ... ...
    

    View Slide

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;
    

    View Slide

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;
    

    View Slide

17. Nested Set
    Преимущества:
    ● Хорошо оптимизирован для чтения
    произвольных частей дерева
    

    View Slide

18. Nested Set
    Недостатки:
    ● Сложности при изменении структуры
    дерева
    

    View Slide

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
    

    View Slide

20. Основные операции
    Поиск предков:
    SELECT t1.name
    FROM comment t1,
    comment t2
    WHERE t2.path LIKE CONCAT( t1.path, '.%')
    AND t2.id = 3;
    

    View Slide

21. Основные операции
    Выборка ветки:
    SELECT comment, strfind(path, '.') AS level
    FROM comment
    WHERE path LIKE '1.1%';
    

    View Slide

22. Materialized Path
    Преимущества:
    ● Удобство чтения и изменения дерева
    ● Подходит для больших объемов данных
    

    View Slide

23. Materialized Path
    Недостатки:
    ● Вставка узла в середину существующей
    структуры
    ● Тяжелый перенос одной ветки в другую
    

    View Slide

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
    

    View Slide

25. Closure Table
    Comment 1
    Comment 2 Comment 3
    Comment Comment 5
    

    View Slide

26. Основные операции
    Выборка всех потомков:
    SELECT *
    FROM comment AS c
    JOIN tree_paths ON (c.id = tree_paths.descendant_id)
    WHERE tree_paths.ancestor_id = 2;
    

    View Slide

27. Основные операции
    Выборка всех предков:
    SELECT *
    FROM comment AS c
    JOIN tree_paths ON (c.id = tree_paths.ancestor_id)
    WHERE tree_paths.descendant_id = 2;
    

    View Slide

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;
    

    View Slide

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
    );
    

    View Slide

30. Основные операции
    DELETE FROM tree_paths
    WHERE descendant IN (
    SELECT descendant FROM (
    SELECT descendant FROM tree_paths
    WHERE ancestor = 7
    ) AS tmptable
    )
    

    View Slide

31. Closure Table
    Преимущества:
    ● Простота чтения произвольных частей
    дерева
    

    View Slide

32. Closure Table
    Недостатки:
    ● Добавление новых элементов
    ● Удаление элементов
    

    View Slide

33. ● django-treebeard
    ● django-mptt
    ● django-polymorphic-tree
    ● django-closuretree
    

    View Slide

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
    

    View Slide

35. Вопросы?
    

    View Slide