Überlegungen zur MySQL-Optimierung

Beim Betrieb der Datenbank müssen wir MySQL optimieren. In diesem Artikel geht es um Optimierungsvorkehrungen.

Der erste Punkt ist, dass die Hardware zu alt ist
Wir werden hier hauptsächlich über die Hardware unter den drei Aspekten CPU, Speicher und Festplatte sprechen. Es gibt auch einige Faktoren wie Netzwerkkarten, Computerraumnetzwerke usw. usw. Aufgrund der Länge des Artikels werden wir sie nicht einzeln vorstellen. Ja, es wird in Zukunft eine weitere Gelegenheit zum Chatten geben.
Werfen wir zunächst einen Blick auf die CPU-Auslastungseigenschaften von MySQL:
5.1 kann 4 Kerne nutzen, 5.5 kann 24 Kerne nutzen und 5.6 kann 64 Kerne nutzen
Beispielsweise kann MySQL5.6 If verwenden mehr als 48 Kerne und läuft gut, 64 Kerne können verwendet werden (zwischen 48 Kernen und 64 Kernen, die offizielle Ankündigung ist 48 Kerne, und in meinem tatsächlichen Test können 64 Kerne erreicht werden).
MySQL 5.6 kann 48 Kerne+ verwenden
* Vor MySQL 5.1 können bis zu 4 Kerne verwendet werden **
Heutzutage sind die allgemeinen Produktionsumgebungsserver 32Kerne oder höher.
Daher empfehle ich jedem hier, so oft wie möglich MySQL5.5 oder MySQL5.6 zu verwenden, es sei denn, der Server Ihres Unternehmens verwendet einen sehr alten Server mit nur 4 Kernen oder 1 Kern.
Da es vor 5.1 (dasselbe wie 5.0) im internen Code fest codiert war und auf der Innobase-Speicher-Engine basierte, hatte die Datenbank eine schlechte Hardwareauslastung. Nach der Weiterentwicklung zur InnoDB-Engine wurde es viel besser.
Jede Verbindung ist ein Thread (kein Thread-Pool) und jede Abfrage kann nur einen Kern verwenden.
Außerdem kann in MySQL jede Abfrage nur eine CPU verwenden.
Oracle verwendet paralleles SQL und parallele Abfragen. Diese Art von Funktion gibt es in MySQL nicht.
Kein Ausführungsplan-Cache (keine SQL-Ausführungsplan-Vorkompilierung)
Zweitens gibt es in MySQL keine SQL-Vorkompilierung. Daher gibt es in der Speicherstruktur von Oracle keine Struktur wie den Bibliothekscache. Daher verfügt MySQL nur über Hard-Parsing, es gibt kein Soft-Parsing, geschweige denn Soft-Parsing.
MySQL wird mit zunehmender Anzahl von Verbindungen einen Leistungsabfall erfahren
Dies ist ebenfalls ein Fehler von MySQL, aber mit der Weiterentwicklung der MySQL-Versionen sind viele Lösungen entstanden.
Zum Beispiel: der offiziell gestartete Thread-Pool, der als TP bezeichnet wird. Es soll das Problem einer zu hohen Anzahl gleichzeitiger Verbindungen lösen. Dies ist jedoch eine zusätzliche Komponente von MySQL, und für den Kauf des offiziellen TP ist zusätzliches Geld erforderlich.
Darüber hinaus gibt es in China eine Person namens Lou Fangxin, die eine OneSQL-Middleware entwickelt hat, um ähnliche Probleme zu lösen.
Es gibt einen Ergebniscache, der jedoch nutzlos ist.
MySQL verfügt auch über einen Ergebniscache ähnlich dem in Oracle, der als Abfragecache bezeichnet wird, aber eine relativ nutzlose Funktion ist und selten verwendet wird.
Da es sich bei den meisten tatsächlichen Produktionsumgebungen um OLTP-Systeme mit häufigen Aktualisierungs- und Änderungsvorgängen handelt, beeinträchtigt dieser Abfragecache die Leistung von MySQL erheblich, wenn er in einer Umgebung verwendet wird, in der Daten häufig aktualisiert und geändert werden. Daher wird er im Allgemeinen selten verwendet .
Da MySQL verwendet wird, wird grundsätzlich die InnoDB-Speicher-Engine verwendet. Die vorherigen MyISAM-Engines werden selten verwendet. (Was ist eine Speicher-Engine? Wenn Sie das nicht wissen, können Sie gg)
Der Abfrage-Cache in der InnoDB-Engine muss nicht aktiviert werden, da es sich um eine transaktionale Speicher-Engine handelt und die Verwendung von InnoDB dies erfordert Wenn Sie die Fähigkeit zur Transaktionsverarbeitung nutzen, werden auf jeden Fall häufige Datenaktualisierungen und -änderungen auftreten.
Sehen wir uns noch einmal die Speichernutzungseigenschaften von MySQL an
Der Server mit einem 64-Bit-Betriebssystem kann Speicher ((2^64-1)/1024/1024/1024)G verwenden
In einem hohen Maße In einer gleichzeitigen Umgebung mit hoher Geschwindigkeit wird grundsätzlich auf Speicher-Caching zurückgegriffen, um die E/A-Auswirkungen auf die Festplatte zu reduzieren.
Normalerweise wird der Speicher entsprechend 15 % bis 20 % der tatsächlichen Daten geplant, wenn die Daten besonders heiß sind, ein größerer Anteil muss berücksichtigt werden, um die Daten zwischenzuspeichern
Diese 15%-20% Die Daten werden normalerweise als Hot Data bezeichnet. (Dies ist auch ein allgemeiner Erfahrungswert)
Wenn Sie beispielsweise schätzen, dass das Gesamtdatenvolumen Ihres MySQL etwa 500 G beträgt, dann kann der von MySQL bereitgestellte Speicher 75 G (5000,15) betragen, dann benötigen Sie möglicherweise eine 128 G-Maschine Speicherserver.
Darüber hinaus verfügen einige Unternehmen über besonders heiße und große Mengen an heißen Daten (es ist möglich, dass der Bereich von 15 % bis 20 % deutlich überschritten wird), wie beispielsweise QQ Farm.
Ich glaube, jeder hat schon einmal Spiele zum Essensdiebstahl gespielt, wie zum Beispiel QQ Farm, Happy Farm und dergleichen. (Es gibt auch eine 12306-Website zum Buchen von Tickets).
Diese Art von Geschäft ist in unserer Branche von großer Bedeutung, da es sich im Grunde genommen um 100 % heiße Daten handelt: Wenn jeder QQ Farm spielt, wird er angezeigt Jeden Tag kamen sie zum Spaß vorbei und stahlen ab und zu eine Handvoll Gemüse, wenn sie mitten in der Nacht aufstanden, um auf die Toilette zu gehen.
Daher muss die Speicherkonfiguration der MySQL-Datenbank für diese Art von Unternehmen erhöht werden. 15-20 % reichen nicht aus.
Zusammenfassung: ****Allgemeines Geschäft 15 %–20 % werden zur Planung wichtiger Daten verwendet, z. B.: Benutzercenter, Bestellungen und andere allgemeine Geschäfte. Für einige andere Spezialgeschäfte muss die spezifische Situation im Detail analysiert werden.
Die Zuweisung von Leitlinien kann auf der Grundlage der Antwortzeit der Abfrage erfolgen.
Bei der Planung und Gestaltung dieser umfangreichen Online-Architektur mit großer Datenbank ist
die Antwortzeit der SQL-Abfrage ebenfalls ein sehr wichtiger Indikator.
In einem so großen System müssen Millionen oder sogar Dutzende Millionen Benutzer gleichzeitig online sein. Die Antwortzeit der SQL-Abfrage (Abfrage) muss streng kontrolliert werden muss innerhalb einer bestimmten Zeitspanne unter Kontrolle sein.
Für unsere Kernbibliothek benötige ich beispielsweise, dass die Antwortzeit (durchschnittliche Antwort) von Query unter 30 ms liegt. Wenn es 30 ms überschreitet, gehen wir davon aus, dass die Datenbank möglicherweise ihre Auslastungsgrenze erreicht hat und die Datenbank erweitert werden muss.
Darüber hinaus ist eine langfristige Indikatorüberwachung dieser Abfrageantwortzeit erforderlich.
Dies ist die Kernbibliothek, wenn es andere, weniger wichtige Hilfsbibliotheken gibt, z. B. Bibliotheken, die Protokolle speichern, oder einige Bibliotheken, deren Leistungsanforderungen nicht zu hoch sind, können wir die Abfrageantwortzeit auf innerhalb von 1 Sekunde oder 2 Sekunden reduzieren.
Bestimmen Sie den Schwellenwert dieser Abfrageantwortzeit basierend auf der Bedeutung des Unternehmens.
Dies ist ein sehr wichtiges Leitprinzip: Planen Sie Ihre Leistungskapazität basierend auf der Antwortzeit der Abfrage.
Es gibt zwei Arten von Kapazität: Leistungskapazität und Platzkapazität. Die Speicherplatzkapazität ist sehr einfach, das heißt, wie viele SIZE-Daten platziert werden und wie viele T.
Leistungsfähigkeit ist wichtiger und bestimmt, ob sie Ihrem geschäftlichen Druck und Ihrer Belastung gewachsen ist.
Jeder sollte bedenken: Wenn das Unternehmen, mit dem Sie zusammenarbeiten möchten, Millionen aktiver Benutzer und nicht Hunderte von Benutzern umfasst, ist die Leistung entscheidend und die Erfüllung der Anforderungen des Unternehmens ist das Wichtigste.
Egal wie großartig Ihre Funktionen sind, egal wie gut Ihr Produkt ist, alles andere ist Unsinn. Hunderttausende Menschen können in wenigen Sekunden Ihr gesamtes System und Ihr Projekt lahmlegen Das Unternehmen wird geblendet.
Die Benutzer, die so hart gearbeitet haben, werden ebenfalls in großer Zahl verloren gehen, und die Verluste werden hoch sein.
Leistung ist das Fundament. Die gesamte Architektur macht nur dann Sinn, wenn die Leistung dem standhält. Wenn die Leistung nicht zufriedenstellend ist, ist es sinnlos, später über Hochverfügbarkeit nachzudenken.
Merkmale der Festplattenauslastung von MySQL
Binlog, Redo-Log, Undo-Log sequentieller E/A
MySQL verfügt über verschiedene E/A-Typen.
Binlog, Redolog, Undolog, das sind sequentielle E/A-Schreibvorgänge.
Sequentielles Schreiben auf herkömmliche mechanische Festplatten ist auch sehr schnell. Darüber hinaus gibt es bei SSDs Probleme mit dem Schreibverlust und der Schreiblebensdauer Es ist nicht erforderlich, es auf einer SSD zu speichern. Es reicht aus, es auf eine herkömmliche SAS-Festplatte zu legen. Es ist nicht erforderlich, eine SSD einzubauen.
SSD wird zum Speichern von Datendateien verwendet. Da die meisten E/A-Vorgänge in der Datendatei zufällige E/A-Vorgänge sind, ist es für SSD sehr vorteilhaft, zufällige E/A-Vorgänge auszuführen. SSD-Solid-State-Festplatte und herkömmliche SAS-Festplatte werden zur Speicherung gemischt. Darüber hinaus sollten Sie keine SSDs für Backup-Festplatten verwenden.
Zufällige Datendatei-E/A und sequentielle E/A kombiniert
Sequentielle E/A ist immer schneller. Was beim Datenbankdesign darüber entscheidet, ob Sie ein großartiger DBA oder ein großartiger Architekt sind, hängt davon ab, ob Sie ein Unternehmen so weit wie möglich als sequenzielles IO entwerfen und gleichzeitig zufälliges IO reduzieren können. Zum Beispiel: Wenn ich ein Freundschaftsbeziehungsgeschäft entwerfe, hoffe ich, dass eine Abfrage die Freundschaftsbeziehung durch sequentielle E/A herausnehmen kann. Wie entwirft man sie?
In MySQLs InnoDB können wir eine Funktion von InnoDB nutzen: Clustered-Index-Tabellen. (Ähnlich wie Oracles IOT).
Mit dieser Funktion können die Freundesdaten des Benutzers so weit wie möglich auf einer Seite oder auf mehreren angrenzenden Seiten gesammelt werden. Beim Lesen kann eine sequentielle Lese-E/A durchgeführt werden, wodurch die Leistung erheblich verbessert wird.
Die Struktur der Freundesbeziehungstabelle ist wie folgt (die Voraussetzungstabelle ist die InnoDB-Engine):
owner_id freund_id (Freund-ID)
Die beiden oben genannten Felder werden als Primärschlüssel verwendet InnoDB ist der Clustered-Index. Dann kann IO die Felder in einer bestimmten Reihenfolge verarbeiten.
In allen Datenbankdesignbüchern wurde in der Vergangenheit immer erwähnt, dass jede Tabelle eine Spezifikation für einen automatisch inkrementierenden Primärschlüssel hinzufügen muss. Tatsächlich ist die Spezifikation tot und die Antwort ist lebendig Das obige Beispiel ist nutzlos. Anstatt einen zusätzlichen Primärschlüssel hinzuzufügen, werden zwei Geschäftsfelder, die über Geschäftsattribute verfügen und häufig gelesen werden, als Primärschlüssel verwendet, was zu einer besseren Leistung führt.
Merken Sie sich daher beim Lernen nicht die Normen und Vorschriften in diesen Büchern. Stattdessen sollten Sie die Prinzipien von etwas wirklich verstehen, z. B. das Erlernen der internen Prinzipien von InnoDB, und diese dann in der tatsächlichen Arbeit anwenden der Prinzipien, Verwendung Das Prinzip besteht darin, Rückschlüsse von einem Fall auf andere Fälle zu ziehen.
Die Prinzipien von InnoDB sind ein riesiges Stück Wissen und erfordern ein Lernen im Laufe der Zeit. Sie können meinem offiziellen Konto mehr Aufmerksamkeit schenken und einige Artikel über InnoDB werden nacheinander veröffentlicht.
OLTP-Geschäft erfordert mehr zufällige E/A
Sie können Speicher zum Caching verwenden und dadurch zufällige E/A reduzieren.
OLAP-Geschäft erfordert mehr sequenzielle E/A.
Speicher-Caching ist nicht sehr nützlich.
Vor MySQL 5.6 war es das Seitenänderungen werden nicht unterstützt und der Standardwert ist 16 KB.
MySQL5.6 kann nach MySQL5.6 geändert werden. Dieser Parameter ist innodb_page_size, aber MySQL5.6 kann nur auf 8K oder 4K geändert werden und kann nicht auf 32K oder 64K erhöht werden, bis MySQL5.7 oder höher ist .
Bei OLAP-Systemen tragen größere Seiten zur Verbesserung der Leistung bei, da OLAP-Systeme relativ große Abfragen haben und viele Daten scannen.
Zweiter Punkt: Das Datenbankdesign ist nicht gut
Zum Beispiel werden viele Datenbankfunktionen verwendet, wie Trigger, Partitionen, viele gespeicherte Prozeduren, Funktionen usw.
Wir sagen oft: „Klein ist schön“, was bedeutet, dass Einfachheit das Beste ist. Wenn Sie alle Funktionen der Datenbank nutzen, wird die Leistung der Datenbank natürlich verlangsamt und die Wahrscheinlichkeit möglicher Fehler und zugrunde liegender Ausfälle steigt.
Daher muss jeder verstehen, dass ein gutes Datenbankprojektdesign klein, schön, prägnant und prägnant ist. Darüber hinaus ist die Datenbank nur ein Teil des Gesamtprojekts. Dinge wie Trigger und gespeicherte Prozeduren können durchaus über Anwendungscode im Gesamtprojekt implementiert werden.
Wenn wir also MySQL verwenden, nutzen wir nur seine leistungsstarken Funktionen wie Tabellen, Indizes und Transaktionen, anstatt alle seine Funktionen zu nutzen.
Ein weiterer Punkt ist, dass vor MySQL 5.6 Unterabfragen in der Hauptdatenbank der Produktionsumgebung nicht zulässig waren.
Die Leistung von Unterabfragen vor MySQL 5.6 war besonders schlecht. (Syntax wird unterstützt, aber die SQL-Leistung ist sehr schlecht).
Wenn Sie beispielsweise jetzt Oracle verwenden und Oracle auf MySQL migrieren möchten, wird die Verwendung der MySQL 5.6-Version empfohlen. MySQL 5.6 hat große Verbesserungen bei der Unterabfrageunterstützung und Leistung gebracht.
Die Leistung der MySQL 5.6-Unterabfrage wird erheblich verbessert.
Der dritte Punkt: Das Programm ist zu schlecht geschrieben

