Solution to index failure caused by MySQL implicit type conversion

question

At work, I found that there was an interface that only executed one SQL query statement, and the SQL clearly used the primary key column, but the speed was very slow.
After EXPLAINN in MySQL, it was found that the primary key index was not used during execution, but a full table scan was performed.

Reproduction

The data table DDL is as follows, using user_id as the primary key index:

 CREATE TABLE `user_message` (
   `user_id` varchar(50) NOT NULL COMMENT 'User ID',
   `msg_id` int(11) NOT NULL COMMENT 'Message ID',
   PRIMARY KEY (`user_id`)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Execute the following query statement and find that although the key shows that the primary key index is used, the rows shows that the entire table is scanned and the primary key index does not work:

 EXPLAIN SELECT COUNT(*) FROM user_message WHERE user_id = 1;
 ​
 id|select_type|table |partitions|type |possible_keys|key |key_len|ref|rows |filtered|Extra |
 --+-----------+------------+----------+-----+-------------+-------+-------+---+--------+------------------------+
  1|SIMPLE |user_message| |index|PRIMARY |PRIMARY|206 | |10000| 10.0|Using where; Using index|

After investigation, it was found that the user_id field in the data table is of VARCHAR type, and the user_id in the SQL statement is of INT type. MySQL will convert the type when executing the statement, which should cause the primary key index to become invalid after the type conversion.

Implicit Conversion

The official documentation of MySQL: https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html, introduces the rules of MySQL type implicit conversion:

When the operand types on both sides of an operator are inconsistent, MySQL performs type conversions to make the operands compatible. These conversions occur implicitly. The following describes the implicit conversions for comparison operations:

• If one or both arguments are NULL, the result of the comparison is NULL, except for the <=> equality comparison operator, where NULL <=> NULL evaluates to true without conversion.
• If both arguments in a comparison operation are strings, they are compared as strings.
• If both arguments are integers, they are compared as integers.
• If you don't compare a hexadecimal value to a number, it's treated as a binary string.
• If one of the arguments is a TIMESTAMP or DATETIME column and the other is a constant, the constant is converted to a timestamp before the comparison is performed. This is not done for the arguments to IN(). To be safe, always use full datetime, date, or time strings when doing comparisons. For example, to obtain best results when using BETWEEN with date or time values, use CAST() to explicitly convert those values ​​to the desired data type.
• A single-row subquery on one or more tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers; the integer is not converted to a time value. See the previous item. In this case, use CAST() to convert the resulting integer value of the subquery to DATETIME.
• If one of the arguments is a decimal value, the comparison depends on the other argument. The arguments are compared as decimal values ​​if the other argument is a decimal or integer value; if the other argument is a floating-point value, the arguments are compared as floating-point values.
• In all other cases, the arguments are compared as floating-point numbers (real numbers). For example, string and numeric operands are compared as floating-point numbers.

According to the last rule above, in the previous SQL statement, the comparison between the string and the integer will be converted into two floating-point comparisons. The left side is the string type "1" converted into the floating-point number 1.0, and the right side is the INT type 1 converted into the floating-point number 1.0.

Logically speaking, since both sides are floating point numbers, the index should be able to be used. Why is it not used during execution?

The reason is that the conversion rules for converting strings to floating-point types in MySQL are as follows:

1. All strings that do not start with a number will be converted to 0:

 SELECT CAST('abc' AS UNSIGNED)
 ​
 CAST('abc' AS UNSIGNED) |
 -----------------------+
                       0|

2. When converting a string that starts with a number, it will be intercepted from the first character to the first non-digital content:

 SELECT CAST(' 0123abc' AS UNSIGNED)
 ​
 CAST(' 0123abc' AS UNSIGNED) |
 ----------------------------+
                          123|

Therefore, in MySQL, strings such as "1", "1", "1a", and "01" are all converted to numbers as 1.

When MySQL executes the above SQL statement, it converts the value of the primary key column of each row into a floating point number (the CAST function is executed on the primary key) and then compares it with the condition parameter. Using a function on an index column will cause the index to become invalid, which will ultimately result in a full table scan.

We only need to change the parameters passed in the previous SQL to strings, and then we can use the primary key index:

 EXPLAIN SELECT COUNT(*) FROM user_message WHERE user_id = '1';
 ​
 id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
 --+-----------+------------+----------+----+-------------+-------+-------+-----+----+--------+-----------+
  1|SIMPLE |user_message| |ref |PRIMARY |PRIMARY|202 |const| 135| 100.0|Using index|

Summarize

1. When the condition column is a string, if the condition parameter passed in is an integer, it will be converted into a floating point number first, and then the entire table will be scanned, causing the index to fail;
2. The conditional parameters should be of the same type as the column as much as possible to avoid implicit conversion, or execute the conversion function on the passed parameters to convert them to the same type as the index column.

refer to

1. Brief analysis of MySQL implicit conversion

This is the end of this article about how to solve the problem of index invalidation caused by MySQL implicit type conversion. For more information about MySQL implicit type conversion causing index invalidation, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!