最近学了一些Oracle的基础知识和sql性能优化的一些技巧，虽然每次做项目或日常的时候，DBA 都会对我们写的sql进行sql审核，但是了解相关方面的知识，能使我们更加容易写出最符合当前业务且性能又最高的sql，毕竟作为开发对业务是最了解 的。
首先，既然是对sql的性能优化，了解一下Oracle关于性能优化的相关知识还是有必要的。
一：优化器的相关概念
1：优化器（Optimizer）：
Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer) 来完成的。
2：优化器的优化方式：
优化方式即是指优化器指定执行计划的方式。
如果优化器在分析sql语句时，是根据Oracle内部制定的一些规则来制定执行计划的话，比如当where条件中有一列有索引时就走索引。那么我们就称 这种优化方式是基于规则的优化方式（Rule-Based Optimization,简称为RBO)）
而如果优化器在分析sql语句的时候，根据执行计划的“成本”（这里的成本指的一般是cpu和内存的耗费）而不是Oracle制定的一些规则来制定执行计 划，那么我们就称这种优化方式是基于成本的优化方式（Cost-Based Optimization,简称为CBO）。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息（statistics）。统计信息给出表的 大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行 计划,因些我们应及时更新这些信息。
3:优化模式(Optermizer Mode)
优化模式又是什么概念呢？我自己的理解就是优化器面对的Oracle环境千变万化，我不可能一条sql语句我就选择一种优化方式，在不同的情况下我要使用 不同的优化方式，而优化模式指的就是根据特定的情况选择特定的优化方式的各种模式。
优化模式分四种：
Choose、Rule、All_Rows、First_Rows
Choose（默认情况下的优化模式）：如果当一个表或索引有统计信息，那么我们就走CBO的方式，如果没有，就走RBO。
Rule：在这种模式下，我不管有没有统计信息，我都走RBO的优化方式。
All_Rows（淘宝目前用的优化模式）：在这种模式下，我不管有没有统计信息，我都走CBO的优化方式。
First_Rows：这种模式优化方式的选择跟Choose是一样的，只不过返回的是前n条记录。
在PlSqlDev中的解释计划窗口，我们就可以看到各种优化模式的选择，如下图的“优化器目标”下拉框
二：讲完了优化器的相关概念，我们来了解一下访问table的两种方式
一种是全表扫描,如下图的执行计划：
可以看到由于city字段上没有加索引，所以走了“TABLE ACCESS (FULL) OF ‘SELLER’ (TABLE)”的方式
另外一种是通过rowid来访问表:
由于id是加了索引的，所以走了“ TABLE ACCESS (BY INDEX ROWID) OF ‘SELLER’ (TABLE)”的方式
三：下面，我们来了解一下索引的相关概念（只讲B树索引）。
假设一个seller表，它的加了索引的卖家id的范围是从0到10050，那么就可以得到这个索引的结构如下所式
首先，第一层的节点，也就是根节点，记录了这个根节点下面的每个分支节点的最大值和最小值，比如0-50，51-100。所以假设我在查询 id=69的数据的时候，很快就会发现，69在51-100这个范围内，所以很明显就能找到根节点下面的51-100这个分支节点去继续寻找。而通过相同 的方法，我们就会发现id=69这个数据在51-100这个分支节点下面的59-63这个叶子节点里。最终我们也就定位到了叶子节点的id=69这一行数 据了，从图上可知，索引的存储，是跟这一行的rowid来一起存储的。也就是说如果我们定位到了指定的索引，也就知道了id=69这一行数据的唯一的 rowid，最后再通过rowid在表中查询。
这里需要注意的是，如果sql语句中返回的字段是索引字段的，那么，Oracle就会直接把索引的数据返回（因为我们需要的数据都已经在索引中了），而不 会执行接下了的通过rowid来访问数据库表的操作，而如果返回的字段有些是没有加索引的，则需要继续执行通过rowid来访问表。举例：
执行sql：select id from seller where id =44932322;
从上图我们可以看出，由于这条sql返回的字段id是索引字段，所以他的执行计划中也只是进行了一次索引唯一扫描（ INDEX (UNIQUE SCAN)）
执行sql：select city from seller where id =44932322;
从上图可以看出，由于这条sql返回了的字段city是不加索引的，所以它不仅进行了一次索引唯一扫描，而且还多出了通过rowid查询表（TABLE ACCESS (BY INDEX ROWID)）这一步。
上面提到了索引唯一扫描的概念，这个索引唯一扫描又是什么概念呢？具体概念我就不解释了，查看下面资料http://database.51cto.com/art/200911/162180.htm
四：性能参数的解释：
执行一条sql语句的时候，我们常常需要查看一下这句sql所耗费的资源，如下图所示
这些参数表示的是执行这条sql语句时所产生的各种统计信息,评估一条sql语句的性能好坏，往往就是以这些参数为衡量标准的。
那么，这些统计信息的参数具体又是什么意思呢：
1：recursive calls （递归调用），当你的sql语句引起了其他sql语句的执行的时候，就会产生这个参数，这个参数表示递归调用其他sql语句的次数。比如触发器，这个触发 器会触发其他sql的执行，所以会产生recursive calls，另外，如果一条sql产生了另一条对数据字典进行访问或操作的sql，这个也会产生recursive calls。
比如，执行如下语句，可以看出由于第一次执行这个sql语句，需要做硬解析，需要产生调用数据字典的sql，所以
recursive calls为1，
但是如果再执行同样的语句的话，由于sql已经被放入sql缓存中了，所以无需硬解析，无需访问数据字典，recursive calls参数为0，如图
另外，不仅仅是硬解析，由于数据字典记录了所有对象的结构、数据信息，因此在对象结构、数据发生变化时（如update，delete）都会访问数 据字典，都会产生recursive calls，有兴趣的同学可以试验一下。
2：db block gets ：指的是在查询语句开始执行的那个时间点读取的block数。
3：consistent gets（一致读）：则是为了保证数据的一致性，所以是从回滚段中读取的block数。这句话应该怎么理解呢，就是说如果在你查询的过程中，由于其他进程 对数据块进行了操作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的 一致性。（注1：）
4：physical reads（物理读）：指的是内存中没有该数据块，需要从磁盘中读取，这个参数指的就是从磁盘上读取数据块的数量
这三个参数的关系可以用以下的公式来表明：db block gets+consistent gets=logical read（从内存中读取的block数） + physical read（从磁盘中读取的block数）
一般查看sql性能的好坏，上面几个参数是最重要的，至于其他的参数，可以查看《Performance Tuning Guide 10g Release 2 (10.2)》
了解了上面我讲的一些概念，我想至少你可以知道从哪几方面来优化sql性能了，是通过优化模式的选择，还是通过访问表方式的选择？是走全表扫描，还 是走索引？是选CBO，还是选RBO？虽然这些都是DBA需要考虑的问题，但我想，作为开发多了解一些总是有好处的。
下面就讲讲对sql性能优化的一些小技巧：
一：避免不走索引的情况
sql中以下几种情况是不走索引的，需要注意：
1：不要对加了索引的字段进行函数操作,比如NVL(a,2),a+3=12等;否则无法走索引
2：不要对加了索引的字段进行了以下的操作，否则不走索引
a is Null
a not < 0(注2：)
a !=5(索引只能定位到你想要的值, 而不能定位到你不想要的值.)
a||b
3： 如果是<>用在索引列上面的话，就分两种情况了
<1> 如果是基于成本的话，那一般情况下都是走索引全扫描，其他情况则走索引范围扫描（具体要看统计信息的情况）
<2> 如果是基于规则的话，则走全表扫描。
4： 对于复合索引来说，如果前导字段没有出现在where条件中，也不走索引（比如index(id,name)这个是复合索引，其中id是前导字段，如果 select id from test where name=’mocui’,id没有出现在where条件中，则不走索引）
5：where子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.比如：select name from test where name like ‘%mocui%’; 在这种情况下，ORACLE将使用全表扫描.
6：隐式转换的问题（这个问题我在《写sql时容易忽略的几点》这篇文章也提到过，可惜的是我当时的理解是正好与事实情况相反的，惭愧，经过我的实际验 证，正确的说法应该如下）
看下面四副图：
其中msm_grow_plan表中id和name是加了索引的，可以看出来：
对于类型为number的加索引字段，无论传的是number类型还是varchar类型，都会走索引。
而对于类型为varchar的加索引最淡，如果传的数据室varchar，走索引，如果是number，则全表扫描，且报错。
二：巧用ROWID：
我们知道，通过rowid来定位表中的数据的效率是很高的，所以，如果你想查询一个表中的很多字段，不妨先得到符合要求的rowid，然后通过 rowid来与原先的表进行多表连接，这样的话，效率会大大提高。这方面最明显的例子就是公司分页的标准写法。
select /*+ ordered use_nl(t1 t2) */ id, user_id, age, gmt_create
from (select rid
from (select rid, rownum as rn
from (select rowid as rid
from test
where user_id = :1
order by gmt_create desc)
where rownum <= :2)
where rn >= :3) t1, test t2
where t1.rid = t2.rowid
对于这种分页写法大家应该都很熟悉了吧，主要是先得到需要的rowid，然后根据rowid得到表中的数据，因为通过rowid得到表中的数据比直接就得 到表中所有的数据的效率要高的多。
三: 选择最有效率的表名顺序(只在基于规则的优化器中有效)（From后面表的顺序）
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.因为当ORACLE处理多个表时, NESTED LOOPS（函数嵌套循环连接）（注3:）的方式连接它们.
也就是说如果有两个表，a表和b表，假设其中a表中有100条数据，b表中有10000条数据。
那么，如果以a表为驱动表的话，I/O次数就为，100+（100*10000）
而如果以b表为驱动表的话，I/O次数就为：10000+（10000*100）
很明显，以a表为驱动表的sql的执行效率要高。
事实胜于雄辩，下面就举个简单的例子来说明这个情况：
举例，现有两个表，review_log,seller，其中review_log表中的数据量比seller表中的数据量要小
执行如下语句：
select /*+ RULE */ count(*) from review_log,seller ; （注4：）
得到统计信息如下
而执行如下sql，select /*+ RULE */ count(*) from seller,review_log;得到统计信息
从consistent gets这个参数就可以看出来，以小表review_log为驱动表的sql语句的执行效率要高。
另外，在基于规则的优化器中，Where 后面条件的顺序也是有讲究的，因为 ORACLE是采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
四：CBO下索引不一定比全表扫描效率高
在基于成本的优化器中，是通过对索引的选择性进行判断来决定索引的使用是否能提高效率，如果索引有很高的选择性, 那就是说对于每个不重复的索引键值,只对应数量很少的记录。比如, 表中共有100条记录而其中有80个不重复的索引键值. 这个索引的选择性就是80/100 = 0.8 . 选择性越高, 通过索引键值检索出的记录就越少. 如果索引的选择性很低, 检索数据就需要大量的索引范围查询操作和ROWID 访问表的操作. 也许会比全表扫描的效率更低.Oracle也就有可能会走全表扫描的。
有兴趣的同学可以参看如下资料：http://space.itpub.net/519536/viewspace-612715
注1： 讲到这里，你在查看统计信息的时候可能会发现，在执行select查询的时候，consistent gets的参数会比db block gets多一点，而在执行增删改的时候，db block gets的参数则会比consistents gets多一点，这是什么原因呢？是这样的，当我执行selelct语句的时候，会看有没有前镜像（回滚段中的数据块的前映像），如果有，就读 consistent gets，没有，则读db block gets。而当我执行update/delete/select for update这些dml语句的时候，不管有没有前镜像，都会读db block gets
注2： 需要注意的是，些时候, ORACLE优化器会自动将NOT转化成相对应的关系操作符.
NOT > to <=
NOT >= to <
NOT < to >=
NOT <= to >
注3： 在嵌套循环连接中,Oracle从第一个表读取第一行,然后和第二个表的数据进行对比。所有匹配的记录放在结果 集中,然后Oracle将读取第表中的下一行。按这种方式直至第一个表中的所在行都经过处理。第一个表通常称为外部表,或者驱动表,相应的第二个行源称为 内部表，或者被驱动表。 而在驱动表(就是您正在查找的记录)较小、或者被驱动表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。
注4 ： /*+ RULE */ 这个hint表示强制指定优化模式为RULE。
扫一扫
100
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
