„Implizite Konvertierung“ von MySQL 5.6 führt zu Indexfehlern und ungenauen Daten

Hintergrund

• Beim Ausführen einer SQL-Abfrage habe ich versucht, die einfachen Anführungszeichen im Feld vom Typ „vachar“ in der Where-Bedingung zu entfernen. Dabei stellte ich fest, dass diese Anweisung, die eigentlich sehr schnell hätte sein sollen, tatsächlich sehr langsam war. Dieses VARCHAR-Feld hat einen zusammengesetzten Index. Die Gesamtzahl der Einträge beträgt 58989, und selbst ohne einfache Anführungszeichen sind die abgerufenen Daten nicht die gewünschten Daten.
• Es wird die MySQL 5.6-Version verwendet. Die tatsächliche Situation der InnoDB-Engine ist wie folgt

Werfen wir einen Blick auf die Ergebnisse der Ausführung

In der obigen Beschreibung müssen wir auch beachten, dass die Zeichenfolge in Ihrer Where-Bedingung nur aus Zahlen ohne einfache Anführungszeichen bestehen muss. Andernfalls wird ein Fehler gemeldet

Es ist auch möglich, dass die gefundenen Daten nicht die gewünschten Daten sind. Wie unten gezeigt

analysieren

1. Aus den Ausführungsergebnissen können wir erkennen, dass der entsprechende Index in einfachen Anführungszeichen verwendet wird. Ohne einfache Anführungszeichen wird der Index nicht verwendet und ein vollständiger Tabellenscan durchgeführt.
2. Warum passiert das? Warum führt der MySQL-Optimierer die Typkonvertierung nicht direkt durch?

• Die Einführung von einfachen Anführungszeichen in SQL-Anweisungen zeigt an, dass der Typ ein Zeichenfolgendatentyp CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM und SET ist. .
• Ohne einfache Anführungszeichen bedeutet es, dass es sich um einen anderen Typ als eine Zeichenfolge handelt, z. B. int, bigDecimal usw.
• Wenn Sie eine Zeichenfolge mit Untertiteln und Sonderzeichen nicht in einfache Anführungszeichen setzen, schlägt die Typkonvertierung fehl und SQL kann nicht ausgeführt werden.

Wie in der Abbildung oben gezeigt:

1054 - Unbekannte Spalte „000w1993521“ in „Where-Klausel“, Zeit: 0,008000 s

Schauen wir uns zunächst den Ausführungsprozess einer SQL an

(Online-Bild)

