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

贵 州 科 学 30( 2) : 21 28，2012Guizhou Science基于数据库典型低效率语句的分析及InformixSQL优化方法郑添健1，2索红敏1( 1 中国人寿保险股份有限公司 黔南分公司信息技术部 都匀 558000; 2 贵州民族学院 贵阳 550025)摘 要:本文从跟踪 SQL 性能的方法和 Informix 应用调优的原则，对 Informix 典型低效率 SQL 语句进行分析，在 Informix 数据库系统实际应用方面提出了提高 Informix 数据库运行效率的方法，旨在为 Informix 数据库维护人员和程序设计人员提供有 益的参考。关键词:Informix，低效率 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 Guizhou 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 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 数据库是一种被广泛应用的关系型数据库，因其具有高性能、高可靠性、支持数据完整性 定义、检查等特性而得到广泛应用( 范俊军，1999 ) 。 保险公司的核心业务就是使用的 Informix 数据库。 如何提高其应用性能是一个关键的课题，特别是随经验，针对 Informix 典型低效率 SQL 语句进行分析，提出优化策略与措施。1跟踪 SQL 性能的常用方法收稿日期: 2012-02-23; 修回日期: 2012-03-20作者简介: 郑添健( 1976-) ，工程师，在读硕士，研究方向: 数据库，计算 机网络。E-mail: ztjpsc 163 com通讯作者: 索红敏( 1962-) 男，教授，研究方向: 计算机与数学研究。Set explain 语句分析当发现某一部分 Informix 语句运行特别慢又找1 1不到原因时，可在程序中加入“set explain on”语句，程序运行时，在程序运行的当前目录下产生一个 “sqexplain out”文件，该文件记录了 Informix 数据库 服务器采用何种优化策略来查找数据库。在该文件 中可以发现并查找其中有无使用索引条件、估计的 查找代价等信息。具体测试 sql 的性能: Set explain 语句Set explain 后可带以下参数: ON : 为每个后续查询生成评估并将结果写 入当前目录中的输出文件。如果文件已经存在，那 么新输出会附加到现有文件; AVOID_EXECUTE : 防止 SELECT、INSERT、 UPDATE 或 DELETE 语句在数据库服务器将查询 计划打印到输出文件中时执行; OFF : 终止 SET EXPLAIN 语句的活动，以便 不再为后续查询生成评估或不再将评估写入输出文 件; FILE TO : 为每个后续查询生成评估并使您 能够指定说明输出文件的位置;在 SET EXPLAIN OFF 语句或程序结束之前，来 自 SET EXPLAIN ON 语句的输出将定向到适当的 文件。如果没有输入 SET EXPLAIN 语句，那么缺省 行为是 OFF，并且数据库服务器不会为查询生成评 估。SET EXPLAIN 语句在数据库服务器优化阶段 期间执行，该优化阶段在启动查询时开始。对于与 游标相关的查询，如果查询已准备好且没有主变量， 那么优化在准备期间发生。否则，优化在打开游标 时发生。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; 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-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 语句，可以执行:当使用 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 daysaddress6145e0186145e5186145e6186145f418flags P D P D P F P Fsessid6116525656281263261251215231320110userinformix informix informix obpsqn obpsqdn informix informix informix informix obpsqn obpsqdnttywait00000000000tout00000000000locks00000000000nreads103233240417382356153301023254145nwrites1400000000006146f318 P F6146g2186146g9156146d2196146d7186146d9186146d117 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 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 cust_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_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 ( 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 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_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_marr_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 ，x0 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 ipsn_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 ，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) 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 ) ，要注意日期区间( 尤其 是“日“) 的选择。 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 statistics 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 _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 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 优化方法与措施: 对于字符型( char，varchar) 字段，查询的 sql条件，务必以“”或标明为字符键值; 如果是程序变量为查询条件，则变量一定要 定义为字符型; 在数据库设计时，也应该考虑到这个因素，同 义字段的数据库表中，都应该定义为相同的数据类 型。例:2)因为对列的任何操作结果都是在 SQL 运行时逐列计算得到的，因此它不得不进行表搜索，而无法使用该列上面的索引; 如果这些结果在查询编译 时就能得到，那么就可以被 SQL 优化器优化，使用 索引，避免表搜索;3) 构建查询 SQL 时要尽量避免对列使用函数 及表达式4)可以对索引上的函数建立索引3 2 常见的列函数使用情况1)对 datetime 型字段使用 date 函数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_char = 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_item_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( 数量量: 千万级) 记录，都在保单表 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_contractdelete. existss 等价于 select. not exists。在两个小表之间操作，速度非常快。优化策略与措施小结: 善用临时表，减少对大表的访问 尽量少用子查询; 如果必须使用，要避免在子 查询中对大表( 10 万行以上) 进行检索 ; 尽量使用 exists 语句及键值 查询，避免子查 询的顺序扫描。3 5 将实表当成临时表用 delete from bxhth where 1 = 1; insert into bxhthselect. from. Where. 临时表是一种特殊的库表，利用临时表可以暂存数 据 当数据被使用一次以上时，使用临时表可以显著 地减少运行时间。尤其是使用参数 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，将无查询条件激发顺序查询的子查询改成键值查询后，sql 的运行效率已经有了非常大的提高 第二次优化，两个重点 一是利用临时表，避免对 std_contract 的反复select 二是用 exists 代替 not exists 。3)在子查询语句中，in / not exists 语句等均要得到子查询的全体结果集后才能返回，而前者( ex-ists) 只要得到一个记录，即可返回 true 而结束子查 询。因此 exists 是效率最高的子查询语句。因此， 当子查询不可避免时，尽量使用 exists 语句实现。 select unique cntr_no from mio_log where 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_contract，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_log) 关联到保单表( std_contract) 、客户表 ( customer) 等大表，对磁盘的读写很大。 通常 select 的结果集只不过是沧海一粟，临 时表的规模和实表相比无多大差异，即使对临时表 作顺序扫描，也比实表走索引的开销要小。 因此，如果不是一次 select 就能得到最后结 果的情况，建议都改成以临时表实现。这在统计型 的应用中尤为重要。 监控 BCV 服务器的数据空间使用情况表 明，分公司目前还没有使用临时表的编程习惯，临时 数据空间 tempdbas 的使用率几乎为 0。应用实例4create temp table tmp_tab1( . ) ;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_dateand a i_info_appl_no = b i_info_appl_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 上 有可能发生长事务回滚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 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)运行两次走索引的查询，比一次顺序扫描效率 高。优化程序结构中的难改问题，按如下方法进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_stat = “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 pol_code = tmp_tab1 pol_code and a enter_date = tmp_tab1 sign_yearand a card_cls_code = tmp_tab1 card_cls_code and a card_class = tmp_tab1 card_class)and a card_class)where 1 = 1; =tmp_tab1 card_class 要拆成两句 sql，将子查询的结果先保存在临时表里， 再对临时表作子查询。如下:select sum( nvl( cfm_cpnst_amt，0 0) )，a pol_codecfm_cpnst_amt，year( b enter_date)，a card_cls_code，a card_classfrom 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_no and b cpnst_vrfyopn_stat = “C” and a pol_code = tmp_tab1 pol_codeenter_date结论5本文主要阐述了 Informix 数据库对 SQL 语句性能监控的方法及使用，文中穿插了一些简洁实例，并对 Informix 典型低效率 SQL 语句进行了分析以便于理解掌握，介绍了如何对 SQL 语句进行调优的相 应方法及建议，从实际应用情况看，提高效率效果明 显。and year( b enter_date)and a card_cls_code = tmp_tab1 sign_yeartmp_tab1 card_cls_codeand a card_class = tmp_tab1 card_classgroup by 2，3，4，5into temp bbb with no log 结果保存在临时表中; 构建临时表时，将与外查询有关的 where子句都屏蔽， 改为 select 子句，并作 group by 聚合update tmp_tab1 set pay =( select cfm_cpnst_amtREFERENCESFan JJ，1999 Improve operation efficiency strategy and informix measuresJ China s Computer Reported，61-23 ( in chi- nese) Liu ZL，2001 Improve the system efficiency of Informix data- base wayJ Computer and Information Technology，2: 66( in chinese) 附中文参考文献范俊军，1999 提高 Informix 运行效率的策略与措施J 中 国计算机报，61-23.刘正龙，2001 提 高 Informix 数据库系统运行效率的途径 J 电脑与信息技术，2: 66.from bbb awhere 子查询从临时表取数据 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_no