Tipps zur Optimierung von MySQL SQL-Anweisungen

Wenn wir mit einer SQL-Anweisung konfrontiert werden, die nicht ausreichend optimiert ist oder eine extrem schlechte Leistung aufweist, möchten wir die SQL-Anweisung normalerweise so umgestalten, dass der Abfrageergebnissatz derselbe wie der ursprüngliche bleibt, und hoffen, dass die SQL-Leistung verbessert werden kann. Beim Refactoring von SQL stehen im Allgemeinen bestimmte Methoden und Techniken als Referenz zur Verfügung. Dieser Artikel stellt vor, wie Sie SQL mithilfe dieser Techniken und Methoden refactoren.

1. SQL zerlegen

Manchmal denken wir bei einem komplexen SQL zuerst darüber nach, ob wir das komplexe SQL in mehrere einfache SQLs zerlegen müssen, um dieselben Geschäftsverarbeitungsergebnisse zu erzielen.

In der Vergangenheit wurde immer betont, dass die Datenbankebene so viel Arbeit wie möglich erledigen muss. Deshalb ist es nicht schwer zu verstehen, warum wir in einigen alten Produkten und Projekten oft viele superkomplexe und superlange SQL-Anweisungen sehen. Früher wurde angenommen, dass die Logik dazu mehrere Interaktionen erfordert, was in Bezug auf Netzwerkbandbreite, Netzwerkkommunikation zwischen Programmen und Datenbanken usw. sehr kostspielig war. Sowohl in Bezug auf Bandbreite als auch Latenz ist die Netzwerkgeschwindigkeit jetzt viel schneller als zuvor und es gibt keine größeren Probleme mit mehreren Interaktionen. Sogar auf einem Allzweckserver können mehr als 100.000 Abfragen pro Sekunde ausgeführt werden, sodass die Ausführung mehrerer kleiner Abfragen mittlerweile kein großes Problem mehr darstellt.

Durch die Zerlegung komplexer SQL-Anweisungen kann die Leistung bei der Verarbeitung extrem komplexer SQL-Anweisungen erheblich verbessert werden. Wenn Sie mit sehr komplexen SQL-Anweisungen konfrontiert sind und Leistungsprobleme auftreten, empfiehlt es sich daher, diese zur Optimierung in kleinere Abfragen aufzuteilen .

Wenn beim Entwurf einer Anwendung jedoch eine Abfrage ausreicht und keine Leistungsprobleme verursacht, kann sie mit einem etwas komplexeren SQL abgeschlossen werden. Es ist nicht ratsam, sie starr in mehrere kleine Abfragen aufzuteilen.

In vielen heutigen Hochleistungsanwendungssystemen wird dringend empfohlen, Einzeltabellenoperationen zu verwenden und die Einzeltabellenabfrageergebnisse dann in der Anwendung zu verknüpfen, um die Abfrageanforderungen komplexer Unternehmen zu erfüllen. Warum separate SQL-Anweisungen schreiben, wenn eine die Aufgabe erledigen kann? Und warum SQL-Abfragen in der Anwendung mehrfach ausführen und dann die Ergebnismengen verknüpfen? Warum müssen wir das tun?

Dies erscheint auf den ersten Blick kompliziert und bringt keinen Nutzen. Statt einer einzigen Abfrage werden so mehrere Abfragen durchgeführt. Tatsächlich hat diese Zerlegung folgende Vorteile:

• Machen Sie das Caching effizienter. In einer Anwendung können Sie das Ergebnisobjekt, das einem Einzeltabellenabfrageergebnis entspricht, problemlos zwischenspeichern, sodass Sie später jederzeit direkt Daten aus dem Ergebnisobjekt abrufen können.
• Nach dem Aufteilen der Abfrage kann die Ausführung einer einzelnen Abfrage die Konkurrenz um Tabellensperren reduzieren.
• Durch die Erstellung von Verknüpfungen auf der Anwendungsebene lässt sich die Datenbank leichter aufteilen und eine hohe Leistung und Skalierbarkeit erreichen.
• Die Effizienz von Einzeltabellenabfragen ist höher als die von komplexen Mehrtabellenabfragen.
• Reduzieren Sie die Abfrage redundanter Datensätze. Durch die Assoziation auf Anwendungsebene muss die Anwendung einen bestimmten Datensatz nur einmal abfragen, während für eine assoziierte Abfrage in der Datenbank unter Umständen ein wiederholter Zugriff auf einige Datensätze erforderlich ist. Aus dieser Sicht kann eine solche Rekonstruktion auch den Netzwerk- und Speicherverbrauch reduzieren.
  

2. Abfragesegmentierung

Manchmal müssen wir bei großen Abfragen, also Abfragen mit großen Ergebnismengen, das Prinzip „Teile und herrsche“ anwenden und die großen Abfragen in kleine Abfragen aufteilen. Jede Abfrage hat genau dieselbe Funktion, führt jedoch nur einen kleinen Teil aus und gibt jedes Mal nur einen kleinen Teil der Abfrageergebnisse zurück. Einfach ausgedrückt geht es darum, den Filterbereich der Where-Bedingung aufzuteilen und jedes Mal nur einen Teil der Daten abzufragen, was einer Paging-Abfrage ähnelt.

Dadurch entsteht sowohl für die SQL-Abfrage selbst als auch für die Dienste auf höherer Ebene nur ein sehr geringer Mehraufwand. Der typischste Fall ist die Paging-Abfrage, die von verschiedenen Frameworks wie MyBatis usw. gut unterstützt wird. Durch ein wenig Aufmerksamkeit bei der tatsächlichen Verwendung kann dies vermieden werden.

3. Ausführungsplan

Durch die Verwendung des Schlüsselworts EXPLAIN im Ausführungsplan erfahren wir, wie MySQL SQL-Anweisungen ausführt, was uns bei der Analyse der Leistungsengpässe unserer Abfrageanweisungen oder Tabellenstrukturen helfen kann. Die Abfrageergebnisse von EXPLAIN verraten uns auch, wie der Primärschlüssel des Index verwendet wird, wie die Datentabelle durchsucht oder sortiert wird usw.

Das Syntaxformat ist:

EXPLAIN SELECT-Anweisung;

Die Ergebnisse des Ausführungsplans helfen uns bei der weiteren Rekonstruktion von SQL-Anweisungen, z. B. beim Hinzufügen von Indizes, Anpassen der Indexreihenfolge, Vermeiden der Verwendung bestimmter Funktionen usw.

Der Ausführungsplan wird in den folgenden Kapiteln ausführlich erläutert.

IV. Einhaltung der Grundsätze

Wenn Sie sich beim täglichen Schreiben von SQL gute Gewohnheiten aneignen und mehr aufpassen, können Sie einige SQL-Leistungsprobleme weitgehend vermeiden. Die Zusammenfassung lautet wie folgt:

• Legen Sie für jede Tabelle immer einen ID-Primärschlüssel fest.
• Vermeiden Sie die Verwendung von SELECT *.
• Indizieren Sie die Suchfelder.
• Verwenden Sie beim Verknüpfen von Tabellen Spalten entsprechender Typen und indizieren Sie diese.
• Verwenden Sie, wann immer möglich, NOT NULL.
• Kleinere Spalten sind schneller.
• Verwenden Sie LIMIT 1, wenn nur eine Datenzeile benötigt wird.
• Bei der Operatoroptimierung wird versucht, keine Operatoren zu verwenden, die der Indizierung nicht förderlich sind, um vollständige Tabellenscans zu vermeiden.
  

