索引失效的几种情况

1.查询的数量是大表的大部分

说明：单次查询如果查出表的大部分数据，这会导致编译器认为全表扫描性能比走索引更好，从而导致索引失效。一般单次查询数量大概占大表的30%以上索引会失效。

 

2.索引本身失效

说明：索引需要定期重建。

重建索引的原因主要包括：

1、 删除的空间没有重用，导致索引出现碎片

2、 删除大量的表数据后，空间没有重用，导致索引"虚高"

3、索引的 clustering_facto 和表不一致

 

3.查询条件使用函数在索引列上

select * from test where round(id)=10;   此时id的索引已经不起作用了.

正确的例子：

首先建立函数索引:create index test_id_fbi_idx on test(round(id)); 
然后 select * from test where round(id)=10;

这时函数索引起作用了 .

 

4.对小表查询

说明：对于数据量较少的表，本身不需要创建索引，如果建立了索引，索引未必生效。

 

5.提示不使用索引

说明：SQL执行时强行指定不走索引。

 

6.统计数据不真实或者表没分析

说明：数据库会定时对表进行分析，如果表过大导致分析计划没有及时跑完，或者由于其他原因导致统计数据不真实，这样会导致CBO计算走索引花费不准确的情况，可能会导致不走索引而使用全表扫描。这也是为什么当表的数据量达到一定级别的时候，我们建议进行分表分库，因为表数据量过大，可能导致表分析过程没有执行完成。

 

7.隐式转换导致索引失效

这一点应当引起重视，也是开发中经常会犯的错误.。由于表的字段tu_mdn定义为varchar2(20), 但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.。
错误的例子：select * from test where tu_mdn=13333333333; 
正确的例子：select * from test where tu_mdn='13333333333'; 

 

8.对索引列进行运算导致索引失效

对索引列进行运算包括(+，-，*，/，! 等) 
错误的例子：select * from test where id-1=9; 
正确的例子：select * from test where id=10; 

 

9.where 子句中使用<>或者!=

说明：形如select * from table where id <>1 这样的情况一般不走索引。

 

10.like "%****" 百分号在前（like '%XX'或者like '%XX%'）

说明：

select * from table where name like ‘%aaa’   不走索引

select * from table where name like 'aaa%'  走索引

 

11.单独引用复合索引里非第一位置的索引列

说明：索引遵循最左原则，形如ABC的复合索引如果单独使用B或者C则不走此复合索引。

复合索引 :ABC

引用情况：  ABC（走ABC复合索引） AB(走AB联合索引)      AC（使用索引A）      BC(不使用索引)    A(使用索引A)     B(不使用索引)     C（不使用索引）

 

12.not in ,not exist

说明：使用not in ,not exist一般不走索引。

在使用 not in 的时候，需要保证子查询的匹配字段是非空的。如若不然，就会导致 not in 返回的整个结果集为空。

 

13.is null,is not null

B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走 联合索引的not NULL比较麻烦，实际需要测试，联合索引is not null会走索引，同样也需要满足最左原则。

 

14.条件中有or

select * from table where id = 10 or pid = 10 ；   此类查询必须满足id和pid均有索引，如果只是id或者pid存在索引，则索引不生效。

可以改为：

select * from table where id = 10   union select * from table where  pid = 10 ； 这样的话，就不需要满足id和pid均有索引的条件。

 

15.in,exists

in：   select * from t1 where id in (select id from t2);

大表 t2 做外表还是内表，都会走索引的，小表 t1 做内表时也会走索引,小表t1做外表不走索引

exists:   select * from t1 where exists (select 1 from t2 where t1.id=t2.id);

t1 表哪种情况都不会走索引，而 t2 表是有索引的情况下就会走索引。

最终结论： 外层大表内层小表，用in。外层小表内层大表，in和exists效率差不多（甚至 in 比 exists 还快，而并不是网上说的 exists 比 in 效率高）。

PS:使用in需要注意，如果内层不是子查询，而是我们组织出来的数据比如 select * from table where id in (1,2,3,4,5...........) 则内层数据每次查询不能超过1000个，否则会导致SQL执行缓慢，对于部分国产数据库甚至可能出现卡死。

select * from t1 where name in (select name from t2);

对于 in 查询来说，会先执行子查询，如上边的 t2 表，然后把查询得到的结果和外表 t1 做笛卡尔积，再通过条件进行筛选（这里的条件就是指 name 是否相等），把每个符合条件的数据都加入到结果集中。

for(x in A){
    for(y in B){
     if(condition is true) {result.add();}
    }
}

select * from t1 where  exists (select 1 from t2 where t1.name = t2.name);

对于 exists 来说，是先查询遍历外表 t1 ，然后每次遍历时，再检查在内表是否符合匹配条件，即检查是否存在 name 相等的数据。

for(x in A){
  if(exists condition is true){result.add();}
}

 

 

说明：文章部分内容借鉴网上文章，部分来源于工作经验，如有侵权，请及时联系我进行删除。