MySQL-Optimierung: Cache-Optimierung (Fortsetzung)

In MySQL gibt es überall Caches. Wenn ich den Quellcode von MySQL lese, werde ich im Detail analysieren, wie der Cache verwendet wird. In diesem Abschnitt werden hauptsächlich verschiedene explizite Caches optimiert:

1. Abfrage-Cache-Optimierung
2. Ergebnissatz-Cache
3. Sortier-Cache
4. Verbindungs-Cache beitreten
5. Tabellen-Cache Cache und Cache für Tabellenstrukturdefinitionen Cache
6. Tabellenscanpuffer
7. MyISAM-Index-Cache-Puffer
8. Protokollcache
9. Vorlesemechanismus
10. Verzögerte Tabellen und temporäre Tabellen
    

1. Abfrage-Cache-Optimierung

Der Abfragecache speichert nicht nur die Struktur der Abfrageanweisung, sondern auch die Abfrageergebnisse. Wenn das SQL innerhalb eines bestimmten Zeitraums gleich ist, werden die Ergebnisse direkt aus dem Cache gelesen, um die Effizienz der Datensuche zu verbessern. Wenn die Daten im Cache jedoch nicht mit den Daten auf der Festplatte übereinstimmen, wird der Cache ungültig.

mysql> Variablen wie „%query_cache%“ anzeigen;
+------------------------------+---------+
| Variablenname | Wert |
+------------------------------+---------+
| habe_Abfragecache | JA |
| Abfrage-Cache-Limit | 1048576 |
| Abfrage-Cache_Mindestressource | 4096 |
| Abfrage-Cache-Größe | 1048576 |
| Abfrage-Cache-Typ | AUS |
| query_cache_wlock_invalidate | AUS |
+------------------------------+---------+

have_query_cache Gibt an, ob der Abfrage-Cache unterstützt wird.

query_cache_limit Wenn die Ergebnismengengröße einer Select-Anweisung den Wert von querycachelimit überschreitet, wird die Ergebnismenge nicht zum Abfrage-Cache hinzugefügt.

query_cache_min_res_unit Der Abfrage-Cache bezieht sich auf den Speicherplatz in Blöcken, und die Größe jedes angewendeten Blocks entspricht dem eingestellten Wert. 4K ist ein sehr guter Wert und muss nicht verändert werden.

query_cache_size Die Größe des Abfragecaches.

query_cache_type Abfrage-Cache-Typ, die Werte sind 0 (AUS), 1 (AN), 2 (DEMOND). AUS bedeutet, dass der Abfragecache deaktiviert ist. ON bedeutet, dass die Abfrage immer zuerst den Abfragecache durchsucht, es sei denn, die Option sql_no_cache ist in der Select-Anweisung enthalten. DEMOND bedeutet, dass das Caching nur angewendet wird, wenn die Option sql_cache in der Select-Anweisung enthalten ist.

query_cache_wlock_invalidate Mit diesem Parameter wird die Beziehung zwischen exklusiven Sperren auf Zeilenebene und Abfragecaches festgelegt. Der Standardwert ist 0 (AUS), was bedeutet, dass alle Abfragecaches der Tabelle gültig bleiben, während exklusive Sperren auf Zeilenebene angewendet werden. Wenn der Wert auf 1 (Ein) gesetzt ist, bedeutet dies, dass alle Abfrage-Caches für die Tabelle ungültig gemacht werden, während die exklusive Sperre auf Zeilenebene gewährt wird.

Anzeigen der Trefferquote im Abfragecache

mysql> Status wie „Qcache%“ anzeigen;
+-------------------------+---------+
| Variablenname | Wert |
+-------------------------+---------+
| Qcache_freie_blöcke | 1 |
| Qcache_freier_Speicher | 1031360 |
| Qcache_Hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_nicht_zwischengespeichert | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+

Zeigen Sie die aktuellen Cache-Statusinformationen an:

Qcache_freie_blöcke

Gibt die Anzahl der Speicherblöcke (Anzahl der Fragmente) im Abfragecache an, die sich in einem wiederkehrenden Zustand befinden. Wenn der Wert von Qcache_free_blocks groß ist, bedeutet dies, dass sich im Abfragecache viele Fragmente befinden, was darauf hinweist, dass der Abfrageergebnissatz klein ist. In diesem Fall können Sie den Wert von query_cache_min_res_unit verringern. Durch die Verwendung des Flush Query Cache werden mehrere Fragmente im Cache aussortiert, um einen größeren freien Block zu erhalten. Cache-Fragmentierungsrate = Qcache_free_blocks/ Qcache_total_blocks * 100 %

