sql索引失效和优化

一、oracle索引失效情况

1、没有where子句

​ 添加索引的字段必须在where条件后适当使用才会生效

2、where子句中使用函数

如果没有使用基于函数的索引，那么对有索引的列使用函数会使索引失效；

但是把函数放在应用条件上，索引是可以有效的

eg：select * from staff where trunc( birthdate ) = ‘01-MAY-82’; // birthdate上的索引失效

select * from staff where birthdate< to_date( ‘01-MAY-82’); // birthdate上的索引有效

3、使用is null 和 is not null 索引全部失效

4、使用 like ‘%T’ 进行模糊查询

select * from staff where name like ‘aaa%’ ; // name索引是有效的

select * from staff where name like ‘%aaa’ ; // name索引是无效

5、使用不等于 <>, !=, NOT colum >=?, NOT colum<=? 可以通过OR替代，

例如 Colum <> 0 可用 colum>0 OR colum<0

6、等于和范围索引不会被合并使用，只会使用第一个索引

7、比较不匹配的数据类型

比如一个字段是varchar2类型，查询时用的是 where a=10001，会被自动转换成 where to_number(a) = 10001

二、索引相关其他

1. or语句如果有一个字段没有索引，就全部不走索引
2. 建立索引一般分为在线索引和非在线索引

在线索引：create index index_My_test on userId
非在线索引： 锁表，优先创建索引，此时DML都会阻塞  create index X on student_info(student_id);  explain select * from student_info 

三、sql优化

• sql语句使用大写

• 条件子句优先级：on，where，having

• exists替代in，not exists替代not in

• group by最好使用where子句筛选后分组

• where子句执行顺序从右到左，过滤最多的条件放在最后