MySQL隐式转换造成索引失效

CREATE TABLE `test1` (
    `id` int(11) NOT NULL,
    `num1` int(11) NOT NULL DEFAULT '0',
    `num2` varchar(11) NOT NULL DEFAULT '',
    `type1` int(4) NOT NULL DEFAULT '0',
    `type2` int(4) NOT NULL DEFAULT '0',
    `str1` varchar(100) NOT NULL DEFAULT '',
    `str2` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `num1` (`num1`),
    KEY `num2` (`num2`),
    KEY `type1` (`type1`),
    KEY `str1` (`str1`),
    KEY `str2` (`str2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. SQL 测试

先来看这组 SQL，一共四条，我们的测试数据表num1是int类型，num2是varchar类型，但是存储的数据都是跟主键id一样的顺序数字，两个字段都建立有索引。

SELECT * FROM `test1` WHERE num1 = 10000;
SELECT * FROM `test1` WHERE num1 = '10000';
SELECT * FROM `test1` WHERE num2 = 10000;  # 本身是 varchar
SELECT * FROM `test1` WHERE num2 = '10000';

经测试这四条 SQL 最后的执行结果却相差很大，其中 124 三条 SQL 基本都是瞬间出结果，大概在 0.001~0.005 秒，在千万级的数据量下这样的结果可以判定这三条 SQL 性能基本没差别了。但是第三条 SQL，多次测试耗时基本在 4.5~4.8 秒之间。

为什么 34 两条 SQL 效率相差那么大，但是同样做对比的 12 两条 SQL 却没什么差别呢？查看一下执行计划，下边分别 1234 条 SQL 的执行计划数据：

2. 官方文档

当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。某些转换是隐式发生的。例如，MySQL 会根据需要自动将字符串转换为数字，反之亦然。以下规则描述了比较操作的转换方式：

两个参数至少有一个是NULL时，比较的结果也是NULL，特殊的情况是使用<=>对两个NULL做比较时会返回1，这两种情况都不需要做类型转换
两个参数都是字符串，会按照字符串来比较，不做类型转换
两个参数都是整数，按照整数来比较，不做类型转换
十六进制的值和非数字做比较时，会被当做二进制串
有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp
有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较
所有其他情况下，两个参数都会被转换为浮点数再进行比较

3. 分析

根据官方文档的描述，我们的第 23 两条 SQL 都发生了隐式转换

第 2 条 SQL 的查询条件num1 = '10000'，左边是int类型右边是字符串
第 3 条 SQL 相反
那么根据官方转换规则第 7 条，左右两边都会转换为浮点数再进行比较

先看第 2 条 SQL：SELECT * FROM test1 WHERE num1 = '10000'; 左边为 int 类型10000，转换为浮点数还是10000，右边字符串类型'10000'，转换为浮点数也是10000。两边的转换结果都是唯一确定的，所以不影响使用索引。

第 3 条 SQL：SELECT * FROM test1 WHERE num2 = 10000; 左边是字符串类型'10000'，转浮点数为 10000 是唯一的，右边int类型10000转换结果也是唯一的。但是，因为左边是检索条件，'10000'转到10000虽然是唯一，但是其他字符串也可以转换为10000，比如'10000a'，'010000'，'10000'等等都能转为浮点数10000，这样的情况下，是不能用到索引的。

关于这个隐式转换我们可以通过查询测试验证一下，先插入几条数据，其中num2='10000a'、'010000'和'10000'：

INSERT INTO `test1` (`id`, `num1`, `num2`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000001', '10000', '10000a', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');
INSERT INTO `test1` (`id`, `num1`, `num2`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000002', '10000', '010000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');
INSERT INTO `test1` (`id`, `num1`, `num2`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000003', '10000', ' 10000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');

然后使用第三条 SQL 语句SELECT * FROM test1 WHERE num2 = 10000;进行查询：

从结果可以看到，后面插入的三条数据也都匹配上了。那么这个字符串隐式转换的规则是什么呢？为什么num2='10000a'、'010000'和'10000'这三种情形都能匹配上呢？查阅相关资料发现规则如下：