ORACLE如提高SQL语句性能---索引优化培训教材.docVIP

文档编号：P_2008_PX_JC_005 如何提高ORACLE SQL语句性能索引优化培训教材 福州特力惠电子有限公司 文档版本 编写人员 编写日期 主要内容 V1.0 张弘扬 2008-06-16 创建文档 V1.0 郑斌 2008-06-17 审核 V1.1 张弘扬 2008-06-18 增加例子 批准人员： 生效时间：  福州特力惠电子有限公司对本文件资料享受著作权及其它专属权利，未经书面许可，不得将该等文件资料披露予任何第三方，或进行修改后使用。 1.培训目标 让开发人员尽量避免写效率低的SQL语句。 2.对象： 开发人员。 3.对象： 1.索引原理简介 2.限制索引发挥作的情况介绍 3.如何采集耗资源的sql语句 4.提高SQL语句性能的例子 5.利用SQL语句进行分页介绍 3.为什么索引能提高性能 3.1.rowid 表中每一行都有一个rowid，来唯一标识该行的物理地址； 如果让你从1千万份档案中找出10份档案，你怎么找，最快的方法是通过档案号，因为档案号:库房好,柜号,排号,层号,盒号；也就是说档案号唯一标识了一份档案的物理地址，所以可以快速定位这10份档案；记录这些档案号的那本本子就相当于“索引”。 ROWID由18个字符组成，字符可以是A-Z、a-z、0-9、+、/。这18个字符按照OOOOOOFFFBBBBBBRRR的格式分为4组，每一组都代表了特殊的含义，各组内容代表不同的含义如下： ?OOOOOO 这6个字符表示该条记录所在数据对象编号（data object number）。 ?FFF 这3个字符表示该条记录所在的某一表空间中的数据文件编号（datafile number）。 ?BBBBBB 这6个字符表示该条记录所在的数据块编号。 ?RRR 这3个字符是该条记录的编号，同一个表中的所有记录的这3个字符都是不同的。 ? ROWID的重要功能 ?利用ROWID可以快速查找一条记录 ?利用ROWID可以查询表的存储结构 ?利用ROWID唯一标识某一条记录 ? 3.2.B树索引结构: 索引有很多类型，其中最常用的也就是我们使用create index命令创建索引时默认的索引是 B树索引（B代表balance，即平衡树索引，是多叉的） 就是因为索引叶块有键值(key column value)和rowid,以及前一个和后一个树叶块的数据库块地址,而且索引键值是经过排序的,所以可以快速定位表中某行,甚至只扫描索引而不扫描表;范围扫描时也很快,必然where numa=100 and numa=60 4.限制索引发挥作用的情况: 以下是开发人员容易忽视的，在where中将限制使用列上的索引的情况及解决方法 (1)使用,!=,not in,not exist,not like 比如： where name != abc 将无法使用name上的索引 解决方法: where name abc and name abc 备注：这个方法适用的情况下，值’abc’出现的比例很高。假设name的值有100个，假设值等于’abc’的很少，甚至一两个，这种情况下，使用索引范围查找反而是低效的，这种情况下应该适应的是全扫描，从叶子节点左边一直查找到最右边。如果是使用了两个范围查找，则需要两次从根结点路由到叶节点。 当然如果’abc’的重复值很多，这多出的一次路由，就被叶节点少扫描的数量抵消，所以重复的越多，少扫描的越多。从而得到更高的效率。 所以，衡量是否转换的标准是少扫描的叶块是否大于等于一次路由的高度。 (2)使用is null或is not null 比如： Where sal is null将无法使用sal上的索引 备注：各个数据库的实现是不一样的，从理论上讲，如果说，这种情况下也是可以使用索引的，在索引的最左边是存放null值的元组。所以，如果是is null情况下，直接扫描该索引的前面，将is null的元组取出来就可以了。 (3) 对索引列使用函数 比如： Where to_char(saledate)= 2008-01-01 将无法使用saledate上的索引 Where product_id+3=5同样无法使用product列上的索引 故事：(足球队和裁判的故事：某场足球比赛前10分钟，裁判发现其中一对队员的一份颜色和裁判一样，结果裁判居然让11个队员去换衣服；其实解决问题的方法很简单：让裁判去换衣服！ 我们把索引列saledate比作裁判，因为它有很多行，把值2008-01-01比作裁判，因为每次只能传一个值进来；所以以下就是解决方法：即应该把让裁判去换衣服，而不是让那么多队员去换衣服！ 解决方法 a.改变列上的值,而不要对列使用函数；比如 Where sale=