Ich denke, Studenten, die DBAs waren, sollten dies erlebt haben. In kleinen und mittleren Unternehmen ist das Niveau der Programmierer unterschiedlich.
Besonders wenn man viele Programmierer trifft, die gerade erst in die Branche eingestiegen sind (frische Absolventen), ist es wahrscheinlicher, dass diese Programmierer, die gerade erst in die Branche eingestiegen sind, auch einige sehr dringende Bedürfnisse übernehmen werden. Es ist schwierig, sich ein Programm vorzustellen, das in einer solchen Umgebung entwickelt wurde.
Natürlich ist es nicht die Schuld unserer Programmierer, wir können ihnen keine Vorwürfe machen.
Der Hauptgrund für mein oben beschriebenes Phänomen ist, dass die Entwicklungsumgebung nicht dringend ist (Produkte werden jeden Tag aktiviert) und die Programmierer mit der Arbeit beschäftigt sind (langfristige Überstunden). Seien Sie nur damit beschäftigt, Geschäftsprogramme umzusetzen, und es bleibt überhaupt keine Zeit, das Programm zu optimieren.
Natürlich ist es in diesem Umfeld eine Chance für uns DBAs. Schlechtes SQL und komplexes SQL, das von Programmierern geschrieben wurde, führten dazu, dass das System langsam war oder sogar abstürzte. Dann griff unser DBA ein, um dieses schlechte SQL und langsame SQL zu optimieren und zu transformieren, und das System normalisierte sich wieder und wurde immer stabiler. Auch das ist etwas, das sehr erfüllend ist und von Kollegen und Führungskräften respektiert wird.
Gleichzeitig können Datenbankadministratoren auch die Ausbildung von Programmierern verstärken, um deren Fähigkeit zu verbessern, schnell gutes SQL zu schreiben. Lassen Sie sie weniger Zeit aufwenden und SQL-Anweisungen mit besserer Leistung und reibungsloserer Leistung schreiben. Auf diese Weise kann auch die Belastung des DBA verringert werden.
Ich persönlich bevorzuge es, mit Programmierern über Schulungen zu sprechen. Erstens kann jeder durch den Austausch von Technologie etwas gewinnen. Zweitens hilft es, eine gute Beziehung aufzubauen wird leicht zu besprechen sein. Das ist besser, als ihnen eine Mahlzeit zu gönnen.
Wir haben hauptsächlich die folgenden Lösungen für schlecht geschriebene Programme:
Um Anwendungen dazu zu bringen, Datenbankverbindungspools zu verwenden, müssen Verbindungspools verwendet werden, insbesondere in großen, auf JAVA basierenden Anwendungen mit hoher Parallelität.
Der Vorteil der Verwendung eines Verbindungspools besteht darin, dass die Anzahl der Verbindungen in der Anwendung begrenzt werden kann. Darüber hinaus ist es nicht erforderlich, jede zusätzliche Verbindung zu erstellen. Die Kosten für die Erstellung einer Verbindung sind ebenfalls hoch Eine neue Verbindung entspricht dem Erstellen eines neuen Verbindungsthreads durch MySQL.
Ich habe gerade auch erwähnt, dass es bei MySQL zu Leistungseinbußen kommen wird, wenn die Anzahl der Verbindungen zunimmt.
Studenten, die Programmcode geschrieben haben, sollten auch wissen, dass Sie auf unserem normalen PC-Notebook (normalerweise 4CORE) 400 Threads erstellen und jeder Thread 1+1+1+1+ ausführt ... einfache Arbeit, wieder schlafen und Überprüfen Sie, ob Ihr PC feststeckt oder nicht. Sie werden feststellen, dass die CPU Ihres PCs fast voll ist. Wenn Sie es wagen, 600 Threads zu erstellen, wird Ihre Maschine bald neu gestartet. Dies liegt daran, dass die CPU aufgrund des Thread-Overheads voll ausgelastet ist.
Komplexe SQL-Anweisungen
Wie ich gerade sagte, hat das von Programmierern geschriebene SQL normalerweise viele Probleme. Schließlich sind sie zu beschäftigt, um die Leistung und Funktionsweise dieses SQL zu berücksichtigen. In einigen Fällen kann das vom Programmierer gespleißte SQL direkt das gesamte System zum Absturz bringen.
Lassen Sie mich ein einfaches Beispiel geben: Eine unserer Anwendungen stellt 10 Verbindungen zur Datenbank her (maximale Anzahl von Verbindungen = 10). Jede dieser 10 Verbindungen führt das gleiche komplexe SQL gleichzeitig aus Sekunden, um dieses komplexe SQL auszuführen, können diese 10 Verbindungen dieses komplexe SQL nur innerhalb von 10 Minuten ausführen und alle anderen nachfolgenden SQLs werden blockiert.
Das hat zur Folge, dass die meisten Anwendungen 10 Minuten lang nicht verfügbar sind, oder? Und es könnte eine Lawine auslösen und zum Zusammenbruch des Systems führen.
Die Optimierung komplexer SQL ist auch eine sehr wichtige Aufgabe für DBAs. Es ist notwendig, diese komplexe SQL, langsame SQL und schlechte SQL durch Überwachungsmethoden herauszufinden und dann Optimierungsvorschläge an Programmierer zu geben (DBA muss die Leistung ermitteln). Vergleichstests). Nur wenn Programmierern erlaubt wird, den Code zu ändern, kann das System wirklich reibungslos und parallel laufen, wie eine Autobahn ohne Staus.
Manche Leute fragen sich vielleicht, dass die Programmierer unseres Unternehmens nur Bösewichte sind, selbst wenn sie sterben, sie werden ihn nicht optimieren, selbst wenn sie sterben, und sie können nicht kommunizieren. Was sollen wir also tun?
Wir können auch eine dedizierte Slave-Bibliothek (Slave-Bibliothek) erstellen, um damit umzugehen.
In unserem Unternehmen ist beispielsweise unser Hintergrundsystem, das Berichte generiert, zur Abfrage mit der Slave-Datenbank verbunden und stellt keine Verbindung zur Hauptdatenbank her.
Ungültige Logik
Vollständiger Tabellenscan
Zum Beispiel: update t set a = a + 1; Vergessen, die Where-Bedingung hinzuzufügen.
Wenn Sie möchten, dass Ihr System Millionen von Online-Benutzern unterstützt, müssen Sie ein SQL-Überprüfungssystem hinzufügen, um SQL mit ungültiger Logik und SQL mit vollständigen Tabellenscans zu eliminieren.
SQL kann erst online freigegeben werden, nachdem es vom DBA überprüft und genehmigt wurde.
Darüber hinaus sollte diese Art der großen SQL-Aktualisierung stapelweise aktualisiert werden und die große SQL-Aufgabe sollte zur Ausführung in kleine Aufgaben aufgeteilt werden. In MySQL erfordert dies besondere Aufmerksamkeit.
Warum stapelweise aktualisieren?
**Grund 1. **Wie oben erwähnt, kann eine MySQL-Abfrage nur einen CORE verwenden. SQL-Transaktionen sind zu groß und komplex, ihre Ausführung dauert lange, was leicht zu einer Überlastung führt.
Grund 2. In der Online-Umgebung verfügt MySQL im Allgemeinen über eine Master/Slave-Architektur. Wenn im Master eine große Aktualisierungstransaktion mit 1 Million Zeilen stattfindet, bleibt der SLAVE wahrscheinlich dort hängen, da der SLAVE dies tut eine Single-Threaded-Struktur, die zu Synchronisationsverzögerungen führt.
Schreiben Sie SQL in MySQL und erstellen Sie kleine Transaktions-SQL, die schnell ausgeführt und schnell übermittelt werden kann. Lassen Sie jede Abfrage schneller abschließen und die Verbindung schneller freigeben.

In diesem Artikel werden die Vorsichtsmaßnahmen für die MySQL-Optimierung erläutert. Weitere Informationen finden Sie auf der chinesischen PHP-Website.

Verwandte Empfehlungen:

Discuz!X/database DB:: Funktionsoperationsmethode

Detaillierte Erklärung der String-Klasse im ThinkPHP-Framework

JS Basics-Math Array Date

Das obige ist der detaillierte Inhalt vonÜberlegungen zur MySQL-Optimierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!