Zusammenfassung einiger gängiger Schreibmethoden, die zu MySQL-Indexfehlern führen

Vorwort

Vor kurzem war ich damit beschäftigt, einige SQL-Optimierungsprobleme zu lösen, die noch aus dem alten Projekt übrig waren. Aufgrund der Probleme im ursprünglichen Tabellen- und Felddesign traten mit dem Wachstum des Unternehmens viele langsame SQLs auf, was dazu führte, dass die CPU-Ressourcen von MySQL in die Höhe schossen. Auf dieser Grundlage möchte ich diese relativ praktischen und leicht zu erlernenden und zu verwendenden Erfahrungen kurz mit Ihnen teilen.

Dieses Mal werde ich kurz darüber sprechen, wie Sie verhindern können, dass Ihr Index ungültig wird.

Bevor ich fortfahre, möchte ich Ihnen zunächst meine Ansichten zu Indizes auf Grundlage meiner jüngsten Erfahrungen darlegen. Ich glaube nicht, dass alle Tabellen indiziert werden müssen. Bei manchen Geschäftsdaten kann das Volumen groß sein und die Abfrage der Daten kann etwas anstrengend sein. In diesem Fall ist die einfachste und schnellste Möglichkeit, einen geeigneten Index zu erstellen. Bei manchen Unternehmen sind jedoch möglicherweise nicht viele Daten in der Tabelle enthalten oder die Tabelle wird nicht sehr häufig verwendet, sodass kein Index erstellt werden muss. Beispielsweise enthalten einige unserer Tabellen in zwei Jahren möglicherweise nur etwa 10 Daten, und die Leistung mit und ohne Indizes ist nahezu gleich.

Die Indizierung ist für uns lediglich eine Möglichkeit, unser Geschäft zu optimieren. Wir sollten niemals Indizes nur des Indizierungswillens wegen erstellen.

Unten finden Sie eine Tabellenstruktur und einige Testdaten, die ich in diesem Test verwendet habe

