关于数据库:索引失效的场景有哪些索引何时会失效

尽管你这列上建了索引，查问条件也是索引列，但最终执行打算没有走它的索引。上面是引起这种问题的几个关键点。

列与列比照

某个表中，有两列（id 和 c_id）都建了独自索引，上面这种查问条件不会走索引

select * from test where id=c_id;

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

存在 NULL 值条件

咱们在设计数据库表时，应该尽力防止 NULL 值呈现，如果非要不可避免的要呈现 NULL 值，也要给一个 DEFAULT 值，数值型能够给 0、- 1 之类的，字符串有时候给空串有问题，就给一个空格或其余。

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

select * from test where id is not null;

NOT 条件

咱们晓得建设索引时，给每一个索引列建设一个条目，如果查问条件为等值或范畴查问时，索引能够依据查问条件去找对应的条目。

反过来当查问条件为非时，索引定位就艰难了，执行打算此时可能更偏向于全表扫描，这类的查问条件有：<>、NOT、in、not exists

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。

所以业务设计的时候，尽量思考到含糊搜寻的问题，要更多的应用后置通配符。

select * from test where name like 张 ||'%';

条件上包含函数

查问条件上尽量不要对索引列应用函数，比方上面这个 SQL

select * from test where upper(name)='SUNYANG';

这样是不会走索引的，因为索引在建设时会和计算后可能不同，无奈定位到索引。但如果查问条件不是对索引列进行计算，那么仍然能够走索引。比方

select * from test where name=upper('sunyang');
--INDEX RANGE SCAN

这样的函数还有：to_char、to_date、to_number、trunc 等。搜寻公众号民工哥技术之路，回复“1024”，送你一份技术资源，还可查阅更多技术干货文章及面试题精选。

复合索引前导列辨别大

当复合索引前导列辨别小的时候，咱们有 INDEX SKIP SCAN，当前导列区分度大，且查后导列的时候，前导列的决裂会十分耗资源，执行打算想，还不如全表扫描来的快，而后就索引生效了。

select * from test where owner='sunyang';

数据类型的转换

当查问条件存在隐式转换时，索引会生效。比方在数据库里 id 存的 number 类型，然而在查问时，却用了上面的模式：

select * from sunyang where id='123';

Connect By Level

应用 connect by level 时，不会走索引。

• 谓词运算

咱们在下面说，不能对索引列进行函数运算，这也包含加减乘除的谓词运算，这也会使索引生效。建设一个 sunyang 表，索引为 id，看这个 SQL：

select * from sunyang where id/2=:type_id;

这里很显著对索引列 id 进行了’/2’除二运算，这时候就会索引生效，这种状况应该改写为：

select * from sunyang where id=:type_id*2;

就能够应用索引了。举荐：从意识索引到了解索引「索引优化」

Vistual Index

先阐明一下，虚构索引的建设是否有用，须要看具体的执行打算，如果起作用就能够建一个，如果不起作用就算了。

一般索引这么建：

create index idx_test_id on test(id);

虚构索引 Vistual Index 这么建：

create index idx_test_id on test(id) nosegment;

做了一个试验，首先创立一个表：

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 为虚构索引。

在表中插入十万条数据

begin 
for i in 1 .. 100000 loop 
        insert into test_1116 values (i,i); 
end loop; 
commit; 
end; 

接着别离去执行上面的 SQL 看工夫，因为在内网机做试验，图贴不进去，数据保障真实性。

select count(id) from test_1116;
-- 第一次耗时：0.061 秒
-- 第二次耗时：0.016 秒

select count(a) from test_1116; 
-- 第一次耗时：0.031 秒
-- 第二次耗时：0.016 秒

因为在执行过一次后，oracle 对后果集缓存了，所以第二次执行耗时不走索引，走内存就都一样了。能够看到在这种状况下，虚构索引比一般索引快了一倍。

具体虚构索引的应用细节，这里不再展开讨论。

Invisible Index

Invisible Index 是 oracle 11g 提供的新性能，对优化器（还接到后面博客里讲到的 CBO 吗）不可见，我感觉这个性能更次要的是测试用，如果一个表上有那么多索引，一个一个去看执行打算调试就很慢了，这时候不如建一个对表和查问都没有影响的 Invisible Index 来进行调试，就显得很好了。

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

alter index idx_test_id invisible;
alter index idx_test_id visible;

如果想让 CBO 看到 Invisible Index，须要退出这句：

alter session set optimizer_use_invisible_indexes = true;

根本就这些了，有问题欢送留言指出，共同进步！

作者：番茄发烧了
blog.csdn.net/bless2015/article/details/84134361