Leitfaden zur MySQL-Leistungsoptimierung

Das Hauptziel dieses Artikels besteht darin, Ihnen vorzustellen, wie Sie die MySQL-Datenbank regelmäßig analysieren und überprüfen, Tabellen regelmäßig optimieren und einige gängige SQL-Anweisungen optimieren. Dies kann Ihnen dabei helfen, effizienteres SQL in MySQL zu schreiben Entwicklung.

1. Periodische Analysetabelle und Prüftabelle

Die Syntax der Analysetabelle lautet wie folgt:

ANALYZE [LOCAL |. NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name] ...

Die obigen Anweisungen werden verwendet, um die Schlüsselwortverteilung der Tabelle zu analysieren und zu speichern, damit das System sie erhalten kann Genaue statistische Informationen, die es SQL ermöglichen, einen korrekten Ausführungsplan zu generieren. Wenn der Benutzer der Meinung ist, dass der tatsächliche Ausführungsplan nicht dem erwarteten Ausführungsplan entspricht, kann das Problem möglicherweise durch Ausführen einer Analysetabelle gelöst werden. Während der Analyse wird die Tabelle mit einer Lesesperre gesperrt. Dies funktioniert für MyISAM-, DBD- und InnoDB-Tabellen.

Zum Beispiel eine Datentabelle analysieren

Tabelle Tabellenname analysieren

Die Syntax von Die Prüftabelle lautet wie folgt:

CHECK TABLE tb1_name]...[option]...option = {QUICK |. EXTENDED} >

Die Funktion der Prüftabelle besteht darin, zu überprüfen, ob eine oder mehrere Tabellen Fehler aufweisen. Bei MyISAM- und InnoDB-Tabellen werden Schlüsselwortstatistiken aktualisiert

CHECK TABLE kann die Ansicht auch auf Fehler überprüfen, z. B. auf eine Tabelle, auf die in der Ansichtsdefinition verwiesen wird und die nicht vorhanden ist.

2. Optimieren Sie die Tabelle regelmäßig

Die Syntax der optimierten Tabelle lautet wie folgt:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name]...

Wenn ein großer Teil der Tabelle gelöscht wurde oder wenn eine Tabelle mit Zeilen variabler Länge (eine Tabelle mit VARCHAR, BLOB , oder TEXT-Spalten) wurde Für weitere Änderungen sollte der Befehl OPTIMIZE TABLE zur Tabellenoptimierung verwendet werden. Dieser Befehl kann die Speicherplatzfragmente in der Tabelle zusammenführen und die durch Löschen oder Aktualisieren verursachte Speicherplatzverschwendung beseitigen, aber der Befehl OPTIMIZE TABLE funktioniert nur bei MyISAM-, BDB- und InnoDB-Tabellen.

Zum Beispiel: Tabelle optimieren Tabellenname

Hinweis: Die Tabelle wird während der Ausführung von Analyse, Prüfung und Optimierung gesperrt. Stellen Sie daher sicher, dass Sie entsprechende Vorgänge ausführen, wenn die MySQL-Datenbank nicht ausgelastet ist .

Häufig verwendete SQL-Optimierung

Die SQL-Anweisungen, die wir während der Entwicklung häufig verwenden, sind nichts anderes als INSERT, GROUPBY usw. Wie optimieren wir diese SQL-Anweisungen?

1. Daten in großen Mengen einfügen

Bei Verwendung des Ladebefehls zum Importieren von Daten können entsprechende Einstellungen die Importgeschwindigkeit erhöhen.

Für MyISAM-Speicher-Engine-Tabellen können Sie große Datenmengen auf folgende Weise schnell importieren

Der Code lautet wie folgt:

ALTER TABLE tb1_name DISABLE KEYS;
loading the data
ALTER TABLE tb1_name ENABLE KEYS;