Qcache_freier_Speicher

Gibt an, wie viel verfügbarer Speicher für den Abfrage-Cache der aktuellen MySQL-Dienstinstanz zur Verfügung steht.

Qcache_Hits

Gibt an, wie oft der Abfrage-Cache verwendet wird. Der Wert steigt sequenziell an. Wenn Qcache_hits relativ groß ist, bedeutet dies, dass der Abfragecache sehr häufig verwendet wird und Sie den Abfragecache vergrößern müssen.

Qcache_inserts

Gibt die Gesamtzahl der Select-Anweisungsergebnissätze an, die im Abfragecache zwischengespeichert wurden.

Qcache_lowmen_prunes

Gibt die Anzahl der Abfrageergebnisse an, die MySQL gelöscht hat, weil der Abfragecache voll war und übergelaufen ist. Wenn dieser Wert groß ist, weist dies darauf hin, dass der Abfragecache zu klein ist.

Qcache_nicht_zwischengespeichert

Gibt die Anzahl der Selects an, die nicht in den Abfrage-Cache gelangt sind.

Qcache_queryies_in_cache

Gibt an, wie viele Select-Statement-Ergebnismengen im Abfrage-Cache zwischengespeichert werden

Qcache_Gesamtblockanzahl

Die Gesamtzahl der Abfrage-Caches

Berechnungsmethode für die Cache-Trefferquote: Abfrage-Cache-Trefferquote = Qcache_hits / Com_select * 100 %

Com_select ist die Anzahl der Select-Anweisungen, die von der aktuellen MySQL-Instanz ausgeführt werden. Im Allgemeinen gilt: Com_select = Qcache_insert + Qcache_not_cached. Qcache_not_cached enthält Auswahlanweisungen, die aufgrund häufiger Datenänderungen zu einer Ungültigkeit des Abfragecaches führen. Daher ist die Trefferquote im Allgemeinen niedrig. Wenn wir den Faktor der Ungültigkeit ignorieren, ist die Trefferquote des Abfrage-Cache = Qcache_hits / (Qcache_hits + Qcache_inserts). Wenn die mit dieser Formel berechnete Trefferquote des Abfrage-Cache relativ hoch ist, bedeutet dies, dass die meisten Select-Anweisungen den Abfrage-Cache treffen.

Verwenden Sie den folgenden Befehl, um anzuzeigen, wie viele Select-Anweisungen im aktuellen System ausgeführt werden

mysql> Status wie „Com_select“ anzeigen;
+---------------+-------+
| Variablenname | Wert |
+---------------+-------+
| Com_Auswahl | 1 |
+---------------+-------+

2. Ergebnissatz-Cache

Der Ergebnissatzcache ist ein Sitzungscache, der erstellt wird, nachdem der MySQL-Client erfolgreich eine Verbindung zum Server hergestellt hat. Der MySQL-Server verwaltet für jeden MySQL-Client einen Ergebnissatz-Cache. Zwischenspeichern Sie die Verbindungsinformationen des MySQL-Client-Verbindungsthreads und zwischenspeichern Sie die an den MySQL-Client zurückgegebenen Ergebnissatzinformationen. Wenn der MySQL-Client eine Select-Anweisung an den Server sendet, speichert MySQL das Ausführungsergebnis der Select-Anweisung vorübergehend im Ergebnissatzcache. Die Größe des Ergebnissatzpuffers wird durch den Parameterwert net_buffer_length definiert:

mysql> Variablen wie „net_buffer_length“ anzeigen;
+---------------------+------+
| Variablenname | Wert |
+---------------------+------+
| Nettopufferlänge | 16384 |
+---------------------+------+

Überschreitet die Ergebnismenge den durch net_buffer_length festgelegten Wert, wird die Kapazität automatisch erweitert, überschreitet jedoch nicht den Schwellenwert von max_allowd_packet:

mysql> Variablen wie „max_allowed_packet“ anzeigen;
+--------------------+---------+
| Variablenname | Wert |
+--------------------+---------+
| maximal zulässiges Paket | 4194304 |
+--------------------+---------+

3. Cache sortieren

MySQL verwendet üblicherweise zwei Datenspeicher-Engines: InnoDB und MyISAM. Daher verwendet jede Engine bei der Optimierung eine Optimierungsmethode, die zu ihrer eigenen Engine passt. In Bezug auf die Unterschiede zwischen MySQL- und InnoDB-Tabellenstrukturdateien und Datenprotokolldateien können Sie zunächst meinen Blog zum MySQL-Protokollsystem lesen, um ein ausreichendes Verständnis dieser grundlegenden Konzepte zu erlangen. Dann können Sie die Methoden zur Engine-Optimierung lesen, um dies problemlos durchführen zu können und sich nicht zu langweilen.

1. Gewöhnlicher Sortiercache

Der Sortiercache ist ein Sitzungscache, wenn die vom Client an den Server gesendete SQL-Anweisung eine Order By- oder Group By-Klausel zur Entwurfssortierung enthält. MySQL wählt den entsprechenden Sortieralgorithmus aus und sortiert nach dem gemeinsamen Sortierindex, um die Sortiergeschwindigkeit zu verbessern. Die Größe des normalen Sortierindex wird durch den Parameter sort_buffer_size definiert. Wenn Sie die Sortiergeschwindigkeit erhöhen möchten, sollten Sie zuerst einen geeigneten Index hinzufügen und dann den Sortierindex-Cache sort_buffer_size erhöhen.

mysql> wähle @@global.sort_buffer_size / 1024;
+----------------------------------+
| @@global.sort_buffer_size / 1024 |
+----------------------------------+
| 256,0000 |
+----------------------------------+
1 Zeile im Satz (0,00 Sek.)

Als nächstes werfen wir einen Blick auf die Parameter im Zusammenhang mit dem Sortiercache:

mysql> Variablen wie „%sort%“ anzeigen;
+---------------------------------+---------------------+
| Variablenname | Wert |
+---------------------------------+---------------------+
| innodb_disable_sort_file_cache | AUS |
| innodb_ft_sort_pll_degree | 2 |
| innodb_sort_buffer_size | 1048576 |
| maximale Länge für Sortierdaten | 1024 |
| maximale Sortierlänge | 1024 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_sort_buffer_size | 8388608 |
| Sortierpuffergröße | 262144 |
+---------------------------------+---------------------+

mysql> Status wie „%sort%“ anzeigen;
+---------------------+------+
| Variablenname | Wert |
+---------------------+------+
| Sort_Merge_Passes | 0 |
| Sortierbereich | 0 |
| Zeilen_sortieren | 0 |
| Sort_scan | 0 |
+---------------------+------+

maximale_Länge_für_Sortierdaten

Die Standardgröße beträgt 1024 Byte. Der Sortiervorgang wird für jede Spalte ausgeführt. Wenn die Wertelänge der Spalte lang ist, kann die MySQL-Leistung durch Erhöhen dieses Parameters verbessert werden.

maximale Sortierlänge

Bei Verwendung von order by oder group by werden die ersten max_sort_length Bytes der Spalte zum Sortieren verwendet. Nach Abschluss des Sortiervorgangs werden die Sortierinformationen im Status dieser Sitzung aufgezeichnet.

Sortier- und Zusammenführungspässe

Die Häufigkeit, mit der ein Sortiervorgang unter Verwendung einer temporären Datei abgeschlossen wurde. Wenn MySQL einen Sortiervorgang ausführt, versucht es zunächst, die Sortierung im normalen Sortiercache abzuschließen. Wenn der Cache-Speicherplatz nicht ausreicht, verwendet MySQL den Cache, um mehrere Sortierungen durchzuführen. Und speichern Sie jedes Sortierergebnis in einer temporären Datei und sortieren Sie die Daten abschließend erneut in der temporären Datei. Der Wert Sort_merge_passes zeichnet auf, wie oft die Datei sortiert wird. Weil das Sortieren von Dateien das Lesen von Dateien, das Öffnen von Dateihandles und anschließend das Schließen von Dateien umfasst. Daher ist der Systemverbrauch beim Lesen von Dateien relativ groß. Durch Erhöhen der sort_buffer_size des normalen Sortiercaches kann die Häufigkeit reduziert werden, mit der temporäre Dateien zum Sortieren verwendet werden, wodurch die Sortierleistung verbessert wird.

