Detaillierte Analyse der MySQL-Abfrageabfangung

1. Abfrageoptimierung

1. MySQL-Tuning-Übersicht

• Aktivieren und Erfassen langsamer Abfragen
• Erklären Sie + langsame SQL-Analyse
• Profilabfragen zeigen die Ausführungsdetails und den Lebenszyklus von SQL im MySQL-Server an
• Parameteroptimierung des SQL-Datenbankservers

2. Kleiner Tisch treibt großen Tisch an

Das Implementierungsprinzip von MySQL Join besteht darin, die Daten der treibenden Tabelle als Grundlage zu verwenden und „verschachtelte Schleifen“ zu verwenden, um Datensätze in der getriebenen Tabelle abzugleichen. Der Index der treibenden Tabelle ist ungültig, während der Index der angetriebenen Tabelle gültig ist.

#Angenommen, Tabelle A enthält 10.000 Daten und Tabelle B enthält 20 Daten. select * from a join b on a.bid =b.id

Tabelle a steuert Tabelle b wie folgt:
für 20 Daten, um 10.000 Daten abzugleichen (basierend auf der Verbindungsbedingung für a.bid=b.id, führen Sie eine B+-Baumsuche durch)
Die Anzahl der Suchvorgänge beträgt: 20+ log10000
Tabelle b veranlasst Tabelle a, 20 Daten für 10.000 Daten abzugleichen (basierend auf der Join-Bedingung für a.bid=b.id wird eine B+-Baumsuche durchgeführt). Die Anzahl der Suchvorgänge beträgt: 10.000+ log20

3. in und existiert

Verwendung von Exits

• EXISTS-Syntax: EXISTS(Unterabfrage) gibt nur TRUE oder FALSE zurück, daher kann das SELECT * in der Unterabfrage auch SELECT 1 oder etwas anderes sein. Die offizielle Aussage ist, dass die SELECT-Liste während der tatsächlichen Ausführung ignoriert wird, es gibt also keinen Unterschied
  • SELECT ... FROM Tabelle WHERE EXISTS(Unterabfrage)
  • Diese Syntax kann wie folgt verstanden werden: Füge die abgefragten Daten zur bedingten Überprüfung in die Unterabfrage ein und entscheide basierend auf dem Überprüfungsergebnis (WAHR oder FALSCH), ob die Datenergebnisse der Hauptabfrage beibehalten werden.
• Der eigentliche Ausführungsprozess der EXISTS-Unterabfrage kann optimiert werden, anstatt wie wir es verstehen einen Einzelvergleich durchzuführen. Wenn Sie über Effizienzprobleme besorgt sind, können Sie tatsächliche Tests durchführen, um festzustellen, ob Effizienzprobleme vorliegen.
• EXISTS-Unterabfragen können häufig durch bedingte Ausdrücke, andere Unterabfragen oder JOIN ersetzt werden. Die beste Lösung erfordert eine spezifische Analyse spezifischer Probleme.

#Wenn in verwendet wird, steuert die interne Tabelle B die externe Tabelle A
wähle * aus A, wobei ID in (wähle ID aus B)
#Mithilfe von exists steuert die externe Tabelle A die interne Tabelle B
wähle * aus A, wo existiert (wähle 1 aus B, wo B.id = A.id)

abschließend:

Denken Sie immer daran: Wenn der Datensatz der Tabelle B kleiner ist als der Datensatz der Tabelle A, verwenden Sie in
Wenn der Datensatz der Tabelle A kleiner ist als der der Tabelle B, verwenden Sie

4. Tabelle per Orderby erstellen

Tabelle tblA erstellen(
 #id int Primärschlüssel nicht null auto_increment,
 Alter int,
 Geburtszeitstempel nicht null
);

in tblA (Alter, Geburt) Werte (22, jetzt ()) einfügen;
in tblA (Alter, Geburt) Werte (23, jetzt ()) einfügen;
in tblA (Alter, Geburt) Werte (24, jetzt ()) einfügen;
#Erstellen Sie einen zusammengesetzten Index. create index idx_A_ageBirth on tblA(age, birth); 

Orderby trifft den Index

Orderby trifft den Index nicht

