从 SQL优化角度对医院信息系统进行优化

.医疗设备信息医院数字化从SQL优化角度对医院信息系统进行优化周鸾杰',宋传军2,周宝林3(1.大庆市人民医院哈尔滨医科大学附属第五医院,黑龙江大庆163311;2.大庆油田总医院，黑龙江大庆163311; 3.大庆市交通局，黑龙江大庆163316)[摘要]医院信息系统 (HIS)中的Oracle数据库系统,其应用程序中SQL语句占了很大的比例。本文主要介绍几种优化SQL语句的方法与实践技巧,来提高医院信息系统的传输效率。[关键词]医院信息系统(HIS);Oracle数据库; SQL语句优化[中图分类号]TP311.132.3 [文献标志码]A[文章编号]1007 -7510(2007)05 - 0023 -03Optimization of HIS from the Angle of SQLZHOU Luan -jie' , SONG Chuan -jun', ZHOU Bao - lin'(1. Daqing Municipal People' s Hospital, Daqing Heilongjiang 163311, China; 2. General Hospital of Daqing Oil - field,Daqing Heilongjiang 163311, China ;3. Daqing Communication Bureau, Daqing Heilongiang 163316, China)Abstract: Describes mainly several ways and practical skill of SQL tuning to improve the transmitting efficiency of HIS.Key words: hospital information system; Oracle database; SQL tuning1 SQL语句优化的意义可以通过如下几种方法。2.1人工检查SQL作为-种结构化查询语言，其处理过程首先通过0-人工检查即把SQL语句编写好了以后，首先对其进行检RACLE的SQL/PLUS界面或者应用程序传送到ORACLE查,以确保其基本有效或者是正确的，这要求开发人员有一定服务器系统中，进行处理，所有的语句分为三个阶段:语句分的经验。如在某HIS中,在药品库存盘点模块中,有如下的语析;语句执行和读取数据。语句分析是最花时间的处理过程,它句:是将SQL语句自底向上进行分析。将保留字与非保留字分开SELECT * FROMXT . YPBZ WHERE ZD-来查其语法是否正确,若正确则下一步根据数据字典中的统计DH=' 1007. UNION SELECT *FROM XT .信息确定运行方案,或是基于代价的,或是基于规则的。总之,YPBZ WHEREJBWXS=1选择代价最小，执行最快的方案，并且同时选择数据访问路作如下修改:径。语句的执行即句法分析后的SQL语句生成-些子命令 ,根SELECT *;据确定的运行方案和数据访问路径将这些命令放入系统的各FROM XT_ YPBZ WHERE ZDDH=，1007*部分。读取数据,即将那些需要的,合乎条件的数据取出来返回AND JBDWSH=1;界面。下面，先提出几种判断SQL语句效率不高的方法，用这两句执行所得的结果是一样的,执行的过程却不一样。HIS系统以前的语句来对比分析,指出哪些语句是实质性效率第二个要比第-一个执行效率快得多。总之,人工检查要有一定不高的。并介绍ORACLE数据库自带的基于代价与基于开销的编程经验。的SQL语句的比较。最后,提出几种优化SQL语句的方法与2.2用EXPLAIN PLAN统计实践技巧。人工检查一般对比较复杂的语句无能为力,这时候可以采用ORACLE里面的EXPLAIN PLAN命令,对一些信息进2几种SQL语句效率不高的检查方法及其优化方法行统计,格式如下:CTATEMENT- ID= "中国煤化工首先要明确-一点，并不是所有的SQL语句都要去优化一BQYH下,如果语句本身很简单,则再优化也没有太大意义。这里所说:THCN MH GRSELECT DIS-的优化是针对可能效率不太高的语句。检查语句是否要优化TINCT XMMC,XMDH,LEVEL;收稿日期:2006-07- 1122卷5期柴2007.5.●23.医院数字化医疗设备信怠FROM XT - MLSZ START WITHXMDH=SELECT HXRQ, SUM(XMJE)PRIORFBDH;FROM ZY - SFXM GROUP BY HXRQ(55秒)ORDERBYLEVELDESC;上述HXRQ上有大量的重复值,在非群集索引下,数据在然后察看一下PLAN_TABLE中的内容，在内容中OP-物理上随机存放在数据页上,在范围查找时，必须执行- -次表ERATION列表示进行的操作，这条SQL语句有两个操作,扫描才能找到这一范围内的全部行。一个是SELECT操作,一个是表的访问, OPTION列是对方(2)在HXRQ.上的一个群集索引:案选择描述。POSITION列为 系列开销列，这个开销的度量SELECT COUNT( * )FROM ZY - SFXM尺度可以说是1/O开销,要知道开销的大小,要对比多条SQLWHERE HXRQ》’' 9990901’ AND WCKSIN( '语句进行。4222','3961')(1秒)2.3使用SET AUTOTRACE在群集索引下，数据在物理上按顺序在数据页上，重复值用EXPLAIN PLAN来检查语句是否有效，其实是一种也排列在一-起,因而在范围查找时,可以先找到这个范围的起手工方法,用SETAUTOTRACE则是- -种自动完成的，他每始点及终点,只在这个范围内扫描数据页,避免了大范围扫描,执行一条SQL语句时，都对其执行情况进行统计,并存入提高了查询速度。PLAN_ TABLE表中,应用程序收集性能统计数据时，应设置(3)在WCKS,HXRQ,XMJES上的组合索引:初始化参数TIMES. STATISTICS= TRUE,因为这是一个SELECT COUNT(*) FROM ZY_SFXM动态参数，可以在数据库打开时,使用ALTER SYSTEM或WHERE HXRQ>' 20010130' AND HXRQ<'ALTER SESSION命令修改它，为了避免不必要的开销,应20021020'该在完成之后将TIME STATISTICS设置为FALSE。AND XMJE>200 (26 秒)3基于代价与基于规则的SQL语句之间的比较检查到不好的SQL语句后，下面就是要确定是基于代价WHERE HXRQ>' 19990901' AND WCKS IN ('的优化还是基于规则的优化。所谓基于代价(CBO)的优化是指4222' ,'3961')(1秒)依靠统计信息,基于选择代价最小,即执行时间最少,1/0最少上例中这是一个不很合理的组合索引,因为它的前导列是的方案来执行语句。所谓基于规则的优化，即ORACLE首先WCKS,第一条SQL没有引用WCKS ,因此也没有利用上索定义一个规则，它将SQL语句的访问路径分成15个等级,每引;第二个SQL使用了WCKS,且引用的所有列都包含在组一个等级都有-一个权值，权值越小,等级越低, SQL语句执行合索引中,形成了索引覆盖,所以他的速度是非常快的。越快。这样,每遇到一个SQL语句,系统自动对语句进行简单3.2不充分的连接条件分析后,确定其等级,然后按这一等级的访问路径访问。我们通过例子来比较说明:例:表ZY_SFYP(住院收费药SQL语句在KMHIS中的运用在使用SQL语句时,性能品)有7896行,在XMDH上有一个非聚集索引，在YP_ KC-差异在大型的或是复杂的数据库环境中表现得尤为明显,如在SL(药品库存数量)有191122行,在XMDH上有一个非聚集HIS系统中，经过一- 段时间的总结，发现SQL语句的性能比索引，试看在不同的表连接条件下，两个SQL的执行情况。较低下的原因主要来自于不恰当的索引设计,不充分的连接条SELECT SUM(A. XMJE)FROM ZY. SFYP A, YP. KC-件和不可优化的WHERE子句及其不恰当的语句操作等，在SL B WHERE A. XMDH=B.X MDH (20 秒)将SQL改对它们进行适当的优化后,其运行速度有了明显的提高，下面为:将从几个方面结合实际应用分别进行说明。测试环境:SELECT SUM(A. XMJE) FROM ZY . SFYP A,主机hp ld pro,主频330M，内存128M;操作系统YP_KCSLBnt4. 0,数据库oracle8.0。WHER EA. XMDH= B.X MDH AND A. XMDH =3.1不合理的索引设计B.XMDH (<1 秒)例:表zY _SFXM有197万行,试看在不同的索引下，下可见，只有充分的连接条件，真正的最佳方案才会被执面几个SQL的运行情况:(1)在HXRQ上建有一个非群集索引:3.3关于WHERE子句SELECT COUNT(*) FROM ZY-SFXM中国煤化工有恰当的索引，但执行速WHERE HXRQ>' 20010130' AND HXRQ< '20010220' AND XMJE>200 (25秒)//核销日期,项目金额，YHCNMH GM完成科室,住院收费项目，门诊收费项目。WHERE XMJE/ 30<1000 (11 秒) WHERE子句中对.24.22卷5期骤2007.5医疗设备体&医院数字化列的任何操作结果都是在SQL运行时逐列计算得到的，因此4总结它不得不进行全表搜索，而没有使用该列上的索引;如果这些从上面这些例子可以看出，SQL 优化的实质是在结果正结果在查询编译时发现,那么就可以被查询优化器优化,使用确的前提下,用优化器可以识别的语句，充分利用索引,减少表列上的索引,避免全表搜索。扫描的I/ O次数,尽量避免表搜索的发生,其实SQL的优化3.4用(NOT) EXIST替代NOT IN是一个复杂的过程，上述这些只是在SQL语句应用时的一些语句1具体实例和平时应用的总结。SELECT XMFL，XMDHFROMXT-CSsZ[参考文献]WHERE XMDH NOT IN(SELECTXMDH FROM XT -[1 ]Gaja krishnavaidynatha, kirtikumar deshpande johnXMSZ)kostelac Oracle performance tuning 101[J]. Mcgraw-语句2hill education (asia) publication 2004(1): 72- 74.SELECTXMFL，XMDH FROM XT - CSSZ[2]Mullner, Ross M. The Internet and Healthcare&WHERE NOT EXISTScolon; Oppoutunities and Challenges [J].Journal of(SELECT XMDH FROM XT. XMD H WHEREmedical systems, 2002(6) : 54 - 57.XT_CSSZ.XMDH= XT - XMSZ. XMDH);可见，语句2要[3]Eyal aronoff, evin loney , noorali sonawalla, Oracle比语句1的执行性能好很多.因为语句1中对XT_ XMSZ进advanced tuning & administrator[J ]. oracle press 2000行了全表扫描，这是很浪费时间的操作.而且语句1中没有用(1):44 -46.到EMP的索引。而2中的语句对XT. XMSZ进行的是在条[4]刘晓霞,王联华,徐海琛,等.oracle8优化技术[M].北京:件下的有限扫描。机械工业出版社,2001:56 - 78.3.5在海量查询时尽量少用格式转换[5]刘刚,曲建华,等.医院信息系统oracle服务器端数据库维如用在药品采购账本的批号中用护例析[J].医疗设备信息,2002(3):5.WHERE YP _CGZB. YPPH= YP_CGZB. YPPH;[6]周鸾杰,等.从Oracle体系结构方面对医院信息系统进行而不用优化[J].医疗设备信息,2006(5):32 - 33.WHERETO . NUMBER(SUBSTR(YP[7]刘伟,杜薇。关于oracle关系数据库性能调整的研究[J].CGZB. YPPH, INSTR(YP_ CGZB. YPPH,'.')-1)= TO.微型机与应用, 2001(10): 47-49.NUMBER(SUBSTR(YP. CGZB. YPPH，INSTR(YP[8]何泽恒,吕建波,李建军.基于oracle数据库的信息系统优CGZB. YPPH,'.')-1)。化设计[J].哈尔滨商业大学学报,2002(2:)23 -25.★(上接第57页)2.3.1冠心病早期诊断好的经济效益和社会效益;也提高了我院的科研水平，对各级2.3.2一次性完成心肌血流灌注、代谢和冠状动脉成像及其科研课题的申请,人才的引进等都将有很大的促进作用。融合图像,同时判定心肌存活性及缺血位置和供血血管的解剖结构[1]潘屏南,等.现代大型医用设备[M].北京:中国医药科技2.3.3心脏、冠状动脉血管二维和三维解剖成像。对心脏和冠出版社,2002.状动脉的结构和功能评价,鉴别冠状动脉血管内软、硬斑块形[2]尚辉. PET/CT 的原理及临床应用[J].医疗设备信息,成,并可对钙化斑块定量分析。2005, 20(11):20.2.3.4对心肌心内 膜下心梗进行诊断和评估。[3]林景辉。核医学[M].北京:北京医科大学出版社,2002.2.3.5对血管内支架的术后评价。[4]钱英. PET/CT的最新进展[J].医疗设备信息,2005(5): .2.4神经系统疾病的应用31.2.4.1神经受体, 神经递质的显像。[5]李天然,等.功能影像设备PET/CT[J].医疗卫生装备,2.4.2 癫痫的诊断、鉴别诊断和治疗效果的评估。2005(12):27.2.4.3帕金森氏病、 早老性痴呆诊断、病因的鉴别。通过神经[6]倪萍PFT/CT- 核医学影像发展的未来[J].医疗设备信.递质显像剂、受体显像剂等新型显像剂的应用对这类疾病进行中国煤化工研究。[7]黄RYHCN M H G[J].医疗卫生装备，通过PET -CT的引进,提高了我院的肿瘤诊治水平,对2004(9):41 -42.肿瘤的早期诊断、选择合适的治疗方案起到指导作用,取得良22卷5期骤2007.5.25.