Detaillierte Erläuterung der MySQL SQL-Anweisungsanalyse und Abfrageoptimierung

So erhalten Sie SQL-Anweisungen mit Leistungsproblemen

1. Erhalten Sie SQL-Anweisungen mit Leistungsproblemen durch Benutzerfeedback
2. Erhalten Sie SQL-Anweisungen mit Leistungsproblemen durch langsame Abfrageprotokolle
3. SQL mit Leistungsproblemen in Echtzeit abrufen

Verwenden Sie langsame Abfrageprotokolle, um SQL-Anweisungen mit Leistungsproblemen abzurufen

Lassen Sie uns zunächst die Parameter im Zusammenhang mit langsamen Abfragen vorstellen.

1. slow_query_log startet die benutzerdefinierte Einstellung für das langsame Abfrageprotokoll. Sie können es über die MySQL-Befehlszeile festlegen: set global slow_query_log=on
Oder ändern Sie die Datei /etc/my.cnf und fügen Sie slow_query_log=on hinzu

2. slow_query_log_file gibt den Speicherpfad und die Datei des langsamen Abfrageprotokolls an. Es wird empfohlen, den Protokollspeicher und den Datenspeicher getrennt zu speichern.

3. long_query_time gibt den Schwellenwert für die Aufzeichnung der SQL-Ausführungszeit des langsamen Abfrageprotokolls an. ① Zeichnet alle SQL auf, die die Bedingungen erfüllen
② Anweisungen zur Datenänderung ③ Einschließlich Abfrageanweisungen ④ Zurückgesetztes SQL

Beachten:
Die Zeit kann auf Mikrosekunden genau sein, und die Speichereinheit ist Sekunden. Der Standardwert beträgt 10 Sekunden. Wenn wir beispielsweise den Wert von 1 Mikrosekunde abfragen möchten, müssen wir ihn auf 0,001 Sekunden einstellen.

4. log_queries_not_using_indexes Gibt an, ob SQL protokolliert werden soll, das keine Indizes verwendet

5. log_output legt das Speicherformat der langsamen Protokollabfrage fest (wenn Sie es als Datei speichern müssen, ändern Sie es bitte in DATEI).

Im Nutzungsprotokoll für langsame Abfragen aufgezeichnete Informationen

1. Die erste Zeile zeichnet die Testinformationen mit sbtest auf
2. Die zweite Zeile zeichnet die Zeit des langsamen Abfrageprotokolls auf
3. Die in der dritten Zeile aufgezeichneten Informationen sind die Zeit der verwendeten Sperre
4. Die in der vierten Zeile aufgezeichneten Informationen sind die Anzahl der zurückgegebenen Datenzeilen
5. Die in der fünften Zeile aufgezeichneten Informationen sind die Anzahl der Zeilen der gescannten Daten
6. Die in der sechsten Zeile aufgezeichneten Informationen sind der Zeitstempel
7. Die in der siebten Zeile aufgezeichneten Informationen sind die Abfrage-SQL-Anweisung

Verwenden Sie eine langsame Abfrage, um SQL mit Leistungsproblemen abzurufen

Häufig verwendetes Tool zur Analyse langsamer Abfrageprotokolle (mysqldumpslow)
Einleitung: Fasst bis auf die Abfragebedingungen identische SQL-Anweisungen zusammen und gibt die Analyseergebnisse in der in den Parametern angegebenen Reihenfolge aus.

Beispiel für ein langsames Abfrageprotokoll

Konfigurationseinstellungen für langsame Abfragen

Befehlszeilenausführungsparameter zum Anzeigen der Analyseergebnisse

]# cd /var/lib/mysql/log
]# mysqldumpslow -sr -t 10 langsames MySQL

Häufig verwendetes Tool zur Analyse langsamer Abfrageprotokolle (pt-query-digest)

Bevor Sie das Tool verwenden können, müssen Sie es zunächst installieren. Wenn Sie es bereits haben, können Sie die folgenden Installationsschritte überspringen.
1. Perl-Modul
]# yum install -y perl-CPAN perl-Time-HiRes perl-IO-Socket-SSL perl-DBD-mysql perl-Digest-MD5
2. Wechseln Sie in das src-Verzeichnis, um das RPM-Paket herunterzuladen
]# cd /usr/local/src
]# wget https://www.percona.com/downloads/percona-toolkit/3.0.7/binary/redhat/7/x86_64/percona-toolkit-3.0.7-1.el7.x86_64.rpm

3. Installieren Sie das Toolkit
]# rpm -ivh percona-toolkit-3.0.7-1.el7.x86_64.rpm

Ausführen von Befehlen zum Analysieren langsamer Abfrageprotokolle

]# pt-query-digest --user=root --password=redhat --host=127.0.0.1 langsam-mysql > langsam.rep
Die Ergebnisse der Analyse sind wie folgt

Der gesamte Prozess der Verarbeitung von Abfrageanforderungen durch den MySQL-Server

1. Der Client sendet eine SQL-Anfrage an den Server
2. Der Server prüft, ob im Cache-Server ein Treffer auf das SQL vorliegt
3. Der Server analysiert und verarbeitet SQL vor, und dann führt der Optimierer den entsprechenden Ausführungsplan aus
4. Rufen Sie gemäß dem Ausführungsplan die Speicher-Engine-API auf, um Daten abzufragen
5. Das Ergebnis an den Kunden zurückgeben

