SQL优化规范

优化规范
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等。
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
8.用执计划分析SQL性能      EXPLAIN PLAN是一个很好的分析SQL语句的工具,它可以在不执SQL的情况下分析语句      通过分析,我们就可以知道ORACLE是怎样连接表,使用什么方式扫描表(索引扫描或全表扫描),以及使用到的索引名称      按照从里到外,从上到下的次序解读分析的结果      EXPLAIN PLAN的分析结果是用缩进的格式排列的,最内部的操作将最先被解读,如果两个操作处于同一层中,带有最小操作号的将首先被执      目前许多第三方的工具如PLSQL Developer和TOAD等都提供了极其方便的EXPLAIN PLAN工具      PG需要将自己添加的查询SQL文记入log,然后在EXPLAIN PLAN中进分析,尽量减少全表扫描      ORACLE SQL性能优化系列      1.选择最有效率的表名顺序(只在基于规则的优化器中有效)      ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表driving table)将被最先处理      在FROM子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表      当ORACLE处理多个表时,会运用排序及合并的方式连接它们      首先,扫描第一个表(FROM子句中最后的那个表)并对记录进排序;      然后扫描第二个表(FROM子句中最后第二个表);      最后将所有从第二个表中检索出的记录与第一个表中合适记录进合并      例如:      表 TAB1 16,384 条记录      表 TAB2 5 条记录      选择TAB2作为基础表 (最好的方法)      select count(*) from tab1,tab2 执时间0.96秒      选择TAB2作为基础表 (不佳的方法)      select count(*) from tab2,tab1 执时间26.09秒      如果有3个以上的表连接查询,那就需要选择交叉表(intersection table)作为基础表,交叉表是指那个被其他表所引用的表      例如:   EMP表描述了LOCATION表和CATEGORY表的交集   SELECT *   FROM LOCATION L,   CATEGORY C,   EMP E   WHERE E.EMP_NO BETWEEN 1000 AND 2000   AND E.CAT_NO = C.CAT_NO   AND E.LOCN = L.LOCN      将比下列SQL更有效率   SELECT *   FROM EMP E ,   LOCATION L ,   CATEGORY C   WHERE E.CAT_NO = C.CAT_NO   AND E.LOCN = L.LOCN   AND E.EMP_NO BETWEEN 1000 AND 2000      2.WHERE子句中的连接顺序      ORACLE采用自下而上的顺序解析WHERE子句      根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾      例如:   (低效,执时间156.3秒)   SELECT *   FROM EMP E   WHERE SAL > 50000   AND JOB = 'MANAGER'   AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);      (高效,执时间10.6秒)   SELECT *   FROM EMP E   WHERE 25 50000   AND JOB = 'MANAGER';      3.SELECT子句中避免使用'*'      当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用'*'是一个方便的方法,不幸的是,这是一个非常低效的方法      实际上,ORACLE在解析的过程中,会将'*'依次转换成所有的列名      这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间      4.减少访问数据库的次数
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值