Die MySQL-Optimierung umfasst drei Aspekte

Datenbanken sind zu einer unverzichtbaren zugrunde liegenden Abhängigkeit für Internetanwendungen geworden, unter denen MySQL häufiger als Open-Source-Datenbank verwendet wird. In letzter Zeit habe ich mich auf die Entwicklung von Projektprojekten konzentriert und einige im Entwicklungsprozess verwendete Datenbankoptimierungsprinzipien zusammengefasst, in der Hoffnung, mehr Anwendungsentwicklern dabei zu helfen, die MySQL-Datenbank besser zu nutzen.

Die Optimierung von MySQL umfasst hauptsächlich drei Aspekte. Der erste ist die Optimierung von SQL-Anweisungen, und der zweite ist die Optimierung der Tabellenstruktur Dies bezieht sich hauptsächlich auf die Optimierung von Indizes und schließlich auf die Optimierung der Serverkonfiguration.

1. Optimierung der SQL-Anweisung

1) Die Abfrageanweisung sollte zunächst versuchen, einen vollständigen Tabellenscan zu vermeiden Erwägen Sie vor allem die Erstellung von Indizes für die Where-Klausel und die OrderBy-Klausel, aber jede SQL-Anweisung durchläuft höchstens einen Index und erstellt zu viele Indizes Wird beim Einfügen und Aktualisieren zu einem Mehraufwand führen. Gleichzeitig sollten Sie versuchen, die Indizierung zu vermeiden. Sie können das Schlüsselwort explain vor der Abfrageanweisung verwenden, um die SQL anzuzeigen -Anweisung, um zu bestimmen, ob die Abfrageanweisung einen Index verwendet

2) sollte versuchen, EXIST zu verwenden 🎜>NOT EXISTErsetzen Sie IN ​​​​und NOT IN, da letzteres wahrscheinlich dazu führt, dass der vollständige Tabellenscan die Verwendung des Index aufgibt

3) Sie sollten versuchen, eine NULL-Beurteilung von Feldern in der Where-Klausel zu vermeiden, da die NULL Die Beurteilung führt zu einem vollständigen Tabellenscan.

4) Sie sollten versuchen, die Verwendung von oder als Join-Bedingung in Where-Klausel, da sie auch zu einem vollständigen Tabellenscan führt; 5) sollte im

= oder <> bewirken auch einen vollständigen Tabellenscan

6) Die Verwendung von like „%abc%“ oder like „%abc“ führt ebenfalls zu einem vollständigen Tabellenscan, während like „abc%“ verwendet den Index.

7) Bei Verwendung des Union-Operators sollten Sie überlegen, ob Sie stattdessen Union ALL verwenden können, denn Wenn der Union-Operator einen Sortiervorgang für die Ergebnisse durchführt und doppelte Datensätze löscht, sollte Union ALL für Anwendungen verwendet werden, für die diese Anforderung nicht gilt. Letzteres nur Die Ergebnisse werden zusammengeführt und zurückgegeben, was die Leistung erheblich verbessern kann;

8) sollte versuchen, die Verwendung von Ausdrucksoperatoren in der Where-Klausel zu vermeiden zu einem vollständigen Tabellenscan;

9) Sie sollten versuchen, die Verwendung von Funktionen für Felder in der Where-Klausel zu vermeiden, da dies auch der Fall ist Ursache Vollständiger Tabellenscan

10) Auswählen Versuchen Sie, die Verwendung von „*“ zu vermeiden. , da während des Analysevorgangs der SQL-Anweisung „*“ in die Spaltennamen aller Spalten konvertiert wird und diese Arbeit durch Abfragen des Datenwörterbuchs abgeschlossen wird ein gewisser Overhead;

11) In der Where-Klausel sollte die Tabellenverbindungsbedingung vor anderen Bedingungen geschrieben werden, da das Parsen des Where-Klausel ist von hinten nach vorne. Versuchen Sie also, die Einschränkungen, die die meisten Datensätze herausfiltern können, am Ende der Where-Klausel zu platzieren 12) Wenn es einen gemeinsamen Index wie index(a,b,c) in der Datenbanktabelle gibt, die Reihenfolge des Erscheinens der Bedingungsfelder in der

-Klausel sollte mit der Reihenfolge des Erscheinens der Indexfelder übereinstimmen, sonst kann der gemeinsame Index nicht verwendet werden 13) Die Reihenfolge des Erscheinens der Tabellen im From Die -Klausel wirkt sich auch auf die Reihenfolge der SQL-Anweisung aus. Die

-Klausel wird von hinten nach vorne analysiert, d. h. die am Ende geschriebene Tabelle Ende wird zuerst verarbeitet. Die Tabelle mit weniger Datensätzen sollte als Basistabelle ausgewählt und am Ende platziert werden. Wenn es 3 und 3 oder mehr Tabellenverbindungsabfragen gibt, wird das Kreuz Tabelle sollte als Basistabelle verwendet werden;

14) Versuchen Sie, den Operator >= anstelle des Operators > zu verwenden, zum Beispiel das folgende SQL Anweisung, select dbInstanceIdentifier from DBInstance where id > 3, diese Anweisung sollte ersetzt werden durch select dbInstanceIdentifier from DBInstance where id >=4 , die Ausführungsergebnisse der beiden Anweisungen sind die Gleich, aber die Leistung ist unterschiedlich. Letzteres ist effizienter, da bei der Ausführung des ersteren zuerst der Datensatz gefunden wird, der 3 entspricht, und dann vorwärts gescannt wird, während der letztere den Datensatz direkt findet, der gleich ist bis 4.

