mysql索引失效

1、条件字段函数操作：

注意：如果对索引字段做了函数操作，可能会破坏索引值的有序性，优化器会放弃走树索引树查询

原因：

select count(*) from tradelog where month(t_modified)=7;

 

 由于B+树提供的快速定位能力，来源于同一层兄弟节点的有序性。当你使用 where t_modified='2018-7-1’的话，引擎就会按照上面绿色箭头的路线，快速定位到 t_modified='2018-7-1’需要的结果。而 当你使用 where month(t_modified)=7，在传入第一层的时候，索引就不知道该怎么走了。

解决方法：

将SQL 语句改成基于字段本身的范围查询：

mysql> select count(*) from tradelog where
-> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
-> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or
-> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

 

2、条件左侧不要有运算，避免索引失效：

比如： select * from a   where id + 1 = 10      ##索引失效

             select * from a   where id = 10 -1   #索引生效

 

3、隐式类型转换：

查询用到的字段   跟  表字段的 字段类型不一致。mysql会自动将字段类型转换，使二者相匹配，在转换过程中，会用到函数 CAST（），导致索引失效

##id为varchar类型  

语句： select * from a   where id  = 10    相当于：   select * from a   where  CAST(id  AS signed  int) = 10 ;  #由于对索引字段进行了函数计算，导致索引失效

 

4、隐式字符编码转换：

两个表的字符集不同，一个是 utf8，一个是 utf8mb4，在做表连接查询的时候用不上关联字段的索引。

优化方法：主动把 l.tradeid 转成 utf8，避免被驱动表上的字符编码转换

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;