--总结:
--第一部分:真正意义上用不到索引(即失效)
(1)使用null条件, 索引失效(说明:not null会使用索引,但是是索引全扫描)
(2)使用纯不等式条件!=或者<>, 索引失效
(3)使用表达式或者函数,索引失效
(4)字符存储但却使用数字查询,索引失效(如存储为'2',使用2查询)
(5)使用or但存在没有建立索引字段,索引失效
(6)使用联合索引不遵循最左原则,索引失效(即必须使用了索引最左侧字段,就算仅使用最左侧字段也会用索引)
--第二部分:用到了索引,但是是全扫描
(1)'%like'并不会让索引失效,只是使用索引全扫描,其他形式使用范围扫描
--第三部分:引起误解的部分(说明:oracle版本:11gR2)
(1)between并不会让索引失效
(2)in并不会让索引失效
--索引原理:
--将聚集索引字段建立平衡树,节点存储索引字段值,聚集索引直接找索引所在节点,非聚集索引会先找到聚集索引再往后找。
--唯一索引直接找记录,非唯一索引还会根据rowid查找对应记录。
--索引失效
--1.使用null值条件,索引失效
select * from student where score is null --索引失效
select * from student where score is not null --索引全扫描:INDEX FULL SCAN
--2.使用纯不等式条件: != 或者 <>,索引失效
select * from student where score != 88 --索引失效
select * from student where score <> 88 --索引失效
select * from student where score > 88 --索引范围扫描:INDEX RANGE SCAN
select * from student where score < 88 --索引范围扫描:INDEX RANGE SCAN
select * from student where score = 88 --索引范围扫描:INDEX RANGE SCAN
--3.使用表达式/函数,索引失效
select * from student where score / 2 = 30 --索引失效
select * from student where substr(name, 0, 10) = '语文' --索引失效
select * from student where name = '语文' --索引范围扫描:INDEX RANGE SCAN
--4.字符存储使用数字查询,索引失效
select * from student where scorelevel = '1'
--5.使用or且存在没有建立索引字段,索引失效
select * from student where score = 88 or lesson = '语文' --索引失效
select * from student where score = 88 or name = 'szxu' --两个索引范围扫描:INDEX RANGE SCAN
--6.联合索引不遵循最左原则,索引失效(即必须使用索引最左侧字段)
select * from student where lesson = '语文' --索引失效
select * from student where age = 50 --索引范围扫描:INDEX RANGE SCAN
--------- 第二部分:索引未失效,但是可能是全扫描 -------------
--7.‘%like’并不会让索引失效,只是使用索引全扫描,其他形式使用范围扫描
select * from student where name like 'szxu' --索引范围扫描:INDEX RANGE SCAN
select * from student where name like 'szxu%' --索引范围扫描:INDEX RANGE SCAN
select * from student where name like '%szxu' --索引全扫描:INDEX FULL SCAN
--8.between并不会让索引失效
select * from student where score between 70 and 80 --索引不会失效,使用索引范围扫描
--9.in并不会让索引失效
select * from student where score in ( 88, 66, 96 ) --索引不会失效,使用索引范围扫描
select * from student where name in ( 'nihao' ) --索引不会失效,使用索引范围扫描
--附:表格详情
select * from student
-- drop table student
truncate table student
create table student (
id number(19),
name varchar2(50),
age number(10),
lesson varchar2(50),
score number(10),
scorelevel varchar2(10)
)
alter table student
add constraint pk_student_id primary key(id)
create index ix_student_name on student(name)
create index ix_student_score on student(score)
create index ix_student_age_lesson on student(age, lesson)
create index ix_student_scorelevel on student(scorelevel)
insert into student values(1, 'szxu_orcl', 30, '语文', 90, '1');
insert into student values(2, 'yxiong_orcl', 25, '数学', 80, '2');
insert into student values(3, 'ymli_orcl', 20, '英语', 70, '3');
insert into student values(4, 'nihao_orcl', 15, '物理', 60, '4');
insert into student values(5, 'hello_orcl', 10, '化学', 50, '5');
select * from student