达梦索引测试

达梦索引测试

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可减少IO，快速访问数据库表中的特定信息。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

建设原则:

1索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询，并且检索行数小于总表行数的5%。则应该考虑。（返回数据量占10%以上 建议使用全表扫描，使用多块读操作，提升吞吐量）。

2 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序（先等值再其他，返回数据量，使用频率）

3 对于小型的表，没必要建立索引，优化不明显，增加开销

4 随着数据的变化，索引的效率会下降，因此应定期重建索引

alter index index_name rebuild

CSCN ：基础全表扫描(a)，从头到尾，全部扫描
SSCN ：二级索引扫描(b), 从头到尾，全部扫描
SSEK ：二级索引范围扫描(b) ，通过键值精准定位到范围或者单值
CSEK ：聚簇索引范围扫描(c) ，通过键值精准定位到范围或者单值
BLKUP ：根据二级索引的ROWID 回原表中取出全部数据(b + a)

创建测试表

create table idx (id int,name varchar(50),count int)

构造10万条测试数据

CREATE OR REPLACE PROCEDURE proc_index AS

begin

for i in 1..100000 loop

INSERT INTO  idx (id,name,count)  select i,CHR(MOD(i,27)+65),convert(int,100*rand()) from dual;

end loop;

commit;

end;

调用存储过程，可以看到idx有10万条数据，name为A的有3千多条

call PROC_INDEX

 

没建索引之前，查看执行计划

如下示例，执行计划走了全表扫描CSCN

 

 创建索引，查看执行计划

create index index_t on idx (name)

 explain select /*+INDEX(idx, index_t) */ *  from idx where name='A'

下图可以看到，执行计划走了二级索引范围扫描SSEK

 

创建聚集索引

create cluster index index_t2 on idx(id);

explain select * from idx where id >1000 and id <2000

如下所示，执行计划走了聚簇索引范围扫描CSEK

 

创建组合索引

create index index_t3 on idx(name,count)

explain select  /*+INDEX(idx, index_t3) */ * from idx where  name='A' and count=50

 

删除组合索引，查看执行计划

drop index index_t3

 

下列情况不走索引

索引列上使用函数

 

 索引列上进行计算

 

索引列上使用IS NULL和IS NOT NULL

 

索引列上做了隐式转换（经测试，实际上走了索引）

 

返回结果过多

1 聚集索引创建之后执行计划都能走索引

 

2 经测试，除了聚集索引，其他索引如不加hint，执行计划还是走的全表扫描

create index index_t5 on idx(count);

3 加hint之后，执行计划虽走了索引，因返回值过多，开销太大，优化不明显

 

查看索引信息

select table_name,index_name from dba_indexes where table_name=表名