Shulou(Shulou.com)06/01 Report--

This article mainly introduces the solutions to index failure and data inaccuracy caused by Mysql 5.6 "implicit conversion". It has a certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Background

When I tried to remove the single quotation mark query from the vachar type field in the where condition during a SQl query, I found that the statement that was supposed to be fast turned out to be slow. This varchar field has a composite index. The total number of entries is 58989, and the data checked without single quotation marks is not what we want. Using mysql version 5.6, the actual situation of the innoDB engine is as follows

Let's take a look at the results of the implementation.

We should also note in the above description that the string of your where condition must be a full number without single quotation marks. Otherwise, you will make a mistake.

It is also possible that the data found out is not the data we want. The figure below is as follows

Analysis.

From the execution result, the corresponding index is walked away by using single quotation marks. Those without single quotation marks did not walk the index and carried out a full table scan. What causes it? Why doesn't mysql's optimizer do the type conversion directly? The introduction of single quotation marks in SQL statements means that the types are string data types CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. Not adding single quotation marks also means that this is a type other than a string, such as the int,bigDecimal type. If you do not add single quotation marks to a string with subtitles and special symbols, the result is that the type conversion fails and the SQl cannot be executed.

As shown in the above figure:

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

Let's first take a look at the implementation process of a SQL.

(net diagram)

We first come to the conclusion that if you do a function operation on the index field (in this case, the cast function is implicitly converted), it may break the order of the index value, so the optimizer decides to give up the walking tree search function. (https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html) [external link image transfer failed, and the origin server may have a hotlink protection mechanism. It is recommended to save the image and upload it directly (img-l5AwT0xu-1607244327891) (http://note.youdao.com/yws/res/23689/CE6F785994E6476D816B23787CE65217)] means: please note that if you use BINARY,CAST () or CONVERT () to convert index columns, MySQL may not be able to use the index effectively. The inaccuracy of the checked data is also due to implicit conversion, which leads to different numerical types and inequality.

Implicit conversion

1. Production condition

When operators are used with different types of operands, type conversions occur to make operands compatible. A conversion implicit occurs

Conditions under which implicit conversion occurs:

When at least one of the two parameters is NULL, the result of comparison is also NULL. The exception is that 1 is returned when comparing two NULL. In both cases, there is no need for type conversion. Both parameters are strings and will be compared by string. Without type conversion, both parameters are integers. When comparing hexadecimal values without type conversion with non-digits, compare them. It will be treated as a binary string where one parameter is TIMESTAMP or DATETIME, and the other parameter is constant, and the constant will be converted to timestamp. If the other parameter is decimal or integer, the integer will be converted to decimal and compared. If the other parameter is a floating point number, the decimal will be converted to a floating point number for comparison. In all other cases, the two parameters will be converted to floating point numbers and then compared.

two。 Analyze the actual situation encountered

1. Then we are clear, the example I mentioned above is the comparison of integers and strings, which belongs to other cases. Let's first analyze the causes of index failure.

Because of other cases of implicit conversion, the ratios have to be converted to floating-point numbers. We first convert the query condition values to floating-point numbers, and then convert the record values of the table. So the previously created index sorting can no longer take effect. Because the implicit conversion (function) has changed the original value, so here the optimizer directly does not select the index, directly uses the full table scan.

two。 Query out mismatched values (or partially matched values), such as the query results above. It really depends on the source code, which is the implicit conversion rule of MYsql. I will analyze it in detail here (because the relevant documents have not been found)

Due to historical reasons, you need to be compatible with older designs, and you can use MySQL's type conversion functions cast and convert to explicitly convert.

Summary

Implicit conversion and the use of functions will lead to index failure and the conditions of inaccurate implicit conversion of select data, as well as the specific reasons for index failure caused by rule implicit conversion. To avoid implicit type conversion, the main types of implicit conversion are inconsistent field types, multiple types of in parameters, character set types or inconsistent proofreading rules, etc. Thank you for reading this article carefully. I hope the editor will share the solutions to index failure and data inaccuracy caused by Mysql 5.6 "implicit conversion". At the same time, I also hope that you will support and pay attention to the industry information channel. If you encounter a problem, find a detailed solution, waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.