精华区文章阅读

发信人: wodeji (西西), 信区: Database
标  题: [转载] PowerBuilder 开发系列谈 (18)
发信站: 紫丁香 (Tue Sep 29 17:52:12 1998), 站内信件

【以下文字转载自 Programming 讨论区】
【原文由 two 所发表】

        我们使用PowerBuilder 编程时总是会大量的用到数据库的查询语句，对于一
条复杂的查询语句来说，对相同的查询条件的实现一般总可以有多种不同的
表达方法，而不同的表达会使数据库的响应速度大相径庭。据统计，约有90%
性能问题是由于应用开发的程序员或用户使用了不恰当的查询语句而使系统
的响应速度减慢，因此提高书写SQL 语句的质量对软件性能的提高有很大关
系，然而查询语句的好坏往往是同实际运行系统的数据库结构、记录的数量
等具体情况有关的，我们无法只用几条简单的普遍适用的规律来总结应当怎
样优化查询语句，以得到更高的性能。不过我们首先应当对数据库管理系统
最基本的工作规律有一些了解，这样才能使我们在对查询进行时优化有所根
据。

        由于SQL 语言是面向结果而不是面向过程的查询语言，所以一般支持SQL
语言的大型关系型数据库都需要使用一个基于成本的优化器，为一个即时查
询提供一个最佳的执行策略。对于优化器，输入是一条查询语句，输出是一
个执行策略。这个执行策略是执行这个查询所需要的一系列步骤。数据库的
反应速度经常就体现在这一个优化算法上。不同的查询策略和查询步骤可使
得服务器的反应不同，因此采用适当的查询策略可使得系统性能大大的提
高。

        优化器的优化是基于用户对所查询表的内容和其他一些服务器的有关因
素如Cache 大小、Cache 策略、I/O 大小等。一般来说硬盘访问是成本最高的操
作，因此对用户来讲，使优化器能对字段的索引进行操作是优化查询的关
键。

        SQL 查询语句都可以有很多种执行策略，优化器将估计出全部的执行方法
中所需时间最少的也就是所谓成本最低的那一种方法。一般来讲，最为重要
的选择就是使用什么索引和采用何种表的连接手段，而所有优化的进行都是
基于用户所使用的查询语句中的where 子句。

        优化器对where 子句中的优化分为以下几类：
        搜索参数(searchargument)

        搜索参数的核心就是数据库能否使用表中字段的索引来查询数据，而不
必直接查询记录中的数据。如带有=,<,>,>=,<= 等操作符的条件系统就可以直接
使用索引。

        如下列条件是搜索参数：

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   $15000
SELECT  name  FROM employee   WHERE  salary  >=  $10000   AND   salary  <=  $15000
SELECT  name  FROM  employee  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  >  $36000

        因此我们在查询中应当提供能一些冗余的搜索参数，使优化器有更多的
选择余地，如title 和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'

        显然三种方法一种比一种要好，因为后者为优化器提供了更多的选择机
会。

连接条件

        接连时优化器将所有连接的方法全部列举出来，计算每一种连接的成本，
选择成本最低的一种。如连接时用到的数据无法获得，一般系统会使用平均
密度作为依据，估算可能的命中率。 ' 或' 运算条件

        当查询语句中有IN 这样的关键词时，优化器将转化其中的内容以OR 并列
条件。例如：

SELECT * FROM author
WHERE au_lname in ('Berry','Densham')
将转化为：
SELECT * FROM author
WHERE au_lname = 'Berry' or au_lname = 'Densham'

        数据库管理系统将对每一个OR 从句进行查询，将所有的结果合并后去除
重复项作为最终结果。

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

        1. 避免使用不兼容的数据类型。例如float 和int，char 和varchar，binary 和varbinary 是
不兼容的。数据类型的不兼容可能使得优化器无法执行一些本来可以进行的
优化操作。例如：

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  >  30000
写成为：
SELECT  name  FROM  employee
WHERE  id   like 'B%'
SELECT  name  FROM  emplyee
WHERE  salary   >  3000

        4. 避免使用!= 或<> 等这样的操作符，因为这会使系统无法使用索引，而只
能直接搜索表中的数据。例如：
        SELECT id FROM employee
        WHERE id != 'B%'

        优化器将无法通过索引来确定将要命中的行数。
        上述我们提到的是一些基本的提高查询速度的注意事项，但是在更多的
情况下，程序员往往需要反复地试验比较不同的语句以得到最佳的方案。此
外更为重要的是需要数据库管理员在数据库的服务器一端调整数据库管理系
统的参数，以得到更快的响应性能，这就超出了本文的讨论范围。



--
※ 来源:．紫丁香 bbs.hit.edu.cn．[FROM: 202.97.236.132]
--
※ 转载:．紫丁香 bbs.hit.edu.cn．[FROM: whs.hit.edu.cn]
--
※ 转载:．紫丁香 bbs.hit.edu.cn．[FROM: cadcam.hit.edu.c]

Database 版 (精华区)