mysql 条件字段有索引,但是不走索引的几个场景

1. 对SQL 进行函数操作,导致不走索引

# 不走索引
select * from t1 where date(c) ='2019-05-21';
# 走索引
select * from t1 where c>='2019-05-21 00:00:00' and c<='2019-05-21 23:59:59';

补充: c 字段存储值为 (datetime)  2019-05-22 00:00:00

2. 条件隐式转换导致不走索引

# 不走索引
select * from t1 where a=1000;
# 走索引
select * from t1 where a='1000';

补充: a 字段存储类型为 (varchar)  100

3. 模糊查询 like,不走索引
   具体原因参照上篇文章 Mysql 索引

不走索引
select * from t1 where a like '%1111%';
走索引
select * from t1 where a like '1111%';

补充: like 有个最左匹配原则

4. 范围查询太大,导致不走索引

不走索引
select * from t1 where b>=1 and b <=2000;
拆分之后走索引 
select * from t1 where b>=1 and b <=1000;
select * from t1 where b>=1001 and b <=2000;

5. 计算操作导致不走索引

不走索引
select * from t1 where b-1 =1000;
走索引
select * from t1 where b =1000 + 1;

总结

• 应该避免隐式转换
• like查询不能以%开头
• 范围查询时，包含的数据比例不能太大
• 不建议对条件字段做运算及函数操作

申明:本文只是学习总结,具体详细手册,锁定慕课网哦! 慕课网专栏链接