• Wir kommen zunächst zu dem Schluss, dass, wenn eine Funktionsoperation auf dem Indexfeld ausgeführt wird (in diesem Fall führt die Cast-Funktion eine implizite Konvertierung durch), die Reihenfolge des Indexwerts zerstört werden kann, sodass der Optimierer beschließt, die Baumsuchfunktion aufzugeben. (https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html)
• [Bildübertragung über externen Link fehlgeschlagen, die Quellsite verfügt möglicherweise über einen Anti-Hotlink-Mechanismus, es wird empfohlen, das Bild zu speichern und direkt hochzuladen (img-l5AwT0xu-1607244327891)(http://note.youdao.com/yws/res/23689/CE6F785994E6476D816B23787CE65217)]
• Das heißt, Sie sollten sich darüber im Klaren sein, dass MySQL den Index möglicherweise nicht effizient nutzen kann, wenn Sie eine indizierte Spalte mit BINARY, CAST() oder CONVERT() konvertieren.
• Die abgerufenen Daten sind aufgrund einer impliziten Konvertierung ungenau. Nach der Konvertierung sind die numerischen Typen unterschiedlich, wodurch aus Ungleichheit Gleichheit wird.
  

Implizite Konvertierung

1. Voraussetzungen für die Generierung <br /> Wenn ein Operator mit Operanden unterschiedlichen Typs verwendet wird, erfolgt eine Typkonvertierung, um die Operanden kompatibel zu machen. Eine implizite Konvertierung erfolgt in folgenden Fällen:

1. Wenn mindestens einer der beiden Parameter NULL ist, ist das Ergebnis des Vergleichs ebenfalls NULL. Die Ausnahme besteht darin, dass die Verwendung von <=> zum Vergleichen zweier NULL-Werte 1 zurückgibt. In beiden Fällen ist keine Typkonvertierung erforderlich.
2. Beide Parameter sind Zeichenfolgen und werden als Zeichenfolgen ohne Typkonvertierung verglichen.
3. Beide Parameter sind ganze Zahlen und werden daher als ganze Zahlen ohne Typkonvertierung verglichen.
4. Hexadezimale Werte werden im Vergleich zu nicht numerischen Werten als Binärzeichenfolgen behandelt.
5. Wenn ein Parameter TIMESTAMP oder DATETIME ist und der andere Parameter eine Konstante, wird die Konstante in einen Zeitstempel umgewandelt.
6. Wenn ein Parameter vom Typ Dezimal ist und der andere Parameter eine Dezimalzahl oder Ganzzahl ist, wird die Ganzzahl zum Vergleich in eine Dezimalzahl umgewandelt. Wenn der andere Parameter eine Gleitkommazahl ist, wird die Dezimalzahl zum Vergleich in eine Gleitkommazahl umgewandelt.
7. In allen anderen Fällen werden beide Argumente vor dem Vergleich in Gleitkommazahlen umgewandelt.

2. Analysieren Sie die tatsächliche Situation

1. Dann ist uns klar, dass es sich bei dem oben erwähnten Beispiel um den Vergleich zwischen Ganzzahlen und Zeichenfolgen handelt, der zu anderen Fällen gehört. Lassen Sie uns zunächst die Gründe für den Indexfehler analysieren.

• Da es sich um eine implizite Konvertierung handelt, müssen alle Vergleichswerte für den Vergleich in Gleitkommazahlen umgewandelt werden.
• Wir konvertieren zuerst den Abfragebedingungswert in eine Gleitkommazahl und konvertieren dann den Datensatzwert der Tabelle. Daher ist die zuvor erstellte Indexsortierung nicht mehr wirksam. Da die implizite Konvertierung (Funktion) den ursprünglichen Wert geändert hat, verwendet der Optimierer den Index hier nicht und führt direkt einen vollständigen Tabellenscan durch.
  

2. Fragen Sie die nicht übereinstimmenden Werte (oder teilweise übereinstimmenden Werte) ab, wie in den obigen Abfrageergebnissen gezeigt. Sie müssen sich unbedingt den Quellcode ansehen, dies ist die implizite Konvertierungsregel von MySQL. Ich werde es hier nicht im Detail analysieren (da keine relevanten Dokumente gefunden wurden)
Aus historischen Gründen ist Kompatibilität mit alten Designs erforderlich. Sie können die MySQL-Typkonvertierungsfunktionen „cast“ und „convert“ verwenden, um explizite Konvertierungen durchzuführen.
Zusammenfassen

• Die Verwendung impliziter Konvertierungen und Funktionen kann zu Indexfehlern und ungenau ausgewählten Daten führen.
• Implizite Konvertierungsbedingungen und Regeln
• Der spezifische Grund, warum implizite Konvertierungen zu Indexfehlern führen, besteht darin, dass die Vergleichswerte in verschiedene Typen konvertiert werden müssen.
• Vermeiden Sie implizite Typkonvertierungen. Implizite Konvertierungen umfassen hauptsächlich inkonsistente Feldtypen, mehrere Typen im In-Parameter, inkonsistente Zeichensatztypen oder Korrekturleseregeln usw.
  

siehe
https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html
https://xiaomi-info.github.io/2019/12/24/mysql-implicit-conversion/
https://zhuanlan.zhihu.com/p/95170837

Dies ist das Ende dieses Artikels über die Index-Ungültigkeit und ungenauen Daten, die durch die „implizite Konvertierung“ von MySQL 5.6 verursacht werden. Weitere Informationen zur Index-Ungültigkeit, die durch die implizite Konvertierung von MySQL 5.6 verursacht wird, finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!