MySQL-unabhängiger Index und gemeinsame Indexauswahl

Häufig fehlt das Verständnis für mehrspaltige Indizes. Ein häufiger Fehler besteht darin, unabhängige Indizes für viele Spalten einzurichten oder die Indexspalten in der falschen Reihenfolge zu verwenden. Wir werden das Problem der Indexspaltenreihenfolge im nächsten Artikel besprechen. Betrachten wir zunächst den Fall mehrerer unabhängiger Indizes und nehmen dabei die folgende Tabellenstruktur als Beispiel:

CREATE TABLE-Test (
  c1 INT,
  c2 INT,
  c3 INT,
  SCHLÜSSEL(c1),
  SCHLÜSSEL(c2),
  SCHLÜSSEL(c3),
);

Die Verwendung dieser Indexierungsstrategie ist normalerweise das Ergebnis einer maßgeblichen Empfehlung (z. B. das Hinzufügen von Indizes zu den Bedingungsspalten, die in der WHERE-Bedingung verwendet werden). Tatsächlich ist das völlig falsch und ich würde bestenfalls einen Stern vergeben. Die Indizierung auf diese Weise ist um mehrere Größenordnungen langsamer als eine wirklich optimierte Indizierung. Wenn Sie manchmal keinen Index mit mehr als drei Sternen entwerfen können, ist es besser, sich auf die Optimierung der Zeilenreihenfolge oder die Erstellung abdeckender Indizes zu konzentrieren, als die WHERE-Bedingung zu ignorieren.

Ein überdeckender Index bedeutet, dass die Ausführung einer Abfrageanweisung nur aus dem Index abgerufen werden kann, ohne dass diese aus der Datentabelle gelesen werden muss. Man kann auch sagen, dass eine Indexabdeckung erreicht ist. Wenn eine Abfrageanweisung die Bedingung des abdeckenden Index erfüllt, muss MySQL den Index nur verwenden, um die für die Abfrage erforderlichen Daten zurückzugeben. Dadurch wird die Notwendigkeit vermieden, nach dem Auffinden des Index zur Tabelle zurückzukehren, was die E/A reduziert und die Effizienz verbessert. Beispielsweise gibt es einen gemeinsamen Index idx_key1_key2(key1, key2) in der Tabelle coverage_index_sample. Wenn wir die SQL-Anweisung verwenden: select key2 from coverage_index_sample where key1 = 'keytest';, können wir den überdeckenden Index abfragen, ohne Datenzeilen aus der Datentabelle suchen zu müssen.

Das Erstellen separater Indizes für viele Spalten trägt in vielen Fällen nicht dazu bei, die Leistung von MySQL zu verbessern. MySQL 5.0 und höher kann dieses Design mithilfe einer Indexzusammenführungsstrategie leicht optimieren. Mit diesem Ansatz können Abfragen von Tabellen mit mehrspaltigen Indizes die Verwendung von Indizes zum Auffinden der erforderlichen Zeilen einschränken.

Bei der Indexzusammenführung werden bedingte Scans für mehrere Indizes separat durchgeführt und anschließend die jeweiligen Ergebnisse zusammengeführt (Schnittmenge/Vereinigung).

Frühere MySQL-Versionen konnten nur einen Index verwenden, sodass MySQL häufig einen vollständigen Tabellenscan durchführte, wenn keine Indexunterstützung vorhanden war. Beispielsweise verfügt die Tabelle „film_actor“ über einen Index für „film_id“ und „actor_id“, aber die Verwendung beider Indizes in der WHERE-Bedingung ist keine gute Wahl:

WÄHLEN SIE film_id, actor_id FROM film_actor WHERE actor_id = 1 OR film_id = 1;

In früheren MySQL-Versionen führt diese Abfrage zu einem vollständigen Tabellenscan, sofern Sie die beiden Abfragen nicht wie unten gezeigt zusammenführen.

