Tipps zur MySQL-Leistungsoptimierung

MySQL-Leistungsoptimierung

MySQL wird in Internetunternehmen häufig verwendet und es besteht häufig Bedarf an einer MySQL-Leistungsoptimierung. Ich habe einige praktische Tipps zur MySQL-Optimierung zusammengestellt.

Schema- und Datentypoptimierung

1. Für Identitätsspalten sind Ganzzahlen normalerweise die beste Wahl, da sie schnell sind und AUTO_INCREMENT verwenden können.
2. Völlig „zufällige“ Zeichenfolgen (wie sie beispielsweise von MD5() , SHA1() oder UUID() generiert werden) werden zufällig in einem großen Bereich verteilt, wodurch INSERT und einige SELECT Anweisungen sehr langsam werden.
3. Wenn Sie möchten, dass die Abfrage schnell und mit guter Parallelität ausgeführt wird, sollten Sie nicht zu viele verknüpfte Abfragen in einer einzigen Abfrage ausführen (Internetunternehmen sind verknüpften Abfragen gegenüber sehr abgeneigt) und Programme verwenden, um die verknüpften Vorgänge abzuschließen.
4. Wenn Sie die Tabellenstruktur einer relativ großen Tabelle ändern müssen (eine Spalte im Vorgang ALTER TABLE hinzufügen), wird empfohlen, zuerst eine Tabelle mit derselben Struktur wie die Originaltabelle zu kopieren, dann die Daten hineinzukopieren und die neue Tabelle schließlich in den Namen der Originaltabelle umzubenennen. Denn beim Ändern der Tabellenstruktur besteht eine große Wahrscheinlichkeit, dass die gesamte Tabelle gesperrt wird und möglicherweise für längere Zeit nicht verfügbar ist.
5. Um Mehrfachtabellenzuordnungen zu vermeiden, können Sie einige Anti-Paradigmen-Lösungen zum Erstellen von Tabellen in Betracht ziehen und einige redundante Felder hinzufügen.

InnoDB-Indexoptimierung

1. Wenn Sie die Suche nicht in der äußersten linken Spalte des Index beginnen, können Sie den Index nicht verwenden.
2. Alle nicht gruppierten Indizes müssen zuerst den entsprechenden Primärschlüssel über den Index lokalisieren und dann die Daten im gruppierten Index finden. Daher müssen Sie beim Definieren des Primärschlüsselindex vorsichtig sein.
3. MySQL kann den Index nur dann zum Sortieren der Ergebnisse verwenden, wenn die Reihenfolge der Indexspalten genau mit der Reihenfolge ORDER BY Klausel übereinstimmt und die Sortierrichtung (rückwärts oder vorwärts) aller Spalten gleich ist. Es gibt einen Fall, in dem ORDER BY Klausel die Anforderung des äußersten linken Präfixes des Index nicht erfüllen muss, nämlich wenn die führende Spalte eine Konstante ist.
4. Wenn Sie like verwenden, um den Wert eines Zeichenfolgenfelds abzugleichen, versuchen Sie, Präfixübereinstimmungen like 'XX%' zu verwenden und vermeiden Sie die Verwendung like '%XX'
5. Hash-Indizes werden basierend auf Hash-Tabellen implementiert. Nur Abfragen, die genau mit allen Spalten des Index übereinstimmen, sind gültig und sie folgen nicht dem am weitesten links stehenden Übereinstimmungsprinzip des Index.
6. Wenn der Server eine gemeinsame Operation an mehreren Indizes durchführen muss (normalerweise mit mehreren OR Bedingungen), wird empfohlen, in UNION Modus zu wechseln, der das Erreichen des Indexes erleichtert.
7. Für die Auswahl der Spaltenreihenfolge im Index gibt es eine Faustregel: Platzieren Sie die selektivsten Spalten zuerst im Index.
8. Verwenden Sie so oft wie möglich abdeckende Indizes (wenn ein Index die Werte aller abzufragenden Felder enthält oder abdeckt, nennen wir ihn einen abdeckenden Index). Sie können die Informationen "Index verwenden" in der Spalte Extra von EXPLAIN sehen.
9. Wenn ID der Primärschlüssel ist, ist das Erstellen des Index (A) gleichbedeutend mit dem Erstellen zweier Indizes (A) und (A, ID).
10. Je mehr Indizes eine Tabelle hat, desto langsamer sind SELECT , UPDATE und DELETE -Operationen und desto mehr Speicher wird benötigt.
11. InnoDB verwendet gemeinsame Sperren für Sekundärindizes, für den Zugriff auf den Primärschlüsselindex ist jedoch eine exklusive Sperre erforderlich.
12. Verwenden Sie WHERE IN und WHERE BETWEEN AND um nach Möglichkeit Bereichsabfragen durchzuführen.
13. Je größer der LIMIT Offset, desto langsamer die Leistung.
14. Vermeiden Sie beim Schreiben von Abfragen die Suche nach einzelnen Zeilen, verwenden Sie möglichst die native Reihenfolge der Daten, um zusätzliche Sortiervorgänge zu vermeiden, und verwenden Sie möglichst Indexabdeckungsabfragen.