Sort_range

Die Häufigkeit, mit der eine Bereichssortierung verwendet wird

Zeilen sortieren

Die Anzahl der sortierten Zeilen

Sort_scan

Häufigkeit, mit der die Sortierung über einen vollständigen Tabellenscan abgeschlossen wurde

2. MyISAM-Sortiercache

Wenn wir die Anweisung „alter table“ oder „create index“ verwenden, um einen Index für eine MyISAM-Tabelle zu erstellen, oder einen Teil der Daten mithilfe des Pfads „load data infile“ importieren, führen diese Vorgänge dazu, dass der Index neu erstellt wird. Beim Neuaufbau des Index muss das Indexfeld sortiert werden. Um den Neuaufbau des Index zu beschleunigen, stellt MyISAM einen Sortiercache bereit, um die Indexsortierarbeit zu implementieren. Diese Methoden versuchen, die Sortierarbeit im Speicher abzuschließen. Die Größe des MyISAM-Sortiercaches wird durch myisam_sort_buffer_size definiert. Nach dem Neuaufbau des Indexes wird der Cache sofort freigegeben.

Wenn der Sortiercache jedoch den Schwellenwert von myisam_sort_buffer_size überschreitet, ist es notwendig, die Sortierung der Indexfelder in einer temporären Datei abzuschließen. Die Größe der externen temporären Datei wird durch den Parameter myisam_max_sort_file_size festgelegt. Nachdem der Index neu erstellt wurde, wird die temporäre Datei sofort gelöscht.

mysql> wähle @@global.myisam_sort_buffer_size/1024;
+------------------------------------------+
| @@global.myisam_sort_buffer_size/1024 |
+------------------------------------------+
| 8192.0000 |
+------------------------------------------+

mysql> wähle @@global.myisam_max_sort_file_size /1024;
+------------------------------------------+
| @@global.myisam_max_sort_file_size /1024 |
+------------------------------------------+
|9007199254739967.7734 |
+------------------------------------------+

3. InnoDB-Sortiercache

Ähnlich wie die MyISAM-Engine stellt InnoDB beim Ausführen von „Alter Table“ oder „Create Index“ drei InnoDB-Sortierpuffer für die Indexsortierung bereit. Die Größe jedes Caches wird durch innodb_sort_buffer_size definiert.

mysql> wähle @@global.innodb_sort_buffer_size/1024;
+------------------------------------------+
| @@global.innodb_sort_buffer_size/1024 |
+------------------------------------------+
| 1024,0000 |
+------------------------------------------+

4. Verbindungs-Cache beitreten

Der Join-Cache ist ein Sitzungscache. Wenn zwei Tabellen verbunden sind, der Index jedoch nicht verwendet werden kann (dies ist die Voraussetzung für die Verwendung des Join-Cache), weist MySQL jeder Tabelle einen Join-Cache zu.

mysql> wähle @@global.join_buffer_size/1024;
+--------------------------------+
| @@global.join_buffer_size/1024 |
+--------------------------------+
| 256,0000 |
+--------------------------------+

join_buffer_size definiert die Größe des Verbindungspuffers, wie oben gezeigt, der Standardwert ist 256;

5. Tabellen-Cache und Tabellenstruktur-Definitions-Cache

Wenn der MySQL-Dienst auf eine Tabelle in der Datenbank zugreift, führt MySQL tatsächlich einen Dateilesevorgang aus. MySQL-Daten werden in Dateien auf der Festplatte gespeichert, was sich von einigen speicherbasierten Datenbanken unterscheidet. Wenn wir eine Tabelle mit einer Select-Anweisung abfragen, ohne die Verwendung des Abfragecaches zu berücksichtigen, muss das Betriebssystem zuerst die Datei öffnen und einen Deskriptor für die Datei generieren. Das Betriebssystem übergibt den Dateideskriptor an MySQL, sodass MySQL CURD-Operationen auf der Datenbank ausführen kann. Das Öffnen von Dateien und Generieren von Dateideskriptoren verbraucht Systemressourcen und verursacht Zugriffsverzögerungen. MySQL speichert die geöffneten Dateien, einschließlich der Dateideskriptoren, im Cache, sodass Sie die Datei beim späteren Zugriff darauf nicht öffnen müssen. Dies verbessert die Effizienz beim Lesen von Dateien.

