贵 州 科 学 30( 2) : 21 － 28，2012 Gu i z hou  S c i e nc e
基于 数据库典型低效率
语句的分析及 In f o r m ix  SQ L  优化方法
郑添健1，2 索红敏
1
( 1
中国人寿保险股份有限公司 黔南分公司信息技术部 都匀  558000; 2
贵州民族学院 贵阳  550025)
摘  要: 本文从跟踪 SQL 性能的方法和 I n fo r m i x  应用调优的原则，对 I n for m i x  典型低效率 SQL 语句进行分析，在 I n for m i x  数
据库系统实际应用方面提出了提高 I n for m i x  数据库运行效率的方法，旨在为 I n for m i x  数据库维护人员和程序设计人员提供有 益的参考。
关键词: I n for m i x ，低效率 SQL 语句，运行效率，优化方法 文章编号  1003-6563( 2012) 02-0021-08
中图分类号  TP 31. 312
文献标识码  A
SQL S t a t e m e n t  An a l y s i s  and Optimization M e t h o d  f o r  Typical L o w -e ff i c i e n c y Based on I n f o r m i x D a t a b a se
ZHENB  T i a n -j i a n 1，2
SUO H o n g -m i n 1
( 1 Ch i na  L i f e  I n s u r an c e  Company ，South  G u i z h o u  B r an c h ，Duy un ，G u i z h o u  558000，Ch i na ; 2
G u i z h o u  C o ll e g e  f o r Na t i o na li t i es ，G u i y ang ，G u i z h o u  550025，Ch i na )
Ab s t r ac t : Trac i n g  SQL perfo rman ce and a pp l y i n g  t un i n g  p r i n c i p l es  of i n for m i x ，
t h i s  art i c l e  a n a l yze d on the ty p i - ca l  l ow  eff i c i e n cy  of i n for m i x  SQL state ments and prop osed a method to i mp rove  the o p erat i o n eff i c i e n cy  in the prac- t i ca l  a pp li cat i o n of i n for m i x  database so as to p rov i d e  u sef u l  reference s for those who m a i n ta i n and d es i g n the i n- for m i x  d ata b ases
K e y w o r d s : I NF O RM I X ，
l ow  eff i c i e n cy  of SQ L ，state m e n ts ，o p erat i o n a l  eff i c i e n cy ，o p t i m i zat i o n m et h o d s  着库中数据量与应用处理交易量的不断增多，其运
行效率问题尤显突出。根据作者多年的研究和工作
I n for m i x  数据库是一种被广泛应用的关系型数 据库，因其具有高性能、高可靠性、支持数据完整性 定义、检查等特性而得到广泛应用( 范俊军，
1999 ) 。 保险公司的核心业务就是使用的 I n for m i x  数据库。 如何提高其应用性能是一个关键的课题，特别是随 经验，针对 I n for m i x  典型低效率 SQL 语句进行分析， 提出优化策略与措施。
1 跟踪 SQL 性能的常用方法
收稿日期: 2012-02-23; 修回日期: 2012-03-20 作者简介: 郑添健( 1976-) ，工程师，在读硕士，研究方向: 数据库，计算 机网络。E-m a il :
zt j p sc @ 163． com  通讯作者: 索红敏( 1962-) 男，教授，研究方向: 计算机与数学研究。
Se t  explain 语句分析
当发现某一部分 I n for m i x  语句运行特别慢又
1． 1
不到原因时，可在程序中加入“set ex p l a i n o n”语句，程序运行时，在程序运行的当前目录下产生一个“s q e x p l a i n．o u t”文件，该文件记录了I n for m i x数据库服务器采用何种优化策略来查数据库。在该文件中可以发现并查其中有无使用索引条件、估计的查代价等信息。
具体测试s q l的性能:Set ex p l a i n 语句
Set ex p l a i n 后可带以下参数:
ON : 为每个后续查询生成评估并将结果写入当前目录中的输出文件。如果文件已经存在，那么新输出会附加到现有文件;
AV O I D_E XE C U T E ：防止S ELE CT、I N S E R T、UPDATE 或DELETE  语句在数据库服务器将查询计划打印到输出文件中时执行;
OFF ：终止SET EX P LA I N 语句的活动，以便不再为后续查询生成评估或不再将评估写入输出文件;
F I LE TO ：为每个后续查询生成评估并使您能够指定说明输出文件的位置;
在SET EX P LA I N OFF 语句或程序结束之前，来自SET EX P LA I N ON 语句的输出将定向到适当的文件。如果没有输入SET EX P LA I N 语句，那么缺省行为是O FF，并且数据库服务器不会为查询生成评估。
SET EX P LA I N 语句在数据库服务器优化阶段期间执行，该优化阶段在启动查询时开始。对于与游标相关的查询，如果查询已准备好且没有主变量，那么优化在准备期间发生。否则，优化在打开游标时发生。
set ex p l a i n on avo i d_exec u te;
SELECT UN I Q U E pmn04 FROM pmn_f il e
WHE RE pmn04 !=  ’’
AND pmn04 I S NOT NULL
ORDER BY pmn04;
3)如果希望了解下述SQL 语句的查询计划，并将结果输出到指定的位置，可以执行:
set ex p l a i n on avo i d_exec u te;
set ex p l a i n f il e to ’/u /i n f7． 3 /ex p l a i n．o u t’;
SELECT UN I Q U E pmn04 FROM pmn_f il e
WHE RE pmn04 !=  ’’
AND pmn04 I S NOT NULL
ORDER BY pmn04(刘正龙，2001)。
4)如果不再希望了解下述SQL 语句的查询计划，可以执行:
set ex p l a i n off;
总之，我们可以通过获取s q ex p li a n．out 文件，查看DBMS 的查询计划，从而进行优化：增加索引、更新统计量、修改应用。
1． 2寻sql 瓶颈
我们可以通过Onmode-Y s i d 1 命令，启动动态
分析跟踪正在运行的线程的s q l的查询计划。
onmode-Y 命令基本语法:
调用
onmode-Y s i d  2
解释
打开对s i d  的SET
EX P LA I N，并且仅
显示查询计划onmode-Y s i d  1打开对s i d  的
EX P LA I N
关闭对s i d  的
EX P LA I N
SET
1)如果希望了解下述SQL 语句的查询计划并onmode-Y s i d  0SET 执行下述SQL 语句，可以执行:
set ex p l a i n on ;
SELECT UN I Q U E pmn04 FROM pmn_f il e
WHE RE pmn04 !=  ’’
AND pmn04 I S NOT NULL
ORDER BY pmn04;
2)如果希望了解下述SQL 语句的查询计划但不希望执行下述SQL 语句，可以执行:
当使用onmode-Y 命令打开SET EX P LA I N 时，
文件中，可从中查看输出显示在s q ex p l a i n．o u t
DBMS 的查询计划。从而根据查询计划进行优化：增加索引、更新统计量、修改应用。
具体实施步骤可如下:
按用户名
onsta t-u 到I O高的线程
2 期郑添健，等：基于I n for m i x数据库典型低效率SQL 语句的分析及优化方法23
tong1 $ onstat  －u
I B M I n for m i x D y n a m i c Server V e rs i o n 9． 04． FC6
U sert h rea d s
－－On －L i n e－－Up 28 days
address 6145e018 6145e518 6145e618 6145f418
f l a gs
－－－P －D
－－－P －－D
－－－P －F
－－－P －－F
sess i d
611
652
565
628
126sql语句优化方式
326
1251
215
23
1320
110
user
i n for m i x
i n for m i x
i n for m i x
obpsqn
obpsqdn
i n for m i x
i n for m i x
i n for m i x
i n for m i x
obpsqn
obpsqdn
tty
－
－
－
－
－
－
－
－
－
－
－
wa i t
to u t
l oc k s
n rea d s
103
233
240
41738
2356
1533
10
23
254
145
n wr i tes
14
6146f318  －－－P －－F
6146g218 6146g915 6146d219 6146d718 6146d918 6146d117 －－－P －－F －－－P －－F －－－P －－F －－－P －－F －－－P －－F －－－P －－F
运行“o nm o d e-Y s i d 1”，打开动态分析tong1 $ onstat  －g s q l 23
I B M I n for m i x D y n a m i c Server V e rs i o n 9． 04． FC6 －－
SQL
ERR
0 On －L i n e
I S AM
ERR
－－Up 28 days
F． E．
Vers E x p l a i n
9． 03 D y n a m i c
Sess I d 628 SQL
Stmt ty p e
SELECT
C u rre n t
D ata b ase
V i ew db_4420
I so Lock
L v l Mode
CR Not W a i t
Curren t SQL state m e n t:
se l ect x3．occ_n a m e_c hn，x0． h l d r_c u st from
c l_c n tr1:"c bp s8"．p s n_c n tr_h o l
d er x0，c l_c u st1:"c bp s8"．custo mer x1，
c l_c u st1:"c bp s8"．p s n_c u sto m er x2，co
d e:"c bp s8"．occ_co d
e x3 where
( x0．c n tr_i d  =  ?)and ( ( ( x0． h l d r_c u st_n o  =  x1．c u st_n o)a nd
( x1．c u st_oac_b ra n c h_n o＜=  ＇4420ff＇)  )  and ( x1．c u st_oac_b ra n c h_n o
’442000’))  and ( x2．c u st_i d  =  x1．c u st_i d )  and ( ( ( x3．occ_co d e  = ＞= x2．occ_d t l_co d e)and ( x3．occ_s ub c l s_co d e  =  x2．occ_s ub c l s_co d e) )  a nd ( x3．occ_c l ass_c o d e  =  x2．occ_c l ass_co d e) )
Last parsed SQL state ment :
se l ect x3．occ_n a m e_c hn，x0． h l d r_c u st from
c l_c n tr1:"c bp s8"．p s n_c n tr_h o l
d er x0，c l_c u st1:"c bp s8"．custo mer x1，
c l_c u st1:"c bp s8"．p s n_c u sto m er x2，co
d e:"c bp s8"．occ_co d
e x3 where
( x0．c n tr_i d  =  ?)and ( ( ( x0． h l d r_c u st_n o  =  x1．c u st_n o)a nd
( x1．c u st_oac_b ra n c h_n o＜=  ＇4420ff＇)  )  and ( x1．c u st_oac_b ra n c h_n o＞= ’442000’))  and ( x2．c u st_i d  =  x1．c u st_i d )  and ( ( ( x3．occ_co d e  =
x2．occ_d t l_co d e)and ( x3．occ_s ub c l s_co d e  =  x2．occ_s ub c l s_co d e) )  a nd ( x3．occ_c l ass_c o d e  =  x2．occ_c l ass_co d e) )
在用户目录下，生成s q ex p l a i n．o u t．s i d 文件
tong1 $ l /u /to n g1
tota l  1448
－ rw － r －  － r －  － － rw － r －  － r －  － － rw － r －  － r －  － － rw － r －  － r －  －
1 to n g 1 1 to n g 1 1 to n g 1 1 to n g 1
i n for m i x  i n for m i x i n for m i x i n for m i x
697 Sep 22 477 Sep 22 2336 Sep 22 1772 Sep 22
s q ex p l a i n ． o u t ． 1279 s q ex p l a i n ． o u t ． 1386 s q ex p l a i n ． o u t ． 628 s q ex p l
a i n ． o u t ． 9159
查看 s q ex p l a i n ． o u t ． s i d 文件，寻高代价或顺序扫描的 s q l ，定位瓶颈 Q UE RY :
－  －  －  －  －  －  －  －
createv i e w “c bp s 8”． acce p t _i n s u r  ( i n s u r _c n tr _n o ，i  _i n fo  _n o ，p o l  _co d e ，i n fo  _p re m i um ，i n s u r  _a mn t ，p ay  _i n terva l ，i n s u r  _exec  _stat ，rev  _i n terva l ，rev  _ a mn t ，b c l k _c l er k _n o ，d c l k _c l er k _n o ，a pp l _b ra n c h _n o ，occ _a dd _a mn t ，h ea l t h _a dd _a mn t ，i n s u r _du r ，va li d _d ate ，i n s u r  _eff d ate ，i n s u r _i d ，i n s u r _num ，i n s u r  _ty p e ，num _of  _i n s u rs ，p ay  _a l  _f l a g ，p ay  _du r ，start  _rev  _age ，i p s n _ n a m e ，i p s n _se q ，i p s n _occ _n o ，i p s n _n at i o n ，i p s n _se x ，i p s n _b i t h _d ate ，i p s n _m arr _stat ，h l d _n a m e ，h l d _se q ，h l d _occ _n o ，h l d _n a -t i o n ，h l d _b i t h _d ate ，h l d _m arr _stat ，rev _va ry _p ct ，b o uu s _d e li v _m t h ，pp ay _cv ，i n s u r _yea r ，p ay _p re m _num ，p ay _p re m _tota l ，rec _p re m _d ate ，rec _i npu t _d ate ，p ay _p re m ，p ay _ty p e _co d e ，rev _i te m _co d e ，occ _co d e ，d e p t _n o ，i n s u r _sto r ) se l ect  x 0． i n s u r _c n tr _n o  ，x 0． i _i n fo _n o  ，x 0． p o l _co d e  ，x 0． i n fo _p re m i um ，x 0． i n s u r _a mn t  ，x 0． p ay _i n terva l  ，x 0． i n s u r _e xe c _st
at  ，x 0． rev _i n terva l  ，x 0． rev _a mn t  ，x 0． b c l k _c l er k _n o  ，x 0． d c l k _c l er k _n o  ，x 0． a pp l _b ra n c h _n o  ，x 0． occ _a dd _a mn t  ，x 0． h ea l t h _a dd _a mn t  ，x 0． i n s u r _du r  ，x 0． va li d _d ate  ，x 0． i n s u r _eff d ate  ，x 0． i n s u r _i d ，x 0． i n s u r _num ，x 0． i n s u r _ty p e  ，x 0． num _of _i n s u rs  ，x 0． p ay _a l _f l ag  ，x 0． p ay _du r  ，x 0． start _re v _a ge  ，x 0． i p s n _n a m e  ，x 0． i p s n _se q ，x 0． i p s n _occ _n o  ，x 0． i p s n _n at i o n ，x 0． i p s n _sex  ，x 0． i p s n _b i t h _d ate  ，x 0． i p s n _m arr _stat  ，x 0． h l d _n a m e  ，x 0． h l d _se q ，x 0． h l d _occ _n o  ，x 0． h l d _n at i o n ，x 0． h l d _b i t h _d ate  ，x 0． h l d _m arr _stat  ，x 0． rev _vary _p ct  ，x 0． b o uu s _d e li v _m t h ，x 0． pp ay _cv  ，x 0． i n s u r _yea r  ，x 0． p ay _p re m _num ，x 0． p ay _p re m _tota l  ，x 0． rec _p re m _d ate  ，x 0． rec _i npu t _d ate  ，x 0． p ay _p re m ，x 0． p ay _ty p e _co d e  ，x 0． rev _i te m _co d e  ，x 0． occ _co d e  ，x 0． d e p t _n o  ，x 0． i n s u r _stor  from p i cc 1: " a1gd" ．  acce p t _i n s u r
as  x0 where ( ( x 0． a pp l _b ra n c h _n o  ＞  =  ’440300’) ＜  =  ’4403FF ’) )  ;
E st i m ate d Cost : 2147483647
E st i m ate d # of Rows R et u r n e d : 2147483647
1) c bp s 8． age n cy _re g _t b l : S E Q UEN T I AL SCAN
F il ters : ’P A ’ =  ’O A ’
2) c bp s 8． age n cy _fra m e : S E Q UEN T I AL SCAN NESTED LOOP J O I N
Onmode-Y s i d 0 关闭动态分析
AND ( x 0． a pp l _b ra n c h _n o  ■
联合索引 a ，b ，c
■ 确保 a 是不同值最多的字段
■
建好索引，必须 update  stat i st i cs  3) 确保 s q l  走在正确的索引上
应用调优的原则
2 1) 2) 消除对大表的顺序扫描 建立合适的索引
2 期
郑添健，等: 基于 I n for m i x  数据库典型低效率 SQL 语句的分析及优化方法 25
YYYY ) )
注意: exten d 函数默认扩展为零点零分零秒( YYYY-MM-DD 00: 00: 00 ) ，要注意日期区间( 尤其
是“日“
) 的选择。 ■
i n for m i x  指定查询索引功能
Se l ect  { + i nd ex  ( tab na me i d x n a m e ) } …
<  w h ere ...
■ 除了 i nd ex  关键字，还有 avo i d
_f u ll 、avo i d _i n- d ex 、f u ll 、i nd e x _a ll  4 种( 共 5 种) 指定
■
及时更新统计信息
2) 对 date 型字段使用 year 函数
Y ea r ( d ate _co l ) = YYYY  应改成
d at
e _ co l  betwee n mdy ( 1，1，YYYY )  and mdy ( 12，31，YYYY )
3) 对字符型字段使用下标［］表达式
■ Upd ate  stat i st i cs  h i g h  | m e d i um  | l ow
■
Upd ate  stat i st i cs  for ta b l e
■ Upd ate  stat i st i cs  for ta b l e ( co ll ，co l 2l …)
如 where m gr _b ra n c h _n o ［1，4］ = 应改成
where m gr _b ra n c h _n o b etwee n “441400”a nd “4413ff ”
4413 典型低效 SQL 分析及优化方法
3 4) 对字符型字段使用数值进行匹配
3． 1  对索引字段使用函数进行匹配 se l ect ...
from st d _co n tract  x0 ，m i o _l og  x1 ，p o li cy  x 2 where DATE ( x 1． m i o  _l og  _upd _t i m e ) ＜  = MD Y ( MONTH ( 1) ，DAY ( 15) ，YEAR  ( 2005) )
AND DATE ( x 1． m i o  _ l og  _ upd _ t i m e  )  ＞  = MD Y ( 1，1，YEAR  (  2005) ) AND x 1． c n tr _n o  =  x 0． c n tr _n o
分析及优化策略与措施: 1) 在查询 wh ere 子句中，任何对列的加工处理 都将导致表扫描，包括:
■ 数据库函数 ■
计算表达式
se l ect  *  from age n t _tra n s  a  where a ． o  _ age n t  _ n o  =  t  _ age n t  _ re g  _ t b l ． age n t  _ re g _n o
and a ． o _b ra n c h _n o  =  t _age n t _re g  _t b l ． b ra n c h _ no
其中: age n t _tra n s ． o _a ge n t _n o  数据类型为 char t _age n t _re g _t b l ． age n t _reg  _n o  数据类型为 d ec i - m a l
5) 分析
用一个数值作条件去检索一个字符型的字段时，会在字符型字段上实施强制类型转换，将字符转化为 asc ii  码值，与数值比较，相当于在索引字段加函数，等同于低效 SQL1 中介绍的情况，因此导致索引作废，无论 upda te 多少次 stat i st i cs 、设怎样的数据库参数，结果都一样的顺序扫描。 3． 3  优化方法与措施:
■ 对于字符型( c h ar ，varc h ar ) 字段，查询的 s q l
条件，务必以“”或‘’标明为字符键值;
■
如果是程序变量为查询条件，则变量一定要
定义为字符型;
■
在数据库设计时，也应该考虑到这个因素，同
义字段的数据库表中，都应该定义为相同的数据类 型。
例:
2) 因为对列的任何操作结果都是在 SQL 运行 时逐列计算得到的，因此它不得不进行表搜索，而无 法使用该列上面的索引; 如果这些结果在查询编译 时就能得到，那么就可以被 SQL 优化器优化，使用 索引，避免表搜索;
3) 构建查询 SQL 时要尽量避免对列使用函数 及表达式
4) 可以对索引上的函数建立索引 3． 2  常见的列函数使用情况
1) 对 d atet i m e  型字段使用 date 函数
Where date ( d atet i m e  _ co l  ) YYYY ) 应改
成: = mdy ( MM ，DD ， 1． 2． 3． 4． √w h ere  co l _c h ar  = “
123” × where co l _c h ar  = ? 123 √d ef i n e  l _str  varc h ar ( )
se l ect ..... where co l _c h ar  = l _str
Where d atet i m e _co l  ＜ e xte nd ( m y d ( MM ，DD + 1， YYYY ) )
＞    = extend ( mdy ( MM ，DD ，
and d atat i m e  _ co l

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。