Tipps zum MySQL-Abfragecache

Vorwort

Wir wissen, dass das Cache-Designkonzept in RDBMS-Datenbanken allgegenwärtig ist. Nehmen wir beispielsweise die Oracle-Datenbank, die angeblich 25 Millionen Codezeilen enthält und voller Fehler ist. Der SQL-Ausführungsplan kann im Bibliothekscache zwischengespeichert werden, um hartes Parsen (syntaktische Analyse -> semantische Analyse -> Generierung des Ausführungsplans) zu vermeiden, wenn dasselbe SQL erneut ausgeführt wird. Die SQL-Ausführungsergebnisse werden in der Speicherkomponente RESULT CACHE zwischengespeichert, die physische IO effektiv in logische IO umwandelt und die Effizienz der SQL-Ausführung verbessert.

MySQLs QueryCache ähnelt dem von Oracle. Er speichert SQL-Anweisungstext und den entsprechenden Ergebnissatz im Cache. Das scheint eine großartige Idee zu sein. Warum ist er dann in MySQL 5.6 standardmäßig deaktiviert, in 5.7 veraltet und in Version 8.0 nach der Veröffentlichung von MySQL 4.0 entfernt? Lassen Sie uns heute über die Vergangenheit und Gegenwart von MySQL QueryCache sprechen.

Einführung in QueryCache

Der MySQL-Abfragecache (QC: QueryCache) wurde in MySQL 4.0.1 eingeführt. Der Abfragecache speichert den Text von SELECT-Anweisungen und den an den Client gesendeten Ergebnissatz. Wenn dasselbe SQL erneut ausgeführt wird, ruft der Server das Ergebnis aus dem Abfragecache ab und gibt es an den Client zurück, anstatt das SQL erneut zu analysieren und auszuführen. Der Abfragecache wird zwischen Sitzungen gemeinsam genutzt, sodass der von einem Client generierte zwischengespeicherte Ergebnissatz einem anderen Client antworten kann, der dasselbe SQL ausführt.

Zurück zur Eingangsfrage: Wie lässt sich feststellen, ob SQL gemeinsam genutzt wird?

Der SQL-Text wird danach beurteilt, ob er vollständig konsistent ist, einschließlich Groß- und Kleinschreibung, Leerzeichen und anderen Zeichen, die genau gleich sind. Er kann geteilt werden. Der Vorteil der Freigabe besteht darin, dass hartes Parsen vermieden und die Ergebnisse direkt von QC abgerufen und an den Client zurückgegeben werden können. Die folgenden beiden SQLs werden nicht geteilt, da einer von und der andere von ist.

--SQL 1
Wählen Sie ID und Kontostand vom Konto mit der ID = 121;
--SQL 2
Wählen Sie ID, Kontostand vom Konto, wobei ID = 121;

Nachfolgend sehen Sie den Algorithmus, den die Oracle-Datenbank verwendet, um SQL_ID über SQL_TEXT zu generieren. Wenn die SQL_ID unterschiedlich ist, bedeutet dies, dass es sich nicht um dasselbe SQL handelt und es nicht gemeinsam genutzt wird. Außerdem wird eine harte Analyse durchgeführt.

#!/usr/bin/perl -w
verwenden Sie Digest::MD5 qw(md5 md5_hex md5_base64);
verwenden Sie Math::BigInt;
mein $stmt = "Wählen Sie ID, Kontostand vom Konto mit ID = 121\0"; 
mein $hash = md5 $stmt; 
mein($a,$b,$msb,$lsb) = auspacken("V*",$hash);
mein $sqln = $msb*(2**32)+$lsb;
mein $stop = log($sqln) / log(32) + 1;
mein $sqlid = '';
mein $charbase32 = '0123456789abcdfghjkmnpqrstuvwxyz';
meine @chars = geteilt '', $charbase32;
für($i=0; $i < $stop-1; $i++){
 mein $x = Math::BigInt->new($sqln);
 meine $seq = $x->bdiv(32**$i)->bmod(32);
 : $sqlid = $chars[$seq].$sqlid;
}
drucken "SQL ist:\n $stmt \nSQL_ID ist\n $sqlid\n";

