Drei Strategien zum Umschreiben von MySQL-Abfrageanweisungen

Beim Optimieren problematischer Abfragen müssen wir die Art und Weise ändern, wie wir Abfrageergebnisse erhalten. Dies bedeutet jedoch nicht, dass wir von MySQL denselben Ergebnissatz erhalten. Manchmal können wir eine Abfrage in ein Formular umwandeln, das dieselben Ergebnisse liefert, jedoch eine bessere Leistung aufweist. Wir müssen jedoch auch in Betracht ziehen, Abfragen umzuschreiben, um andere Ergebnisse zu erhalten, da dies die Entwicklungseffizienz verbessern kann. Sie können auch den Anwendungscode ändern, um denselben Effekt zu erzielen. Dieser Artikel enthält einige Tipps zum Umschreiben von Abfragen.

Komplexe Abfrage und schrittweise Abfrage

Eine wichtige Frage beim Abfrageentwurf ist, ob es besser ist, eine komplexe Abfrage in mehrere einfachere Abfragen zu zerlegen. Beim traditionellen Datenbankdesign liegt der Schwerpunkt darauf, eine große Menge an Arbeit mit so wenig Abfragen wie möglich zu lösen. Früher wäre das besser gewesen. Dies liegt an den höheren Kosten der Netzwerkkommunikation in der Vergangenheit und der Belastung des Abfrageparsers und -optimierers.

Für MySQL gilt dieser Rat allerdings nicht im Wesentlichen, da MySQL den Aufbau und die Trennung von Verbindungen sehr effizient handhabt und schnell auf einfache Abfragen reagiert. Auch die heutigen Netzwerkgeschwindigkeiten haben sich im Vergleich zu früher deutlich erhöht. Abhängig von der Serverversion kann MySQL auf einem normalen Computer mehr als 100.000 einfache Abfragen pro Sekunde ausführen und in einem Gigabit-Netzwerk 2.000 Abfragekommunikationen pro Sekunde durchführen. Daher ist die Verteilung von Abfragen nicht mehr so ​​schlimm wie früher.

Im Vergleich zur Anzahl der pro Sekunde durchlaufenen Datenzeilen ist die Verbindungsreaktion immer noch relativ langsam. Bei Speicherdaten beträgt diese Zeit mehrere Millisekunden. Natürlich ist es immer noch eine gute Wahl, so viele Abfragen wie möglich zu verwenden. Manchmal können wir die Leistung jedoch verbessern, indem wir eine komplexe Abfrage in mehrere einfachere Abfragen aufteilen. Als nächstes zeigen wir einige Beispiele.

Die Verwendung zu vieler Abfragen ist ein häufiger Fehler beim Programmieren. Einige Anwendungen führen beispielsweise 10 separate Abfragen aus, um 10 Datenzeilen abzurufen (wobei sie eine Schleife verwenden, um sie einzeln abzurufen). Dies könnte jedoch auch mit einer einzigen Abfrage erreicht werden, die 10 Datenzeilen abruft. Daher wird hier nicht empfohlen, Abfragen jedes Mal aufzuteilen, es hängt jedoch von der tatsächlichen Situation ab.

Geteilte Abfrageanweisung

Ein anderer Ansatz besteht darin, die Abfrage aufzuteilen und neu zusammenzusetzen. Durch Aufteilen großer Datenabfragen in kleinere Abfragen wird die Anzahl der jeweils betroffenen Zeilen verringert.

Ein klassisches Beispiel ist die Bereinigung alter Daten. Bei der regelmäßigen Datenbereinigung müssen große Datenmengen entfernt werden, wodurch große Mengen von Datenzeilen für längere Zeit gesperrt werden können. Dieser Vorgang generiert auch Transaktionsprotokolle, verbraucht viele Ressourcen und kann kleine Abfragen blockieren, die nicht unterbrochen werden sollten. Durch das Aufteilen der DELETE-Anweisungen in mittelgroße Abfragen können Sie die Leistung erheblich verbessern und die zusätzliche Latenz reduzieren, die durch wiederholte Abfragen entsteht, wenn die Abfragen wiederholt werden. Beispielsweise die folgende Delete-Anweisung:

LÖSCHEN AUS Nachrichten, bei denen erstellt wurde <DATE_SUB(NOW(), INTERVAL 3 MONTH);

Der Pseudocode der Anwendung lautet wie folgt:

Betroffene Zeilen = 0
Tun {
  betroffene Zeilen = Abfrage ausführen(
  "LÖSCHEN VON Nachrichten, bei denen erstellt wurde < DATE_SUB(NOW(), INTERVAL 3 MONTH)
  GRENZE 10000")
  } während betroffene Zeilen > 0

Das gleichzeitige Löschen von 10.000 Zeilen ist eine ausreichend große Aufgabe, um jede Abfrage effizient zu gestalten. Eine ausreichend kurze Aufgabe reduziert die Auswirkungen auf den Server (transaktionale Speicher-Engines profitieren davon). Es ist außerdem eine gute Idee, in die DELETE-Anweisungen eine Ruhezeit einzufügen, um die Last zeitlich zu verteilen und die Dauer der aufrechterhaltenen Sperren zu verkürzen.

Demontage der gemeinsamen Abfrage

Viele Hochleistungsanwendungen zerlegen gemeinsame Abfragen. Sie können die Union-Abfrage in mehrere Einzeltabellenabfragen aufteilen und die Ergebnisse dann in der Anwendung kombinieren. Zum Beispiel:

SELECT * FROM-Tag
	JOIN tag_post ON tag_post.tag_id=tag.id
  Beitrag beitreten ON tag_post.post_id=post.id
WO tag.tag='mysql';

Diese Union-Abfrage kann in die folgenden Teile zerlegt werden.

Wählen Sie * aus dem Tag, wobei tag='mysql' ist;
Wählen Sie * aus Tag-Post, wobei tag_id=1234 ist.
Wählen Sie * aus dem Beitrag, wobei post.id in (123, 456, 567, 9098, 8904) ist.

Hinweis: tag_id = 1234 und post.id IN (123, 456, 567, 9098, 8904) sind die Werte, die basierend auf den Ergebnissen der vorherigen Abfrage erhalten wurden. Warum das tun? Dies scheint auf den ersten Blick unnötig, es erhöht lediglich die Anzahl der Abfragen. Allerdings kann diese Rekonstruktion der Abfrage folgende Vorteile bringen:

• Der Caching-Mechanismus wird effektiver sein. Viele Anwendungen verwenden ORM, um Datentabellen direkt zuzuordnen. Wenn in diesem Beispiel das Objekt mit dem Tag „mysql“ bereits zwischengespeichert ist, wird die erste Abfrage übersprungen. Wenn sich Beiträge mit der ID 123, 567 oder 9908 im Cache befinden, können Sie diese aus der IN-Liste entfernen. Der Abfrage-Cache profitiert entsprechend von dieser Strategie. Wenn sich nur eine der Tabellen häufig ändert, kann das Aufteilen der Join-Abfrage die Anzahl der Cache-Invalidierungen reduzieren.
• Durch die separate Ausführung dieser Abfragen kann manchmal die Wahrscheinlichkeit von Tabellensperren verringert werden.
• Auf diese Weise ist es einfach, die Datenbank zu skalieren und Tabellen auf andere Maschinen zu verschieben.
• Die Abfragen selbst können optimiert werden. In diesem Beispiel kann die Verwendung einer IN-Abfrage anstelle einer Union-Abfrage MySQL ermöglichen, die Zeilen-IDs zu sortieren und die Zeilen optimaler abzurufen.
• Kann redundante Zeilenzugriffe reduzieren. Bei dieser Vorgehensweise wird die Datenzeile nur einmal abgefragt, während bei einer gemeinsamen Abfrage die Möglichkeit besteht, dieselben Daten mehrfach abzurufen. Aus diesem Grund kann diese Zerlegung auch die allgemeine Netzwerklast und den Speicherverbrauch reduzieren.
• Um dies zu erweitern, können Sie auch die verschachtelten Schleifen von MySQL-Joinabfragen durch manuelles Ausführen von Hash-Joinabfragen ersetzen, was möglicherweise auch effizienter ist.
  

Schließlich können wir erkennen, dass durch die Aufteilung der Union-Abfrage der Cache besser wiederverwendbar ist, die auf mehreren Servern verteilte Datenlösung einfacher wird und IN-Abfragen in großen Datentabellen verwendet werden können, um Union-Abfragen oder mehrere wiederholte Abfragen für dieselbe Tabelle zu ersetzen.

Oben sind die Details der drei Strategien für MySQL zum Umschreiben von Abfrageanweisungen aufgeführt. Weitere Informationen zum Umschreiben von Abfrageanweisungen in MySQL finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!