基于Informix数据库典型低效率SQL语句的分析及优化方法

1、贵 州 科 学 30( 2) : 21 28，2012guizhou science基于数据库典型低效率语句的分析及informixsql优化方法郑添健1，2索红敏1( 1 中国人寿保险股份有限公司 黔南分公司信息技术部 都匀 558000; 2 贵州民族学院 贵阳 550025)摘 要:本文从跟踪 sql 性能的方法和 informix 应用调优的原则，对 informix 典型低效率 sql 语句进行分析，在 informix 数据库系统实际应用方面提出了提高 informix 数据库运行效率的方法，旨在为 informix 数据库维护人员和程序设计人员提供有 益的参考。关键词:infor

2、mix，低效率 sql 语句，运行效率，优化方法文章编号 1003-6563( 2012) 02-0021-08中图分类号 tp 31. 312 文献标识码 asql statement analysis and optimization method for typical low-efficiencybased on informix databasezhenb tian-jian1，2suo hong-min1( 1 china life insurance company，south guizhou branch，duyun，guizhou 558000，china; 2 guizho

3、u college fornationalities，guiyang，guizhou 550025，china)abstract:tracing sql performance and applying tuning principles of informix，this article analyzed on the typi-cal low efficiency of informix sql statements and proposed a method to improve the operation efficiency in the prac-tical application

4、of informix database so as to provide useful references for those who maintain and design the in- formix databaseskey words:informix，low efficiency of sql，statements，operational efficiency，optimization methods着库中数据量与应用处理交易量的不断增多，其运行效率问题尤显突出。根据作者多年的研究和工作informix 数据库是一种被广泛应用的关系型数据库，因其具有高性能、高可靠性、支持数据完整

5、性 定义、检查等特性而得到广泛应用( 范俊军，1999 ) 。 保险公司的核心业务就是使用的 informix 数据库。 如何提高其应用性能是一个关键的课题，特别是随经验，针对 informix 典型低效率 sql 语句进行分析，提出优化策略与措施。1跟踪 sql 性能的常用方法收稿日期: 2012-02-23; 修回日期: 2012-03-20作者简介: 郑添健( 1976-) ，工程师，在读硕士，研究方向: 数据库，计算 机网络。e-mail: ztjpsc 163 com通讯作者: 索红敏( 1962-) 男，教授，研究方向: 计算机与数学研究。set explain 语句分析当发现某一

6、部分 informix 语句运行特别慢又找1 1不到原因时，可在程序中加入“set explain on”语句，程序运行时，在程序运行的当前目录下产生一个 “sqexplain out”文件，该文件记录了 informix 数据库 服务器采用何种优化策略来查找数据库。在该文件 中可以发现并查找其中有无使用索引条件、估计的 查找代价等信息。具体测试 sql 的性能: set explain 语句set explain 后可带以下参数: on : 为每个后续查询生成评估并将结果写 入当前目录中的输出文件。如果文件已经存在，那 么新输出会附加到现有文件; avoid_execute : 防止 sel

7、ect、insert、 update 或 delete 语句在数据库服务器将查询 计划打印到输出文件中时执行; off : 终止 set explain 语句的活动，以便 不再为后续查询生成评估或不再将评估写入输出文 件; file to : 为每个后续查询生成评估并使您 能够指定说明输出文件的位置;在 set explain off 语句或程序结束之前，来 自 set explain on 语句的输出将定向到适当的 文件。如果没有输入 set explain 语句，那么缺省 行为是 off，并且数据库服务器不会为查询生成评 估。set explain 语句在数据库服务器优化阶段 期间执行，该

8、优化阶段在启动查询时开始。对于与 游标相关的查询，如果查询已准备好且没有主变量， 那么优化在准备期间发生。否则，优化在打开游标 时发生。set explain on avoid_execute;select unique pmn04 from pmn_filewhere pmn04 != and pmn04 is not null order by pmn04;3)如果希望了解下述 sql 语句的查询计划，并将结果输出到指定的位置，可以执行:set explain on avoid_execute;set explain file to / u / inf7 3 / explain out;

9、select unique pmn04 from pmn_file where pmn04 != and pmn04 is not nullorder by pmn04( 刘正龙，2001) 。4)如果不再希望了解下述 sql 语句的查询计划，可以执行:set explain off;总之，我们可以通过获取 sqexplian out 文件，查 看 dbms 的查询计划，从而进行优化: 增加索引、更 新统计量、修改应用。1 2寻找 sql 瓶颈我们可以通过 onmode-y sid 1 命令，启动动态分析跟踪正在运行的线程的 sql 的查询计划。onmode-y 命令基本语法:调用onmode

