Lösen Sie das Problem von Indexfehlern und ungenauen Daten, die durch die „implizite Konvertierung“ von MySQL 5.6 verursacht werden.

MySQL-Video-TutorialSpalte stellt die Lösung des Problems von Indexfehlern und Datenungenauigkeiten in MySQL 5.6 vor

Eine SQL-Abfrage Als ich versuchte, das Vachar-Typfeld in der Where-Bedingung abzufragen, indem ich die einfachen Anführungszeichen entfernte, stellte ich fest, dass diese Anweisung, die schnell sein sollte, tatsächlich sehr langsam war. Dieses Varchar-Feld verfügt über einen zusammengesetzten Index. Die Gesamtzahl der Einträge beträgt 58989, und die gefundenen Daten sind auch ohne einfache Anführungszeichen nicht die von uns gewünschten Daten.

Wir verwenden MySQL Version 5.6. Die tatsächliche Situation der innoDB-Engine ist wie folgt: Werfen wir einen Blick auf die Ausführungsergebnisse. In der obigen Beschreibung müssen wir auch auf die Zeichenfolge achten Ihrer Where-Bedingung Es muss eine vollständige Zahl ohne einfache Anführungszeichen sein. Andernfalls wird ein Fehler gemeldet

und die gefundenen Daten sind möglicherweise nicht die von uns gewünschten Daten. Wie in der Abbildung unten gezeigt

• Analyse
• Aus den Ausführungsergebnissen wird der entsprechende Index in einfache Anführungszeichen gesetzt. Wenn keine einfachen Anführungszeichen verwendet werden, findet keine Indizierung statt und es wird ein vollständiger Tabellenscan durchgeführt.

Warum ist das so? Warum führt der MySQL-Optimierer die Typkonvertierung nicht direkt durch?

Die Einführung von einfachen Anführungszeichen in SQL-Anweisungen bedeutet, dass dieser Typ der Zeichenfolgendatentyp CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM und SET ist. .

Wenn Sie keine einfachen Anführungszeichen hinzufügen, bedeutet dies, dass es sich um einen anderen Typ als eine Zeichenfolge handelt, z. B. int, bigDecimal usw.

Wenn Sie einer Zeichenfolge mit Untertiteln und Sonderzeichen keine einfachen Anführungszeichen hinzufügen, führt dies zu einer Typkonvertierung Fehler. SQL kann nicht ausgeführt werden.

Wie im Bild oben gezeigt:

1054 - Unknown column '000w1993521' in 'where clause', Time: 0.008000s

Werfen wir zunächst einen Blick auf den Ausführungsprozess eines SQL

(Netzwerkdiagramm)
2. Wir ziehen zunächst die Schlussfolgerung: Wenn wir Funktionsoperationen ausführen Das Indexfeld (in diesem Beispiel ist es die Umwandlungsfunktion, die die implizite Konvertierung durchführt), was die Ordnung der Indexwerte zerstören kann. Daher hat der Optimierer beschlossen, die Baumsuchfunktion aufzugeben. (https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html)

[Die Übertragung des externen Linkbildes ist möglicherweise über einen Anti-Leeching-Mechanismus verfügen Das Bild speichern und direkt hochladen. oder CONVERT() zum Konvertieren der Indexspalte verwenden, kann MySQL den Index möglicherweise nicht effizient nutzen.

• Die erkannten Daten sind aufgrund der impliziten Konvertierung ungenau. Nach der Konvertierung sind die numerischen Typen unterschiedlich, was dazu führt, dass aus Ungleichheit Gleichheit wird.
• Implizite Konvertierung

1 Bedingungen generieren

Wenn ein Operator mit Operanden unterschiedlichen Typs verwendet wird, erfolgt eine Typkonvertierung, um die Operanden kompatibel zu machen. Dann erfolgt eine implizite Konvertierung:

Wenn mindestens einer der beiden Parameter NULL ist, ist das Vergleichsergebnis auch NULL. Die Ausnahme besteht darin, dass zwei NULL-Werte verglichen werden Rückgabe 1. In beiden Fällen ist keine Typkonvertierung erforderlich

Beide Parameter sind Zeichenfolgen und werden als Zeichenfolgen ohne Typkonvertierung verglichen.
• Beide Parameter sind Ganzzahlen und werden als Ganzzahlen ohne Typkonvertierung verglichen und wenn man sie mit nicht numerischen Werten vergleicht, werden sie als binäre Zeichenfolgen behandelt. Ein Parameter ist TIMESTAMP oder DATETIME, und der andere Parameter ist eine Konstante, und die Konstante wird in einen Zeitstempel umgewandelt. Ein Parameter ist vom Typ If Der andere Parameter ist eine Dezimalzahl oder eine Ganzzahl. Die Ganzzahl wird zum Vergleich in eine Dezimalzahl umgewandelt. Wenn der andere Parameter eine Gleitkommazahl ist, wird die Dezimalzahl zum Vergleich in eine Gleitkommazahl umgewandelt Parameter werden in Gleitkommazahlen konvertiert und dann verglichen
• 2 Analysieren Sie die tatsächliche Situation
• 
  
1 Das oben vorgeschlagene Beispiel ist der Vergleich von Ganzzahlen und Zeichenfolgen Situation. Dann analysieren wir zunächst die Gründe für das Indexversagen

• Aufgrund anderer Fälle der impliziten Konvertierung müssen die Vergleichswerte zum Vergleich in Gleitkommazahlen konvertiert werden.
• Wir konvertieren zuerst die Abfragebedingungswerte in Gleitkommazahlen und dann die Datensatzwerte Die Tabellen müssen ebenfalls konvertiert werden, sodass die zuvor erstellte Indexsortierung zu diesem Zeitpunkt nicht mehr wirksam werden kann. Da die implizite Konvertierung (Funktion) den ursprünglichen Wert geändert hat, verwendet der Optimierer hier nicht den Index und verwendet direkt den vollständigen Tabellenscan.
  

2. Fragen Sie nicht übereinstimmende Werte (oder teilweise übereinstimmende Werte) ab, wie die obigen Abfrageergebnisse. 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 muss es mit dem alten Design kompatibel sein. Sie können die Typkonvertierungsfunktionen „cast“ und „convert“ von MySQL verwenden, um explizit zu konvertieren.
Zusammenfassung

• Die Verwendung impliziter Konvertierungen und Funktionen führt zu Indexfehlern und ungenauen ausgewählten Daten
• Die Bedingungen und Regeln für implizite Konvertierungen
• Die spezifischen Gründe, warum implizite Konvertierungen aufgrund der Notwendigkeit eines Vergleichs zu Indexfehlern führen Werte müssen typkonvertiert werden, um einen Fehler zu verursachen.
• Vermeiden Sie implizite Typkonvertierungen. Zu den Arten der impliziten Konvertierung gehören hauptsächlich inkonsistente Feldtypen, Parameter mit mehreren Typen, inkonsistente Zeichensatztypen oder Korrekturleseregeln usw.

Wenn Sie mehr über die Programmierung erfahren möchten, bleiben Sie bitte auf dem Laufenden PHP-Schulung Kolumne!

Das obige ist der detaillierte Inhalt vonLösen Sie das Problem von Indexfehlern und ungenauen Daten, die durch die „implizite Konvertierung“ von MySQL 5.6 verursacht werden.. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!