2. Optimierung der Tabellenstruktur

Dies bezieht sich hauptsächlich auf die korrekte Erstellung von Indizes, da unangemessene Indizes dazu führen, dass das Ganze abgefragt wird Das Scannen von Tabellen und zu viele Indizes führen zu einem Leistungsaufwand beim Einfügen und Aktualisieren Es ist klar, dass SQL-Anweisungen höchstens einen Index verwenden können. Wenn mehrere Indizes verwendet werden können, wählt das System einen Index zur Ausführung basierend auf den Ausführungskosten aus >

2) Für die Innodb-Tabelle generiert das System zwar automatisch eine Primärschlüsselspalte, wenn der Benutzer keinen Primärschlüssel angibt, die Automatisch generierte Primärschlüsselspalte weist mehrere Probleme auf 1. Unzureichende Leistung, Cache zum Lesen nicht möglich

Auto_Increment-Spalte. Daher muss für alle Tabellen in InnoDB der Primärschlüssel beim Erstellen der Tabelle angegeben werden. 3) Erstellen Sie keine Indizes für Felder, die nicht sehr eindeutig sind. 4) Sie müssen nur einen Index für ein Feld erstellen. Es ist nicht erforderlich, einen eindeutigen Index und einen INDEX

5) Für große Textfelder oder BLOB

6) Das Verbindungsfeld der Verbindungsabfrage sollte indiziert werden.

7) Das Sortierfeld muss im Allgemeinen indiziert werden ;

8) Gruppenstatistikfelder müssen im Allgemeinen indiziert werden

9) Verwenden Sie gemeinsame Indizes korrekt. Das erste Feld des gemeinsamen Indexes kann allein verwendet werden. Beispielsweise kann der folgende gemeinsame Index index(userID,dbInstanceID) die folgenden Abfrageanweisungen diesen Index verwenden, select dbInstanceIdentifier from DBInstance where userID=? , aber die Anweisung select dbInstanceIdentifier from DBInstance where dbInstanceID=? kann diesen Index nicht verwenden

Indizes werden im Allgemeinen für Tabellen mit vielen Datensätzen verwendet. Wenn eine Tabelle DBInstance vorhanden ist, verfügen alle Abfragen über das Bedingungsfeld userID. Derzeit ist bekannt, dass dieses Feld unterscheiden kann Datensätze sind sehr gut, das heißt, es gibt nicht viele Datensätze unter einer Benutzer-ID, sodass die Tabelle nur einen Index für Benutzer-ID erstellen muss. Da es für jede Benutzer-IDnicht viele entsprechende Datensatzdaten gibt, hat es im Grunde keine Auswirkungen, wenn andere Felder nicht indiziert werden. Gleichzeitig kann der Leistungsaufwand beim Einfügen und Aktualisieren durch die Einrichtung zu vieler Indizes verursacht werden vermieden;

. MySQLServerkonfigurationsoptimierung

1) Der MySQL -Server verfügt über ein langsames Verbindungsprotokoll, das Abfrageanweisungen aufzeichnen kann, die ein bestimmtes Zeitintervall überschreiten, und keine Indizes verwendet, um die Entwicklerverfolgung zu erleichtern. Aktivieren und deaktivieren Sie die Protokollfunktion für langsame Verbindungen, indem Sie slow_query_log=ON/ festlegen. OFF

slow_query_log_file legt den Dateinamen des langsamen Verbindungsprotokolls fest, long_query_time stellt das Timeout ein, die Einheit ist ms,Achten Sie auf die langsame Verbindung logMySQL ist standardmäßig deaktiviert

2) MySQL verfügt über eine Abfrage-Cache-Funktion. Der Server speichert Abfrageanweisungen und entsprechende Rückgabeergebnisse, um den durch dieselbe Abfrage verursachten Serveraufwand zu reduzieren. Sie können die Abfrage festlegen, indem Sie query_cache_size Die Größe des Caches, 0 bedeutet, dass der Abfrage-Cache deaktiviert wird. Es ist jedoch zu beachten, dass nach der Aktualisierung der Tabelle alle Abfrage-Caches standardmäßig ungültig sind, MySQL schaltet die Abfrage zwischengespeichert aus;

3) Sie können die maximale Anzahl von Verbindungen zur Datenbank festlegen, indem Sie max_connections, wait_timeout zum Festlegen der maximalen Anzahl von Verbindungen. Lange Aufbewahrungszeit, die Zeiteinheit beträgt s, MySQL ist standardmäßig 8 Stunden, sobald sie 8 überschreitet Stunden wird die Datenbank die Verbindung automatisch trennen. Dies muss bei der Verwendung des Datenbankverbindungspools beachtet werden, da die Verbindung im Verbindungspool zu diesem Zeitpunkt möglicherweise vom Server getrennt wurde Die Anwendung macht einen Fehler, wenn sie die Verbindung aus dem Verbindungspool erhält und verwendet. max_connect_errorsKonfigurieren Sie, dass die Hostverbindung zur Datenbank beendet wird, wenn die Anwendung auf mehrere Ausnahmen stößt

【Verwandte Empfehlungen】1.

2. Neuestes MySQL-Handbuch-Tutorial

3 .Dinge zum Datenbankdesign

Das obige ist der detaillierte Inhalt vonDie MySQL-Optimierung umfasst drei Aspekte. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!