Gründe und Lösungen für die Auswahl des falschen Index durch MySQL

In MySQL können Sie mehrere Indizes für eine Tabelle angeben, aber wenn die Anweisung ausgeführt wird, wird der zu verwendende Index vom Executor in MySQL bestimmt. Nach welchen Grundsätzen wählt der Testamentsvollstrecker einen Index aus und wird möglicherweise der falsche Index ausgewählt?

Schauen wir uns ein Beispiel an:

Erstellen Sie Tabelle Y, legen Sie zwei gemeinsame Indizes fest und erstellen Sie eine gespeicherte Prozedur zum Einfügen von Daten.

MySQL: 5.7.27, Isolationsebene: RR

CREATE TABLE `Y` (
 `id` int(11) NICHT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 Primärschlüssel (`id`),
 SCHLÜSSEL `a` (`a`),
 SCHLÜSSEL `b` (`b`)
)ENGINE=InnoDB;

Trennzeichen;;
Prozedur idata() erstellen
beginnen
 deklariere i int;
 setze i=1;
 während(i<=100000)mache
   in Y einfügen (`a`,`b`) Werte(i, i);
  setze i=i+1;
 Ende während;
Ende;;
Trennzeichen ;
rufen Sie idata() auf;

Sehen Sie sich die folgenden Transaktionen an:

Wenn „select * from Y where a between 10000 and 20000;“ nur in Sitzung B ausgeführt wird, wird zweifelsohne Index a ausgewählt.

Wenn Sie jedoch Sitzung A und Sitzung B nacheinander installieren, werden Sie feststellen, dass die Indexauswahl wie folgt ist:

Es zeigt sich, dass der Executor im Szenario der Sitzung B nicht den Index auswählte, in dem sich a befand, sondern einen vollständigen Tabellenscan basierend auf dem Primärschlüsselindex wählte.

setze long_query_time=0;
--Öffnen Sie das Protokoll für langsame Abfragen und setzen Sie den Schwellenwert auf 0. Im aufgezeichneten Protokoll können Sie feststellen, dass MySQL den Index, in dem sich a befindet, nicht ausgewählt hat und länger gebraucht hat.

Aus dieser Perspektive ist der MySQL-Optimierer möglicherweise nicht immer in der Lage, den richtigen Index auszuwählen. Um den Grund für dieses Phänomen zu verstehen, müssen wir mit der Auswahllogik des Optimierers beginnen.

Optimierer

Der Zweck des Optimierers in MySQL besteht darin, einen optimalen Ausführungsplan zu finden, um die Anweisung mit den geringsten Kosten auszuführen.

Bei der Indexauswahl berücksichtigt der Optimierer vor allem folgende Faktoren:

• Anzahl der gescannten Zeilen: Je weniger Zeilen gescannt werden, desto seltener wird auf die Daten auf der Festplatte zugegriffen und desto weniger CPU-Ressourcen werden verbraucht.
• Ist eine temporäre Tabelle beteiligt?
• Sortierung

Bestimmen der Anzahl der Scanzeilen

Berechnen der Kardinalität eines Indexes

Vor der Ausführung einer Anweisung kann MySQL die Anzahl der gescannten Zeilen nicht genau berechnen, schätzt jedoch die Anzahl der Datensätze anhand mathematischer Statistiken. Diese Statistik wird als „Diskriminierung“ des Index bezeichnet. Je mehr unterschiedliche Werte der Index enthält, desto höher ist die Diskriminierung. Die Anzahl der unterschiedlichen Werte in einem Index wird als Kardinalität bezeichnet. Je größer die Kardinalität, desto besser ist die Unterscheidungsfähigkeit des Index.

Die Kardinalität ist hier die Kardinalität des Index, aber die Kardinalität ist nicht ganz genau. MySQL verwendet tatsächlich Stichprobenstatistiken, um die Kardinalität zu ermitteln.

Während der Berechnung werden N Datenseiten ausgewählt und die verschiedenen Werte auf diesen Seiten gezählt, um einen Durchschnittswert zu erhalten. Dieser wird dann mit der Anzahl der Seiten des Index multipliziert und das Ergebnis ist die Kardinalität des Index.