Sie können feststellen, dass die vom Code in SQL 1 und SQL 2 generierten SQL_ID-Werte unterschiedlich sind und daher nicht gemeinsam genutzt werden.

SQL lautet: wähle ID, Kontostand vom Konto mit ID = 121 
SQL_ID ist dm5c6ck1g7bds
SQL lautet: select id, balance Von Konto mit id = 121 
SQL_ID ist 6xb8gvs5cmc9b

Wenn Sie aufgefordert werden, den Inhalt zweier Java-Codedateien zu vergleichen, um die Unterschiede zu erkennen, müssen Sie diesen Code nur gründlich verstehen und können ihn dann transformieren, um Ihre eigene Geschäftslogik zu implementieren.

QueryCache-Konfiguration

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

query_cache_min_res_unit Beschreibung

Die Standardgröße beträgt 4 KB. Wenn Sie viele Abfragen mit kleinen Ergebnismengen haben, kann die standardmäßige Blockgröße zu einer Speicherfragmentierung führen. Die Fragmentierung kann dazu führen, dass der Abfragecache Abfragen aufgrund von Speichermangel aus dem Cache entfernt.

In diesem Fall können Sie den Wert von query_cache_min_res_unit reduzieren. Die Anzahl der durch das Bereinigen entfernten freien Blöcke und Abfragen wird durch die Werte der Statusvariablen Qcache_free_blocks und Qcache_lowmem_prunes angegeben. Wenn eine große Anzahl von Abfragen große Ergebnismengen hat, können Sie den Wert dieses Parameters erhöhen, um die Leistung zu verbessern.

Normalerweise geöffneter QueryCache-Modus

# Ändern Sie die MySQL-Konfigurationsdatei /etc/my.cnf, fügen Sie die folgende Konfiguration hinzu und starten Sie den MySQL-Server neu.
[mysqld]
Abfrage-Cachegröße = 32 M
query_cache_type = 1

QueryCache-Nutzung

Lassen Sie uns zunächst einige Testdaten abrufen und die Szenarien mit deaktiviertem und aktiviertem QueryCache testen.