Die Tabellenstruktur ändert sich nicht oft. Wenn auf eine Tabelle zugegriffen wird, wird die Tabelle nicht nur in den Tabellencache von MySQL implantiert, sondern MySQL legt die Tabellenstruktur auch in den Tabellenstrukturdefinitionscache für die nächste Verwendung.

mysql> Variablen wie „table%“ anzeigen;
+----------------------------+----------+
| Variablenname | Wert |
+----------------------------+----------+
| Tabellendefinitionscache | 1400 |
| Tabelle_öffnen_Cache | 2000 |
| Tabelle_öffnen_Cache-Instanzen | 1 |
+----------------------------+----------+

mysql> Variablen wie „%open%“ anzeigen;
+----------------------------+----------+
| Variablenname | Wert |
+----------------------------+----------+
| have_openssl | DEAKTIVIERT |
| innodb_open_files | 2000 |
| Anzahl der geöffneten Dateien | 65535 |
| Tabelle_öffnen_Cache | 2000 |
| Tabelle_öffnen_Cache-Instanzen | 1 |
+----------------------------+----------+

Tabelle_öffnen_Cache

Legt eine Begrenzung für die Anzahl der Tabellen und Ansichten fest, die zwischengespeichert werden können.

Tabellendefinitionscache

Legt fest, wie viele Frm-Tabellenstrukturen gespeichert werden können

Für die MySQL MyISAM-Engine umfasst die Tabellenstruktur MYI und MYD sowie die Tabellenstruktur frm. Beim Zugriff auf die MyISAM-Engine müssen zwei Dateien (MYI und MYD) gleichzeitig geöffnet werden, um zwei Dateideskriptoren zu generieren.

Begrenzung_der_offenen_Dateien

Maximale Anzahl geöffneter Dateien

innodb_open_files

Wenn die InnoDB-Tabelle eine unabhängige Tablespace-Datei (ibd) verwendet, legt dieser Parameter die Anzahl der Dateien fest, die gleichzeitig geöffnet werden können.

Nachfolgend sind die Statuswerte aufgeführt, die sich auf das Öffnen einer Tabelle beziehen:

mysql> Status wie „Open%“ anzeigen;
+--------------------------+----------+
| Variablenname | Wert |
+--------------------------+----------+
| Dateien öffnen | 18 |
| Offene_Streams | 0 |
| Tabellendefinitionen öffnen | 70 |
| Offene_Tische | 63 |
| Geöffnete_Dateien | 125 |
| Geöffnete_Tabellendefinitionen | 0 |
| Geöffnete_Tabellen | 0 |
+--------------------------+----------+

6. Tabellenscanpuffer

Tabellenscans werden in zwei Typen unterteilt: sequentieller Scan und zufälliger Scan.

Wenn eine MyISAM-Tabelle nicht indiziert ist, entspricht die Abfragegeschwindigkeit einem vollständigen Tabellenscan, was sehr ineffizient ist. Um die Geschwindigkeit vollständiger Tabellenscans zu erhöhen, stellt MySQL einen sequentiellen Scan-Cache (Lese-Puffer) bereit. Zu diesem Zeitpunkt liest MySQL alle Datenblöcke entsprechend der Speicherreihenfolge der gespeicherten Daten. Jeder gelesene Datenblock wird im sequentiellen Scan-Cache zwischengespeichert. Wenn der Lesepuffer voll ist, werden die Daten an den Aufrufer der höheren Ebene zurückgegeben.

Zufälliger Scan

Wenn in der Tabelle ein Cache vorhanden ist, werden beim Scannen der Tabelle zuerst die Indexfelder der Tabelle in den Speicher gelegt und sortiert. Anschließend werden die Daten entsprechend der sortierten Reihenfolge auf der Festplatte durchsucht.

7. MyISAM-Index-Cache-Puffer