TASTEN DEAKTIVIEREN und ENABLE KEYS werden verwendet, um die Aktualisierung nicht eindeutiger Indizes für MyISAM-Tabellen zu aktivieren oder zu deaktivieren. Beim Importieren einer großen Datenmenge in eine nicht leere MyISAM-Tabelle kann das Festlegen dieser beiden Befehle die Effizienz des Imports verbessern.

Beim Importieren einer großen Datenmenge in eine leere MyISAM-Tabelle werden standardmäßig zuerst die Daten importiert und dann der Index erstellt. Der Index muss nicht festgelegt werden.

Daten in Datei „/home/mysql/text_txt“ in Tabellentext laden

Für Tabelle vom Typ InnoDB: This Die Methode kann die Effizienz des Datenimports nicht verbessern, es gibt jedoch mehrere Möglichkeiten, Tabellen vom Typ InnoDB zu optimieren.

1. Da Tabellen vom Typ InnoDB in der Reihenfolge der Primärschlüssel gespeichert werden, kann das Sortieren der importierten Daten in der Reihenfolge der Primärschlüssel die Effizienz der importierten Daten effektiv verbessern.

2. Führen Sie SET UNIQUE_CHECKS=0 aus, bevor Sie Daten importieren, um die Eindeutigkeitsüberprüfung zu deaktivieren. Führen Sie nach Abschluss des Imports SET UNIQUE_CHECKS=1 aus, um die Eindeutigkeitsüberprüfung wiederherzustellen, was die Effizienz des Imports verbessern kann.

3. Wenn die Anwendung die automatische Übermittlung verwendet, wird empfohlen, SET AUTOCOMMIT=0 vor dem Import auszuführen, um die automatische Übermittlung zu deaktivieren. Nachdem der Import abgeschlossen ist, führen Sie SET AUTOCOMMIT=1 aus, um die automatische Übermittlung zu aktivieren kann auch die Importeffizienz verbessern.

INSERT-Anweisungen optimieren

Beim Durchführen von Daten-INSERT können Sie die folgenden Möglichkeiten zur Optimierung in Betracht ziehen

1 Wenn viele Zeilen gleichzeitig von einem Kunden eingefügt werden, versuchen Sie es Um INSERT-Anweisungen mehrerer Wertetabellen zu verwenden, verkürzt diese Methode die Verbindung, das Herunterfahren und andere Verbrauchsvorgänge zwischen dem Client und der MySQL-Datenbank erheblich und macht die Effizienz schneller als eine einzelne INSERT-Anweisung, die separat ausgeführt wird.

Zum Beispiel:

i

nsert into test values(1,2)
insert into test values(3,4)
insert into test values(5,6)

Ändern Sie die obigen drei Sätze wie folgt: Einfügen in Testwerte(1,2),(3,4),(5 ,6)……

2. Wenn Sie viele Zeilen von verschiedenen Clients einfügen, können Sie mit der INSERT DELAYED-Anweisung eine höhere Geschwindigkeit erzielen.

DELAYED bedeutet, dass die INSERT-Anweisung tatsächlich in die Speicherwarteschlange gestellt und nicht tatsächlich auf die Festplatte geschrieben wird. Dies ist genau das Gegenteil other Der Benutzer fügt nach dem Lesen und Schreiben in die Tabelle ein.

3. Speichern Sie Indexdateien und Datendateien auf verschiedenen Datenträgern

4. Wenn Sie Stapeleinfügungen durchführen, können Sie die Geschwindigkeit erhöhen, indem Sie den Wert der Variablen „bulk_insert_buffer_size“ erhöhen Für MyISAM-Tabellen verwenden.

5. Wenn Sie eine Tabelle aus einer Textdatei laden, verwenden Sie LOAD DATA INFILE. Dies ist normalerweise etwa 20-mal schneller als die Verwendung vieler Einfügeanweisungen.

Das Obige sind einige andere Dinge, die bei der MySQL-Leistungsoptimierung beachtet werden müssen. Lassen Sie uns sie kombinieren, um die Leistung von MySQL voll auszunutzen.