Grafische Erklärung des MySQL-Abfragecaches

1. Prinzipübersicht

Der Abfrage-Cache dient zum Zwischenspeichern der „von der Abfrage zurückgegebenen Ergebnisse“. Es ist jedoch nicht richtig, einfach „die von der Abfrage zurückgegebenen Ergebnisse“ zwischenzuspeichern. Query Cache speichert auch Abfrageanweisungen zwischen und bildet „Abfrageanweisungen“ und „die von der Abfrage zurückgegebenen Ergebnisse“ im Speicher ab.
Wenn MySQL eine SELECT-Abfrageanweisung empfängt, führt es eine Hash-Berechnung für die Abfrageanweisung durch, um einen Hash-Wert zu erhalten. Anschließend wird der Hashwert zum Abgleichen mit dem Abfrage-Cache verwendet. Wenn kein passendes Ergebnis erzielt wird, wird der Hash-Wert in einer Hash-verknüpften Liste gespeichert und das Ergebnis der Abfrage wird im Cache gespeichert. Jeder Knoten der verknüpften Liste, der Hash-Werte speichert, speichert die Adresse des entsprechenden Abfrage-Rückgabeergebnisses im Cache sowie relevante Informationen zu einigen an der Abfrage beteiligten Tabellen. Bei einer Übereinstimmung mit der gleichen Abfrage über den Hashwert wird das entsprechende Abfrageergebnis im Cache direkt an den Client zurückgegeben. Wenn sich Daten in einer beliebigen MySQL-Tabelle ändern, wird der Abfrage-Cache benachrichtigt, alle mit dieser Tabelle verknüpften Abfrage-Caches werden ungültig und der belegte Speicherplatz wird freigegeben. Zu den Vorgängen, die Tabellendaten ändern, gehören: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE und DROP DATABASE usw.

Das Funktionsprinzip des Abfragecaches lautet: Der schnellste Weg, eine Abfrage auszuführen, besteht darin, sie nicht auszuführen. Das Komponentendiagramm und das Flussdiagramm von Query Cache sind wie folgt:

Query Cache-Systemvariablen

Die wichtigsten konfigurierbaren Systemvariablen für den Abfrage-Cache sind wie folgt:

1. have_query_cache

Gibt an, ob mysqld Query Cache unterstützt.

2. query_cache_limit

Gibt die maximale Ergebnissatzgröße einer einzelnen Abfrage an, die vom Abfrage-Cache zwischengespeichert werden kann. Der Standardwert ist 1 MB. Wenn die Größe des Ergebnissatzes einer Abfrage den Wert dieser Systemvariable überschreitet, speichert der Abfrage-Cache den Ergebnissatz dieser Abfrage nicht im Cache.

3. query_cache_min_res_unit

Gibt die Mindestgröße des Speichers an, den MySQL jedes Mal für den Abfragecache reserviert, d. h. die Mindestgröße des Speicherplatzes, der zum Zwischenspeichern von Abfrageergebnissen verwendet wird. Der Standardwert ist 4 KB.

4. Abfrage-Cachegröße

Gibt die maximale Speichergröße an, die vom Abfrage-Cache verwendet werden kann. Der Standardwert ist 1 MB. Der Wert muss ein ganzzahliges Vielfaches von 1024 sein. Wenn es kein ganzzahliges Vielfaches ist, passt MySQL ihn automatisch auf den Maximalwert an, der ein Vielfaches von 1024 ist.

5. Abfrage-Cache-Typ

Gibt den Arbeitsmodus des Abfragecaches an und schaltet die Abfragecache-Funktion ein und aus. Der Wert kann auf 0 (AUS), 1 (EIN) und 2 (ANFORDERUNG) eingestellt werden:

• 0 (AUS): Deaktiviert die Abfrage-Cache-Funktion. Der Abfrage-Cache wird unter keinen Umständen verwendet.

• 1 (EIN): Aktiviert die Abfrage-Cache-Funktion. Wenn jedoch die Option SQL_NO_CACHE in der SELECT-Anweisung verwendet wird, wird der Abfrage-Cache nicht verwendet.

• 2 (ANFORDERUNG): Aktivieren Sie die Abfrage-Cache-Funktion, verwenden Sie den Abfrage-Cache jedoch nur, wenn die Option SQL_CACHE in der SELECT-Anweisung verwendet wird.

6. query_cache_wlock_invalidate

Steuert, ob der mit der Tabelle verknüpfte Abfragecache zuerst ungültig gemacht werden soll, wenn der Tabelle eine Schreibsperre hinzugefügt wird. Es gibt zwei Werte: 1 (EIN) und 0 (AUS):

