MySQL-Serie 9 MySQL-Abfrage-Cache und -Index

Tutorial-Reihe

MySQL-Reihe: Grundlegende Konzepte der relationalen MySQL-Datenbank
MariaDB-Serverinstallation der MySQL-Reihe
MySQL Series II-Konfiguration für mehrere Instanzen
MySQL Serie 3 Grundlagen
MySQL Serie 4 SQL-Syntax
MySQL-Serie fünf Ansichten, gespeicherte Funktionen, gespeicherte Prozeduren, Trigger
MySQL Series 6-Benutzer und Autorisierung
MySQL Series 7 MySQL-Speicher-Engine
MySQL Serie 8 MySQL Server-Variablen
MySQL Series 10 MySQL-Transaktionsisolierung zur Implementierung der Parallelitätskontrolle
MySQL Series 11-Protokollierung
MySQL Serie 12 Backup und Wiederherstellung
MySQL Serie 13 MySQL-Replikation
MySQL Serie 14 MySQL Hochverfügbarkeitsimplementierung
MySQL-Serie 15: Allgemeine MySQL-Konfiguration und Leistungsstresstest

1. MySQL-Architektur

1. Anschlüsse
2. Verbindungspool, Sicherheitsauthentifizierung, Threadpool, Verbindungslimit, Speicherprüfung, Cache
3. SQL-Schnittstelle DML, DDL
4. SQL-Parser, der die Berechtigungen von SQL-Anweisungen überprüft und sie in Binärprogramme zerlegt
5. Optimizer, Zugriffspfade optimieren
6. Cache, Puffer
7. Speicher-Engine innodb
8. Dateisystem
9. Protokoll

Abfrage-Cache

1. SQL-Anweisungen

2. Abfrage-Cache

3. Parser

4. Baum analysieren

5. Vorverarbeitung

6. Finden Sie den besten Abfragepfad

7. Abfrageoptimierung SQL-Anweisung

8. Ausführungsplan

9. API-Aufrufe an Speicher-Engines

10. Daten aufrufen und Ergebnisse zurückgeben

Zwischenspeichern Sie den Ergebnissatz und die SQL-Anweisung des SELECT-Vorgangs oder der vorverarbeiteten Abfrage. Wenn eine neue SELECT-Anweisung oder eine vorverarbeitete Abfrageanweisung angefordert wird, fragen Sie zuerst den Cache ab, um festzustellen, ob ein verfügbarer Datensatzsatz vorhanden ist. Die Beurteilungskriterien sind: ob es genau mit der zwischengespeicherten SQL-Anweisung übereinstimmt, Groß-/Kleinschreibung beachten.

SQL-Anweisungen müssen nicht analysiert und ausgeführt werden. Natürlich muss zuerst die Syntax analysiert werden. Holen Sie Abfrageergebnisse direkt aus dem Abfragecache, um die Abfrageleistung zu verbessern.

Die Beurteilungsregeln des Abfragecaches sind nicht intelligent genug, was die Schwelle für die Verwendung des Abfragecaches erhöht und dessen Effizienz verringert. Die Verwendung des Abfragecaches erhöht die Kosten für die Überprüfung und Bereinigung der Datensätze im Abfragecache.

Welche Abfragen dürfen nicht zwischengespeichert werden:

• Der Parameter SQL_NO_CACHE wird der Abfrageanweisung hinzugefügt.
• Die Abfrageanweisung enthält Funktionen, die Werte abrufen, einschließlich benutzerdefinierter Funktionen wie NOW(), CURDATE(), GET_LOCK(), RAND(), CONVERT_TZ() usw.
• Fragen Sie die Systemdatenbank ab: mysql, information_schema. Verwenden Sie in Abfrageanweisungen Variablen auf SESSION-Ebene oder lokale Variablen in gespeicherten Prozeduren.
• Die Abfrageanweisung verwendet LOCK IN SHARE MODE- und FOR UPDATE-Anweisungen und ähnelt SELECT ...INTO-Anweisungen zum Exportieren von Daten.
• Abfragevorgänge für temporäre Tabellen; Abfrageanweisungen mit Warninformationen; Abfrageanweisungen, die keine Tabellen oder Ansichten beinhalten; Abfrageanweisungen, für die ein Benutzer nur Berechtigungen auf Spaltenebene hat;
• Wenn die Transaktionsisolationsebene serialisierbar ist, können nicht alle Abfrageanweisungen zwischengespeichert werden.

