首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >索引失效的场景有哪些?索引何时会失效?

索引失效的场景有哪些?索引何时会失效?

作者头像
用户2242639
发布于 2021-11-04 02:47:58
发布于 2021-11-04 02:47:58
57900
代码可运行
举报
文章被收录于专栏:Java经验总结Java经验总结
运行总次数:0
代码可运行

来源:blog.csdn.net/bless2015/article/details/84134361

虽然你这列上建了索引,查询条件也是索引列,但最终执行计划没有走它的索引。下面是引起这种问题的几个关键点。

列与列对比

某个表中,有两列(id和c_id)都建了单独索引,下面这种查询条件不会走索引

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from test where id=c_id;

这种情况会被认为还不如走全表扫描。

存在NULL值条件

我们在设计数据库表时,应该尽力避免NULL值出现,如果非要不可避免的要出现NULL值,也要给一个DEFAULT值,数值型可以给0、-1之类的, 字符串有时候给空串有问题,就给一个空格或其他。

如果索引列是可空的,很可能是不会给其建索引的,索引值是少于表的count(*)值的,所以这种情况下,执行计划自然就去扫描全表了。

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from test where id is not null;

NOT条件

我们知道建立索引时,给每一个索引列建立一个条目,如果查询条件为等值或范围查询时,索引可以根据查询条件去找对应的条目。

反过来当查询条件为非时,索引定位就困难了,执行计划此时可能更倾向于全表扫描,这类的查询条件有:<>NOTinnot exists

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from test where id<>500;
select * from test where id in (1,2,3,4,5);
select * from test where not in (6,7,8,9,0);
select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

LIKE通配符

当使用模糊搜索时,尽量采用后置的通配符,例如:name||’%’,因为走索引时,其会从前去匹配索引列,这时候是可以找到的,如果采用前匹配,那么查索引就会很麻烦,比如查询所有姓张的人,就可以去搜索’张%’。

相反如果你查询所有叫‘明’的人,那么只能是%明。这时候索引如何定位呢?前匹配的情况下,执行计划会更倾向于选择全表扫描。后匹配可以走INDEX RANGE SCAN。

所以业务设计的时候,尽量考虑到模糊搜索的问题,要更多的使用后置通配符。

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from test where name like 张||'%';

条件上包括函数

查询条件上尽量不要对索引列使用函数,比如下面这个SQL

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from test where upper(name)='SUNYANG';

这样是不会走索引的,因为索引在建立时会和计算后可能不同,无法定位到索引。但如果查询条件不是对索引列进行计算,那么依然可以走索引。比如

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from test where name=upper('sunyang');
--INDEX RANGE SCAN

这样的函数还有:to_char、to_date、to_number、trunc等。

复合索引前导列区分大

当复合索引前导列区分小的时候,我们有INDEX SKIP SCAN,当前导列区分度大,且查后导列的时候,前导列的分裂会非常耗资源,执行计划想,还不如全表扫描来的快,然后就索引失效了。

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from test where owner='sunyang';

数据类型的转换

当查询条件存在隐式转换时,索引会失效。比如在数据库里id存的number类型,但是在查询时,却用了下面的形式:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from sunyang where id='123';

Connect By Level

使用connect by level时,不会走索引。

谓词运算

我们在上面说,不能对索引列进行函数运算,这也包括加减乘除的谓词运算,这也会使索引失效。建立一个sunyang表,索引为id,看这个SQL:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from sunyang where id/2=:type_id;

这里很明显对索引列id进行了’/2’除二运算,这时候就会索引失效,这种情况应该改写为:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from sunyang where id=:type_id*2;

就可以使用索引了。

Vistual Index

先说明一下,虚拟索引的建立是否有用,需要看具体的执行计划,如果起作用就可以建一个,如果不起作用就算了。

普通索引这么建:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
create index idx_test_id on test(id);

虚拟索引Vistual Index这么建:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
create index idx_test_id on test(id) nosegment;

做了一个实验,首先创建一个表:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
CREATE TABLE test_1116( 
id number, 
a number 
); 

CREATE INDEX idx_test_1116_id on test_1116(id); 
CREATE INDEX idx_test_1116_a on test_1116(a)nosegment; 

其中id为普通索引,a为虚拟索引。

