Ursachen und Lösungen für langsame MySQL-Abfragegeschwindigkeit und schlechte Leistung

1. Wodurch wird die Geschwindigkeit der Datenbankabfrage beeinflusst?

1.1 Vier Faktoren, die die Datenbankabfragegeschwindigkeit beeinflussen

1.2 Risikoanalyse

QPS: QueriesPerSecond bedeutet „Abfragerate pro Sekunde“, also die Anzahl der Abfragen, auf die ein Server pro Sekunde antworten kann. Es ist ein Maß für die Menge an Datenverkehr, die ein bestimmter Abfrageserver innerhalb einer bestimmten Zeit verarbeitet.

TPS: Dies ist die Abkürzung für TransactionsPerSecond und bezeichnet die Anzahl der Transaktionen pro Sekunde. Es ist eine Maßeinheit für Softwaretestergebnisse. Der Client startet die Zeitmessung, wenn er eine Anfrage sendet, und beendet sie, nachdem er eine Antwort vom Server erhalten hat. Auf diese Weise berechnet er die verstrichene Zeit und die Anzahl der abgeschlossenen Transaktionen.

Tipps: Am besten verzichten Sie auf die Datensicherung auf der Hauptdatenbank und sagen solche Pläne vor Großveranstaltungen ab.

Ineffizientes SQL: Ultrahohe QPS und TPS. Hoher Grad an Parallelität: Die Anzahl der Datenverbindungen ist voll (der Standardwert von max_connection ist 100 und die Anzahl der Verbindungen wird normalerweise höher eingestellt). Gleichzeitigkeit: Die Anzahl der Anfragen, die der Datenbankserver gleichzeitig verarbeitet. Extrem hohe CPU-Auslastung: Die CPU-Ressourcen sind erschöpft und der Server stürzt ab. Festplatten-E/A: Die Festplatten-E/A-Leistung sinkt plötzlich und geplante Aufgaben verbrauchen viel Festplattenleistung. Lösung: Schnellere Festplattengeräte, Anpassen geplanter Aufgaben und Durchführen einer Festplattenwartung.

1.3 Netzwerkkartenverkehr: So vermeiden Sie, dass keine Verbindung zur Datenbank hergestellt werden kann

Reduzieren Sie die Anzahl der Slave-Server (Slave-Server kopieren Protokolle vom Master-Server). Führen Sie hierarchisches Caching durch (um große Cache-Ausfälle auf dem Frontend zu vermeiden). Vermeiden Sie die Verwendung von „select*“ für Abfragen. Trennen Sie Unternehmensnetzwerke und Servernetzwerke.

1.4 Probleme durch große Tabellen (wichtig)

1.4.1 Eigenschaften großer Tabellen

Die Anzahl der Datensätze ist riesig: Eine einzelne Tabelle enthält mehr als 10 Millionen Zeilen. Die Datendatei ist riesig und über 10 GB groß.

1.4.2 Die Gefahren großer Tabellen

1. Langsame Abfrage: Es ist schwierig, die erforderlichen Daten in kurzer Zeit herauszufiltern. Das Abfragewort hat eine geringe Unterscheidung -> Um einen Teil der Daten in einer großen Datentabelle herauszufiltern, wird eine große Menge an Festplatten-E/A generiert -> Reduziert die Festplatteneffizienz

2. Auswirkungen auf DDL:

Der Aufbau des Indexes dauert lange:

MySQL-v<5.5 Durch das Erstellen eines Index wird die Tabelle gesperrt. MySQL-v>=5.5 Durch das Erstellen eines Index wird eine Master-Slave-Verzögerung verursacht (MySQL erstellt einen Index, zuerst für die Gruppe, dann für die Datenbank).

Das Ändern der Tabellenstruktur erfordert eine lange Tabellensperre: Dies führt zu einer langen Master-Slave-Verzögerung ('480 Sekunden Verzögerung').

1.4.3 Umgang mit großen Tabellen in der Datenbank

Aufteilen einer großen Tabelle in mehrere kleine Tabellen

Schwierigkeit:

Auswahl des Primärschlüssels für Sharding-Tabellen Abfrage und Statistik von partitionierten Daten nach dem Sharding

1.5 Probleme mit großen Transaktionen (wichtig*)*

1.5.1 Was ist eine Transaktion?

1.5.2 ACID-Eigenschaften von Transaktionen

1. Atomarität: Alle sind erfolgreich, alle Rollbacks schlagen fehl. Ein- und Auszahlungen bei Banken.

2. Konstant: Der Gesamtbetrag der Überweisung bleibt unverändert.

3. Isolierung:

Isolationsstufe:

