论文部分内容阅读
摘要：经过多年的信息化建设，我们医院变成了全面信息化的现代化医院。特别近两年来，医院信息系统（HIS）、实验室信息管理系统（LIS）以及影像归档和通信系统（PACS）系统的上线，大大增加了数据库的数据量，影像归档和通信系统（PACS）的图片传输对网絡速度也提出了很高的要求。因此，SQL语句的优化就显得格外重要了。
关键词：医院信息系统；数据库；SQL
中图分类号：TP393文献标识码：A文章编号：1007-9599 (2012) 02-0000-02
Discussion on the Optimization of SQL Statements in the Hospital Database Systems
Lu Fangjian
(Yangjiang City,Guangdong Province Hospital of People,Yangjiang529500,China)
Abstract:After years of information construction,our hospital has become a comprehensive information technology of a modern hospital.Especially in the past two years,HIS,LIS and PACS system political line,greatly increased the amount of data of the database,and also made high demands on the PACS image transfer speed of the network.Therefore,the optimization of the SQL statement is particularly important.
Keywords:HIS;Database;SQL
医院经过多年的信息化建设，由原来简单的以收费为主、各医技科室独立系统变成了全院全面信息化、甚至看病无纸化的程度。特别近两年来，HIS、LIS以及PACS系统的上线，大大增加了数据库的数据量，PACS系统的图片传输对网络速度也提出了很高的要求，医院一天的业务数据由原来的1G不到，发展成现在的10G以上，而这庞大的数据，每天的运行，都让服务器产生很大的压力。特别在上午十点到十二点这段时间，由于门诊医生站的大量使用，以及医技部门的频繁检查，使得系统运行速度越来越慢，甚至出现超时、假死机甚至数据出错等现象，这就严重影响到医院的正常工作了。当我们在服务器上找寻影响效率原因的时候，发现有些查询语句耗时特别多，使用的频率也不低。经过研讨发现，这些耗时多的语句可以进行优化，优化后的耗时明显降低了。在大量优化语句工作后，总结出了些经验，以此分享。
一、优化的前提：需求分析要尽量详细
数据库模式的设计是数据库设计的基础，要设计出一个好的数据库系统，首先要从需求开始，在符合我们现实条件的前提下，把需要的对数据处理的功能都整合进去，并且要通过不断的修改和反复的测试才能使之完善。
二、优化的核心是规范化的应用
关系模式的规范化，是在数据库的设计周期中，对逻辑结构设计优化的必要步骤。什么是关系规范化呢，它是指按统一标准对关系进行优化，从而最大程度上能消除关系数据库中的数据冗余、添加、删除和修改等操作异常，以提高关系的质量。
当我们没有处理好关系模式的时候，就会出现删除异常、插入异常和冗余度大等问题，所以这一步骤非常重要。
在规范化理论中，关系的各属性是相互关联、互相制约、互相依赖的，成为一个严谨的整体。按属性间相关的关系，可大概分为连接依赖、多值依赖和函数依赖。按函数依赖又可分成传递依赖、完全依赖和部分依赖，由以上的依赖关系可分出五种范式，分别为第1范式、第2范式、第3范式、BC范式和第4范式，如果在关系模式中存在这些范式，就可在不同程度上避免更新异常、插入和冗余等问题。
进行规范化做法是，首先确定数据依赖，按照数据依赖的理论，逐一分析这组关系模式，确定他们属于第几范式。其次进行模式分解，逐步消除非主属性对码的传递依赖和部分依赖。在分解过程当中必须遵守相互独立原则和无损分解原则。
无损分解指的是对关系模式分解时，原关系模型下任一合法的关系值在分解之后应能通过自然联接运算恢复起来。
相互独立是指分解后的新关系之间相互独立，对一个关系内容的修改不应该影响到另一关系。
三、优化技术在查询中的应用
优化技术在查询中的重要性体现得更突出，虽然DBMS也能对查询进行优化，但是查询计划本身的结构却是对查询效率有着从根本上的影响。我们要区分查询计划和查询规划的却别，前者是用户所提交的SQL语句的集合，后者是经过优化处理之后所产生的语句集合。现在大部分数据库产品都是采用基于代价的优化方法，它对查询规划代价的估计，都是根据系统字典表的信息得到来，再择优而取。即使现在的数据库查询优化做得再好，根本的SQL语句差了，效率都无法提高上去的。所以为了查询语句有高的执行效率，我们总结了以下心得：
（一）索引使用的时候要恰到好处
索引，就是为提高查询效而生的，它是重要的数据结构，而IBM的ISAM索引结构就是主流数据库产品所采用的索引结构。其有以下使用原则：
1.在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不建立索引。
2.在频繁进行排序或分组的列上建立索引。
3.在经常进行连接、但没指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。例如在zy_bh0表的“在院情况”列上，仅有“在院”和“出院”两种不同情况的代表值，就不必要使用索引。一旦强行建立索引，不仅提高不了查询效率，反而使更新速度严重降低。当然，当待排序的列有多个，我们还可以在这些列上建立复合索引。
（二）对排序进行简化甚至避免
应当尽量避免或简化对大型表进行重复的排序。为了优化器避免排序的步骤，可以利用索引自动以适当的次序产生输出。
（三）要消除顺序存取大型表行数据的情况
对表的顺序存取，如果是在嵌套查询中，那么其查询效率将会降低好多。对连接的列进行索引是避免这种情况的主要方法。当然使用并集也是一个好办法。
（四）尽量避免出现相关子查询
如果一个列的标签在主查询和where子句中的查询中同时出现，当主查询中的列值改变后，子查询要重新查询一次。当查询嵌套层次越多时，效率就越低，所以要尽量避免子查询。
（五）巧妙避开正规表达式的使用
关于正规表达式，例如LIKE就是支持通配符匹配。要知道通配符匹配起来是非常耗时的。如SELECT*FROM zy_bfyz0 WHERE bhid LIKE“98___”
即便在bhid字段上建了索引，也还是采用顺序扫描法的。当我们把语句改为SELECT*FROM zy_bfyz0r WHERE bhid>“98000”，执行起来就大大不同了，它能利用索引来查询，大大提高了速度。
（六）加速查询还可以使用临时表
在大多数情况下，把表的一个子集创建临时表并进行排序，能加快查询的，因为它能避免多重排序的操作。
（七）减少使用or的次数
一旦使用or，就会引起对全表的扫描，这就使查询效率大大降低了。
（八）不要盲目多提取无用字段
实践证明，使用“select字段1，字段2，字段3........”的提取速度比使用“select*”的要快。
四、案例分析
案例一：药房系统慢。我院中心药房的药师反映在系统自动计划这个功能执行起来特别慢，有时候打开时候甚至超过20分钟，严重影响了工作，当我们分析器结构时，发现了关键字“not in”，通过语法结构转换，用“not exists”代替，结果运行速度得到很大的提升。
案例二：显示收费员个人报表慢。收费处工作人员进入HIS系统中，有这样一个功能，当他们进行做个人日报表时，可以查看到哪些收费人员还没有做当天的日报表，就是这个查看功能的响应速度非常慢，经过对其收sql语句的分析，发现里面存在不合理的子查询，经过语法结构转换，把子查询去掉，结果改功能能在1秒钟内就查处结果了。
案例三：没索引导致很慢。在HIS系统的医生站中，有一个病号信息查询功能，这个界面能通过惟一识别的住院号来查找所有来医院住过院的病人的信息，无论是在院还是出院的，都能查到，这个功能是查找zy_bh0的表，这个表非常庞大。在没有建立索引的情况下，我们查找一个病人需要10分钟才能找到，因为当我们输入一个住院号的时候，需要在zy_bh0这表的上千万条数据中逐一对照，效率非常低；通过建立合理的索引，再通过此功能查找病人，时间已经缩短为0.5秒了。所以说，在大的表中，通过建立合理的索引，查询的效率能得到很大的提高的。
五、小结
通过上面的这些优化数据库sql语句心得的总结，我们发现，其优化的方式虽然简单，但是小小的改动，能提高很大的效率。所以，为了达到数据库高效地运行，我们要认真分析语句中的执行效率问题，通过综合的考虑，具体问题具体分析，不要乱套模式，因为每一个表都有最适合的优化方法，当此表经过一段时间变化后，原来的方法很可能已经不是适合的了，又要重新思考其优化的办法。所以说，数据库的优化要细心，更要有耐心。
参考文献：
[1]陳倩文.医院数据库系统的性能分析及优化经验[J].中国数字医学,2009,4(8):66-68
[2]马辰.医院信息系统数据库的性能优化和故障维修[J].医疗装备,2009,6:69-70
[3]王勇.基于SQL数据库的性能优化问题分析[J].电脑知识与技术,2009,15:1004-1007
[4]殷美桂.谈关系数据库查询优化方法[J].电脑知识与技术,2008,16:1188-1189
[5]骆正云,宋念东,俞国霖.医院HIS系统SQL SERVER数据库性能优化[J].医疗卫生装备,2005,26(2):32-33
[6]刘兆良,陈中举,刘方.SQL SERVER数据库查询优化技术[J].科技信息,2007,24:377-378
[作者简介]卢方建（1986.10-），男，籍贯：广东阳江，职称：软件设计工程师，学历：本科，学位：学士。
关键词：医院信息系统；数据库；SQL
中图分类号：TP393文献标识码：A文章编号：1007-9599 (2012) 02-0000-02
Discussion on the Optimization of SQL Statements in the Hospital Database Systems
Lu Fangjian
(Yangjiang City,Guangdong Province Hospital of People,Yangjiang529500,China)
Abstract:After years of information construction,our hospital has become a comprehensive information technology of a modern hospital.Especially in the past two years,HIS,LIS and PACS system political line,greatly increased the amount of data of the database,and also made high demands on the PACS image transfer speed of the network.Therefore,the optimization of the SQL statement is particularly important.
Keywords:HIS;Database;SQL
医院经过多年的信息化建设，由原来简单的以收费为主、各医技科室独立系统变成了全院全面信息化、甚至看病无纸化的程度。特别近两年来，HIS、LIS以及PACS系统的上线，大大增加了数据库的数据量，PACS系统的图片传输对网络速度也提出了很高的要求，医院一天的业务数据由原来的1G不到，发展成现在的10G以上，而这庞大的数据，每天的运行，都让服务器产生很大的压力。特别在上午十点到十二点这段时间，由于门诊医生站的大量使用，以及医技部门的频繁检查，使得系统运行速度越来越慢，甚至出现超时、假死机甚至数据出错等现象，这就严重影响到医院的正常工作了。当我们在服务器上找寻影响效率原因的时候，发现有些查询语句耗时特别多，使用的频率也不低。经过研讨发现，这些耗时多的语句可以进行优化，优化后的耗时明显降低了。在大量优化语句工作后，总结出了些经验，以此分享。
一、优化的前提：需求分析要尽量详细
数据库模式的设计是数据库设计的基础，要设计出一个好的数据库系统，首先要从需求开始，在符合我们现实条件的前提下，把需要的对数据处理的功能都整合进去，并且要通过不断的修改和反复的测试才能使之完善。
二、优化的核心是规范化的应用
关系模式的规范化，是在数据库的设计周期中，对逻辑结构设计优化的必要步骤。什么是关系规范化呢，它是指按统一标准对关系进行优化，从而最大程度上能消除关系数据库中的数据冗余、添加、删除和修改等操作异常，以提高关系的质量。
当我们没有处理好关系模式的时候，就会出现删除异常、插入异常和冗余度大等问题，所以这一步骤非常重要。
在规范化理论中，关系的各属性是相互关联、互相制约、互相依赖的，成为一个严谨的整体。按属性间相关的关系，可大概分为连接依赖、多值依赖和函数依赖。按函数依赖又可分成传递依赖、完全依赖和部分依赖，由以上的依赖关系可分出五种范式，分别为第1范式、第2范式、第3范式、BC范式和第4范式，如果在关系模式中存在这些范式，就可在不同程度上避免更新异常、插入和冗余等问题。
进行规范化做法是，首先确定数据依赖，按照数据依赖的理论，逐一分析这组关系模式，确定他们属于第几范式。其次进行模式分解，逐步消除非主属性对码的传递依赖和部分依赖。在分解过程当中必须遵守相互独立原则和无损分解原则。
无损分解指的是对关系模式分解时，原关系模型下任一合法的关系值在分解之后应能通过自然联接运算恢复起来。
相互独立是指分解后的新关系之间相互独立，对一个关系内容的修改不应该影响到另一关系。
三、优化技术在查询中的应用
优化技术在查询中的重要性体现得更突出，虽然DBMS也能对查询进行优化，但是查询计划本身的结构却是对查询效率有着从根本上的影响。我们要区分查询计划和查询规划的却别，前者是用户所提交的SQL语句的集合，后者是经过优化处理之后所产生的语句集合。现在大部分数据库产品都是采用基于代价的优化方法，它对查询规划代价的估计，都是根据系统字典表的信息得到来，再择优而取。即使现在的数据库查询优化做得再好，根本的SQL语句差了，效率都无法提高上去的。所以为了查询语句有高的执行效率，我们总结了以下心得：
（一）索引使用的时候要恰到好处
索引，就是为提高查询效而生的，它是重要的数据结构，而IBM的ISAM索引结构就是主流数据库产品所采用的索引结构。其有以下使用原则：
1.在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不建立索引。
2.在频繁进行排序或分组的列上建立索引。
3.在经常进行连接、但没指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。例如在zy_bh0表的“在院情况”列上，仅有“在院”和“出院”两种不同情况的代表值，就不必要使用索引。一旦强行建立索引，不仅提高不了查询效率，反而使更新速度严重降低。当然，当待排序的列有多个，我们还可以在这些列上建立复合索引。
（二）对排序进行简化甚至避免
应当尽量避免或简化对大型表进行重复的排序。为了优化器避免排序的步骤，可以利用索引自动以适当的次序产生输出。
（三）要消除顺序存取大型表行数据的情况
对表的顺序存取，如果是在嵌套查询中，那么其查询效率将会降低好多。对连接的列进行索引是避免这种情况的主要方法。当然使用并集也是一个好办法。
（四）尽量避免出现相关子查询
如果一个列的标签在主查询和where子句中的查询中同时出现，当主查询中的列值改变后，子查询要重新查询一次。当查询嵌套层次越多时，效率就越低，所以要尽量避免子查询。
（五）巧妙避开正规表达式的使用
关于正规表达式，例如LIKE就是支持通配符匹配。要知道通配符匹配起来是非常耗时的。如SELECT*FROM zy_bfyz0 WHERE bhid LIKE“98___”
即便在bhid字段上建了索引，也还是采用顺序扫描法的。当我们把语句改为SELECT*FROM zy_bfyz0r WHERE bhid>“98000”，执行起来就大大不同了，它能利用索引来查询，大大提高了速度。
（六）加速查询还可以使用临时表
在大多数情况下，把表的一个子集创建临时表并进行排序，能加快查询的，因为它能避免多重排序的操作。
（七）减少使用or的次数
一旦使用or，就会引起对全表的扫描，这就使查询效率大大降低了。
（八）不要盲目多提取无用字段
实践证明，使用“select字段1，字段2，字段3........”的提取速度比使用“select*”的要快。
四、案例分析
案例一：药房系统慢。我院中心药房的药师反映在系统自动计划这个功能执行起来特别慢，有时候打开时候甚至超过20分钟，严重影响了工作，当我们分析器结构时，发现了关键字“not in”，通过语法结构转换，用“not exists”代替，结果运行速度得到很大的提升。
案例二：显示收费员个人报表慢。收费处工作人员进入HIS系统中，有这样一个功能，当他们进行做个人日报表时，可以查看到哪些收费人员还没有做当天的日报表，就是这个查看功能的响应速度非常慢，经过对其收sql语句的分析，发现里面存在不合理的子查询，经过语法结构转换，把子查询去掉，结果改功能能在1秒钟内就查处结果了。
案例三：没索引导致很慢。在HIS系统的医生站中，有一个病号信息查询功能，这个界面能通过惟一识别的住院号来查找所有来医院住过院的病人的信息，无论是在院还是出院的，都能查到，这个功能是查找zy_bh0的表，这个表非常庞大。在没有建立索引的情况下，我们查找一个病人需要10分钟才能找到，因为当我们输入一个住院号的时候，需要在zy_bh0这表的上千万条数据中逐一对照，效率非常低；通过建立合理的索引，再通过此功能查找病人，时间已经缩短为0.5秒了。所以说，在大的表中，通过建立合理的索引，查询的效率能得到很大的提高的。
五、小结
通过上面的这些优化数据库sql语句心得的总结，我们发现，其优化的方式虽然简单，但是小小的改动，能提高很大的效率。所以，为了达到数据库高效地运行，我们要认真分析语句中的执行效率问题，通过综合的考虑，具体问题具体分析，不要乱套模式，因为每一个表都有最适合的优化方法，当此表经过一段时间变化后，原来的方法很可能已经不是适合的了，又要重新思考其优化的办法。所以说，数据库的优化要细心，更要有耐心。
参考文献：
[1]陳倩文.医院数据库系统的性能分析及优化经验[J].中国数字医学,2009,4(8):66-68
[2]马辰.医院信息系统数据库的性能优化和故障维修[J].医疗装备,2009,6:69-70
[3]王勇.基于SQL数据库的性能优化问题分析[J].电脑知识与技术,2009,15:1004-1007
[4]殷美桂.谈关系数据库查询优化方法[J].电脑知识与技术,2008,16:1188-1189
[5]骆正云,宋念东,俞国霖.医院HIS系统SQL SERVER数据库性能优化[J].医疗卫生装备,2005,26(2):32-33
[6]刘兆良,陈中举,刘方.SQL SERVER数据库查询优化技术[J].科技信息,2007,24:377-378
[作者简介]卢方建（1986.10-），男，籍贯：广东阳江，职称：软件设计工程师，学历：本科，学位：学士。