Introduction

Les données hiérarchiques sont partout dans le développement de logiciels: les commentaires liés aux lignes de conduiteM SK1 les diagrammes organisationnels , les systèmes de fichiers a écrit sur ce sujet pour la première fois en 2004, et les défis fondamentaux demeurent les mêmes aujourd’hui

Pourquoi les hiérarchies sont difficiles dans SQL

C’est là le problème fondamental. les bases de données relationnelles pensent dans des ensembles, non des arbres. Comme Joe Celko l’explique dans son excellent livre Penser en séries, SQL fonctionne sur des tableaux entiers, non sur des lignes individuelles .

Lorsque vous écrivez une demande SQL, le moteur de base fonctionne sur les ensembles de lignes. C'est une opération brilliante de ' qui permet d'effectuer toutes les commandes sur 100" ou ", ainsi que d'associer les clients à leurs achats, " et -; il s'agit là d'opérations définies qui correspondent naturellement au fonctionnement des tables.

Mais une hiérarchie est intrinsèquement Recursive. Pour trouver tous les descendants d'un node, vous devez

1. Trouver les enfants immédiats
2. Pour chaque enfant, trouver de leur enfants
3. Répétez jusqu’à ce que vous ' avez traversé l’ensemble de la sous-arbre

Cette traversation récursive ne permet pas de mapper naturellement les opérations.