Nicht festgeschriebenes Lesen (READ UNCOMMITED) ist ein Dirty Read. Zwei Transaktionen sind füreinander sichtbar. Festgeschriebenes Lesen (READ COMMITED) entspricht dem Grundkonzept der Isolation. Während eine Transaktion ausgeführt wird, sind andere festgeschriebene Transaktionen für die Transaktion sichtbar, d. h. die von anderen Transaktionen festgeschriebenen Daten können abgerufen werden. Wiederholbares Lesen (REPEATABLE READ) Die Standardisolationsstufe von InnoDB. Während eine Transaktion ausgeführt wird, sind alle anderen Transaktionen für sie unsichtbar, d. h. das Ergebnis mehrerer Lesevorgänge ist dasselbe! SERIALIZABLE sperrt jede gelesene Datenzeile, was zu einer großen Anzahl von Sperrtimeouts und Sperranforderungen führen kann. Es kann mit strikter Datenkonsistenz und ohne Parallelität verwendet werden.

Zeigen Sie die Transaktionsisolationsstufe des Systems an: Zeigen Sie Variablen wie „%iso%“ an.

Eine neue Transaktion starten: begin;

Eine Transaktion übermitteln: Commit;

Ändern Sie die Isolationsstufe der Transaktion: setsession tx_isolation='read-committed';

4. LANGLEBIGKEIT: Aus Sicht der Datenbank ist eine Fortdauer der Daten nicht möglich, wenn die Festplatte beschädigt ist.

Der Redolog-Mechanismus stellt die Konsistenz und Persistenz von Transaktionsaktualisierungen sicher

1.5.3 Große Transaktionen

Transaktionen, deren Ausführung lange dauert und viele Daten erfordert.

Risiken: Zu viele gesperrte Daten, lange Rollback-Zeit, lange Ausführungszeit.

Das Sperren zu vieler Daten führt zu zahlreichen Blockierungs- und Sperrzeitüberschreitungen. Das Zurücksetzen dauert lange und die Daten sind weiterhin gesperrt. Eine lange Ausführungszeit führt zu Master-Slave-Verzögerungen, da der Slave-Server die Synchronisierung erst startet, wenn der Master-Server alle Protokollschreibvorgänge abgeschlossen hat, was zu Verzögerungen führt.

Lösung:

Vermeiden Sie die gleichzeitige Verarbeitung zu vieler Daten, indem Sie sie in Stapeln verarbeiten. Entfernen Sie unnötige SELECT-Operationen, um sicherzustellen, dass nur die erforderlichen Schreiboperationen in die Transaktion einbezogen werden. 2. Was beeinflusst die MySQL-Leistung (sehr wichtig)

2.1 Verschiedene Aspekte, die die Leistung beeinflussen

Serverhardware. Serversystem (Optimierung der Systemparameter). Speicher-Engine. MyISAM: Unterstützt keine Transaktionen oder Sperren auf Tabellenebene. InnoDB: unterstützt Transaktionen, Zeilensperren und Transaktions-ACID. Konfiguration der Datenbankparameter. Entwurf der Datenbankstruktur und SQL-Anweisungen. (Tastenoptimierung)

2.2 MySQL-Architektur

Aufgeteilt in drei Schichten: Client->Serviceschicht->Speicher-Engine

MySQL ist eine Plug-in-Speicher-Engine, von der es viele Typen gibt. Solange Sie eine Schnittstelle implementieren, die der MySQL-Speicher-Engine entspricht, können Sie Ihre eigene Speicher-Engine entwickeln! Alle speichermaschinenübergreifenden Funktionen werden in der Serviceschicht implementiert. Die Speicher-Engine von MySQL ist für Tabellen und nicht für Bibliotheken gedacht. Das heißt, dass in einer Datenbank unterschiedliche Speicher-Engines verwendet werden können. Aber das ist nicht zu empfehlen.

2.3 InnoDB-Speicher-Engine

Die Standardspeicher-Engine für MySQL 5.5 und spätere Versionen ist InnoDB.

2.3.1 InnoDB verwendet Tablespace zur Datenspeicherung.

Variablen wie ‚innodb_file_per_table‘ anzeigen

Wenn innodbfileper_table aktiviert ist, wird ein unabhängiger Tabellenbereich erstellt und die Datei lautet tablename.ibd.

Wenn innodbfileper_table deaktiviert ist, werden die Daten im gemeinsam genutzten Tabellenbereich des Systems gespeichert und die Datei ist ibdataX (X ist eine Ganzzahl, die bei 1 beginnt).