Optimierung der Abfrageleistung

1. Bei ineffizienten Abfragen analysieren wir sie normalerweise unter zwei Gesichtspunkten:

1. Stellen Sie fest, ob die Anwendung größere Datenmengen abruft als nötig. Dies bedeutet normalerweise, dass auf zu viele Zeilen zugegriffen wurde. Manchmal kann es aber auch sein, dass auf zu viele Spalten zugegriffen wurde.
2. Überprüfen Sie, ob der MySQL-Server mehr Zeilen als nötig analysiert
   

2. Im Allgemeinen kann MySQL die folgenden drei Methoden verwenden, um WHERE-Bedingungen anzuwenden, von der besten bis zur schlechtesten:

• Verwenden Sie die WHERE-Bedingung im Index, um nicht übereinstimmende Datensätze herauszufiltern
• Verwenden Sie einen Indexabdeckungsscan, um Datensätze zurückzugeben
• Daten aus der Datentabelle zurückgeben und dann Datensätze herausfiltern, die die Bedingungen nicht erfüllen
  

3.MySQL ist so konzipiert, dass das Herstellen und Trennen von Verbindungen einfach und effizient erfolgt und kleine Abfrageergebnisse zurückgegeben werden. Auf einem Allzweckserver können mehr als 100.000 Abfragen pro Sekunde ausgeführt werden. Eine Gigabit-Netzwerkkarte kann problemlos mehr als 2.000 Abfragen pro Sekunde verarbeiten. MySQL kann pro Sekunde Millionen von Datenzeilen im Speicher scannen.

4. Beim Löschen einer großen Datenmenge empfiehlt es sich, nach dem Löschen einer kleinen Datenmenge eine Weile zu warten, bevor Sie die nächste Menge löschen.

5. Das Sortieren ist ohnehin ein sehr kostenintensiver Vorgang. Aus Leistungsgründen sollten Sie das Sortieren daher möglichst vermeiden bzw. das Sortieren großer Datenmengen möglichst vermeiden.

6. Die Funktion COUNT() hat zwei verschiedene Funktionen: Sie kann die Anzahl der Werte in einer Spalte oder die Anzahl der Zeilen zählen. Der einfachste Weg ist, die Anzahl der Zeilen mit COUNT(*) zu zählen.

7. Stellen Sie bei einer verwandten Abfrage sicher, dass ein Index für das verwandte Feld vorhanden ist

8. Wenn die Datenmenge groß ist und historische Daten regelmäßig gelöscht werden müssen, können Sie die Verwendung einer Partitionstabelle in Betracht ziehen

9. Wenn die angegebene Indexspalte und die Partitionsspalte nicht übereinstimmen, kann die Abfrage keine Partitionsfilterung durchführen

10. Vermeiden Sie Fremdschlüsseleinschränkungen so weit wie möglich, die normalerweise durch Programmierung implementiert werden, und denken Sie an Fremdschlüssel

11. Es ist am besten, keine Trigger, gespeicherten Prozeduren, benutzerdefinierten Funktionen usw. zu verwenden.

12. Verwenden Sie den Abfragecache so oft wie möglich. Wenn beim Schreiben der Abfrageanweisung unsichere Daten vorhanden sind (z. B. NOW () oder CURRENT_DATE ()), werden diese nicht zwischengespeichert.

13. Die Verwendung mehrerer kleiner Tabellen anstelle einer großen Tabelle ist gut für das Abfrage-Caching

14. Beim Schreiben in Stapeln ist nur eine Cache-Ungültigmachung erforderlich, daher ist dies effizienter als einzelne Schreibvorgänge (der Cache wird bei jedem Schreibvorgang ungültig gemacht). Deaktivieren Sie bei schreibintensiven Anwendungen den Abfrage-Cache direkt.

15. Wenn der Cache-Speicherplatz zu groß ist, kann der Server während des Ablaufvorgangs einfrieren

Das Obige ist eine Zusammenfassung meiner persönlichen Erfahrungen bei der Arbeit. Wenn die Beschreibung Fehler enthält, hoffe ich, dass Sie mir dabei helfen können, sie aufzuzeigen, damit wir kommunizieren und gemeinsam lernen können!

Dies ist das Ende dieses Artikels über Tipps zur Leistungsoptimierung von MySQL. Weitere relevante Inhalte zur Leistungsoptimierung von MySQL finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den verwandten Artikeln weiter unten. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!