Eine kurze Diskussion über den Entwurf und die Optimierung von MySQL-Baumstrukturtabellen

Vorwort

In vielen Verwaltungs- und Bürosystemen sind überall Baumstrukturen zu sehen. Wenn Sie beispielsweise „Abteilung“ oder „Institution“ verwendet haben, sollten Sie wissen, dass die endgültige Anzeige auf der Seite eine hierarchische Struktur ist. Die folgende Abbildung listet zufällig eine Baumstrukturanzeige einer Abteilung auf.

Überlegungen zum Entwurf

1. Tischstruktur-Design

Für Studenten, die ein wenig Erfahrung in der Entwicklung und im Entwurf von Tabellenstrukturen haben, sollte es einfach sein, eine solche Tabelle zu entwerfen. Sie müssen nur eine PID/ein Feld in der Depart-Tabelle hinzufügen, um die Anforderungen zu erfüllen. Siehe die folgende Tabelle:

CREATE TABLE `abfahren` (
  `depart_id` varchar(32) NOT NULL COMMENT 'Abteilungs-ID',
  `pid` varchar(32) NOT NULL DEFAULT '0' COMMENT 'Übergeordnete Organisations-ID',
  `name` varchar(64) NOT NULL COMMENT 'Abteilungsname',
  `Beschreibung` varchar(512) DEFAULT NULL COMMENT 'Abteilungsbeschreibung',
  `code` varchar(64) DEFAULT NULL COMMENT 'Abteilungscode',
  PRIMÄRSCHLÜSSEL (`depart_id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

2. Geschäftsgestaltung

Die obige Abbildung ist ein allgemeines Baumstrukturdiagramm, das für die meisten Geschäftsszenarien geeignet ist. Wenn beispielsweise die „Abteilung“ nicht unabhängig existiert, umfasst das mit der Abteilung verbundene Geschäft hauptsächlich die folgenden Punkte:

• Ob die Abteilung unabhängig existiert und ob es Unternehmen gibt, die direkt mit der Abteilung verbunden sind. Beispiel: Benutzer können mit der Abteilung verknüpft sein.
• Wird beim Anzeigen des Abteilungseffekts und Laden der Abteilungsbaumliste dynamisch geladen? Oder eine einmalige Rückgabe?
• Geschäfte im Zusammenhang mit dem Hinzufügen, Löschen, Ändern und Abfragen von Abteilungen
• Verknüpfen Sie das Geschäft der Abteilung für ihn, d. h. die Abteilung ist das verknüpfte Geschäftsobjekt

3. Leistungsüberlegungen

• Das vollständige Laden ist nicht ratsam. Es ist der letzte Ausweg. Sobald die Hierarchie zu tief und die Datenmenge zu groß ist, wird die Abfrageleistung zum Albtraum.
• Dynamisches Laden hat einen besseren Effekt. Der Server-Overhead und -Druck sind viel geringer als beim vollständigen Laden. Das Problem des dynamischen Ladens ist jedoch auch offensichtlich. Sobald das Level tief ist, ist die Benutzererfahrung nicht gut.
• Die hierarchische Strukturfunktion erhöht die Codierungskomplexität des Datenimports, d. h. die Excel-Importfunktion

Zum ersten Punkt möchte ich noch ein paar Ergänzungen zum zweiten Punkt machen. Sowohl Volllast als auch dynamische Belastung können umgesetzt werden. Ich habe sie in den Projekten oder Produkten gesehen, die ich erlebt habe. Es hängt wirklich vom Produktdesign und den Kundenanforderungen ab, denn die unterschiedlichen Designs von Volllast und dynamischer Belastung bringen auch entsprechende Ergebnisse.

Der Vorteil des vollständigen Ladens besteht beispielsweise darin, dass die Daten auf einmal an die Seite zurückgegeben werden und die Seite nach dem Rendern zwischengespeichert wird, sodass sie beim späteren erneuten Laden sehr schnell ist. Gleichzeitig sind Suchvorgänge wie die folgende sehr effizient, da keine Interaktion mit der Benutzeroberfläche erforderlich ist.

Es treten jedoch auch Probleme auf. Abteilungsdaten sind nicht statisch und Hinzufügungs-, Lösch- und Änderungsvorgänge sind üblich. Wenn die Seite so konzipiert ist, dass sie die gesamten Daten lädt, bedeutet dies, dass bei der ersten Abfrage, wenn das Datenvolumen sehr groß und die Hierarchie sehr tief ist, die Seite auch die mit der Abteilung verknüpften Benutzerdaten rendern muss, dies den Server stark belastet. Studenten mit ein wenig Erfahrung sollten sich die Rückgabedatenstruktur dieses Servers grob vorstellen können.

Nachfolgend eine vorläufige Umsetzungsidee

Funktion (currentDepart_id) {
  
  1. Suchen Sie die aktuelle Abteilungs-DB...

  2. Suchen Sie die Unterabteilungs-Datenbank der aktuellen Abteilung …
  
  3. Basierend auf der Unterabteilungsliste der aktuellen Abteilung die DB durchlaufen, rekursiv abfragen und verpacken …

}

Aus der obigen Codeimplementierung ist ersichtlich, dass bei zunehmender Datenmenge die Abfrage voraussichtlich zu einem Leistungsengpass wird. Bei der Entwicklung des Projekts des Herausgebers wurden ähnliche Tests durchgeführt. Es gibt 3 Ebenen mit jeweils 1.000 Daten (das Laden von Daten von Benutzern, die Abteilungen zugeordnet sind, wird nicht berechnet). Auf einem 4-Core-16G-Server (mit normaler CPU-Leistung) dauert es durchschnittlich etwa 3 Sekunden, um eine vollständige Datenladung abzuschließen. Für B-Side-Produkte ist dieses Design plus diese Verzögerung für Benutzer akzeptabel (1.000 Abteilungen, diese Datenmenge ist relativ groß).

Wie oben analysiert, liegt der Leistungsengpass bei Volllast in der Datenbank-E/A. Stellen Sie sich vor, dass bei einer Abfrage, beginnend beim obersten Knoten oder einem bestimmten Knoten, die Datenmenge umso größer ist, je tiefer die Ebene ist, desto mehr Abfragen erfolgen und desto größer der E/A-Overhead ist.

Was ist die Lösung? In der Praxis gibt es zwei Erfahrungen, die als Referenz verwendet werden können:

• Entwerfen Sie eine sinnvolle Cache-Speicherstruktur
• Tabellenstruktur verbessern

Zum ersten Punkt: Auch das ist jedem leicht klar, aber wie kann man es sinnvoller gestalten? Am Beispiel der folgenden Abbildung können wir erwägen, Nicht-Blattknoten als Schlüssel und die Sammlungen unter den Blattknoten als Werte zu verwenden und alle Werte in einer Redis-Sammlung zu speichern. Diese Überlegung ergibt sich aus der tatsächlichen Überprüfung der Geschäfts- und Benutzeranforderungen, d. h. die Daten von Abteilungen oder Institutionen, die wirklich von Bedeutung sind, werden auf Blattknoten verteilt.

Auf diese Weise kann die Codierungsimplementierung wie folgt umgewandelt werden:

1. Neue Funktion zur Abteilung hinzufügen add(params){
    
    1. Abfahrt zur DB......
    2. Bestimmen Sie die Ebene des aktuellen Abgangs, ob es sich um einen Blattknoten handelt (ob es im Begriff ist, ein Blattknoten zu werden)

      wenn(Blattknoten){
        3. Suchen Sie die übergeordnete Knoten-ID und fragen Sie den Schlüssel in Redis ab

        4. Nehmen Sie die Cache-Sammlung heraus, die dem übergeordneten Schlüssel entspricht, und fügen Sie die neu hinzugefügte part_id hinzu

      } anders {
        5. Erstellen Sie einen neuen Schlüssel, d. h. eine neue Cache-Leersammlung, und warten Sie darauf, dass nachfolgende Daten hinzugefügt werden (oder nicht erstellt werden).
      }

}


2. Abteilung löschen functiob delete(params){
    
    1. Eigene Lösch-DB des Absenders...

    2. Wenn es unter der aktuellen Abteilung Unterabteilungen gibt, müssen Sie die Unterabteilungen zusammen löschen (zusammen mit Ihrem eigenen Produktgeschäft)?
        DB...

        Holen Sie sich alle Nicht-Blattknotensätze 3. Angenommen, der zweite Schritt ist abgeschlossen. Sie müssen auch den vom aktuellen Abteilungsknoten erstellten Schlüssel verwenden, die im Schlüssel festgelegte Liste herausnehmen und die Redis-Operation zusammen löschen, um im zweiten Schritt alle Nicht-Blattknotensätze abzurufen, sie zu Schlüsseln zusammenzusetzen und die Schlüssel in einer Schleife zu löschen (Speicheroperation, Leistung ist kein Problem, und asynchrone Operationen können ebenfalls durchgeführt werden).

}

Die Kombination aus vollständigem Laden und Redis ist ein wichtiger Schritt, um den Leistungsengpass zu überwinden. Durch die obige Implementierung hat sich die Komplexität der Codierung jedoch tatsächlich erhöht, und die Codierungsanforderungen für Entwickler sind relativ hoch. Nach dieser Implementierung kann jedoch gesagt werden, dass die Abfrageleistung erheblich verbessert wird.

Die zweite Überlegung zur Optimierung der Abfrageleistung ist die Transformation der Tabellenstruktur

Viele Studierende fragen sich, welchen Einfluss die Transformation der Tabellenstruktur auf die Leistung haben kann. Sie werden es vielleicht nicht glauben, aber bei der Simulation eines Daten-Stresstests ohne Verwendung der modifizierten Implementierung werden 5 Abteilungsebenen verwendet, wobei jede Abteilung über 1.000 Daten verfügt (ich meine, das Datenvolumen jeder Abteilung auf jeder Ebene beträgt 1.000, Sie können das Gesamtdatenvolumen berechnen), jede Abteilung ist mit 500 Benutzern verknüpft und die endgültige Leistung dieses Datenvolumens beträgt etwa 5 Minuten.

Es scheint, dass der Abfragedruck wirklich groß ist, wenn die Datenmenge zunimmt. Mit dem geänderten Design und den Testergebnissen werden dieselben Daten schließlich im Durchschnitt in 15 bis 20 Sekunden angezeigt, was einer Verbesserung um mehr als das Zehnfache entspricht. Vielleicht haben viele Studenten es verwendet, aber seine Magie noch nicht wirklich erlebt, bevor ich die Antwort gebe.

Basierend auf der Tabellenstruktur am Anfang dieses Artikels fügen wir ein Pfadfeld hinzu, sodass die transformierte Tabelle wie folgt aussieht:

CREATE TABLE `abfahren` (
  `depart_id` varchar(32) NOT NULL COMMENT 'Abteilungs-ID',
  `pid` varchar(32) NOT NULL DEFAULT '0' COMMENT 'Übergeordnete Organisations-ID',
  `name` varchar(64) NOT NULL COMMENT 'Abteilungsname',
  `Beschreibung` varchar(512) DEFAULT NULL COMMENT 'Abteilungsbeschreibung',
  `code` varchar(64) DEFAULT NULL COMMENT 'Abteilungscode',
  PRIMÄRSCHLÜSSEL (`depart_id`),
  `Pfad` varchar(128) NOT NULL COMMENT 'Abteilungspfad',
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

Das Pfadfeld ist von großer Bedeutung. Normalerweise wird davon ausgegangen, dass jede Ebene, beginnend mit der ersten Ebene, bis zu 10.000 Abteilungen enthält. Die Daten der ersten Ebene sehen dann so aus: 00001, 00002, 00003... und so weiter. Wenn wir für die zweite Ebene eine Abteilung der zweiten Ebene unter der Abteilung 00002 hinzufügen, sehen die Daten so aus: 00002/00001, 00002/00002, 00002/00003... und so weiter.

Was die tiefere Ebene betrifft, bin ich der Meinung, dass jeder die folgenden Strukturen selbst auflisten kann, auch wenn ich keine Beispiele angebe.

Welche Vorteile hat dies?

Wir wissen, dass MySQL reguläre Ausdrucksfunktionen und Ähnliches unterstützt. Stellen Sie sich vor, wir möchten alle hierarchischen Daten ab einer bestimmten Ebene auf einmal abfragen. Was sollen wir tun, wenn kein Pfadfeld vorhanden ist? Offensichtlich geschieht dies durch Rekursion, wie oben erwähnt.

Mit dem Pfadfeld können wir jedoch direkt die reguläre Ausdrucksfunktion von MySQL verwenden. Am Beispiel der obigen Daten können durch die folgenden beiden SQL-Anweisungen alle Teilmengen der Daten der Abteilung der ersten Ebene (Test) gleichzeitig gefunden werden. Auf diese Weise kann die Anzahl der Interaktionen mit der Datenbank erheblich reduziert werden.

Diese Implementierung ist anfällig für Fallstricke, oder der Ort, an dem im tatsächlichen Betrieb eher Probleme auftreten, ist die Generierung von Pfadregeln. Normalerweise muss eine benutzerdefinierte Funktion im Voraus angepasst werden, um Pfade für Benutzer zu generieren. Solange die generierten Pfadfelddaten genau sind, ist diese Implementierung ein großer Durchbruch bei der Optimierung der Abfrageleistung. Diese Methode wird im Entwicklungsprojekt verwendet, an dem der Editor arbeitet.

Funktion generatePath(pid){
    
  1. Ist pid die oberste Ebene? 2. Holen Sie sich die Abteilung der übergeordneten Abteilung

  3. Listen Sie alle Pfadfelder auf der gleichen Ebene wie die Abteilung auf, die unter der übergeordneten Abteilung hinzugefügt werden soll. 4. Nehmen Sie den Maximalwert des Pfads in Schritt 3. 5. Generieren Sie einen neuen Pfad basierend auf dem Maximalwert des Pfads in Schritt 4.

}

Eine weitere Schwierigkeit besteht darin, dass nach dem Entwurf des Pfadfelds beim Importieren von Abteilungsdaten in Excel die Verarbeitung dieses Pfads immer noch ein relativ komplexer Implementierungspunkt ist, über den jeder nachdenken muss.

Die obige Diskussion befasst sich mit der Optimierung der Geschäftsimplementierung und des Codedesigns unter Volllast sowie mit der Optimierung des Tabellenstrukturdesigns. Die Implementierung des dynamischen Ladens ist mit ein wenig Referenz basierend auf den beiden oben genannten Lösungen relativ einfach umzusetzen.

Zusammenfassend folgt hier eine Empfehlung für Best Practices im Geschäftsdesign mit hierarchischer Struktur.

Verwenden Sie in Bezug auf die Tabellenstruktur das Laden von Pfadfelddaten und versuchen Sie, dynamisches Laden zu verwenden. Wenn sich die Abteilung (hierarchisches Geschäft) nicht stark ändert, können Sie die Einführung eines Caches in Betracht ziehen. Informationen zu spezifischen Vorgehensweisen finden Sie im obigen Artikel.

Dies ist das Ende dieses Artikels über das Design und die Optimierung von MySQL-Baumstrukturtabellen. Weitere relevante Inhalte zur Optimierung von MySQL-Baumstrukturtabellen 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!