DB2 9 应用开发（733 考试）认证指南，第 8 部分: 高级编程(3) - DB2

用户定义的数据类型、并发性考虑因素等等

参数标记

参数标记是 SQL 语句中变量的占位符。它们由一个问号字符表示（?）。通过使用参数标记，可以减少需要编译和优化的 SQL 语句数量。您可能想知道，如果已经使用了动态 SQL，那么为什么要在应用程序中使用参数标记，并且每次都要构建语句字符串。您很快就会了解这个常见问题的答案。

一定要记住，一个参数标记替代一个字面值，如下所示：

SELECT firstname, lastname
FROM employee
WHERE empno = ?

SELECT firstname, lastname
FROM employee
WHERE hiredate > ?
    AND mgrno = ?

我们来看三个示例，它们演示了在使用参数标记时常见的错误。下面是第一个示例：

SELECT ?, lastname
FROM employee
WHERE empno = ?

上面的 SQL 语句用一个参数标记替换 SELECT 列表中的一个列名。为了优化查询，DB2 需要知道将要返回哪些列中的数据。所返回的列会极大地影响 DB2 如何检索数据。例如，它们可以确定是使用索引，还是进行表扫描。

让我们看一看另一个错误代码的示例：

SELECT firstname, lastname
FROM ?
WHERE empno = ?

在这个示例中，在 FROM 子句中用一个参数标记替代表名。DB2 无法确定从哪个表中提取数据，因此无法编译这个 SQL 语句。

现在是最后一个示例：

SELECT firstname, lastname
FROM employee
WHERE ? = 14
    AND ? = ?

这个示例在 WHERE 子句中使用参数标记。它首先使用参数标记表示一个要与值 14 进行比较的字段。它还使用两个参数标记来指定第二个谓词中的列和值字段。在这种情况下，DB2 无法确定谓词是什么以及值是什么，因此它无法编译这个语句。

回页首

使用参数标记的优点

参数标记会带来许多优点。其中主要的优点是减少了语句编译时间。DB2 在可以执行 SQL 语句之前先准备这个语句。在准备阶段，创建一个访问计划，它决定如何检索数据。当编译这个 SQL 语句时，DB2 检查称为 “包缓存（package cache）” 的数据库内存区域，其中包含最近执行的 SQL 语句及其相关的访问计划。如果缓存中的一个语句与提交的语句匹配，那么 DB2 可以重用它，而无需重新编译。

要想重用包缓存中的语句，提交的语句必须与缓存中的语句精确地匹配。让我们看一些示例，从而说明什么样的语句是匹配的。假设以下语句是包缓存中的初始语句：

SELECT deptName, location
FROM department
WHERE mgrno = '000056'

以下是一个匹配的语句：

SELECT deptName, location
FROM department
WHERE mgrno = '000056'

但是，下面这些语句都不会被当作匹配的语句：

SELECT deptName, location
FROM department
WHERE mgrno = '000055'

SELECT deptName, location
FROM department
WHERE mgrno = ?

SELECT deptName, location
FROM department
WHERE mgrno = :varMgrno

第一个语句不匹配是因为 '000055' 与 '000056' 不同。DB2 使用直接的文本匹配来检查语句是否相同。第二个语句不匹配是因为参数标记与 '000055' 不匹配。第三个示例匹配失败也是因为同样的原因。

但是，如果在初始语句中使用了参数标记，那么在参数标记的位置上使用任何值的任何语句都会匹配。可以使用在运行时提供的任何值执行使用参数标记的 SQL 语句，例如 '000055'、'005600'、'000123' 等等。

下面这个示例更深入地解释了这个概念。假设以下语句是包缓存中的初始语句：

SELECT deptName, location
FROM department
WHERE mgrno = ?

下列语句将匹配它：

SELECT deptName, location
FROM department
WHERE mgrno = ?

下列语句不匹配：

SELECT deptName, location
FROM department
WHERE mgrno = 55

如果考虑一下查询可能要花多长时间进行编译，那么能够跳过编译阶段的优势就变得很明显了。假设我们有一个查询，其编译时间为 0.001 秒，执行时间为 0.001 秒。那么将要用多长时间执行这个查询 10,000 次呢？以下是确定总时间的简单公式：

总语句时间 = 语句编译时间 + 语句执行时间

在使用参数标记时，结果是：

0.001 + (10000 * 0.001) = 10.001 秒

换句话说，语句只需进行一次编译，然后执行语句 10,000 次。如果不使用参数标记，语句每次执行前都要编译，因为在包缓存中找不到匹配的语句，结果就是：

(10000 * 0.001) + (10000 * 0.001) = 20 秒

在这种情况下，在每次执行时都要承担编译和执行成本。正如这个简单的示例所示，使用参数标记会将总语句时间减半！

