Eine kurze Analyse und Aufschlüsselung der Vor- und Nachteile von drei Baumstrukturtabellendesigns in MySQL

Einführung

Bei der Entwicklung stoßen wir häufig auf baumstrukturierte Szenarien. In diesem Artikel werden die Vor- und Nachteile verschiedener Designs am Beispiel der Abteilungstabelle verglichen.

Frage

Bedarfshintergrund : Personalsuche nach Abteilung.
Frage : Wie kann bei Auswahl einer Abteilung der obersten Ebene die Tabelle gestaltet werden, um ebenenübergreifend das gesamte Personal der aktuellen Abteilung und ihrer Unterabteilungen anzuzeigen?

Rekursion? Rekursion kann dieses Problem lösen, wird aber zwangsläufig Leistung verbrauchen

Design 1: Adjazenzliste

Hinweis: (Gemeinsames Design der übergeordneten ID)

Tischdesign

CREATE TABLE `dept_info01` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel automatisch inkrementieren',
  `dept_id` int(10) NOT NULL COMMENT 'Abteilungs-ID',
  `dept_name` varchar(100) NOT NULL COMMENT 'Abteilungsname',
  `dept_parent_id` int(11) NOT NULL COMMENT 'Übergeordnete Abteilungs-ID',
  `create_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
  `update_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Änderungszeit',
  PRIMÄRSCHLÜSSEL (`id`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Dies ist das am häufigsten verwendete Design, mit dem die Baumstruktur des Menüs ohne redundante Daten korrekt ausgedrückt werden kann. Allerdings erfordern ebenenübergreifende Abfragen eine rekursive Verarbeitung.

SQL-Beispiele

1. Abfrage der direkten Teilmenge eines Knotens

Wählen Sie * aus dept_info01, wobei dept_parent_id = 1001 ist.

Vorteil

Einfache Struktur;

Mangel

1. Es ist unmöglich, alle Eltern und alle Kinder eines Knotens ohne Rekursion abzufragen

Entwurf 2: Pfadaufzählung

Basierend auf Design 1 wird ein übergeordnetes Abteilungs-ID-Set-Feld hinzugefügt, um alle übergeordneten Sets zu speichern, wobei mehrere Sets durch feste Trennzeichen wie Kommas getrennt sind.

Tischdesign

CREATE TABLE `dept_info02` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel automatisch inkrementieren',
  `dept_id` int(10) NOT NULL COMMENT 'Abteilungs-ID',
  `dept_name` varchar(100) NOT NULL COMMENT 'Abteilungsname',
  `dept_parent_id` int(11) NOT NULL COMMENT 'Übergeordnete Abteilungs-ID',
  `dept_parent_ids` varchar(255) NOT NULL DEFAULT '' COMMENT 'Übergeordnete Abteilungs-ID festgelegt',
  `create_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
  `update_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Änderungszeit',
  PRIMÄRSCHLÜSSEL (`id`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

SQL-Beispiele

1. Alle Teilmengen abfragen
1). Durch Fuzzy-Abfrage

WÄHLEN
 *
AUS
	dept_info02
WO
	dept_parent_ids wie „%1001%“

2). Es wird empfohlen, die Funktion FIND_IN_SET zu verwenden

WÄHLEN
	* 
AUS
	dept_info02 
WO
	FIND_IN_SET( '1001', Abteilungs-Eltern-IDs )

Vorteil

• Praktisch zum Abfragen aller Teilmengen;
• Daher kann die aktuelle Knotenebene durch Vergleichen der Länge der Zeichenfolge dept_parent_ids ermittelt werden.

Mangel

• Beim Hinzufügen eines neuen Knotens muss der Feldwert dept_parent_ids ordnungsgemäß verarbeitet werden.
• Die Länge des Felds dept_parent_ids ist schwer zu bestimmen. Unabhängig davon, wie groß die Länge ist, kann sie nicht unendlich erweitert werden.
• Die Punktbewegung ist komplex und erfordert die gleichzeitige Änderung des Feldwerts dept_parent_ids in allen Teilmengen.

Design 3: Verschlusstabelle

• Closure-Tabellen sind eine einfache und elegante Lösung für das Problem der hierarchischen Speicherung und eine Möglichkeit, Speicherplatz gegen Zeit einzutauschen.
• Es muss eine zusätzliche TreePaths-Tabelle erstellt werden, die die Beziehung zwischen allen Knoten im Baum aufzeichnet.
• Enthält zwei Spalten, die Vorgängerspalte und die Nachkommenspalte, auch wenn zwischen den beiden Knoten keine direkte Eltern-Kind-Beziehung besteht; und fügt außerdem eine Zeile hinzu, die auf den Knoten selbst verweist;

Tischdesign

Haupttabelle