• CTEs récursifs (ajoute à SQL:1999, mais coûte cher sur le plan computationnelM SK2
• Plusieurs voyages tournées vers la base de données
• Dénormalisation intelligente qui présuppose les relations

Chaque approche de la présente série représente un échange différent. Trees and Hierarchies in SQL for Smarties, qui couvre toutes ces approches en profondeur

Exemple: Commentaires threadés

Pour faire les comparaisons concrètes, nous' utiliserons des commentaires threaded comme notre exemple opérationnel M SK2 quelque chose que ce même blog utilise . Un thread de commentaire pourrait ressembler à ce

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

Nous devons soutenir ces opérations.

• Obtenez tous les commentaires pour un post (avec structure de threading préservéeM SK1
• Obtenir tous les ancêtres d’un commentaire (traîneau de l’écorce-breadcrumbM SK1
• Obtenir tous les descendants d'un commentaire (arbre de sous-tree entière)
• Ajouter un nouveau commentaire (en réponse à un commentaire existant)
• Supprimer un sous-arbre (sortir un commentaire et toutes les réponsesM SK1
• Déplacer un sous-arbre (réparer un commentaire - plus raresM SK2 mais parfois nécessaires)

Les cinq approches

Chaque approche est décrite en détail dans son article.

1. Liste de voisinage Référence parentaleM SK2

Lire l'article complet

L’approche la plus simple et la plus intuitive. Chacune de ces lignes conserve une référence à son node parent.

Comment il fonctionne: Chaque commentaire a un nullable ParentCommentId column. Les commentaires Root ont NULLM SK1 les réponses indiquent leur parent.

Le meilleur pour: Hiérarchies étroites ( moins élevées que 5-6 niveaux), déplacements fréquents d'un sous-arbreM SK3 lorsque vous voulez que les propriétés de navigation du Core EFMSC4 fonctionnent naturellement

Commerce-offs Pour obtenir des ancêtres ou des descendants, il faut faire appel à des CTE récursives ou à plusieurs demandes.

2. Tableau de fermeture

Lire l'article complet

Précompute et stocke tous les ancêtres-relations descendantes dans une table séparéeM SK1 Au lieu de déterminer les relations au moment de la demande, nous les stockons explicitement avec leur profondeur .

Comment il fonctionne: Un système distinct CommentClosure table stores (ancesteur_idM SK2 descendantMSC3id, profondeurMNK5 pour chaque coupleMRK6 Commentaire 4 sous Commentaires 1 via Commentaires

Le meilleur pour: Lire-applications lourdes, lorsque vous avez besoin de faire une recherche à des profondeurs arbitraires

Commerce-offs Insérations plus complexes (il faut ajouter des entrées de fermetureM SK1 le stockage augmente avec la profondeur , les sous-arbres qui se déplacent nécessitent des fermetures de reconstruction.

3. Voie materialisée

Lire l'article complet

Entretenir l’ancienneté complète comme une chaîne délimitée. Pensez à ce qu’il s’agisse d’entreposer l’adresse postale complète plutôt que simplement le nom de la rue.

Comment il fonctionne: Chaque commentaire a un Path column comme "1/3/7" signifiant "root est |1, parent est \3, il s'agit de ♫7". Les ancêtres peuvent être analysés à partir de la chaîneM SK5 descendants trouvés avec les demandes LIKE.

Le meilleur pour: Génération de crustacés, lorsque les ancêtres sont recherchés plus que leurs descendants, lorsque vous voulez des humainsM SK2 chemins lisibles pour le débugging .

Commerce-offs Les demandes LIKE peuvent être lentes sans index appropriés, les chaînes de chemin ont des limites de longueurM SK1 les sous-arbres en mouvement nécessitent la mise à jour de tous les chemins descendants.

4. Ensembles nichés

Lire l'article complet

Attribue à chaque node des numéros de limite gauche et droite d'une profondeur-première traverséeM SK1 Tous les descendants ont des valeurs entre les limites du parent' des limites de .

Comment il fonctionne: Chaque commentaire a Left et Right valeurs. Un node avec LM SK1 RMSC2 contient tous les nodes où 4 < gauche et droite < ≥7. Des descendants sont trouvés avec des demandes de ranges simples

Le meilleur pour: Lire- lourde, écrireM SK2 rarement des scénarios comme les arbres de catégorie . Excellent pour MSC4 obtenir l'ensemble du sous-arbre en ordre d'affichage

Commerce-offs Les insertions nécessitent la mise à jour de nombreuses lignes (changer tous les valeurs pour faire room), le déplacement des sous-arbres est complexe . Ne convient pas aux écritures fréquentesM SK3

5. L'arbre PostgreSQL

Lire l'article complet

PostgreSQL's extension native pour les données hiérarchiques . Comme des chemins materialisés mais avec base de données-optimisation du niveau et combinaison puissante d'un modèle à l'autre

Comment il fonctionne: Utilise le ltree le type de données avec des chemins comme "1.3.7". les indices GiST permettent d'effectuer des recherches efficientes sur l'ancesteur /descendant en utilisant des opérateurs comme @> et <@.

Le meilleur pour: PostgreSQL-seulement les déploiements où la performance est importante, lorsque vous avez besoin de réponses à des questions correspondantes à un modèleM SK2 quand vous voulez le meilleur des chemins materialisés .

Commerce-offs PostgreSQL-seulement,ajoute la dépendance de l'extension des bases de données . Note Le fournisseur de Npgsql appuie les traductions LINQ pour ltree par le biais du LTree type, bien que les CTE récursives aient encore besoin d’une SQL brute

Résumé de la comparaison

n = nodes totauxM SK1 d = profondeur, s = taille de sous-arbre *Avec un index approprié

Diagramme de décision

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

Real-World Choice: Ce blog

Ce blog utilise un Tableau de clôture approche pour les commentaires. La justificationM SK1

1. Les commentaires sont lus beaucoup plus souvent que les écrits
2. Nous devons afficher efficacement les threads d'observation enchâssés
3. Depth-Les demandes limitées sont importantes pour le rendement M SK1la limite est atteinte à des niveaux 5de profondeur
4. Les commentaires sont rares (les modérateurs ont parfois besoin de le faireM SK1

Voir Partie 1.2: Tableau de fermeture pour plus de détails sur la mise en œuvre intégrale.

Navigation des séries

• Partie 1: Aperçu (le présent articleM SK1
• Partie 1.1: Liste de voisinage - Références parentales simples
• Partie 1.2: Tableau de fermeture - Relations précomputées
• Partie 1.3: Voie matérialisée - Les chaînes de chemin
• Partie 1.4: Ensembles en positionnement -à gaucheM SK1à droite limites
• Partie 1.5: ltree - Extension native PostgreSQL
• Partie 2: Raw SQL and Dapper (coming soon)