Ієрархії даних: Керування ієрархічними даними з EF Core та PostgreSQL M SK1Виgląd)
Введение
Ієрархічні дані є всюди в програмному розробці: потокові коментаріМSK1 організаційні діаграми МSK2 файлові системи , категорії продуктів МСК4 і дискусії на форумі М СК5 Вечное питання МСК6 як я зберігаю дерево у реляційній базі даних MСК7 боїть розробників з ранніх днів SQL M СК8 та, чесно кажучи, МС К9 там МсК10 досі немає єдиної відповіді, яка робить всіх щасливими МSК11 Я вперше написав про це питання назад в 2004МSK0 і фундаментальні виклики залишаються такими ж і сьогодні .
Чому ієрархії складні в SQL
Ось фундаментальна проблема: відносні бази даних думають в наборах, а не в деревах. Як пояснює Джо Селко в своїй чудовій книзі Подумки в наборах, SQL працює на цілих tabelaх , не на окремих рядках МSK2
Коли ви пишете SQL query,, база даних працює на набору рядів. Це МSK1 найкраще в таких операциях, як МSK2 знайти всі замовлення через 100" або ♫ " залучити покупців до своїх покупок ♫ МSK5 ♫ ♫ Ми маємо набір операцій, які природньо підходять до того, як працюють столи ♫. ♫ Результат завжди плоский
Але ієрархія по суті рекурсивна. Для того, щоб знайти всі нащадки вузла , вам потрібноM SK2
- Найти близьких дітей
- Для кожного дитини, знайти їхніх дітей
- Повторюйте, доки ви не перетинаєте весь піддерево
Цей рекурсивний траверsal не maps naturally to set operations. You can't express M SK3 give me all descendants at any depthMSC4 in a single , simple SQL statement without either
- Рекурсивні CTE ( включений до SQLM SK1 але обчислювально дорогий
- Багато подорожей до бази даних
- Розумна денормалізація, яка спричиняє передчасне сприйняття відносин
Кожен підхід в цій серії показує різну торгівлю: МСК0 - розрив між складністю написання, МСК1 - складністю читання, МSK2 - накладною витратою на зберігання, . - там не є безкоштовного обіду. МСК5 - ви, МКС6 - завжди торгуєте однією ціною за іншою. Дерево та ієрархії в SQL для умних,, що охоплює всі ці підходи у глибині
До прикладу: Комментари в темах
Щоб зробити порівняння конкретними, ми використаємо потокові коментарі як наш приклад: - те, що використовує цей самий блог . Ссылка для коментарів може виглядати так:
flowchart TD
subgraph Post["Post: How to Deploy Docker Containers"]
C1["Comment 1: Great article!<br/>depth 0"]
C2["Comment 2: Thanks!<br/>depth 1"]
C3["Comment 3: Very helpful indeed<br/>depth 1"]
C4["Comment 4: Agreed!<br/>depth 2"]
C5["Comment 5: What about Kubernetes?<br/>depth 0"]
C6["Comment 6: That's covered in part 2<br/>depth 1"]
end
C1 --> C2
C1 --> C3
C3 --> C4
C5 --> C6
style Post stroke:#10b981,stroke-width:2px
style C1 stroke:#6366f1,stroke-width:2px
style C2 stroke:#8b5cf6,stroke-width:2px
style C3 stroke:#8b5cf6,stroke-width:2px
style C4 stroke:#a855f7,stroke-width:2px
style C5 stroke:#6366f1,stroke-width:2px
style C6 stroke:#8b5cf6,stroke-width:2px
Нам потрібно підтримувати ці операції.
- Отримайте всі коментарі до статті (з утримкою структури ниткиM SK1
- Візьміть усіх предків з відгуку (наслідок хребтаM SK1
- Візьміть усіх потомків komentarzу (головний піддеревоM SK1
- Dodaj новий коментар ( як відповідь на існуючий коментарM SK1
- Утворити піддерево (remove a comment and all repliesM SK1
- Переміщувати піддерево (редагувати коментар МSK1 рідкіснішеM SK2 але іноді потрібно
П 'ять шляхів
Кожен підхід охоплений у своїй статті.
1. Список залежностей МSK1Референція батьківM SK2
Найпростіший і найінтуїтивніший підхід. Кожна рядка зберігає інформацію про свій батьківський вузолM SK1 Це те, що більшість розробників досягають першими, тому що це природньо відображає те, як ми думаємо про ієрархії
Як це працює: Кожен коментар має нульове значення ParentCommentId column. Корейні коментарі мають NULLM SK1 відповіді вказує на їхнє батьківство
Найкраще для: Порожні ієрархії ( менші за МSK1 рівні МSK2 часті рухи піддербів , коли ви хочете, щоб EF Core \ ' \ навігаційні властивості працювали природно
Торгівля Щоб отримати предків чи нащадків, потрібно рекурсивні CTE або багато запитань. Легко написатиM SK1 потенційно повільно читати глибокі дерева.
2. Закритий стіл
Вираховує і зберігає кожен предок в одній таблиці. Замість того, щоб розібратися в стосунках у часі запиту, ми explicitно зберігаємо їх разом з їхніми глибиною.
Як це працює: Відокремлена CommentClosure таблиці запасів (ancestorM SK1id, descendantMSC3id, depthMska5 для кожної париM Ska6 Комментарий Мska7 внизу Комментарий Мska8 через Комментерь мska9 мали б записиMСК10 МСК11 | | МСК12 | MСК13
Найкраще для: Прочитайте-важкі додатки, коли вам потрібно задавати питання на произвольних глибинахM SK2 коли ви рідко переміщуєте підземні дерева .
Торгівля Складні inserts (мусить додати вхідні записи для замкнутостіM SK1 зберігання зростає з глибиною , рухомі підземні дерева потребують перебудови замкнуток.
3. Материалізована дорога
Поміщає ціле походження як обмежену стрічку. Думайте про це як про зберігання повної поштової address, а не просто вуличного ім 'яM SK1
Як це працює: Кожен коментар має Path стовп 'я, як "1/3/7" означає " корінь - МSK2 батьківство - ♫ 3, це ♫ МSK4 Ансестори можна розшифрувати з строку ♫; потомки, знайдені в LIKE запитах ♫
Найкраще для: Покоління хліба, коли ancestors are queried more than descendants, when you want humanM SK2readable paths for debugging .
Торгівля Попити на LIKE можуть бути повільні без відповідних індексів, стрічки маршруту мають обмеження довжиниM SK1 рухомі піддерби потребують оновлення всіх послідовних маршрутів .
4. Нестеровані набірки
Назначає кожен вузол ліворуч та правою лінією від глибини-перший перехідM SK1 Усі спадки мають значення між parent's boundariesM SK1
Як це працює: Кожен коментар Left і Right значення. Нод з LM SK1 RMSC2 містить всі вузли, де 4 < Ліворуч і праворуч МSK5 \7. Descendants є знайденими з простими питаннями про діапазон
Найкраще для: Читайте-важкеM SK1 писати-рідко сценарії, такі як дерева категорійMSC3 Чудово для МSK4 отримати цілий піддерб у порядку表示у
Торгівля Введення потребує оновлення багатьох рядків. ( переміщувати всі значення, щоб зробити кімнату.
5. PostgreSQL ltree
PostgreSQL' нативне поширення для ієрархічних даних . Як матеріалізовані шляхи, але з базою даних
Як це працює: Використовує ltree тип даних з такими шляхами, як "1.3.7". Індекси GiST umożliwiają ефективні питання пращура / пращуря за допомогою таких операторів, як @> і <@.
Найкраще для: PostgreSQL- тільки застосування, в яких має значення продуктивність , коли вам потрібні співпадні питання з шаблонами МSK2 коли ви хочете мати найкращі матеріалізовані шляхи
Торгівля PostgreSQL-only, додає залежність від розширення бази даних Поставник Npgsql підтримує переклади LINQ для Ltree через LTree тип, хоча рекурсивні CTE все ще потребують сирого SQL.
Підсумок порівняння
| Досуг МSK1 Вставляйте МSK2 Субдерево для пошуку | Анцестори для пошуку | ||||||
|---|---|---|---|---|---|---|---|
| Список близьких МSK0 O(1) МSK2 O+(n+M SK4 з CTE M SK5 O=(d=M SK7 із CTE | |||||||
| Закрити таблицю МSK0 O(d) M SK3 O+(1) | O=(1) | О+M SK8s x d=M SK9 ・ | МSK10 O^(n x d^M SK12 ─ | МSK13 Хороша МSK14 | ||||
| Матеріалізований шлях МSK0 О(1) МSK2 OM SK3* МSK0 О(1) МSK2 OM SK3s) ♫ ♫ МSK5 ♫ | |||||||
| Вбудовані набірки МSK0 O(n) M SK3 O+(1) | O=(1) | О+M SK8n | МSK9 ─ | Minimal ─ | Хороша ─ МSK12 | |||
дерево МSK0 О(1) МSK2 OM SK3 ♫ ♫ МSK4 ♫ O (1) ♫ LTree тип) M SK1 |
n = загальна кількість вузлів , d МSK2 глибина МSK3 s M= розмір піддерева *З відповідним індексом
Блок-схема прийняття рішень
flowchart TD
Start([Start]) --> Q1{Shallow tree?<br/>< 5 levels}
Q1 -->|Yes| Q2{Need EF Core<br/>navigation properties?}
Q2 -->|Yes| AL[Adjacency List]
Q2 -->|No| Q3{Performance<br/>critical?}
Q3 -->|No| AL
Q3 -->|Yes| MP[Materialised Path]
Q1 -->|No| Q4{Read-heavy?}
Q4 -->|Yes| Q5{Writes rare?}
Q5 -->|Yes| NS[Nested Sets]
Q5 -->|No| CT[Closure Table]
Q4 -->|No| Q6{PostgreSQL only?}
Q6 -->|Yes| LT[ltree]
Q6 -->|No| Q7{Need breadcrumbs?}
Q7 -->|Yes| MP
Q7 -->|No| CT
style Start stroke:#10b981,stroke-width:2px
style AL stroke:#6366f1,stroke-width:2px
style MP stroke:#8b5cf6,stroke-width:2px
style NS stroke:#ec4899,stroke-width:2px
style CT stroke:#f59e0b,stroke-width:2px
style LT stroke:#14b8a6,stroke-width:2px
Реальний-World Choice: Цей блог
Цей блог використовує Закрити таблицю підход до коментарів МСК0 Причина МSK1
- Комментари читаються набагато частіше, ніж написані
- Нам потрібно ефективніше відображати вбудовані потоки коментарів
- Глубина-погані питання важливі для продуктивності M SK1в нас є обмеження на рівнях 5 глибинна МSK3
- Переміщення коментарів є рідкісним. (розмовники іноді мають це робити.
Подивіться Частина 1.2: Закривальний стіл для повної інформації про впровадження.
Навігація серії
- Частина 1: Обзор (таков artykułМSK1
- Частина 1.1: Допомічник - Прості зразки батьків
- Частина 1.2: Закривальний стіл - Передвимірні стосунки
- Частина 1.3: Матеріалізований шлях - Стрини маршруту
- Частина 1.4: Насаджені набіри - ЛіворучM SK1праворуч кордони
- Частина 1.5: ltree - Нативне розширення PostgreSQL
- Частина 2: Raw SQL and Dapper (coming soon)