Wie wirkt sich der zusammengesetzte Index von MySQL aus?

Hintergrund

Da es in letzter Zeit häufig zu Problemen mit der Systemleistung durch langsame SQL-Anweisungen kam, haben wir uns entschlossen, die Indizes zu optimieren. Einige Tabellenstrukturen haben bereits viele Indizes. Wenn Sie weiterhin Indizes hinzufügen, wirkt sich dies zwangsläufig auf die Leistung beim Einfügen von Daten aus. Ist es also möglich, dieses Ziel mithilfe eines kombinierten Index zu erreichen? Lassen Sie es uns in diesem Artikel herausfinden.

Zusammengesetzte Indizes verstehen

Wenn in der Where-Bedingung mehrere Felder verwendet werden und mehrere Felder indiziert werden müssen, können Sie die Verwendung eines zusammengesetzten Indexes in Erwägung ziehen. Wenn Sie beispielsweise nach einer Adresse suchen, müssen Sie die Provinz und die Stadt eingeben. Wenn dann ein Index für die Provinz und die Stadt erstellt wird, verbessert sich die Abfragegeschwindigkeit bei großen Datenmengen erheblich.

Was sind die Vorteile kombinierter Indizes?

• Reduzieren Sie den Abfrage-Overhead: Das Erstellen eines zusammengesetzten Index (c1, c2, c3) entspricht tatsächlich dem Erstellen von drei Indizes: (c1), (c1, c2) und (c1, c2, c3). Bei großen Tabellen kann dies den Mehraufwand erheblich reduzieren.
• Index abdecken: MySQL kann Daten direkt durch Durchlaufen des Index abrufen, ohne zur Tabelle zurückzukehren, wodurch viele zufällige E/A-Vorgänge reduziert werden.
• Hohe Effizienz: Je mehr Indexspalten vorhanden sind, desto weniger Daten werden durch den Index herausgefiltert, was wiederum die Abfrageeffizienz verbessert.

Mangel:

• Je mehr Indexfelder Sie haben, desto mehr Indizes erstellen Sie und jeder Index erhöht den Speicherplatzbedarf.
• Je mehr Indizes vorhanden sind, desto effizienter ist die Abfrage. Dies wirkt sich jedoch auf die Effizienz von Hinzufügungs-, Lösch- und Änderungsvorgängen aus, für die der Index aktualisiert werden muss.

Empfehlungen zur Verwendung zusammengesetzter Indizes: Eine einzelne Tabelle sollte nicht mehr als einen zusammengesetzten Index haben und ein einzelner zusammengesetzter Index sollte nicht mehr als drei Felder haben. Bei Überschreitung muss die Notwendigkeit und die Möglichkeit anderer Alternativen geprüft werden.

Prinzip der Übereinstimmung ganz links

Zusammengesetzte Indizes folgen dem Prinzip der Übereinstimmung ganz links. Wie der Name schon sagt, wird in einem zusammengesetzten Index zuerst das Feld ganz links abgeglichen. Daher wird beim Erstellen eines zusammengesetzten Indexes das am häufigsten verwendete Feld in der Where-Klausel ganz links im zusammengesetzten Index platziert.

Der Hilfsindex wird durch einen B+-Baum implementiert. Obwohl mehrere Spalten angegeben werden können, ist die Vergleichspriorität jeder Spalte unterschiedlich, und die davor geschriebene hat eine höhere Priorität. Sobald eine Auslassung auftritt, kann die Suche im B+-Baum nicht fortgesetzt werden (es sei denn, das Problem wird durch Maßnahmen wie das Ausfüllen der Lücken behoben). Daher wird die Suche entsprechend der am weitesten links stehenden kontinuierlichen Übereinstimmung durchgeführt. Da die Suche auf einem B+-Baum erfolgt, ist für den Vergleich der Bedingungen natürlich eine exakte Übereinstimmung (also "=" und "IN") erforderlich.

In der Where-Klausel werden zwei Felder c1 und c2 verwendet. Soll beim Erstellen eines Index die Reihenfolge der beiden Felder (c1, c2) oder (c2, c1) sein?

Der richtige Ansatz besteht darin, denjenigen mit den am wenigsten wiederholten Werten an den Anfang zu stellen. Wenn beispielsweise 95 % der Werte eindeutig sind, können Sie erwägen, diese an den Anfang zu stellen.

Auswirkung der Feldreihenfolge

Der zusammengesetzte Index folgt dem Übereinstimmungsprinzip „ganz links“. Müssen die Felder in den Where-Abfragebedingungen also auch in der Reihenfolge des Index geschrieben werden?
Wenn der zusammengesetzte Index beispielsweise (c1, c2, c3) ist, wirken sich die folgenden beiden Abfragebedingungen auf den Index aus?

wähle * aus t_user, wobei c1 = 1 und c2 = 4;
Wählen Sie * aus t_user, wobei c2 = 4 und c1 = 1;

Ich habe einen Artikel gelesen, in dem behauptet wird, die erste SQL-Anweisung sei effizienter. Ist das glaubwürdig? Die beiden Abfragemethoden haben die gleichen Bedingungen und die Ergebnisse sollten gleich sein. Normalerweise lässt MySQL sie denselben Index verwenden.

Durch die Analyse der beiden obigen Anweisungen mit dem Abfrageoptimierer von MySQL können wir feststellen, dass die Ausführungspläne genau gleich sind. Mit anderen Worten, die Reihenfolge der Felder in der SQL-Anweisung muss nicht mit der Reihenfolge der zusammengesetzten Indexfelder übereinstimmen, und der Abfrageoptimierer passt die Reihenfolge automatisch an.

Wenn es Auswirkungen auf die Effizienz gibt, handelt es sich wahrscheinlich um Auswirkungen der Korrekturreihenfolge des Abfrageoptimierers, die nahezu vernachlässigbar sind.

Kann ein einzelnes Feld einen Index auslösen?

Bei einem zusammengesetzten Index von (c1, c2, c3), der drei Indizes (c1), (c1, c2) und (c1, c2, c3) entspricht, ist es offensichtlich, dass der Index verwendet wird, wenn in der Abfragebedingung nur c1 vorhanden ist.
Was aber, wenn die Where-Bedingung wie folgt lautet:

von t_user, wobei c2 = 4;

Wird die obige Anweisung den Index durchlaufen? Dies kann durch mehrere Situationen erklärt werden.
Führen Sie die SQL-Anweisung mit „explan query c1“ als Bedingung aus:

Erläutern Sie „select * from t_user“, wobei c1 = 1;

Der Indextyp der obigen Anweisung ist: ref. Der Ref-Typ bedeutet, dass MySQL basierend auf einem bestimmten Algorithmus schnell den Index findet, der die Bedingungen erfüllt, ohne jedes einzelne Datenstück im Index zu scannen und zu beurteilen. Um Daten schnell zu finden, muss dieser Indextyp bestimmte Datenstrukturen erfüllen.
Führen Sie die SQL-Anweisung mit „explan query c2“ als Bedingung aus:

Erklären Sie „Select c2 from t_user“, wobei c2 = 4;

Der Indextyp der obigen Anweisung ist: Index. Der Indextyp bedeutet, dass MySQL den gesamten Index scannt. Solange es sich um einen Index oder einen Teil eines Index handelt, kann MySQL ihn im Indextyp scannen. Da die Daten bei dieser Methode einzeln gesucht werden, ist die Leistung nicht besonders hoch.

In diesem Beispiel gibt es bestimmte Anforderungen an die abgefragten Felder. Die Bedingung in „where“ ist c2, und das in „select“ abgefragte Feld kann nur c2 sein, daher wird der Indextyp „index“ verwendet.

Wenn c2 durch * oder andere Felder ersetzt wird:

Erklären Sie „Select * from t_user“, wobei c2 = 4;

Die obige Anweisung stellt fest, dass der Index nicht mehr verwendet wird, aber ein vollständiger Tabellenscan durchgeführt wird. Dies erklärt auch, warum MySQL dem Prinzip der äußersten linken Übereinstimmung folgt.
Die Schlussfolgerung lautet also: Wenn ein einzelnes Feld das erste Feld eines zusammengesetzten Indexes ist, wird der Index normal verwendet. Wenn ein einzelnes Feld ein weiteres Feld eines zusammengesetzten Indexes ist und nur dieses Feld nach der Auswahl erscheint, wird der Indextypindex verwendet. In anderen Fällen wird der vollständige Tabellenscan verwendet.

Kann ein zusammengesetzter Index einen einzelnen Index ersetzen?

Einzelindex: (c1), zusammengesetzter Index: (c1, c2).

Wenn c1 als Abfragebedingung verwendet wird, ist die Abfragegeschwindigkeit eines einzelnen Indexes fast dieselbe wie die eines zusammengesetzten Indexes oder sogar etwas schneller als die eines zusammengesetzten Indexes.
Wenn nur die nicht startende Spalte (c2) des zusammengesetzten gruppierten Indexes als Abfragebedingung verwendet wird, hat der zusammengesetzte Index keine Wirkung.
Wenn für eine Tabelle ein zusammengesetzter Index (c1, c2) vorhanden ist, muss kein einzelner Index (c1) erstellt werden.
Wenn bereits ein einzelner Index (c1) vorhanden ist, können Sie zur Verbesserung der Effizienz einen zusammengesetzten Index (c1, c2) hinzufügen, falls die Abfrage dies erfordert.

Zusammenfassung

Dieser Artikel fasst einige Wissenspunkte zusammen, die Sie bei der Verwendung von MySQL-Verbundindizes beachten müssen. Bei der Verwendung können Sie mithilfe von „explain“ überprüfen, ob Ihre SQL-Anweisung den Index verwendet und welchen Index sie verwendet.
Sie müssen sich jedoch auch darüber im Klaren sein, dass der Ausführungsplan von MySQL und der tatsächliche Ausführungsprozess der Abfrage nicht vollständig übereinstimmen.
Fragen Sie mich nicht, woher ich das weiß, denn ich habe es in der Praxis erlebt. Für dieselbe SQL-Anweisung kann der Index abhängig von den Abfragebedingungen verwendet werden oder nicht.

Dies ist das Ende dieses Artikels über die Funktionsweise des MySQL-Verbundindex. Weitere Informationen zum MySQL-Verbundindex 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!