SQL 过程的性能: 提示和技巧

将此页作为电子邮件发送

级别： 初级

Gustavo Arocena, DB2 SQL 编译器团队, IBM 多伦多实验室

2003 年 9 月 01 日

本文就如何组织 SQL 存储过程中的逻辑以增强性能提供了一些具体建议。

简介

有关如何调优数据库系统和应用程序的可用建议来源有很多。诸如 OLTP 应用程序的 DB2 调优技巧（以前在 IBM® DB2® 开发者园地上发表）之类的文章通过使用事务和数据并行性以及分析查询方案，给出了从表空间和索引设计到缓冲池的内存分配等方面的建议。这些方面的内容是性能调优的基础知识。

但是，有关如何组织存储过程自身中的逻辑并着眼于其性能的专门建议却并不多见。本文就提供了这样一种建议。尽管本文着重于介绍 SQL 过程，但是这里所提供的大多数信息同样适用于用其它语言编写的在应用程序中或存储过程中嵌入的 SQL 逻辑。

背景知识和术语

在 深入研究详细问题之前，让我们先回顾 DB2 中有关过程化 SQL 的一些基本术语和概念。过程化 SQL 构造（例如标量变量、IF 语句和 WHILE 循环）是在 DB2 Universal Database™ (UDB) V7 发行版中引入 DB2 的。以前的 DB2 发行版支持 C 和 Java™ 作为存储过程的语言。V7 引入了 SQL 存储过程，以及其它许多可以促进 OLTP 应用程序开发的特性（例如临时表、应用程序保存点和标识列）。

当创建 SQL 过程时，DB2 将过程主体中的 SQL 查询与过程逻辑区分开来。为了使性能最优，SQL 查询被静态地编译成包中的节。（对于静态编译的查询而言，节主要是由 DB2 优化器为该查询选择的存取方案构成的。包是节的集合。有关包和节的更多信息，请参阅 DB2 SQL 参考大全，第 1 卷。）另一方面，过程逻辑被编译成 DLL（动态链接库）。

在 过程的执行期间，每当控制从过程逻辑流向 SQL 语句时，在 DLL 和 DB2 引擎之间就存在“上下文切换”。（在 DB2 V8 中，SQL 过程是在“不受保护的方式”下运行的，即与 DB2 引擎在相同的寻址空间中。因此我们这里谈及的上下文切换并不是操作系统级别上的完全的上下文切换，而是指 DB2 中层的更换。）减少频繁调用的过程（例如 OLTP 应用程序中的过程）或者处理大量行的过程（例如执行数据清理的过程）中的上下文切换次数，对它们的性能有显著的影响。本文中的几个技巧恰好旨在减少这些上 下文切换。

刚开始的时候（DB2 通用数据库 V7 GA），只允许在 SQL 过程中使用 SQL 过程语言（通常称为 SQL PL）。后来（在 DB2 UDB V7.2 中），在 SQL 函数和触发器主体中开始支持该语言的子集。SQL PL 的这个子集即所谓的 内联（inline）SQL PL。“内 联”一词突出显示了它与完整语言的重要区别。SQL PL 过程是通过将其单独的 SQL 查询静态地编译成包中的节实现的，而内联 SQL PL 函数就象其名称所展示的，是通过将函数主体内联到使用它的查询中实现的。稍后我们将再看一下内联 SQL PL 及其用法的一些示例。

现在，让我们研究在使用 SQL 过程语言时可用来提高性能的一些具体工作。

在只使用一条语句即可做到时避免使用多条语句

让我们从一个简单的编码技巧开始。如下所示的单个 INSERT 行序列：

INSERT INTO tab_comp VALUES (item1, price1, qty1);                    INSERT INTO tab_comp VALUES (item2, price2, qty2);                    INSERT INTO tab_comp VALUES (item3, price3, qty3);                    

可以改写成：

INSERT INTO tab_comp VALUES(item1, price1, qty1),                    (item2, price2, qty2),                    (item3, price3, qty3);                    

执行这个多行 INSERT 语句所需时间大约是执行原来三条语句的三分之一。孤立地看，这一改进看起来似乎是微乎其微的，但是，如果这一代码段是重复执行的（例如该代码段位于循环体或触发器体中），那么改进是非常显著的。

类似地，如下所示的 SET 语句序列：

SET A = expr1;                    SET B = expr2;                    SET C = expr3;                    

可以写成一条 VALUES 语句：

VALUES expr1, expr2, expr3 INTO A, B, C;                    

如果任何两条语句之间都没有相关性，那么这一转换保留了原始序列的语义。为了说明这一点，请考虑：