Durch das Zwischenspeichern des Inhalts der MYI-Indexdatei können Sie die Geschwindigkeit beim Lesen des Index und bei der Indizierung beschleunigen. Der Index-Cache funktioniert nur für MyISAM-Tabellen und wird von allen Threads gemeinsam genutzt. Wenn eine Abfrageanweisung oder ein Update-Index über einen Index auf Tabellendaten zugreift, prüft MySQL zunächst, ob die erforderlichen Indexinformationen bereits im Index-Cache vorhanden sind. Wenn dies der Fall ist, kann über den Index im Cache direkt auf die dem Index entsprechende MYD-Datei zugegriffen werden. Wenn nicht, wird die MYI-Datei gelesen und die entsprechenden Indexdaten in den Cache gelesen. Der Index-Cache spielt eine entscheidende Rolle für die Zugriffsleistung von MyISAM-Tabellen.

mysql> Variablen wie „key%“ anzeigen;
+--------------------------+---------+
| Variablenname | Wert |
+--------------------------+---------+
| Schlüsselpuffergröße | 8388608 (8 M)| 
| Schwellenwert für Schlüsselcachealter | 300 |
| Schlüsselcacheblockgröße | 1024 |
| Schlüsselcache-Aufteilungslimit | 100 |
+--------------------------+---------+

Schlüsselpuffergröße

Legen Sie die Größe des Index-Cache fest. Der Standardwert beträgt 8 MB. Verbesserung vorschlagen.

Schlüsselcacheblockgröße

Geben Sie die Blockgröße jedes Index-Caches an. Es wird empfohlen, sie auf 4K einzustellen, also 4096

key_cache_division_limit

Um den Cache effektiv zu nutzen. Standardmäßig ist der MySQL-Cache in zwei Index-Cache-Bereiche unterteilt: „Warm Area“ und „Hot Area“. Der Parameter key_cache_division_limit teilt den Index-Cache in Form von Prozentsätzen in mehrere Regionen auf. Wenn der Standardwert 100 ist, bedeutet dies, dass der Index-Cache nur eine Warmzone hat und der LRU-Algorithmus aktiviert wird, um Indizes im Index-Cache zu eliminieren.

key_cahe_age_threshold

Steuern Sie, wann Indizes in warmen und heißen Zonen herauf- oder herabgestuft werden. Liegt der Wert unter 100, handelt es sich um einen Hotspot. Der Bewegungsalgorithmus ähnelt in etwa dem LRU-Algorithmus.

Zeigen Sie die Statuswerte des aktuellen Indexlese- und Indexschreibvorgangs der MySQL-Dienstinstanz an:

mysql> Status wie „Key%“ anzeigen;
+------------------------+----------+
| Variablenname | Wert |
+------------------------+----------+
| Schlüsselblöcke nicht geleert | 0 |
| Key_blocks_unbenutzt | 6698 |
| Verwendete Schlüsselblöcke | 0 |
| Schlüsselleseanforderungen | 0 |
| Schlüssel_Lesungen | 0 |
| Schlüsselschreibanforderungen | 0 |
| Schlüsselschreibvorgänge | 0 |
+------------------------+----------+

8. Protokoll-Cache

Der Log-Cache ist in einen Binärlog-Cache und einen InnoDB-Redo-Log-Cache unterteilt

1. Binärer Protokollcache

mysql> Variablen wie „%binlog%cache%“ anzeigen;
+----------------------------+----------------------+
| Variablenname | Wert |
+----------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
+----------------------------+----------------------+

mysql> Status wie „%binlog%cache%“ anzeigen;
+----------------------------+----------+
| Variablenname | Wert |
+----------------------------+----------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
+----------------------------+----------+

Wenn MySQL Daten erstellt oder aktualisiert, zeichnet es ein Binärprotokoll auf. Häufige E/A-Vorgänge wirken sich jedoch erheblich auf die MySQL-Leistung aus. Daher hat MySQL einen Binärprotokoll-Cache binlog_cache_size geöffnet. Zuerst wird der Vorgang in das Binärprotokoll geschrieben, und wenn der Vorgang erfolgreich ist, wird das Binärprotokoll auf die Festplatte geschrieben.

2. InnoDB-Redo-Log-Cache

Bevor eine Transaktion festgeschrieben wird, werden die generierten Redo-Logs in den InnoDB-Redo-Log-Cache geschrieben. Anschließend wählt InnoDB [eine Möglichkeit], die Polling-Strategie auszuführen und die Redo-Log-Dateien im Cache in die Redo-Logs ib_logfile0 und ib_logfile1 zu schreiben.

