引言:在数据库优化中,索引是一个非常重要的话题。许多人都认为只要在查询字段上创建索引,查询就会变得更快,但实际情况并非总是如此。有时候,索引可能会失效,甚至导致查询变慢。因此,了解索引的使用情况以及如何排查索引失效是至关重要的。
题目
用了索引一定就有用吗?如何排查?
推荐解析
可以用过 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/
使用了索引并不总是能够提高查询效率,有时甚至会导致查询变慢。因此,当我们使用索引后发现查询效率没有提升,就需要进行排查。以下是一些常见的排查方法:
- 查看执行计划(EXPLAIN):通过执行
EXPLAIN {SQL语句}
来查看数据库的执行计划。主要关注查询的访问类型(type)、使用的索引(key)以及扫描的行数(rows)。如果发现查询使用了全表扫描(type为ALL)或没有使用索引(key为NULL),就说明索引可能没有生效。 - 检查索引是否被正确使用:
- 确保查询条件中的字段与索引匹配,并且遵循最左前缀原则。
- 避免在索引字段上使用函数或运算,这会导致索引失效。
- 注意模糊查询中通配符的位置,通常应该避免将通配符放在开头。
- 对于OR条件,确保所有条件都能使用索引,否则可能导致索引失效。
- 分析索引是否选择合适:有时候索引的选择不合适,可能是选择性太低或者覆盖度不够。可以通过查看索引的选择性(distinct值的比例)和覆盖度(索引包含的列是否满足查询需求)来进行分析。
- 检查索引的存储情况:索引也需要占用存储空间,如果数据量较小或者索引列的重复度较高,可能会导致索引失效。因此,需要根据实际情况评估是否需要创建或删除索引。
- 监控数据库性能指标:通过监控数据库的性能指标,如查询响应时间、索引命中率等,可以及时发现索引是否生效以及可能存在的性能问题。
通过以上方法的排查,可以帮助我们确定索引是否真正起到了作用,以及找出可能导致索引失效的原因,进而进行优化和调整。
欢迎交流
在阅读完本文后,你应该了解索引的失效场景和如何正确利用索引提高查询的效率,关于数据库索引这块需要好好去深入了解,在文末还有三个问题,欢迎小伙伴在评论区留言!
1)慢查询日志如何开启?
2)索引的类型有哪些?
3)索引失效的场景有几种?简单介绍一下