10、-y sid 2解释打 开 对 sid 的setexplain，并 且 仅显示查询计划onmode-y sid 1打 开 对 sid 的explain关 闭 对 sid 的explainset1)如果希望了解下述 sql 语句的查询计划并onmode-y sid 0set执行下述 sql 语句，可以执行:set explain on ;select unique pmn04 from pmn_filewhere pmn04 != and pmn04 is not null order by pmn04;2) 如果希望了解下述 sql 语句的查询计划但 不希望执行下述 sql 语句，可以执行:当

11、使用 onmode-y 命令打开 set explain 时，文 件 中，可 从 中 查 看输 出 显 示 在sqexplain outdbms 的查询计划。从而根据查询计划进行优化:增加索引、更新统计量、修改应用。具体实施步骤可如下: 按用户名 onstat-u 找到 io 高的线程2 期郑添健，等: 基于 informix 数据库典型低效率 sql 语句的分析及优化方法23tong1 $ onstat uibm informix dynamic server version 9 04 fc6userthreads on line up 28 daysaddress6145e0186145e

12、5186145e6186145f418flags p d p d p f p fsessid6116525656281263261251215231320110userinformix informix informix obpsqn obpsqdn informix informix informix informix obpsqn obpsqdnttywait00000000000tout00000000000locks00000000000nreads103233240417382356153301023254145nwrites1400000000006146f318 p f6146g

13、2186146g9156146d2196146d7186146d9186146d117 p f p f p f p f p f p f 运行 “onmode-y sid 1”，打开动态分析tong1 $ onstat g sql 23ibm informix dynamic server version 9 04 fc6 sql err0on lineisam err0 up 28 daysf evers explain9 03 dynamicsessid628sqlstmt typeselectcurrentdatabaseviewdb_4420iso locklvl modecr not

14、waitcurrent sql statement:select x3 occ_name_chn，x0 hldr_cust fromcl_cntr1: cbps8 psn_cntr_holder x0，cl_cust1: cbps8 customer x1，cl_cust1: cbps8 psn_customer x2，code: cbps8 occ_code x3 where( x0 cntr_id = ?) and ( ( ( x0 hldr_cust_no = x1 cust_no) and( x1 cust_oac_branch_no = 4420ff ) ) and ( x1 cus

15、t_oac_branch_no442000) ) and ( x2 cust_id = x1 cust_id ) and ( ( ( x3 occ_code = =x2 occ_dtl_code) and ( x3 occ_subcls_code = x2 occ_subcls_code ) ) and( x3 occ_class_code = x2 occ_class_code ) )last parsed sql statement :select x3 occ_name_chn，x0 hldr_cust fromcl_cntr1: cbps8 psn_cntr_holder x0，cl_

16、cust1: cbps8 customer x1，cl_cust1: cbps8 psn_customer x2，code: cbps8 occ_code x3 where( x0 cntr_id = ?) and ( ( ( x0 hldr_cust_no = x1 cust_no) and( x1 cust_oac_branch_no = 4420ff ) ) and ( x1 cust_oac_branch_no =442000) ) and ( x2 cust_id = x1 cust_id ) and ( ( ( x3 occ_code =x2 occ_dtl_code) and (

17、 x3 occ_subcls_code = x2 occ_subcls_code ) ) and( x3 occ_class_code = x2 occ_class_code ) ) 在用户目录下，生成 sqexplain out sid 文件tong1 $ l / u / tong1total 1448 rw r r rw r r rw r r rw r r 1 tong11 tong11 tong11 tong1informixinformix informix informix697 sep 22477 sep 222336 sep 221772 sep 22sqexplain out

