浅谈如何优化SQL语句提高数据库系统效率

摘要：对于大型的企业或部门来说，每天都需要处理大量的数据业务，数据库系统的设计变的异常复杂，而数据库的性能的好坏直接影响到各项业务能否顺利进行。然而数据库优化涉及到许多方面，如：良好的系统和数据库设计；优质的SQL语句编写；合适的数据表索引设计等，甚至包括各种硬件因素，如：网络性能；服务器的性能；操作系统的性能等。如果只想通过升级硬件系统来提升性能将会付出很大的经济代价，通过优化SQL语句的编写提升数据库性能会是一个不错的选择。

关键词：数据库设计;数据库系统;数据库性能;SQL语句;优化

中图分类号：TP311文献标识码：A文章编号：1009-3044(2012)05-0988-03

SQL（Structured Query Language）结构化查询语言，是一种高级的非过程化编程语言，是沟通数据库服务器和客户端的重要工具，它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有在不同数据库系统上使用相同的SQL语言作为数据输入与管理的接口。它以记录集合作为操作对象，所有SQL语句接受集合作为输入，返回集合作为输出，这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入，所以SQL语句可以嵌套，这使它具有极大的灵活性和强大的功能，在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的，这也意味着用SQL语言可以写出非常复杂的语句。然而SQL语句的不同写法却会对数据库系统运行带来完全不同的效果，本文就如何优化SQL语句提升系统性能作一深入探讨。

1优化SQL语句的好处

对于大部分基于数据库的应用程序来说，多数都是C/S或B/S架构，其与数据库的联系是通过客户端嵌入的SQL语句或调用数据库上的过程实现的。所以SQL语句质量的好坏会影响整个系统，因此优化SQL语句有以下几个好处：一是提高系统的运行效率，减少数据库死锁的风险；二是降低系统对硬件资源的消耗，节约投资；三是强化系统源代码的可读性，降低程序员修改程序的劳动强度。

2合理使用数据库索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化器，提高系统的性能。

然而增加索引也有许多不利的方面。第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

因此在写SQL语句时要合理使用数据库索引，这会让你的数据库运行效果事半功倍。

3优化SQL语句的方法

3.1合理的使用各种操作符

首先要注意操作符的使用方法，虽然有时候不同的操作符实现的功能是一样的，但执行的效率却相差很多，这对于数据库的初学者是常常容易忽视的。

3.1.1 IN（NOT IN）与EXISTS（NOT EXISTS）操作符的区别

我们在调用数据的时候会将一列和一系列值相比较，最简单的办法就是在WHERE子句中使用子查询，而在WHERE子句中有两种方式的子查询。下面以zy_dxsfls，zy_brzlls两个表为例，如下所示：

第一种方式使用IN操作符，

Select a.blh,a.sfks,a.sfrq,a.yzm,a.zfje,a.xzys

From zy_dxsfls a

where a.blh in (select b.blh from zy_brzlls b where b.csrq>= ’1970-1-1’)；

第二种方式使用EXIST操作符，

Select a.blh,a.sfks,a.sfrq,a.yzm,a.zfje,a.xzys

From zy_dxsfls a

where exists (select 1 from zy_brzlls b where b.blh=a.blh and b.csrq>=’1970-1-1’)。

虽然两种方法得出的结果是一样的，但是使用的时候却是有区别的。IN操作符适合于zy_brzlls表大而zy_dxsfls表小的情况，而EXISTS操作符适合于zy_brzlls表小而zy_dxsfls表大的情况。因此使用的时候应视情况而定，不能盲目使用，NOT IN操作符和NOT EXISTS操作符也是一样的。

2.1.2巧用比较条件（>、>=、

以大于操作符和大于等于操作符为例，它们能实现相同的功能，但是对它们优化之后会有不同的效果。如一个表有100万记录，有一个数值型字段“A”，A=0的记录有30万行，A=1的记录30万行，A=2的记录有39万行，A=3的记录有1万行。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时数据库会先找出为2的记录再进行比较，而A>=3时数据库则直接找到等于3的记录，所以选择好的比较条件会提高数据库的执行效率。

3.1.3尽量少用UNION操作符

UNION操作符是用来合并查询结果的，最常见的是过程表与历史表的合并。在执行该操作时数据库先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致数据库系统效率下降。不过可以采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

3.1.4变相使用IS NULL（IS NOT NULL）操作符

NULL值即空值的意思，根据数据库的特性，是不能使用包含NULL值的字段作为索引的，即使对该字段建立了索引，只要某行上面有NULL值，该字段就不能使用索引。但是我们可以使用变相方法来表示NULL值，如将某行上的NULL值用0表示，这样WHERE条件语句“a is null”就可以改成“a=’0’”来实现；另外还可以设置字段不允许为空，而用一个缺省值代替空值，如一个时间类型的字段，可以将默认时间设为“1900-01-01”来表示空值。

