一种数据库调优方法的分析及应用

一种数据库调优方法的分析及应用摘要：本文对数据库各种调优方法进行了总结，将数据库调优方法分成了5个级别，并简要阐述了每个级别调优所包含的各种方法。然后结合一个实际案例，详细说明了根据数据库实际特点对程序业务逻辑进行优化设计，可以明显提高程序性能。以此提出了优化程序业务逻辑设计是实现数据库调优的最有效方法。关键词：数据库SQL优化业务逻辑优化0引言现在大部分计算机系统都涉及到对信息数据的存储与处理。存储数据离不开数据库，因此对数据库的优化是应用系统高效稳定运行的基础。实际中，由于系统配置、程序设计、系统负荷变化等原因使得数据库很难在设计之初就到达最优的运行效果。这就需要在系统运行过程中不断发现问题，根据实际情况对数据库进行调优，以到达系统稳定运行的目的。可见，数据库调优对系统开发人员来说是一项分外重要的工作。数据库调优并不是一件简单的工作，可能涉及系统的方方面面。对数据库调优很难找到固定的模式和方法进行参照。一般需要根据具体的实际情况具体分析，根据不同的原因，综合考虑数据库的内在实现机制，采取恰当的措施进行优化，才能产生令人满意的效果。1数据库调优方法的级别分类数据库调优是一项知识密集型的工作【1】，需要综合考虑各个方面因素对数据库性能的影响，然后采取相应的措施进行调整。按照优化对象的由低到高的层次不同可以将数据库调优方法分为五个级别：（1）第一个级别是主机系统的优化。这个级别的调优主要是通过对硬件的升级改造，操作系统的参数配置等手段使系统整体性能得到改进，从而使运行在其中的系统的性能也得到改进。这个级别的优化可以说是外部性能的调整，比如CPU的处理速度，使用内存的大小，网络传输性能，磁盘阵列的物理设计等指标都会对系统性能有重要的影响。（2）第二个级别是数据库RDBMS级别的优化【2】。这种优化是通过修改数据库的一些配置参数等手段来实现的【3】。比如在Oracle数据库中可以对以下几个方面的参数进行调整，包括修改系统全局区，数据缓存区，共享内存区，程序全局区，排序区，连接数量等方法。（3）第三个级别是数据库逻辑结构及数据模型级的优化【2】。这类优化主要集中在对数据库逻辑结构和数据建模方面，在设计阶段就需要重点考虑这类问题。包括为提高磁盘I/O速度【4】将不同的文件放到不同的I/O通道通过分区存储提高大数据表的读写效率、合理的设计索引、数据和索引的表空间分开、合理设计表结构和表间关联关系等方法。（4）第四个级别是SQL级的优化。一般情况下，程序中不良的SQL语句可能导致整个数据库系统高负荷运转，甚至对外暂停服务【3】，所以需要根据不同数据库对SQL的编译和执行特点，对SQL语句进行优化。比如在Oracle数据库的SQL设计中，需要尽量采用静态SQL以提高执行速度，尽量书写格式相同的SQL以减少系统解析次数、尽量不用“”或者“!=”操作符等方法来提高SQL执行速度。（5）第五个级别是业务逻辑级别的优化。这一级别的优化方法是从应用系统的业务逻辑出发，根据具体的业务逻辑分析，结合数据库内部运行机制，同时参考第四个级别（SQL级别）的调优方法，采用最优的SQL执行方法，对程序的设计进行优化。优化过程中，需要充分考虑业务逻辑，数据库特征及二者之间的联系，如减少大表之间的关联，重新设计业务流程等，从而使性能的优化得到很大提升。这是调优中通常被忽视的方法，但却可能取得意想不到的效果。以上五个级别之间并没有绝对的分界线，各个级别的优化也不是孤立的，可能某个级别的优化就是为另一级别的优化提供一个好的基础。比如进行业务逻辑级别的调优时通常也会同时对SQL进行调优，或者SGA参数的配置就受操作系统一些参数的制约【4】。还有主机系统级别的优化中，有时会对磁盘阵列做相应的调整，而这对于数据库的分区有着重要的影响。本文结合一个实际例子调优，对业务逻辑级别优化方法的应用进行了详细的描述和总结，并对比优化前后的程序性能，可以看到调优取得了很好的效果。2业务逻辑级别调优方法的分析及应用业务逻辑级别的调优方法包含非常丰富的内容。包括业务逻辑的分析，结合数据库内部机制实现对应程序的优化等。对于不同的系统不同的程序，需要具体分析程序的瓶颈所在，然后根据具体的设计来决定最终采用何种方法进行优化。下面结合具体例子说明如何采用业务逻辑调优方法进行程序优化。2.1实例的需求定义根据pnr（旅客订座记录）的改变对最终的疑似重复pnr结果做相应的处理。对于改变的pnr，逻辑上分两个部分：一部分是由于pnr的改变，它与别的pnr不再是疑似重复pnr对，则在结果表中删除原来与其疑似重复的记录；另一部分是由于pnr的改变，它与新的pnr成为了疑似重复pnr对，则结合新的原始疑似重复pnr结果，从最终结果中删除原来与其疑似重复的记录，且保留新的疑似重复pnr对。系统采用Oracle数据库实现。2.2系统涉及的表及索引表iaa_change_info：保存每天修改的pnr号，通常数据量在10万到20万之间。表只有一列，无需索引。表iaa_sim_pnr_info：保存每天初步判断为疑似重复pnr的记录，共37列，通常数据量在10万左右；表上pnr_ref,pnr_ref_o列建有索引。表pnr_inc_chk_rslt：保存最终的疑似重复pnr记录，共23列，目前数据量在35万左右。其中有主键索引列result_id,另外在pnr_ref,pnr_ref_o两列建有一个多列索引。此外，最终结果表还有两个相关联的表，分别有9和16列，数据量与结果表基本相当。删除结果表时，会同时删除两个关联表的相关记录。删除的数据量在6万左右。2.3程序实现及调优方法的应用最初的版本是通过loop循环实现的，代码片段如下：forrec_tmp2in(selectpic.result_idfrompnr_inc_chk_rsltpicwhereexists(selectici.pnr_reffromiaa_change_infoiciwhereici.pnr_ref=pic.pnr_reforici.pnr_ref=pic.pnr_ref_o)andnotexists(selectisp.pnr_ref,isp.pnr_ref_ofromiaa_sim_pnr_infoispwhere(isp.pnr_ref=pic.pnr_refandisp.pnr_ref_o=pic.pnr_ref_o)or(isp.pnr_ref=pic.pnr_ref_oandisp.pnr_ref_o=pic.pnr_ref)))loopdeletefrompnr_inc_chk_rsltwhereresult_id=rec_tmp2.result_idandopt_ind=0;endloop;......程序按照业务逻辑将改变的pnr分成两部分，通过两个loop循环（上面仅显示一个）实现了所需的功能。事实证明这种方法是不可取的，在前面所示的数据量的情况下，程序运行完毕居然需要二十个小时以上。通过分析可知，loop循环每次都要进行表扫描，对于数据量较大的表进行此操作是比较费时的，而且程序中大表之间的关联比较密切，导致扫描次数成数量级的增加。同时由于sql语句过于复杂，子查询条件过于复杂，导致执行时都要花费较长时间的去解析，而且经测试该语句并没有使用到索引（对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.【1】），每次都是全表扫描，另外在实际系统中删除结果表时还会级联删除另外两个相关的表。这些因素综合在一起直接导致了程序性能的低下。2.3.1SQL优化的应用由于实际系统所在的服务器同时运行多个系统，需要综合考虑各个系统的需求，不会因为某个系统而修改一些数据库参数，所以首先考虑进行SQL级别调优。优化时考虑不用loop循环实现，而是改用临时表加批量数据处理的方式实现，同时对复杂的SQL语句进行分解，使其尽量用到索引。增加了三个临时表，分别存放两部分不同的changepnr以及结果表中存在的changepnr号。优化后的程序如下：--在iaa_change_info中存在的PNR，但在iaa_sim_pnr_info中不存在相关PNR对insertintotmp_no_sim_pnr1selectpnr_reffromiaa_change_info;--tmp_no_sim_pnr表存储在iaa_change_info中存在的PNR，同时在iaa_sim_pnr_info中存在相关PNR对insertintotmp_no_sim_pnrselectpnr_reffromtmp_no_sim_pnr1awhereexists(select1fromiaa_sim_pnr_infobwhereb.pnr_ref=a.pnr_ref);insertintotmp_no_sim_pnrselectpnr_reffromtmp_no_sim_pnr1awhereexists(select1fromiaa_sim_pnr_infobwhereb.pnr_ref_o=a.pnr_ref);--tmp_no_sim_pnr1存储在iaa_change_info中存在的PNR，但在iaa_sim_pnr_info中不存在相关PNR对deletefromtmp_no_sim_pnr1awhereexists(select1fromiaa_sim_pnr_infobwhereb.pnr_ref=a.pnr_ref);deletefromtmp_no_sim_pnr1awhereexists(select1fromiaa_sim_pnr_infobwhereb.pnr_ref_o=a.pnr_ref);deletefrompnr_inc_chk_rsltawhereexists(select1fromtmp_no_sim_pnr1bwhere(a.pnr_ref=b.pnr_refora.pnr_ref_o=b.pnr_ref)anda.opt_ind=0);insertintotmp_dup_pnr_changedselectresult_id,pnr_ref,pnr_ref_ofrompnr_inc_chk_rsltbwhereexists(select1fromtmp_no_sim_pnrawhereb.pnr_ref=a.pnr_reforb.pnr_ref_o=a.pnr_ref);deletefromtmp_dup_pnr_changedawhereexists(select1fromiaa_sim_pnr_infobwhere(b.pnr_ref=a.pnr_refandb.pnr_ref_o=a.pnr_ref_o)or(b.pnr_ref=a.pnr_ref_oandb.pnr_ref_o=a.pnr_ref));deletefrompnr_inc_chk_rsltwhereresult_idin(selectresult_idfromtmp_dup_pnr_changed);这个版本的程序经试验，其执行时间在十个小时左右，较前一个版本性能上有了一定的提升。究其原因，主要是因为：一、通过批量处理减少了表扫描的次数；二、分解了几个较复杂的SQL语句；三使用了pnr_inc_chk_rslt表的主键索引。但是由于并没有减少大表之间的关联，导致大表扫描次数还较多，并且也没有充分使用定义的索引，所以性能并不是理想，还需要考虑使用更好的优化方法。2.3.2业务逻辑级别调优的应用当SQL优化取得的效果并不满意的时候，可以考虑从程序设计方面进行优化。要进行程序设计方面的优化，就需要对业务逻辑进行进一步深入分析，考虑是否有更加简洁的实现方式。同时也需要对现有程序结构进行修改整合，力争达到结构的最优。对以上需求定义进行进一步的分析，可以发现虽然从逻辑上可以将改变的pnr分为两部分分别进行处理，但是对这两部分的处理蕴含着一个共性，那就是最终都要从结果中删除与其pnr号相关的数据。通过对这一共性的提炼，可以发现一个更简洁的实现