• 1 (EIN): Alle mit der Tabelle verbundenen Abfrage-Caches werden beim Schreiben der Sperre ungültig.

• 0 (AUS): Das Lesen des mit der Tabelle verknüpften Abfragecaches ist während des Schreibens der Sperre weiterhin zulässig.

Die Umgebungsvariablen, die häufig angepasst werden müssen, sind query_cache_limit und query_cache_min_res_unit. Beide müssen basierend auf den tatsächlichen Geschäftsanforderungen entsprechend angepasst werden. Wenn beispielsweise die meisten zwischengespeicherten Abfrageergebnissätze kleiner als 4 KB sind, können Sie den Wert von query_cache_min_res_unit entsprechend anpassen, um eine Speicherverschwendung zu vermeiden. Wenn die Größe des Abfrageergebnissatzes 1 MB überschreitet, müssen Sie den Wert von query_cache_limit anpassen, um zu verhindern, dass der Ergebnissatz zwischengespeichert wird, weil die Größe den Grenzwert überschreitet.

Abfrage-Cache-Statusvariablen

MySQL stellt eine Reihe von Statusvariablen bereit, um den aktuellen Status des Abfragecaches aufzuzeichnen. So können Sie feststellen, ob der Abfragecache ordnungsgemäß ausgeführt wird, wie hoch die Trefferquote ist, ob ausreichend Speicherplatz vorhanden ist usw. Die Statusvariablen des Abfragecaches sind wie folgt:

1. Qcache_free_blocks

Gibt an, wie viele freie Speicherblöcke sich derzeit im Query Cache befinden. Wenn dieser Wert groß ist, bedeutet dies, dass im Abfragecache möglicherweise eine starke Speicherfragmentierung vorliegt. FLUSH QUERY CACHE defragmentiert den Cache, um einen größeren freien Speicherblock zu erstellen.

2. Qcache_free_memory

Zeigt die aktuelle freie Speichergröße des Abfragecaches an.

3. Qcache_hits

Gibt an, wie viele Abfragen den Abfrage-Cache treffen.

4. Qcache_inserts

Die Häufigkeit, mit der neue Datensätze in den Abfrage-Cache eingefügt wurden, d. h. die Häufigkeit, mit der die Abfrage keinen Treffer ergab.

5. Qcache_lowmem_prunes

Gibt die Anzahl der Abfrageergebnisse an, die aufgrund von zu wenig Speicher im Abfragecache aus dem Cache entfernt wurden. Wenn dieser Wert ständig ansteigt, bedeutet dies normalerweise, dass der freie Speicher des Abfragecaches nicht ausreicht (beurteilt durch Qcache_free_memory) oder der Speicher stark fragmentiert ist (beurteilt durch Qcache_free_blocks).

6. Qcache_nicht_gecached

Gibt die Anzahl der Abfragen an, die nicht zwischengespeichert werden. Es gibt drei Situationen, in denen Abfrageergebnisse nicht zwischengespeichert werden: erstens aufgrund der Einstellung von query_cache_type; zweitens ist die Abfrage keine SELECT-Anweisung; drittens wird eine Funktion wie now() verwendet, wodurch sich die Abfrageanweisung ständig ändert.

7. Qcache_queries_in_cache

Gibt die Anzahl der Abfrageergebnisse an, die derzeit im Abfragecache enthalten sind.

8. Qcache_total_blocks

Gibt die Gesamtzahl der Speicherblöcke im Abfragecache an.

4. Vorteile und Nachteile

1. Vorteile

Abfragen des Abfragecaches erfolgen, nachdem MySQL die Abfrageanforderung des Clients empfangen und die Abfrageberechtigungen überprüft hat, bevor das Abfrage-SQL analysiert wird. Das heißt, wenn MySQL die SQL-Abfrage vom Client empfängt, muss es nur die entsprechenden Berechtigungen überprüfen und dann über den Abfragecache nach den Ergebnissen suchen. Es muss nicht einmal den Ausführungsplan über das Optimizer-Modul analysieren und optimieren und erfordert keine Interaktion mit der Speicher-Engine. Da der Query Cache speicherbasiert ist und die entsprechenden Abfrageergebnisse direkt aus dem Speicher zurückgibt, werden viele Festplatten-E/A- und CPU-Berechnungen reduziert, was zu einer sehr hohen Effizienz führt.

2. Nachteile

Auch wenn die Vorteile von Query Cache offensichtlich sind, können einige seiner Nachteile nicht ignoriert werden:

• Ressourcenverbrauch durch Hash-Berechnung und Hash-Suche von Abfrageanweisungen. Wenn query_cache_type auf 1 (also EIN) gesetzt ist, führt MySQL für jede empfangene Abfrage vom Typ SELECT Hash-Berechnungen durch und prüft anschließend, ob das zwischengespeicherte Ergebnis dieser Abfrage vorhanden ist. Obwohl die Effizienz der Hash-Berechnung und -Suche hoch genug ist und der Overhead einer Abfrageanweisung ignoriert werden kann, muss der Overhead der Hash-Berechnung und -Suche ernst genommen werden, sobald eine hohe Parallelität vorliegt und Zehntausende von Abfrageanweisungen vorhanden sind.

• Problem mit dem Ablauf des Abfragecaches. Wenn sich die Tabelle häufig ändert, ist die Fehlerrate des Abfragecaches sehr hoch. Tabellenänderungen beziehen sich nicht nur auf Änderungen der Daten in der Tabelle, sondern umfassen auch alle Änderungen in der Tabellenstruktur oder den Indizes.

• Abfragen mit unterschiedlichen Abfrageanweisungen, aber denselben Abfrageergebnissen werden zwischengespeichert, was zu einem übermäßigen Verbrauch von Speicherressourcen führt. Wenn die Abfrageanweisungen unterschiedliche Groß- und Kleinschreibung, Leerzeichen oder Kommentare aufweisen, betrachtet Query Cache sie als unterschiedliche Abfragen (da ihre Hash-Werte unterschiedlich sind).

• Falsche Einstellungen der entsprechenden Systemvariablen führen zu einer starken Speicherfragmentierung, was wiederum zu häufigem Löschen des Abfrage-Cache führt.

5. Häufig gestellte Fragen

1. Unter welchen Bedingungen sollte Query Cache verwendet werden?

Tatsächlich sind nicht alle Tabellen für die Verwendung des Abfragecaches geeignet. Der Hauptgrund für einen Abfragecache-Fehler ist, dass sich die entsprechende Tabelle geändert hat. Daher sollten Sie die Verwendung des Abfragecaches bei Tabellen vermeiden, die sich häufig ändern. MySQL hat zwei dedizierte SQL-Optionen für den Abfragecache: SQL_NO_CACHE und SQL_CACHE. Wenn query_cache_type auf 1 (Ein) gesetzt ist, kann die Option SQL_NO_CACHE verwendet werden, um die Verwendung des Abfragecaches zu erzwingen. Wenn query_cache_type auf 2 (Nachfrage) gesetzt ist, kann die Option SQL_CACHE verwendet werden, um die Verwendung des Abfragecaches zu erzwingen. Indem MySQL die Verwendung des Abfragecaches erzwingt, kann es die Verwendung des Abfragecaches bei sich häufig ändernden Tabellen verhindern. Dadurch wird der Speicheraufwand sowie der Aufwand für die Hash-Berechnung und Suche reduziert.

2. Welche Beziehung besteht zwischen Abfrage-Cache und Abfrageanweisungen?

Unabhängig davon, ob die von MySQL empfangene Abfrageanweisung eine Einzeltabellen- oder Mehrtabellen-SQL-Anweisung ist oder eine Unterabfrage enthält, wird sie als Abfrage behandelt und nicht zum Zwischenspeichern in mehrere Abfragen aufgeteilt, einschließlich Union-Anweisungen.

3. Hat die Groß-/Kleinschreibung der vom Client übermittelten Abfrageanweisung Auswirkungen auf den Abfragecache?

einflussreich. Da der Abfragecache mithilfe einer Hash-Struktur im Speicher abgebildet wird, sind die Zeichen, aus denen die Abfrageanweisung besteht, die Grundlage des Hash-Algorithmus. Daher muss die gesamte Abfrageanweisung auf Zeichenebene vollständig konsistent sein, um den Abfragecache zu erreichen.

4. Unter welchen Umständen werden die im Abfragecache einer Abfrageanweisung zwischengespeicherten Abfrageergebnisse ungültig?

Um sicherzustellen, dass der Inhalt des Abfrage-Cache absolut mit den tatsächlichen Daten übereinstimmt, werden bei jeder Änderung der Daten in der Tabelle, einschließlich Hinzufügung, Änderung, Löschung usw., alle im Abfrage-Cache zwischengespeicherten Daten, die auf die Tabelle verweisen, ungültig.

5. Wie hoch ist die Fragmentierungsrate des Abfragecaches? Was nützt das?