Cache-bezogene Servervariablen abfragen:

• query_cache_min_res_unit: Die minimale Zuordnungseinheit von Speicherblöcken im Abfragecache. Der Standardwert ist 4 KB. Ein kleinerer Wert reduziert die Verschwendung, führt jedoch zu häufigeren Speicherzuweisungsvorgängen. Ein größerer Wert führt zu Verschwendung, übermäßiger Fragmentierung und unzureichendem Speicher.
• query_cache_limit: Der Maximalwert, der für ein einzelnes Abfrageergebnis zwischengespeichert werden kann. Der Standardwert ist 1 M. Für Anweisungen, deren Abfrageergebnisse zu groß sind, um zwischengespeichert zu werden, wird empfohlen, SQL_NO_CACHE zu verwenden.
• query_cache_size: der gesamte verfügbare Speicherplatz für den Abfrage-Cache; Einheit: Bytes, muss ein ganzzahliges Vielfaches von 1024 sein, Mindestwert: 40 KB, darunter wird ein Alarm ausgelöst;
• query_cache_wlock_invalidate: Kann der Abfragecache noch Ergebnisse zurückgeben, wenn eine Tabelle durch andere Sitzungen gesperrt ist? Der Standardwert ist OFF, was bedeutet, dass Daten weiterhin aus dem Cache zurückgegeben werden können, auch wenn die Tabelle durch andere Sitzungen gesperrt ist; ON bedeutet, dass dies nicht zulässig ist.
• 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 Abfragezwischenspeicherung bei Bedarf durchgeführt und nur SELECT-Anweisungen zwischengespeichert, die explizit SQL_CACHE angeben; andere werden nicht zwischengespeichert.

MariaDB [(keine)]> VARIABLEN WIE 'query_cache%' ANZEIGEN;
+------------------------------+----------+
| Variablenname | Wert |
+------------------------------+----------+
| Abfrage-Cache-Limit | 1048576 |
| Abfrage-Cache_Mindestressource | 4096 |
| Abfrage-Cache-Größe | 33554432 |
| query_cache_strip_comments | AUS |
| Abfrage-Cache-Typ | EIN |
| query_cache_wlock_invalidate | AUS |
+------------------------------+----------+

Abfrage-Cache optimieren:

Cache-bezogene Statusvariablen abfragen:

• Qcache_free_blocks: Die Anzahl der Speicherblöcke im Abfragecache, die sich im Leerlaufzustand befinden;
• Qcache_free_memory: Die Gesamtmenge des Abfrage-Cache-Speichers im Leerlaufzustand;
• Qcache_hits: Trefferanzahl des Abfrage-Cache;
• Qcache_inserts: Die Häufigkeit, mit der ein neuer Abfragecache in den Abfragecache eingefügt wird, d. h. die Häufigkeit, mit der kein Treffer vorliegt.
• Qcache_lowmem_prunes: Wenn die Speicherkapazität des Abfragecaches nicht ausreicht, die Häufigkeit, mit der der alte Abfragecache gelöscht werden muss, um Platz für neue Cacheobjekte zu schaffen.
• Qcache_not_cached: Die Anzahl der SQL-Anweisungen, die nicht zwischengespeichert werden, einschließlich SQL-Anweisungen, die nicht zwischengespeichert werden können, und SQL-Anweisungen, die aufgrund der Einstellung „query_cache_type“ nicht zwischengespeichert werden.
• Qcache_queries_in_cache: die Anzahl der SQLs im Abfragecache;
• Qcache_total_blocks: Gesamtzahl der Blöcke im Abfragecache.

