Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

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

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

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

Moscow Python Meetup

December 22, 2016
Tweet

More Decks by Moscow Python Meetup

Other Decks in Programming

Transcript

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  8. 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 full-size slide

  9. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

  12. 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 full-size slide

  13. Основные операции
    Вставка нового узла:
    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 full-size slide

  14. Основные операции
    Выборка всех дочерних элементов:
    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 full-size slide

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

    View full-size slide

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

    View full-size slide

  17. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  22. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  26. Основные операции
    Вставка нового узла:
    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 full-size slide

  27. Основные операции
    Удаление вложенного дерева:
    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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  32. Полезные ссылки
    ● 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 full-size slide

  33. Вопросы?

    View full-size slide