SET A = monthly_avg * 12;                    SET B = (A / 2) * correction_factor;                    

将上面两条语句转换成：

VALUES (monthly_avg * 12, (A / 2) * correction_factor) INTO A, B;                    

不会保留原始的语义，因为是以“并行”方式对 INTO 关键字之前的表达式进行求值的。这意味着赋给 B 的值并不以赋给 A 的值为基础，这是原始语句预期的语义。

从多个 SQL 语句到一个 SQL 表达式

跟 其它编程语言一样，SQL 语言提供了两类条件构造：过程型（IF 和 CASE 语句）和函数型（CASE 表达式）。在大多数环境中，可使用任何一种构造来表达计算，到底使用哪一种只是喜好问题。但是，使用 CASE 表达式编写的逻辑不但比使用 CASE 或 IF 语句编写的逻辑更紧凑，而且更有效。

请考虑下面的 SQL PL 代码片段：

IF (Price <= MaxPrice) THEN                    INSERT INTO tab_comp(Id, Val) VALUES(Oid, Price);                    ELSE                    INSERT INTO tab_comp(Id, Val) VALUES(Oid, MaxPrice);                    END IF;                    

IF 子句中的条件仅用于决定将什么值插入 tab_comp.Val 列中。为了避免过程层和数据流层之间的上下文切换，可利用 CASE 表达式将相同的逻辑表示成一个 INSERT 语句：

INSERT INTO tab_comp(Id, Val)                    VALUES(Oid,                    CASE                    WHEN (Price <= MaxPrice) THEN Price                    ELSE MaxPrice                    END);                    

值得注意的是，CASE 表达式可在任何希望有标量值的上下文中使用。特别地，可在赋值符号的右边使用它们。例如：

IF (Name IS NOT NULL) THEN                    SET ProdName = Name;                    ELSEIF (NameStr IS NOT NULL) THEN                    SET ProdName = NameStr;                    ELSE                    SET ProdName = DefaultName;                    END IF;                    

可以改写成：

SET ProdName = (CASE                    WHEN (Name IS NOT NULL) THEN Name                    WHEN (NameStr IS NOT NULL) THEN NameStr                    ELSE  DefaultName                    END);                    

实际上，这个特殊的示例有一个更好的解决方案：

SET ProdName = COALESCE(Name, NameStr, DefaultName);                    

使用 SQL 的一次处理一个集合语义

诸 如循环、赋值和游标之类的过程化构造允许我们表达那些只使用 SQL DML 语句是不可能表达的计算。但是，当我们拥有一些可以随意使用的过程语句时，即使我们手头的计算实际上仅使用 SQL DML 语句就可表达，但转换成过程语句还是有风险的。正如我们以前提到的，过程计算的性能与使用 DML 语句表达的同一个计算的性能相比会慢几个数量级。请考虑下面的代码片段：

DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab_comp;                    OPEN cur1;                    FETCH cur1 INTO v1, v2;                    WHILE SQLCODE <> 100 DO                    IF (v1 > 20) THEN                    INSERT INTO tab_sel VALUES (20, v2);                    ELSE                    INSERT INTO tab_sel VALUES (v1, v2);                    END IF;                    FETCH cur1 INTO v1, v2;                    END WHILE;                    

首先，通过应用上一节讨论的转换可以改进循环体：

DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab_comp;                    OPEN cur1;                    FETCH cur1 INTO v1, v2;                    WHILE SQLCODE <> 100 DO                    INSERT INTO tab_sel VALUES (CASE                    WHEN v1 > 20 THEN 20                    ELSE v1                    END, v2);                    FETCH cur1 INTO v1, v2;                    END WHILE;                    

但是通过进一步观察，我们发现整个代码块可以写成一个带有 SELECT 子句的 INSERT 语句：

INSERT INTO tab_sel (SELECT (CASE                    WHEN col1 > 20 THEN 20                    ELSE col1                    END),                    col2                    FROM tab_comp);                    

在原始的表述中，SELECT 语句中每行的过程层和数据流层之间都有一个上下文切换。在最后一个表述中，根本没有上下文切换，并且优化器有机会对整个计算进行全局优化。另一方面，如果 每个 INSERT 语句针对的都是不同的表，那么这种引人注目的简化是不可能的，如下所示。

DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab_comp;                    OPEN cur1;                    FETCH cur1 INTO v1, v2;                    WHILE SQLCODE <> 100 DO                    IF (v1 > 20) THEN                    INSERT INTO tab_default VALUES (20, v2);                    ELSE                    INSERT INTO tab_sel VALUES (v1, v2);                    END IF;                    FETCH cur1 INTO v1, v2;                    END WHILE;                    