• MySQL unterstützt zwei Sortiermethoden: Mithilfe von Index und Mithilfe von Filesort. Filesort ist weniger effizient und um die Indexsortierung verwenden zu können, müssen zwei Bedingungen erfüllt sein: Die Sortierung erfolgt so weit wie möglich nach der Indexspalte und es wird das beste linke Präfix des Index befolgt.
  • Die Anweisung order by selbst verwendet die äußerste linke Spalte des Index
  • Verwenden Sie die Kombination aus Where-Klausel und Order-By-Klausel für Bedingungsspalten, um die Spalte ganz links zu finden
• Wenn die Sortierung nicht in der Indexspalte erfolgt, wird der Filesort-Algorithmus verwendet: Zwei-Wege-Sortierung und Einweg-Sortierung
  • Vor MySQL 4.1 wurde eine zweiseitige Sortierung verwendet, was buchstäblich bedeutete, dass die Festplatte zweimal gescannt wurde, um schließlich an die Daten zu gelangen. Lesen Sie den Zeilenzeiger und sortieren Sie nach Spalten, sortieren Sie sie, scannen Sie dann die sortierte Liste und lesen Sie die entsprechende Datenübertragung aus der Liste entsprechend dem Wert in der Liste erneut
  • Lesen Sie alle für die Abfrage benötigten Spalten von der Festplatte, sortieren Sie sie im Puffer entsprechend der Spaltenreihenfolge und durchsuchen Sie dann die sortierte Liste nach Ausgabe. Dies ist schneller, vermeidet das zweite Lesen von Daten und wandelt zufällige IO in sequentielle IO um, benötigt jedoch mehr Speicherplatz, da jede Zeile im Speicher bleibt.

select * from user where name = "zs", sortiert nach Alter
#Dualsortierung 1) Finden Sie die erste Primärschlüssel-ID, die Name = 'zs' aus Name erfüllt
2) Nehmen Sie die gesamte Zeile basierend auf der Primärschlüssel-ID und legen Sie die Sortierfelder „Alter“ und „Primärschlüssel-ID“ in den Sortierpuffer. 3) Nehmen Sie die nächste Primärschlüssel-ID aus dem Namen, die dem Datensatz mit dem Namen = „zs“ entspricht.
4) Wiederholen Sie 2 und 3, bis name = 'zs' nicht mehr erfüllt ist
5) Sortieren Sie das Altersfeld und die Primärschlüssel-ID im sort_buffer entsprechend dem Altersfeld. 6) Durchlaufen Sie die sortierte ID und das Altersfeld, kehren Sie entsprechend dem ID-Wert zur ursprünglichen Tabelle zurück, rufen Sie die Werte aller Felder ab und geben Sie sie an den Client zurück. #Einwegsortierung 1) Suchen Sie die erste Primärschlüssel-ID aus dem Namen, die die Bedingung „Name = ‚zs‘“ erfüllt.
2) Nehmen Sie die gesamte Zeile basierend auf der Primärschlüssel-ID, nehmen Sie die Werte aller Felder heraus und speichern Sie sie im sort_buffer (Sortierpuffer). 3) Suchen Sie aus dem Indexnamen die nächste Primärschlüssel-ID, die die Bedingung name = 'zs' erfüllt
4) Wiederholen Sie die Schritte 2 und 3, bis name = 'zs' nicht mehr erfüllt ist
5) Sortieren Sie die Daten im sort_buffer nach dem Feldalter und geben Sie das Ergebnis an den Client zurück

Probleme und Optimierung der Single-Path-Sortierung

Frage:
 Da der Single-Pass-Algorithmus verbessert wurde, ist er im Allgemeinen besser als der Dual-Pass-Algorithmus im Sortierpuffer. Methode B nimmt viel mehr Platz ein als Methode A, da Methode B alle Felder herausnimmt. Daher kann die Gesamtgröße der herausgenommenen Daten die Kapazität des Sortierpuffers überschreiten, was dazu führt, dass bei jedem Sortieren (Erstellen einer temporären Datei, Zusammenführen mehrerer Durchgänge) nur Daten bis zur Kapazität des Sortierpuffers herausgenommen werden, dann Daten bis zur Kapazität des Sortierpuffers herausgenommen werden und erneut sortiert wird ... Dies führt zu mehreren I/Os.
