Eine kurze Erläuterung der Situationen in MySQL, die zu Indexfehlern führen

Hier einige Tipps von Ausbildungsstätten und mein eigenes Resümee:
Um den folgenden Indexinhalt zu erklären, erstellen wir zunächst eine temporäre Tabelle test02

Tabelle „sys_user“ erstellen (
  `id` varchar(64) NOT NULL COMMENT 'Primärschlüssel',
  `name` varchar(64) STANDARD NULL KOMMENTAR 'name',
  `Alter` int(64) DEFAULT NULL KOMMENTAR 'Alter',
  `pos` varchar(64) DEFAULT NULL KOMMENTAR 'Position',
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_sys_user_nameAgePos` (`Name`,`Alter`,`Pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Benutzertabelle';

Diese Tabelle hat vier Felder: Primärschlüssel, Name, Alter, Position

Lassen Sie uns die erste Formel erklären:
1. Vollwertiges Match mit meinem Favoriten
2. Regel für das beste linke Präfix (wichtig)
Vollständige Wertübereinstimmung bedeutet, dass die Reihenfolge und Anzahl der kombinierten zusammengesetzten Indizes mit der Reihenfolge und Anzahl der Suchbedingungen übereinstimmen müssen.
Die Regel des besten linken Präfixes bedeutet, dass bei der Indizierung mehrerer Spalten die Regel des ganz linken Präfixes befolgt werden muss. Das bedeutet, dass die Abfrage in der äußersten linken Spalte des Index beginnt und keine Spalten im Index überspringt. <br /> Als Nächstes erstellen wir einen zusammengesetzten Index für diese Tabelle.

ALTER TABLE sys_user ADD INDEX idx_sys_user_nameAgePos(Name, Alter, Position);

Nachfolgend sehen Sie unsere Suchanweisung:

SELECT * FROM sys_user WHERE name = ‚Xiaoming‘ AND age = 22 AND pos = ‚java‘;

Ob der Index verwendet wird, können wir erkennen, indem wir vor die Suchanweisung das Schlüsselwort EXLAIN setzen.

(1) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';
(2) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22;
(3) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND pos = 'java';

Aus den angezeigten Ergebnissen können wir ersehen, dass wir alle drei Felder des ersten zusammengesetzten Index, nur zwei Felder des zweiten zusammengesetzten Index und nur ein Feld des dritten zusammengesetzten Index verwendet haben. Wir verwenden in allen drei Anweisungen Indizes und die erste ist offensichtlich die beste.

Mal sehen, was schief geht:

(4) EXPLAIN SELECT * FROM sys_user WHERE age = 22;
(5) EXPLAIN SELECT * FROM sys_user WHERE pos = 'java';
(6) EXPLAIN SELECT * FROM sys_user WHERE age = 22 AND pos = 'java';

Die oben genannten drei Situationen werden alle zu vollständigen Tabellenscans, da sie das Prinzip des ganz linken Präfixes verletzen. Da die ganz linke Spalte des zusammengesetzten Indexes Name ist, ist der Index ungültig, wenn die Suchbedingung Name nicht vorne steht. Die erste Situation erfüllt die vollständige Werteübereinstimmung, die zweite erfüllt die beiden Felder Name und Alter und die dritte Situation erfüllt nur Name, sodass der Index nur Name verwendet.

3. Führen Sie keine Vorgänge (Berechnungen, Funktionstypkonvertierungen (automatisch oder manuell)) an der Indexspalte durch, da dies den Index ungültig macht und in einen vollständigen Tabellenscan umwandelt.

(7) EXPLAIN SELECT * FROM sys_user WHERE LEFT(name,1)='Xiaoming';

Der siebte Fall schlägt fehl, weil die Indexspalte Berechnungen oder Funktionsoperationen ausführt, was zu einem vollständigen Tabellenscan führt.

4. Die Speicher-Engine kann die Spalten auf der rechten Seite der Bereichsbedingung im Index nicht verwenden <br /> Vielleicht verstehen Sie nicht, was der obige Text bedeutet. Führen wir die folgende Abfrageanweisung aus, um ihn zu verstehen.

(8) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age < 22 AND pos = 'java';

Aus der obigen Abbildung können wir ersehen, dass der Typ zu einer Bereichsebene geworden ist, was bedeutet, dass der Index des POS-Felds nach Alter <22 ungültig ist.

5. Versuchen Sie, abdeckende Indizes zu verwenden (Abfragen, die nur auf den Index zugreifen (Indexspalten und Abfragespalten sind identisch) und reduzieren Sie die Verwendung von select * <br /> Dies ist die wörtliche Bedeutung. Das Abfragen bestimmter Felder ist effizienter als das Abfragen von *. Vergleichen wir es.

(9) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';
(10) EXPLAIN SELECT name, age, pos FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';

6. MySQL kann keine Indizes verwenden, wenn ungleiche Werte (!= oder <>) verwendet werden, was zu einem vollständigen Tabellenscan führt
(!= oder <>) entspricht normalerweise einer großen Datenmenge. Wenn der Aufwand für die Verwendung des Index höher ist als bei einem vollständigen Tabellenscan, gibt MySQL die Verwendung des Index auf und wählt stattdessen einen vollständigen Tabellenscan.

(11) EXPLAIN SELECT * FROM sys_user WHERE name != 'Xiaoming'

Die Ergebnisse zeigen, dass der Indexfehler einen vollständigen Tabellenscan verursacht hat

7. Ist null, ist nicht null und Indizes können nicht verwendet werden
ist null, ist nicht null entspricht normalerweise einer großen Datenmenge. Wenn die Kosten für die Verwendung des Index höher sind als bei einem vollständigen Tabellenscan, gibt MySQL die Verwendung des Index auf und wählt stattdessen einen vollständigen Tabellenscan.

(12) EXPLAIN SELECT * FROM sys_user WHERE name ist nicht null

8. Wenn „like“ mit einem Platzhalter („%abc...“) beginnt, wird der MySQL-Index ungültig und der Vorgang wird zu einem vollständigen Tabellenscan. (Durch das Schreiben von „%“ auf der rechten Seite kann die Ungültigkeit des Index vermieden werden. Wenn das Unternehmen „%abc...%“ wirklich benötigt, können Sie einen überdeckenden Index verwenden, um die Ungültigkeit des Index zu vermeiden.)

(13) EXPLAIN SELECT * FROM sys_user WHERE Name wie '%明%'
(14) EXPLAIN SELECT * FROM sys_user WHERE Name wie '明%'
(15) EXPLAIN SELECT name,age,pos FROM sys_user WHERE name like '%明%'

Aus den obigen Ergebnissen geht hervor, dass der erste Index fehlschlägt. Der zweite Index kann Indexfehler vermeiden, indem er einfach das % auf der rechten Seite schreibt. Der dritte Index kann das Problem des Indexfehlers lösen, indem er einen abdeckenden Index verwendet, wenn das Unternehmen wirklich ein SQL wie „%abc...%“ benötigt.

9. Die Indizierung von Zeichenfolgen schlägt fehl, wenn sie nicht in einfache Anführungszeichen eingeschlossen sind

(16) EXPLAIN SELECT * FROM sys_user WHERE name=222;

Da die Suchzeichenfolge in einfache Anführungszeichen eingeschlossen werden muss, ist die oben verwendete Zahl 222 vom Typ int. Bei der Suche ermittelt MySQL, dass der Name vom Typ varchar ist, und konvertiert 222 zum Abrufen in „222“. Die Indexspalte wird einer Typkonvertierung unterzogen, sodass der Index ungültig wird.

10. Verwenden Sie oder weniger, da es den Index ungültig macht, wenn es zum Verbinden verwendet wird

(16) EXPLAIN SELECT * FROM sys_user WHERE name = "Xiaoming" oder age = 22; 

Dies ist das Ende dieses Artikels über die Situationen, in denen MySQL-Indizes ungültig werden. Weitere Informationen zur Ungültigkeit von MySQL-Indizes 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!