Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of sql slow query caused by implicit conversion. I hope you will get something after reading this article. Let's discuss it together.

Introduction

Really speechless ah, encountered a mysql implicit conversion question, asked the surrounding dba big this question, they unexpectedly asked me, you do not even know this? I didn't know if I had spent so much time with them, Nima. Sin, sin... .

The problem is like this, a field called task_id, itself is a varchar string type, but because the old system is too long, I thought it was int or bigint, so directly in the code to write sql to run data, the result of waiting for a long time is no response, feel bad ah. See the sql statement in mysql processlist and kill it directly. This field is indexed, and its sql selectivity is high, and the value of the index is also high. But why so slow?

Analyze the problem

The results are analyzed by explain. When using integers to query string fields, you can see below that key is NULL, without index, Rows is a large number, basically the whole table has been scanned. When it is normal to query a string with a string, the index is fine. The value of rows is 1. Here we are talking about scanning the rows of the clustered index, not the index secondary index.

So why is this a problem?

The following is the official statement given by mysql, the last one is very important, when in other cases, the two parameters will be unified as float to compare. Unexpectedly, the new version of mysql has made some adjustments at the optimizer level to avoid this problem, but my own test version is mysql 5.6, and Aliyun uses 5.7, which does not solve the problem. It seems that it is a higher version of the solution, this needs to be verified.

After reading the official commentary, we know that the above slow query sql is actually equivalent to where to_int (taskid) = 516006380. Of course, the conversion is displayed directly with to_int, but the comparison results are consistent. Whether it is implicit conversion or display conversion, it is strange that the speed can rise. Because mysql does not support functional indexes.

# xiaorui.cc

If both arguments in a comparison operation are strings, they are compared as strings.

If both arguments are integers, they are compared as integers.

Hexadecimal values are treated as binary strings if not compared to a number.

If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN ()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST () to explicitly convert the values to the desired data type.

If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

In all other cases, the arguments are compared as floating-point (real) numbers.

Translated into Chinese is:

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, type conversion is not required.

Both parameters are strings and will be compared according to strings without type conversion

Both parameters are integers and are compared according to integers without type conversion

When comparing a hexadecimal value with a non-number, it is treated as a binary string

One parameter is TIMESTAMP or DATETIME, and the other is a constant, which is converted to timestamp

One parameter is of type decimal. If the other parameter is decimal or integer, the integer will be converted to decimal for comparison. If the other parameter is floating point, decimal will be converted to floating point for comparison.

In all other cases, the two parameters are converted to floating-point numbers and compared

After reading this article, I believe you have some understanding of "example Analysis of sql slow query caused by implicit conversion". If you want to know more about it, please follow the industry information channel. Thank you for your reading!

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.