Optimierungsstrategie:
 Erhöhen Sie die Einstellung des Parameters sort_buffer_size. Erhöhen Sie die Einstellung des Parameters max_length_for_sort_data. Hinweise:
  Die Auswahl von * bei der Bestellung per ist ein großes Tabu. Fragen Sie nur die erforderlichen Felder ab. Denn je mehr Felder vorhanden sind, desto mehr Daten müssen im Speicher gespeichert werden, was dazu führt, dass pro I/O weniger Datenspalten geladen werden.

5. Groupby-Optimierung

1) Group by sortiert zuerst und gruppiert dann nach dem besten linken Präfix des Index
2) Wenn die Indexspalte nicht verwendet werden kann, erhöhen Sie die Parametereinstellung max_length_for_sort_data + erhöhen Sie die Parametereinstellung sort_buffer_size
3) „where“ ist höher als „having“. Wenn die Bedingungen in „where“ geschrieben werden können, verwenden Sie „having“ nicht.
4) Die übrigen Regeln sind die gleichen wie bei Order by

2. Langsames Abfrageprotokoll

1. Was ist das Protokoll für langsame Abfragen?

1. Das MySQL Slow Query Log ist ein Protokolltyp, der von MySQL bereitgestellt wird. Es wird verwendet, um Anweisungen in MySQL aufzuzeichnen, deren Antwortzeit den Schwellenwert überschreitet. Insbesondere werden SQL-Anweisungen, deren Ausführungszeit den Wert „long_query_time“ überschreitet, im Slow Query Log aufgezeichnet.
2. Der Standardwert von long_query_time ist 10, was bedeutet, dass SQL-Anweisungen, die länger als 10 Sekunden ausgeführt werden, aufgezeichnet werden.
3. Damit wird überprüft, welche SQL-Anweisungen unseren maximalen Toleranzzeitwert überschreiten. Wenn beispielsweise die Ausführung einer SQL-Anweisung länger als 5 Sekunden dauert, betrachten wir sie als langsame SQL-Anweisung. Wir hoffen, SQL-Anweisungen zu sammeln, die länger als 5 Sekunden dauern, und basierend auf der vorherigen Erklärung eine umfassende Analyse durchzuführen.

2. Aktivieren Sie das Protokoll für langsame Abfragen

Standardmäßig ist das MySQL-Protokoll für langsame Abfragen nicht aktiviert. Wenn es für die Optimierung nicht erforderlich ist, wird die Aktivierung dieses Parameters im Allgemeinen nicht empfohlen, da die Aktivierung des langsamen Abfrageprotokolls die Leistung beeinträchtigt. Das langsame Abfrageprotokoll unterstützt das Schreiben von Protokolldatensätzen in Dateien.

a) Aktivieren Sie das Protokoll für langsame Abfragen

#Überprüfen Sie, ob das langsame Protokoll aktiviert ist. Zeigen Sie Variablen wie „slow_query_log%“ an.
# Aktivieren Sie das langsame Abfrageprotokoll. Um es dauerhaft zu machen, setzen Sie global slow_query_log = 1 in my.cnf; 

b) Festlegen des Schwellenwerts für langsame Abfrageprotokolle

#Die Standardschwellenzeit zum Anzeigen langsamer Abfrageprotokolle beträgt 10 Sekunden
Variablen wie „long_query_time%“ anzeigen;
#Auf 3s einstellen. Neustart schlägt fehl. Wenn Sie möchten, dass es dauerhaft ist, setzen Sie global long_query_time = 3 in my.cnf
#Um es erneut anzuzeigen, müssen Sie das Fenster wechseln, um Variablen wie „long_query_time%“ anzuzeigen. 

c) Dauerhafte langsame Abfrageprotokolle und Zeitschwellenwerte

[mysqld]
#Persistentes Protokoll langsamer Abfragen slow_query_log=1;
slow_query_log_file=/var/lib/mysql/hadoop102-slow.log
lange_Abfragezeit = 3;
log_output=DATEI

d) Langsamer Abfragefall

#Abfrage warte 4s
wähle sleep(4);

#Zeigen Sie im Linux-System das Protokoll für langsame Abfragen an cat /var/lib/mysql/hadoop102-slow.log

e) Überprüfen Sie die Anzahl der langsamen Abfrageprotokolle im aktuellen System

globalen Status wie „%Slow_queries%“ anzeigen;

3. Protokollanalysebefehl mysqldumpslow

