Häufige Szenarien und Vermeidungsmethoden für Indexfehler in MySQL

Vorwort

Ich habe zuvor viele ähnliche Artikel gelesen, in denen erwähnt wurde, dass die falsche Verwendung einiger SQL-Anweisungen zum Fehlschlagen der MySQL-Indizes führt. Es gibt auch einige „Militärvorschriften“ oder Spezifikationen von MySQL, die besagen, dass bestimmte SQL-Anweisungen nicht auf diese Weise geschrieben werden dürfen, da sonst der Index ungültig wird.

Dem größten Teil des Inhalts stimme ich zu, allerdings finde ich, dass die Formulierungen in manchen Beispielen zu absolut sind und die Gründe nicht erläutert werden, sodass viele Leute nicht wissen, warum. Daher werde ich auf jeden Fall die häufigsten Szenarien für Indexfehler in MySQL noch einmal durchgehen und die Gründe zu Ihrer Information analysieren.

Denken Sie natürlich daran, dass Erklären eine gute Angewohnheit ist!

Häufige Szenarien für MySQL-Indexfehler

Bereiten Sie beim Überprüfen des folgenden Szenarios ausreichend Daten vor. Wenn die Datenmenge klein ist, kann der MySQL-Optimierer manchmal feststellen, dass ein vollständiger Tabellenscan harmlos ist und den Index nicht trifft.

1. Wenn die Where-Anweisung „oder“ enthält, kann der Index ungültig werden

Die Verwendung von oder macht den Index nicht unbedingt ungültig. Sie müssen prüfen, ob die Abfragespalten auf der linken und rechten Seite von oder denselben Index treffen.

Angenommen, die Spalte „user_id“ in der Tabelle „USER“ hat einen Index, die Spalte „age“ hat jedoch keinen Index.

Die folgende Anweisung führt tatsächlich zum Index (angeblich ist dies nur bei neueren MySQL-Versionen möglich. Wenn Sie eine ältere MySQL-Version verwenden, können Sie dies mit „explain“ überprüfen).

Wählen Sie * aus „Benutzer“, wobei Benutzer-ID = 1 oder Benutzer-ID = 2 ist.

Diese Aussage kann jedoch nicht den Index treffen.

Wählen Sie * aus „Benutzer“, wobei Benutzer-ID = 1 oder Alter = 20 ist.

Angenommen, die Spalte „Alter“ verfügt auch über einen Index, kann der Index dennoch nicht erreicht werden.

Wählen Sie * aus „Benutzer“, wobei Benutzer-ID = 1 oder Alter = 20 ist.

Daher wird empfohlen, die Verwendung der Anweisung „or“ möglichst zu vermeiden. Je nach Situation können Sie stattdessen „union all“ oder „in“ verwenden. Die Ausführungseffizienz dieser beiden Anweisungen ist besser als die von „or“.

2. In der Indexspalte in der Where-Anweisung wird eine negative Abfrage verwendet, was dazu führen kann, dass der Index fehlschlägt

Zu den negativen Abfragen zählen: NOT, !=, <>, !<, !>, NOT IN, NOT LIKE usw.

Eine bestimmte „Militärregelung“ besagt, dass die Verwendung negativer Abfragen definitiv dazu führt, dass der Index ungültig wird. Der Autor überprüfte einige Artikel und stellte fest, dass einige Internetnutzer diesen Punkt widerlegten und Beweise lieferten.

Tatsächlich führen negative Abfragen nicht unbedingt zu Indexfehlern. Dies hängt von der Einschätzung des MySQL-Optimierers ab, was die geringeren Kosten verursacht: vollständiger Tabellenscan oder Index.

3. Das Indexfeld kann null sein. Die Verwendung von is null oder is not null kann dazu führen, dass der Index fehlschlägt.

Tatsächlich kann ein einzelnes Indexfeld den Index treffen, wenn es null oder nicht null ist. Internetnutzer sagten jedoch in ihren Beweisen, dass der Index ungültig wird, wenn zwei verschiedene Indexfelder mit oder verbunden werden. Ich denke, der Index ist tatsächlich ungültig, aber die Schuld sollte auf oder geschoben werden. Dies gehört zum ersten Szenario ~~

