条件字段有索引，为什么查询也这么慢？

在MySQL中，如果需要查找某一行的值，可以先通过索引找到对应的值，然后根据索引匹配的记录找到需要查询的数据行。然而，有些时候，即使查询条件有索引，也会查询很慢，那是因为查询条件的错误使用导致没有正确利用到索引，即有索引，但是查询时没走索引。那么都有哪些条件字段有索引但是不走索引的场景呢？如下：

• 计算操作；
• 隐式转换；
• 模糊查询；
• 范围查询；

1. 计算操作

1.1 验证计算操作是否能走索引

比如：查询测试表t1某一天的所有数据，SQL如下：

select * from t1 where date(c) ='2019-05-21';

使用explain来分析这条SQL语句，如下：

explain select * from t1 where date(c) ='2019-05-21';

分析结果如下：

可以看出，type为ALL，key字段结果为Null，rows为10302，因此知道该SQL语句是没走索引的全表扫描。

1.2 计算操作不能走索引的原因

• 对条件字段做计算操作走不了索引，因为索引树中存储的是列的实际值和主键值，如果用’2019-05-21’去匹配，将无法定位到实际值，因为实际值是’2019-05-21 00:00:00’，因此走不了索引，只能进行全表扫描。

1.3 如果优化SQL使得查询走索引？

• 因为计算操作使得条件查询无法走索引，所以SQL优化的目标是避免使用计算操作，针对如下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';

• 执行计划如下，可以看出，type是range，rows只有1行，key是索引idx_c，说明走了索引：
• 

2. 隐式转换

2.1 什么是隐式转换？

• 定义：当操作符与不同类型的操作对象一起使用时，就会发生类型转换以使操作兼容。
• 比如电话号码字段，因为会有连接符等特殊字符，因此设计字段类型的时候通常设置为varchar类型，并且会加上索引，而在查询数据的时候，我们通常没有注意到电话号码字段是varchar字段，以为是个int类型字段，因此会写下错误的SQL：

select tele_phone from user_info where tele_phone =11111111111;

查询的时候，mysql会将tele_phone条件对应的值隐式转化为varchar类型，这样即便我们的条件值是int类型的，实际查询的时候也是按照varchar类型作为条件。

2.2 验证隐式转换是否走索引？

比如：查询测试表t1字段a为1000的记录，其中a字段是varchar类型，SQL执行计划如下：

explain select * from t1 where a = 1000;

分析结果如下，type为ALL，rows为10302所有行，key为null，说明没走索引：

2.3 隐式转换不走索引的原因？

t1表中a字段是varchar类型，而查询字段没有加单引号，导致MySQL会先将a字段转换成int类型再去查询，相当于执行SQL语句如下：

select * from t1 where cast(a as signed int) =1000;

因此，隐式转换相当于一次隐式的计算操作，上面说到计算操作不走索引，因此隐式转换也不走索引。

2.4 隐式转换的SQL优化

·

• 只需要将查询的字段类型和定义的字段类型对应即可，比如上述查询SQL更改为：

explain select * from t1 where a = '1000';

分析结果如下，type为ref，key为idx_a，rows为1，表示走了索引：

3. 模糊查询

3.1 验证模糊查询是否走索引

比如：查询t1表中包含1000的记录，SQL执行计划如下：

explain select * from t1 where a like '%1000%';

分析结果如下，发现是全表扫描，不走索引：

3.2 模糊查询索引优化建议

当like的通配符条件为 '1000%' 时的SQL执行计划如下：

explain select * from t1 where a like '1000%';

分析结果如下，这种条件下是走索引的，但是查询的是以1000开头的数据：

虽然查询条件前面不写通配符的操作可以走索引，但是得结合实际使用，避免查询到脏数据。如果只知道中间的值，需要模糊查询，那么可以使用ES等专门的查询引擎。

4. 范围查询

4.1 不能使用索引的范围查询

通常情况下，范围查询是可以走索引的，但是在一定情况下，即便是条件字段有索引，但是却还是走全表扫描了，比如：查询出t1表中b字段1到2000范围的数据，其中b的类型是int，SQ执行计划如下：

explain select * from t1 where b>=1 and b <=2000;

分析结果如下，可以看出没走索引：

之所以没走索引，是因为MySQL优化器根据检索比例、表大小进行评估是否使用索引，如果单次查询量过大，优化器将不走索引。

4.1 范围查询的优化建议

• 降低单次查询范围，分为多次查询

5. 总结

本文讲解了4种不能走索引的查询场景，针对这些场景应该注意一下几点：

• 计算操作，不要对条件字段进行运算；
• 隐式转换，隐式转换也相当于一次计算操作，所以查询前确定好字段类型；
• 模糊查询，根据业务情况尽量不在条件前面写通配符%；
• 范围查询，查询范围不宜过大，通过explain分析查询范围的大小；