18、1279sqexplain out 1386 sqexplain out 628 sqexplain out 9159 查看 sqexplain out sid 文件，寻找高代价或顺序扫描的 sql，定位瓶颈query: createview“cbps8” accept_insur( insur_cntr_no，i _info _no，pol _code，info _premium，insur _amnt，pay _interval，insur _exec _stat，rev _interval，rev _amnt，bclk_clerk_no，dclk_clerk_no，appl_branch

19、_no，occ_add_amnt，health_add_amnt，insur_dur，valid_date，insur_effdate，insur_id，insur_num，insur _type，num _of _insurs，pay _al _flag，pay _dur，start _rev _age，ipsn _ name，ipsn _ seq，ipsn_occ_no，ipsn_nation，ipsn_sex，ipsn_bith_date，ipsn_marr_stat，hld_name，hld_seq，hld_occ_no，hld_na- tion，hld_bith_date，hld_m

20、arr_stat，rev_vary_pct，bouus_deliv_mth，ppay_cv，insur_year，pay_prem_num，pay_prem_total，rec_prem_date，rec_input_date，pay_prem，pay_type_code，rev_item_code，occ_code，dept_no，insur_stor)select x0 insur_cntr_no ，x0 i_info_no ，x0 pol_code ，x0 info_premium，x0 insur_amnt ，x0 pay_interval ，x0 insur_exec_stat ，x

21、0 rev_interval，x0 rev_amnt ，x0 bclk_clerk_no ，x0 dclk_clerk_no ，x0 appl_branch_no，x0 occ_add_amnt ，x0 health_add_amnt ，x0 insur_dur ，x0 valid_date，x0 insur_effdate ，x0 insur_id ，x0 insur_num ，x0 insur_type，x0 num_of_insurs ，x0 pay_al_flag ，x0 pay_dur ，x0 start_rev_age，x0 ipsn_name ，x0 ipsn_seq ，x0 i

22、psn_occ_no ，x0 ipsn_nation，x0 ipsn_sex ，x0 ipsn_bith_date ，x0 ipsn_marr_stat ，x0 hld_name，x0 hld_seq ，x0 hld_occ_no ，x0 hld_nation ，x0 hld_bith_date，x0 hld_marr_stat ，x0 rev_vary_pct ，x0 bouus_deliv_mth ，x0 ppay_cv，x0 insur_year ，x0 pay_prem_num ，x0 pay_prem_total ，x0 rec_prem_date，x0 rec_input_date

23、 ，x0 pay_prem ，x0 pay_type_code ，x0 rev_item_code，x0 occ_code ，x0 dept_no ，x0 insur_stor from picc1: a1gd accept_insurasx0 where ( ( x0 appl_branch_no = 440300) = 4403ff) ) ;estimated cost: 2147483647estimated # of rows returned: 21474836471) cbps8 agency_reg_tbl: sequential scan filters: pa = oa2)

24、cbps8 agency_frame: sequential scan nested loop join onmode-y sid 0 关闭动态分析and ( x0 appl_branch_no 联合索引 a，b，c 确保 a 是不同值最多的字段 建好索引，必须 update statistics3) 确保 sql 走在正确的索引上应用调优的原则21)2)消除对大表的顺序扫描建立合适的索引2 期郑添健，等: 基于 informix 数据库典型低效率 sql 语句的分析及优化方法25yyyy) )注意: extend 函数默认扩展为零点零分零秒 ( yyyy-mm-dd 00: 00: 00 )

25、 ，要注意日期区间( 尤其 是“日“) 的选择。 informix 指定查询索引功能select + index ( tabname idxname) from. where. 除了 index 关键字，还有 avoid _full、avoid _in- dex、full、index_all 4 种( 共 5 种) 指定 及时更新统计信息2)对 date 型字段使用 year 函数year( date_col) = yyyy应改成date_ col between mdy ( 1，1，yyyy ) and mdy( 12，31，yyyy)3) 对字符型字段使用下标表达式 update stat

26、istics high| medium| low update statistics for table update statistics for table( coll，col2l)如 where mgr_branch_no1，4 =应改成where mgr_branch_no between “441400”and “4413ff”4413典型低效 sql 分析及优化方法34)对字符型字段使用数值进行匹配3 1 对索引字段使用函数进行匹配select.from std_contract x0 ，mio_log x1 ，policy x2where date ( x1 mio _log _

27、upd _time) = mdy( month ( 1) ，day ( 15) ，year ( 2005) )and date ( x1 mio _ log _ upd _ time ) = mdy( 1，1，year ( 2005) )and x1 cntr_no = x0 cntr_no分析及优化策略与措施:1) 在查询 where 子句中，任何对列的加工处理 都将导致表扫描，包括: 数据库函数 计算表达式select * from agent_trans awhere a o _ agent _ no = t _ agent _ reg _ tbl agent _reg_noand a

