前几天同事问了一个问题:在一个只有两个值的字段上加索引,在以这个字段为查询条件的时候是否会走索引。同事说不会,我说会走索引。同事不服,于是我们当场做了一个实验。结果居然没有走索引,满脸尴尬,难道真的是我错了?接下来我们分析下
实验表的情况如下
表结构 :
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的隐式类型转换?
看下官网的描述 隐式类型转换