索引跳跃式扫描（INDEX SKIP SCAN）

索引跳跃式扫描（INDEX SKIP SCAN）适用于所有类型的复合B树索引（包括唯一性索引和非唯一性索引），它使那些在where条件中没有对目标索引的前导列指定查询条件但同时又对该 索引的非前导列指定了查询条件的目标SQL依然可以用上该索引，这就像是在扫描该索引时跳过了它的前导列，直接从该索引的非前导列开始扫描一样（实际的执行过程并非如此），这也是索引跳跃式扫描中"跳跃"（SKIP）一词的含义。

为什么在where条件中没有对目标索引的前导列指定查询条件但Oracle依然可以用上该索引呢？这是因为Oracle帮你对该索引的前导列的所有distinct值做了遍历。

创建一个测试表EMPLOYEE：

create table employee(gender varchar2(1),employee_id number);

将该表的列EMPLOYEE_ID的属性设为NOT NULL：

alter table employee modify(employee_id not null);

创建一个名为IDX_EMPOLYEE的复合B树索引，其中列GENDER是该索引的前导列，列EMPLOYEE_ID是该索引的第二列：

create index idx_employee on employee(gender,employee_id);

使用如下PL/SQL代码往表EMPLOYEE中插入10,000条记录，其中5,000条记录的列GENDER的值为"F"，另外5,000条记录的列GENDER的值为"M"：

begin

for i in 1..5000 loop

insert into employee values ('F',i);

end loop;

commit;

end;

begin

for i in 5001..10000 loop

insert into employee values ('M',i);

end loop;

commit;

end;

对表EMPLOYEE 及索引收集一下统计信息：

 

analyze table EMPLOYEE compute statistics for table for all columns for all indexes;

执行以下sql

select * from employee where employee_id = 100;

where条件是"employee_id = 100"，即它只对复合B树索引IDX_EMPOLYEE的第二列EMPLOYEE_ID指定了查询条件，但并没有对该索引的前导列GENDER指定任何查询条件。

set autotrace traceonly  

select * from employee where employee_id = 100;

执行计划如下:

从上述显示内容可以看出，Oracle在执行时用上了索引IDX_EMPOLYEE，并且其执行计划走的就是对该索引的索引跳跃式扫描。

这里在没有指定前导列的情况下还能用上述索引，就是因为Oracle帮我们对该索引的前导列的所有distinct值做了遍历。

所谓的对目标索引的所有distinct值做遍历，其实际含义相当于对原目标SQL做等价改写（即把要用的目标索引的所有前导列的distinct 值都加进来）。索引IDX_EMPOLYEE的前导列GENDER的distinct值只有"F"和"M"两个值，所以这里能使用索引 IDX_EMPOLYEE的原因可以简单地理解成是Oracle将范例SQL 9等价改写成了如下形式：

select * from employee where gender = 'F' and employee_id = 100 
union all  
select * from employee where gender = 'M' and employee_id = 100;

Oracle中的索引跳跃式扫描仅仅适用于那些目标索引前导列的distinct值数量较少、后续非前导列的可选择性又非常好的情形，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减。

索引范围扫描（INDEX RANGE SCAN）

索引范围扫描（INDEX RANGE SCAN）适用于所有类型的B树索引，当扫描的对象是唯一性索引时，此时目标SQL的where条件一定是范围查询（谓词条件为 BETWEEN、<、>等）；当扫描的对象是非唯一性索引时，对目标SQL的where条件没有限制（可以是等值查询，也可以是范围查询）。 索引范围扫描的结果可能会返回多条记录，其实这就是索引范围扫描中"范围"二字的本质含义。

根据索引的类型与where限制条件的不同，有4种类型的索引扫描：

索引唯一扫描(index unique scan)

索引范围扫描(index range scan)

索引全扫描(index full scan)

索引快速扫描(index fast full scan)

 

对于unique index（唯一性索引，是带唯一性约束的，普通索引则没有唯一性约束。scott.emp表是主键，带有唯一性约束，同时有索引Rowid）来说，如果where 条件后面出现了<,> ,between ...and...的时候，那么就可能执行index range scan,如果where条件后面是=，那么就会执行index unique scan。

例1：where条件后面是=的情况

SQL> explain plan for select * from scott.emp a where a.empno='7369';

Explained

SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | | | |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | | | |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."EMPNO"=7369)
Note: rule based optimization

15 rows selected
例2：where 条件后面出现了<,> ,between ...and...的情况

SQL> explain plan for select * from scott.emp a where a.empno < '7369';

Explained

SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | | | |
|* 2 | INDEX RANGE SCAN | PK_EMP | | | |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."EMPNO"<7369)
filter("A"."EMPNO"<7369)
Note: rule based optimization

16 rows selected

 

2、对于none unique index来说 如果where 条件后面出现了=、>、<、betweed...and...的时候，就有可能执行index range scan。

例子：

SQL> create table emp1 as select * from scott.emp;

Table created

SQL> create index emp1_name_idx on emp1(ename);

Index created

SQL> explain plan for select * from emp1 a where a.ename = 'SMITH';

Explained

SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | | | |
|* 2 | INDEX RANGE SCAN | EMP1_NAME_IDX | | | |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ENAME"='SMITH')
Note: rule based optimization

15 rows selected

 

3、对于组合索引来说，如果where条件后面出现了组合索引的引导列，那么可能执行index range scan。

例：

 

SQL> create index emp1_name_empno_idx on emp1(empno,ename);

Index created

SQL> explain plan for select * from emp1 a where a.ename = 'SMITH' and empno='33';

Explained

SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | | |
|* 2 | INDEX RANGE SCAN | EMP1_NAME_EMPNO_IDX | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."EMPNO"=33 AND "A"."ENAME"='SMITH')
Note: rule based optimization

15 rows selected

数据库中索引会失效的几种情况（oracle）

创建Oracle 索引的目的是为了避免全表扫描数据，提高查询效率，但是如果sql语句写的不好致使索引失效，反而会影响数据查询效率。以下几种情况就会导致索引失效：

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();}
}