28、o_branch _no = t_agent_reg _tbl branch _no其中: agent_trans o_agent_no 数据类型为 char t_agent_reg_tbl agent_reg _no 数据类型为 deci-mal5) 分析用一个数值作条件去检索一个字符型的字段 时，会在字符型字段上实施强制类型转换，将字符转 化为 ascii 码值，与数值比较，相当于在索引字段加 函数，等同于低效 sql1 中介绍的情况，因此导致索 引作废，无论 update 多少次 statistics、设怎样的数据 库参数，结果都一样的顺序扫描。3 3 优化方法与措施: 对于字符型( c

29、har，varchar) 字段，查询的 sql条件，务必以“”或标明为字符键值; 如果是程序变量为查询条件，则变量一定要 定义为字符型; 在数据库设计时，也应该考虑到这个因素，同 义字段的数据库表中，都应该定义为相同的数据类 型。例:2)因为对列的任何操作结果都是在 sql 运行时逐列计算得到的，因此它不得不进行表搜索，而无法使用该列上面的索引; 如果这些结果在查询编译 时就能得到，那么就可以被 sql 优化器优化，使用 索引，避免表搜索;3) 构建查询 sql 时要尽量避免对列使用函数 及表达式4)可以对索引上的函数建立索引3 2 常见的列函数使用情况1)对 datetime 型字段使用 d

30、ate 函数where date ( datetime _ col )yyyy) 应改成:= mdy ( mm，dd，1234where col_char = “123” where col_char = ? 123define l_str varchar( )select. where col_char = l_strwhere datetime_col extend ( myd( mm，dd + 1，yyyy) ) = extend ( mdy ( mm，dd，and datatime _ col5 define l_var decimal( )6 select where col_cha

31、r = l_varfor table aaa;select a cntr_no from aaa a ，std _contract b where acntr_no = b cntr_nointo temp bbb with no log;create index tmpb on bbb ( cntr _ no ) ; update statistics for table bbb; 只访问一次 std _contract，并且与刚刚生成的临时1)子查询 in，not exists 语句select. from mio_log bwhere b mio_class = 1and b mio_i

32、tem_code = ps and b cntr_no not in( select cntr_no from std_contract)into temp aaa with no log分析该 sql 运行时，检索到的每一表收付费表 mio _表 aaa 作表连接( 小表) ，小表连大表的效率是很高的。查询结果保存在临时表 bbb 中，无需再访问 std_contract 表delete from aaawhere exists ( select * from bbb where bbb cntr _ no= aaa cntr_no) ;3 4log( 数量量: 千万级) 记录，都在保单表

33、std _contract( 数量量: 百万级) 激发一次顺序扫描。子查询引起 的嵌套循环 nested loop 代价非常巨大，引起大量的 磁盘读; 同时，子查询在实现时相当于 foreach 循环， 大量消耗 cpu 资源。1) 优化方向: 避免子查询，避免大表操作，尽量 利用临时表操作。如果子查询不可避免，选择效率 最高的 exists 语句，并且使用键值查询。 第一次优化:select. from mio_log bwhere b mio_class = 1 and b mio_item_code = ps and not exists( select cntr_no from std

34、_contractdelete. existss 等价于 select. not exists。在两个小表之间操作，速度非常快。优化策略与措施小结: 善用临时表，减少对大表的访问 尽量少用子查询; 如果必须使用，要避免在子 查询中对大表( 10 万行以上) 进行检索 ; 尽量使用 exists 语句及键值 查询，避免子查 询的顺序扫描。3 5 将实表当成临时表用 delete from bxhth where 1 = 1; insert into bxhthselect. from. where. 临时表是一种特殊的库表，利用临时表可以暂存数 据 当数据被使用一次以上时，使用临时表可以显著 地

35、减少运行时间。尤其是使用参数 with no log 建立 的无日志型临时表，会使用临时数据空间，可以减少 写事务日志的开销，并且在数据库关闭( close data- base) 时不会消失( 如果是日志型临时表，关闭数据 库时临时表会消失) ; 实表并不拥有这些属性和便利，因此如上例 所示的方法，将实表当作临时表来用，是低效率的做 法; informix 支持在 select 语句中直接创建临时 表，十分方便。如下:where std_contract cntr_no= b cntr_no)into temp aaa with no log;2)将 not in 改成 not exists

36、，将无查询条件激发顺序查询的子查询改成键值查询后，sql 的运行效率已经有了非常大的提高 第二次优化，两个重点 一是利用临时表，避免对 std_contract 的反复select 二是用 exists 代替 not exists 。3)在子查询语句中，in / not exists 语句等均要得到子查询的全体结果集后才能返回，而前者( ex-ists) 只要得到一个记录，即可返回 true 而结束子查 询。因此 exists 是效率最高的子查询语句。因此， 当子查询不可避免时，尽量使用 exists 语句实现。 select unique cntr_no from mio_log where

