索引创建规则及优化

示例：

drop table if exists emp;

create table emp (eid int  CLUSTER primary key identity(1,1),ename varchar(200),age int,hiredate date,sal int,deptno int);

declare i int;

begin

for i in 1..50000 loop

insert into emp (ename,age,hiredate,sal,deptno)

select dbms_random.string('2',trunc(dbms_random.value(2,4))),

trunc(dbms_random.value(1,100)),

 ADD_DAYS(sysdate(),dbms_random.value(-10000,-10)),

 trunc(dbms_random.value(1,10000)),

trunc(dbms_random.value(1,6)) from dual;

end loop;

if  mod(i,5000)=0 then 

commit;

end if;

end;

commit;

选择性：基数（某个列不同值的数量）与总行数的比值再乘以100%就是某个列的选择性。一般选择性大于20%就可以考虑建索引了。

--根据下面可以查询ENAME字段的基数和选择性，

select dnum 基数,tnum 总数,round(dnum/tnum*100,2) 选择性 from

(select count(DISTINCT(ename)) dnum ,count(*) tnum from emp ) t;

-- 如果值为0，达梦整数的除法运算需要修改参数CALC_AS_DECIMAL为1，重启数据库生效

统计信息：主要是描述数据库中表和索引的大小数以及数据分布状况等的一类信息，优化器依赖统计信息来评估选择最佳的执行计划

--上面所示，ename选择性好，在该字段创建一个索引进行测试

create index idx_emp_name on emp(ename);

-- 从EMP表中找出Ename为SKJ的数据

 select * from emp where ename= 'SKJ';

查询该sql的执行计划

EXPLAIN select * from emp where ename='SKJ';

 --从上面可以看到，Ename 为SKJ的结果只有4条数据，而执行计划里面的结果集是2500

优化器依赖统计信息来评估选择率(符合条件的记录数与原总记录数的比例)。如果没有统计信息可用，则对于列名=<常量>的谓词，选择率固定为SEL_RATE_EQU，默认值为2.5%,其他谓词为SEL_RATE_SINGLE，默认值5%

收集统计信息

call DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','EMP',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

统计信息收集之后，查看执行计划

回表:先通过普通索引扫描出数据所在的行，再通过的rowid或聚集索引找出表中的数据，回表一般是单块读，回表次数太多会严重影响SQL性能

 --查询ename 开头是A的年龄信息

 select ename,age from emp where ename like 'A%' ;

执行计划如下

EXPLAIN select ename,AGE from emp where ENAME like 'A%';

覆盖索引：索引中包含了查询中的所有字段，是为了避免回表从而降低查询耗时的一种使用索引的方法。

--前面ename的选择性是26.21，而age的选择性是0.2，创建组合索引时将过滤条件好的放前面

--在emp表中建立ename，age组合索引

create index idx_ename_age on EMP(ename,age);

--可以看到执行计划已经没了BLKUP2的操作符了，已经消除了回表 

最左侧匹配原则：使用联合索引进行查询时，会优先使用最左边的列进行匹配，然后再依次向右匹配。遇到范围查询就会停止

select  ename,age from emp where ename like 'A%' and age=20 ;

如上所示，因为ename字段条件是like，后面的字段age就不会使用到创建的组合索引

索引下推：索引断裂走不下去后，不会立即回表，还会向下推一步再继续比较其它索引字段。

--通过添加hint采用索引下推之后，执行计划如下

select /*+ENABLE_INDEX_FILTER(1)*/ ename,age from emp where ename like 'A%' and age=20 ;