a) Parametererklärung

-s: gibt die Sortiermethode an
c: Anzahl der Besuche
l: Sperrzeit
r: Datensätze zurückgeben
t: Abfragezeit
al: durchschnittliche Sperrzeit
ar: durchschnittliche Anzahl der zurückgegebenen Datensätze
at: durchschnittliche Abfragezeit
-t: wie viele Datensätze werden zurückgegeben
-g: gefolgt von einem regulären Übereinstimmungsmuster, ohne Berücksichtigung der Groß-/Kleinschreibung

b) Gemeinsame Methoden

#Holen Sie sich die 10 SQL-Anweisungen, die die meisten Datensätze zurückgeben
mysqldumpslow -sr -t 10 /var/lib/mysql/hadoop102-slow.log
#Holen Sie sich die 10 am häufigsten besuchten SQLs
mysqldumpslow -sc -t 10 /var/lib/mysql/hadoop102-slow.log
#Holen Sie sich die ersten 10 Abfragen, sortiert nach Zeit, die Left Joins enthalten mysqldumpslow -st -t 10 -g "left join" /var/lib/mysql/hadoop102-slow.log
#Diese Befehle werden mit | und mehr kombiniert, um mysqldumpslow -sr -t 10 /var/lib/mysql/hadoop102-slow.log | mehr zu verwenden.

3. Stapelverarbeitung von Datenskripten

1. Erstellen Sie eine Tabelle

Tabelle erstellen Abteilung
(
 deptno int vorzeichenloser Primärschlüssel auto_increment,
 dname varchar(20) nicht null Standard '',
 loc varchar(8) nicht null Standard ''
)ENGINE=INNODB STANDARD-CHARSET=utf8;

Tabelle erstellen emp
(
 id int vorzeichenloser Primärschlüssel auto_increment,
 empno mediumint unsigned nicht null Standard 0,
 ename varchar(20) nicht null Standard '',
 job varchar(9) nicht null Standard '',
 mgr mediumint unsigniert nicht null Standard 0,
 Einstellungsdatum nicht null,
 sal decimal(7,2) nicht null,
 comm decimal(7,2) nicht null,
 deptno mediumint unsigniert nicht null Standard 0
)ENGINE=INNODB STANDARD-CHARSET=utf8;

2. Legen Sie fest, ob dem Ersteller der gespeicherten Funktion vertraut werden soll

#Binlog-Status anzeigen, Variablen wie „log_bin%“ anzeigen;
#Fügen Sie vertrauenswürdige Ersteller von Speicherfunktionen hinzu. Setzen Sie global log_bin_trust_function_creators = 1; 

3. Erstellen Sie eine Funktion

Funktion zum Generieren einer zufälligen Zeichenfolge

# Definieren Sie zwei $$, um das Ende anzuzeigen (ersetzen Sie das ursprüngliche ;)
Trennzeichen $$ 
Funktion „rand_string(n int)“ erstellen gibt „varchar(255)“ zurück
beginnen
 Deklarieren Sie chars_str varchar (100) als Standard „abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ“.
 Deklarieren Sie return_str varchar(255) als Standard '';
 deklariere i als int default 0;
 während ich
 setze return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
 setze i=i+1;
 Ende während;
 Rückgabe return_str;
Ende $$

Funktion zur zufälligen Generierung von Abteilungsnummern

Trennzeichen $$
Funktion „rand_num()“ erstellen gibt „int(5)“ zurück
beginnen
 deklariere i als int default 0;
 setze i = floor(100+rand()*10);
 gebe ich zurück;
Ende $$

4. Erstellen Sie eine gespeicherte Prozedur

Erstellen Sie eine gespeicherte Prozedur zum Einfügen von Daten in die Tabelle emp

Trennzeichen $$
Prozedur insert_emp erstellen(in start int(10),in max_num int(10))
beginnen
 deklariere i als int default 0;
 setze Autocommit = 0;
 wiederholen
 setze i = i+1;
 einfügen in emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) Werte((Start+i),rand_string(6),'Verkäufer',0001,curdate(),2000,400,rand_num());
 bis i=max_num
 Wiederholung beenden;
 begehen;
Ende $$

Erstellen Sie eine gespeicherte Prozedur zum Einfügen von Daten in die Dept-Tabelle