37、 mio_date = mdy( 9，1，2005)into temp aaa with no log;create index tmpa on aaa ( cntr _ no ) ; update statisticsselect. from.no logwhere. into temp 表 名 with创建临时表的四种情况 create temp table. with no log;2 期郑添健，等: 基于 informix 数据库典型低效率 sql 语句的分析及优化方法27读表，改用临时表的方式，对数据依次进行加滤加工，得到结果。形如 :1 select. from std_contr

38、act，mtn_gen_info where. into temp aaa with no log;2 select. from aaa，customer where. into temp bbb with no log;3 select. from bbb，customer where. into temp ccc with no log;. create temp table. ; select. into to temp talbe with no log; select. into to temp talbe。3 6 优化方法 实际的 v8 外挂程序中，典型的应用是从收 付表( mio

39、_log) 关联到保单表( std_contract) 、客户表 ( customer) 等大表，对磁盘的读写很大。 通常 select 的结果集只不过是沧海一粟，临 时表的规模和实表相比无多大差异，即使对临时表 作顺序扫描，也比实表走索引的开销要小。 因此，如果不是一次 select 就能得到最后结 果的情况，建议都改成以临时表实现。这在统计型 的应用中尤为重要。 监控 bcv 服务器的数据空间使用情况表 明，分公司目前还没有使用临时表的编程习惯，临时 数据空间 tempdbas 的使用率几乎为 0。应用实例4create temp table tmp_tab1( . ) ;insert i

40、nto tmp_tab1( . )select . from s_insur_info a，rec_prem_acc b where. ;update tmp _ tab1 set pay = ( select sum ( nvl ( cfm _cpnst_amt，0 0) )from s_insur_info a，cpnst_apply bwhere a i_ info _ appl _ branch = b i _ info _ appl _branchand a i_info_date = b i_info_dateand a i_info_appl_no = b i_info_appl

41、_no and a pol_code = tmp_tab1 pol_codeand b cpnst_vrfyopn_stat = “c”and year( b enter_date) = tmp_tab1 sign_year and a card_cls_code = tmp_tab1 card_cls_code and a card_class = tmp_tab1 card_class)where 1 = 1;create temp table tmp_tab1( . ) with no log; 不加上 with no log ，临时表会建在带日志 的 datadbs 上 有可能发生长事

42、务回滚insert into tmp_tab1( . )select . from s_insur_info a，rec_prem_acc b where. ; update tmp_tab1 set pay =( select sum( nvl( cfm_cpnst_amt，0 0) )from s_insur_info a，cpnst_apply bwhere a i_ info _ appl _ branch = b i _ info _ appl _branchand a i_info_date = b i_info_date3 7在索引字段使用 or 或 inselect.from

43、mio_log where mio_date= mdy( 7，5 ，2005)or mio_date = mdy( 8，6，2005) ;尽管 mio _ date 字段上有索引，但这种形式的 where 子句强迫优化器使用顺序存取; or 语句要检 索的是分离的行的集合。3 8 优化方法将以上语句改写为:select. from mio _ log where mio _ date = mdy ( 7，5，2005)unionselect. from mio _ log where mio _ date = mdy ( 8，6，2005)运行两次走索引的查询，比一次顺序扫描效率 高。优化程序

44、结构中的难改问题，按如下方法进3 9行优化 对典型的 foreach 程序: declare cursor . select. from. foreach cursor into .select. from std_contract. select. from mtn_gen_info. select. from customer.end foreach对于批量处理，应尽量避免 foreach 循环中反复and a i_info_appl_no = b i_info_appl_noand a pol_code = tmp_tab1 pol_code and b cpnst_vrfyopn_st

45、at = “c”and year( b enter_date) = tmp_tab1 sign_yearand a card_cls_code = tmp_tab1 card_cls_codeand a cpnst_vrfyopn_stat = “c”and a pol_code = tmp_tab1 pol_codeand b enter_date = tmp_tab1 sign_yearand a card_cls_code = tmp_tab1 card_cls_code and a card_class = tmp_tab1 card_class)where 1 = 1; 严谨来说，不应该用 1 = 1，而应该判断是 否存在子查询的记录，才作更新exists ( select pol_code from bbb awhere a p