.frm: Dies ist eine auf Serverebene generierte Datei, die dem Datenwörterbuch auf Serverebene ähnelt und die Tabellenstruktur aufzeichnet.

2.3.2 (MySQL 5.5 Standard) System-Tablespace und (MySQL 5.6 und höher Standard) Unabhängiger Tablespace

1.1 Der Systemtabellenbereich kann die Dateigröße nicht einfach verkleinern, was zu Speicherplatzverschwendung und starker Festplattenfragmentierung führt. 1.2 Unabhängige Tablespaces können Systemdateien durch den Befehl „opize table“ verkleinern, ohne den Server neu zu starten und ohne den normalen Zugriff auf die Tabelle zu beeinträchtigen. 2.1 Wenn mehrere Tabellen aktualisiert werden, geschieht dies tatsächlich nacheinander, was zu einem E/A-Engpass führt. 2.2 Unabhängige Tablespaces können Daten gleichzeitig in mehrere Dateien aktualisieren.

Es wird dringend empfohlen, einen separaten Tablespace für Innodb zu verwenden, da die Optimierung dadurch bequemer und kontrollierbarer wird.

2.3.3 So übertragen Sie Tabellen im Systemtabellenbereich in unabhängige Tabellenbereiche

1. Verwenden Sie mysqldump, um alle Datenbankdaten zu exportieren (gespeicherte Prozeduren, Trigger und geplante Aufgaben müssen zusammen exportiert werden). Dies können Sie auf dem Slave-Server tun. 2. Stoppen Sie den MySQL-Server, ändern Sie die Parameter (fügen Sie innodbfileper_table zu my.cnf hinzu) und löschen Sie Inoodb-bezogene Dateien (Sie können das Datenverzeichnis neu erstellen). 3. Starten Sie MySQL neu und erstellen Sie den Innodb-Systemtabellenbereich neu. 4. Importieren Sie die Daten erneut.

Oder Altertable kann auch übertragen werden, aber der vom Systemtabellenbereich belegte Speicherplatz kann nicht zurückgefordert werden.

2.4 Funktionen der InnoDB-Speicher-Engine

2.4.1 Feature 1: Transaktionale Speicher-Engine und zwei spezielle Log-Typen: Redo Log und Undo Log

Innodb ist eine transaktionale Speicher-Engine. Unterstützt die ACID-Eigenschaften von Transaktionen vollständig. Unterstützt zwei spezielle Protokolltypen, die für Transaktionen erforderlich sind: RedoLog und UndoLog

Redo-Log: Realisieren Sie die Transaktionspersistenz (festgeschriebene Transaktionen). Undo-Log: Nicht festgeschriebene Transaktionen erfordern unabhängig vom Tablespace wahlfreien Zugriff und können auf hochleistungsfähigen IO-Geräten gespeichert werden.

Das Undo-Protokoll zeichnet den Wert bestimmter Daten auf, bevor diese geändert werden, und kann zum Rollback verwendet werden, wenn eine Transaktion fehlschlägt. Das Redo-Protokoll zeichnet den Wert eines bestimmten Datenblocks auf, nachdem dieser geändert wurde, und kann zum Wiederherstellen von Daten verwendet werden, die durch erfolgreiche Transaktionen aktualisiert wurden, die nicht in die Datendatei geschrieben wurden.

2.4.2 Funktion 2: Unterstützt Zeilensperren

InnoDB unterstützt Zeilensperren. Zeilensperren können die Parallelitätsunterstützung maximieren. Zeilensperren werden von der Speicher-Engine-Ebene implementiert.

2.5 Was ist ein Schloss?

2.5.1 Sperren

2.5.2 Sperrtypen

2.5.3 Sperrgranularität

Die MySQL-Transaktionsunterstützung ist nicht an den MySQL-Server selbst gebunden, sondern bezieht sich auf die Speicher-Engine.

Fügen Sie dem Tabellennamen einen Sperrbefehl auf Tabellenebene hinzu: locktable table_name write; Die Schreibsperre blockiert Lese- und Schreibvorgänge anderer Benutzer für die Tabelle, bis die Schreibsperre aufgehoben wird: unlock tables;

Je größer der Sperr-Overhead, desto geringer die Granularität und desto höher die Parallelität. Sperren auf Tabellenebene werden normalerweise auf Serverebene implementiert. Zeilensperren werden auf der Speichermodulebene implementiert. Der Sperrmechanismus von InnoDB ist der Serverebene unbekannt

2.5.4 Blockierung und Deadlock