In MySQL gibt es zwei Möglichkeiten zum Speichern von Indizes, die durch Festlegen von innodb_stats_persistent umgeschaltet werden können:

• Wenn aktiviert, bedeutet dies, dass die Statistiken dauerhaft gespeichert werden. Der Standardwert N ist 20 und M ist 10.
• Wenn diese Option deaktiviert ist, werden Statistiken nur im Speicher gespeichert. Der Standardwert für N ist 8 und für M ist 16.
  

Da sich die Daten in der Tabelle ständig ändern, werden automatisch Indexstatistiken ausgelöst, wenn der aktualisierte Wert 1/M überschreitet.

Es ist jedoch zu beachten, dass der Kardinalitätswert nicht genau ist, da es sich um eine Stichprobenstatistik handelt .

Fehler bei der Schätzung der Anzahl der Scanzeilen

Wie wir zuvor gesehen haben, beträgt die geschätzte Zeilenanzahl für Select * from Y where a between 10000 and 20000 100015, was verständlich ist, da ein vollständiger Tabellenscan durchgeführt wird.

Führen Sie dann select * from Y force index(a) where a between 10000 and 20000 Die geschätzte Zeilenanzahl beträgt 37116, was unverständlich ist. Idealerweise sollten es 10001 Zeilen sein (muss bis 20001 durchlaufen werden).

Noch merkwürdiger ist, dass die geschätzte Zeilenanzahl von 37116 zwar nicht sinnvoll ist, aber viel kleiner ist als die 100015 eines vollständigen Tabellenscans. Warum wählt der Optimierer trotzdem einen vollständigen Tabellenscan?

Schauen wir uns zuerst die zweite Frage an. Der Grund für die Wahl von 100015 ist, dass bei Verwendung von Index a zusätzlich zum Scannen des Index a auch ein Zurückgehen zur Tabelle erforderlich ist. Der Optimierer muss auch die Abfragekosten für den Primärschlüsselindex berücksichtigen, daher wird ein vollständiger Tabellenscan gewählt.

Schauen wir uns nun noch einmal die erste Frage an, warum nicht die richtige Zeilenanzahl erreicht wird. Dies hängt mit der Konsistenzansicht zusammen. Zunächst ist in Sitzung A die Konsistenzansicht aktiviert, wird aber nicht übermittelt. Nachdem die nachfolgende Sitzung die Y-Tabelle gelöscht hat, werden dieselben Daten neu erstellt. Zu diesem Zeitpunkt hat jede Datenzeile zwei Versionen. Die alte Version sind die Daten vor dem Löschen und die neue Version sind die als gelöscht markierten Daten. Es gibt also tatsächlich zwei Kopien der Daten am Index a. Dies führt zu einem Fehler bei der geschätzten Zeilenanzahl.

MySQL löscht Datensätze, indem es sie zum Löschen markiert, anstatt sie tatsächlich aus Indizes und Datendateien zu löschen. Und aufgrund der Garantie des konsistenten Lesens kann der Löschbereich nicht gelöscht und der Einfügebereich hinzugefügt werden. Dies führt zu falschen statistischen Informationen.

Lösung zur Auswahl des falschen Index

Wenn die Zeilenanzahl falsch geschätzt wird, können Sie folgende Methode verwenden:

Wenn die von EXPLAIN berechnete Zeilenanzahl erheblich von der geschätzten Zeilenanzahl abweicht, können Sie die Indexinformationen mithilfe der Analysetabelle neu berechnen.

Verwenden Sie „Force Index“, um den zu verwendenden Index direkt anzugeben, ohne den Optimierer eine Entscheidung treffen zu lassen. Der Einsatz von Gewalt kann jedoch auch Probleme mit sich bringen:

• Beim Migrieren einer Datenbank wird die Syntax nicht unterstützt
• Das ist nicht einfach zu ändern und nicht sehr praktisch, da die Auswahl des falschen Index nicht oft vorkommt. Der Code muss nur geändert werden, wenn in der Produktionsumgebung Probleme auftreten, aber er muss auch erneut getestet und online bereitgestellt werden.

