数据访问代码如何影响数据库性能
Bob Beauchemin
关于查询优化和一般的数据库应用程序性能优化到底应该是数据库管理员、应用程序开发人员还是二者共有的责任，这一直是个争论不休的问题。 与开发人员相比，数据库管理员通常可以访问更多工具。 数据库管理员可以查看性能监视器计数器和动态管理视图，可以运行 SQL 事件探查器，可以确定数据库的放置位置，并且可以创建索引以便更好地执行查询。 而应用程序开发人员通常负责编写用于访问数据库的查询和存储过程。 开发人员可以在一个测试系统中使用大多数相同的工具，并且可以根据应用程序的设计和使用情况的知识提供有用的索引建议。 但一个经常被忽视的问题是，用于访问数据库的数据库 API 代码是应用程序开发人员编写的。 而访问数据库（例如 ADO.NET、OLE DB 或 ODBC）的代码可以对数据库性能产生影响。 当尝试编写一个通用的数据访问框架，或选择一个现有的框架时，这一点显得尤其重要。 在本文中，我们将深入探讨一些用于编写数据访问代码的典型方法，并介绍它们对性能的影响。
查询计划重用
我们首先介绍一下查询的生命周期。 当通过查询处理器提交查询时，查询处理器将会分析文本的语法正确性。 针对存储过程中的查询的语法检查将会在 CREATE PROCEDURE 语句的执行过程中进行。 在执行查询或存储过程之前，处理器会在计划缓存中查找与文本匹配的查询计划。 如果它找到匹配文本，则会重用此查询计划；如果未找到匹配项，则会为查询文本创建一个查询计划。 执行查询后，计划将被返回到缓存中以供今后重用。 创建查询计划是一项代价较高的操作，因此一般来说应尽量重用查询计划。 在比较缓存中的计划文本和查询文本时，字符串的比较是区分大小写的。
因此，查询
SELECT a.au_id, ta.title_id FROM authors a
JOIN titleauthor ta ON a.au_id = ta.au_id
WHERE au_fname = 'Innes';
将不匹配
SELECT a.au_id, ta.title_id FROM authors a
JOIN titleauthor ta ON a.au_id = ta.au_id
WHERE au_fname = 'Johnson';
请注意上述查询与下列文本也不匹配
SELECT a.au_id, ta.title_id
FROM authors a JOIN titleauthor ta ON a.au_id = ta.au_id
WHERE au_fname = 'Innes';
这是因为语句中换行符的位置不同。 为了帮助查询计划重用，SQL Server 查询处理器可以执行一个被称为自动参数化 (Autoparameterization) 的过程。 自动参数化会将类似如下语句
SELECT * FROM authors WHERE au_fname = 'Innes'
更改为以下参数化语句和参数声明：
(@0 varchar(8000))SELECT * FROM authors WHERE au_fname = @0
使 用 sys.dm_exec_query_stats 或 sys.dm_exec_cache_plans 可以在计划缓存中查看这些语句，使用带有 CROSS APPLY 的 sys.dm_exec_sql_text(handle) 可以将文本与其他信息相关联。 尽管自动参数化有助于查询计划重用，但它并不完美。
例如，语句
SELECT * FROM titles WHERE price > 9.99
经参数化，将成为
(@0 decimal(3,2))SELECT * FROM titles WHERE price > @0
SELECT * FROM titles WHERE price > 19.99
经参数化，将使用不同的数据类型
(@0 decimal(4,2))SELECT * FROM titles WHERE price > @0
SELECT * FROM titles WHERE price > $19.99
经参数化，将成为
(@0 money)SELECT * FROM titles WHERE price > @0
如果相似的查询原本可以使用同一个计划，但却使用了多个查询计划，此种情况被称为计划缓存污染。 这不仅会使计划缓存中充斥着众多冗余计划，而且创建冗余计划也会导致时间（以及 CPU 和 I/O）的消耗。 请注意，在自动参数化过程中查询处理器必须根据参数值来“猜测”参数类型。 自动参数化有助于减少计划缓存污染，但不能完全消除计划缓存污染。 此外，参数化查询的文本经过规范化，这样即使原始文本使用不同的格式，也可以重用计划。 根据查询的复杂性，自动参数化仅适用于一部分查询。 尽管全面探讨所有自动参数化规则超出了本文的范围，但您应知道 SQL Server 使用以下两个规则集之一：简单参数化和强制参数化。 两者不同之处的一个示例就是简单参数化功能将不会自动参数化多表查询，而强制参数化则会。
存储过程和参数化查询
一个比较好的选择是使用参数化查询或存储过程。 这些技术不仅有助于查询计划重用，而且如果您正确定义了参数，则永远不必执行数据类型猜测。 最佳选择是使用存储过程，因为参数数据类型是在存储过程定义中指定的。 请记住，存储过程也并不完美。 一个问题是数据库对象名称解析并非在 CREATE PROCEDURE 时完成；表或列名称不存在将会导致执行时错误。 此外，尽管存储过程的参数在应用程序代码和过程代码之间建立了一个“约定”，但存储过程也可以返回结果集。 由于没有元数据定义，因此在结果集的数字以及结果集列的数字和数据类型上不存在约定。
在数据库 API 代码中至少可以有两种方式来调用存储过程。 下面是使用 ADO.NET 方式的示例：
SqlCommand cmd = new SqlCommand("EXECUTE myproc 100", conn);
int i = cmd.ExecuteNonQuery();
SqlCommand cmd = new SqlCommand("myproc", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@a", SqlDbType.Int); cmd.Parameters.Value = 100;
int i = cmd.ExecuteNonQuery();
以 命令字符串 (CommandType.Text) 的形式执行存储过程，而不使用 ADO.NET 的 ParameterCollection，这种方式使用 SQL Server 语言语句，同时使用 CommandType.StoredProcedure 也可产生开销较低的远程过程调用 (RPC)。 这种差异可以在 SQL 事件探查器中观察到。 由于查询计划的创建时间，如何传递参数也是非常重要的，但我们将在稍后讨论此问题。
参数化查询使用 API 中参数的方式与存储过程类似，但有一些重要的不同。 ADO.NET 的 SqlParameter 类不仅包含参数名称和值的属性，而且还包含参数数据类型、长度、精度和小数位数等属性。 为了避免计划缓存污染，在参数化查询中为所有相关参数指定正确的值是非常重要的。 否则，因为没有提供参数约定（这与存储过程不同），ADO.NET 必须猜测这些属性。 这类似于自动参数化的猜测方式，但其实现则有一些不同。 下面的图 1 和图 2 显示了 SQL Server 2008 中的自动参数化的当前实现以及 ADO.NET 3.5 SP1 的 SqlParameterCollection 的 AddWithValue 方法。
| 图 1 自动参数化生成的参数数据类型 |
| 文本类型 | 生成的参数 |
| 非 Unicode 字符串 | VARCHAR(8000) |
| Unicode 字符串 | NVARCHAR(4000) |
| 整数 | 最小适应：TINYINT、SMALLINT、INT 或 BIGINT |
| 小数 | DECIMAL(p,s)，精度和小数位数与文本匹配 |
| 货币符号数字 | MONEY |
| 图 2 ADO.NET 的 AddWithValue 和参数化查询生成的参数数据类型 |
| 文本类型 | 生成的参数 |
| 字符串 | NVARCHAR(x)，其中 x 是字符串的长度 |
| 整数 | 最小适应：INT 或 BIGINT |
| 小数 | FLOAT(53)。说明：这是双精度浮点数 |
当使用参数化查询时，使用 Parameters.AddWithValue 并不是好办法。 因为在这种情况下，ADO.NET 必须猜测数据类型，而且如果您使用字符串和 AddWithValue，还会有一个特殊的危害。 首先，.NET 字符串类是一个 Unicode 字符串，而在 T-SQL 中字符串常量可以被指定为 Unicode 或非 Unicode 字符串。 ADO.NET 将传入一个 Unicode 字符串参数（SQL 中 的 NVARCHAR）。 如果谓词中使用的列是非 Unicode 类型，那么这可能会对查询计划本身产生负面影响。 例如，假设您有一个表，表中使用一个 VARCHAR 列作为聚集主键：
CREATE TABLE sample (
thekey varchar(7) primary key,
name varchar(20) -- other columns omitted
)
在上述 ADO.NET 代码中，我希望按主键进行查找：
cmd.CommandText = "SELECT * FROM sample when thekey = @keyvalue;"
我使用以下代码指定了参数：
cmd.Parameters.AddWithValue("@keyvalue", "ABCDEFG");
ADO.NET 将决定参数数据类型为 NVARCHAR(7)。 由于在查询执行过程的检索行步骤中，数据类型将从 NVARCHAR 转为 VARCHAR，因此参数值将不能用作搜索参数。 这样，查询不是执行一行的聚集索引查找，而将执行整个表的聚集索引扫描。 现在，假设这是一个具有 5 百万个行的表。 此时，因为您已提交了参数化查询，SQL Server 自动参数化将无法提供任何帮助，而数据库管理员也无法通过在服务器中更改来修正此行为。 作为最后的办法，使用 FORCESEEK 查询提示也根本无法生成计划。 而当您将参数类型指定为 SqlDbType.VarChar 而不是让 ADO.NET 猜测数据类型时，查询的响应时间将从秒级（这是最快的结果）降低到毫秒级。
参数长度
对于基于字符串的数据类型，另外一个好习惯就是总是指定参数的长度。 此值应该是使用参数的 SQL 谓词中的字段的长度或最大字符串长度（对于 NVARCHAR 为 4，000，对于 VARCHAR 为 8，000），而不是字符串本身的长度。 SQL Server 自动参数化始终假定使用字符串最大长度，但 SqlParameterCollection.AddWithValue 将使参数长度等于字符串的长度。 因此，使用下面的调用将产生不同的参数数据类型，并因此而产生不同的计划：
// produces an nvarchar(5) parameter
cmd.Parameters.AddWithValue(
"SELECT * FROM authors WHERE au_fname = @name", "@name", "Innes");
// produces an nvarchar(7) parameter
cmd.Parameters.AddWithValue(
"SELECT * FROM authors WHERE au_fname = @name", "@name", "Johnson");
在使用 ParameterCollection.AddWithValue 时，如果不指定长度，那么有多少个不同的字符串长度，在计划缓存中就会有多少个不同的查询。 这就是计划缓存污染的一大来源。 尽管我提到 ADO.NET 可能会有此行为，但请注意其他数据库 API 也存在字符串参数计划缓存污染的问题。 LINQ to SQL 和 ADO.NET 实体框架的当前版本在此行为上的表现有所不同。 使用一般的 ADO.NET，您可以指定字符串参数的长度；而使用框架，转换为 API 调用是由 LINQ to SQL 或实体框架本身完成的，因此您无法影响其字符串参数计划缓存污染。 在即将发布的 .NET Framework 4 版本中，LINQ to SQL 和实体框架都将会解决此问题。 因此，如果您使用自己的参数化查询，请不要忘记指定正确的 SqlDbType、字符串参数的长度以及十进制参数的精度和小数位数。 在这里，性能完全是程序员的领域，大多数数据库管理员即使关注性能也不会检查您的 ADO.NET 代码。 如果您使用存储过程，显式参数约定将确保您始终使用正确的参数类型和长度。
尽管您应始终在代码中使用参数化的 SQL 语句，并尽可能输出存储过程，但在少数情况下也可能无法使用参数化。 您无法在 SQL 语句中参数化列名或表名。 这包括 DDL（数据定义语言语句）和 DML（数据操作语言语句）。 因此，尽管参数化有助于提高性能，并且它是防止 SQL 注入攻击（使用字符串串联而非参数可以允许恶意用户在您的代码中注入额外的 SQL 代码）的最佳安全措施，但您并非总是可以将所有内容都参数化。
设置参数值的位置也是非常重要的。 在使用参数化查询时，如果您使用 SQL 事件探查器查看过 ADO.NET 生成的 SQL，您就会注意到它看起来并非类似如下代码：
(@0 VARCHAR(40))SELECT * FROM authors WHERE au_fname = @0
而是类似如下形式：
sp_executesql N'SELECT * FROM authors WHERE au_fname = @name',
N'@name VARCHAR(40)', 'Innes'
过程 sp_executesql 是一个系统存储过程，它执行可包含参数的动态生成的 SQL 字符串。 ADO.NET 使用它来执行参数化查询的一个原因是这将导致使用系统开销较低的 RPC 调用。 使用 sp_executesql 的另一个重要原因是这样可以启用被称为“参数嗅探”的 ADO.NET 查询处理器行为。这将产生最佳的性能，因为查询处理在计划生成时就知道参数值，并可以充分地利用其统计信息。
SQL Server 统计信息
SQL Server 使用统计信息以帮助生成最佳的作业查询计划。 两种主要的统计信息类型为密度统计信息（指定列中存在多少个唯一值）和基数统计信息（值分布的直方图）。有关这些统计信息的更多信息，请参考白皮书 Microsoft SQL Server 2005 的查询优化器使用的统计信息，本白皮书由 Eric N. Hanson 和 Lubor Kollar 编写。 了解 SQL Server 如何使用统计信息的关键，是了解 SQL Server 将会在批处理中为所有查询创建查询计划，或是在批处理或存储过程的开头为存储过程创建查询计划。 如果查询处理器在计划生成时知道参数的值，它就可以使用基数和密度统计信息。 如果在计划创建时不知道参数值，那么仅可以使用密度统计信息。 例如，如果 ADO.NET 程序员使用类似如下参数，查询处理器就没有办法知道您正在寻找来自加利福尼亚的作者，并在州列上使用基数统计信息：
cmd.CommandText = "declare @a char(2); set @a = 'CA'; select * from authors where state = @a ";
SqlDataReader rdr = cmd.ExecuteReader();
计划将会在第一个语句（本例中为 DECLARE 语句）之前创建，而此时还没有分配参数值。 这就是为什么参数化查询被转换成 sp_executesql 的原因。 在这种情况下，系统将在进入 sp_executesql 存储过程时创建计划，而查询处理器可以在计划生成时嗅探参数的值。 参数值将在 sp_executesql 调用中指定。 如果您编写存储过程，相同的概念也适用。 假设您有一个查询，如果传入的值为 NULL 则将检索来自加利福尼亚的作者，否则用户必须指定所需的州，如下所示：
CREATE PROCEDURE get_authors_by_state (@state CHAR(2))
AS
BEGIN
IF @state IS NULL THEN @state = 'CA';
SELECT * FROM authors WHERE state = @state;
END
现在，在大多数常见的情况下（不指定任何参数，并且州为 NULL），将会根据值 NULL 来优化查询，而不是值“CA”。如果 CA 是列的公共值，那么您将可能得到错误的计划。 因此，在 ADO.NET 中使用参数化查询时，请记住这意味着使用 SqlParameterCollection，而不要在 SQL 语句本身中指定参数声明并赋值。 如果您正在编写存储过程，请记住在代码中设置参数的做法与参数探测是相悖的。 请注意在上述使用 pubs 数据库中作者表的示例中，您将不会看到不同查询计划，因为它太小。 在大型表中，这可能会影响所使用的实际 JOIN 类型，并间接影响查询计划的其他部分。 有关参数探测如何影响查询计划的示例，请参阅 Arun Marathe 和 Shu Scott 编写的白皮书 SQL Server 2005 中的批编译、重新编译和计划缓存问题。
探测参数通常是一个好方法。 系统将会计算基数统计信息，以便在每个直方图步骤中得到近似相等的行数，这样任何参数值从总体上来说都表示基数。 但是，由于基数存储桶的数量存在上限（最大 200 存储桶），而且一些列主要包含重复的值，因此使用这种方法也并非总是最好。 假设您有一个客户表。 由于您的业务在加利福尼亚开展，因此您的客户大多数位于加利福尼亚。 我们假设有 200，000 个加利福尼亚客户和 10 个俄勒冈客户。 根据要查找的是加利福尼亚的客户还是俄勒冈的客户，在您的客户表中加入其他五个表的查询计划可能会有很大的不同。 假设第一个查询是查询俄勒冈的客户，此时如果您缓存了计划并对一位加利福尼亚客户重用了此计划，那么系统将认为您具有 10 位加利福尼亚客户，而非具有大量加利福尼亚客户。 在这种情况下，使用基数统计信息不但没有帮助，反而有害。 解决此问题的最简单的（但也最脆弱的）办法，是在应用程序或单独的存储过程中使用条件代码来调用两个不同存储过程，一个用于具有大量客户的州，一个用于具有少量客户的州。 如果查询发生在两个不同的存储过程中，即使查询完全相同 SQL Server 也不会共用查询计划。 脆弱的部分在于如何确定“具有大量客户的州”，并且要注意客户的分布位置会随时更改。 SQL Server 也提供了一些查询提示，以帮助解决此问题。 如果您确定让每个人都使用加利福尼亚客户的计划是合适的（因为您只有少数几个行要使用其他州的计划来处理），那么您可以使用查询提示 OPTION (OPTIMIZE FOR parameter_name=value)。 这可以确保缓存中的计划始终是适用于具有大量客户的州的计划。 作为替代方法，您可以使用 SQL Server 2008 的新 OPTION (OPTIMIZE FOR UNKNOWN) 提示，这可以使 SQL Server 忽略基数统计信息，并提供一个可能未针对客户数量较多或较少的州进行优化的中间计划。 此外，如果您有一个使用多个参数的查询，但一次只对它们使用一个值（假设有这样一个系统：有人可能使用由相同查询中的参数定义的一到十个不同的条件进行搜索），那么您的最佳做法可能每次都专门生成不同的查询计划。 这可以用一个 OPTION RECOMPILE 查询提示指定。
正确的作业计划
总的说来，使用参数可以防止计划缓存污染和 SQL 注入攻击。 请始终使用参数化查询或参数化存储过程。 请始终指定正确的数据类型、长度、精度和小数位数，这可以确保您在执行时不需要进行数据类型强制。 请在查询计划创建时确保值可用，这样可确保优化器可以访问它需要的所有统计信息。 如果参数探测成为问题（因为缓存太大），请不要对每个查询都使用一个计划，因为每个计划都会污染缓存。 而是使用查询提示或存储过程，以确保您获得正确的作业计划。 请记住作为应用程序程序员，您编写的数据访问和存储过程代码可以极大地影响性能。原文URL:http://msdn.microsoft.com/zh-cn/magazine/ee236412.aspx