Trennzeichen $$
erstelle Prozedur insert_dept(in start int(10),in max_num int(10))
beginnen
 deklariere i als int default 0;
 setze Autocommit = 0;
 wiederholen
 setze i = i+1;
 in Abteilung einfügen (Abteilungsnummer, dname, Ort) Werte ((Start + i), Randzeichenfolge (10), Randzeichenfolge (8));
 bis i=max_num
 Wiederholung beenden;
 begehen;
Ende $$

5. Rufen Sie gespeicherte Prozeduren auf, um Daten zu generieren

#10 Datensätze in die Abteilungstabelle einfügen DELIMITER;
Rufen Sie insert_dept(100, 10) auf;
#Fügen Sie 500.000 Datensätze in die Mitarbeitertabelle ein. CALL insert_emp(100001, 500000);

4. Profile anzeigen

1. Einleitung

• SHOW PROFILE ist ein von MySQL bereitgestellter Befehl, mit dem der Ressourcenverbrauch der Anweisungsausführung in der aktuellen Sitzung analysiert werden kann. Kann für SQL-Tuning-Messungen verwendet werden.
• Standardmäßig ist der Parameter deaktiviert und die Ergebnisse der letzten 15 Durchläufe werden gespeichert.

2. Öffnen

#Überprüfen Sie, ob „Profil anzeigen“ aktiviert ist, zeigen Sie Variablen wie „profiling%“ an;
#Profil anzeigen
Profilerstellung auf „Ein“ setzen;

3. Verwenden Sie Showprofile

Erstellen von Testdaten

wähle * aus der Emp-Gruppe nach ID %10, Limit 150000;
wähle * aus der Emp-Gruppe nach ID %10, Limit 150000;
Wählen Sie * aus der Emp-Gruppe nach ID %10, sortiert nach 5;
wähle * aus emp
Wählen Sie * aus der Abteilung
wähle * von emp left, schließe Abteilung an bei emp.deptno = dept.deptno

Showprofile ausführen

Führen Sie „Show Profile CPU, Block IO“ für die Abfrage „Query_ID“ aus.

Suchparameter

ALLE: Alle Kosteninformationen anzeigen
BLOCK IO: Zeigt den Block IO-bezogenen Overhead an
KONTEXTWECHSEL: Kontextwechselbezogener Overhead
CPU: Zeigt CPU-bezogene Overhead-Informationen an
IPC: Zeigt Informationen zum Sende- und Empfangs-Overhead an.
MEMORY: Speicherbezogene Overhead-Informationen anzeigen
SEITENFEHLER: Zeigt Informationen zum Seitenfehler-bezogenen Overhead an.
QUELLE: Zeigt die Overhead-Informationen zu Quellfunktion, Quelldatei und Quellzeile an.
SWAPS: Zeigt Informationen über die Anzahl der Swaps im Zusammenhang mit Overhead an

Ergebnisse zurückgeben

Konvertieren von HEAP in MyISAM: Die Abfrageergebnisse sind zu groß, um in den Speicher zu passen, und müssen auf die Festplatte verschoben werden.
Temporäre Tabelle erstellen: Erstellen Sie eine temporäre Tabelle. MySQL kopiert die Daten zunächst in die temporäre Tabelle und löscht die temporäre Tabelle nach der Verwendung.
Kopieren in temporäre Tabelle auf der Festplatte: Das Kopieren der temporären Tabelle im Speicher auf die Festplatte ist gefährlich! ! !
gesperrt: gesperrte Tabelle

5. Globales Abfrageprotokoll

Aktivieren Sie diese Funktion nicht in einer Produktionsumgebung.

In my.cnf konfigurieren

# Aktivieren Sie general_log=1
# Notieren Sie den Pfad der Protokolldatei general_log_file=/Pfad/Logdatei
# Ausgabeformat log_output=DATEI

Kodierung aktiviert

setze globales General_Log = 1;
Setzen Sie global log_output='TABLE'.

Nachdem die Konfiguration abgeschlossen ist, wird sie in der Tabelle general_log in der MySQL-Datenbank aufgezeichnet

Wählen Sie * aus mysql.general_log;

Zusammenfassen

Dies ist das Ende dieses Artikels über das Abfangen von MySQL-Abfragen. Weitere relevante Inhalte zum Abfangen von MySQL-Abfragen finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!