CREATE TABLE `Benutzer` (
 `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
 `create_time` datetime NICHT NULL,
 `name` varchar(5) NICHT NULL,
 `Alter` tinyint(2) unsigned zerofill NICHT NULL,
 `sex` char(1) NICHT NULL,
 `mobile` char(12) NICHT NULL STANDARD '',
 `Adresse` char(120) DEFAULT NULL,
 `Höhe` varchar(10) DEFAULT NULL,
 Primärschlüssel (`id`),
 SCHLÜSSEL `idx_createtime` (`create_time`) MIT BTREE,
 SCHLÜSSEL `idx_name_age_sex` (`Name`,`Geschlecht`,`Alter`) MIT BTREE,
 SCHLÜSSEL `idx_height` (`height`) USING BTREE,
 SCHLÜSSEL `idx_address` (`Adresse`) USING BTREE,
 SCHLÜSSEL `idx_age` (`Alter`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8;

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::3s::::::333:33333333333333333333ag33333333333333333333333333333 es333333333333333333333333333333 es33 nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht dann dann dann dann aber33333333333333333333 nicht3 nicht3 nicht3 nicht3 nicht3 nicht3 nicht3 nicht3 dann3 dann3 aber3 dann3 nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht dann nichtie dasen aber aber abersossoss aberstens aberstensss aberten aber abers :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::3s::::::333:33333333333333333333ag33333333333333333333333333333 es333333333333333333333333333333 es33 nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht dann dann dann dann aber33333333333333333333 nicht3 nicht3 nicht3 nicht3 nicht3 nicht3 nicht3 nicht3 dann3 dann3 aber3 dann3 nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht nicht dann nichtie dasen aber aber abersossoss aberstens aberstensss aberten aber abers :::::::::::

Einzelindex

1. Die Verwendung von != oder <> führt zum Fehlschlagen des Index

SELECT * FROM `Benutzer` WHERE `Name` != 'Bingfeng';

Wir haben einen Index für das Namensfeld erstellt, aber wenn != oder <> verwendet wird, schlägt der Index fehl und es wird ein vollständiger Tabellenscan durchgeführt. Verwenden Sie es daher mit Vorsicht, wenn die Datenmenge groß ist.

Durch die Analyse des SQL können wir erkennen, dass der Typ ALL ist, 10 Datenzeilen gescannt werden und ein vollständiger Tabellenscan durchgeführt wird. <> hat auch das gleiche Ergebnis.

2. Indexfehler aufgrund inkonsistenter Typen

Bevor ich darüber spreche, muss ich sagen, dass Sie beim Entwerfen von Tabellenfeldern unbedingt die Konsistenz der Feldtypen wahren müssen. Was bedeutet das? Wenn die ID in der Benutzertabelle beispielsweise vom Typ int und automatisch inkrementiert ist, muss das Feld user_id in der Kontotabelle des Benutzers ebenfalls vom Typ int sein. Schreiben Sie es nicht als varchar, char oder etwas Ähnliches.

SELECT * FROM `Benutzer` WHERE Höhe = 175;

Sie müssen dieses SQL sorgfältig lesen. Der Feldtyp der Höhentabelle ist varchar, aber ich habe bei der Abfrage den numerischen Typ verwendet. Da in der Mitte eine implizite Typkonvertierung stattfindet, wird der Index ungültig und es wird ein vollständiger Tabellenscan durchgeführt.

Jetzt verstehen Sie, warum ich gesagt habe, dass Sie beim Entwerfen von Feldern die Typkonsistenz beibehalten müssen. Wenn Sie die Konsistenz nicht sicherstellen, können ein int und ein varchar den Index zwangsläufig nicht verwenden, wenn eine gemeinsame Abfrage mehrerer Tabellen ausgeführt wird (z. B.: 1 = '1').

Möglicherweise empfinden Sie es immer noch als schmerzhaft, auf eine Tabelle wie diese zu stoßen, die Millionen von Daten enthält und nicht geändert werden kann.

Junge Leute, denkt daran, denkt daran.

3. Indexfehler durch Funktion verursacht

SELECT * FROM `Benutzer` WHERE DATE(Erstellungszeit) = '2020-09-03';

Wenn Ihr Indexfeld einen Index verwendet, tut es mir leid, aber es verwendet den Index nicht wirklich.

4. Durch Operatoren verursachter Indexfehler

SELECT * FROM `Benutzer` WHERE Alter - 1 = 20;

Wenn Sie (+, -, *, /, !) auf der Spalte ausführen, wird der Index nicht verwendet.

5. Indexfehler verursacht durch OR

SELECT * FROM `Benutzer` WHERE `Name` = 'Benutzername' OR Höhe = '175';

OR führt unter bestimmten Umständen zur Indizierung. Nicht alle ORs machen Indizes ungültig. Wenn OR dasselbe Feld verbindet, wird der Index nicht ungültig. Andernfalls wird der Index ungültig.

6. Indexfehler durch Fuzzy-Suche

SELECT * FROM `Benutzer` WHERE `Name` LIKE '%Name';

Ich glaube, das versteht jeder. Wenn Sie eine Fuzzy-Suche nach dem Präfix durchführen, wird der Index nicht verwendet.

7. NOT IN und NOT EXISTS verursachen Indexfehler

Wählen Sie s.* aus „Benutzer“ s, wo nicht vorhanden (wählen Sie * aus „Benutzer“ u, wo u.name = s. „Name“ und u. „Name“ = „Benutzer“)

SELECT * FROM `Benutzer` WHERE `Name` NOT IN ('Bingfeng');

Diese beiden Verwendungen machen den Index ebenfalls ungültig. NOT IN verwendet den Index jedoch weiterhin. Verstehen Sie das nicht falsch und sagen Sie nicht, IN würde den Index überhaupt nicht verwenden. Bei mir kam es schon früher zu Missverständnissen (schäm dich ...).

8. IS NULL verwendet den Index nicht, IS NOT NULL verwendet den Index

SELECT * FROM `Benutzer` WHERE Adresse IS NULL 

Folgen Sie nicht dem Index.

SELECT * FROM `Benutzer` WHERE Adresse ist nicht NULL; 

Gehen Sie den Index entlang.

Aufgrund dieser Situation wird empfohlen, beim Entwerfen eines Felds, wenn es nicht unbedingt erforderlich ist, dass es NULL sein muss, einen Standardwert in Form einer leeren Zeichenfolge anzugeben, da dies viele nachfolgende Probleme lösen kann (umfassende Erfahrung <Erfahrung=Lektion>).

Übereinstimmungsindex

1. Prinzip der Übereinstimmung ganz links

EXPLAIN SELECT * FROM `Benutzer` WHERE Geschlecht = 'Benutzer';

EXPLAIN SELECT * FROM `user` WHERE name = 'Benutzer' AND sex = 'Geschlecht';

Löschen Sie vor dem Testen andere einspaltige Indizes.

Was ist das Leftmost-Matching-Prinzip? Es bedeutet, dass für einen Matching-Index die Reihenfolge eines Index von links nach rechts verglichen wird. Beispielsweise wird in der zweiten Abfrageanweisung im Index nach Name und dann nach Alter gesucht. Wenn Alter nicht in der Ergebnisbedingung enthalten ist, wird das nachfolgende Geschlecht nicht im Index gesucht.

Beachten:

SELECT * FROM `user` WHERE sex = 'Benutzer' AND age = 22 AND `name` = 'Name';

Vielleicht haben einige Maurer am Anfang ein Missverständnis mit mir. Unsere Indexreihenfolge ist offensichtlich Name, Geschlecht, Alter, und Ihre aktuelle Abfragereihenfolge ist Geschlecht, Alter, Name. Dies folgt definitiv nicht dem Index. Wenn Sie es nicht selbst getestet haben und eine so unreife Idee haben, dann sind Sie wie ich zu jung. Es hat eigentlich nichts mit der Reihenfolge zu tun, denn die zugrunde liegende Schicht von MySQL wird uns helfen, sie zu optimieren. Sie wird Ihr SQL optimieren, um es auf die effizienteste Weise auszuführen. Lassen Sie dieses Missverständnis also nicht aufkommen.

2. Wenn != verwendet wird, werden alle nachfolgenden Indizes ungültig

SELECT * FROM `Benutzer` WHERE Geschlecht = 'Benutzer' AND `Name` != 'Name' AND Alter = 22;

Wir haben != im Namensfeld verwendet. Da das Namensfeld das Feld ganz links ist, werden gemäß dem Prinzip der Übereinstimmung ganz links die folgenden Felder ebenfalls nicht indiziert, wenn das Namensfeld nicht indiziert ist.

Derzeit gibt es nur zwei Situationen, in denen übereinstimmende Indizes zur Ungültigkeit des Index führen. Tatsächlich denke ich, dass das Wichtigste beim Abgleichen von Indizes darin besteht, wie ein effizienter Index erstellt wird. Sie dürfen nicht sagen, dass ich, wenn ich ein Feld verwende, einen separaten Index erstelle und ihn global verwende. Dies ist zwar möglich, erfüllt jedoch nicht die Anforderungen an die Indexeffizienz. Um ein fortgeschrittener Maurer zu werden, müssen wir daher weiterhin lernen, wie man effiziente Indizes erstellt.

Zusammenfassen

Damit ist dieser Artikel über einige gängige Schreibmethoden, die MySQL-Indexfehler verursachen, abgeschlossen. Weitere Informationen zu gängigen Schreibmethoden, die MySQL-Indexfehler verursachen, finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!