3.1.5合理使用LIKE操作符

LIKE操作符是可以使用通配符查询的语句，里面的通配符组合可以达到任意多个，但是如果用得不好则会产生性能上的问题，如yzm like ’%阿莫西林%’这种查询不会使用数据库索引，而yzm like ’阿莫西林%’则会使用数据库索引。用一个实例测试，如表1所示。

表1

3.1.6避免在WHERE子句中做函数计算

这样做会使数据库在表的每行上进行运算，从而导致该列的索引不能被使用而触发全表扫描。我们可以从下列对比中看出效果，如表2所示。

表2

还有很多操作符都不能利用数据库索引，如：NOT、!=、、!>、!

3.2合理的书写SQL语句

3.2.1注意SQL语句中空格和字母的大小写

首先让我们看一个示例，如表3所示。

以上四个SQL语句分析整理之后产生的结果及执行的时间是一样的，但是从数据库共享内存的原理，可以得出数据库对每个SQL语句都会进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同，则数据库只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL语句的时间，而且可以减少共享内存重复的信息。

表3

3.2.2注意WHERE子句后面的条件顺序

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。以下面两条语句为例：Select id From Emp Where emp? no=1000；Select id From Emp Where sal>=1000 and empno=1000”条件在记录集内比率为99%，而“empno=1000的记录M条，接着处理条件empno

3.2.3按需提取字段，避免使用“SELECT *”

首先，让我们看一个示例，有一张表zy_dxsfls拥有1500万行数据，测试结果如表4所示。

表4

从上面的结果我们可以看出，数据执行的效率相差很大，这是由以下两个方面造成的：（1）“SELECT *”提取了所有的数据，数据容量大（字节数多），查询效率自然会降低，所以字段的提取一定要按照“用多少提多少”的原则，避免使用“SELECT *”这样的操作，但提升的速度还要看你舍弃的字段的大小来判断。(2)“SELECT *”的最大问题是影响查询的方式，数据库主要使用索引去查询你需要的数据，当索引包含你请求查询的所有字段时，数据库就不会再去表中查询，这个概念称作索引覆盖。因为索引扫描比其他方式的扫描快很多。

3.3适当的使用过渡表

把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。以查询在院病人费用超过1万元的医嘱明细为例，如下所示：

Select a.BLH,b.SFKS,b.SFRQ,b.YZM,b.ZFJE,……From zy_brzl a,zy_dxsf b

where a.blh=b.blh and a.zt=’在院’ and a.zfje>=10000

Order by a.blh；

如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序。SQL语句可以改为：

Select a.BLH,b.SFKS,b.SFRQ,b.YZM,b.ZFJE,……Into zy_brzl_zy

From zy_brzl a,zy_dxsf b

Where a.blh=b.blh and a.zt=’在院’

Order by a.blh；

然后以下面的方式在临时表中查询:Select * From zy_brzl_zy Where zfje >=10000；

由于临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘的I/O操作，所以查询工作量可以得到大幅减少。但是需要注意临时表创建后不会反映主表的修改，在主表中数据频繁修改的情况下，不要丢失数据。

4应用实例

以作者所在单位为例，原本使用的是IBM3650服务器，100多个客户端，应用程序比较单一，整个业务系统运行还算平稳。可是仅仅过了2年多的时间，单位业务量便出现急剧增长，应用程序不断扩充，现在已经拥有了300个客户端，而系统的运行也出现了问题，客户端的应用程序会不时的出现“卡机”现象，数据库还会出现“死锁”的情况。经过深入研究发现，每次出现问题都是一些特定的SQL语句造成的，后来经过大规模的优化SQL语句和数据库索引使问题得到了圆满的解决，避免了硬件资源在未到使用寿命之前就被更换掉的命运，从而为单位节约了大量人力和物力，提高了经济效益。

5总结

对于拥有几百甚至上千台客户端的企业或部门来说，每天都有大量的并发业务数据，如果应用程序中的SQL语句写的不好，就需要消耗更多的硬件资源来满足系统的运行，从而导致企业或部门花费更多的人力和财力去提升系统硬件。因此优秀的SQL语句将为整个业务系统的执行效率带来很大的提高，减少数据库死锁的风险，降低数据库维护成本，为大型的企业或部门带来经济效益的提升。

参考文献：

[1]范秀平,尚武.SQL语法范例手册[M].北京:科学出版社,2007.

[2]蒙祖强.SQL Server 2005应用开发大全[M].北京:清华大学出版社,2007.

[3]黄开枝,康会光,于军.SQL Server 2005中文版基础教程[M].北京:清华大学出版社,2007.

[4]韩朝军,梁冰,刘莹.SQL Server管理与开发技术大全[M].北京:人民邮电出版社,2007.