Mysql 5.6 "implicit conversion" causes index failure and inaccurate data

background

• When performing a SQL query, I tried to remove the single quotes on the vachar type field in the where condition. At this time, I found that this statement, which should have been very fast, was actually very slow. This varchar field has a composite index. The total number of entries is 58989, and even without single quotes the data retrieved is not the data we want.
• The MySQL 5.6 version is used, and the actual situation of the innoDB engine is as follows

Let's take a look at the results of the execution

In the above description, we also need to note that the string in your where condition must be all numbers without single quotes. Otherwise it will report an error

It is also possible that the data found is not the data we want. As shown below

analyze

1. From the execution results, we can see that the corresponding index is used in single quotes. Without single quotes, the index is not used and a full table scan is performed.
2. Why is this happening? Why doesn't MySQL's optimizer perform type conversion directly?

• The introduction of single quotes in SQL statements indicates that the type is a string data type CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. .
• Without single quotes, it means that this is a type other than a string, such as int, bigDecimal, etc.
• If you do not use single quotes around a string containing subtitles and special symbols, the result is that the type conversion fails and SQL cannot be executed.

As shown in the figure above:

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

Let's first look at the execution process of a SQL

(Online picture)

• We first come to the conclusion that if a function operation is performed on the index field (in this case, the cast function performs an implicit conversion), the order of the index value may be destroyed, so the optimizer decides to abandon the tree search function. (https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html)
• [External link image transfer failed, the source site may have an anti-hotlink mechanism, it is recommended to save the image and upload it directly (img-l5AwT0xu-1607244327891)(http://note.youdao.com/yws/res/23689/CE6F785994E6476D816B23787CE65217)]
• That is, be aware that if you convert an indexed column using BINARY, CAST(), or CONVERT(), MySQL might not be able to use the index efficiently.
• The data retrieved is inaccurate because of implicit conversion. After the conversion, the numerical types are different, causing inequality to become equality.
  

Implicit Conversion

1. Conditions for generation <br /> When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Implicit conversion occurs when:

1. When at least one of the two parameters is NULL, the result of the comparison is also NULL. The exception is that using <=> to compare two NULLs will return 1. In both cases, no type conversion is required.
2. Both parameters are strings, and will be compared as strings without type conversion.
3. Both parameters are integers, so they are compared as integers without type conversion.
4. Hexadecimal values ​​are treated as binary strings when compared to non-numeric values.
5. If one parameter is TIMESTAMP or DATETIME and the other parameter is a constant, the constant will be converted to timestamp
6. If one parameter is of type decimal, then if the other parameter is a decimal or integer, the integer will be converted to decimal for comparison. If the other parameter is a floating point number, then the decimal will be converted to a floating point number for comparison.
7. In all other cases, both arguments are converted to floating point numbers before comparison.

2. Analyze the actual situation encountered

1. Then it is clear to us that the example I mentioned above is the comparison between integers and strings, which belongs to other cases. Then let's first analyze the reasons for index failure

• Since it is an implicit conversion, all comparison values ​​must be converted to floating point numbers for comparison.
• We first convert the query condition value into a floating point number, and then convert the record value of the table. Therefore, the index sorting that has been created before is no longer effective. Because the implicit conversion (function) has changed the original value, the optimizer does not use the index here and directly uses a full table scan.
  

2. Query the unmatched values ​​(or partially matched values), as shown in the query results above. You really need to look at the source code, this is the implicit conversion rule of MYsql. I won’t analyze it in detail here (because no relevant documents were found)
Due to historical reasons, compatibility with old designs is required. You can use the MySQL type conversion functions cast and convert to perform explicit conversions.
Summarize

• The use of implicit conversions and functions can cause index failure and inaccurate selected data.
• Implicit conversion conditions and rules
• The specific reason why implicit conversion causes index failure is that the comparison values ​​need to be converted to different types.
• Avoid implicit type conversions. Implicit conversions mainly include inconsistent field types, multiple types in the in parameter, inconsistent character set types or proofreading rules, etc.
  

refer to
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

This is the end of this article about the index invalidation and inaccurate data caused by MySQL 5.6 "implicit conversion". For more information about index invalidation caused by MySQL 5.6 implicit conversion, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!