SQL语言艺术(PDF格式)-第4部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
不受缓存（caching）的影响。
表2…2显示了在测试机上的运行结果。
表2…2：处理10000条记录中空格所花的时间
函数 机制 时间
squeeze1
用 PL/SQL 循环处理字符 0。86 秒
…………………………………………………………Page 17……………………………………………………………
squeeze2 Instr（） ＋ ltrim（）
0。48 秒
squeeze3 循环调用 replace（）
0。39 秒
尽管都在1秒内完成了10 000次调用，但 squeeze2的速度是squeeze1的1。8 倍，而 squeeze3
则是它的2。2 倍。为什么呢？原因很简单，因为SQL 函数比PL/SQL“离核心更近”。当函数只偶
尔执行一次时，性能差异微乎其微，但在批处理程序或高负载的 OLTP 服务器中性能差异就非
常明显。
总结：代码喜欢SQL内核——离核心越近，它就运行得越快。
只做必须做的
Doing Only What Is Required
DDooiinngg OOnnllyy WWhhaatt IIss RReeqquuiirreedd
开发者使用count（*）往往只是为了测试“是否存在”。这通常是由以下的需求说明引起的：
如果存在满足某条件的记录
那么处理这些记录
用代码直接实现就是：
select count（*）
into counter
from table_name
where
if（counter》0）then
当然，在 90％ 的情况下，count（*） 是完全不必要的，正如上面的例子。要对多项记录进行操
作，直接做即可，不必用count（*）。即使一个操作对任何记录都没有影响，也没有关系，不用
count（*）没有什么不好。而且，即使要对未知的记录进行复杂处理，也能通过第一个操作就确定
并返回受影响的记录——要么通过特殊的 API （例如 PHP 中的 mysql_affected_rows（）），要么
采用系统变量（Transact…SQL 中为@@ROWCOUNT，PL/SQL 中为SQL％ROWCOUNT），若使
用内嵌式 SQL，则使用SQL通讯区（SQLmunicationArea，SQLCA）的特殊字段。有时，
…………………………………………………………Page 18……………………………………………………………
可以通过函数访问数据库然后直接返回要处理的记录数，例如 JDBC 的executeUpdate（）方法。
总之，统计记录数极可能意味着重复全部搜索，因为它对相同数据处理了两次。
此外，如果是为了更新或插入记录（常使用count检查键是否已经存在），一些数据库系统会提
供专用的语句（例如Oracle9i 提供MERGE 语句），其执行效率要比使用count高得多。
总结：没必要编程实现那些数据库隐含实现的功能。
SQL
SSQQLL语句反映业务逻辑
SQL Statements Mirror Business Logic
大多数数据库系统都提供监控功能，我们可以借此查看当前正在执行的语句及其执行的次数。
同时，必须对有多少个“业务单元（business units）”正在执行心里有数——例如待处理的订单、
需处理的请求、需结账的客户，或者业务管理者了解的任何事情。我们应检查上述语句活动和
业务活动的数量关系是否合理（并不要求绝对精确）。换言之，如果客户数量一定，那么数据库
初始化活动的数量是否与之相同？如果查询customers 表的次数比同一时间正在处理的客户量
多 20 倍，那一定是某个地方出了问题，或许该查询对表中相同记录做了重复（而且多余）的
访问，而不是一次就从表中找出了所需信息。
总结：检查数据库活动，看它是否与当时正进行的业务活动保持合理的一致性。
把逻辑放到查询中
Program Logic into Queries
在数据库应用程序中实现过程逻辑（procedural logic）的方法有几种。SQL语句内部可实现某
种程度上的过程逻辑（尽管SQL语句应该说明做什么，而不是怎么做）。即便内嵌式SQL的宿主
语言（host language）非常完善，依然推荐尽量将上述过程逻辑放在SQL语句当中，而不是宿
主语言当中，因为前一种做法效率更高。过程性语言（Procedural language）的特点在于拥有
执行迭代（循环）和条件（if 。。。 then 。。。 else 结构）逻辑的能力。SQL不需要循环能力，因为它
本质上是在操作集合，SQL只需要执行条件逻辑的能力。
条件逻辑包含两部分——IF和ELSE。要实现IF的效果相当容易——where子句可以胜任，困难
的是实现 ELSE 逻辑。例如，要取出一些记录，然后对其分组，每组进行不同的转换。case 表
达式（Oracle 早已在decode（）（注1）中提供了功能等效的操作符）可以容易地模拟ELSE逻辑：
根据每条记录值的不同，返回具有不同值的结果集。下面用伪代码（pseudocode）表达case 结
构的使用（注2）：
CASE
WHEN condition THEN
WHEN condition THEN
。。。
WHEN condition THEN
ELSE
…………………………………………………………Page 19……………………………………………………………
END
数值或日期的比较则简单明了。操作字符串可以用Oracle 的 greatest（）或least（），或者MySQL
的strcmp（）。有时，可以为insert语句增加过程逻辑，具体办法是多重insert及条件insert（注3），
并借助 merge 语句。如果 DBMS 提供了这样语句，毫不犹豫地使用它。也就是说，有许多
逻辑可以放入 SQL 语句中；虽然仅执行多条语句中的一条这种逻辑价值不大，但如果设法利
用 case、merge 或类似功能将多条语句合并成一条，价值可就大了。
总结：只要有可能，应尽量把条件逻辑放到 SQL语句中，而不是SQL的宿主语言中。
一次完成多个更新
Multiple Updates atOnce
MMuullttiippllee UUppddaatteess aattOOnnccee
我的基本主张是：如果每次更新的是彼此无关的记录，对一张表连续进行多次update操作还可
以接受；否则，就应该把它们合并成一个update操作。例如，下面是来自实际应用的一些代码
（注4）：
update tbo_invoice_extractor
set pga_status =0
where pga_status in（1；3）
andinv_type =0；
update tbo_invoice_extractor
set rd_status=0
where rd_status in（1；3）
andinv_type =0；
两个连续的更新是对同一个表进行的。但它们是否将访问相同的记录呢？不得而知。问题是，
搜索条件的效率有多高？任何名为type或status的字段，其值的分布通常是杂乱无章的，所以上
面两个update语句极可能对同一个表连续进行两次完整扫描：一个update有效地利用了索引，而
第二个update不可避免地进行全表扫描；或者，幸运
的话，两次update都有效地利用了索引。无论如何，把这两个update合并到一起，几乎不会有损
失，只会有好处：
update tbo_invoice_extractor
setpga_status =（casepga_status
when 1then 0
when 3then 0
else pga_status
…………………………………………………………Page 20……………………………………………………………
end）；
rd_status =（caserd_status
when 1then 0
when 3then 0
else rd_status
end）
where （pga_status in（1；3）
orrd_status in（1； 3））
andinv_type =0；
上例中，可能出现重复更新相同字段为相同内容的情况，这的确增加了一小点儿开销。但在多
数情况下，一个update会比多个update快得多。注意上例中的“逻辑（logic）”，我们通过case 语
句实现了隐式的条件逻辑（implicit conditional logic），来处理那些符合更新条件的数据记录，并
且更新条件可以有多条。
总结：有可能的话，用一个语句处理多个更新；尽量减少对同一个表的重复访问。
慎用自定义函数
Careful Use of User…Written Functions
将自定义函数（User…Written Function）嵌到SQL语句后，它可能被调用相当多次。如果在select
语句的选出项列表中使用自定义函数，则每返回一行数据就会调用一次该函数。如果自定义函
数出现在 where 子句中，则每一行数据要成功通过过滤条件都会调用一次该函数；如果此时
其他过滤条件的筛选能力不够强，自定义函数被调用的次数就非常可观了。
如果自定义函数内部还要执行一个查询，会发生什么情况呢？每次函数调用都将执行此内部查
询。实际上，这和关联子查询（correlated subquery）效果相同，只不过自定义函数的方式阻
碍了基于开销的优化器（cost…based optimizer，CBO）对整个查询的优化效果，因为“子查询”
隐藏在函数中，数据库优化器鞭长莫及。
下面举例说明将SQL语句隐藏在自定义函数中的危险性。表flights描述商务航班，有航班号、起
飞时间、到达时间及机场 IATA 代码（注5）等字段。IATA代码均为三个字母，有9 000多个，
它们的解释保存在参照表中，包含城市名称（若一个城市有多个机场则应为机场名称）、国家名
称等。显然，显示航班信息时，应该包含目的城市的机场名称，而不是简单的 IATA 代码。
在此就遇到了现代软件工程中的矛盾之一。被认为是“优良传统”的模块化编程一般情况下非常
适用，但对数据库编程而言，代码是开发者和数据库引擎的共享活动（shared activity），模块
化要求并不明确。例如，我们可以遵循模块化原则编写一个小函数来查找 IATA 代码，并返回
完整的机场名称：
create or replace function airport_city（iata_code in char）
return varchar2
is
…………………………………………………………Page 21……………………………………………………………
city_name varchar2（50）；
begin
select city
into city_name
from iata_airport_codes
where code = iata_code；
return（city_name）；
end；
/
对于不熟悉 Oracle 语法的读者，在此做个说明，以下查询中trunc（sysdate）的返回值为“今天的
00：00 a。m。”，日期计算以天为单位；所以起飞时间的条件是指今天 8：30 a。m。 至 4：00 p。m。 之
间。调用airport_city函数的查询可以非常简单，例如：
select flight_number；
to_char（departure_time； 'HH24：MI'） DEPARTURE；
airport_city（arrival） 〃TO〃
from flights
where departure_time between trunc（sysdate） ＋ 17/48
and trunc（sysdate） ＋ 16/24
order by departure_time
/
这个查询的执行速度令人满意；在我机器上的随机样本中，返回77行数据只用了0。18 秒（多次
执行的平均值），用户对这样的速度肯定满意（统计数据表明，此处理访问了
303个数据块，53个是从磁盘读出的——而且每行数据有个递归调用）。
我们还可以用join来重写这段代码，作为查找函数的替代方案，当然它看起来会稍微复杂些：
select f。flight_number；
to_char（f。departure_time； 'HH24：MI'） DEPARTURE；
a。city 〃TO〃
from flights f；
iata_airport_codes a
where a。code = f。arrival
and departure_time between trunc（sysdate） ＋ 17/48
and trunc（sysdate） ＋ 16/24
order by departure_time
/
…………………………………………………………Page 22……………………………………………………………
这个查询只用了 0。05 秒（统计数据同前，但没有递归调用）。对于执行时间不到 0。2 秒的查
询来说，速度快了3倍似乎无关紧要，但在大型系统中，这些查询每天经常执行数十万次——假
设以上查询每天只执行五万次，于是查询的总耗时为 2。5 小时。若不使用上述查找函数（lookup
function）则只需要不到 42 分钟，速度提高超过300％，这对大数据量的系统意义重大，最终
带来经济上的节约。通常，使用查找函数会使批处理程序的性能极差。而且查询时间的增加，
会使同一台机器支持的并发用户数减少，我们将在第9章对此展开讨论。
总结：优化器对自定义函数的代码无能为力。
SQL
简洁的SSQQLL
Succinct SQL
熟练的开发者使用尽可能少的 SQL语句完成尽可能多的事情。相反，拙劣的开发者则倾向于严
格遵循已制订好的各功能步骤，下面是个真实的例子：
…Get the start of the accounting period
select closure_date
into dtPerSta
from tperrslt
where fiscal_year=to_char（Param_dtAcc；'YYYY'）