Optimieren Sie SQL-Anweisungen und weisen Sie den Optimierer an, den richtigen Index zu verwenden

Schauen wir uns ein ähnliches Beispiel an:

Schauen wir uns zunächst diesen Satz an.

SQL-Auswahl * von Y, wobei a zwischen 1 und 1000 und b zwischen 5000 und 100000, sortiert nach b, Limit 1;

Bei der Ausführung dieses Satzes können Sie Index a oder Index b wählen. Wir wissen, dass jeder Index einem B+-Baum entspricht. Da hier die Schnittmenge von a und b erhalten wird, müssen bei Auswahl des Index a die Zeilen 1 bis 10001 durchlaufen werden. Um den Index b auszuwählen, müssen die Zeilen 50000 - 100001 durchlaufen werden. Theoretisch sollte a als Index gewählt werden, der Optimierer wählt jedoch b als Index.

Der Grund, warum hier b als Index gewählt wird, liegt darin, dass der Optimierer die nachfolgende order by Anweisung sieht. Da eine Sortierung erforderlich ist und der B+-Baum selbst geordnet ist, wird der Sortiervorgang ausgelassen, sodass b als Index gewählt wird.

Aber von der tatsächlichen Ausführungszeit her hat Index a eine kürzere Ausführungszeit, sodass MySQL erneut den falschen Index gewählt hat.

Wir können order by b limit in der obigen Anweisung order by b,a limit 1 ändern. Da in diesem Fall sowohl der a- als auch der b-Index sortiert werden müssen, wird die Anzahl der gescannten Zeilen zur wichtigsten Referenzbedingung für den Ausführer, die die Auswahl des richtigen Index steuert.

Damit soll sichergestellt werden, dass die logischen Ergebnisse der Ausführung konsistent sind. Wenn beispielsweise Grenzwert 1 verwendet wird, sind die Ergebnisse von order by b,a und order by b konsistent. Dies ist jedoch möglicherweise nicht der Fall, wenn Grenzwert 100 verwendet wird.

Es gibt eine weitere Änderung

Wählen Sie * aus (Wählen Sie * aus t, wobei (a zwischen 1 und 1000) und (b zwischen 50000 und 100000) Sortierung nach b-Limit 100) Alias-Limit 1;

Jetzt können Sie sehen, dass der Optimierer einen geeigneten Index gewählt hat. Der Grund dafür ist, dass der Grenzwert 100 den Optimierer glauben lässt, dass die Kosten für die Verwendung von Index b höher sind, und daher Index a wählt. Tatsächlich wird der Grenzwert 100 verwendet, um den Optimierer zu einer Entscheidung zu veranlassen.

Anpassen des Indexes

Können Sie einen besseren und geeigneteren Index finden oder die Indexprinzipien verwenden, um einige unnötige Indizes zu löschen?

Zusammenfassen

Jetzt wissen wir, dass MySQL bei der Indexauswahl Fehler machen kann. Beim Auswählen von Indizes durch den Optimierer gelten drei Hauptprinzipien: die Anzahl der zu scannenden Zeilen, das Vorhandensein einer temporären Tabelle und die Sortierung. Die Anzahl der gescannten Zeilen hängt hauptsächlich von der Kardinalität ab, und die Kardinalitätsstatistiken werden durch statistische Stichproben ermittelt, sodass die geschätzte Zeilenanzahl ungenau sein kann.

Wenn die Anzahl der gescannten Zeilen falsch ist, können Sie die Tabelleninformationen mit analyze table neu berechnen, den angegebenen Index mit force index erzwingen oder die Semantik von sql manuell ändern, um den Optimierer zu veranlassen, die richtige Auswahl zu treffen.

Oben sind die Gründe aufgeführt, warum MySQL den falschen Index ausgewählt hat, sowie die detaillierten Lösungen. Weitere Informationen zum MySQL-Index finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!