Oracle数据库开发优化总结

Oracle数据库开发优化总结

 

虽然现在硬件的成本已经很低了，使用硬件性能可以从某种程度上屏蔽因为软件性能问题带来的问题，但是这并不能说明软件的性能已经无关紧要了。

 

数据库在企业应用和互联网应用是至关重要的，从很大程度上将此类应用是围绕数据展开的，可以说数据出于一个核心的位置，因此提升基于数据库的开发的性能对于系统的服务质量也是至关重要的。

 

我总结了一下之前在开发数据库系统时从设计到维护阶段一些对数据库的优化方法，以此来提升数据库的性能。

 

1.合理的分区或者分表
这个需要在设计阶段来完成，根据业务特点，以及对数据量的估计，采用分表或者分区的方式，采用业务上固定之后就不变或者不易变动的属性来作为分区或者分表的条件。有时需要多个属性混合来作为分区或者分表的条件。使之在进行数据操作是控制在同一个分区或者分表内为最佳。
建立分区时要主意：如果表a采用city_code作为分区属性，如果有update city_code='x' from table_a where city_code='y'的情况发生，则说明属性city_code尽量不要作为分区属性。

具体怎么分区，怎么分表是一个循序渐进的过程，并不是一蹴而就的。因为这需要根据数据量的等级作为一个衡量标准的。并不是分的区或者表越多越好。如果存在一个旧的系统时，相对容易一些，因为这样可以比较容易的计算出数据的数量级，也比较容易找到业务的固定属性。
系统中的分区或者分表也不是一成不变的，系统建成运行之后，随着业务的开展，数据量会逐渐增加，这样原有的设计会逐渐老旧，所以要随着数据量的增加，修改原有的分区或者分表。

 

2.合理的索引，对应合理的where子句
在表上建立合理合量的索引，会增加数据检索的速度。建立索引时索引字段的顺序，要字段可变化的几率从小到大排列为佳。
原因很简历，就像我们在建立一个树状数据结构时我们希望越靠近root的节点的level上的节点可变性越小越好。
建立索引之后，要正确的书写where子句，这样建立的索引才能起到作用，否则还是要进行全表扫描。

如果有分区，要建立分区索引，这样建立的分区才能起到最大化的作用。

另外索引并不是越多越好，索引建立之后，会给优化select语句，但是会增加insert语句的负担，因此如果在insert语句频繁的表上建立索引要格外慎重，我们要评估一下，该表上insert多还是select多。如果update中会改变索引中涵盖的字段也要格外的小心。

 

3.减少多表关联，以多重循环代替
做信息管理系统时经常有这样的场景，使用一个或者多个参数配置表去过滤一个实时数据表，进而获得我们想要的数据，我们首先想到的方法就是多表关联，可以很简单的获得我们想要的数据。

参数配置的数量级一般来讲不会很大，但是当实时数据表的数据量巨大时，采用多表关联选取数据的方法，是会带来灾难的，因为：
i.多表关联，将会使得待处理的数据(DBMS待处理的数据)呈现级数级的增加，因为多表关联是一个笛卡儿乘积的关系。
ii.多表关联不容易控制索引的执行，使得索引形同虚设。

因此在遇到这样的场景时避免使用多表关联，使用多重循环的形式来代替。比如之间我遇到的一个case:配置表a,实时数据表b，我使用一重循环来代替，循环提取参数表a中每一条配置数据，使其作为已知条件，再到表b中提取数据。

仅此一条：提取数据的时间从几秒甚至十几秒，优化到零点几秒。（参数表a的数量级为K，实时数据表b的数量级为10W）

4.减少order by的使用
order by是一个选取排序的过程，当结果集数据量巨大时，排序是一个非常耗时的过程，所以尽量减少它的使用，或者使用其他替代排序的方案，分享我之前做的一个case：在订单处理系统中，订单是有优先级的，业务要求优先处理优先级高的订单，因为我们之前的方案是按照业务要求选取数据之后，再按照优先级进行order by ，然后按照order by之后的结果进行提取处理，这样当数据达到10W以上级别时，整个order by需要几十秒的时间，非常缓慢。
后来对这个地方进行了优化，找到了替代order by的方案（业务需求不能变），我们发现订单的优先级在一个可控的有限区间内，因此我们使用了，如下方式：
 v_max_emergency := 20;
 v_min_emergency := 20;

 LOOP
  IF (v_min_emergency < 0) THEN
   EXIT;
  END IF;
  v_max_emergency := v_min_emergency;
  v_min_emergency := v_min_emergency - 2;
  
  
  SELECT  ...
  FROM ... a
  WHERE ...
  AND a.priority > v_min_emergency
  AND a.priority <= v_max_emergency;
  
  ...
  
 END LOOP;
这样就避免了排序的过程，进而使得选取符合业务特点的工单控制在了零点几秒的范围内。

其他诸如group by的子句当出现在大表上时也尽量寻找可替换的方案执行。

 

5.API访问table时，使用绑定SQL以减少SQL的分析时间。
比如说在C/C++编程使用OCI访问Oracle数据库时，将OCIDefineByxxx之前的操作放在初始化阶段，到真正提取数据时只要执行OCIStmtExecute即可，这样可以减少SQL的分析时间。这一条尤其在频繁更换sql语句时，更需要注意。

 

6.定时trunc table，减少table的碎片
对于频繁有insert、delete操作的表时需要注意，频繁的insert和delete的操作会增加碎片的数量，因而会加重操作的时间，它的原理同磁盘碎片一样，因此我们需要在系统运维阶段定期的trunc table，减少table的碎片。
如何进行trunc table，这又是一个问题，因为trunc会影响业务的执行，因此可以需要停止系统的运行，但是对于一些实时或者准实时系统比如客服系统、电话银行缴费系统、互联网应用系统来讲这是不允许的，如何解决这个矛盾呢？
我们可以建立合理的分区或者分表，按照分区或者分表trunc，我们之前采用的一个方法时，分区或者分表条件中有一个日期单双号的条件，比如我们在3号trunc双号的分区或者分表，在4号trunc单号的分区或者分表，这样不影响业务又能达到目的。

 

7.在sql语句中减少内置函数的使用
原因是：a增加内置函数可能会加重运算时间；b当内置函数出现在where子句是可能会索引不受控制，因此保险的做法是使用替换方法来减少内置函数的使用

 

8.不得已有比较耗时的sql或者procodure需要执行时，比如必须要在大表上做count、sum、order by、group by等之类的操作，或者即使即使已经优化但是由于操作的数据量巨大，也会对系统造成很大的压力，进而可能会影响到其他业务的执行或者是执行的服务质量，对于这类的操作将其放在业务压力较小的时间段执行，因为一般来讲此类操作的业务优先度会比较低一些。
例如在Unix/Linux下设定crontab，在凌晨的时间段内执行，因为这个时间段内业务量较少，即使执行sql会比较耗费资源，也不会对业务产生多大的影响。

 

对系统进行优化的原则就是人能完成的工作，不要让机器去替你完成。虽然SQL提供了强大的功能集，再加上DBMS系统又提供的一些扩充的功能集，有时虽然写出的语句看似很简洁，将所有的工作都交给机器完成，到头来麻烦的还是你自己，因为你还要花费大量的时间和经历来做系统的诊断和优化。

 

有时复杂不一定是好事，简单的就是最好的。