在表中插入十万条数据

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
begin 
for i in 1 .. 100000 loop 
        insert into test_1116 values (i,i); 
end loop; 
commit; 
end; 

接着分别去执行下面的SQL看时间,由于在内网机做实验,图贴不出来,数据保证真实性。

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select count(id) from test_1116;
--第一次耗时:0.061--第二次耗时:0.016
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select count(a) from test_1116; 
--第一次耗时:0.031--第二次耗时:0.016

因为在执行过一次后,oracle对结果集缓存了,所以第二次执行耗时不走索引,走内存就都一样了。可以看到在这种情况下,虚拟索引比普通索引快了一倍。

具体虚拟索引的使用细节,这里不再展开讨论。

Invisible Index

Invisible Index是oracle 11g提供的新功能,对优化器(还接到前面博客里讲到的CBO吗)不可见,我感觉这个功能更主要的是测试用,假如一个表上有那么多索引,一个一个去看执行计划调试就很慢了,这时候不如建一个对表和查询都没有影响的Invisible Index来进行调试,就显得很好了。

通过下面的语句来操作索引

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
alter index idx_test_id invisible;
alter index idx_test_id visible;

如果想让CBO看到Invisible Index,需要加入这句:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
alter session set optimizer_use_invisible_indexes = true;

基本就这些了,有问题欢迎留言指出,共同进步!

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-11-03,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 Java经验总结 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
OR条件拆分:避免索引失效的查询重构技巧
在数据库查询优化中,索引是提升性能的核心利器。然而,一个常见的陷阱是:看似简单的 OR 条件可能导致索引完全失效,引发全表扫描和性能断崖式下跌。
Jimaks
2025/06/30
1061
OR条件拆分:避免索引失效的查询重构技巧
Oracle性能调优之虚拟索引用法简介
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u014427391/article/details/89761234
SmileNicky
2019/05/14
4990
一次非常有意思的 SQL 优化经历:从 30248.271s 到 0.001s
发现没有用到索引,type全是ALL,那么首先想到的就是建立一个索引,建立索引的字段当然是在where条件的字段。
芋道源码
2019/05/23
4070
索引失效了?看看这几个常见的原因!
索引是 MySQL 数据库中优化查询性能的重要工具,通过对查询条件和表数据的索引,MySQL可以快速定位数据,提高查询效率。但是,在实际的数据库开发和维护中,我们经常会遇到一些情况,导致索引失效,从而使得查询变得非常缓慢,甚至无法使用索引来优化查询,这会严重影响系统的性能。那么,是什么原因导致了索引失效呢?
杨同学technotes
2023/04/21
1.4K0
谁还没碰过索引失效呢
稍不注意,可能你写的查询语句是会导致索引失效,从而走了全表扫描,虽然查询的结果没问题,但是查询的性能大大降低。
小林coding
2022/02/11
4840
谁还没碰过索引失效呢
聊聊索引失效的10种场景,太坑了
我之前写的一篇文章《聊聊sql优化的15个小技巧》,自发表之后,在全网广受好评,被很多大佬转载过,说明了这类文章的价值。
苏三说技术
2022/08/25
1.4K0
聊聊索引失效的10种场景,太坑了
一次非常有意思的sql优化经历
发现没有用到索引,type全是ALL,那么首先想到的就是建立一个索引,建立索引的字段当然是在where条件的字段。
后端技术探索
2018/08/09
4220
要不要走索引?MySQL 的成本分析
谈到索引失效,大家可能都能列举出几个场景,比如:后模糊查询、条件中带函数、索引中断等等。今天我想和你分享另一个场景:索引成本分析。
杨同学technotes
2022/12/01
5810
我去,为什么最左前缀原则失效了?
最近,在 mysql 测试最左前缀原则,发现了匪夷所思的事情。根据最左前缀原则,本来应该索引失效,走全表扫描的,但是,却发现可以正常走索引。
烟雨星空
2020/06/16
1.3K0
15个必知的Mysql索引失效场景,别再踩坑了
无论你是技术大佬,还是刚入行的小白,时不时都会踩到Mysql数据库不走索引的坑。常见的现象就是:明明在字段上添加了索引,但却并未生效。
程序新视界
2022/05/06
28.3K1
15个必知的Mysql索引失效场景,别再踩坑了
Oracle优化器基础知识
本博客介绍一下属于oracle优化器范畴的一些基础知识,访问数据的方法,分为直接访问数据的方法和访问索引的方法两种,然后有了这些基础知识后,可以参考学习我的另外一篇博客:Oracle优化器简介,对Oracle 的一些原理的简单介绍,对于学习oracle方面的SQL优化是有帮助的,https://cloud.tencent.com/developer/article/1399323
SmileNicky
2019/03/20
7100
Oracle优化器基础知识
两千字揭密 MySQL 8.0.19 三大索引新功能:隐藏索引,降序索引,函数索引
导读:本文详细介绍 MySQL 8.0.19 三大索引新功能,隐藏索引,降序索引,函数索引,结合其他同仁的技术应用案例,进一步进行验证改编,最后总结心得,希望对大家有帮助。
数据和云
2020/03/25
1.1K0
Oracle sql调优(网络优化知识点)
本博客介绍一下属于oracle优化器范畴的一些基础知识,访问数据的方法,分为直接访问数据的方法和访问索引的方法两种,然后有了这些基础知识后,可以参考学习我的另外一篇博客:Oracle优化器简介,对Oracle 的一些原理的简单介绍,对于学习oracle方面的SQL优化是有帮助的,https://blog.csdn.net/u014427391/article/details/87656904
全栈程序员站长
2022/07/29
7610
Oracle sql调优(网络优化知识点)
你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?
过年回来的第二周了,终于有时间继续总结知识了。这次来看一下SQL调优的知识,这类问题基本上面试的时候都会被问到,无论你的岗位是后端,运维,测试等等。 像本文标题中的两个问题,就是我在实际面试过程中遇到的,所以这次就主要围绕着这两个问题来总结一下。
纪莫
2021/03/04
9820
你们一般都是怎么进行SQL调优的?MySQL在执行时是如何选择索引的?
MySQL常见的索引失效场景
索引创建和删除语句如下,方便大家自己进行其他测试,建议自己将所有语句运行一边,使用explain + 查询语句看看运行计划,加深一边印象
天下之猴
2024/09/11
1850
MySQL常见的索引失效场景
真正线上索引失效的问题是如何排查的
针对索引失效的排查,关键步骤包括确定需要分析的SQL语句,并通过EXPLAIN查看其执行计划。主要关注type、key和extra这几个字段。
@派大星
2024/03/20
2550
真正线上索引失效的问题是如何排查的
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
千寻简
2025/01/03
3740
MySQL秘籍之索引与查询优化实战指南
Christina问我:你都是如何设计索引的?
数据库系列更新到现在我想大家对所有的概念都已有个大概认识了,这周我在看评论的时候我发现有个网友的提问我觉得很有意思:帅丙如何设计一个索引?你们都是怎么设计索引的?怎么设计更高效?
敖丙
2021/01/08
8550
Christina问我:你都是如何设计索引的?
【DB笔试面试553】在Oracle中,什么是不可见索引?
索引维护是DBA的一项重要工作。当一个系统运行很长一段时间,经过需求变更、结构设计变化后,系统中就可能会存在一些不再被使用的索引,或者使用效率很低的索引。这些索引的存在,不仅占用系统空间,而且会降低事务效率,增加系统的负载。因此,需要找出那些无用或低效的索引,并删除它们(找出无用索引可以通过索引监控的方法)。但是,直接删除索引还是存在一定风险的。例如,某些索引可能只是在一些周期的作业中被使用到,而如果监控周期没有覆盖到这些作业的触发点,那么就会认为索引是无用的,从而将其删除。当作业启动后,可能就会对系统性能造成冲击。这时,可能就会手忙脚乱地去找回索引定义语句、重建索引。在Oracle 11g里,Oracle提供了一个新的特性来降低直接删除索引或者禁用索引的风险,那就是不可见索引(Invisible Indexes)。
AiDBA宝典
2019/09/29
7250
最详细的 MySQL 执行计划和索引优化!
不管是工作中,还是面试中,关于mysql的explain执行计划以及索引优化,都是非常值得关注的。
田维常
2023/08/31
9660
最详细的 MySQL 执行计划和索引优化!
相关推荐
OR条件拆分:避免索引失效的查询重构技巧
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档