医院数据库系统SQL语句优化分析

E操作符

LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如like 'a%' 使用索引，like ‘%a’ 不使用索引。用 like ‘%a%’ 查询时，查询耗时和字段值总长度成正比，所以不能用CHAR类型，而是VARCHAR。

2.2 限制返回行

在查询Select语句中用Where字句限制返回的行数，避免表扫描，如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表，后果严重。可以使用TOP语句来限制返回结果。当返回多行数据时，尽可能不使用光标，因为它占用大量的资源，应该使用datastore。

2.3 UNION操作符

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。推荐采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

2.4 Between与IN

Between在某些时候比IN速度更快，Between能够更快地根据索引找到范围。如：

select * from YF_KCMX where YPXH in (12,13)

Select * from YF_KCMX where between 12 and 13

一般在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秒。