优化规范
1.1 限制输出原则
在OLTP系统中,原则上都是小事务、小查询,应当限制输出的行数,使执行计划经过索引,保证响应速度。而且,对于用户来说,返回过多的行是没有意义的。
规范1:结果集不能超过500行
可以通过以下方法限制输出行数:
对于多个可选输入条件的查询,要至少有一个强条件,而且这个条件字段应该有索引。
对于时间范围的查询,要预估结果集,从而确定最长时间范围。
例如:每周录入保单数据1000条,则需要限制查询时间范围为3天,如果3天内没有 查到结果,用户可以自行向前推3天继续查询。
不要使用like作为查询条件。因为like的条件可能会变化很大,如果使用绑定变量的话,会共享执行计划,造成前后执行的cost相差巨大。
例如:
查询条件是 policyno like :B1;
第一次查询,:B1的值是'6204130080120150001823',执行计划会走policyno索引,
第二次查询,:B1的值是'62041300801%',执行计划还会去走policyno索引,这时候会 有几万的保单被查出来,执行效率极差。
可以使用rownum <= 500来限制输出,但不能嵌套使用
例如:
select * from (
select policyno, suminsured, sumgrosspremium
from nbz_policy_main
where inputdate between :B1 and :B2
order by policyno )
where rownum <= 500;
上面的查询虽然限制了输出500行,但参与排序的可能远远超过500行,这条sql的效率同样会很低,应该修改为下面的写法:
select policyno, suminsured, sumgrosspremium
from nbz_policy_main
where inputdate between :B1 and :B2
and rownum <= 500
order by policyno;
需注意:上面两种写法的结果是不同的,如果使用上面一种写法,需限制子查询中参与排序的条数,尽量不超过500行。
规范2:不能使用SELECT *,要根据实际需要,列举所有字段
这样可以减少内存消耗,以及网络传输的数据量。
而且,会减少bug的发生概率。因为表结构经常会变更,如果增加了字段,select *获取的字段数量也会增加,如果接收数据的结构没有变化的话,可能会有报错。
1.2 软解析原则
SQL第一次执行时,会进行硬解析,硬解析就是生成执行计划的过程,这个动作会消耗大量的CPU、内存资源,我们应该尽量避免硬解析。
规范3:所有业务查询都要使用绑定变量
如果sql文本相同,Oralce会尽量重用已有的执行计划,保证sql文本相同的方法就是使用绑定变量。
规范4:要控制SQL中变量的数量,尽量不超过14个
所谓变量,是每次查询都会不同,例如policyno。
对于一些不会改变的值,则无需使用绑定变量,例如rownum < 500,所有查询中这个值都是500,则不需要改成绑定变量。
11g中有自适应游标的特性,对于收集直方图的列,oracle会跟踪sql的执行情况,根据实际的cost调整执行计划。这个特性要求变量数量不超过14个。
规范5:程序中变量长度定义要与数据库中字段长度相同
Oracle中,即使使用绑定变量,也不一定能重用执行计划,变量的长度变化是不能重用的主要原因。对于varchar2类型的变量,长度范围如下:
字符串长度从1~32
字符串长度从33~128
字符串长度从129~2000
字符串长度从2001~
如果字符串长度跨范围,oracle需要重新硬解析,生成新的执行计划。
所以,要尽量固定变量的长度,代码中变量长度与数据库保持一致,减少不必要的硬解析。
1.3 使用索引原则
OLTP系统中,业务表基本都是大表,为了保证效率,所有的查询尽量用到索引,要杜绝大表全表扫描的发生。
规范6:数据类型要准确,与数据库保持一致
数据类型不一致会造成隐式转换,可能无法用到索引,例如:
select /*djtest*/ * from nbz_policy_main t where t.policyno = 0000241364;
policyno是varchar2类型,但等号后面是数字类型,执行计划会自动做隐式转换,走全表扫描:
规范7:不要在WHERE子句中使用函数或表达式
where条件中,等号左边只允许有字段名,不允许有函数或表达式,这会导致索引无法用到。
如果一定要有函数,需通知DBA,考虑增加函数索引。
规范8:不要过多的创建索引
索引可以改善查询性能,但索引的维护成本也很高,特别是经常做DML的字段,尽量不要创建索引。
规范9:对用户输入的数据要做校验,杜绝垃圾数据进入数据库
对客户录入的数据,要先进行校验,符合规则的数据才能入库。垃圾数据会导致数据库生成错误的执行计划,严重影响性能。
而且,垃圾数据也不利于后期的数据分析,客户数据管理。
1.4 优化sql写法
规范10:尽量避免复杂查询,表连接的数量不超过5个
Oracle在硬解析时有时间限制,如果sql写法太复杂、连接的表太多,优化器无法尝试所有的连接方法,导致无法找到最优的执行计划,所以复杂sql的效率往往不高。要根据业务逻辑,尽量简化sql,以获取最优性能。
对于超过5张表的连接,应使用hint /*+ leading*/ 来指定最优的连接顺序。
规范11:尽量避免排序
排序操作会消耗大量的CPU资源,特别是大量数据的排序,可能无法在内存完成,大大降低性能。应尽量避免排序操作,不要使用 distinct / order by / union等。
1.1 限制输出原则
在OLTP系统中,原则上都是小事务、小查询,应当限制输出的行数,使执行计划经过索引,保证响应速度。而且,对于用户来说,返回过多的行是没有意义的。
规范1:结果集不能超过500行
可以通过以下方法限制输出行数:
对于多个可选输入条件的查询,要至少有一个强条件,而且这个条件字段应该有索引。
对于时间范围的查询,要预估结果集,从而确定最长时间范围。
例如:每周录入保单数据1000条,则需要限制查询时间范围为3天,如果3天内没有 查到结果,用户可以自行向前推3天继续查询。
不要使用like作为查询条件。因为like的条件可能会变化很大,如果使用绑定变量的话,会共享执行计划,造成前后执行的cost相差巨大。
例如:
查询条件是 policyno like :B1;
第一次查询,:B1的值是'6204130080120150001823',执行计划会走policyno索引,
第二次查询,:B1的值是'62041300801%',执行计划还会去走policyno索引,这时候会 有几万的保单被查出来,执行效率极差。
可以使用rownum <= 500来限制输出,但不能嵌套使用
例如:
select * from (
select policyno, suminsured, sumgrosspremium
from nbz_policy_main
where inputdate between :B1 and :B2
order by policyno )
where rownum <= 500;
上面的查询虽然限制了输出500行,但参与排序的可能远远超过500行,这条sql的效率同样会很低,应该修改为下面的写法:
select policyno, suminsured, sumgrosspremium
from nbz_policy_main
where inputdate between :B1 and :B2
and rownum <= 500
order by policyno;
需注意:上面两种写法的结果是不同的,如果使用上面一种写法,需限制子查询中参与排序的条数,尽量不超过500行。
规范2:不能使用SELECT *,要根据实际需要,列举所有字段
这样可以减少内存消耗,以及网络传输的数据量。
而且,会减少bug的发生概率。因为表结构经常会变更,如果增加了字段,select *获取的字段数量也会增加,如果接收数据的结构没有变化的话,可能会有报错。
1.2 软解析原则
SQL第一次执行时,会进行硬解析,硬解析就是生成执行计划的过程,这个动作会消耗大量的CPU、内存资源,我们应该尽量避免硬解析。
规范3:所有业务查询都要使用绑定变量
如果sql文本相同,Oralce会尽量重用已有的执行计划,保证sql文本相同的方法就是使用绑定变量。
规范4:要控制SQL中变量的数量,尽量不超过14个
所谓变量,是每次查询都会不同,例如policyno。
对于一些不会改变的值,则无需使用绑定变量,例如rownum < 500,所有查询中这个值都是500,则不需要改成绑定变量。
11g中有自适应游标的特性,对于收集直方图的列,oracle会跟踪sql的执行情况,根据实际的cost调整执行计划。这个特性要求变量数量不超过14个。
规范5:程序中变量长度定义要与数据库中字段长度相同
Oracle中,即使使用绑定变量,也不一定能重用执行计划,变量的长度变化是不能重用的主要原因。对于varchar2类型的变量,长度范围如下:
字符串长度从1~32
字符串长度从33~128
字符串长度从129~2000
字符串长度从2001~
如果字符串长度跨范围,oracle需要重新硬解析,生成新的执行计划。
所以,要尽量固定变量的长度,代码中变量长度与数据库保持一致,减少不必要的硬解析。
1.3 使用索引原则
OLTP系统中,业务表基本都是大表,为了保证效率,所有的查询尽量用到索引,要杜绝大表全表扫描的发生。
规范6:数据类型要准确,与数据库保持一致
数据类型不一致会造成隐式转换,可能无法用到索引,例如:
select /*djtest*/ * from nbz_policy_main t where t.policyno = 0000241364;
policyno是varchar2类型,但等号后面是数字类型,执行计划会自动做隐式转换,走全表扫描:
规范7:不要在WHERE子句中使用函数或表达式
where条件中,等号左边只允许有字段名,不允许有函数或表达式,这会导致索引无法用到。
如果一定要有函数,需通知DBA,考虑增加函数索引。
规范8:不要过多的创建索引
索引可以改善查询性能,但索引的维护成本也很高,特别是经常做DML的字段,尽量不要创建索引。
规范9:对用户输入的数据要做校验,杜绝垃圾数据进入数据库
对客户录入的数据,要先进行校验,符合规则的数据才能入库。垃圾数据会导致数据库生成错误的执行计划,严重影响性能。
而且,垃圾数据也不利于后期的数据分析,客户数据管理。
1.4 优化sql写法
规范10:尽量避免复杂查询,表连接的数量不超过5个
Oracle在硬解析时有时间限制,如果sql写法太复杂、连接的表太多,优化器无法尝试所有的连接方法,导致无法找到最优的执行计划,所以复杂sql的效率往往不高。要根据业务逻辑,尽量简化sql,以获取最优性能。
对于超过5张表的连接,应使用hint /*+ leading*/ 来指定最优的连接顺序。
规范11:尽量避免排序
排序操作会消耗大量的CPU资源,特别是大量数据的排序,可能无法在内存完成,大大降低性能。应尽量避免排序操作,不要使用 distinct / order by / union等。