mysql> Variablen wie „innodb_log_buffer_size“ anzeigen;
+------------------------+---------+
| Variablenname | Wert |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+

Der InnoDB-Redo-Log-Cache kann sicherstellen, dass die während der Transaktion generierten Redo-Logs vor dem Commit der Transaktion im InnoDB-Log-Cache gespeichert, jedoch nicht in die Redo-Log-Datei geschrieben werden. Der Zeitpunkt des Schreibens wird durch den Parameter innodb_flush_log_at_trx_commit gesteuert.

mysql> Variablen wie „innodb_flush_log%“ anzeigen;
+--------------------------------+----------+
| Variablenname | Wert |
+--------------------------------+----------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+----------+

0: Wenn die Redo-Log-Datei im Cache einmal pro Sekunde in den Disk-Cache geschrieben wird, wird sie gleichzeitig auch auf der Festplatte aktualisiert.

1: Bei jedem Commit einer Transaktion wird das Redo-Log im Cache in die Redo-Log-Datei geschrieben und gleichzeitig auf die Festplatte geschrieben. Dies ist das Standardverhalten.

2: Wenn eine Transaktion festgeschrieben wird, wird sie in den Cache geschrieben, löst jedoch keinen Synchronisierungsvorgang vom Dateisystem auf die Festplatte aus. Darüber hinaus wird die Festplatte einmal pro Sekunde synchronisiert.

9. Vorlesemechanismus

Der Vorlesemechanismus verwendet hauptsächlich die in der vorherigen MySQL-Optimierung beschriebenen Prinzipien: 1. Cache-Optimierung. Das heißt, lokale Merkmale, räumliche Lokalität und zeitliche Lokalität, auf die hier nicht näher eingegangen wird.

1. InnoDB-Read-Ahead-Mechanismus

InnoDB verwendet einen Read-Ahead-Mechanismus, um „Daten, auf die zukünftig zugegriffen werden soll“, einschließlich Indizes, in den Read-Ahead-Cache zu laden und so die Leistung beim Datenlesen zu verbessern. InnoDB unterstützt zwei Methoden: lineares Vorauslesen und zufälliges Vorauslesen.

Ein Datenblock (Seite) ist die kleinste Einheit der Festplattenverwaltung von InnoDB. Eine Zone besteht aus 64 aufeinanderfolgenden Datenblöcken. Für sequentielles Vorauslesen platziert InnoDB bevorzugt die Datenblöcke, in denen sich die Daten befinden, im InnoDB-Cache-Pool. Es kann vorhergesagt werden, dass die nachfolgenden Blöcke dieser Datenblöcke bald aufgerufen werden, sodass diese Datenblöcke und die vorhergehenden Datenblöcke im Speicher platziert werden. Legen Sie die Anzahl der Datenblöcke fest, die basierend auf dem Parameter innodb_read_ahead_threshold vorausgelesen werden sollen.

mysql> Variablen wie „innodb_read_ahead%“ anzeigen;
+-----------------------------+----------+
| Variablenname | Wert |
+-----------------------------+----------+
| innodb_read_ahead_threshold | 56 |
+-----------------------------+----------+

2. Vorladen des Index-Cache

Datenbankadministratoren können den MySQL-Befehl „load index into cache“ verwenden, um MyISAM-Tabellenindizes vorab zu laden.

10. Verzögertes Einfügen von MyISAM-Tabellen

mysql> Variablen wie „%delayed%“ anzeigen;
+----------------------------+----------+
| Variablenname | Wert |
+----------------------------+----------+
| verzögertes_Einfügelimit | 100 |
| verzögertes_Einfügetimeout | 300 |
| verzögerte Warteschlangengröße | 1000 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
+----------------------------+----------+

Als ich diese Funktion zur verzögerten Einfügung sah, musste ich an eine ähnliche Funktion im Projekt denken, die mich zu meinen eigenen Ideen inspirierte.

Die Verwendung ist: insert delyed into table values(*);

verzögertes_Einfügelimit

Der Standardwert ist 100. Nachdem 100 Zeilen in die MySQL-Tabelle eingefügt wurden, prüfen Sie, ob in der Tabelle eine Select-Anweisung auf ihre Ausführung wartet. Wenn ja, unterbrechen Sie die Ausführung der Insert-Anweisung.