Angenommen, die Spalte „user_id“ in der Tabelle „USER“ hat einen Index und lässt Nullwerte zu, und die Spalte „age“ hat einen Index und lässt Nullwerte zu.

Wählen Sie * aus „Benutzer“, wobei „user_id“ nicht null oder „age“ nicht null ist;

Einige „militärische Vorschriften“ und Spezifikationen betonen jedoch, dass Felder auf ungleich null gesetzt werden und Standardwerte bereitstellen sollten, worauf aus gutem Grund hingewiesen werden sollte.

• Null-Spalten machen Indizes/Indexstatistiken/Wertevergleiche komplizierter und für MySQL schwieriger zu optimieren.
• Dieser Nulltyp erfordert eine spezielle Verarbeitung innerhalb von MySQL, was die Komplexität der Datenbankdatensatzverarbeitung erhöht. Unter denselben Bedingungen wird die Datenbankverarbeitungsleistung erheblich reduziert, wenn die Tabelle viele leere Felder enthält.
• Nullwerte erfordern mehr Speicherplatz, und die Nullspalte in jeder Zeile sowohl in der Tabelle als auch im Index erfordert zusätzlichen Speicherplatz, um sie zu identifizieren.
• Beim Umgang mit Null können Sie nur „ist null“ oder „ist nicht null“ verwenden und keine Operationssymbole wie =, in, <, <>, != und nicht in. Beispiel: wobei Name!='shenjian'. Wenn Datensätze vorhanden sind, bei denen der Name Null ist, enthalten die Abfrageergebnisse keine Datensätze, bei denen der Name Null ist.

4. Die Verwendung integrierter Funktionen für Indexspalten führt definitiv zum Fehlschlagen des Index

Beispielsweise wird in der folgenden Anweisung eine Funktion auf die Indexspalte login_time angewendet, die den Index ungültig macht:

Wählen Sie * aus „Benutzer“, wobei DATE_ADD (Anmeldezeit, INTERVALL 1 TAG) = 7 ist.

Optimierungsvorschlag: Versuchen Sie Berechnungen und Umrechnungen in der Anwendung durchzuführen.

Tatsächlich wurden von Internetnutzern zwei Indexfehlerszenarien erwähnt, die beide auf die Verwendung von Funktionen in der Indexspalte zurückzuführen sind.

4.1 Indexungültigkeit durch implizite Typkonvertierung

Beispielsweise ist in der folgenden Anweisung die Indexspalte „user_id“ vom Typ „varchar“ und wird den Index nicht treffen:

Wählen Sie * aus „Benutzer“, wobei Benutzer-ID = 12 ist;

Dies liegt daran, dass MySQL eine implizite Typkonvertierung durchführt und die Benutzer-ID durch Aufrufen der Funktion konvertiert.

Wählen Sie * von „Benutzer“, wobei CAST(Benutzer-ID AS signiert int) = 12 ist;

4.2 Indexungültigkeit durch implizite Zeichenkodierungskonvertierung

Wenn beim Ausführen einer Join-Abfrage zwischen zwei Tabellen die Zeichenkodierungen der Felder in den beiden Tabellen inkonsistent sind, ruft MySQL möglicherweise die Funktion CONVERT auf, um die unterschiedlichen Zeichenkodierungen implizit zu konvertieren und so eine Einheitlichkeit zu erreichen. Bei einer Auswirkung auf die zugehörigen Felder wird der Index ungültig.

Beispielsweise ist in der folgenden Anweisung die Zeichenkodierung von d.tradeid utf8, während die Zeichenkodierung von l.tradeid utf8mb4 ist. Da utf8mb4 eine Obermenge von utf8 ist, verwendet MySQL bei der Konvertierung CONVERT, um utf8 in utf8mb4 umzuwandeln. Einfach ausgedrückt wirkt CONVERT auf d.tradeid, sodass der Index ungültig ist.

Wählen Sie l.operator aus Tradelog l, Trade_Detail d, wobei d.tradeid=l.tradeid und d.id=4;

Für diese Situation gibt es im Allgemeinen zwei Lösungen.

Lösung 1: Vereinheitlichen Sie die Zeichenkodierung verwandter Felder.