MariaDB [(keine)]> GLOBALEN STATUS ANZEIGEN, WIE 'Qcache%';
+-------------------------+----------+
| Variablenname | Wert |
+-------------------------+----------+
| Qcache_freie_blöcke | 1 |
| Qcache_freier_Speicher | 33536824 |
| Qcache_Hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_nicht_zwischengespeichert | 4 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+

Schätzung der Trefferquote und des Speicherverbrauchs:

• Die minimale Zuordnungseinheit von Speicherblöcken im Abfragecache query_cache_min_res_unit: (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
• Abfrage-Cache-Trefferquote: Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100 %
• Speichernutzung des Abfrage-Cache: (Abfrage-Cachegröße – qcache_free_memory) / Abfrage-Cachegröße * 100 %

3. Stichwortverzeichnis

​ Der Index ist eine spezielle Datenstruktur: Er definiert die Felder, die während der Suche als Suchbedingungen verwendet werden, und der Index wird in der Speicher-Engine implementiert.

Indizes können die Datenmenge reduzieren, die der Dienst scannen muss, und so die Anzahl der E/A-Vorgänge verringern. Indizes können Servern dabei helfen, das Sortieren und die Verwendung temporärer Tabellen zu vermeiden. Indizes können dabei helfen, zufällige E/A-Vorgänge in sequentielle E/A-Vorgänge umzuwandeln.
Aber es nimmt zusätzlichen Platz ein und beeinträchtigt die Einsteckgeschwindigkeit

1. Indextyp:

• B+Baumindex: sequentielle Speicherung, der Abstand von jedem Blattknoten zur Wurzel ist gleich, linker Präfixindex, geeignet zum Abfragen von Bereichsdaten;
  - Abfragetypen, die für die Verwendung von B-Tree-Indizes geeignet sind
  - Volle Wertübereinstimmung
  - Stimmt mit dem Präfix ganz links überein
  - Bereichswert abgleichen
  - Genaue Übereinstimmung in einer Spalte und Bereichsübereinstimmung in einer anderen Spalte (zusammengesetzter Index)
  - Abfragen, die nur auf Indizes zugreifen
  - Abfragetypen, die nicht für die Verwendung von B-Tree-Indizes geeignet sind
  - Nicht beginnend in der Spalte ganz links
  - Spalten im Index können nicht übersprungen werden
  - Wenn eine Spalte in der Abfrage eine Bereichsabfrage ist, kann die Spalte rechts den Index nicht mehr zur Optimierung der Abfrage verwenden
• Hash-Index: Erstellt auf Grundlage der Hash-Tabelle einen Index aus Schlüssel-Wert-Paaren. Er eignet sich besonders für Indexspalten im Index für exakte Übereinstimmungen. Er unterstützt nur Gleichheitsvergleichsabfragen (IN, =, <>). Er ist nicht für sequentielle Abfragen geeignet und unterstützt kein Fuzzy-Matching. Nur die Speicher-Engine Memory unterstützt explizite Hash-Indizes.

• Räumlicher Index (R-Tree): Nur MyISAM unterstützt räumlichen Index

• Volltextindizierung: Schlüsselwörter im Text finden

2. Leistungsstarke Indizierungsstrategie:

• Verwenden Sie Spalten unabhängig voneinander und vermeiden Sie soweit möglich deren Einbeziehung in Berechnungen.
• Verwenden Sie einen linken Präfixindex: Die Anzahl der Zeichen auf der linken Seite des zu indizierenden Felds wird durch Indexselektivität ausgewertet. Indexselektivität: das Verhältnis nicht wiederholter Indexwerte zur Gesamtzahl der Datensätze in der Datentabelle
• Mehrspaltige Indizes: Es ist sinnvoller, mehrspaltige Indizes für UND-Operationen zu verwenden, als für jede Spalte einen separaten Index zu erstellen.
• Wählen Sie die entsprechende Indexspaltenreihenfolge: Wenn keine Sortierung oder Gruppierung erfolgt, platzieren Sie die Spalten mit der höchsten Selektivität links.

3. Vorschläge zur Indexoptimierung

• Solange die Spalte NULL-Werte enthält, sollte in diesem Fall am besten kein Index gesetzt werden. Wenn ein zusammengesetzter Index NULL-Werte enthält, wird diese Spalte bei Verwendung des Indexes nicht verwendet.
• Versuchen Sie, kurze Indizes zu verwenden und geben Sie, wenn möglich, eine Präfixlänge an.
• Für Spalten, die häufig in der Where-Klausel verwendet werden, ist es am besten, einen Index festzulegen
• Für Where- oder Order-By-Klauseln mit mehreren Spalten sollte ein zusammengesetzter Index erstellt werden
• Bei Like-Anweisungen gilt: Wenn sie mit % oder „-“ beginnen, wird der Index nicht verwendet, wenn sie jedoch mit % enden, wird der Index verwendet.
• Versuchen Sie, keine Operationen (Funktionsoperationen und Ausdrucksoperationen) auf Spalten auszuführen.
• Vermeiden Sie die Verwendung von „not in“ und „<>“-Operationen.
• Versuchen Sie beim Verbinden mehrerer Tabellen, eine große Tabelle mit einer kleinen Tabelle anzutreiben, das heißt, eine kleine Tabelle wird mit einer großen Tabelle verbunden.
• Verwenden Sie beim Paging ein Limit auf der 10-Millionen-Ebene
• Für häufig verwendete Abfragen können Sie das Caching aktivieren
• In den meisten Fällen sind Verknüpfungen viel effizienter als Unterabfragen.

4. Erstellen und Löschen von Indizes

Index erstellen

CREATE INDEX index_name ON tbl_name (index_col_name,...);

MariaDB [hellodb]> CREATE INDEX index_name ON students(name); #Erstellen Sie einen einfachen Index
MariaDB [hellodb]> CREATE INDEX index_name_age ON students(name,age); #Erstellen Sie einen zusammengesetzten Index

Index anzeigen

INDIZES VON [db_name.]tbl_name ANZEIGEN;

MariaDB [hellodb]> INDEX VON Studenten anzeigen\G

Löschen eines Indexes

DROP INDEX Indexname ON Tabellenname;

MariaDB [hellodb]> DROP INDEX index_name ON Studenten;

Optimieren Sie den Tablespace

MariaDB [hellodb]> Tabelle optimieren, Studenten;

Anzeigen der Indexnutzung

Protokollierung der Indexnutzung aktivieren: SET GLOBAL userstat=1;

Indexnutzung anzeigen: SHOW INDEX_STATISTICS;

Wir können selten verwendete Indizes zur Optimierung zählen

4. EXPLAIN-Befehl

Analysieren Sie die Wirksamkeit des Indexes durch EXPLAIN: EXPLAIN SELECT-Klausel, erhalten Sie Informationen zum Abfrageausführungsplan und sehen Sie, wie der Abfrageoptimierer die Abfrage ausführt

MariaDB [hellodb]> EXPLAIN SELECT name FROM students WHERE name = 'Lin Daiyu'\G
*************************** 1. Reihe ***************************
           ID: 1
  select_type: EINFACH
        Tabelle: Studierende
         Typ: ref
mögliche Schlüssel: Indexname_Alter
          Schlüssel: Indexname_Alter
      Schlüssellänge: 152
          Verweis: const
         Reihen: 1
        Extra: Verwenden von „where“; Verwenden von „index“

• id: die Nummer jeder SELECT-Anweisung in der aktuellen Abfrageanweisung; es gibt drei Arten komplexer Abfragen: einfache Unterabfragen, in der FROM-Klausel verwendete Unterabfragen und Union-Abfragen (UNION, Hinweis: In den Analyseergebnissen von UNION-Abfragen wird eine zusätzliche anonyme temporäre Tabelle angezeigt)
• Typ auswählen:
  - SIMPLE: Einfache Abfrage
  - SUBQUERY: Einfache Unterabfrage
  - PRIMARY: das äußerste SELECT
  - DERIVED: wird für Unterabfragen in FROM verwendet
  - UNION: Die SELECT-Anweisung nach der ersten UNION-Anweisung
  - UNION RESULT: anonyme temporäre Tabelle
• Tabelle: die Tabelle, mit der die SELECT-Anweisung verknüpft ist
• Typ: Assoziationstyp oder Zugriffstyp, der bestimmt, wie MySQL Zeilen in der Tabelle abfragt. Die folgende Reihenfolge, Leistung von niedrig bis hoch
  - ALLE: vollständiger Tabellenscan
  - Index: Führen Sie einen vollständigen Tabellenscan entsprechend der Indexreihenfolge durch. Wenn in der Spalte „Extra“ „Index verwenden“ erscheint, bedeutet dies, dass anstelle eines vollständigen Tabellenscans ein überdeckender Index verwendet wird.
  - Bereich: Ein Bereichsscan wird basierend auf einem Index mit einem begrenzten Bereich durchgeführt. Die Scanposition beginnt an einem Punkt im Index und endet an einem anderen Punkt
  - ref: Gibt alle Zeilen in der Tabelle zurück, die gemäß dem Index einem einzelnen Wert entsprechen
  - eq_ref: gibt nur eine Zeile zurück, erfordert aber zusätzlich einen Vergleich mit einem Referenzwert
  - const, system: gibt direkt eine einzelne Zeile zurück
• Possible_keys: Indizes, die für die Abfrage verwendet werden können
• Schlüssel: der in der Abfrage verwendete Index
• key_len: Die Anzahl der im Index verwendeten Bytes
• ref: Die Spalte oder der konstante Wert, der zum Vervollständigen der Abfrage unter Verwendung des durch das Schlüsselfeld dargestellten Index verwendet wird
• Zeilen: Die Anzahl der Zeilen, die MySQL nach Schätzung lesen muss, um alle Zielzeilen zu finden
• Extra: Weitere Informationen
  - Index verwenden: MySQL verwendet einen überdeckenden Index, um den Zugriff auf die Tabelle zu vermeiden
  - Verwenden von „where“: Der MySQL-Server führt einen weiteren Filter aus, nachdem die Speicher-Engine ihn abgerufen hat.
  - Verwendung von temporären Tabellen: MySQL verwendet eine temporäre Tabelle beim Sortieren der Ergebnisse
  - Verwenden von Filesort: Verwenden Sie einen externen Index, um die Ergebnisse zu sortieren

5. Leistungsoptimierung für SQL-Anweisungen

1. Vermeiden Sie bei der Abfrage möglichst die Verwendung von * und versuchen Sie, den vollständigen Feldnamen zu schreiben
2. In den meisten Fällen sind Verknüpfungen viel effizienter als Unterabfragen.
3. Versuchen Sie beim Verbinden mehrerer Tabellen, eine große Tabelle mit einer kleinen Tabelle anzutreiben, das heißt, eine kleine Tabelle wird mit einer großen Tabelle verbunden.
4. Verwenden Sie beim Paging ein Limit auf der 10-Millionen-Ebene
5. Für häufig verwendete Abfragen können Sie das Caching aktivieren
6. Verwenden von „Explain“ und „Profile“ zum Analysieren von Abfrageanweisungen
7. Zeigen Sie das Protokoll langsamer Abfragen an, um die Optimierung von SQL-Anweisungen mit langer Ausführungszeit herauszufinden

Damit ist dieser Artikel zu MySQL Serie 9: MySQL-Abfrage-Cache und -Index abgeschlossen. Weitere Informationen zu MySQL-Abfrage-Cache und -Index finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!