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

-1-浅谈如何优化SQL语句提高数据库系统效率陈庆1（1.扬州市中医院,江苏省扬州市，225009）摘要：对于大型的企业或部门来说，每天都需要处理大量的数据业务，数据库系统的设计变的异常复杂，而数据库的性能的好坏直接影响到各项业务能否顺利进行。然而数据库优化涉及到许多方面，如：良好的系统和数据库设计；优质的SQL语句编写；合适的数据表索引设计等，甚至包括各种硬件因素，如：网络性能；服务器的性能；操作系统的性能等。如果只想通过升级硬件系统来提升性能将会付出很大的经济代价，通过优化SQL语句的编写提升数据库性能会是一个不错的选择。关键词：数据库设计;数据库系统;数据库性能;SQL语句;优化1引言SQL（StructuredQueryLanguage)结构化查询语言，是一种高级的非过程化编程语言，是沟通数据库服务器和客户端的重要工具，它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有在不同数据库系统上使用相同的SQL语言作为数据输入与管理的接口。它以记录集合作为操作对象，所有SQL语句接受集合作为输入，返回集合作为输出，这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入，所以SQL语句可以嵌套，这使它具有极大的灵活性和强大的功能，在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的，这也意味着用SQL语言可以写出非常复杂的语句。然而SQL语句的不同写法却会对数据库系统运行带来完全不同的效果，本文就如何优化SQL语句提升系统性能作一深入探讨。2优化SQL语句的好处对于大部分基于数据库的应用程序来说，多数都是C/S或B/S架构，其与数据库的联系是通过客户端嵌入的SQL语句或调用数据库上的过程实现的。所以SQL语句质量的好坏会影响整个系统，因此优化SQL语句有以下几个好处：一是提高系统的运行效率，减少数据库死锁的风险；二是降低系统对硬件资源的消耗，节约投资；三是强化系统源代码的可读性，降低程序员修改程序的劳动强度。3合理使用数据库索引索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化器，提高系统的性能。然而增加索引也有许多不利的方面。第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空-2-间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。因此在写SQL语句时要合理使用数据库索引，这会让你的数据库运行效果事半功倍。4优化SQL语句的方法4.1合理的使用各种操作符首先要注意操作符的使用方法，虽然有时候不同的操作符实现的功能是一样的，但执行的效率却相差很多，这对于数据库的初学者是常常容易忽视的。4.1.1IN（NOTIN）与EXISTS（NOTEXISTS）操作符的区别我们在调用数据的时候会将一列和一系列值相比较，最简单的办法就是在WHERE子句中使用子查询，而在WHERE子句中有两种方式的子查询。下面以zy_dxsfls，zy_brzlls两个表为例，如下所示：第一种方式使用IN操作符，Selecta.blh,a.sfks,a.sfrq,a.yzm,a.zfje,a.xzysFromzy_dxsflsawherea.blhin(selectb.blhfromzy_brzllsbwhereb.csrq='1970-1-1')；第二种方式使用EXIST操作符，Selecta.blh,a.sfks,a.sfrq,a.yzm,a.zfje,a.xzysFromzy_dxsflsawhereexists(select1fromzy_brzllsbwhereb.blh=a.blhandb.csrq='1970-1-1')。虽然两种方法得出的结果是一样的，但是使用的时候却是有区别的。IN操作符适合于zy_brzlls表大而zy_dxsfls表小的情况，而EXISTS操作符适合于zy_brzlls表小而zy_dxsfls表大的情况。因此使用的时候应视情况而定，不能盲目使用，NOTIN操作符和NOTEXISTS操作符也是一样的。4.1.2巧用比较条件（、=、、=）操作符以大于操作符和大于等于操作符为例，它们能实现相同的功能，但是对它们优化之后会有不同的效果。如一个表有100万记录，有一个数值型字段“A”，A=0的记录有30万行，A=1的记录30万行，A=2的记录有39万行，A=3的记录有1万行。那么执行A2与A=3的效果就有很大的区别了，因为A2时数据库会先找出为2的记录再进行比较，而A=3时数据库则直接找到等于3的记录，所以选择好的比较条件会提高数据库的执行效率。4.1.3尽量少用UNION操作符UNION操作符是用来合并查询结果的，最常见的是过程表与历史表的合并。在执行该操作时数据库先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致数据库系统效率下降。不过可以采用UNIONALL操作符替代UNION，因为UNIONALL操作只是简单的将两个结果合并后就返回。4.1.4变相使用ISNULL（ISNOTNULL）操作符NULL值即空值的意思，根据数据库的特性，是不能使用包含NULL值的字段作为索引的，即使对该字段建立了索引，只要某行上面有NULL值，该字段就不能使用索引。但是我们可-3-以使用变相方法来表示NULL值，如将某行上的NULL值用0表示，这样WHERE条件语句“aisnull”就可以改成“a=’0’”来实现；另外还可以设置字段不允许为空，而用一个缺省值代替空值，如一个时间类型的字段，可以将默认时间设为“1900-01-01”来表示空值。4.1.5合理使用LIKE操作符LIKE操作符是可以使用通配符查询的语句，里面的通配符组合可以达到任意多个，但是如果用得不好则会产生性能上的问题，如yzmlike'%阿莫西林%'这种查询不会使用数据库索引，而yzmlike'阿莫西林%'则会使用数据库索引。用一个实例测试，如表1所示：SQL语句数据库用时Selecttop10000blh,sfks,sfrq,yzm,zfje,xzysFromzy_dxsflswhereyzmlike'阿莫西林%'356msSelecttop10000blh,sfks,sfrq,yzm,zfje,xzysFromzy_dxsflswhereyzmlike'%阿莫西林%'1420ms表14.1.6避免在WHERE子句中做函数计算这样做会使数据库在表的每行上进行运算，从而导致该列的索引不能被使用而触发全表扫描。我们可以从下列对比中看出效果，如表2所示：SQL语句数据库用时Select*Fromzy_brzllswhereyear(csrq)19802580msSelect*Fromzy_brzllswherecsrq'1980-01-01'1150ms表2还有很多操作符都不能利用数据库索引，如：NOT、!=、、!、!、NOTEXISTS、NOTIN、NOTLIKE、OR等，这些都会导致数据库引擎放弃使用索引而进行全表扫描。4.2合理的书写SQL语句4.2.1注意SQL语句中空格和字母的大小写首先让我们看一个示例，如表3所示：书写示例SQL语句ASelect*fromzl_yhjbqkBSelect*fromdlyx.zl_yhjbqk（带表所有者的前缀）CSelect*fromDLYX.ZLYHJBQK（大写表名）DSelect*fromDLYX.ZLYHJBQK（中间多了空格）表3以上四个SQL语句分析整理之后产生的结果及执行的时间是一样的，但是从数据库共享内存的原理，可以得出数据库对每个SQL语句都会进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同，则数据库只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL语句的时间，而且可以减少共享内存重复的信息。-4-4.2.2注意WHERE子句后面的条件顺序WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。以下面两条语句为例：SelectidFromEmpWhereempno=2000andsal=1000；SelectidFromEmpWheresal=1000andempno=2000；假设以上两个SQL语句中“empno”及“sal”两个字段都没有索引，所以执行的时候都是全表扫描，其中的“sal=1000”条件在记录集内比率为99%，而“empno=2000”的比率只为0.5%。在执行第一条SQL语句的时候，查询先得到符合条件sal=1000的记录M条，接着处理条件empno=2000这一次只需要在第一次查询的结果集中进行查询即可，得出N条，所以总共查询了M+N条记录。而在进行第二条SQL语句的时候，查询先得到符合条件empno=2000的记录为A条，接着处理条件sal=1000，同样只要在第一次查询的结果集中进行查询即可，得出B条，所以总共查询了A+B条记录。可以看出数据库扫描的数据量A+BM+N,因此第二条语句的执行效率更高。4.2.3按需提取字段，避免使用“SELECT*”首先，让我们看一个示例，有一张表zy_dxsfls拥有1500万行数据，测试结果如表4所示：SQL语句数据库用时Selecttop100000*Fromzy_dxsfls3454msSelecttop100000blh,sfks,sfrq,yzm,zfje,xzysFromzy_dxsfls1296msSelecttop100000blh,sfrq,yzm,zfjeFromzy_dxsfls1110msSelecttop100000blh,sfrq,zfjeFromzy_dxsfls920ms表4从上面的结果我们可以看出，数据执行的效率相差很大，这是由以下两个方面造成的：（1）“SELECT*”提取了所有的数据，数据容量大（字节数多），查询效率自然会降低，所以字段的提取一定要按照“用多少提多少”的原则，避免使用“SELECT*”这样的操作，但提升的速度还要看你舍弃的字段的大小来判断。(2)“SELECT*”的最大问题是影响查询的方式，数据库主要使用索引去查询你需要的数据，当索引包含你请求查询的所有字段时，数据库就不会再去表中查询，这个概念称作索引覆盖。因为索引扫描比其他方式的扫描快很多。4.3适当的使用过渡表把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。以查询在院病人费用超过1万元的医嘱明细为例，如下所示：Selecta.BLH,b.SFKS,b.SFRQ,b.YZM,b.ZFJE,……Fromzy_brzla,zy_dxsfbwherea.blh=b.blhanda.zt='在院'anda.zfje=10000Orderbya.blh；如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序。SQL语句可以改为：Selecta.BLH,b.SFKS,b.SFRQ,b.YZM,b.ZFJE,……-5-Intozy_brzl_zyFromzy_brzla,zy_dxsfbWherea.blh=b.blhanda.zt='在院'Orderbya.blh；然后以下面的方式在临时表中查询:Select*Fromzy_br