Lösung für Indexfehler, die durch implizite MySQL-Typkonvertierung verursacht werden

Frage

Bei der Arbeit stellte ich fest, dass es eine Schnittstelle gab, die nur eine SQL-Abfrageanweisung ausführte. Das SQL verwendete eindeutig die Primärschlüsselspalte, die Geschwindigkeit war jedoch sehr langsam.
Nach EXPLAINN in MySQL wurde festgestellt, dass der Primärschlüsselindex während der Ausführung nicht verwendet wurde, aber ein vollständiger Tabellenscan durchgeführt wurde.

Reproduktion

Die Datentabelle DDL sieht wie folgt aus, wobei user_id als Primärschlüsselindex verwendet wird:

 CREATE TABLE `Benutzernachricht` (
   `user_id` varchar(50) NOT NULL COMMENT 'Benutzer-ID',
   `msg_id` int(11) NOT NULL COMMENT 'Nachrichten-ID',
   PRIMÄRSCHLÜSSEL (`user_id`)
 )ENGINE=InnoDB STANDARD-CHARSET=utf8mb4;

Führen Sie die folgende Abfrageanweisung aus und stellen Sie fest, dass der Schlüssel zwar anzeigt, dass der Primärschlüsselindex verwendet wird, die Zeilen jedoch zeigen, dass die gesamte Tabelle gescannt wird und der Primärschlüsselindex nicht funktioniert:

 EXPLAIN SELECT COUNT(*) FROM Benutzernachricht WHERE Benutzer-ID = 1;
 ​
 ID|Typ auswählen|Tabelle |Partitionen|Typ |mögliche Schlüssel|Schlüssel |Schlüssellänge|Ref.|Zeilen |gefiltert|Extra |
 --+--------------+------------+----------+-----+-------------+-----------+-----------+-----------+-----------+--------+------------------------+
  1|SIMPLE |user_message| |index|PRIMARY |PRIMARY|206 | |10000| 10.0|Mit where; Mit index|

Nach der Untersuchung wurde festgestellt, dass das Feld user_id in der Datentabelle vom Typ VARCHAR und die user_id in der SQL-Anweisung vom Typ INT ist. MySQL führt bei der Ausführung der Anweisung eine Typkonvertierung durch, die dazu führen sollte, dass der Primärschlüsselindex nach der Typkonvertierung ungültig wird.

Implizite Konvertierung

Die offizielle Dokumentation von MySQL: https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html, stellt die Regeln der impliziten MySQL-Typkonvertierung vor:

Wenn die Operandentypen auf beiden Seiten eines Operators inkonsistent sind, führt MySQL Typkonvertierungen durch, um die Operanden kompatibel zu machen. Diese Konvertierungen erfolgen implizit. Im Folgenden werden die impliziten Konvertierungen für Vergleichsoperationen beschrieben:

• Wenn ein oder beide Argumente NULL sind, ist das Ergebnis des Vergleichs NULL, mit Ausnahme des Gleichheitsvergleichsoperators <=>, bei dem NULL <=> NULL ohne Konvertierung als „true“ ausgewertet wird.
• Wenn beide Argumente in einer Vergleichsoperation Zeichenfolgen sind, werden sie als Zeichenfolgen verglichen.
• Wenn beide Argumente ganze Zahlen sind, werden sie als ganze Zahlen verglichen.
• Wenn Sie einen Hexadezimalwert nicht mit einer Zahl vergleichen, wird er als Binärzeichenfolge behandelt.
• Wenn eines der Argumente eine TIMESTAMP- oder DATETIME-Spalte und das andere eine Konstante ist, wird die Konstante vor dem Vergleich in einen Zeitstempel umgewandelt. Dies wird für die Argumente von IN() nicht durchgeführt. Um sicherzugehen, verwenden Sie bei Vergleichen immer vollständige Datums- und Uhrzeitzeichenfolgen. Um beispielsweise bei der Verwendung von BETWEEN mit Datums- oder Uhrzeitwerten die besten Ergebnisse zu erzielen, verwenden Sie CAST(), um diese Werte explizit in den gewünschten Datentyp zu konvertieren.
• Eine einzeilige Unterabfrage für eine oder mehrere Tabellen wird nicht als Konstante betrachtet. Wenn eine Unterabfrage beispielsweise eine Ganzzahl zurückgibt, die mit einem DATETIME-Wert verglichen werden soll, wird der Vergleich als zwei Ganzzahlen durchgeführt; die Ganzzahl wird nicht in einen Zeitwert konvertiert. Siehe vorheriges Element. Verwenden Sie in diesem Fall CAST(), um den resultierenden ganzzahligen Wert der Unterabfrage in DATETIME zu konvertieren.
• Wenn eines der Argumente ein Dezimalwert ist, hängt der Vergleich vom anderen Argument ab. Die Argumente werden als Dezimalwerte verglichen, wenn das andere Argument ein Dezimalwert oder ein ganzzahliger Wert ist, wenn das andere Argument ein Gleitkommawert ist, werden die Argumente als Gleitkommawerte verglichen.
• In allen anderen Fällen werden die Argumente als Gleitkommazahlen (reelle Zahlen) verglichen. Beispielsweise werden Zeichenfolgen- und numerische Operanden als Gleitkommazahlen verglichen.

