Mysql隐式类型转换

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/markzy/article/details/80323454

前几天同事问了一个问题:在一个只有两个值的字段上加索引,在以这个字段为查询条件的时候是否会走索引。同事说不会,我说会走索引。同事不服,于是我们当场做了一个实验。结果居然没有走索引,满脸尴尬,难道真的是我错了?接下来我们分析下

实验表的情况如下

表结构

Field Type Null Key
id int(11) NO PRI
ssss varchar(20) YES
sex tinyint(1) YES MUL
var varchar(1) YES MUL
aaa varchar(1) YES
ccc varchar(1) YES
eee varchar(1) YES
ddd varchar(1) YES

总数据量200W

数据分布

var的值 数据量
0 9
1 2097143

var字段只有两种值:0和1。0有9条,1有200W+条

索引

索引名 字段 索引类型 索引方法
idx_var var Normal BTREE

查询语句

select * from test_200 where var = 1;

执行计划

select type table type possible keys key key len ref rows filtered extra
SIMPLE test_200 ALL idx_var 2028246 10 Using where

执行计划中显示没有走索引,而是全表扫描,这是为什么呢?

EXPLAIN EXTENDED 看下:

EXPLAIN EXTENDED select * from test_200 where var = 1;
show warnings;
level Code message
Warning 1681 ‘EXTENDED’ is deprecated and will be removed in a future release.
Warning 1739 Cannot use ref access on index ‘idx_var’
due to type or collation conversion on field ‘var’
Warning 1739 Cannot use range access on index ‘idx_var’
due to type or collation conversion on field ‘var’
Note 1003 /* select#1 */ select test.test_200.id AS id,
test.test_200.ssss AS ssss,
test.test_200.sex AS sex,
test.test_200.var AS var,
test.test_200.aaa AS aaa,
test.test_200.ccc AS ccc,
test.test_200.eee AS eee,
test.test_200.ddd AS ddd from test.test_200
where (test.test_200.var = 1)

看到警告中的提示,我开始有点想法了。
提示中说

Cannot use ref access on index ‘idx_var’ due to type or collation conversion on field ‘var’
Cannot use range access on index ‘idx_var’ due to type or collation conversion on field ‘var’

简单来说就是:本次查询不能走 idx_var 这个索引,因为在var 这个字段上发生了类型转换

var 是varchar类型, where条件中使用的是却是数字型 “where var = 1”。所以mysql为了方便与where条件中的1进行比较,把全表中的var值都转换成了数字型,导致了全表扫描。

好了,接下来验证一下我的想法:

查询语句 :

select * from test_200 where var = '1';

执行计划

select type table type possible keys key key len ref rows filtered extra
SIMPLE test_200 ref idx_var idx_var 6 const 1014123 100

从执行计划可以看出,查询已经走索引了。之前不走索引,并不是因为这个字段只有两种值,而是因为where条件的类型不正确,导致mysql进行了 隐式类型转换


  • 隐式类型转换

什么是mysql的隐式类型转换?

看下官网的描述 隐式类型转换

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页