每种编程语言都按照自己的方式在运行时为参数标记提供实际值。清单 1 给出一个 SQL 存储过程示例，它在一个准备语句中使用参数标记，然后在执行这个语句之前为参数标记提供值。

清单 1. 在存储过程中使用参数标记

                    
CREATE PROCEDURE SIMPLE_UPDT_PROC (IN  p_emp_id      SMALLINT, 
                                   IN  p_new_salary  DECIMAL)
LANGUAGE SQL
BEGIN
  
  DECLARE v_sqltext       VARCHAR(1000);
  
  SET v_sqltext = 'UPDATE STAFF SET salary=? WHERE id=?';
	
  PREPARE v_stmt1 FROM v_sqltext;
  EXECUTE v_stmt1 USING p_new_salary, p_emp_id;
  
END

在这个存储过程中，首先准备一个 UPDATE 语句，其中包含两个参数标记，然后执行这个语句。因为准备语句时带有参数标记，所以以后对这个过程的调用不会导致语句重新编译，这是因为在包缓存中可以找到这个语句。实际的参数值是在 EXECUTE 语句中通过 USING 子句提供的。

回页首

使用参数标记的缺点

使用参数标记也可能有缺点。最主要的缺点就是缺乏参数值的有关信息。当使用参数标记时，DB2 在编译该语句时不知道参数的值是什么。在执行时，它将以用户提供的值替代参数标记。如果该变量的值对于所选择的 SQL 访问计划具有较大影响，就可能会导致问题。

例如，考虑一个名为 “bigTable” 的表，其中包含玩具的有关信息。假设一共有 50,000 件红色玩具、5 件棕色玩具、800,000 件粉色玩具和 75,000 件绿色玩具。这个应用程序查询当前出售的玩具数量。以下是表的定义：

CREATE TABLE toyLists (
	id       INTEGER NOT NULL,
	color    CHAR(10),
	type     CHAR(10),
	price    INTEGER,
	amount   INTEGER
)

假设这个表在 color 列上有索引。以下两个查询说明当使用这个表时，以何种方式使用参数标记会导致很大的性能差异：

SELECT price, amount
FROM toyLists
WHERE color = 'PINK'
	AND type = 'CAR'



SELECT price, amount
FROM toyLists
WHERE color = 'BROWN'
	AND type = 'CAR'

在第一个查询中，将为颜色 'PINK' 返回 800,000 条记录。然后基于这些粉色玩具中有多少件类型为 'CAR' 的玩具，进一步减少该列表。简单地扫描该表而不使用索引是有意义的，因为会返回如此多的记录。

第二个示例基于颜色 'BROWN' 只返回 5 条记录；当将其搜索范围缩小到 'CAR' 类型时，返回的记录数目可能更少。最好是使用 colour 上的索引来减少必须为类型 'CAR' 搜索的条目数。

现在让我们考虑一下，如果使用参数标记进行类似的搜索，将发生什么情况：

SELECT price, amount
FROM toyLists
WHERE color = ?
	AND type = 'CAR'

在这个示例中，DB2 不知道必须检索什么颜色。它不知道是要返回 5 条还是 300,000 条记录。如果使用不同的颜色值，就可能选择完全不同的访问计划。

当涉及参数标记时，DB2 使用列的平均统计数据生成访问计划。DB2 确定每种颜色平均有多少条记录。因为 'PINK' 十分普遍，所以每种颜色的平均记录数目会非常高，因此 DB2 可能使用表扫描执行每个以颜色作为谓词的查询。这对于过滤 'PINK' 颜色的查询很合适，但是对于 'RED' 或 'BROWN' 颜色却很糟糕。在后两种情况下，在查询中不使用参数标记很可能会更好一些，因为额外的编译时间可能比由于不正确使用表扫描导致的额外处理时间要少很多。

回页首

何时应该使用参数标记

在很多情况下，参数标记可以改善应用程序的性能。应该在以下情况下包含参数标记：

执行多次并只更改其参数值的查询。
其编译时间可能长于执行时间的查询。
短的、OLTP 类型的查询。

但是，有一些查询不会受益于参数标记的使用：

只执行一次且其参数值总是相同的查询。
大的、OLAP 类型的查询，其编译时间只占总执行时间的一小部分。
其中变量值极大地影响 SQL 访问计划的创建方式的查询。
其中参数标记值可能相互关联并导致 DB2 能够识别统计数据中的模式或数据分布（例如 state-city 或 year-month 对）的查询。
对于不均匀分布的数据集运行的查询，因为使用参数标记可能导致 DB2 优化器对于某些值忽略重要的统计偏差。

幸运的是，在 DB2 9 for Linux, UNIX, and Windows 中有一些新的改进，使我们能够影响默认的查询优化行为，这意味着可以克服上面所述的一些缺点。更多信息请参考文章 “Influence query optimization with optimization profiles and statistical views in DB2 9”（参见参考资料一节）。