但是，这里也可以利用 SQL 的一次处理一个集合（set-at-a-time）特性：

INSERT INTO tab_sel (SELECT col1, col2                    FROM tab_comp                    WHERE col1 <= 20);                    INSERT INTO tab_default (SELECT col1, col2                    FROM tab_comp                    WHERE col1 > 20);                    

在研究改进现有过程逻辑的性能时，为消除游标循环而花费的任何时间都可能是值得的。

改进游标性能

如果存储过程中的逻辑确实需要游标，那么要使性能最优，请牢记下面这些内容。

首 先，请确保不使用高于您所需的隔离级别。隔离级别决定了 DB2 对过程读取或更新的行应用的锁定的数量。隔离级别越高，DB2 将执行的锁定越多，因此为同一资源而竞争的应用程序之间的并发就越少。例如，使用可重复读（Repeatable Read，RR）隔离级别的过程将形成对其读取的任何行的共享锁，而使用游标稳定性（Cursor Stability，CS）的过程只会锁定任何可更新游标的当前行。可以使用 DB2_SQLROUTINE_PREPOPTS 注册表变量来指定 SQL 过程的隔离级别。例如，要将 SQL 过程的隔离级别设置为未提交的读（Uncommitted Read）（最低的级别，用于访问只读数据的过程），请使用下面这条命令：

db2set  DB2_SQLROUTINE_PREPOPTS="ISOLATION UR"                    

注：要使该设置生效，必须重新启动 db2 实例。

DB2 中缺省的隔离级别是游标稳定性。但是，当然了，为了保持应用程序的正确性，有时需要使用可重复读。还需记住一件重要的事情，一旦创建了需要可重复读的过程，必须将 DB2_SQLROUTINE_PREPOPTS 重新设置回较低的隔离级别。

有关隔离级别还值得一提的是，DB2 允许我们在单独的查询中覆盖缺省的隔离级别，如下所示：

DECLARE cur1 CURSOR FOR SELECT col1 FROM tab_comp WITH UR;                    

上面的查询将以隔离级别 UR 进行执行，而不管 DB2_SQLROUTINE_PREPOPTS 中指定的隔离级别。

在尝试改进游标性能时需要牢记的一个相关问题是游标的可更新能力。如果游标涉及的行是可以使用 INSERT 或 DELETE 语句中的 WHERE CURRENT OF 子句进行更新或删除，那么它就是 可删除的。当游标可删除时，DB2 必须获取行上的 互斥锁（与 共享锁相对），并且不能执行行分块。行上的互斥锁甚至可以防止其它应用程序读取该行（在互斥锁被释放之前，这些应用程序必须等待，除非它们的隔离级别是 UR），而行分块通过在一个操作中检索行块，从而减少了用于游标的数据库管理器开销。

只 有不可删除的游标才可以进行行分块。这就是为什么让 DB2 了解将如何使用游标是很重要的原因。通过在 SELECT 语句中指定 FOR READ ONLY 子句，可以将游标显式地声明为不可删除，或者通过在 SELECT 语句中使用 FOR UPDATE 子句将其声明为可删除。根据该信息（并且还根据下面描述的 BLOCKING 选项），DB2 将确定是否将行分块用于给定的游标。

缺 省情况下，对于那些使用 FOR READ ONLY 子句定义的游标，DB2 将始终使用行分块，除非指定了 BLOCKING NO 绑定选项。另一方面，如果使用了 BLOCKING ALL 绑定选项，那么对于含混游标（既不是定义成 FOR READ ONLY 也不是定义成 FOR UPDATE 的游标），DB2 将使用行分块。

简而言之：如果可能，则在游标定义中使用 FOR READ ONLY 子句；如果您的过程包含含混游标，那么请使用 BLOCKING ALL 绑定选项。要设置 BLOCKING 绑定选项的值，我们还可以使用 DB2_SQLROUTINE_PREPOPTS 注册表变量。例如，要将 SQL 过程的隔离级别设置为未提交的读，并将行分块设置为 BLOCKING ALL，请使用下面这条命令：

db2set  DB2_SQLROUTINE_PREPOPTS="ISOLATION UR BLOCKING ALL"                    

对于返回大型结果集的过程而言，分块特别重要。

