Lassen Sie uns ausführlich über die Richtung der langsamen SQL-Optimierung in MySQL sprechen

Vorwort

Es gibt viele Faktoren, die die Laufgeschwindigkeit eines Systems beeinflussen. Sie sind vielschichtig und können sogar zufällig sein, sei es das Front-End, das Back-End, die Datenbank, die Middleware, der Server, das Netzwerk usw. Um ein Problem wirklich zu lokalisieren, müssen Sie ein gewisses Verständnis des Systems haben und in der Lage sein, den Umfang des Problems nach eigenem Ermessen einzugrenzen.

Heute werde ich nicht über andere Optimierungen sprechen, sondern mich auf die Datenbankoptimierung konzentrieren und verschiedene Optimierungsrichtungen diskutieren.

Wie die Optimierungsrichtung des Systems ist auch die Optimierung der Datenbank vielschichtig und umfasst die Ausführung von SQL-Anweisungen, die Situation der Datenbank selbst usw. Als Nächstes werden wir über die Optimierungsrichtung langsamer SQL-Anweisungen in der MySQL-Datenbank sprechen und hoffen, Ihnen einige Optimierungsideen zu geben.

SQL-Anweisungsoptimierung

Es gibt viele Artikel zur Optimierung von SQL-Anweisungen und auch viele Anleitungen zum Schreiben von SQL. Diese können jedoch nur die grundlegende Entwicklung unterstützen. Wenn Sie Probleme beheben möchten, können Sie sich nicht nur auf das Schreiben von SQL beschränken, sondern müssen einen umfassenden Prozess zur Problemerkennung verwenden.

Die Optimierungsrichtung dieses Mal ist grob in mehrere Aspekte unterteilt, z. B. das Erkennen langsamer SQL-Abfragen, das Anzeigen und Analysieren des SQL-Ausführungsplans, das Optimieren des SQL-Schreibens und die Indexoptimierung.

Langsame SQL-Abfragen aufzeichnen

Das Protokollieren langsamer SQL-Abfragen in MySQL kann durch die Verwendung der internen MySQL-Konfiguration erreicht werden, bei der es sich um die slow_query_log-Konfiguration handelt.

Sie können Showvariablen wie „%query%“ verwenden, um die folgenden drei zugehörigen Ergebnisse abzufragen.

lange Abfragezeit | 1,00000
slow_query_log | aus
slow_query_log_file | /data/mysql/mysql_slow.log

Erklären Sie diese drei Parameter.

• long_query_time: Um SQL-Abfragen von langsamen Abfragen unterscheiden zu können, muss eine Abfragezeit angegeben werden. Abfragen, die diese Zeit überschreiten, werden als langsame Abfragen eingestuft. Mit diesem Parameter wird der Zeitbereich festgelegt; die Einheit ist Sekunden, es können Dezimalstellen festgelegt werden.
• slow_query_log: Mit diesem Parameter können Sie die Protokollierung langsamer SQL-Abfragen aktivieren oder deaktivieren. Es gibt zwei Optionen: ein oder aus. Der Standardwert ist aus. Wir wissen also, dass wir die Protokollierung langsamer SQL-Abfragen manuell aktivieren müssen, wenn wir sie aktivieren möchten.
• slow_query_log_file: Der Dateipfad des langsamen Abfrage-SQL-Protokolls, den Sie selbst angeben können.
  

So ändern Sie die Konfiguration

Es gibt zwei Methoden.

Erstens: Ändern Sie die Datei my.ini oder my.cnf und konfigurieren Sie diese drei Konfigurationen als eine.

Zweitens: Verwenden Sie die festgelegte Syntax, um die Parameter direkt in SQLPlus zu ändern. Nach einem Neustart der MySQL-Datenbank wird diese jedoch ungültig. Das SQL lautet wie folgt:

setze globale long_query_time = 10;

Setzen Sie global slow_query_log = on;

Setzen Sie die globale slow_query_log_file = /data/mysql/mysql_slow.log;

Da diese Methode nach dem Neustart fehlschlägt, wird empfohlen, die erste Methode zu verwenden.

Anzeigen von Protokollen langsamer Abfragen

Wie fragt man das langsame Abfrageprotokoll ab? Wenn die Menge gering ist, müssen keine Tools verwendet werden und man kann es einfach direkt öffnen.

Wenn die Menge groß ist, ist die Abfrage mit dem Tool mysqldumpslow bequemer.

mysqldumpslow ist ein Ausführungsskript vom gleichen Typ wie mysqld und kann direkt in der Befehlszeile ausgeführt werden. Die spezifische Verwendung ist wie folgt:

mysqldumpslow-Parameter:

-s ist die Reihenfolge
-----al Durchschnittliche Sperrzeit
-----ar Durchschnittliche Rendite-Aufzeichnungszeit
-----bei durchschnittlicher Abfragezeit (Standard)
-----c zählen
-----l Sperrzeit
-----r Datensatz zurückgeben
-----t Abfragezeit

