Mit immer mehr datenbankgestützten Anwendungen bringen Menschen MySQL an seine Grenzen. Hier finden Sie 101 Tipps zum Optimieren und Optimieren Ihrer MySQLInstallation. Einige Tipps beziehen sich speziell auf eine bestimmte Installationsumgebung, die Ideen sind jedoch allgemeiner Natur. Ich habe sie in mehrere Kategorien unterteilt, um Ihnen zu helfen, mehr MySQL-Tuning- und -Optimierungskenntnisse zu erlernen
MySQL ist eine leistungsstarke Open-Source-Datenbank. Mit immer mehr datenbankgesteuerten Anwendungen bringen Menschen MySQL an seine Grenzen. Hier finden Sie 101 Tipps zum Optimieren und Optimieren Ihrer MySQL-Installation. Einige Tipps beziehen sich speziell auf eine bestimmte Installationsumgebung, die Ideen sind jedoch allgemeiner Natur. Ich habe sie in mehrere Kategorien unterteilt, um Ihnen dabei zu helfen, weitere MySQL-Tuning- und Optimierungstechniken zu erlernen.
Optimierung der MySQL-Server-Hardware und des Betriebssystems:
1. Genügend physischer Speicher, um die gesamte InnoDB-Datei in den Speicher zu laden – beim Zugriff auf die Datei im Speicher ist die Geschwindigkeit viel schneller als beim Zugriff auf die Festplatte.
2. Vermeiden Sie unbedingt die Verwendung von Swap – der Austausch erfolgt von der Festplatte und ist sehr langsam.
3. Verwenden Sie batteriebetriebenen RAM (Hinweis: RAM ist ein Arbeitsspeicher).
4. Verwenden Sie erweitertes RAID (Hinweis: Redundante Arrayss kostengünstiger Festplatten, d. h. Festplatten-Arrays) – vorzugsweise RAID10 oder höher.
5. Vermeiden Sie RAID5 (Hinweis: Eine Speicherlösung, die Speicherleistung, Datensicherheit und Speicherkosten in Einklang bringt) – die Überprüfung zur Gewährleistung der Datenbankintegrität ist mit einem Preis verbunden. 6. Trennen Sie die Betriebssystem- und Datenpartitionen nicht nur logisch, sondern auch physisch – die Lese- und Schreibvorgänge des Betriebssystems wirken sich auf die Leistung der Datenbank aus.
7. Platzieren Sie temporären MySQL-Speicherplatz sowie Replikationsprotokolle und -daten in verschiedenen Partitionen. Wenn der Datenbankhintergrund von der Festplatte liest und schreibt, wirkt sich dies auf die Leistung der Datenbank aus.
8. Mehr Speicherplatz bedeutet höhere Geschwindigkeiten.
9. Bessere und schnellere Festplatten.
10. Verwenden Sie SAS (Hinweis: Serial Attached SCSI, Serial Attached SCSI) anstelle von SATA (Hinweis: SATA, Serial Hard Drive).
11. Kleinere Festplatten sind schneller als größere Festplatten, insbesondere in RAID-Konfigurationen.
12. Verwenden Sie einen batteriegepufferten Hochgeschwindigkeits-
RAIDController. 13. Vermeiden Sie die Verwendung von Software-Festplatten-Arrays. 14. Erwägen Sie die Verwendung von Solid-State-IO-Karten (keine Festplattenlaufwerke) für Datenpartitionen – diese Karten unterstützen Schreibgeschwindigkeiten von 2 GB/s für nahezu jede Datenmenge.
15. Setzen Sie den Swappiness-Wert unter Linux auf 0 – Es gibt keinen Grund, Dateien auf dem Datenbankserver zwischenzuspeichern, was für einen Server oder Desktop von Vorteil ist.
16. Verwenden Sie nach Möglichkeit noatime und nodirtime, um das Dateisystem zu mounten – es gibt keinen Grund, die Änderungszeit der Datenbankdateien, auf die zugegriffen wird, zu aktualisieren.
17. Verwenden Sie das XFS-Dateisystem – ein Dateisystem, das schneller und kleiner als ext3 ist und über viele Protokollierungsoptionen verfügt. Es hat sich gezeigt, dass ext3 Probleme mit der doppelten Pufferung mit MySQL hat.
18. Passen Sie die Protokoll- und Puffervariablen des XFS-Dateisystems an – für höchste Leistungsstandards.
19. Verwenden Sie in Linux-Systemen den geplanten Planer NOOP oder DEADLINE IO – Im Vergleich zu den geplanten Planern NOOP und DEADLINE sind die geplanten Planer CFQ und ANTICIPATORY sehr langsam.
20. Verwenden Sie ein 64-Bit-Betriebssystem – Für MySQL gibt es eine größere Speicherunterstützung und -nutzung.
21. Löschen Sie nicht verwendete Installationspakete und Daemons auf dem Server – weniger Ressourcenverbrauch.
22. Fügen Sie den Host, der MySQL verwendet, und Ihren MySQL-Host in eine Hosts-Datei ein – keine DNS-Suche.
23. Erzwingen Sie niemals das Beenden eines MySQL-Prozesses – Sie beschädigen die Datenbank und das Programm, das die Sicherung ausführt.
24. Tragen Sie den Server zu MySQL bei – Hintergrundprozesse und andere Dienste können die von der Datenbank belegte CPU-Zeit verkürzen.
MySQL-Konfiguration:
25. Verwenden Sie beim Schreiben innodb_flush_method=O_DIRECT, um doppelte Pufferung zu vermeiden.
26. Vermeiden Sie die Verwendung von O_DIRECT- und EXT3-Dateisystemen – Sie werden alles, was Sie schreiben, serialisieren.
27. Weisen Sie genügend innodb_buffer_pool_size zu, um die gesamte InnoDB-Datei in den Speicher zu laden – weniger Lesevorgänge von der Festplatte.
28. Stellen Sie den Parameter innodb_log_file_size nicht zu groß ein, damit Sie schneller sind und mehr Speicherplatz haben. Das Löschen weiterer Protokolle ist normalerweise sinnvoll und kann die Zeit für die Wiederherstellung der Datenbank nach einem Datenbankabsturz verkürzen.
29. Mischen Sie nicht die Parameter innodb_thread_concurrency und thread_concurrency – diese beiden Werte sind inkompatibel.
30. Weisen Sie dem Parameter max_connections eine sehr kleine Zahl zu – zu viele Verbindungen verbrauchen RAM und blockieren den MySQL-Dienst.
31. Halten Sie thread_cache auf einem relativ hohen Wert, etwa 16 – um Langsamkeit beim Öffnen von Verbindungen zu verhindern.
32. Verwenden Sie den Skip-Name-Resolve-Parameter – entfernen Sie die DNS-Suche.
33. Wenn Ihre Abfragen wiederholt werden und sich die Daten nicht oft ändern, können Sie Abfrage-Caching verwenden. Wenn sich Ihre Daten jedoch häufig ändern, ist die Verwendung des Abfragecaches ein Nachteil.
34. Erhöhen Sie den Wert „temp_table_size“, um das Schreiben auf die Festplatte zu verhindern.
35. Stellen Sie den Wert „sort_buffer_size“ nicht zu hoch ein, da sonst Ihr Speicher beschädigt wird bald erschöpft sein
37. Bestimmen Sie die Größe von key_buffer basierend auf den Werten key_read_requests und key_reads, sonst können Sie key_buffer nicht effizient nutzen
38. Wenn Sie jedoch den Standardwert (1) beibehalten möchten, müssen Sie die Datenintegrität sicherstellen und außerdem sicherstellen, dass die Replikation nicht verzögert wird.
39. Sie benötigen eine Testumgebung, um Ihre Konfiguration zu testen und sie regelmäßig neu zu starten, ohne die normale Produktion zu beeinträchtigen.
MySQL-Modusoptimierung:
40. 41. Archivierung alter Daten – überflüssige Zeilenrückgaben oder
Suchanfragen löschen. 42. Indizieren Sie Ihre Daten
.43. Verwenden Sie nicht zu viele Indizes, Vergleiche und Abfragen.
44. Komprimieren Sie Text und BLOB
– Zum Speichern Speicherplatz und reduzieren Sie die Anzahl der Festplattenlesevorgänge.45. Sowohl UTF 8 als auch UTF16 haben eine geringere Ausführungseffizienz als Latin1.46. Redundante Daten werden auf ein Minimum beschränkt – keine unnötige Duplizierung von Daten.
48. Verwenden Sie verknüpfte Tabellen, keine erweiterten Zeilen.
49. Achten Sie auf die kleinstmöglichen Datentypen in Ihren realen Daten.50 Wird häufig in Abfragen verwendet, BLOB/TEXT-Daten jedoch nicht. Trennen Sie BLOB/TEXT-Daten von anderen Daten.51. Überprüfen und optimieren Sie Tabellen häufig.
52. Schreiben Sie die InnoDB-Tabellenoptimierung häufig neu.
53 , löschen Sie den Index, wenn Sie Spalten hinzufügen, und fügen Sie ihn dann wieder hinzu, was schneller ist.
54. Verwenden Sie verschiedene Speicher-Engines für unterschiedliche Anforderungen.
55. Verwenden Sie Protokolltabellen oder Prüftabellen für Archivspeicher-Engines effizienter beim Schreiben.
56. Sitzungsdaten werden im Cache (Memcache) statt in MySQL gespeichert – der Cache ermöglicht das automatische Ausfüllen von Werten und verhindert, dass Sie räumlich-zeitliche Daten erstellen, die schwer in MySQL zu lesen und zu schreiben sind .
57. Verwenden Sie VARCHAR anstelle von CHAR, wenn Sie Zeichenfolgen variabler Länge speichern
– spart Platz, da die VARCHAR-Länge nicht festgelegt ist (UTF8 ist davon nicht betroffen).
Schemaänderungen inkrementell – eine kleine Änderung kann große Auswirkungen haben.
59. Testen Sie alle Schemata in einer Entwicklungsumgebung, berücksichtigen Sie Produktionsänderungen.
60 Ändern Sie keine Werte in Ihren
Konfigurationsdateien Zufälligerweise kann es katastrophale Auswirkungen haben.61 Manchmal ist weniger mehr in MySQL-Konfigurationen
Abfrageoptimierung:
63. Verwenden Sie langsame Abfrageprotokolle, um langsame Abfragen zu erkennen.
64. Verwenden Sie den Ausführungsplan, um festzustellen, ob die Abfrage normal ausgeführt wird.
65. Testen Sie Ihre Abfragen immer, um zu sehen, ob sie optimal funktionieren – die Leistung wird sich im Laufe der Zeit immer ändern.
66. Vermeiden Sie die Verwendung von count(*) für die gesamte Tabelle, da sonst die gesamte Tabelle gesperrt wird.
67. Halten Sie Abfragen konsistent, damit nachfolgende ähnliche Abfragen den Abfragecache verwenden können.
68. Verwenden Sie gegebenenfalls GROUP BY anstelle von DISTINCT.
69. Verwenden Sie indizierte Spalten in WHERE-, GROUP BY- und ORDER BY-Klauseln.
70. Halten Sie Indizes einfach und schließen Sie nicht dieselbe Spalte in mehrere Indizes ein.
71. Manchmal verwendet MySQL den falschen Index. Verwenden Sie in diesem Fall USE INDEX.
72. Überprüfen Sie das Problem bei der Verwendung von SQL_MODE=STRICT.
73. Bei Indexfeldern mit weniger als 5 Datensätzen ist die Verwendung von LIMIT in UNION kein OR.
74 Um SELECT vor der Aktualisierung zu vermeiden, verwenden Sie INSERT ON DUPLICATE KEY oder INSERT IGNORE, verwenden Sie nicht UPDATE.
75. Verwenden Sie nicht MAX, verwenden Sie Indexfelder und die ORDER BY-Klausel.
76. Vermeiden Sie die Verwendung von ORDER BY RAND().
77 LIMIT M, N kann Abfragen in einigen Fällen tatsächlich verlangsamen, verwenden Sie es sparsam.
78. Verwenden Sie UNION anstelle der Unterabfrage in der WHERE-Klausel.
79. Verwenden Sie für UPDATES den SHARE-MODUS, um exklusive Sperren zu verhindern.
80. Denken Sie vor dem Neustart von MySQL daran, Ihre Datenbank aufzuwärmen, um sicherzustellen, dass sich Ihre Daten im Speicher befinden und Abfragen schnell erfolgen.
81. Verwenden Sie DROP TABLE, CREATE TABLE DELETE FROM, um alle Daten aus der Tabelle zu löschen.
82. Wenn Sie die benötigten Daten mit minimierten Daten abfragen, nimmt die Verwendung von * viel Zeit in Anspruch.
83. Erwägen Sie dauerhafte Verbindungen anstelle mehrerer Verbindungen, um den Overhead zu reduzieren.
84. Benchmark-Abfragen, einschließlich der Auslastung des Servers, manchmal kann eine einfache Abfrage andere Abfragen beeinflussen.
85. Wenn die Auslastung Ihres Servers zunimmt, verwenden Sie SHOW PROCESSLIST, um langsame und problematische Abfragen anzuzeigen.
86. Testen Sie alle verdächtigen Abfragen der in der Entwicklungsumgebung generierten Bilddaten.
MySQL-Sicherungsvorgang:
87. Sicherung vom sekundären Replikationsserver.
88. Stoppen Sie die Replikation während der Sicherungen, um Inkonsistenzen bei Datenabhängigkeiten und Fremdschlüsseleinschränkungen zu vermeiden. 89. Stoppen Sie MySQL vollständig und erstellen Sie eine Sicherungskopie der Datenbankdatei.
90. Wenn Sie MySQL-Dump zur Sicherung verwenden, sichern Sie bitte auch die binären Protokolldateien – stellen Sie sicher, dass es bei der Replikation zu keiner Unterbrechung kommt.
91. Vertrauen Sie LVM-Snapshots nicht – dies führt wahrscheinlich zu Dateninkonsistenzen, die Ihnen in Zukunft Probleme bereiten werden.
92. Um die Wiederherstellung einzelner Tabellen zu vereinfachen, exportieren Sie Daten in Tabelleneinheiten – wenn die Daten von anderen Tabellen isoliert sind.
93. Bitte verwenden Sie –opt, wenn Sie mysqldump verwenden.
94. Überprüfen und optimieren Sie Tabellen vor dem Sichern.
95. Für einen schnelleren Import werden Fremdschlüsseleinschränkungen während des Imports vorübergehend deaktiviert.
96. Für einen schnelleren Import ist die Eindeutigkeitserkennung während des Imports vorübergehend deaktiviert.
97. Berechnen Sie die Größe der Datenbank, Tabellen und Indizes nach jeder Sicherung, um das Wachstum der Datengröße besser überwachen zu können.
98. Überwachen Sie Replikationsinstanzen mithilfe automatisierter Planungsskripte auf Fehler und Latenz.
99. Führen Sie regelmäßige Backups durch.
100. Testen Sie Ihre Backups regelmäßig.
The Last 101: MySQL-Überwachung durchführen: Monitis stellt die weltweit erste kostenlose On-Demand-MySQL-Überwachung vor.
Das obige ist der detaillierte Inhalt vonVermittlung von 101 Debugging- und Optimierungsfähigkeiten in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
