用了索引一定就有用吗？如何排查？

引言：在数据库优化中，索引是一个非常重要的话题。许多人都认为只要在查询字段上创建索引，查询就会变得更快，但实际情况并非总是如此。有时候，索引可能会失效，甚至导致查询变慢。因此，了解索引的使用情况以及如何排查索引失效是至关重要的。

题目

用了索引一定就有用吗？如何排查？

推荐解析

可以用过 MySQL 的执行计划来查看索引是否有用 explain {SQL语句}，主要观察几个点：

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

全表扫描，没有使用索引，简单查询，预计扫描 42 行

const 查询，仅次于 System，使用的索引是主键，预计扫描一行

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

索引真的生效了吗？

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

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操作，从而导致查询变慢。

其他补充

鱼聪明 AI 的回答：

鱼聪明 AI 地址：https://www.yucongming.com/

使用了索引并不总是能够提高查询效率，有时甚至会导致查询变慢。因此，当我们使用索引后发现查询效率没有提升，就需要进行排查。以下是一些常见的排查方法：

1. 查看执行计划（EXPLAIN）：通过执行 EXPLAIN {SQL语句} 来查看数据库的执行计划。主要关注查询的访问类型（type）、使用的索引（key）以及扫描的行数（rows）。如果发现查询使用了全表扫描（type为ALL）或没有使用索引（key为NULL），就说明索引可能没有生效。
2. 检查索引是否被正确使用：
   • 确保查询条件中的字段与索引匹配，并且遵循最左前缀原则。
   • 避免在索引字段上使用函数或运算，这会导致索引失效。
   • 注意模糊查询中通配符的位置，通常应该避免将通配符放在开头。
   • 对于OR条件，确保所有条件都能使用索引，否则可能导致索引失效。
3. 分析索引是否选择合适：有时候索引的选择不合适，可能是选择性太低或者覆盖度不够。可以通过查看索引的选择性（distinct值的比例）和覆盖度（索引包含的列是否满足查询需求）来进行分析。
4. 检查索引的存储情况：索引也需要占用存储空间，如果数据量较小或者索引列的重复度较高，可能会导致索引失效。因此，需要根据实际情况评估是否需要创建或删除索引。
5. 监控数据库性能指标：通过监控数据库的性能指标，如查询响应时间、索引命中率等，可以及时发现索引是否生效以及可能存在的性能问题。

通过以上方法的排查，可以帮助我们确定索引是否真正起到了作用，以及找出可能导致索引失效的原因，进而进行优化和调整。

欢迎交流

在阅读完本文后，你应该了解索引的失效场景和如何正确利用索引提高查询的效率，关于数据库索引这块需要好好去深入了解，在文末还有三个问题，欢迎小伙伴在评论区留言！

1）慢查询日志如何开启？

2）索引的类型有哪些？

3）索引失效的场景有几种？简单介绍一下