Wählen Sie Film-ID, Schauspieler-ID aus Film-Schauspieler, wobei Schauspieler-ID = 1 UNION ALL ist. 
Wählen Sie Film-ID, Schauspieler-ID aus Film-Schauspieler, wobei Film-ID = 1 und Schauspieler-ID <> 1 ist.

In MySQL 5.0 und höher verwendet die Abfrage beide Indizes und führt die endgültigen Ergebnisse zusammen. Zur Umsetzung dieses Verfahrens sind drei Varianten des Algorithmus erforderlich:

1. Verwenden Sie ODER-Bedingungen, um Vereinigungsdaten zu erhalten
2. Verwenden Sie die UND-Bedingung, um Schnittpunktdaten zu erhalten
3. Bilden Sie die Vereinigung der Schnittpunkte der Daten aus den beiden obigen Schritten.
   

Das Obige ist etwas verwirrend. Tatsächlich sollte es unter Verwendung einer einzigen Bedingung (um den Index zu verwenden) verteilt werden, um alle Daten zu finden und dann die Daten zu kombinieren. Lassen Sie uns dies mithilfe von EXPLAIN überprüfen.

EXPLAIN SELECT `film_id`,`actor_id` FROM `film_actor` WHERE `actor_id`=1 OR `film_id`=1

Es ist ersichtlich, dass die Abfragemethode ein vollständiger Tabellenscan ist, aber Extra wird zur Optimierung verwendet. MySQL verwendet diese Technik bei der Verarbeitung komplexer Abfragen, daher sehen Sie in Extra möglicherweise verschachtelte Vorgänge. Diese Indexzusammenführungsstrategie kann manchmal gut funktionieren, sollte aber häufiger als Hinweis auf eine schlechte Indexverwendung betrachtet werden:

1. Wenn der Server einen Schnittmengenindex verwendet (normalerweise mit einer UND-Bedingung), bedeutet dies normalerweise, dass Sie einen Index wünschen, der alle verknüpften Spalten enthält, und keine separaten Indizes, die die Spalten kombinieren.
2. Wenn der Server Union-Indizes verwendet (normalerweise mit einer ODER-Bedingung), können die Caching-, Sortier- und Zusammenführungsvorgänge manchmal viele CPU- und Speicherressourcen beanspruchen, insbesondere wenn die Indizes nicht immer über Filter verfügen, was dazu führen kann, dass der Scan für den Zusammenführungsvorgang eine große Anzahl von Zeilen zurückgibt.
3. Bedenken Sie, dass dem Optimierer diese Kosten nicht entstehen – er optimiert lediglich die Anzahl der zufälligen Seitenaufrufe. Dadurch kann die Abfrage „billiger“ werden, was dazu führt, dass der vollständige Tabellenscan tatsächlich langsamer wird. Eine hohe CPU- und Speicherauslastung wirkt sich auf gleichzeitige Abfragen aus. Diese Auswirkungen treten jedoch nicht auf, wenn Sie Abfrageanweisungen einzeln ausführen. Daher ist es manchmal optimaler, Abfragen so umzuschreiben, dass UNION verwendet wird, wie dies in MySQL 4.1 getan wurde.
   

Wenn Sie bei der Verwendung der EXPLAIN-Analyse Indexzusammenführungen feststellen, sollten Sie die Abfrageanweisung und Tabellenstruktur überprüfen, um festzustellen, ob dies der optimale Ansatz ist. Sie können dies überprüfen, indem Sie die Indexzusammenführung mit optimizer_switch deaktivieren.

Ändern Sie den Index von film_actor in einen gemeinsamen Index (löschen Sie die beiden ursprünglichen unabhängigen Indizes film_id und actor_id) und sehen Sie sich die Auswirkungen an. Sie können sehen, dass die vollständige Tabellenabfrage vermieden wird.

ALTER TABLE film_actor ADD INDEX `sindex` (`film_id`,`actor_id`);

Oben sind die Details zur Auswahl des unabhängigen MySQL-Index und des gemeinsamen Index aufgeführt. Weitere Informationen zum unabhängigen MySQL-Index und zum gemeinsamen Index finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!