MySQL 不能使用索引的情况总结注意隐式转换的坑

老骥 · 简书 · · 2021-04-29 09:06

1、索引字段上加函数
select * from table where year(datefield) ='2020'
mysql 8.0版本已支持函数索引

2、索引字段上有表达式
select * from table where field + 2= N

3、like 使用了左匹配通配符
select * from table where field like '%aa'

4、联合查询没有遵守左前缀匹配原则
alter table t add index idx_id_name(id,name)
select * from t where name = 'aaa'

5、or 条件一边字段有索引而另一边字段没有索引

上面的5种很好理解，也不容易犯错。下面讲一下隐式转换带来的问题

6、索引字段执行了隐式转换
有些隐式转换是能使用索引的，有些是不能使用的下面一一来看一下
看测试数据

-- 构造测试数据1
CREATE TABLE `testconvert` (
`id` int NOT NULL ,
`numfield` int NOT NULL ,
`strfield` varchar(100) NOT NULL ,
`datefield` datetime NOT NULL ,
PRIMARY KEY (`id`)
)
;


insert into testconvert(id,numfield,strfield,datefield)
values(1,100,'100',cast('2020-10-01' as datetime )),
(2,200,'200',cast('2020-10-02' as datetime)),
(3,300,'300',cast('2020-10-03' as datetime ))

建立三个索引

alter table testconvert add index idx_num(numfield);
alter table testconvert add index idx_str(strfield);
alter table testconvert add index idx_date(datefield);