数据库索引优化.ppt

《数据库索引优化.ppt》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

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

2、层的块称为叶节点，包含每个索引码和指向正在索引的行的行ID，在叶节点上面的中间快，被称为分支块，用来导航结构。B树索引示意图<50>=50……rowIDrowIDrowIDrowID……<40..50<30..40<20..30<10..20…………<80..90<70..80<60..70<50..60rowIDrowIDrowIDrowIDrowID……41rowID42rowID43rowID44rowID……建立B树索引建立简单的B树索引createindexindex_nameontable_name(col1,col2,…);修改索引为不可用alterindexi

3、ndex_nameunusable重建索引alterindexindex_namerebuild删除索引dropindexindex_name测试相关使用SQL*PLUS的Autotrace功能显示执行计划和统计信息：setautotraceon打开setautotraceoff关闭setautotracetraceonly不输出执行结果执行计划统计信息测试时间的计算清空缓存数据存储在数据缓存区中的数据会导致测试的时间不准确。清空数据缓存使用altersystemflushbuffer_cache简单的测试建表createtabletbxx01(xxnonumbernotnu

4、ll,xxagenumber,xxanumber,xxbnumber,xxcnumber,primarykey(xxno))简单的测试向TBXX01表插入10000笔数据，测试同一个查询语句在使用索引和不使用索引的情况下的执行时间。默认情况下，Oracle系统会在主键上自动建立索引。select*fromtbxx01wherexxno=10000;简单的测试-----1万笔记录简单的测试-----增加到5万笔记录单列索引测试测试表记录总数为100万条。单列索引测试(无索引)执行select*fromtbxx05where…查询条件执行时间（毫秒）xxnum<=1001312x

5、xnum<=10001328xxnum<=100001375xxnum<=1000002234xxnum<=5000005750xxnum<=8000008328xxnum<=100000010187Select*fromtbxx0510938单列索引测试（使用索引）执行select*fromtbxx05where..查询条件执行时间（毫秒）xxnum<=10078xxnum<=1000125xxnum<=10000344xxnum<=1000001969xxnum<=5000008125xxnum<=8000008953xxnum<=100000010172Select*f

6、romtbxx0510110返回记录数占总数的比例无索引的执行时间（毫秒）使用索引的执行时间（毫秒）1/100001312（全表扫描）781/10001328（全表扫描）1251/1001375（全表扫描）3441/102234（全表扫描）19691/25750（全表扫描）81254/58328（全表扫描）8953（全表扫描）1/110187（全表扫描）10172（全表扫描）1/110938（全表扫描）10110（全表扫描）单列索引测试单列索引测试小结从前表可以看出当返回记录的条数占数据记录总数的1/100或者更少，使用索引得到的查询效率的提升还是很明显的，随着返回记录数比例

7、的增加，效率逐渐降低，甚至比不使用索引的时间还要长（不能排除是本机数据库的性能问题）。当查询比例过大时，系统会放弃使用索引，选择全表扫描。多列索引测试测试表记录条数为1万，系统自动在XXA，XXB，XXC列上建立一个索引。多列索引测试通过查询执行计划，在下面条件下可以使用该索引：查询条件中至少包括三列中的XXA列查询条件中使用and连接小结：可以看出使用多列索引的前提是查询条件中必须包括前导列，当前表的前导列是XXA多列索引测试如果前导列的值分布比较少的时候，可以使用后面的列为前导列，本例中就是XXB列