Kenntnisse zur Überwachung der MySQL-Indexnutzung (es lohnt sich, sie zu erwerben!)

Überblick

In einer relationalen Datenbank ist ein Index eine separate, physische Speicherstruktur, die die Werte einer oder mehrerer Spalten einer Datenbanktabelle sortiert. Es handelt sich dabei um eine Sammlung von Werten in einer oder mehreren Spalten einer Tabelle und eine entsprechende Liste logischer Zeiger auf die Datenseiten in der Tabelle, die diese Werte physisch identifizieren.

MySQL unterstützt Hash- und B-Tree-Indizes. InnoDB und MyISAM unterstützen nur Btree-Indizes, während die Memory- und Heap-Speicher-Engines sowohl Hash- als auch Btree-Indizes unterstützen können.

1. Aktuelle Indexnutzung anzeigen

Die aktuelle Indexnutzung können wir mit folgender Anweisung abfragen:

• Handler_read_first stellt die Anzahl der Lesevorgänge des Indexheaders dar. Wenn dieser Wert hoch ist, bedeutet dies, dass viele vollständige Indexscans durchgeführt werden.
• Handler_read_key stellt die Häufigkeit dar, mit der ein Index verwendet wird. Wenn wir einen neuen Index hinzufügen, können wir prüfen, ob Handler_read_key zugenommen hat. Wenn ja, bedeutet dies, dass SQL den Index verwendet.
• Handler_read_next stellt das Lesen des nächsten Elements des Index dar, was normalerweise zu einem Bereichsscan führt.
• Handler_read_prev stellt das Lesen der vorherigen Spalte des Index dar, was normalerweise in ORDER BY ... DESC erfolgt.
• Handler_read_rnd stellt das Lesen von Zeilen an einer festen Position dar. Wenn dieser Wert hoch ist, bedeutet dies, dass eine große Anzahl von Ergebnismengen sortiert werden, vollständige Tabellenscans durchgeführt werden und zugehörige Abfragen keine geeigneten Schlüssel verwenden.
• Handler_read_rnd_next zeigt an, dass viele Tabellenscans durchgeführt werden und die Abfrageleistung schlecht ist.

Tatsächlich ist in vielen Anwendungsszenarien der Wert von Handler_read_key sehr hoch, wenn der Index funktioniert. Dieser Wert gibt an, wie oft eine Zeile den Indexwert liest. Ein sehr niedriger Wert zeigt an, dass die durch das Hinzufügen des Index erzielte Leistungsverbesserung nicht hoch ist, da der Index nicht häufig verwendet wird.

Ein hoher Wert für Handler_read_rnd_next bedeutet, dass Abfragen ineffizient ausgeführt werden und dies durch Indizierung behoben werden sollte. Dieser Wert gibt die Anzahl der Anforderungen zum Lesen der nächsten Zeile in der Datendatei an. Wenn eine große Anzahl von Tabellenscans durchgeführt wird, weist ein hoher Wert für Handler_read_rnd_next normalerweise darauf hin, dass die Tabelle nicht richtig indiziert ist oder dass die geschriebenen Abfragen die Indizes nicht nutzen.

2. Prüfen Sie, ob der Index verwendet wird

WÄHLEN
 Objekttyp,
 Objektschema,
 Objektname,
 Indexname,
 zähle_sterne,
 Anzahl der Lesevorgänge,
 ANZAHL_ABRUFEN 
AUS
 PERFORMANCE_SCHEMA.table_io_waits_summary_by_index_usage;

Wenn die Anzahl der Lese- und Abrufvorgänge beide 0 sind, bedeutet dies, dass es nicht verwendet wurde.

3. Überprüfen Sie, welche Indizes verwendet werden

Erläutern Sie das entsprechende SQL und überprüfen Sie den Typ, um zu sehen, welcher Indextyp in der Abfrage verwendet wird.

+-----+-------+-------+-----+--------+-----------+---------+-----------+
| ALLE | Index | Bereich | Ref | eq_ref | const | System | NULL |
+-----+-------+-------+-----+--------+-----------+---------+-----------+

Vom Besten zum Schlechtesten sind dies:

System > const > eq_ref > ref > Volltext > ref_or_null > Index_Merge > eindeutige Unterabfrage > Index_Unterabfrage > Bereich > Index > ALLE

