荔园在线
荔园之美,在春之萌芽,在夏之绽放,在秋之收获,在冬之沉淀
[回到开始]
[上一篇][下一篇]
发信人: 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软件 网络书店