verzögertes_Einfüge-Timeout

Wenn sich innerhalb des Timeout-Bereichs keine Daten in der Verzögerungswarteschlange befinden, wird der Thread zur verzögerten Einfügung heruntergefahren.

verzögerte_Warteschlangengröße

Die Warteschlangenlänge für verzögerte Einfügungen. Bei Überschreitung erfolgt eine Blockierung, bis genügend Platz vorhanden ist.

max_delayed_threads

Die Anzahl der Threads für verzögerte Einfügungen.

Stapelverzögerte Einfügungen in MyISAM-Tabellen

Ähnlich wie „In Tabelle einfügen: Werte(1), Werte(2), Werte(n). MyISAM führt Batch-Einfügungen durch. Legen Sie die eingefügten Daten zunächst in den Cache. Wenn der Cache voll oder festgeschrieben ist, schreibt MySQL den Cache sofort auf die Festplatte. Durch Batch-Einfügen können die Verbindungssyntaxanalyse und andere Kosten zwischen dem MySQL-Client und dem Server erheblich reduziert werden, wodurch die Effizienz viel schneller erreicht wird, als wenn einzelne Einfügeanweisungen separat ausgeführt werden.

mysql> wähle @@global.bulk_insert_buffer_size/(1024*1024);
+-------------------------------------------------+
| @@global.bulk_insert_buffer_size/(1024*1024) |
+-------------------------------------------------+
| 8,0000 |
+-------------------------------------------------+

Die Standardgröße für Batcheinfügungen beträgt 8 MB. Falls für geschäftliche Zwecke erforderlich, können Sie den Wert größer einstellen, um die Stapeleinfügungsleistung zu verbessern.

Verzögerte Aktualisierung von Indizes für MyISAM-Tabellen

Indizes können den Datenabruf beschleunigen, aber für Aktualisierungen müssen Sie nicht nur die Datensätze ändern, sondern möglicherweise auch die Indizes. Daher verlangsamen Indizes die Datenaktualisierungsvorgänge. Wenn Sie den MySQL-Parameter delay_key_write auf 1 (ON) setzen, können Sie diesen Mangel ausgleichen. Wenn der Aktualisierungsvorgang aktiviert ist, wird die Datenaktualisierung bei Änderung der Daten zunächst an die Festplatte übermittelt und die Indexaktualisierung wird vollständig im Indexcache abgeschlossen. Beim Schließen der Tabelle wird diese gleichzeitig auf der Festplatte aktualisiert, damit der Index schneller aktualisiert werden kann. Nur gültig für MyISAM.

mysql> Variablen wie „delay_key_write“ anzeigen;
+-----------------+--------+
| Variablenname | Wert |
+-----------------+--------+
| Verzögerung_Schlüssel_Schreiben | EIN |
+-----------------+--------+

Verzögerte InnoDB-Updates

Aktualisierungsvorgänge an nicht gruppierten Indizes verursachen normalerweise zufällige E/A-Vorgänge und verringern die Leistung von InoDB. Beim Aktualisieren (Einfügen, Löschen, Aktualisieren = Einfügen + Löschen) der Daten eines nicht gruppierten Index wird zunächst geprüft, ob sich die nicht gruppierte Indexseite im InnoDB-Pufferpool befindet. Wenn dies der Fall ist, wird sie direkt aktualisiert. Andernfalls wird die „Informationsänderung“ zuerst im Aktualisierungspuffer aufgezeichnet.

Da dieser Blog sehr viele Inhalte enthält, fasse ich sie für eine spätere Überprüfung zusammen. Zunächst benötigen wir einen Gesamtrahmen für die Optimierung des gesamten MySQL, und dann können wir schrittweise Fortschritte machen. Diese Parameter müssen Sie sich nicht merken. Wenn Sie sie brauchen, schauen Sie einfach im Blog oder bei Baidu nach. Wenn Sie das Tao verstehen und die Technik kennen, können Sie den Optimierungsprozess abschließen. Es ist viel einfacher, das Prinzip zu kennen, als langweilige Prinzipien auswendig zu lernen. Blogger, die sich für MySQL-Optimierung interessieren, können meinem Blog folgen, um die nachfolgenden Beiträge zu sehen.