索引失效的场景

查询条件做函数计算

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

查询条件做函数计算，在查索引的时候，利用不了索引。因为索引利用的是树的有序性，但是函数计算后的结果在索引的B+树上并不连续。MySQL在查询的时候利用不到树的有序性。

但并不是完全不利用索引，MySQL在选择索引的时候，判断该字段的索引要比主键索引更小，所以还是会查询该字段 t_modified 对应索引，但是会查询整棵树。

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

隐式类型转换

假如 tradeid 字段类型是 varchar ，查询语句

explain   select * from tradelog where tradeid=3;

查询结果走了全表查询。

原因是因为 varchar 类型 和 int 类型做对比的时候，会将 varchar 做类型转换为 int，导致索引失效。类型转换的过程就是在 tradeid 字段做了函数计算。

---

反过来 id 为 int 类型，查询条件是 varchar 时，会走索引。因为将 varchar 转为 int，并没有在索引字段做函数计算。

explain   select * from tradelog where id="3";

字符集不同导致索引失效

在MySQL中 utf8mb4 是 utf8 的超集。

如果两个表A、B分别为 utf8mb4、utf8 时，匹配时 MySQL内部会将 utf8 转换为 utf8mb4。转换过程也是一个函数计算，可能导致索引失效。

• 第一种场景

  select a.*,b.* from A a,B b where a.id = 1 and a.name = b.name

  此时根据 查到 a 的 name 之后，查 b 表变为

  select * from b where b.name = "123"(utf8mb4)
  等价于
  select * from b where CONVERT(name USING utf8mb4) = "123"(utf8mb4)

  在查询条件字段上做函数计算，导致索引不生效。

• 第二种场景

  select a.*,b.* from A a,B b where b.id = 1 and a.name = b.name

  此时根据 查到 b 的 name 之后，查 a表变为

  select * from a where a.name = "123"(utf8)
  等价于
  select * from b where a(utf8mb4) = CONVERT("123" USING utf8mb4)

  在查询条件字段上没有做函数计算，索引继续生效。

优化方法

1. 保证两张表的字符集问题，优先使用 utf8mb4。

2. 显式转换查询条件的字符集。

   select * from b where CONVERT(name USING utf8mb4) = "123"(utf8mb4)
   转为
   select * from b where name(utf8) = CONVERT("123" USING utf8)