1) Verwenden Sie „in“ und nicht „in“ mit Vorsicht. Versuchen Sie, „between“ statt „in“ zu verwenden, und verwenden Sie „not exists“ statt „not in“.
2) Verwenden Sie „ist null“ und „ist nicht null“ mit Vorsicht
3) Vermeiden Sie wenn möglich die Verwendung der Operatoren != oder <>. Andernfalls verzichtet die Engine auf die Verwendung des Index und führt einen vollständigen Tabellenscan durch.

5. Verwenden Sie den Abfrage-Cache

Wenn viele identische Abfragen mehrmals ausgeführt werden, werden die Abfrageergebnisse in einen Cache gestellt, sodass nachfolgende identische Abfragen ohne Operationen direkt auf die zwischengespeicherten Ergebnisse zugreifen können.

Der MySQL-Abfragecache speichert die vollständigen von Abfragen zurückgegebenen Ergebnisse. Wenn eine Abfrage den Cache erreicht, gibt MySQL das Ergebnis zurück und überspringt dabei die Analyse, Optimierung und Ausführungskürzung.

Dies ist eine der effektivsten Möglichkeiten, die Abfrageleistung zu verbessern, und wird von der MySQL-Engine übernommen. Normalerweise aktiviert MySQL den Abfragecache nicht standardmäßig und muss manuell aktiviert werden.

Der Abfragecache ist für die Anwendung vollständig transparent. Die Anwendung muss sich nicht darum kümmern, ob MySQL die Ergebnisse durch Abfragen oder die tatsächliche Ausführung zurückgibt. Tatsächlich sind die Ergebnisse dieser beiden Methoden genau gleich. Mit anderen Worten: Zum Abfragen des Caches ist keine Syntax erforderlich.

Da aktuelle Allzweckserver immer leistungsfähiger werden, stellt der Abfragecache einen Faktor dar, der die Skalierbarkeit des Servers beeinflusst. Es kann zu einem einzigen Punkt der Ressourcenkonkurrenz für den gesamten Server werden und auf einem Multi-Core-Server sogar einen Server-Deadlock verursachen. Daher sollte der Abfragecache in den meisten Fällen standardmäßig deaktiviert sein. Wenn der Abfragecache sehr nützlich ist, können Sie einen kleinen Cache-Speicherplatz von mehreren zehn Megabyte konfigurieren. (Bei der Auswahl müssen Sie einen Kompromiss eingehen)

Für die Konfiguration des Abfragecaches stehen die folgenden Parameter zur Verfügung:

• Abfrage-Cache-Typ

Ob der Abfragecache aktiviert werden soll. Sie können es auf OFF, ON oder DEMAND setzen. DEMAND bedeutet, dass nur Anweisungen, die in der Abfrageanweisung explizit in sql_cache geschrieben werden, in den Abfragecache gestellt werden.

• Abfrage-Cachegröße
  

Der gesamte vom Abfragecache verwendete Speicherplatz in Bytes. Dieser Wert muss ein ganzzahliges Vielfaches von 1024 sein, sonst weicht die tatsächlich zugewiesene Datengröße von der angegebenen ab.

• query_cache_min_res_unit
  

Die minimale Speichereinheit, die im Abfragecache zugewiesen ist.

• query_cache_limit

Maximale zu zwischenspeichernde Abfrageergebnisse. Wenn das Abfrageergebnis größer als dieser Wert ist, wird es nicht zwischengespeichert. Da der Abfragecache versucht, die Daten bereits bei ihrer Generierung zwischenzuspeichern, erkennt MySQL erst, ob die Abfrageergebnisse den Grenzwert überschreiten, wenn alle Ergebnisse zurückgegeben wurden.

Was den Abfragecache betrifft, wird er in den folgenden Kapiteln gesondert und ausführlich erläutert.

Oben finden Sie Einzelheiten zu den Techniken zur MySQL-Optimierung von SQL-Anweisungen. Weitere Informationen zu SQL-Anweisungen zur MySQL-Optimierung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!