数据库索引优化

索引学习总结,生产组杨海波,目录,索引简介测试使用的相关方法对单列索引进行的简单测试对多列索引进行的测试相关的数据字典对表进行分析导致索引失效的原因,索引简介,索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合及相应的指向表中物理的标识这些值的数据页的逻辑指针清单。通过使用索引，可以降低I/O次数，提高数据库访问性能。,Oracle索引分类,Oracle数据库的索引种类很多，包括B树索引、基于位图的索引、以及基于函数的索引等等。以下只测试都是基于B树索引。B树索引结构与二叉树类似，根据索引码提供对单个行或一系列行的快速访问，通常需要很少的读取就能找到正确的行。在树中，最底层的块称为叶节点，包含每个索引码和指向正在索引的行的行ID，在叶节点上面的中间快，被称为分支块，用来导航结构。,B树索引示意图,=50,rowIDrowIDrowIDrowID,40.5030.4020.3010.20,80.9070.8060.7050.60,rowIDrowIDrowIDrowIDrowID,41rowID42rowID43rowID44rowID,建立B树索引,建立简单的B树索引createindexindex_nameontable_name(col1,col2,);修改索引为不可用alterindexindex_nameunusable重建索引alterindexindex_namerebuild删除索引dropindexindex_name,测试相关,使用SQL*PLUS的Autotrace功能显示执行计划和统计信息：setautotraceon打开setautotraceoff关闭setautotracetraceonly不输出执行结果,执行计划,统计信息,测试时间的计算,清空缓存数据,存储在数据缓存区中的数据会导致测试的时间不准确。,清空数据缓存,使用altersystemflushbuffer_cache,简单的测试,建表createtabletbxx01(xxnonumbernotnull,xxagenumber,xxanumber,xxbnumber,xxcnumber,primarykey(xxno),简单的测试,向TBXX01表插入10000笔数据，测试同一个查询语句在使用索引和不使用索引的情况下的执行时间。默认情况下，Oracle系统会在主键上自动建立索引。select*fromtbxx01wherexxno=10000;,简单的测试-1万笔记录,简单的测试-增加到5万笔记录,单列索引测试,测试表记录总数为100万条。,单列索引测试,(无索引)执行select*fromtbxx05where,单列索引测试,（使用索引）执行select*fromtbxx05where.,单列索引测试,单列索引测试小结,从前表可以看出当返回记录的条数占数据记录总数的1/100或者更少，使用索引得到的查询效率的提升还是很明显的，随着返回记录数比例的增加，效率逐渐降低，甚至比不使用索引的时间还要长（不能排除是本机数据库的性能问题）。当查询比例过大时，系统会放弃使用索引，选择全表扫描。,多列索引测试,测试表记录条数为1万，系统自动在XXA，XXB，XXC列上建立一个索引。,多列索引测试,通过查询执行计划，在下面条件下可以使用该索引：查询条件中至少包括三列中的XXA列查询条件中使用and连接小结：可以看出使用多列索引的前提是查询条件中必须包括前导列，当前表的前导列是XXA,多列索引测试,如果前导列的值分布比较少的时候，可以使用后面的列为前导列，本例中就是XXB列。执行查询语句select*fromtbxx09wherexxb=1andxxc=1,多列索引测试,可以看出当前情况下前面查询语句使用了全表扫描。执行语句updatetbxx09setxxa=1分析表execdbms_stats.gather_table_stats(DB,TBXX09),多列索引测试,执行查询语句select*fromtbxx09wherexxb=1andxxc=1,多列索引测试,根据上面的查询计划可以看出系统使用了索引跳跃扫描（INDEXSKIPSCAN）的方式，在这种情况下，XXB列也可以作为引导列使用索引进行查询了。,多列索引测试,建立测试表TBXX08插入1万笔数据在C、D、E三列上分别建立索引IND_C、IND_D、IND_E,多列索引测试,执行查询语句：select*fromtbxx08wherec=42andd=42ande=42;,多列索引测试,执行计划如下,多列索引测试,在C、D、E列上建立索引IND_CDE执行前面的查询,多列索引测试,执行计划如下,多列索引测试,由上可以看出，在使用C、D、E三个列共同作为查询条件的前提下，建立一个三列索引的查询效率要更好一些。,相关数据字典,User_indexes索引的相关信息User_tables表的相关信息User_ind_columns索引名和列名的对应关系,聚簇因子,根据系统的优化策略，当查询笔数小于总数的一定比例时，会趋向于使用索引，而当返回记录数大于一定比例时，会趋向于使用全表扫描。影响这个比例的因素包括索引的聚簇因子。关于聚簇因子的说明：如果这个值和块的数量接近，这个表很好排序。在这种情况下，单个叶块上的索引条目趋向于指向同一个数据块上的列；如果这个值和行的数量接近，那么这个表是随机排序。在这种情况下，同一叶块上的索引条目不太可能指向同一数据块上的列。,聚簇因子,可以在数据字典user_indexes中查询聚簇因子（clustering_factor），在user_tables中查询块数（blocks）和行数（num_rows）。selecta.index_name,b.num_rows,b.blocks,a.clustering_factorfromuser_ind_statisticsa,user_tablesbwhereindex_name=SYS_C0011095anda.table_name=b.table_name,对表进行分析,如果索引建立的时间比较长，被索引的列的构成已经发生了变化，比如，新增了一些数据，或者删除了一些数据，而系统保存的还是基于索引建立时的统计信息，这样就会造成统计信息与实际信息不匹配，导致本应该使用索引的查询却使用了全表扫描。,对表进行分析,建立测试表TBXX11插入1万条记录,对表进行分析,执行查询语句select*fromtbxx11wherexxno1000,对表进行分析,查询聚簇因子再插入9万条记录执行查询语句select*fromtbxx11wherexxno10000,对表进行分析,查询聚簇因子，可以看到统计信息没有变化,对表进行分析,执行分析语句查询聚簇因子,对表进行分析,再执行前面使用全表扫描的查询语句select*fromtbxx11wh