医院数据库系统SQL语句优化分析
加入技术交流群
一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By 个Having的开销小，查询快。对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快。
2.5 注意细节
一般不要用如下的字句: “＜＞”, “!=”, “!＞”, “!＜”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500’”，因为他们不走索引全是表扫描。NOT IN会多次扫描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用， “＜＞”, “!=”, “!＞”,等还是不能优化，用不到索引。
不要在WHere字句中的列名加函数，如Convert，substring等,如果必须用函数的时候，创建计算列再创建索引来替代。还可以变通写法：
WHERE SUBSTRING(firstname,1,1) = ‘m’
改为：WHERE firstname like ‘m%’（索引扫描）,但MIN() 和 MAX()能使用到合适的索引。
select * form ZY_FYMX where FYDJ ＞ 3000
分析在此语句中若FYDJ是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。应改为：
select * form ZY_FYMX where FYDJ ＞ 3000.00
2.6 避免相关子查询
一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。
3 SQL案例分析
3.1案例分析一
医院数据库容量为28GB，根据对MS_CF01和MS_CF02的大小统计，其中MS_CF02记录条数为1000万条；发药统计时间一个月，耗时30分钟仍然无法得到结果，严重影响正常业务，遂中止。
原先统计的SQL语句如下：
select sum(MS_CF02.YPSL*MS_CF02.YPDJ*MS_CF02.CFTS) as total
from MS_CF01,MS_CF02
where MS_CF01.CFSB=MS_CF02.CFSB and MS_CF01.CFLX=1
and (MS_CF01.FYBZ=1 or MS_CF01.FYBZ=3)
and MS_CF01.FYRQ＞=”2004.3.1 00:00:00”
and MS_CF01.FYRQ＜=”2004.3.30 00:00:00”
and MS_CF01.ZFPB=0
根据对系统的分析(仅限于MS SQL Server数据库)，给出相应的优化方案，可以在性能上大幅度提高：
select top 1CFSB from MS_CF01 where FYRQ＞=”2004.3.1 00:00:00”
//得到该时间段内最小的CFSB,例如3198724
select top 1CFSB from MS_CF01 where FYRQ＜=”2004.3.30 00:00:00”
order by CFSB desc //得到该时间段内最大的CFSB，例为4178763
select sum(MS_CF02.YPSL*MS_CF02.YPDJ*MS_CF02.CFTS) as total
from MS_CF01,MS_CF02
where MS_CF01.CFSB=MS_CF02.CFSB and MS_CF01.CFLX=1
and MS_CF02.CFSB＞=3198724 and MS_CF02.CFSB＜=4178763
and (MS_CF01.FYBZ=1 or MS_CF01.FYBZ=3)
and MS_CF01.ZFPB=0
所有语句执行完毕后，结果不超过18秒。
3.2案例分析二
医院工作人员反映在药库系统自动计划模块执行速度很慢，有时大约需要半个小时才能跳出窗口。
经过分析发现,在w_yk_plan.cb_auto.clicked() 18行开始的代码如下：
ls_select+= select YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL,0
ls_select+= from YK_TYPK,YK_KCMX
ls_select+= where XTSB=+string(base_info.syscode)+ and DCSL＞0 and GCSL＞DCSL and YKZF=0
ls_select+= and YK_TYPK.YPXH not in (select YPXH from YK_KCMX)
ls_select+= group by YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL
显然是NOT IN语句导致速度很慢,然后把该语句改成:
ls_select+= select YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL,0
ls_select+= from YK_TYPK
ls_select+= where XTSB=+string(base_info.syscode)+ and DCSL＞0 and GCSL＞DCSL and YKZF=0
ls_select+= and not exists ( select YPXH FROM YK_KCMX WHERE YK_TYPK.YPXH = YK_KCMX.YPXH )
ls_select+= group by YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL “
结果5秒内执行完成。
3.3案例分析三
以下SQL的功能是医院用于作日报时显示哪些收费员还没有做过当天的个人日报，速度非常慢，每次都需要好几分钟才能出来数据。
显然这个问题是由于不合理的子查询造成的，经分析后我们把子查询从where子句中去除，语句改成如下后，2秒内数据库出来了。
某些HIS系统中病区系统中医嘱提交、医嘱执行、医技提交速度慢问题原因分析ZY_BQYZ 这个表中出院数据没有转出到ZY_BQYZ_CY 表中。
没有建立正确的索引，SQL 语句本身存在的问题，在医嘱提交中的WHERE子句：
WHERE ( ZY_BQYZ.ZYH = ZY_BRRY.ZYH AND ZY_BRRY.CYPB = 0 )
AND ( ZY_BQYZ.SRKS = :al_hsql)
AND ( ZY_BQYZ.LSBZ=0 OR ZY_BQYZ.LSBZ=2)
AND ( ZY_BQYZ.SYBZ = 0)
AND ( ZY_BQYZ.XMLX＜4 )
AND ( ZY_BQYZ.YZPB=0 )
AND ( ZY_BQYZ.FYSX＜2)
AND ( ZY_BQYZ.JFBZ＜2)
AND ( ZY_BQYZ.YSBZ = 0 OR (ZY_BQYZ.YSBZ = 1 AND ZY_BQYZ.YSTJ = 1))
评论