CREATE TABLE `dept_info03` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel automatisch inkrementieren',
  `dept_id` int(10) NOT NULL COMMENT 'Abteilungs-ID',
  `dept_name` varchar(100) NOT NULL COMMENT 'Abteilungsname',
  `create_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
  `update_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Änderungszeit',
  PRIMÄRSCHLÜSSEL (`id`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Tabelle der Vorfahren-Nachkommen-Beziehungen

CREATE TABLE `dept_tree_path_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel automatisch inkrementieren',
  `Vorfahr` int(10) NICHT NULL KOMMENTAR 'Vorfahr-ID',
  `Nachkomme` int(10) NICHT NULL KOMMENTAR 'Nachkomme-ID',
  `Tiefe` tinyint(4) NICHT NULL STANDARD '0' KOMMENTAR 'Leveltiefe',
  PRIMÄRSCHLÜSSEL (`id`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Hinweis: Tiefe ist das Ebenentiefenfeld. Die Selbstreferenz ist 1, der direkte untergeordnete Knoten ist 2, die nächste Ebene ist 3 und so weiter. Die Ebene ist dasselbe wie die Ebene.

SQL-Beispiele

Einfügen eines neuen Knotens

INSERT INTO dept_tree_path_info (Vorfahr, Nachfahre, Tiefe)
Wählen Sie t.ancestor, 3001, t.depth+1 FROM dept_tree_path_info AS t 
WO t.Nachkomme = 2001
UNION ALLE
WÄHLEN SIE 3001,3001,1

Alle Vorfahren abfragen

WÄHLEN
	C.*
AUS
	dept_info03 AS c
INNER JOIN dept_tree_path_info t ON c.dept_id = t.ancestor
WO
	t.Nachkomme = 3001

Alle Nachkommen abfragen

WÄHLEN
	C.*
AUS
	dept_info03 AS c
INNER JOIN dept_tree_path_info t ON c.dept_id = t.descendant
WO
t.Vorfahr = 1001

Alle Teilbäume löschen

LÖSCHEN 
AUS
	Abteilungsbaumpfadinfo 
WO
	Nachkomme IN 
	( 
		WÄHLEN
			eine.Abteilungs-ID 
		AUS
		( SELECT Nachkomme dept_id FROM dept_tree_path_info WHERE Vorfahr = 1001 ) ein
	)

Blattknoten löschen

LÖSCHEN 
AUS
	Abteilungsbaumpfadinfo 
WO
	Nachkomme = 2001

Mobiler Knoten

• Löschen Sie alle Teilbäume (trennen Sie sich zuerst vom ursprünglichen Vorgänger).
• Neue Beziehungen aufbauen

Vorteil

• Nicht-rekursive Abfragen reduzieren redundante Berechnungszeit;
• Bequeme nicht-rekursive Abfrage aller übergeordneten Sätze eines beliebigen Knotens;
• Es ist praktisch, alle Teilmengen eines beliebigen Knotens abzufragen.
• Es können unbegrenzt viele Level erreicht werden;
• Unterstützt mobile Knoten;
  

Mangel

• Wenn zu viele Ebenen vorhanden sind, führt das Verschieben von Baumknoten zu mehreren Vorgängen in der Beziehungstabelle.
• Zum Speichern der entsprechenden Beziehung ist eine separate Tabelle erforderlich. Beim Hinzufügen und Bearbeiten von Knoten ist der Vorgang relativ kompliziert.

Verwendung in Kombination

Die Adjazenzlistenmethode kann mit der Closure-Table-Methode kombiniert werden. Tatsächlich wird die übergeordnete ID redundant zur Haupttabelle hinzugefügt. In einigen Unternehmen, die nur direkte Beziehungen abfragen müssen, kann die Haupttabelle direkt abgefragt werden, ohne dass zwei Tabellen verknüpft werden müssen. Die Vorfahren-Nachkommen-Beziehungstabelle ist insbesondere dann wichtig, wenn ebenenübergreifende Abfragen erforderlich sind.

Tischdesign

Haupttabelle

CREATE TABLE `dept_info04` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel automatisch inkrementieren',
  `dept_id` int(10) NOT NULL COMMENT 'Abteilungs-ID',
  `dept_name` varchar(100) NOT NULL COMMENT 'Abteilungsname',
  `dept_parent_id` int(11) NOT NULL COMMENT 'Übergeordnete Abteilungs-ID',
  `create_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
  `update_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Änderungszeit',
  PRIMÄRSCHLÜSSEL (`id`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Tabelle der Vorfahren-Nachkommen-Beziehungen

CREATE TABLE `dept_tree_path_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel automatisch inkrementieren',
  `Vorfahr` int(10) NICHT NULL KOMMENTAR 'Vorfahr-ID',
  `Nachkomme` int(10) NICHT NULL KOMMENTAR 'Nachkomme-ID',
  `Tiefe` tinyint(4) NICHT NULL STANDARD '0' KOMMENTAR 'Leveltiefe',
  PRIMÄRSCHLÜSSEL (`id`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Zusammenfassen

Tatsächlich habe ich in meiner früheren Arbeit unterschiedliche Arten von Designs gesehen, darunter Adjazenzlisten, Pfadaufzählungen und Adjazenzlisten und Pfadaufzählungen zusammen. Jedes Design hat seine eigenen Vor- und Nachteile und das Design, für das Sie sich entscheiden, hängt davon ab, welche Vorgänge in Ihrer Anwendung die meiste Leistungsoptimierung benötigen.

Zusammenfassend

• Sie müssen lediglich die Kind-Eltern-Mengenbeziehung herstellen und die Adjazenzlistenmethode verwenden.
• Für Aufwärts- und Abwärtssuchen wird die Verwendung der Abschlusstabellenmethode empfohlen.

Damit ist dieser Artikel über die Analyse und den Austausch der Vor- und Nachteile von drei Designs von baumstrukturierten Tabellen in MySQL abgeschlossen. Weitere relevante Inhalte zu baumstrukturierten MySQL-Tabellen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!