数据库索引优化.ppt

索引学习总结 生产组杨海波 目录 索引简介测试使用的相关方法对单列索引进行的简单测试对多列索引进行的测试相关的数据字典对表进行分析导致索引失效的原因 索引简介 索引是一个单独的 物理的数据库结构 它是某个表中一列或若干列值的集合及相应的指向表中物理的标识这些值的数据页的逻辑指针清单 通过使用索引 可以降低I O次数 提高数据库访问性能 Oracle索引分类 Oracle数据库的索引种类很多 包括B树索引 基于位图的索引 以及基于函数的索引等等 以下只测试都是基于B树索引 B树索引结构与二叉树类似 根据索引码提供对单个行或一系列行的快速访问 通常需要很少的读取就能找到正确的行 在树中 最底层的块称为叶节点 包含每个索引码和指向正在索引的行的行ID 在叶节点上面的中间快 被称为分支块 用来导航结构 B树索引示意图 50 rowIDrowIDrowIDrowID 40 50 30 40 20 30 10 20 80 90 70 80 60 70 50 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 C0011095 anda table name b table name 对表进行分析 如果索引建立的时间比较长 被索引的列的构成已经发生了变化 比如 新增了一些数据 或者删除了一些数据 而系统保存的还是基于索引建立时的统计信息 这样就会造成统计信息与实际信息不匹配 导致本应该使用索引的查询却使用了全表扫描 对表进行分析 建立测试表TBXX11插入1万条记录 对表进行分析 执行查询语句select fromtbxx11wherexxno 1000 对表进行分析 查询聚簇因子再插入9万条记录执行查询语句select fromtbxx11wherexxno 10000 对表进行分析 查询聚簇因子 可以看到统计信息没有变化 对表进行分析 执行分析语句查询聚簇因子 对表进行分析 再执行前面使用全表扫描的查询语句select fromtbxx11wh