通过使用 DB2_SQLROUTINE_PREPOPTS 注册表，还可以为存储过程指定其它绑定选项。请参阅 Application Development Guide: Building and Running Applications中有关“Setting Up the SQL Procedures Environment”方面的内容以获得更多信息。此外，有关隔离级别、锁定和分块的完整说明已超出了本文范围。请参阅 DB2 Administration Guide: Performance和 SQL Reference中有关 DECLARE CURSOR 的条目，以获取完整的说明。

在无副作用的情况下，请使用 SQL 函数

正如我们在简介中提及的，SQL 过程和 SQL 函数是使用不同技术实现的。SQL 过程中的查询是单独编译的，每个查询都成为包中的一个节。编译是在过程创建时进行的，直到重新创建过程或者直到重新绑定其相关的包时才重新编译这些查询。

另一方面，SQL 函数中的查询是一起编译的，就好象函数体是一个查询一样。每当编译一条使用 SQL 函数的语句时，也会对 SQL 函数进行编译。

与 SQL 过程中所发生的情况不同，SQL 函数中的过程语句与数据流语句是在同一个层中执行的。因此，每当控制从过程语句流向数据流语句或相反时，并不发生上下文切换。

因 为存在这些区别，所以当给定的过程代码段作为函数实现时的执行速度通常比作为过程实现时要快。但是，当然了，有一个小问题。函数只能包含那些不会改变数据 库状态的语句（例如 INSERT、UPDATE 或 DELETE 语句是不允许的）。并且只允许完整 SQL PL 语言的子集出现在 SQL 函数中（不能是 CALL 语句、游标和条件处理）。

尽管有这些限制，但大多数 SQL 过程都可以在无副作用的情况下转换成 SQL 函数。例如，下面的过程：

CREATE PROCEDURE GetPrice (IN Vendor CHAR(20),                    IN Pid INT,                    OUT price DECIMAL(10,3))                    LANGUAGE SQL                    BEGIN                    IF Vendor = ‘Vendor 1‘ THEN                    SET price = (SELECT ProdPrice                    FROM V1Table  WHERE Id = Pid);                    ELSE IF Vendor = ‘Vendor 2‘ THEN                    SET price = (SELECT Price                    FROM V2Table  WHERE Pid = GetPrice.Pid);                    END IF;                    END                    

等同于下面的函数：

CREATE FUNCTION GetPrice (Vendor CHAR(20), PId INT)                    RETURNS DECIMAL(10,3)                    LANGUAGE SQL                    BEGIN                    DECLARE price DECIMAL(10,3);                    IF Vendor = ‘Vendor 1‘ THEN                    SET price = (SELECT ProdPrice                    FROM V1Table  WHERE Id = Pid);                    ELSE IF Vendor = ‘Vendor 2‘ THEN                    SET price = (SELECT Price                    FROM V2Table  WHERE Pid = GetPrice.Pid);                    END IF;                    RETURN price;                    END                    

请注意，尽管使用了 CALL 语句来调用过程，但还需要使用 VALUES 语句从命令行调用函数：

VALUES (GetPrice(‘IBM‘, 324))                    

另一方面，与过程不同的是，您可以在允许表达式的任何上下文中调用函数：

SELECT VName FROM Vendors WHERE GetPrice(Vname, Pid) < 100;                    SET price =  GetPrice(Vname, Pid);                    

因此，正如本节标题所展示的，当您只是从数据库抽取数据而不执行任何更改时，请考虑使用 SQL 函数而不是使用 SQL 过程。

使用用于临时数据的临时表

在 V7 中，DB2 引入了临时表。对临时表的操作通常比对常规表的操作快。让我们看一些原因：

• 首先，临时表的创建不会涉及向目录中插入项，并且临时表的使用也不会涉及对目录的访问；因此，不会有目录争用问题。
• 因为临时表只能由创建它们的应用程序访问，因此在其操作中不会涉及锁定问题。
• 如果指定了 NOT LOGGED 选项，则不对临时表上的操作记录日志（当然，这样就不可能回滚更改）。因此，如果您的存储过程生成了大量临时数据，并只打算在数据库的一个会话中使用它们，那么请将这些数据存储进临时表，这样可以显著地改进性能。

在对 SQL 过程中的临时表进行任何应用之前，表定义在编译环境中必须是可用的。例如，在下面的 CLP 脚本（该脚本使用“%”作为语句的终结符）中，表定义的唯一目的就是能够创建 SQL 过程：

CONNECT TO sample %                    DECLARE GLOBAL TEMPORARY TABLE TT(C1 INT, C2 CHAR(20)) %                    CREATE PROCEDURE INSTT(P1 INT, P2 CHAR(20))                    BEGIN                    INSERT INTO SESSION.TT VALUES(P1, P2);                    END %                    CONNECT RESET %                    

