Chinaunix首页 | 论坛 | 博客
  • 博客访问: 426065
  • 博文数量: 239
  • 博客积分: 8010
  • 博客等级: 中将
  • 技术积分: 2431
  • 用 户 组: 普通用户
  • 注册时间: 2008-06-02 21:12
文章分类
文章存档

2008年(239)

我的朋友

分类: DB2/Informix

2008-06-17 23:41:25

如何写好SQL语句

程序员在编写SQL语句时,不能仅仅着眼于它的正确性。SQL语句的执行效率,决定了数据库系统的性能,影响着应用系统的正常运行。基于优化器对SQL语句的优化处理,为了编写出高效的SQL语句,我们应当遵循下面的原则:

 

1. 不要返回不需要的字段数据

 

SQL语句中返回不需要的数据,将浪费系统资源,导致性能的降低。这主要体现在:

1)增加磁盘的I/O操作。

2)将结果返回给客户端,增加了网络流量。

3)增加了表锁定时间,影响了其它用户的访问,损害了系统的并发能力。

此外,返回不需要的字段数据还可能影响到优化器的优化处理。如本章第5.2.3一节所述,不需要的字段数据导致优化器使用表扫描,而不是索引覆盖。

为避免返回不需要的字段数据,在查询语句中,尽可能将结果集中需要的字段列出来,不要笼统地使用“*”代替。

 

2. 避免写一些复杂的SQL语句

 

对于复杂的SQL语句,优化器将花费很长的时间进行优化处理,而它的执行计划也很难在多个应用程序之间共享。这样的SQL语句会严重影响系统性能,在应用程序中应力求避免。

我们可以将复杂的SQL语句划分成多个简单语句,分开处理。如果必要的话,可以考虑使用临时表存放中间结果。

 

3. 书写SQL语句要规范

 

按照一定的规范书写SQL语句,主要是希望SQL语句能够在多个应用程序之间实现共享。这样一来,SQL语句的执行计划就可以为多个应用程序所使用,从而减少优化器的优化处理。

 

4. 避免在查询条件中对字段使用函数或者进行计算

 

数据库中的索引,按照根页、分支页、叶页的层次分级存放。使用索引查找键值时,数据库系统从根页开始,通过键值的匹配和比较,快速地找到键值所在的叶页,进而找到表中的数据页。

如果在查询条件中对字段使用函数或者进行计算,系统就无法执行键值的匹配和比较。即使这些字段上存在索引,系统也不会使用索引进行数据的访问。

要尽可能使用索引而不是表扫描访问数据,就不能在查询条件中对字段使用函数或者进行计算。其实绝大多数情况下,我们通过改写查询条件,完全可以做到这一点。例如下面的这些例子:

1)要求字段取值最前面的一个字符为“m”:substring(col1,1,1) = ‘m’

可以替换为:col1 like ‘m%’

2)对字段取值进行数值计算:col1 –1 = 5

可以替换为:col1 = 5 –1

3)对字段取值小写转换后进行比较:lower(col1) = ‘tom’

可以替换为:col1 = ‘tom’ or col1 = ‘TOM’

4)对字段取值进行否定操作:not col1 > 5

可以替换为:col1 <=5

5)要求字段取值在2007年之前:year(col1) < 2007

可以替换为:col1 < '2007-01-01'

需要说明的是,由于索引可能在应用系统投入使用之后,根据需要进行添加,因此在应用程序开发时,就应当规范查询条件的写法,尽可能将字段和数值、处理函数分开放置在表达式的两边。

 

5. 只有必要时,才使用引起排序的关键字

 

用户对数据结果有什么要求,系统无法知道。它就是根据SQL语句本身,决定最终执行计划、返回结果。因此,只有在必要时,用户才应该在SQL语句中使用引起排序的关键字。这样可以有效地避免系统中出现不必要的排序处理。

我们知道,distinctunion关键字要求结果集中不能有重复的记录,order by关键字要求排序结果集,这些都可能引起系统的排序处理。如果用户不要求对结果集进行排序,允许重复记录的存在,就可以在SQL语句中:

1)不使用distinctorder by关键字。

2)使用union all代替union

另外,即使结果集被要求排序或者不能有重复记录,但用户知道:

1)结果集已经排序。在SQL语句中,就可以不使用order by关键字。

2)不会出现重复的记录。在SQL语句中,就可以不使用distinct关键字,可以使用union all代替union

 

6. 使用topfirst等关键字,返回部分数据

 

如果查询只是找到满足条件的一条或者部分记录即可,并不要求返回所有满足条件的记录。在这种情况下,可以在SQL语句中,使用topfirst关键字(对不同的数据库系统,这些关键字可能并不相同),优化器将使用最快的执行计划,在找到所需的记录后,就立即中断该操作,从而节省了系统资源。

 

7. 在连接属性、查询条件中,使用相同数据类型的字段

 

两个表中用作连接属性或者需要进行比较的字段,如果具有不兼容的数据类型,数据库系统在处理时就自动使用函数,实现数据类型的转换;即使两个字段具有兼容的数据类型(例如:整数型和浮点型),由于在计算机中存储方式的不同,数据库系统仍旧需要进行内部的处理。

在以上的情况下,数据库系统就不会使用索引。为了避免这种情况的发生,在数据库设计时,就应当根据应用程序的使用,使两个表中用作连接属性或者需要进行比较的字段,具有相同的数据类型。

 

8. 选择合适的操作符

 

SQL语句中,尽可能避免使用下列这些操作符:

!=”、“< >”、“!>”、“!<”、“not”、“not exists”、“not in”、“not like

由于这些操作符均使用了否定操作,系统不知道满足条件的键值究竟在什么区间之内,从而无法使用索引。在此情况下,系统只能使用表扫描,逐条对每一记录进行检查、比较。

其实大多数情况下,这些否定操作可以被替换。我们可以使用下列操作符或者它们的组合,代替否定操作:

>”、“>=”、“<”、“<=”、“like”、“in

阅读(890) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~