Lösung 2: Wenn es wirklich unmöglich ist, die Zeichenkodierung zu vereinheitlichen, wenden Sie die Funktion CONVERT manuell auf die rechte Seite des = in der Assoziation an, um die Zeichenkodierung zu vereinheitlichen. Hier wird utf8mb4 zwangsweise in utf8 konvertiert. Natürlich besteht bei der Konvertierung von einer Obermenge in eine Untermenge die Gefahr einer Datenkürzung. wie folgt:

Wählen Sie d.* aus Tradelog l, Trade_Detail d, wobei d.tradeid=CONVERT(l.tradeid USING utf8) und l.id=2;

5. Operationen an Indexspalten führen definitiv dazu, dass der Index ungültig wird

Operationen wie +, -, *, / usw. sind wie folgt:

Wählen Sie * aus „Benutzer“, wobei Alter - 1 = 10;

Zur Optimierung müssen Sie die Operation auf den Wert anwenden oder ihn direkt in der Anwendung berechnen, zum Beispiel:

Wählen Sie * aus „Benutzer“, wobei Alter = 10 - 1 ist;

6. Ähnliche Platzhalter können zu Indexfehlern führen

Wenn eine LIKE-Abfrage mit % beginnt, wird der Index ungültig. Es gibt zwei Lösungen:

Verschieben Sie das % nach hinten, beispielsweise:

Wählen Sie * aus „Benutzer“, wobei „Name“ wie „李%“ aussieht;

Verwenden Sie abdeckende Indizes, um den Index zu erreichen.

Wählen Sie den Namen aus „Benutzer“, wobei „Name“ wie „%李%“ aussieht.

7. Wenn in einem gemeinsamen Index die Indexspalte in der Where-Klausel gegen das Prinzip der ganz linken Übereinstimmung verstößt, wird der Index definitiv ungültig.

Wenn Sie einen gemeinsamen Index erstellen, z. B. (k1, k2, k3), entspricht dies dem Erstellen von drei Indizes: (k1), (k1, k2) und (k1, k2, k3). Dies ist das am weitesten links stehende Übereinstimmungsprinzip.

Beispielsweise führt die folgende Anweisung nicht zum Index:

wähle * aus t, wobei k2=2;
wähle * aus t, wobei k3=3;
slect * von t, wobei k2=2 und k3=3;

Die folgende Anweisung trifft nur auf den Index (k1):

slect * von t, wobei k1=1 und k3=3;

8. Die endgültige Wahl des MySQL-Optimierers besteht darin, keinen Index zu verwenden

Wie oben erwähnt, hängt die Frage, ob der Index letztendlich verwendet wird, auch wenn das Szenario die Anforderungen an die Indexwirksamkeit vollständig erfüllt, von der Beurteilung des MySQL-Optimierers unter Berücksichtigung des tatsächlichen Datenvolumens und anderer Faktoren ab. Natürlich können Sie auch im SQL-Statement angeben, einen bestimmten Index zu erzwingen.

Einige Vorschläge zur Optimierung von Indizes

• Erstellen Sie keine Indizes für Attribute, die sehr häufig aktualisiert werden und eine geringe Differenzierung aufweisen.
  • Durch Aktualisierungen wird der B+-Baum geändert und die Indizierung häufig aktualisierter Felder verringert die Datenbankleistung erheblich.
  • Es macht keinen Sinn, einen Index für eine Eigenschaft wie „Geschlecht“ zu erstellen, die kaum differenziert ist. Damit lassen sich Daten nicht effektiv filtern und die Leistung ist vergleichbar mit der eines vollständigen Tabellenscans.
• Beim Erstellen eines kombinierten Indexes müssen die Felder mit hoher Trennschärfe an den Anfang gestellt werden.

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.

siehe

Warum weisen diese SQL-Anweisungen zwar die gleiche Logik auf, weisen aber große Leistungsunterschiede auf? 》

《Ein Muss für Backend-Programmierer: Die 10 häufigsten Ursachen für Indexfehler》

„Interpretation von 30 Militärregeln der 58 Daojia-Datenbank“

《MySQL or/in/union und Indexoptimierung | Der Weg zum Architekten》