数据库索引优化.ppt

1、索引学习总结,生产组 杨海波,目录,索引简介 测试使用的相关方法 对单列索引进行的简单测试 对多列索引进行的测试 相关的数据字典 对表进行分析 导致索引失效的原因,索引简介,索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合及相应的指向表中物理的标识这些值的数据页的逻辑指针清单。通过使用索引，可以降低I/O次数，提高数据库访问性能。,Oracle索引分类,Oracle数据库的索引种类很多，包括B树索引、基于位图的索引、以及基于函数的索引等等。以下只测试都是基于B树索引。 B树索引结构与二叉树类似，根据索引码提供对单个行或一系列行的快速访问，通常需要很少的读取就能找到正确的行。

2、在树中，最底层的块称为叶节点，包含每个索引码和指向正在索引的行的行ID，在叶节点上面的中间快，被称为分支块，用来导航结构。,B树索引示意图,=50, rowID rowID rowID rowID ,40.50 30.40 20.30 10.20 , 80.90 70.80 60.70 50.60,rowID rowID rowID rowID rowID ,41 rowID 42 rowID 43 rowID 44 rowID ,建立B树索引,建立简单的B树索引 create index index_name on table_name (col1,col2,); 修改索引为不可用 alte

3、r index index_name unusable 重建索引 alter index index_name rebuild 删除索引 drop index index_name,测试相关,使用SQL*PLUS的Autotrace功能 显示执行计划和统计信息： set autotrace on 打开 set autotrace off 关闭 set autotrace traceonly 不输出执行结果,执行计划,统计信息,测试时间的计算,清空缓存数据,存储在数据缓存区中的数据会导致测试的时间不准确。,清空数据缓存,使用alter system flush buffer_cache,简单的测

4、试,建表 create table tbxx01 ( xxno number not null, xxage number, xxa number, xxb number, xxc number, primary key (xxno) ),简单的测试,向TBXX01表插入10000笔数据，测试同一个查询语句在使用索引和不使用索引的情况下的执行时间。 默认情况下，Oracle系统会在主键上自动建立索引。 select * from tbxx01 where xxno=10000;,简单的测试-1万笔记录,简单的测试-增加到5万笔记录,单列索引测试,测试表 记录总数为100万条。,单列索引测试,(

5、无索引)执行select * from tbxx05 where ,单列索引测试,（使用索引）执行select * from tbxx05 where.,单列索引测试,单列索引测试小结,从前表可以看出当返回记录的条数占数据记录总数的1/100或者更少，使用索引得到的查询效率的提升还是很明显的，随着返回记录数比例的增加，效率逐渐降低，甚至比不使用索引的时间还要长（不能排除是本机数据库的性能问题）。当查询比例过大时，系统会放弃使用索引，选择全表扫描。,多列索引测试,测试表 记录条数为1万，系统自动在XXA，XXB，XXC列上建立一个索引。,多列索引测试,通过查询执行计划，在下面条件下可以使用该索引

6、： 查询条件中至少包括三列中的XXA列 查询条件中使用and连接 小结：可以看出使用多列索引的前提是查询条件中必须包括前导列，当前表的前导列是XXA,多列索引测试,如果前导列的值分布比较少的时候，可以使用后面的列为前导列，本例中就是XXB列。 执行查询语句select * from tbxx09 where xxb=1 and xxc=1,多列索引测试,可以看出当前情况下前面查询语句使用了全表扫描。 执行语句update tbxx09 set xxa=1 分析表exec dbms_stats.gather_table_stats(DB,TBXX09),多列索引测试,执行查询语句 select

7、* from tbxx09 where xxb=1 and xxc=1,多列索引测试,根据上面的查询计划可以看出系统使用了 索引跳跃扫描（INDEX SKIPSCAN）的方式，在这种情况下，XXB列也可以作为引导列使用索引进行查询了。,多列索引测试,建立测试表TBXX08 插入1万笔数据 在C、D、E三列上分别建立索引IND_C、IND_D、IND_E,多列索引测试,执行查询语句： select * from tbxx08 where c=42 and d=42 and e=42;,多列索引测试,执行计划如下,多列索引测试,在C、D、E列上建立索引IND_CDE 执行前面的查询,多列索引测试,

8、执行计划如下,多列索引测试,由上可以看出，在使用C、D、E三个列共同作为查询条件的前提下，建立一个三列索引的查询效率要更好一些。,相关数据字典,User_indexes 索引的相关信息 User_tables 表的相关信息 User_ind_columns 索引名和列名的对应关系,聚簇因子,根据系统的优化策略，当查询笔数小于总数的一定比例时，会趋向于使用索引，而当返回记录数大于一定比例时，会趋向于使用全表扫描。影响这个比例的因素包括索引的聚簇因子。关于聚簇因子的说明： 如果这个值和块的数量接近，这个表很好排序。在这种情况下，单个叶块上的索引条目趋向于指向同一个数据块上的列； 如果这个值和行的数

9、量接近，那么这个表是随机排序。在这种情况下，同一叶块上的索引条目不太可能指向同一数据块上的列。,聚簇因子,可以在数据字典user_indexes中查询聚簇因子（clustering_factor），在user_tables中查询块数（blocks）和行数（num_rows）。 select a.index_name,b.num_rows,b.blocks,a.clustering_factor from user_ind_statistics a,user_tables b where index_name=SYS_C0011095 and a.table_name=b.table_name,

10、对表进行分析,如果索引建立的时间比较长，被索引的列的构成已经发生了变化，比如，新增了一些数据，或者删除了一些数据，而系统保存的还是基于索引建立时的统计信息，这样就会造成统计信息与实际信息不匹配，导致本应该使用索引的查询却使用了全表扫描。,对表进行分析,建立测试表TBXX11 插入1万条记录,对表进行分析,执行查询语句 select * from tbxx11 where xxno1000,对表进行分析,查询聚簇因子 再插入9万条记录 执行查询语句 select * from tbxx11 where xxno10000,对表进行分析,查询聚簇因子，可以看到统计信息没有变化,对表进行分析,执行分析语句 查询聚簇因子,对表进行分析,再执行前面使用全表扫描的查询语句 select * from tbxx11 where xxno10000,索引在什么情况下