(1) Eine Blockade ist ein Warteschlangenphänomen, das durch unzureichende Ressourcen verursacht wird. (2) Ein Deadlock tritt auf, wenn zwei Objekte eine Ressource anfordern, während sie bereits eine andere Ressource besitzen, und die andere Ressource zufällig von beiden Objekten gehalten wird, was dazu führt, dass die beiden Objekte den Vorgang nicht abschließen können und die von ihnen gehaltenen Ressourcen nicht freigegeben werden können.

2.6 So wählen Sie die richtige Speicher-Engine aus

Referenzbedingungen:

Transaktionssicherung (kostenloses Online-Backup von Innobd) Wiederherstellung nach einem Absturz Speicher-Engine-spezifische Funktionen

Zusammenfassung: Innodb ist großartig.

Hinweis: Versuchen Sie, keine gemischten Speicher-Engines zu verwenden, da beispielsweise ein Rollback zu Problemen mit dem Online-Hot-Standby führt.

2.7 Konfigurationsparameter

2.7.1 Mit der Speicherkonfiguration verbundene Parameter

Bestimmt die Obergrenze des nutzbaren Speichers.

Die Obergrenze der Speichernutzung darf den physischen Speicher nicht überschreiten, da es sonst leicht zu einem Speicherüberlauf kommen kann. (Bei 32-Bit-Betriebssystemen kann MySQL nur Speicher unter 3 GB ausprobieren.)

Bestimmt den von jeder MySQL-Verbindung verwendeten Speicher individuell.

sort_buffer_size #Definiert die Größe des Sortierpuffers für jeden Thread. MySQL reserviert nur dann Speicher für jeden Puffer, wenn eine Abfrage vorliegt und ein Sortiervorgang ausgeführt werden muss (reserviert den gesamten Speicher dieses Parameters direkt) join_buffer_size #Definiert die Größe des von jedem Thread verwendeten Verbindungspuffers. Wenn eine Abfrage mit mehreren Tabellen verknüpft ist, reserviert MySQL für jede Tabelle einen Verbindungspuffer, sodass für eine Abfrage mehrere Verbindungspuffer entstehen read_buffer_size #Definiert die Größe des Lese-Pufferpools, der reserviert wird, wenn auf einem MyISAM ein vollständiger Tabellenscan ausgeführt wird. MySQL reserviert dafür Speicher, wenn eine Abfrage dies erfordert, und dies muss ein Vielfaches von 4 KB sein; read_rnd_buffer_size #Größe des Indexpuffers. MySQL reserviert dafür Speicher, wenn eine Abfrage dies erfordert, und reserviert nur die erforderliche Größe.

Hinweis: Die oben genannten vier Parameter sind für einen Thread reserviert. Wenn 100 Verbindungen vorhanden sind, ist ×100 erforderlich.

MySQL-Datenbankinstanz:

①MySQL ist ein Einzelprozess-Multithread (während Oracle ein Multiprozess ist), was bedeutet, dass die MySQL-Instanz ein Serviceprozess auf dem System ist, also ein Prozess.

②Die MySQL-Instanz besteht aus Threads und Speicher und wird tatsächlich zum Bedienen der Datenbankdatei verwendet.

Im Allgemeinen betreibt eine Instanz eine oder mehrere Datenbanken; in einem Cluster betreiben mehrere Instanzen eine oder mehrere Datenbanken.

So weisen Sie Speicher für den Cache-Pool zu:

Innodb_buffer_pool_size definiert die Größe des von Innodb verwendeten Pufferpools. Sie ist sehr wichtig für die Leistung und muss groß genug sein. Wenn sie jedoch zu groß ist, dauert es länger, schmutzige Seiten aus dem Pufferpool auf die Festplatte zu übertragen, wenn Innodb geschlossen wird.

Gesamtspeicher - (von jedem Thread benötigter Speicher * Anzahl der Verbindungen) - vom System reservierter Speicher

key_buffer_size definiert die Größe des von MyISAM verwendeten Cache-Pools. Da Daten vom Cache des Speicherbetriebssystems abhängen, sollte mehr Speicherplatz für das Betriebssystem reserviert werden.

Wählen Sie Summe(Indexlänge) aus Information_Schema.Talbes, wobei Engine = "myisam"

Hinweis: Auch wenn alle für die Entwicklung verwendeten Tabellen Innodb-Tabellen sind, muss Speicher für MyISAM reserviert werden, da die vom MySQL-System verwendeten Tabellen immer noch MyISAM-Tabellen sind.

max_connections steuert die maximal zulässige Anzahl von Verbindungen, normalerweise 2000 oder mehr.

Verwenden Sie keine Fremdschlüsseleinschränkungen, um die Datenintegrität sicherzustellen.

2.8 Reihenfolge der Leistungsoptimierung

Von oben nach unten: