35 字段类型不匹配 影响 使用索引?

字段类型不匹配 影响 使用索引?"/>

35 字段类型不匹配 影响 使用索引?

前言

这是一个经常能够看到的问题, 又或者 经常在面试中碰到 

如果 索引字段类型 不匹配, 然后 不会使用索引

这里 我们来看一下 具体的情况 

 

测试表结构如下 

CREATE TABLE `tz_test` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`field1` varchar(128) DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,KEY `field1` (`field1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10000000 DEFAULT CHARSET=utf8

 

数据信息如下, 接近 1000_0000 条数据 

 

 

根据 id 查询 

explain select * from tz_test where id = 9999999;

explain select * from tz_test where id = '9999999';

可以看到的是 主键 这边 均是使用的索引

 

执行 “explain select * from tz_test where id = 9999999;” 的时候 

然后 判断使不使用索引的地方在这里, 这类 SYSTEM/CONST 的增加索引查询的处理在这里 

id 字段为 INT, 传入类型为 INT, 然后 这里判断为兼容的索引查询

然后可以使用 id索引 

 

然后 下面将 key 传递到 join_tab->keyuse

 

然后 下面根据上下文判断 是否可以使用索引, 这里是可以使用 索引

 

 

执行 “explain select * from tz_test where id = '9999999';” 的时候 

然后 判断使不使用索引的地方在这里, 这类 SYSTEM/CONST 的增加索引查询的处理在这里 

id 字段为 INT, 传入类型为 STRING, 然后 这里判断为兼容的索引查询

然后可以使用 id索引 

 

 

根据 field1 索引字段查询 

然后 我们看一下 field1 字段 

explain select * from tz_test where field1 = 9999999;

explain select * from tz_test where field1 = '9999999';

可以看到 field1 类型不同的时候使用的是 index/all, 类型能够匹配上时 使用的是索引

 

然后 判断不使用索引的地方在这里

字段 field1 类型为 STRING 类型, 比较的右值为 INTEGER 类型, 类型匹配不上, 识别为不能使用 索引比较, 然后 后面 构造的索引树为 NULL, 然后 没有使用索引

这里的类型相关判断 和 上面 SYSTEM/CONST 部分的判断标准一致 

 

如果是使用索引的查询语句的情况, 这里判断 可以使用索引

 

然后 下游进入 get_key_scans_params

 

然后 下面比较 使用索引, 和 默认的查询方式的一个比较

这里 显然使用索引开销 更小, 然后 选择使用索引

 

如果是 临时字段 或者 临时右值 不使用索引

如果字段是 字符串类, 右值不是字符串类, 不使用索引 

右值类型为 类型为 JSON 不使用索引 

 

 

tz_test_02 的 根据 id 查询 

为了 更加 深刻的验证 是否使用索引的条件, 我们把 id 和 field1 字段类型调整一下 

id 调整为 varchar, field1 调整为 int 类型 

然后 再来观察一下 情况

CREATE TABLE `tz_test_02` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`field1` int(11) DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,KEY `field1` (`field1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10000000 DEFAULT CHARSET=utf8

 

然后 数据来自于 tz_test

insert into tz_test_02 select * from tz_test;

 

这里 我们仅仅展示一个 explain 的结果, 至于详细情况 请结合上面分析

explain select * from tz_test_02 where id = 9999999;
explain select * from tz_test_02 where id = '9999999';

 

 

可以看到 前者使用了 field1 所在的索引, 全部索引遍历查询了 

这也是基于 field1 所在的索引覆盖了查询条件, 如果增加一个字段, 应该就是 全表扫描了

 

后者 “类型兼容”, 因此 直接使用的 主键索引

 

 

tz_test_02 的 根据 field1 查询 

执行 explain 如下 

explain select * from tz_test_02 where field1 = 9999999;
explain select * from tz_test_02 where field1 = '9999999';

 

前者走的是 field1 所在的索引 

 

后者 也是使用的 field1 所在的索引 

 

 

完