数据库索引优化.pptx

《数据库索引优化.pptx》由会员分享，可在线阅读，更多相关《数据库索引优化.pptx（45页珍藏版）》请在第壹文秘上搜索。

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

2、索引的行的行ID，在叶节点上面的中间快，被称为分支块，用来导航结构。B树索引示意图=50 71 rowID72 rowID73 rowID74 rowID 40.5030.4020.3010.20 80.9070.8060.7050.6021 rowID22 rowID23 rowID24 rowID25 rowID 41 rowID42 rowID43 rowID44 rowID 建立B树索引建立简单的B树索引 create index index_name on table_name (col1,col2,);修改索引为不可用 alter index index_name unusable

3、 重建索引 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简单的测试建表 create table tbxx01 ( xxno number

4、 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万条。单列索引测试(无索引)执行select * from tbxx05 where 查询条件查询条件执行时间（毫秒）

5、执行时间（毫秒）xxnum=100 1312xxnum=10001328xxnum=10000 1375xxnum=1000002234xxnum=5000005750 xxnum=8000008328xxnum=1000000 10187Select * from tbxx0510938单列索引测试（使用索引）执行select * from tbxx05 where.查询条件查询条件执行时间（毫秒）执行时间（毫秒）xxnum=100 78xxnum=1000125xxnum=10000 344xxnum=1000001969xxnum=5000008125xxnum=8000008953xx

6、num=1000000 10172Select * from tbxx0510110返回记录数占总数的比例返回记录数占总数的比例无索引的执行时间（毫秒）无索引的执行时间（毫秒）使用索引的执行时间（毫使用索引的执行时间（毫秒）秒）1/100001312（全表扫描）781/10001328（全表扫描）1251/1001375（全表扫描）3441/102234（全表扫描）19691/25750（全表扫描）81254/58328（全表扫描）8953（全表扫描）1/110187（全表扫描）10172（全表扫描）1/110938（全表扫描）10110（全表扫描）单列索引测试单列索引测试小结从前表可以看出当

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

8、本例中就是XXB列。执行查询语句select * from tbxx09 where xxb=1 and xxc=1多列索引测试 可以看出当前情况下前面查询语句使用了全表扫描。 执行语句update tbxx09 set xxa=1分析表exec dbms_stats.gather_table_stats(DB,TBXX09)多列索引测试执行查询语句 select * from tbxx09 where xxb=1 and xxc=1多列索引测试根据上面的查询计划可以看出系统使用了 索引跳跃扫描（INDEX SKIPSCAN）的方式，在这种情况下，XXB列也可以作为引导列使用索引进行查询了。多

9、列索引测试建立测试表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执行前面的查询多列索引测试执行计划如下多列索引测试由上可以看出，在使用C、D、E三个列共同作为查询条件的前提下，建立一个三列索引的查询效率要更好一些。相关数据字典User_indexes 索引的相关信息User_tables 表的相关信息User_ind_columns 索引名和列名的对

10、应关系聚簇因子根据系统的优化策略，当查询笔数小于总数的一定比例时，会趋向于使用索引，而当返回记录数大于一定比例时，会趋向于使用全表扫描。影响这个比例的因素包括索引的聚簇因子。关于聚簇因子的说明：如果这个值和块的数量接近，这个表很好排序。在这种情况下，单个叶块上的索引条目趋向于指向同一个数据块上的列；如果这个值和行的数量接近，那么这个表是随机排序。在这种情况下，同一叶块上的索引条目不太可能指向同一数据块上的列。聚簇因子可以在数据字典user_indexes中查询聚簇因子（clustering_factor），在user_tables中查询块数（blocks）和行数（num_rows）。selec

11、t 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对表进行分析如果索引建立的时间比较长，被索引的列的构成已经发生了变化，比如，新增了一些数据，或者删除了一些数据，而系统保存的还是基于索引建立时的统计信息，这样就会造成统计信息与实际信息不匹配，导致本应该使用索引的查询却使用了全表扫描。对表进行分析建立测试表TBXX11插入1万条记录对表进行

12、分析执行查询语句select * from tbxx11 where xxno1000对表进行分析查询聚簇因子再插入9万条记录执行查询语句select * from tbxx11 where xxno10000对表进行分析查询聚簇因子，可以看到统计信息没有变化对表进行分析执行分析语句查询聚簇因子对表进行分析再执行前面使用全表扫描的查询语句select * from tbxx11 where xxno10000索引在什么情况下失效在查询条件中使用不等于，IS NULL，IS NOT NULL类型不匹配，查询的类型与该列的数据类型不匹配。在索引列上使用函数，函数索引除外。没有对索引及时分析，统计信息过时。经过系统的评估，如果使用索引会降低速度where条件没有使用索引的主要边界