MySQL索引失效原因

索引介绍

MySQL默认的存储引擎是InnoDB，它采用B+树作为索引的数据结构。

InnoDB和MyISAM存储引擎都支持B+树索引，但是它们的数据存储结构实现方式不同。

• InnoDB：B+树索引的叶子节点保存全部数据
• MyISAM：B+树索引的叶子节点保存数据的物理地址

在创建表的时候，InnoDB存储引擎默认会创建一个主键索引，也就是聚簇（cù）索引，其他索引都是二级索引。它们的区别在于

• 主键索引：叶子节点保存了行记录数据
• 二级索引：叶子节点只保存了主键值和索引列

使用主键索引字段作为查询条件，当主键索引的B+树检索到对应的叶子节点时，因为数据都已经保存在了叶子节点中，可以直接读取要查询的数据。比如下面这个查询语句

select * from users where id = 10;

使用二级索引字段作为查询条件，如果要查询的数据都在主键索引的叶子节点中，那么就需要检索两颗B+树。

先在二级索引中找到对应的叶子节点，获取主键值，再通过主键值去主键索引的B+树上检索对应的叶子节点，然后读取要查询的数据。这个过程就叫回表。

select * from users where name = 'cplemom';

在使用二级索引字段作为查询条件的时候，如果要查询的数据都在二级索引的叶子节点中，那么只要在二级索引的B+树中找到对应的叶子节点，然后直接读取要查询的数据。这个过程叫做覆盖索引。比如下面这个查询语句

select id,name from users where name = 'cplemom';

聚簇索引

聚簇索引的优势

• 数据访问更快：

  • 由于叶子节点保存了行数据，且一个节点（磁盘页）包含多条数据，再次访问的时候可以直接在磁盘中完成，不必再读取硬盘
  • 根据主键值获取数据快

• 对主键的排序查找和范围查找速度非常快

聚簇索引的缺点

• 插入速度严重依赖插入顺序，按照主键的顺序插入是最快的，否则会出现页分裂，严重影响性能

  主键的值是顺序的，所每一条记录都存储在上一条记录的后面。当达到页的最大填充因子（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改）下一条记录就会被写入到新的页中。

• 索引维护昂贵，插入数据或者主键更新导致页分裂的时候，严重影响性能。所以一般主键不允许更新

• 主键过大会导致非叶子节点占用更多物理空间

当对表进行大量的增删改操作的时候，很容易产生一些碎片，这些碎片占据着空间，可以通过OPTIMIZE TABLE重新整理表空间

为什么二级索引使用主键作为“指针”而不是地址值作为指针

减少当出现行移动或者数据页分裂时二级索引的维护。

使用主键值当做指针会让辅助索引占用更多的空间，但是相对的，当数据行的位置（实现中通过16K的Page来定位）随着数据库里数据的修改而发生变化（主键索引的B+树节点分裂以及Page的分裂）时，二级索引不受影响。

为什么主键索引建议使用自增Id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即只要索引是相邻的，那么对应的数据一定也是相邻的存储在物理磁盘上的

如果主键不是自增Id，随着数据添加，存储引擎需要不停的调整数据地址，分页，如果主键是自增Id，存储引擎只需要一页页的写，磁盘碎片少，效率也更高。

索引失效以及失效原因

索引的本质就是一颗B+树，说白了不能最快速直接通过B+树找到符合条件的数据就不会走索引。

对索引列使用左模糊匹配

使用like '%cplemom'或者like '%cplemom%'这两种方式都会引起索引失效。

只有使用like 'cplemom%'的适合才会走索引。

因为索引的 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较

对索引列使用函数

特定场景下，查询时需要对数据进行处理，比如对 name 字段使用 LENGTH 函数就不会走索引了。执行计划也是type=ALL全表扫描。

select * from users where LENGTH(name) = 6;

原因也很简单，索引的B+树存储的是索引字段的原始值，函数计算后的值自然无法比较只能全表扫描了。

从 MySQL 8.0 开始，索引特性增加了函数索引，可以对函数计算后的值建立索引。

对索引列进行表达式计算

select * from users where id + 1 = 10;

原因和对索引列使用函数是一样的

对索引列隐式类型转换

当索引列是字符串类型，where中的查询参数是整型时，索引还是会失效，执行计划还是全表扫描。

select * from users where phone = 12345606324;

当索引列是整型，where中的查询参数是字符串类型时，就可以走索引扫描。

select * from users where age = '18';

MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字进行比较。

可以尝试通过select '10' > 9; 的结果来了解MySQL的转换规则。

所以上面两个查询语句也等于加上了CAST函数，自然无法走索引了。

select * from users where  CAST(phone AS SIGNED); = 12345606324;
select * from users where id = CAST("1" AS SIGNED);

联合索引非最左匹配

多个普通字段组合在一起建立的索引就是联合索引，在建立索引的时候一定注意顺序问题。因为正确使用联合索引需要遵循最左匹配原则，按照最左优先的方式进行索引匹配。

比如创建一个联合索引(a, b, c)，等于创建了(a)、(a, b)、(a, b, c) 3个索引，

当符合下面的几个查询条件的时候就可以走索引。

select * from users where a = 1;
select * from users where a = 1 and b = 2;
select * from users where a = 1 and b = 2 and c = 3;

因为查询优化器的存在，where中a字段的位置并不重要，会被自动优化。

下面这几个查询就不会走索引了，因为不符合最左匹配。

select * from users where b = 2;
select * from users where c = 3;
select * from users where b = 2 and c = 3;

还有一种查询条件就是，where a = 1 and c = 3会走索引吗？这个不同版本有不同的处理方式。

这里需要大概了解一下MySQL的架构，Server层负责SQL语句的解析优化，然后生成执行计划，再调用引擎去执行数据的存储和检索。

在MySQL5.6之前，存储引擎根据联合索引找到了符合a = 1的节点，然后根据主键逐一进行回表查询，去主键索引找到完整的行记录，然后返回给Server层对记录根据c = 3进行筛选。

在MySQL5.6之后，存储引擎根据联合索引找到了符合a = 1节点，因为联合索引节点中还包含了C列，所以存储引擎可以直接根据c = 3过滤，然后再返回给Server层。

在索引遍历过程中直接过滤不符合查询条件的数据，减少回表次数就是索引下推优化。

WHERE查询语句中使用了OR

在Where语句中，如果OR的一边是索引列，一边是普通列，索引还是会失效的。因为单纯遍历索引并没有办法判别数据是否符合条件，只能全表扫描。

如下面的查询语句，id 是主键，name是普通列，执行计划type=ALL。

select * from users where id = 1 or name = 'admin';	

如果给name列也加上索引，执行计划 type=index merge

index merge的意思就是分别对 id 和 name 列进行遍历，然后将查询结果进行合并，从而避免了全表扫描。

总结

本文介绍了索引的数据结构、索引在不同数据库引擎下的区别、聚簇索引（主键索引）和二级索引的区别以及最后列举了几种会导致索引失效的情况及其原因。