索引失效的几种情况
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();}
}
说明：文章部分内容借鉴网上文章，部分来源于工作经验，如有侵权，请及时联系我进行删除。
