InnoDB引擎里面有两种索引类型:一种是主键索引、一种是普通索引。InnoDB用了B+树的结构来存储索引数据。
当使用索引列进行数据查询的时候,最终会到主键索引树中查询对应的数据行进行返回。
理论上来说,使用索引列查询,就能很好的提升查询效率,但是不规范的使用会导致索引失效,从而无法发挥索引本身的价值。
导致索引失效的情况有很多:
1. 在索引列上做运算,比如使用函数,MySQL在生成执行计划的时候,它是根据统计信息来判断是否要使用索引的。
而在索引列上加函数运算,导致MySQL无法识别索引列,也就不会再走索引了。不过从MySQL8开始,增加了函数索引可以解决这个问题。
2. 在一个由多列构成的组合索引中,需要按照最左匹配法则,也就是从索引的最左列开始顺序检索,否则不会走索引。
在组合索引中,索引的存储结构是按照索引列的顺序来存储的,因此在sql中也需要按照这个顺序才能进行逐一匹配。否则InnoDB无法识别索引导致索引失效。
3. 当索引列存在隐式转化的时候,比如索引列是字符串类型,但是在sql查询中没那么MySQL会自动进行类型转化,从而导致索引失效有使用引号。
4. 在索引列使用不等于号、not查询的时候,由于索引数据的检索效率非常低,因此MySQL引擎会判断不走索引。
5. 使用like通配符匹配后缀%xxx的时候,由于这种方式不符合索引的最左匹配原则,所以也不会走索引。但是反过来,如果通配符匹配的是前缀xxx%,符合最左匹配,也会走索引。
6. 使用or连接查询的时候,or语句前后没有同时使用索引,那么索引会失效。只有or左右查询字段都是索引列的时候,才会生效。
除了这些场景以外,对于多表连接查询的场景中,连接顺序也会影响索引的使用。
不过最终是否走索引,我们可以使用explain命令来查看sql的执行计划,然后针对性的进行调优即可。