Solve the problem of index failure and inaccurate data caused by Mysql 5.6 'implicit conversion'

mysql video tutorial Column introduction to solve the problem of Mysql 5.6 index failure and inaccurate data

Related free learning recommendations: mysql video tutorial

Background

• When performing an SQl query, I tried to set the where condition When querying the vachar type field by removing the single quotes, I found that this statement that should be very fast turned out to be very slow. This varchar field has a composite index. The total number of entries is 58989, and the data found even without single quotes is not the data we want.
• The mysql 5.6 version is used. The actual situation of the innoDB engine is as follows

Let’s take a look at the execution results

In the above description, we must also note that the string of your where condition must be all numbers without single quotes. Otherwise, an error will be reported

# and the data found may not be the data we want. As shown in the figure below

Analysis

1. From the execution results, the corresponding index is removed when single quotes are used. If single quotes are not used, there will be no indexing and a full table scan will be performed.
2. Why is this so? Why doesn't mysql's optimizer directly perform type conversion?

• The introduction of single quotes in SQL statements means that this type is a string data type CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. .
• Not adding single quotes means that this is a type other than string, such as int, bigDecimal type, etc.
• If you give a string with subtitles and special symbols without adding single quotes, Quotation marks, the consequence is that type conversion fails and SQl cannot be executed.

As shown in the picture above:

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

Let’s first take a look at the execution process of a SQL

（ Network diagram)

• Let us first draw the conclusion: if you perform function operations on the index field (in this case, the cast function performs implicit conversion), it may destroy the orderliness of the index value, so The optimizer decided to give up the tree search function. (https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html)
• [The external link image transfer failed. The source site may have an anti-leeching mechanism. It is recommended to transfer the image to Save and upload directly (img-l5AwT0xu-1607244327891)(http://note.youdao.com/yws/res/23689/CE6F785994E6476D816B23787CE65217)]
• meaning: Please note that if you use BINARY, CAST () or CONVERT() to convert index columns, MySQL may not be able to use the index efficiently.
• The data found is inaccurate because of implicit conversion. After conversion, the numerical types are different, causing inequality to become equality.
  

Implicit conversion

1. Generates conditions
When the operator is of a different type When operands are used together, type conversion occurs to make the operands compatible. Implicit conversion will occur
Conditions for implicit conversion:

1. When at least one of the two parameters is NULL, the comparison result is also NULL, the exception is the use of When two NULLs are compared, 1 will be returned. In both cases, no type conversion is required.
2. The two parameters are both strings and will be compared as strings without type conversion
3. Both parameters are integers. They are compared as integers without type conversion.
4. When hexadecimal values ​​are compared with non-digits, they will be treated as binary strings.
5. There is one parameter Is TIMESTAMP or DATETIME, and the other parameter is a constant, the constant will be converted to timestamp
6. One parameter is of decimal type, if the other parameter is decimal or an integer, the integer will be converted to decimal and then compared. If the other parameter is a floating point number, the decimal will be converted to a floating point number for comparison
7. In all other cases, both parameters will be converted to a floating point number for comparison

2. Analyze the actual situation encountered

1. Then we will understand that the example I proposed above is a comparison of integers and strings, which belongs to other situations. Then let’s first analyze the reasons for index failure

• Due to other cases of implicit conversion, the comparison values ​​must be converted into floating point numbers for comparison
• We first convert the query condition value into a floating point number, and then convert the table records The values ​​must also be converted, so the index sorting that has been created before is no longer effective at this time. Because the implicit conversion (function) has changed the original value, the optimizer does not use the index here and directly uses the full table scan.
  

2. Query out unmatched values ​​(or partially matched values), such as the query results above. You really need to look at the source code. This is the implicit conversion rule of MYsql. I will not analyze it in detail here (because no relevant documents have been found)
Due to historical reasons, it needs to be compatible with the old design. You can use MySQL's type conversion functions cast and convert to explicitly convert.
Summary

• The use of implicit conversion and functions will lead to index failure and inaccurate selected data
• The conditions for implicit conversion and Rule
• The specific reason why implicit conversion causes index failure is because the comparison value needs to be type converted, causing the failure.
• Avoid implicit type conversion. The types of implicit conversion mainly include inconsistent field types, in parameters containing multiple types, inconsistent character set types or collation rules, etc.

If you want to know more about programming learning, please pay attention to the php training column!

The above is the detailed content of Solve the problem of index failure and inaccurate data caused by Mysql 5.6 'implicit conversion'. For more information, please follow other related articles on the PHP Chinese website!