🔍研究了 4.7 个小时终于了解到了索引使用了却没变快的原因

大家好，我是聪，一个乐于分享的小小程序员。前几天我竟然碰见了加了索引，查询效率反而降低了 0.06% ，知道我排查才发现，原来可能是索引没生效，我赶忙来总结一下没生效的原因。 可以用过 MySQL 的执行计划来查看索引是否有用 explain {SQL语句}，主要观察几个点：

• type（访问类型）：这个属性显示了查询使用的访问方法，例如 ALL、index、range等。当查询使用索引时，这个属性通常会显示为 index 或 range ，表示查询使用了索引访问。如果这个值是 ALL ，则表示查询执行了全表扫描，没有使用索引。
• key（使用的索引）：这个属性显示了查询使用的索引，如果查询使用了索引，则会显示索引的名称。如果这个值是 NULL，则表示查询没有使用索引。
• rows（扫描的行数）：这个属性显示了查询扫描的行数，即查询返回的行数。如果这个值很大，可能表示查询执行了全表扫描，而不是使用了索引。

对于用了索引一定就有用吗这个问题，我们排查要从两方面入手，具体如下：

索引真的生效了吗？

索引失效的场景有很多，也是面试官经常喜欢问的，可以根据具体场景进行排查，具体场景可以分为以下几点：

1）使用了联合索引却不符合最左前缀

举个例子：小鱼对 user 表建立了一个联合索引为 name_age_id 的联合索引，但是呢当他使用 SQL 查询的时候却是这样写的，select * where from age = 10 and id = 1; 这样的写法恰恰不满足最左前缀原则，索引就失效啦。

2）索引中使用了运算

举个例子：小鱼今天突发奇想，我试试新花样我直接在查询条件上运算怎么样！，那么如下 SQL 诞生了 select * from user where id + 3 = 8，真是聪明反被聪明误啊，这样恰恰导致了索引失效了，因为这样会导致全表扫描计算 id 的值再进行比较。

3）索引上使用了函数也会失效

小鱼真的懒死了！它连一点代码都不想写都想交给 MySQL 给他处理问题，它使用了函数让 MySQL 给他转换字段大小写，于是他写出了这样的 SQL 语句 select * from user where LOWER(name) like 'cong%';，这样也会导致索引失效，索引参与了函数处理，会导致去全表扫描喔。

4） like 的随意使用

相信小鱼写查询的时候都很喜欢将 % 写在首位，如：select * from user where name like '%cong%'; 这种情况就会导致索引失效喔，因为索引是从左到右来进行排序查找的，你的占位符直接放在了最左边开头，那么就直接导致要使用全表扫描啦~

5）or 的随意使用。

小鱼在 user 建了一个索引 name ，他以为稳了！写下了以下 SQL ：select * from user where name= 'cong' or age = 18; 正是如此也导致了索引失效。不难看出当使用了 or 关键字时要两边同时都拥有索引才会生效喔~还有一种特殊的情况使用了 < 和 > 号也导致了索引失效因为是 or 使用了范围查询喔，例子如下：select * from user where age > 1 or age < 18;。

6）随意的字段类型使用

粗心的小鱼将原本是 varchar 类型的 name 条件匹配了 int 类型字段，SQL 是这样的 select * from user where name = 1;，在代码中涉及 隐式转换 ！将原本是 int 类型的 1 转成了 varchar 类型，这种情况也是不走索引的喔。

7）不同的参数也会导致索引失效

这个可能有点难以理解，具体 SQL 为 select * from user where age > 18; 、 select * from user where age > 180; 根据数据量的不同，你有可能会发现前面那条走了索引而后面的却没走。这是因为当 MySQL 发现当使用了索引还不如直接全表扫描效率高，因此它会直接让索引失效。

8）表中两个不同字段进行比较

有天小鱼写了一条这样的 SQL :select * from user where id > age; ，小鱼他竟然将 id 跟 age 字段做了比较，好家伙！索引失效！

9）使用了 != 、<>

小鱼写的 SQL ：select * from user where name != 'cong' ，可能会导致索引失效，这个场景下的 SQL 是否失效是根据查询到的数据集决定的，当数据大了的时候可能会导致索引失效。

10）使用了 is not null

注意使用了 is not null 会直接导致失效，而使用 is null 则不会。因为当使用 is not null 条件时，数据库系统需要扫描整个索引，找出所有不为 null 的值，这可能会导致索引失效。使用 is null条件不会导致索引失效，因为对于索引而言，查找 null 值的行与查找其他任何特定值的行都没有本质区别。在大多数情况下，null 值都会被索引包括在内。

11）使用了 order by

当 order by 后面跟的 不是主键 或者 不是覆盖索引 会导致不走索引。

为什么索引生效了反而查询变慢了呢？

1）索引也是需要占空间的。当数据少的时候，建立了多个索引反而会导致查询速度没效果反而更慢。

2）对大字段建立了索引。小鱼给 text 字段建立了索引导致查询需要扫描更多的数据块，反而变慢。

3）对重复值较多的字段建立索引。今天小鱼给性别字段建立了索引，他发现怎么查询还变满了！索引列的一些值重复度非常高，那么查询可能会导致大量的磁盘I/O操作，从而导致查询变慢。