Fragmentierungsrate des Abfragecaches = Qcache_free_blocks / Qcache_total_blocks * 100 %
Wenn die Fragmentierungsrate des Abfragecaches 20 % übersteigt, können Sie den Speicher mit FLUSH QUERY CACHE defragmentieren. Wenn Ihre Abfragen alle kleine Datenmengen umfassen, können Sie versuchen, query_cache_min_res_unit zu reduzieren.

6. Wie hoch ist die Abfrage-Cache-Auslastung? Was nützt das?

Abfrage-Cache-Auslastung = (Abfrage-Cache-Größe – freier Qcache-Speicher) / Abfrage-Cache-Größe * 100 %

Wenn die Auslastung des Abfragecaches unter 25 % liegt, bedeutet dies, dass die query_cache_size zu groß eingestellt ist und entsprechend reduziert werden kann; wenn die Auslastung des Abfragecaches über 80 % liegt und Qcache_lowmem_prunes > 50 ist, bedeutet dies, dass die query_cache_size möglicherweise etwas zu klein ist oder die Speicherfragmentierung zu stark ist.

7. Wie hoch ist die Trefferquote des Abfrage-Cache? Was nützt das?

① Abfrage-Cache-Trefferquote zwischenspeicherbarer Abfragen = Qcache_hits / (Qcache_hits + Qcache_inserts) * 100 %
② Abfrage-Cache-Trefferquote für alle Abfragen = Qcache_hits / (Qcache_hits + Com_select) * 100 %

Wenn die Trefferquote im Bereich zwischen 50 und 70 % liegt, ist die Cache-Effizienz des Query Cache hoch. Wenn die Trefferquote deutlich unter 50 % liegt, empfiehlt es sich, den Query Cache bei Bedarf zu deaktivieren (Query_cache_type auf 0 (OFF) setzen) oder zu verwenden (Query_cache_type auf 2 (DEMAND) setzen). Der eingesparte Speicher kann als Pufferpool von InnoDB verwendet werden.

8. Wie kann ermittelt werden, ob der Abfrage-Cache nicht genügend freien Speicher hat oder die Speicherfragmentierung zu stark ist?

Wenn der Wert Qcache_lowmem_prunes groß ist, bedeutet dies, dass die Speichergröße des Abfragecaches zu klein ist und erhöht werden muss.

Wenn der Wert von Qcache_free_blocks relativ groß ist, bedeutet dies, dass eine starke Speicherfragmentierung vorliegt und Sie die Anweisung FLUSH QUERY CACHE verwenden müssen, um die Speicherfragmentierung zu bereinigen.

9. Auf welchen Wert sollte die Systemvariable query_cache_min_res_unit eingestellt werden?

Die Berechnungsformel für query_cache_min_res_unit lautet wie folgt:

query_cache_min_res_unit = (Abfrage-Cache-Größe – freier Qcache-Speicher) / Qcache_queries_in_cache

Es wird im Allgemeinen nicht empfohlen, die Abfrage-Cache-Größe (d. h. die Systemvariable query_cache_size) auf mehr als 256 MB einzustellen.

Die Funktion des MySQL-Abfragecaches Query_cache

Der Abfragecache von MySQL speichert keine Ausführungspläne, sondern Abfragen und deren Ergebnismengen. Dies bedeutet, dass nur identische Abfragevorgänge den Cache erreichen können, sodass die Trefferquote des Abfragecaches von MySQL sehr niedrig ist. Andererseits können bei Abfragen mit großen Ergebnismengen die Abfrageergebnisse direkt aus dem Cache gelesen werden, was die Abfrageeffizienz effektiv verbessert.

Wie legen Sie also die Cachegröße fest? Werfen wir einen Blick darauf:

1. In der MySQL-Client-Befehlszeile können wir überprüfen, ob der Cache aktiviert ist und wie die Größe des Caches eingestellt ist:

mysql> Variablen wie „%query_cache%“ anzeigen;
+------------------------------+-------------+
| Variablenname | Wert |
+------------------------------+-------------+
| habe_Abfragecache | JA |
| Abfrage-Cache-Limit | 1048576 |
| Abfrage-Cache_Mindestressource | 4096 |
| Abfrage-Cache-Größe | 16106127360 |
| Abfrage-Cache-Typ | EIN |
| query_cache_wlock_invalidate | AUS |
+------------------------------+-------------+
6 Zeilen im Satz (0,01 Sek.)

In:

query_cache_type: Gibt an, ob die Cache-Funktion aktiviert werden soll. Der Wert ist ON, OFF, DEMAND, der Standardwert ist ON
– Wenn der Wert OFF oder 0 ist, ist die Abfrage-Cache-Funktion deaktiviert;
- Wenn der Wert ON oder 1 ist, ist die Abfrage-Cache-Funktion aktiviert. Das SELECT-Ergebnis wird zwischengespeichert, wenn es die Cache-Bedingungen erfüllt. Andernfalls wird es nicht zwischengespeichert. Wenn SQL_NO_CACHE explizit angegeben ist, wird es nicht zwischengespeichert.
- Wenn der Wert DEMAND oder 2 ist, wird die Abfrage-Zwischenspeicherung bei Bedarf durchgeführt und nur SELECT-Anweisungen, die explizit SQL_CACHE angeben, werden zwischengespeichert; andere werden nicht zwischengespeichert.

query_cache_wlock_invalidate: Gibt an, ob zwischengespeicherte Ergebnisse zurückgegeben werden sollen oder ob auf den Abschluss des Schreibvorgangs gewartet werden soll, bevor die Tabelle gelesen wird, um die Ergebnisse abzurufen, wenn sich die Abfrage im Abfragecache befindet, während andere Clients in die MyISAM-Tabelle schreiben.

query_cache_limit gibt die Puffergröße an, die von einer einzelnen Abfrage verwendet werden kann, der Standardwert ist 1 M;

query_cache_min_res_unit ist die vom System zugewiesene Mindestgröße des Cacheblocks. Der Standardwert beträgt 4 KB. Das Festlegen eines großen Wertes ist gut für große Datenabfragen, aber wenn Ihre Abfragen alle kleine Datenabfragen sind, kann dies leicht zu Speicherfragmentierung und -verschwendung führen; query_cache_size: gibt die Größe des Caches an.

Nachdem wir die oben genannten Indikatoren verstanden haben, können wir sie in der MySQL-Konfigurationsdatei my.cnf festlegen. Starten Sie dann den Mysl-Server neu. Fügen Sie die Parameter unter [mysqld] hinzu. Im Allgemeinen sind query_cache_size und query_cache_type festgelegt.

2. Die obige Ansicht ist unsere Cache-Konfiguration, die im Allgemeinen den Wert aus der Konfigurationsdatei liest, aber manchmal müssen wir die aktuelle Datencache-Größe in MySQL in Echtzeit anzeigen.

mysql> Status wie „%qcache%“ anzeigen;
+-------------------------+----------+
| Variablenname | Wert |
+-------------------------+----------+
| Qcache_freie_blöcke | 1 |
| Qcache_freier_Speicher | 1031832 |
| Qcache_Hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_nicht_zwischengespeichert | 16489053 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 Zeilen im Satz (0,00 Sek.)

erklären:

Qcache_free_memory: Freier Speicher im Cache.

Qcache_total_blocks: Die Anzahl der Blöcke im Cache.

Qcache_lowmem_prunes: Die Häufigkeit, mit der der Cache nicht genügend Speicher hatte und bereinigt werden musste, um Platz für weitere Abfragen zu schaffen. Am besten betrachten Sie diese Zahl im Zeitverlauf. Steigt sie, kann dies auf eine sehr starke Fragmentierung oder zu wenig Speicher hinweisen. Um dies festzustellen, müssen Sie sich die beiden Indikatoren Qcache_free_blocks und Qcache_free_memory ansehen.

3. Leeren Sie den Cache:

Befehl zum Leeren des Abfragecaches

Durch die Vergrößerung des Caches können wir die Effizienz unserer Abfragen verbessern:

Hier ist ein Beispiel:

mysql> verwenden Sie mob_adn
Datenbank geändert
mysql> wähle count(*) aus creative_output;
+----------+
| Anzahl(*) |
+----------+
|87151154|
+----------+
1 Reihe im Satz (3 Min. 18,29 Sek.)

mysql> wähle count(*) aus creative_output;
+----------+
| Anzahl(*) |
+----------+
|87151154|
+----------+
1 Zeile im Satz (0,00 Sek.)

mysql> wähle count(*) aus creative_output;
+----------+
| Anzahl(*) |
+----------+
|87151154|
+----------+
1 Zeile im Satz (0,00 Sek.)

Sie sehen, dass das Caching wirklich leistungsstark ist.

Einige Schüler denken möglicherweise an den Parameter innodb_buffer_pool. Ich werde weiter über die Funktionen und Unterschiede zwischen den beiden sprechen.

Übrigens hier einige SQL-Optimierungen (sehr gut):

https://www.cnblogs.com/L-dongf/p/9163848.html

Dies ist das Ende dieses Artikels über MySQL Query Cache mit Bildern und Text. Weitere relevante Inhalte zu MySQL Query Cache finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!