--Erstellen Sie eine Benutzertabelle „Benutzer“ und fügen Sie 1 Million Daten ein.
CREATE TABLE `Benutzer` (
 `id` bigint NICHT NULL AUTO_INCREMENT,
 `name` varchar(20) NICHT NULL STANDARD '' KOMMENTAR 'Name',
 `Alter` tinyint NICHT NULL STANDARD '0' KOMMENTAR 'Alter',
 `Geschlecht` char(1) NICHT NULL STANDARD 'M' KOMMENTAR 'Geschlecht',
 `phone` varchar(16) NOT NULL DEFAULT '' COMMENT 'Telefonnummer',
 `create_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
 `update_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Änderungszeit',
 PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Benutzerinformationstabelle';

Wählen Sie count(*) aus den Benutzern aus.
+----------+
| Anzahl(*) |
+----------+
| 1000000 |

Szenario „QueryCache deaktivieren“

Wenn QueryCache nicht verwendet wird, erfolgt bei jeder Ausführung derselben Abfrageanweisung eine harte Analyse, was viele Ressourcen verbraucht.

#QueryCache-Konfiguration deaktivieren query_cache_size = 0
query_cache_type = 0

Wiederholen Sie die folgende Abfrage und beobachten Sie die Ausführungszeit.

--Erste Ausführung der Abfrageanweisungmysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+----------+---------------------+---------------------+
| ID | Name | Alter | Geschlecht | Telefon | Erstellungszeit | Aktualisierungszeit |
+---------+------------+-----+--------+----------+---------------------+---------------------+
| 997855 | Benutzer997854 | 54 | M | 15240540354 | 15.12.2020 14:34:50 | 15.12.2020 14:34:50 |
.......
10 Zeilen im Satz (0,89 Sek.)
--Führen Sie dieselbe Abfrage ein zweites Mal ausmysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+----------+---------------------+---------------------+
| ID | Name | Alter | Geschlecht | Telefon | Erstellungszeit | Aktualisierungszeit |
+---------+------------+-----+--------+----------+---------------------+---------------------+
| 997855 | Benutzer997854 | 54 | M | 15240540354 | 15.12.2020 14:34:50 | 15.12.2020 14:34:50 |
.......
10 Reihen im Satz (0,90 Sek.)
-- Profilverfolgung MySQL > Profil-CPU anzeigen, E/A blockieren für Abfrage 1; 
+----------------------+----------+----------+------------+--------------+---------------+---------------+
| Status | Dauer | CPU_Benutzer | CPU_System | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+---------------+
| Vorbereitung | 0,000022 | 0,000017 | 0,000004 | 0 | 0 |
| Sortierergebnis | 0,000014 | 0,000009 | 0,000005 | 0 | 0 |
| wird ausgeführt | 0,000011 | 0,000007 | 0,000004 | 0 | 0 |
| Daten werden gesendet | 0,000021 | 0,000016 | 0,000004 | 0 | 0 |
| Sortierindex wird erstellt | 0,906290 | 0,826584 | 0,000000 | 0 | 0 |

Es ist ersichtlich, dass die Ausführungszeit für die mehrmalige Ausführung derselben SQL-Abfrageanweisung etwa 0,89 Sekunden beträgt, fast kein Unterschied besteht. Gleichzeitig wird die Zeit hauptsächlich in der Phase „Sortierindex erstellen“ verbraucht.

Szenario „QueryCache aktivieren“

Wenn die Abfragezwischenspeicherung aktiviert ist, werden der SQL-Text und die Abfrageergebnisse bei der ersten Ausführung der Abfrageanweisung in QC zwischengespeichert. Bei der nächsten Ausführung derselben SQL-Anweisung werden die Daten aus QC abgerufen und an den Client zurückgegeben.

#QueryCache-Konfiguration deaktivieren query_cache_size = 32M
query_cache_type = 1

--Erste Ausführung der Abfrageanweisungmysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+----------+---------------------+---------------------+
| ID | Name | Alter | Geschlecht | Telefon | Erstellungszeit | Aktualisierungszeit |
+---------+------------+-----+--------+----------+---------------------+---------------------+
| 997855 | Benutzer997854 | 54 | M | 15240540354 | 15.12.2020 14:34:50 | 15.12.2020 14:34:50 |
.......
10 Zeilen im Satz (0,89 Sek.)
--Führen Sie die Abfrageanweisung ein zweites Mal ausmysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+----------+---------------------+---------------------+
| ID | Name | Alter | Geschlecht | Telefon | Erstellungszeit | Aktualisierungszeit |
+---------+------------+-----+--------+----------+---------------------+---------------------+
| 997855 | Benutzer997854 | 54 | M | 15240540354 | 15.12.2020 14:34:50 | 15.12.2020 14:34:50 |
.......
10 Zeilen im Satz (0,00 Sek.)
-- Profilverfolgung datamysql> Profil-CPU anzeigen, E/A blockieren für Abfrage 3;
+---------------------------------+----------+----------+------------+--------------+---------------+
| Status | Dauer | CPU_Benutzer | CPU_System | Block_ops_in | Block_ops_out |
+---------------------------------+----------+----------+------------+--------------+---------------+
| Warte auf Abfrage-Cache-Sperre | 0,000016 | 0,000015 | 0,000001 | 0 | 0 |
| Abfrage-Cache für Abfrage prüfen | 0,000007 | 0,000007 | 0,000000 | 0 | 0 |
| Berechtigungen für zwischengespeicherte Dateien werden überprüft | 0,000004 | 0,000003 | 0,000000 | 0 | 0 |
| Berechtigungen werden geprüft | 0,000034 | 0,000033 | 0,000001 | 0 | 0 |
| zwischengespeichertes Ergebnis an Client senden | 0,000018 | 0,000017 | 0,000001 | 0 | 0 |

Es ist ersichtlich, dass beim ersten Ausführen von QueryCache kein SQL-Text und keine SQL-Daten zwischengespeichert werden und die Ausführungszeit 0,89 s beträgt. Da QC aktiviert ist, werden der SQL-Text und die Ausführungsergebnisse in QC zwischengespeichert. Beim zweiten Ausführen derselben SQL-Abfrageanweisung wird QC direkt aufgerufen und die Daten werden ohne harte Analyse zurückgegeben, sodass die Ausführungszeit auf 0 s reduziert wird. Aus dem Profil können wir ersehen, dass das Senden des zwischengespeicherten Ergebnisses an den Client die Daten in QC direkt an den Client zurücksendet.

Abfrage-Cache-Trefferquote

Cache-bezogene Statusvariablen abfragen

mysql>GLOBALEN STATUS ANZEIGEN WIE 'QCache\_%';
+-------------------------+----------+
| Variablenname | Wert |
+-------------------------+----------+
| Qcache_free_blocks | 1 | – Die Anzahl der freien Speicherblöcke im Abfragecache.
| Qcache_free_memory | 33268592 | – Menge an freiem Speicher für den Abfrage-Cache.
| Qcache_hits | 121 | – Die Häufigkeit, mit der der Ergebnisset von QC abgerufen wird.
| Qcache_inserts | 91 | – Die Häufigkeit, mit der ein Abfrageergebnissatz zum QC hinzugefügt wurde, was bedeutet, dass sich die Abfrage nicht mehr im QC befindet.
| Qcache_lowmem_prunes | 0 | – Anzahl der Abfragen, die aufgrund von Speichermangel aus dem Abfragecache entfernt wurden.
| Qcache_not_cached | 0 | – Anzahl der nicht zwischengespeicherten Abfragen.
| Qcache_queries_in_cache | 106 | – Die Anzahl der im Abfragecache registrierten Abfragen.
| Qcache_total_blocks | 256 | – Gesamtzahl der Blöcke im Abfragecache.

Abfrage-Cache-Trefferquote und durchschnittliche Größe

           Qcache_Hits
Abfrage-Cache-Trefferquote = ------------------------------------------------ x 100 %
      Qcache_Treffer + Qcache_Einfügungen + Qcache_nicht_zwischengespeichert
      
        query_cache_size = Qcache_freier_Speicher
Abfrage-Cache Durchschnittliche Abfragegröße = --------------------------------------- 
          Qcache_queries_in_cache

Auswirkungen von Aktualisierungsvorgängen auf die Qualitätskontrolle

Beispielsweise müssen Sie in der Überweisungslogik des Zahlungssystems zuerst das Konto sperren und dann den Kontostand ändern. Die wichtigsten Schritte sind wie folgt:

Warum QueryCache aufgeben?

Allgemeine Geschäftsszenarien

Für QueryCache geeignete Szenarien

Erstens beträgt die Größe des Abfragecaches QC nur wenige MB, daher ist es nicht sinnvoll, den Cache zu groß einzustellen. Da der Thread den Abfragecache während des Aktualisierungsvorgangs sperren muss, kann es bei sehr großen Caches zu Sperrkonflikten kommen. In welchen Situationen kann man also am besten vom Abfrage-Caching profitieren? Ideale Voraussetzungen sind:

1. Die gleiche Abfrage wird wiederholt vom gleichen oder von mehreren Clients gestellt.
2. Die zugrunde liegenden Daten, auf die zugegriffen wird, sind statischer oder halbstatischer Natur.
3. Die Abfrage kann ressourcenintensiv sein und/oder einen kurzen, aber rechnerisch komplexen Ergebnissatz erstellen, während der Ergebnissatz klein ist.
4. Gleichzeitigkeit und Abfrage-QPS sind nicht hoch.

Diese vier Situationen sind lediglich Idealsituationen. Tatsächliche Geschäftssysteme verfügen alle über CRUD-Operationen, Datenaktualisierungen sind relativ häufig und die QPS der Abfrageschnittstelle ist relativ hoch. Daher gibt es nur sehr wenige Geschäftsszenarien, die die oben genannten Idealsituationen erfüllen können. Was mir einfällt, ist, dass Konfigurationstabellen und Datenwörterbuchtabellen grundsätzlich statisch oder halbstatisch sind und QC verwendet werden kann, um die Abfrageeffizienz zu verbessern.

Für QueryCache nicht geeignete Szenarios

Wenn sich die Tabellendaten schnell ändern, wird der Abfragecache ungültig und die Serverlast steigt, was dazu führt, dass die Verarbeitung aufgrund des ständigen Entfernens von Abfragen aus dem Cache langsamer wird. Wenn die Daten alle paar Sekunden oder häufiger aktualisiert werden, ist der Abfragecache wahrscheinlich nicht geeignet.

Gleichzeitig verwendet der Abfragecache einen einzelnen Mutex, um den Zugriff auf den Cache zu steuern, wodurch der SQL-Verarbeitungs-Engine des Servers tatsächlich ein Single-Thread-Gateway aufgezwungen wird. Wenn die Abfrage-QPS relativ hoch ist, kann dies zu einem Leistungsengpass werden und die Abfrageverarbeitungsgeschwindigkeit erheblich verringern. Daher ist der Abfragecache in MySQL 5.6 standardmäßig deaktiviert.

QueryCache löschen

Der Abfragecache ist ab MySQL 5.7.20 veraltet und wird in MySQL 8.0 entfernt. Die Veraltung umfasst query_cache_type. Von der standardmäßigen Deaktivierung von MySQL 5.6 über die Aufgabe von 5.7 bis hin zur vollständigen Entfernung von 8.0 hat Oracle diese Entscheidung nach umfassender Abwägung verschiedener Aspekte getroffen.

Wir haben oben die für QueryCache geeigneten und ungeeigneten Geschäftsszenarien besprochen. Wir haben festgestellt, dass diese Funktion zu hohe Anforderungen an Geschäftsszenarien stellt und sich nur schwer mit dem tatsächlichen Geschäft vereinbaren lässt. Darüber hinaus werden nach der Aktivierung die Datenbank-Parallelität und die Verarbeitungskapazität erheblich reduziert. Im Folgenden werden die Hauptgründe zusammengefasst, warum MySQL von Deaktiviert->Veraltet->Abgerufen-QueryCache entfernt wurde.

Gleichzeitig erhöht die Fragmentierung des Abfragecaches auch die Serverlast und beeinträchtigt die Stabilität der Datenbank. Die Suche nach QueryCache auf der offiziellen Website von Oracle zeigt, dass es viele Fehler gibt, weshalb MySQL 8.0 diese Funktion direkt und entschieden entfernt.

Zusammenfassen

Oben wird der Prozess von MySQL QueryCache von Starten -> Deaktivieren -> Aufgeben -> Löschen vorgestellt. Ursprünglich wurde er entwickelt, um den durch wiederholte SQL-Abfragen verursachten Aufwand bei der harten Analyse zu reduzieren und physische IO in logische IO umzuwandeln, um die Effizienz der SQL-Ausführung zu verbessern. MySQL hat jedoch mehrere Iterationen durchlaufen. Gleichzeitig bietet QC angesichts der rasanten Entwicklung des Hardwarespeichers heute fast keine Vorteile und verringert auch die gleichzeitige Verarbeitungskapazität der Datenbank. Schließlich wurde es in Version 8.0 direkt entfernt.

Tatsächlich gibt es überall Ideen für das Cache-Design in den Bereichen Hardware und Software. In Bezug auf die Hardware: RAID-Karten und CPUs haben ihre eigenen Caches. In Bezug auf die Software gibt es zu viele, darunter OS-Cache, Datenbankpufferpool und Java-Programm-Cache. Als F&E-Ingenieur ist es sehr wichtig, je nach Geschäftsszenario eine geeignete Cache-Lösung auszuwählen. Wenn keine davon geeignet ist, müssen Sie die Cache-Entwicklung anpassen, damit sie besser zu Ihrem eigenen Geschäftsszenario passt. Das ist alles für heute, und ich hoffe, es wird für alle hilfreich sein.

Ich bin Ao Bing. Je mehr man weiß, desto weniger weiß man. Vielen Dank an alle für eure Likes, Favoriten und Kommentare. Bis zum nächsten Mal!

Oben finden Sie detaillierte Informationen zum MySQL-Abfragecache. Weitere Informationen zum MySQL-Abfragecache finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!