深入分析隐式转换导致MySQL索引失效

前言

关于MySql索引失效的几种原因分析，之前在这篇文章中已经有过整理，也演示了隐式转换造成索引失效的例子，本文主要来分析一下，文章中提到的：如果是数值类型，则右边无论是带引号还是不带引号都可以走索引。

案例

先看一下之前的案例

CREATE TABLE t_user_demo (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
name VARCHAR(20) DEFAULT NULL COMMENT '姓名',
phone char(11) DEFAULT NULL COMMENT '手机号',
age TINYINT DEFAULT NULL COMMENT '年龄',
sex CHAR(1) DEFAULT NULL COMMENT '性别',
PRIMARY KEY (id),
UNIQUE KEY idx_name_phone (name, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

为什么对于数值类型，等于右边无论是带引号还是不带引号都可以走索引呢？

隐式转换规则

要弄清楚这一点，我们必须要先知道MySQL的隐式转换规则。

我们可以执行如下SQL， SELECT 1+‘1’; 得到的结果是2，也就是说MySQL把字符’1’转换成了数值1进行了计算。

下面也摘录了一段MySQL官方给出的隐式转换规则：

• 如果一个或两个参数为NULL，则比较结果为NULL，但NULL-safe <=> 相等比较运算符除外。对于NULL <=> NULL，结果为真。不需要转换。
• 如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较。
• 如果两个参数都是整数，则将它们作为整数进行比较。
• 如果不与数字进行比较，十六进制值将被视为二进制字符串。
• 如果其中一个参数是 a TIMESTAMP or DATETIME列而另一个参数是常量，则在执行比较之前，该常量将转换为时间戳。这样做是为了对 ODBC 更友好。这不是为 的参数完成的 IN()。为安全起见，在进行比较时始终使用完整的日期时间、日期或时间字符串。例如，要在使用BETWEEN日期或时间值时获得最佳结果 ，请使用CAST()将值显式转换为所需的数据类型。
  来自一个或多个表的单行子查询不被视为常量。例如，如果子查询返回要与DATETIME 值进行比较的整数，则比较将作为两个整数进行。整数不会转换为时间值。要将操作数作为DATETIME值进行比较 ，请使用 CAST()将子查询值显式转换为DATETIME。
• 如果其中一个参数是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较，如果另一个参数是浮点值，则作为浮点值进行比较。
• 在所有其他情况下，参数将作为浮点（实数）数进行比较。例如，字符串和数字操作数的比较是作为浮点数的比较进行的。

上述演示的案例，就在于最后一条，字符串会被作为数值进行比较。

所以文中一开始提到的：select * from t_user_demo where name = ‘zz’ and phone = 13933333333;

实际上会被解析成：select * from t_user_demo where name = ‘zz’ and CAST(phone AS UNSIGNED) = 13933333333;
由于索引列发上发生了函数操作，因此索引失效了。

解答文章一开始的问题

为什么对于数值类型，等于右边无论是带引号还是不带引号都可以走索引呢？

因为phone是数值类型，执行这样的SQL：select * from t_user_demo where name = ‘zz’ and phone = ‘13933333333’;

根据规则，字符串会被作为数值进行比较，所以，实际上会被解析成：select * from t_user_demo where name = ‘zz’ and phone = CAST(‘13933333333’ AS UNSIGNED) ;

索引列上并没有发生函数操作，因此还是可以正常走索引的。