Zusammenfassung der zehn häufigsten Probleme bei MySQL-Indexfehlern

Hintergrund

Vor kurzem wurde aufgrund der Verwendung von „or“ und „ !=“ eine langsame SQL-Anweisung erstellt, die einen Indexfehler verursachte. Ich habe also die zehn häufigsten Ursachen für Indexfehler zusammengefasst. Ich hoffe, dass sie Ihnen weiterhelfen. Kommen Sie.

1. Die Abfragebedingung enthält "oder", was dazu führen kann, dass der Index fehlschlägt

Erstellen Sie eine neue Benutzertabelle mit einem gemeinsamen Index „userId“ und folgender Struktur:

CREATE TABLE `Benutzer` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `userId` int(11) NICHT NULL,
  `Alter` int(11) NICHT NULL,
  `name` varchar(255) NICHT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_userId` (`userId`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

Bei der Ausführung einer SQL-Abfrage wird der Index durchsucht, wie in der folgenden Abbildung dargestellt:

Das Hinzufügen der Bedingung „oder“ + „Alter“ ohne Index führt dazu, dass der Index nicht verwendet wird, wie in der Abbildung gezeigt:

Analyse & Fazit:

• Nehmen wir für den Fall von „oder+Alter“ ohne Index an, dass der Index der Benutzer-ID verwendet wird. Bei der Abfragebedingung „Alter“ muss jedoch immer noch die gesamte Tabelle gescannt werden. Dies bedeutet, dass drei Schritte erforderlich sind: vollständiger Tabellenscan + Indexscan + Zusammenführen
  
• Wenn es mit einem vollständigen Tabellenscan beginnt, wird dieser in einem Durchgang durchgeführt.
  
• MySQL verfügt über einen Optimierer. Aus Effizienz- und Kostengründen kann der Index beim Auftreten einer ODER-Bedingung ungültig werden, was sinnvoll erscheint.

Hinweis: Wenn die Spalten in der Bedingung „oder“ indexiert sind, kann die Indexierung fehlschlagen. Sie können es selbst versuchen.

2. Wenn der Feldtyp eine Zeichenfolge ist, müssen Sie ihn bei der Verwendung von where unbedingt in Anführungszeichen einschließen, da sonst der Index ungültig ist

Nehmen wir an, dass die Demotabelle wie folgt aufgebaut ist:

CREATE TABLE `Benutzer` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `userId` varchar(32) NICHT NULL,
  `name` varchar(255) NICHT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_userId` (`userId`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

userId ist ein Zeichenfolgentyp, der ein gemeinsamer Index des B+-Baums ist. Wenn in der Abfragebedingung eine Nummer übergeben wird, wird sie nicht durch den Index geleitet, wie in der Abbildung dargestellt:

Wenn Sie der Zahl '' hinzufügen, also eine Zeichenfolge übergeben, erfolgt die Sortierung natürlich nach Index, wie unten gezeigt:

Analyse und Fazit:

Warum durchläuft die erste Anweisung den Index nicht ohne einfache Anführungszeichen? Dies liegt daran, dass ohne einfache Anführungszeichen der Vergleich zwischen Zeichenfolgen und Zahlen erfolgt und deren Typen nicht übereinstimmen. MySQL führt eine implizite Typkonvertierung durch und wandelt sie vor dem Vergleich in Gleitkommazahlen um.

3. Ähnliche Platzhalter können zur Ungültigkeit des Index führen.

Es ist nicht so, dass der Index ungültig wird, wenn das Like-Platzhalterzeichen verwendet wird, sondern dass die Like-Abfrage mit % beginnt, was dazu führt, dass der Index ungültig wird.

Tabellenstruktur:

CREATE TABLE `Benutzer` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `userId` varchar(32) NICHT NULL,
  `name` varchar(255) NICHT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_userId` (`userId`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Wenn die Abfrage mit „%“ beginnt, ist der Index ungültig, wie in der Abbildung gezeigt:

Setzen Sie am Ende „%“ und stellen Sie fest, dass der Index weiterhin normal funktioniert, und zwar wie folgt:

Fügen Sie % wieder hinzu und ändern Sie es so, dass nur die indexierten Felder ( die den Index abdecken ) durchsucht werden. Ich habe festgestellt, dass der Index immer noch verwendet wird. Sind Sie überrascht?

abschließend:

Wenn die Like-Abfrage mit % beginnt, wird der Index ungültig. Zur Optimierung gibt es zwei Möglichkeiten:

• Verwenden von überdeckenden Indizes
• Setzen Sie % am Ende

Hinweis: Ein Index, der alle Daten enthält, die die Abfrageanforderungen erfüllen, wird als überdeckender Index bezeichnet.

4. Kombinierter Index: Wenn die Bedingungsspalte während der Abfrage nicht die erste Spalte im kombinierten Index ist, ist der Index ungültig.

Tabellenstruktur: (Es gibt einen gemeinsamen Index idx_userid_age , zuerst userId , dann age )

CREATE TABLE `Benutzer` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `userId` int(11) NICHT NULL,
  `Alter` int(11) DEFAULT NULL,
  `name` varchar(255) NICHT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_userid_age` (`userId`,`age`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Wenn in einem gemeinsamen Index die Abfragebedingung das Prinzip der ganz links stehenden Übereinstimmung erfüllt, ist der Index normal wirksam. Bitte sehen Sie sich die Demo an:

Wenn die Bedingungsspalte nicht die erste Spalte im gemeinsamen Index ist, wird der Index wie folgt ungültig:

Analyse und Fazit:

• Wenn wir einen gemeinsamen Index erstellen, beispielsweise (k1, k2, k3), entspricht dies der Erstellung von drei Indizes: (k1), (k1, k2) und (k1, k2, k3). Dies ist das Prinzip der Übereinstimmung ganz links.
• Der gemeinsame Index erfüllt nicht das Prinzip „ganz links“ und der Index wird im Allgemeinen ungültig. Dies hängt jedoch auch mit dem MySQL-Optimierer zusammen.

5. Die Verwendung der integrierten Funktionen von MySQL auf der Indexspalte führt dazu, dass der Index ungültig wird.

Tabellenstruktur:

CREATE TABLE `Benutzer` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `userId` varchar(32) NICHT NULL,
  `loginTime` datetime NICHT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_userId` (`userId`) MIT BTREE,
  SCHLÜSSEL `idx_login_time` (`loginTime`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Obwohl loginTime indiziert ist, ist der Index direkt GG, da die integrierte Funktion Date_ADD () von MySQL verwendet wird, wie in der Abbildung gezeigt:

6. Wenn Operationen an Indexspalten ausgeführt werden (wie +, -, *, /), wird der Index ungültig.

Tabellenstruktur:

CREATE TABLE `Benutzer` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `userId` varchar(32) NICHT NULL,
  `Alter` int(11) DEFAULT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_age` (`Alter`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Obwohl das Alter indexiert ist, geht der Index verloren, da er berechnet wird. . . Wie in der Abbildung gezeigt:

7. Wenn (!= oder < >, nicht in) im Indexfeld verwendet wird, kann der Index ungültig werden.

Tabellenstruktur:

CREATE TABLE `Benutzer` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `userId` int(11) NICHT NULL,
  `Alter` int(11) DEFAULT NULL,
  `name` varchar(255) NICHT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_age` (`Alter`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Obwohl das Alter indiziert ist, wird es verwendet! = oder < >, nicht drin, der Index ist Dummy. wie folgt:

8. Die Verwendung von „is null“ und „is not null“ im Indexfeld kann dazu führen, dass der Index fehlschlägt.

Tabellenstruktur:

CREATE TABLE `Benutzer` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `Karte` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_name` (`name`) USING BTREE,
  SCHLÜSSEL `idx_card` (`card`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Wenn ein einzelnes Namensfeld indiziert ist und die Abfrage nach einem nicht leeren Namen ausgeführt wird, wird der Index wie folgt verwendet:

Wenn ein einzelnes Kartenfeld indiziert ist und die Abfrage für einen nicht leeren Namen ausgeführt wird, wird der Index wie folgt verwendet:

Wenn es jedoch mit „oder“ verbunden wird, wird der Index wie folgt ungültig:

9. Die Kodierungsformate der mit der Left Join-Abfrage oder der Right Join-Abfrage verknüpften Felder sind unterschiedlich, was zu einem Indexierungsfehler führen kann.

Erstellen Sie zwei neue Tabellen, eine Benutzertabelle und eine Benutzerjobtabelle.

CREATE TABLE `Benutzer` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `name` varchar(255) ZEICHENSATZ utf8mb4 STANDARD NULL,
  `Alter` int(11) NICHT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_name` (`name`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
 
Tabelle „user_job“ erstellen (
  `id` int(11) NICHT NULL,
  `userId` int(11) NICHT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_name` (`name`) MIT BTREE
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

Das Namensfeld der Benutzertabelle ist in utf8mb4 codiert, während das Namensfeld der User_Job-Tabelle in utf8 codiert ist.

Führen Sie die Left-Outer-Join-Abfrage aus. Die Tabelle „user_job“ durchläuft dennoch wie folgt den vollständigen Tabellenscan:

Wenn Sie sie so ändern, dass sie die gleiche Kodierung wie das Namensfeld haben, wird der Index weiterhin verwendet.

10. MySQL geht davon aus, dass ein vollständiger Tabellenscan schneller ist als ein Index und verwendet daher den Index nicht.

• Wenn die Indizes einer Tabelle abgefragt werden, wird der beste Index verwendet, es sei denn, der Optimierer hält einen vollständigen Tabellenscan für effizienter. Der Optimierer führt eine vollständige Tabellenüberprüfung durch, je nachdem, ob die mit dem besten Index gefundenen Daten 30 % der Daten in der Tabelle überschreiten.
• Fügen Sie keine Indizes zu „Geschlecht“ usw. hinzu. Wenn eine Datenspalte Werte wie „0/1“ oder „J/N“ enthält, also viele doppelte Werte enthält, ist der Indexeffekt nicht sehr gut, selbst wenn ein Index dafür erstellt wird, und kann sogar zu einem vollständigen Tabellenscan führen.

Aus Effizienz- und Kostengründen schätzt MySQL, was schneller ist: ein vollständiger Tabellenscan oder die Verwendung eines Indexes. Dies hängt mit seinem Optimierer zusammen. Werfen wir einen Blick auf sein logisches Architekturdiagramm (Bildquelle: online)

Zusammenfassen

Wir haben die zehn häufigsten Ursachen für Indexfehler zusammengefasst. Lassen Sie uns die langsame SQL-Anweisung analysieren, die wir erstellt haben. Die simulierte Tabellenstruktur und das verursachende SQL sind wie folgt:

Tabelle „Benutzersitzung“ erstellen (
  `user_id` varchar(32) ZEICHENSATZ utf8mb4 NICHT NULL,
  `device_id` varchar(64) NICHT NULL,
  `status` varchar(2) NICHT NULL,
  `create_time` datetime NICHT NULL,
  `update_time` datetime STANDARD NULL BEIM UPDATE CURRENT_TIMESTAMP,
  PRIMÄRSCHLÜSSEL (`user_id`,`device_id`) MIT BTREE
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

erklären 
Benutzersitzung aktualisieren, Status festlegen = 1
wobei (`user_id` = '1' und `device_id` != '2')
oder (`user_id` != '1' und `device_id`='2')

analysieren:

• Das ausgeführte SQL verwendet or . Aufgrund des zusammengesetzten Primärschlüssels ( user_id , device_id ) sieht es so aus, als ob jede Spalte indiziert ist und der Index wirksam wird.
• Das Vorhandensein von != kann jedoch dazu führen, dass der Index ungültig wird. Das heißt, die beiden Hauptsyndrome or + != führen zu einer langsamen Aktualisierung von SQL.

Lösung:

Also, wie lässt sich das Problem lösen? Wir entfernen or -Bedingung und teilen es in zwei Ausführungen auf. Fügen Sie gleichzeitig einen normalen Index zur device_id hinzu.

Abschließend werden die zehn häufigsten Ursachen für Indexfehler zusammengefasst. Ich hoffe, dass jeder diese zehn Ursachen bei seiner Arbeit und seinem Studium berücksichtigen kann, die expain mit den Szenarien kombiniert und spezifische Analysen durchführt, anstatt der Routine zu folgen und sich an die Regeln zu halten, um zu bestimmen, welches Szenario zum Indexfehler führt.

Dies ist das Ende dieses Artikels über die zehn häufigsten Probleme bei MySQL-Indexfehlern. Weitere Informationen zu den zehn häufigsten Problemen bei MySQL-Indexfehlern finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den verwandten Artikeln weiter unten. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!