在执行了 CONNECT RESET 命令后，临时表将不复存在。在运行时，应用程序必须确保在执行使用临时表的首个查询之前该表是存在的。最后的这个观察引出了一个我们从未提及的要点：引用 临时表的任何查询都将被动态地编译，即使该查询被写成静态的 SQL。跟其它任何动态查询一样，在编译该查询之后，它将以已编译的形式保留在包高速缓存中。在下一次执行相同的查询时，仅当无法在高速缓存发现它时， DB2 才重新编译它。

如果您打算创建相对较大的临时表，并对这些表运行几个查询，请考虑定义索引并对它们运行 runstats（显然后者是填充了表后进行的）。 下一节将介绍更多这方面的内容。

有关在 SQL 过程中使用临时表的最后一个说明是：如果需要根据在同一个过程中创建的临时表返回结果集，那么必须在嵌套的复合语句中定义结果集，如下面的示例所示：

CREATE PROCEDURE INSTT2(P1 INT, P2 CHAR(20))                    BEGIN                    DECLARE GLOBAL TEMPORARY TABLE TT(C1 INT, C2 CHAR(20)) %                    INSERT INTO SESSION.TT VALUES(P1, P2);                    BEGIN                    DECLARE C1 CURSOR WITH RETURN FOR SELECT * FROM SESSION.TT;                    END;                    END %                    

必须在嵌套的复合语句中定义结果集的理由是， DECLARE GLOBAL TEMPORARY TABLE 是一个可执行语句，而可执行语句只能在声明语句（例如 DECLARE CURSOR）之后编写。如果我们在游标定义之后在外部作用域中声明表，那么当编译 DECLARE CURSOR 语句时，该表在编译环境中将不可用，因此编译会失败。

保持 DB2 优化器处于被通知状态

当 创建了一个过程时，其单独的 SQL 查询被编译成包中的节。其中，DB2 优化器根据表的统计信息（例如，表大小或某列中数据值出现的相对频率）以及编译查询时可用的索引来选择查询的执行方案。当表经过了重大更改时，让 DB2 再次收集有关这些表的统计信息可能是个好主意。当更新了统计信息时，或者当创建了新的索引时，重新绑定那些与使用表的 SQL 过程相关联的包，以使 DB2 创建使用最新统计信息和索引的方案，这可能也是一个好主意。

可以使用 RUNSTATS 命令更新表的统计信息。要重新绑定与 SQL 过程关联的包，可以使用 REBIND_ROUTINE_PACKAGE 内置过程（在 DB2 V8 中可用）。例如，可以使用下面这条命令来重新绑定过程 MYSCHEMA.MYPROC 的包：

CALL SYSPROC.REBIND_ROUTINE_PACKAGE(‘P‘, ‘MYSCHEMA.MYPROC‘, ‘ANY‘)                    

其中 ‘P‘ 表明该包对应于一个过程，而 ‘ANY‘ 表明 SQL 路径中的任何函数和类型都被当作函数和类型解析。（请参阅 REBIND 命令的 Command Reference 条目，以获取更多详细信息。）

结束语

在本文中，我提供了一类可能有助于改进 SQL 过程的性能的提示和技巧（请查阅 Yip等 编写的书籍，以获取 SQL PL 的良好简介）。作为一般规则，首先考虑系统性能（硬件和 OS）和数据库管理器（缓冲池、容器和表空间等等）这些基本的问题。DB2 配置顾问程序对于后者非常有帮助。然后请确保应用程序中一些关键查询的方案是合适的。最后，利用本文提供的建议来研究改进您的存储过程和应用程序。祝您在 调优的工作中好运！

参考资料

• [1] An, Yongli 和 Shum, Peter 撰写的文章 OLTP 应用程序的 DB2 调优技巧，DB2 开发者园地，2001 年 7 月。
• [2] Yip, Paul 等撰写的文章， DB2 SQL Procedural Language for Linux, UNIX and Windows，Prentice Hall，2003 年。 请参阅 http://www7b.software.ibm.com/dmdd/library/techarticle/sqlplbook/index.html以获取更多信息。

致谢

感谢 Lee Johnson、Paul Yip、Drew Bradstock 和 Clara Liu 对本文草稿提供的宝贵意见。

关于作者

Gustavo Arocena是 DB2 SQL 编译器方面的技术经理。他于 1998 年加入 IBM 多伦多实验室，目前负责 SQL 过程和 SQL 解析器的开发。Gustavo 拥有多伦多大学计算机科学硕士学位，研究方向是数据库查询语言。