MyISAM 和 InnoDB 之间的数据组织结构

MyISAM 和 InnoDB 都是使用 B+ 树组织数据的，MyISAM 在叶子结点中存储的是数据的地址，InnoDB 在叶子结点存储的是真实的数据记录。具体的数据存储如下图所示：

回表的理解

在 MySQL 使用查询语句的时候，如果 where 后面的字段不是主键索引，走了二级索引的时候，因为在二级索引的叶子结点中存储的不是真实行记录，而是主键 id , 需要使用获取到的主键 id 再次的去表中走主键索引获取到行记录，这样经过了两次查询获取到最终的数据的操作就是回表。

索引失效的几种情况

对索引使用左或者左右模糊匹配

EXPLAIN SELECT * FROM salaries WHERE id LIKE '%12';

EXPLAIN SELECT * FROM salaries WHERE id LIKE '%22%';

如上所示， id 本身是主键索引，但是在使用 like 的左匹配的时候，主键索引失效了，导致了查询的效率明显下降了。

为什么 like 关键词左或者左右模糊匹配

因为 B+ 树在底层是有序的，只能从最左边开始匹配，不是从最左边开始的匹配都会走全表扫描。

对索引使用函数

EXPLAIN SELECT * FROM salaries WHERE LENGTH(id) = 5;

如上所示，在对索引字段使用函数之后，进行了全表扫描，在执行计划中的 TYPE = ALL。

为什么索引使用了函数之后，就无法走索引了呢？

因为在索引中保存的数据的原始值，想要经过函数运算得到具体的结果，自然是需要通过全表扫描找到符合函数的行记录。

对索引进行表达式计算

在查询条件中，对索引的字段进行了表达式计算，所谓的表达式就是加减乘除的运算。

EXPLAIN SELECT * FROM salaries WHERE id + 1 = 1222;

从上面来看，主键索引的 id 字段，由于在 where 条件后面，添加了 id + 1 的操作，使得索引字段加入了表达式，所以就会进行全表扫描，使得查询的效率变得低下。

为什么索引字段存在表达的时候，索引就会失效？

因为索引本身保存的值不是经过表达式计算的数值，想要得到满足需要使用的条件的数据，需要将表中所有的数据一一进行比较，然后的到最终的结果。

开发人员在使用的时候，最好不要使用表达式进行查询结果，否则可能效率十分低下。

对索引隐式类型转换

如果索引段是字符串类型，在条件查询中如果的 where 条件是整数类型，那么类型就不匹配了，那么就会查询全表。

// 在索引字段 phone 上面添加了隐性函数，所以导致了索引失效
EXPLAIN SELECT * FROM salaries WHERE phoone = 13111111111;  // 不走索引 因为隐式的 CAST 函数添加到了等号的左边

select * from t_user where id = "1"; // 走索引 因为隐式的 CAST 函数添加到了等号的右边

如上所示：

在数据库中 phone 是一种 String 类型，但是在使用 SQL 查询的时候，使用的是整数类型，这个时候就发生了隐式的类型转换，在 InnoDB 中就会走全表扫描，而不是走索引，这样一来就会使得查询的时间增长。

在 MySQL 底层的字符类型和整数类型转换的时候，会自动的将字符类型转换为之后整数类型，底层使用的是 CAST 函数。

需要注意：上述隐式的类型转换的核心问题就是在索引字段 phone 上面添加了 CAST 函数，在索引字段隐性的使用函数进行转换，所以导致了索引的失效。

不满足最左匹配原则

一建立了（a, b, c）三个字段的联合索引，每次在 where 后面的条件中国，必须先出现 a 字段的条件然后是 b 字段的条件， c 字段的条件，否则不满足最左匹配原则，导致索引失效。

索引失效的最本质的问题就是，索引在底层的 B+ 树是有序的，符合最左匹配原则的时候，可以从 B+ 树中获取数据，不符合的时候，就不能使用 B+ 树进行索引了。

存在一种特殊的情况：

使用索引 a 和索引 c ，这就发生了索引截断，在不同的版本不同的额处理方式：

5.5 ：先使用 a 索引查询到满足条件 a 的所有记录以及符合 a 条件的所有记录所在行记录的主键值，然后使用拿到的主键们再找到符合 c 条件的字段的值，进行了回表操作。

5.6 ：使用索引下推解决，索引下推一般简称为 ICP，在搜索引擎中提前判断对应的搜索条件是否满足，满足了再去回表，通过减少回表次数进而提高查询效率。直接在存储引擎判断条件 a 以及条件 c，只有所有条件都满足的时候，将相关的行记录返回，在使用主键索引进行回表查询。和上面的 5.5 相比减少了回表次数，没有将条件一个一个找，而是所有条件一起找。

索引下推相比较非索引下推就是，就是一次性的将所有的的在索引上面添加的字段都传入到存储引擎中，存储引擎找到一次性找到符合所有条件的行记录，然后在找到符合所有条件的行记录的 id 主键进行回表操作，这样一来，减少了回表操作，使得操作的效率有了一定程度的提升。

where 后面 or 左右一个条件是索引，一个条件不是索引，索引失效

EXPLAIN SELECT * FROM salaries where id = 1 OR emp_no = 1000;

在上面的语句中， id 是有索引的，但是 emp_no 字段是没有索引的，所以在使用 explain 的时候，发现走的是全表扫描，效率是比较差的。

因为 or 的含义就是满足一个就行，所以就算左边的字段存在索引，但是右边的字段没有索引，为了查询右边的字段有没有满足条件的行记录，所以右边的字段会进行全表扫描，最终的结果就是，整体的查询使用了全表扫描。

小结

上面总结了 6 中索引失效的具体情况：

1、使用左或者左右的模糊匹配的时候，索引会失效

2、对索引列使用函数的时候，索引会失效

3、对索引列进行表达式计算的时候，索引会失效

4、在隐式数据类型转换如果作用到索引列的时候，索引会失效

5、联合索引但是不满足最左匹配原则的时候，索引会失效

6、where 后面使用 or 只有一边是索引列的时候，索引会失效

MySQL 索引失效的情况