-t, top, das heißt, wie viele Datensätze werden vorne zurückgegeben
-g, benutzerdefinierter regulärer Ausdruck

Beispielsweise wie folgt:

mysqldumpslow -sr -t 5 /data/mysql/mysql_slow.log

Fragen Sie die fünf langsamen Abfrage-SQLs ab, die die meisten Datensätze zurückgeben.

Ich werde eine Testbibliothek erstellen und später einen separaten Artikel schreiben, um die Verwendung genauer zu erklären.

SQL-Ausführungsplan anzeigen

Den Ausführungsplan anzeigen Schlüsselwörter: EXPLAIN

Anwendung

Führen Sie einfach EXPLAIN SELECT * FROM TABLE_NAME direkt aus;

Ich wollte zunächst nur kurz darauf eingehen, fand es dann aber zu lang und werde es daher für den nächsten Artikel aufheben. Vielen Dank für Ihr Verständnis.

SQL-Schreiboptimierung

Es gibt viele Möglichkeiten, das Schreiben von SQL zu optimieren. Ich habe hier einige davon aufgelistet. Bitte prüfen Sie und füllen Sie die Lücken selbst aus.

• Unabhängig von der in der Abfrageanweisung verwendeten Beurteilungsbedingung (gleich, kleiner als oder größer als) dürfen Sie im Bedingungsabfragefeld auf der linken Seite von „where“ keine Funktionen oder Ausdrücke verwenden.
• Verwenden Sie select * nicht direkt, sondern verwenden Sie die spezifischen Tabellenfelder, die abgefragt werden müssen. select * verwendet einen vollständigen Tabellenscan und keine Indizes.
• Vermeiden Sie NULL-Prüfungen von Spalten in der WHERE-Klausel.
• Vermeiden Sie die Verwendung der Operatoren != oder <> in WHERE.
• Verwenden Sie BETWEEN AND statt IN.
• Erstellen von Indizes für allgemeine Suchkriterien
• Wählen Sie die richtige Speicher-Engine, z. B. InnoDB, MyISAM, MEMORY usw. Die Verwendung unterschiedlicher Speicher-Engines in unterschiedlichen Szenarien führt zu besseren Ergebnissen.
• Die Verwendung von „%123%“ führt nicht zum Durchlaufen des Index, die Verwendung von „123%“ führt jedoch zum Durchlaufen des Index. Sehr wichtig! ! !
• Wählen Sie den entsprechenden Feldtyp aus.
• Versuchen Sie beim Entwerfen von Feldern, NOT NULL zu verwenden.

Warum sollten wir langsames SQL verwalten?

Aus Datenbanksicht: Jede SQL-Ausführung verbraucht eine bestimmte Menge an I/O-Ressourcen. Die Geschwindigkeit der SQL-Ausführung bestimmt, wie lange die Ressourcen belegt sind. Angenommen, die Gesamtressourcenzahl beträgt 100 und eine langsame SQL-Anweisung belegt insgesamt 1 Minute lang 30 Ressourcen. Dann beträgt während dieser Minute die Gesamtmenge der Ressourcen, die anderen SQL-Anweisungen zugewiesen werden können, 70, und der Zyklus wird fortgesetzt. Wenn alle Ressourcen zugewiesen sind, werden alle neuen SQL-Anweisungen in die Warteschlange gestellt.

Aus Anwendungssicht: Eine lange SQL-Ausführungszeit bedeutet Wartezeiten, was zu einer schlechten Benutzererfahrung in OLTP-Anwendungen führt.

Governance-Prioritäten

• Master-Datenbank -> Slave-Datenbank
  • Derzeit verfügen Datenbanken grundsätzlich über eine Architektur mit getrenntem Lesen und Schreiben, wobei das Lesen in der Slave-Datenbank und das Schreiben in der Master-Datenbank erfolgt.
  • Da die Daten der Slave-Datenbank aus der Master-Datenbank kopiert werden, erhöht sich die Replikationsverzögerung mit der Slave-Datenbank, wenn die Master-Datenbank lange wartet.
• SQL-Anweisungen mit langen Ausführungszeiten werden zuerst verarbeitet
• Wenn es einen SQL-Typ mit hoher Parallelität und konzentriertem Zugriff auf eine bestimmte Tabelle gibt, sollte dieser zuerst verwaltet werden.

Zusammenfassen

Dies ist noch lange nicht abgeschlossen. Es gibt noch viele Schreibregeln, und die Erstellung von Indizes wurde noch nicht besprochen. Ich lasse Ihnen etwas Zeit, das Buch selbst zu lesen. Ich hoffe, Sie können Fortschritte machen.

Dies ist das Ende dieses Artikels über die Optimierungsrichtung von langsamem SQL in MySQL. Weitere relevante Inhalte zur Optimierungsrichtung von langsamem MySQL-SQL 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!