Die Auswirkungen des Abfragecaches auf die SQL-Leistung

1. Priorisieren Sie die Überprüfung, ob die gesamte Abfrage die Daten im Abfragecache trifft
2. Implementierung über eine Groß-/Kleinschreibung beachtende Hash-Suche

Optimieren der Parameter für den Abfrage-Cache

query_cache_type legt fest, ob der Abfragecache verfügbar ist.
AN, AUS, NACHFRAGE

Hinweis: DEMAND bedeutet, dass in der Abfrageanweisung nur SQL-CACHE und SQL_NO_CACHE verwendet werden, um zu steuern, ob Caching erforderlich ist

query_cache_size legt die Speichergröße des Abfragecaches fest

query_cache_limit legt den Maximalwert des verfügbaren Speicherplatzes im Abfrage-Cache fest

query_cache_wlock_invalidate legt fest, ob zwischengespeicherte Daten zurückgegeben werden sollen, nachdem die Tabelle gesperrt wurde (diese Option ist standardmäßig deaktiviert und wird empfohlen)

query_cache_min_res_unit legt den Mindestwert des vom Abfrage-Cache zugewiesenen Speicherblocks fest

Gründe, die dazu führen, dass MySQL falsche Ausführungspläne generiert

1. Ungenaue statistische Informationen
2. Die Kostenschätzung im Ausführungsplan entspricht nicht den tatsächlichen Kosten des Ausführungsplans
3. Der MySQL-Optimierer könnte denken, dass das Beste etwas anderes ist als das, was Sie für das Beste halten
4. MySQL berücksichtigt niemals andere gleichzeitige Abfragen, was sich auf die aktuellen Abfragedaten auswirken kann
5. MySQL generiert manchmal Ausführungspläne basierend auf einigen festen Regeln
6. MySQL berücksichtigt keine Kosten, die außerhalb seiner Kontrolle liegen

SQL-Typen, die der MySQL-Optimierer optimieren kann

1. Definieren Sie die Assoziationsreihenfolge der Tabelle neu. Der Optimierer bestimmt die Assoziationsreihenfolge der Tabelle anhand statistischer Informationen.

2. Externe Links in interne Links umwandeln
Where-Bedingungen und Datenbanktabellenstruktur usw.

3. Verwenden Sie äquivalente Transformationsregeln
(5=5 und a > 5) wird umgeschrieben als a > 5

4. Optimieren Sie count(), min() und max()
Tabellen auswählen, die optimiert wurden
Der Optimierer hat die Tabelle aus dem Ausführungsplan entfernt und durch eine Konstante ersetzt.

5. Konvertieren Sie einen Ausdruck in einen konstanten Ausdruck

6. Verwenden Sie äquivalente Transformationsregeln

7. Unterabfrageoptimierung

8. Optimieren Sie die in()-Bedingung

So ermitteln Sie die in den verschiedenen Phasen der Abfrageverarbeitung aufgewendete Zeit

Verwenden von Profilen

Profilierung festlegen = 1;
Führen Sie die Abfrage aus:
Profile anzeigen;

Profil für Abfrage N anzeigen;

Die für jede Phase der Abfrage benötigte Zeit

Verwenden Sie das Profil, um die von der Anweisung verbrauchte Zeit anzuzeigen

Spezifische SQL-Abfrageoptimierung

1. Verwenden Sie das Prinzip des Master-Slave-Umschaltens, um die Tabellenstruktur einer großen Tabelle zu ändern. Ändern Sie sie beispielsweise jetzt auf dem Slave-Server, führen Sie nach Abschluss der Änderung ein Master-Slave-Umschalten durch und ändern Sie dann die große Tabelle auf dem ursprünglichen Master. Es besteht ein gewisses Risiko.
2. Erstellen Sie eine neue Tabelle auf dem primären Server. Die Tabellenstruktur ist dieselbe wie die Tabellenstruktur nach der Änderung der großen Tabelle. Importieren Sie dann die Daten der alten Tabelle erneut in die neue Tabelle und erstellen Sie eine Reihe von Triggern in der alten Tabelle, um die Daten der alten Tabelle mit der neuen Tabelle zu synchronisieren. Nachdem alle Daten in der alten Tabelle mit der neuen Tabelle synchronisiert wurden, fügen Sie der alten Tabelle eine exklusive Sperre hinzu, ändern Sie den Namen der neuen Tabelle in die alte Tabelle und löschen Sie die umbenannte alte Tabelle, wie in der folgenden Abbildung gezeigt

Verwenden Sie den Befehl pt-online-schema-change, um die große Tabelle zu ändern. Der spezifische Vorgang ist in der folgenden Abbildung dargestellt

Erklärung der Parameter in der obigen Abbildung

--alter Verwendete SQL-Anweisung
--user Datenbank-Anmeldebenutzer
--password Passwort des angemeldeten Benutzers
D gibt den Datenbanknamen für alle geänderten Tabellen an
t Der Name der Tabelle
--charset gibt die Datenbankzeichenfolge an
--execute Ausführen

Originalarbeit, bei Nachdruck bitte Quelle angeben