Gemäß der letzten Regel oben wird in der vorherigen SQL-Anweisung der Vergleich zwischen dem String und dem Integer in zwei Gleitkommavergleiche umgewandelt. Auf der linken Seite wird der Stringtyp „1“ in die Gleitkommazahl 1,0 umgewandelt, und auf der rechten Seite wird der INT-Typ 1 in die Gleitkommazahl 1,0 umgewandelt.

Da es sich bei beiden Seiten um Gleitkommazahlen handelt, müsste der Index logischerweise verwendbar sein. Warum wird er bei der Ausführung nicht verwendet?

Der Grund dafür ist, dass die Konvertierungsregeln zum Konvertieren von Zeichenfolgen in Gleitkommatypen in MySQL wie folgt lauten:

1. Alle Zeichenfolgen, die nicht mit einer Zahl beginnen, werden in 0 umgewandelt:

 Wählen Sie CAST('abc' AS UNSIGNED)
 ​
 CAST('abc' AS UNSIGNED) |
 --------------------------------------+
                       0|

2. Beim Konvertieren einer Zeichenfolge, die mit einer Zahl beginnt, wird diese vom ersten Zeichen bis zum ersten nicht-digitalen Inhalt abgefangen:

 SELECT CAST(' 0123abc' AS UNSIGNED)
 ​
 CAST('0123abc' AS UNSIGNED) |
 ----------------------------+
                          123|

Daher werden in MySQL Zeichenfolgen wie „1“, „1“, „1a“ und „01“ alle als 1 in Zahlen umgewandelt.

Wenn MySQL die obige SQL-Anweisung ausführt, konvertiert es den Wert der Primärschlüsselspalte jeder Zeile in eine Gleitkommazahl (die CAST-Funktion wird auf dem Primärschlüssel ausgeführt) und vergleicht ihn dann mit dem Bedingungsparameter. Die Verwendung einer Funktion auf einer Indexspalte führt dazu, dass der Index ungültig wird, was letztendlich zu einem vollständigen Tabellenscan führt.

Wir müssen nur die im vorherigen SQL übergebenen Parameter in Zeichenfolgen ändern und können dann den Primärschlüsselindex verwenden:

 EXPLAIN SELECT COUNT(*) FROM Benutzernachricht WHERE Benutzer-ID = '1';
 ​
 ID|Typ auswählen|Tabelle |Partitionen|Typ|mögliche Schlüssel|Schlüssel |Schlüssellänge|Ref |Zeilen|gefiltert|Extra |
 --+-----------+------------+----------+----+-------------+-----------+-----------+-----+----+--------+-----------+
  1|SIMPLE |user_message| |ref |PRIMARY |PRIMARY|202 |const| 135| 100.0|Index wird verwendet|

Zusammenfassen

1. Wenn die Bedingungsspalte eine Zeichenfolge ist und der übergebene Bedingungsparameter eine Ganzzahl ist, wird er zuerst in eine Gleitkommazahl umgewandelt und dann die gesamte Tabelle gescannt, was zu einem Indexfehler führt.
2. Die bedingten Parameter sollten möglichst vom gleichen Typ wie die Spalte sein, um eine implizite Konvertierung zu vermeiden, oder führen Sie die Konvertierungsfunktion für die übergebenen Parameter aus, um sie in den gleichen Typ wie die Indexspalte zu konvertieren.

siehe

1. Kurze Analyse der impliziten MySQL-Konvertierung

Dies ist das Ende dieses Artikels zur Lösung des Problems der Indexungültigkeit, das durch implizite MySQL-Typkonvertierung verursacht wird. Weitere Informationen zur impliziten MySQL-Typkonvertierung, die zur Indexungültigkeit führt, 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!