荔园在线

荔园之美,在春之萌芽,在夏之绽放,在秋之收获,在冬之沉淀

[回到开始] [上一篇][下一篇]


发信人: Peter (小飞侠), 信区: Program
标  题: 优化数据库查询
发信站: BBS 荔园晨风站 (Wed Jan 27 17:45:54 1999), 转信


  使用PowerBuilder编程时会大量用到数据库查询语句。对于一条复
杂的查询语句来说, 对相同查询条件的实现一般总可以有多种不同的
表达方法,而不同的表达会使数据库的响应速度大相径庭。据统计,约
有90%的性能问题是由于程序员或用户使用了不恰当的查询语句造成
的,因此提高书写SQL语句的质量对软件性能的提高有很大关系。然而
查询语句的好坏往往是同实际运行系统的数据库结构、记录的数量等
具体情况有关的,我们无法只用几条简单的普遍适用的规律来总结优
化查询语句。不过我们首先应当对数据库管理系统最基本的工作规律
有一些了解,这样才能使我们在对查询进行时优化有所根据。
    由于SQL语言是面向结果而不是面向过程的查询语言,所以一般支
持SQL语言的大型关系型数据库都需要使用一个基于成本的优化器,为
即时查询提供一个最佳的执行策略。对于优化器,输入是一条查询语
句,输出是一个执行策略。这个执行策略是执行这个查询所需要的一
系列步骤。数据库的反应速度经常就体现在这一个优化算法上。不同
的查询策略和查询步骤可使服务器的反应不同,因此采用适当的查询
策略可使系统性能大大提高。
    优化器的优化基于用户对所查询表的内容和其他一些与服务器有
关的因素,如Cache大小、Cache策略、I/O大小等。一般来说硬盘访问
是成本最高的操作,因此对用户来讲,使优化器对字段索引进行操作是
优化查询的关键。
    SQL查询语句都可以有很多种执行策略,优化器将估计出全部的执
行方法中所需时间最少的也就是所谓成本最低的一种方法。一般来讲
,最为重要的选择就是使用什么索引和采用何种表的连接手段,而所有
优化的进行都是基于用户所使用的查询语句中的where子句。
    优化分类
    优化器对where子句中的优化分为以下几类:

    1.搜索参数
    搜索参数的核心就是数据库能否使用表中字段的索引来查询数据
,而不必直接查询记录中的数据。如带有=、<、>、>=、<=等操作符的
条件查询就可以直接使用索引。
    如下列条件是搜索参数:
    id = "T0001",salary>30000,a = 1 and c = 7。
    而下列则不是搜索参数:
    salary = commission,dept != 10,salary *12  >=  30000,a =
1 or c = 7。
    优化器有时可以将非搜索参数转化为搜索参数,如:
    将SELECT  name FROM employee WHERE  salary   BETWEEN   $
10000  AND   $1500
    转化为:SELECT  name  FROM employee   WHERE  salary  >=
$10000   AND   sala ry  <=  $15000   将SELECT name  FROM  em
ployee  WHERE  name like "a%"
    转化为:SELECT name  FROM  employee  WHERE  name >= "a"
AND   name<"b"
    将SELECT  name  FROM  employee  WHERE  salary  >  $3000
* 12
    转化为:SELECT name FROM  employee WHERE salary  >  $3600
0
    因此我们在查询中应当提供一些冗余的搜索参数,使优化器有更
多的选择余地。如titl e和titleauthor两张表是一对多的关系,同样
的查询条件我们有以下三种表现方法:
    ●SELECT  title_id, title   FROM  titles,  titleauthor
    WHERE  title.title_id = titleauthor.title_id
    AND  titleauthor.title_id = ‘T81002'
    ●SELECT  title_id, title   FROM  titles,  titleauthor
    WHERE  title.title_id = titleauthor.title_id
    AND  title.title_id = ‘T81002'
    ●SELECT  title_id, title   FROM  titles,  titleauthor
    WHERE  title.title_id = titleauthor.title_id
    AND  title.title_id = ‘T81002'
    AND  titleauthor.title_id  = ‘T81002'
    显然三种方法一种比一种要好,因为后者为优化器提供了更多的
选择机会。
    2.连接条件
    在进行查询连接时优化器将所有连接的方法全部列举出来,计算
每一种连接的成本,选择成本最低的一种。如连接时用到的数据无法
获得,一般系统会使用平均密度作为依据,估算可能的命中率。

    3.‘或’运算条件
    当查询语句中有IN这样的关键词时,优化器将转化其中的内容以O
R并列条件。例如:
    SELECT * FROM author WHERE au_lname in (‘Berry',‘Densh
am')
    将转化为:
    SELECT * FROM author WHERE au_lname = ‘Berry' or au_lna
me = ‘Densham'
    数据库管理系统将对每一个OR从句进行查询,将所有的结果合并
后去掉重复项作为最终结果。

    优化技巧
    基于对上述数据库优化器的了解,为确保对我们将要执行的查询
语句得以进行准确的优化,我们应注意以下几点:

    1.避免使用不兼容的数据类型。例如float和int、char和varcha
r、binary和varbinar y是不兼容的。数据类型的不兼容可能使优化
器无法执行一些本来可以进行的优化操作。例如:
    SELECT  name  FROM  employee WHERE  salary  >  60000
    在这条语句中,如salary字段是money型的,则优化器很难对其进
行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱
币型,而不要等到运行时转化。
    2.如果在一个存储过程或触发器中,有表达式的值在编译时无法
得到,优化器就只能使用它的平均密度来估计命中的记录数。例如:
    DECLARE  @value  money
    SELECT name FROM   employee WHERE  salary = @value
    这样的命令是可优化的。只是由于@value的值在执行前不知道,
它只能使用其平均密度来估计这条命令将要命中的记录数。

    3.避免对搜索参数使用其它数学操作符,如要将
    SELECT  name  FROM  employee WHERE  SUBSTRING(id, 1, 1)
= ‘B'
    SELECT  name  FROM  emplyee WHERE  salary  * 12  >  3000
0
     写成为:
    SELECT  name  FROM  employee WHERE  id   like ‘B%'
    SELECT  name  FROM  emplyee WHERE  salary   >  3000
    4.避免使用!=或<>等这样的操作符,因为这会使系统无法使用索
引,而只能直接搜索表中的数据。例如:
    SELECT id FROM employeeWHERE   id  !=  'B%'
    优化器将无法通过索引来确定将要命中的行数。
    上面我们提到的是一些基本的提高查询速度的注意事项,但是在
更多的情况下,程序员往往需要反复试验比较不同的语句以得到最佳
方案。此外更为重要的是需要数据库管理员在数据库的服务器一端调
整数据库管理系统的参数,以得到更快的响应性能,这就超出了本文的
讨论范围。

--
※ 来源:.BBS 荔园晨风站 bbs.szu.edu.cn.[FROM: 192.168.1.3]


[回到开始] [上一篇][下一篇]

荔园在线首页 友情链接:深圳大学 深大招生 荔园晨风BBS S-Term软件 网络书店