• Es gibt nur einen Datensatz in der Systemtabelle und dieser wird im Allgemeinen nur in der Systemtabelle angezeigt.
• const bedeutet, dass die Abfrage durch eine einzelne Indexabfrage gefunden wird. Im Allgemeinen ist die entsprechende Indexspalte der Primärschlüssel oder in der eindeutigen Where-Anweisung wird eine Konstante angegeben. Da nur eine Datenzeile übereinstimmt, kann MySQL diese Abfrage auf eine Konstante optimieren, sodass sie sehr schnell ist.
• eq_ref Eindeutiger Index-Scan. Dieser Typ wird normalerweise in Verknüpfungsabfragen für mehrere Tabellen angezeigt. Für jede entsprechende Spalte, die von der vorherigen Tabelle verbunden ist, hat die entsprechende Spalte der aktuellen Tabelle einen eindeutigen Index, und höchstens eine Datenzeile stimmt damit überein.
• ref Nicht eindeutiger Indexscan. Wie oben, aber die entsprechende Spalte der aktuellen Tabelle hat keinen eindeutigen Index und es können mehrere Zeilen mit übereinstimmenden Daten vorhanden sein. Dieser Abfragetyp tritt normalerweise bei Verknüpfungsabfragen mehrerer Tabellen, bei Abfragen von nicht eindeutigen Indizes oder Indizes mit anderen Schlüsseln als Primärschlüsseln oder bei Abfragen auf, die den Index mit der Präfixregel ganz links verwenden.
• Bereichsabfrage auf dem Bereichsindex. Fragen Sie die Werte eines Bereichs von Indexschlüsselwörtern ab.
• index Volltextindex-Scan. Im Prinzip dasselbe wie bei allen anderen, es wird der gesamte Text gescannt, die abgefragten Felder werden jedoch in den Index aufgenommen, so dass die Daten in der Tabelle nicht gelesen werden müssen, sondern nur die Felder im Indexbaum.
• alles Volltextscan. Es wird kein Index verwendet, was die geringste Effizienz bedeutet.

Hier übrigens noch ein paar Optimierungspunkte:

1. Optimieren Sie die Insert-Anweisung:

1) Versuchen Sie, „insert into test values(),(),(),()...“ zu verwenden.
2) Wenn Sie mehrere Zeilen von verschiedenen Kunden einfügen, können Sie mit der Anweisung INSERT delay eine höhere Geschwindigkeit erzielen. Delayed bedeutet, dass die Anweisung INSERT sofort ausgeführt wird. Tatsächlich werden die Daten in eine Speicherwarteschlange gestellt und nicht wirklich auf die Festplatte geschrieben. Dies ist viel schneller, als jede Anweisung einzeln einzufügen. Low_priority ist genau das Gegenteil. Die Einfügung wird ausgeführt, nachdem alle anderen Benutzer mit dem Lesen und Schreiben der Tabelle fertig sind.
3) Speichern Sie Indexdateien und Datendateien auf unterschiedlichen Datenträgern (mithilfe von Anweisungen zur Tabellenerstellung).
4) Wenn Sie Batch-Einfügungen durchführen, können Sie den Wert der Variablen bulk_insert_buffer_size erhöhen, um die Geschwindigkeit zu steigern, aber nur für MyISAM-Tabellen.
5) Wenn Sie eine Tabelle aus einer Textdatei laden, verwenden Sie „load data file“, was normalerweise 20-mal schneller ist als „insert“

2. Optimieren Sie die Group-By-Anweisung:

Standardmäßig sortiert MySQL alle „Group by“-Felder, was „Order by“ ähnelt. Wenn die Abfrage eine Gruppierung nach enthält, der Benutzer jedoch die Verwendung sortierter Ergebnisse vermeiden möchte, können Sie „order by null“ angeben, um die Sortierung zu unterdrücken.

3. Optimieren Sie die Order-By-Anweisung:

In einigen Fällen kann MySQL einen Index verwenden, um die ORDER BY-Klausel zu erfüllen, wodurch die Notwendigkeit einer zusätzlichen Sortierung entfällt. Die Where-Bedingung und Order By verwenden denselben Index, und die Order By-Reihenfolge ist dieselbe wie die Indexreihenfolge, und die Order By-Felder erfolgen in aufsteigender oder absteigender Reihenfolge.

4. Verschachtelte Abfragen optimieren:

MySQL 4.1 unterstützt nun Unterabfragen, aber in manchen Fällen können Unterabfragen durch effizientere Verknüpfungen ersetzt werden, insbesondere wenn die zu verknüpfende passive Tabelle einen Index hat. Der Grund dafür ist, dass MySQL keine temporäre Tabelle im Speicher erstellen muss, um diese Abfrage abzuschließen, die logischerweise zwei Schritte erfordert.

Zum Schluss noch ein Punkt:

Eine Tabelle kann maximal 16 Indizes haben und die maximale Indexlänge beträgt 256 Byte. Indizes beeinträchtigen die Einfügeleistung im Allgemeinen nicht wesentlich (außer bei großen Mengen kleiner Daten), da der Zeitaufwand zum Erstellen eines Indexes O(1) oder O(logN) beträgt. Allerdings sind zu viele Indizes auch nicht gut, denn für Vorgänge wie Aktualisierungen ist die Pflege der Indizes erforderlich.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.