SQL Server 2005 查询性能优化(转)

优化索引

创建有用的索引是提高查询性能的最重要方法之一。有用的索引可帮助减少在查找数据时所需使用的磁盘 I/O 操作数和系统资源量。

若要创建有用的索引，您必须了解下列知识：数据的使用方式，查询的类型及其运行频率，以及查询处理器如何使用索引快速查找数据。

当选择要创建何种索引时，请检查您的关键查询，其性能将对用户体验产生极大的影响。需要创建专门辅助这些查询的索引。在添加索引后，重新运行查询以查看是否提高了性能。如果未提高，则删除索引。

与大多数性能优化方法一样，此方法也有一些缺点。例如，使用较多索引时，SELECT 查询的运行速度很可能会更快。但是，DML（INSERT、UPDATE 和 DELETE）操作的速度将显著减慢，因为对于每个操作都必须维护更多的索引。因此，如果您的查询主要包含 SELECT 语句，则使用较多的索引是非常有帮助的。如果您的应用程序需要执行许多 DML 操作，就有必要控制创建的索引数。

SQL Server Compact Edition 包括对显示计划的支持，显示计划可以帮助评估和优化查询。除了 SQL Server Compact Edition 使用的只是一部分运算符之外，SQL Server Compact Edition 使用与 SQL Server 2005 相同的显示计划架构。有关详细信息，请参阅位于 http://go.microsoft.com/fwlink/?LinkId=38029 的 Microsoft 显示计划架构。

下面几节介绍有关创建有用索引的其他信息。

创建高选择性索引

对关键查询的 WHERE 子句中使用的列创建索引，通常会提高性能。但是，这取决于索引选择性的高低。选择性是指符合条件的行数与总行数之比。如果比率较低，索引就是高选择性的。它可以筛选掉大多数行，从而大大减小结果集的大小。因此，这样的索引就是要创建的有用索引。与之相比，选择性低的索引就没有那么有用。

唯一索引具有最高的选择性。只有一行可以匹配，这对只希望返回一行的查询是最有用的。例如，唯一 ID 列的索引可帮助您快速查找特定的行。

通过对 SQL Server Compact Edition 表运行 sp_show_statistics 存储过程，您可以评估索引的选择性。例如，若要评估两个列（“Customer ID”和“Ship Via”）的选择性，可以运行下列存储过程：

sp_show_statistics_steps 'orders', 'customer id';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

ALFKI 0 7 0

ANATR 0 4 0

ANTON 0 13 0

AROUT 0 14 0

BERGS 0 23 0

BLAUS 0 8 0

BLONP 0 14 0

BOLID 0 7 0

BONAP 0 19 0

BOTTM 0 20 0

BSBEV 0 12 0

CACTU 0 6 0

CENTC 0 3 0

CHOPS 0 12 0

COMMI 0 5 0

CONSH 0 4 0

DRACD 0 9 0

DUMON 0 8 0

EASTC 0 13 0

ERNSH 0 33 0

(90 rows affected)

和

sp_show_statistics_steps 'orders', 'reference3';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

1 0 320 0

2 0 425 0

3 0 333 0

(3 rows affected)

结果表明“Customer ID”列的重复程度更低。这意味着其索引的选择性将比“Ship Via”列的索引的选择性要高。

有关使用这些存储过程的详细信息，请参阅 sp_show_statistics (SQL Server Compact Edition)、sp_show_statistics_steps (SQL Server Compact Edition) 和 sp_show_statistics_columns (SQL Server Compact Edition)。

创建多列索引

多列索引是单列索引的自然扩展。对于计算与预先指定的一组键列匹配的筛选表达式，多列索引是非常有用的。例如，组合索引 CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) 有助于计算下列查询：

... WHERE "Last Name" = 'Doe'
... WHERE "Last Name" = 'Doe' AND "First Name" = 'John'
... WHERE "First Name" = 'John' AND "Last Name" = 'Doe'

但是，它对下面的查询是没有用的：

... WHERE "First Name" = 'John'

在创建多列查询时，应该将选择性最高的列放在键中的最左端。这样，在匹配多个表达式时，就可以使索引的选择性更高。

避免对小表创建索引

小表是指内容只适合一个或几个数据页的表。因为进行表扫描通常效率更高，所以要避免对非常小的表创建索引。这会节省加载和处理索引页的开销。不对非常小的表创建索引，可以避免优化器选择此类索引。

SQL Server Compact Edition 按 4 Kb 一页来存储数据。使用以下公式可以计算出近似的页数，尽管实际的页数由于存储引擎开销的原因可能会稍微多一些。

<列的总大小（字节）> * <行数>

<页数> = -----------------------------------------------------------------

4096

例如，假设一个表的架构如下：

列名	类型（大小）
订单 ID	INTEGER（4 字节）
产品 ID	INTEGER（4 字节）
单价	MONEY（8 字节）
数量	SMALLINT（2 字节）
折扣	REAL（4 字节）

此表包含 2820 行。按照公式，存储该表数据需要大约 16 页：

<页数> = ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15.15 页

选择要创建索引的对象

建议始终对主键创建索引。另外，对外键创建索引通常也非常有用。这是因为通常使用主键和外键来联接表。创建这些键的索引后，优化器可以使用效率更高的索引联接算法。如果您的查询使用其他列来联接表，由于相同的原因，对这些列创建索引通常也非常有用。

在创建主键和外键的约束后，SQL Server Compact Edition 将自动为其创建索引并在优化查询时加以利用。请记住使用较小的主键和外键。这样，联接的运行速度会更快。

与筛选子句一起使用索引

索引可以用于提高某些类型的筛选子句的计算速度。虽然所有筛选子句都会减小查询的最终结果集，但是某些筛选子句还可以帮助减小所需扫描的数据量。

搜索参数 (SARG) 可指定精确匹配、值的范围或由 AND 联接的两项或多项的连接，因此能够限制搜索范围。搜索参数采用以下格式之一：

列运算符 <常量或变量>

<常量或变量> 运算符列

SARG 运算符包括 =、>、<、>=、<=、IN、BETWEEN，有时还包括 LIKE（在进行前缀匹配时，如 LIKE 'John%'）。SARG 可以包括由 AND 联接的多个条件。SARG 还可以是匹配特定值的查询，例如：

"Customer ID" = 'ANTON'
'Doe' = "Last Name"

SARG 也可以是匹配一定范围的值的查询，例如：

"Order Date" > '1/1/2002'
"Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'
"Customer ID" IN ('ANTON', 'AROUT')

不使用 SARG 运算符的表达式无法提高性能，因为 SQL Server Compact Edition 查询处理器必须评估所有行，以确定是否满足筛选子句。因此，对于不使用 SARG 运算符的表达式，索引是没有用的。非 SARG 运算符包括 NOT、<>、NOT EXISTS、NOT IN、NOT LIKE 和内部函数。

使用查询优化器

在确定基表的访问方法时，SQL Server Compact Edition 优化器将确定是否存在 SARG 子句的索引。如果存在索引，优化器将通过计算返回多少行来评估索引。然后，优化器会估计通过使用索引查找符合条件的行的开销。如果使用索引的开销比表扫描的开销低，优化器将选择索引访问。如果在 SARG 中使用索引的第一列或预先指定的一组列，且 SARG 指定了限制搜索范围的下限、上限或同时执行了这两者，则索引很可能是有用的。

了解响应时间与总时间

响应时间是查询返回第一条记录所用的时间。总时间是查询返回所有记录所用的时间。对于交互式应用程序，响应时间是很重要的，因为这决定了用户要等待多长时间，才能通过看到第一条返回记录确定查询正在处理中。对于批处理应用程序，总时间反映了总体吞吐量。您必须确定应用程序和查询的性能标准，然后才能相应地进行设计。

例如，假设查询返回 100 条记录，前五条记录用来填充一个列表。在此情况下，您不关心返回全部 100 条记录所用的时间。相反，您只希望查询快速返回前几条记录，以便填充列表。

可以执行许多查询操作，而无需存储中间结果。这些操作称为管道操作。管道操作的示例是映射、选择和联接。通过这些操作执行的查询可以立即返回结果。其他操作（如 SORT 和 GROUP-BY）需要使用所有输入信息，才可以将结果返回到父操作。这就是我们所说的要求具体化的操作。由于具体化的原因，通过这些操作执行的查询通常会有初始延迟。在此初始延迟之后，此类查询通常也可以很快地返回记录。

具有响应时间要求的查询应避免具体化。例如，与使用排序相比，使用索引执行 ORDER-BY 的响应时间更短。下一节将对此进行详细说明。

对 ORDER-BY/GROUP-BY/DISTINCT 列创建索引以缩短响应时间

ORDER-BY、GROUP-BY 和 DISTINCT 操作都是排序类型的操作。SQL Server Compact Edition 查询处理器使用两种方法进行排序。如果记录已经按索引进行排序，则处理器只使用索引。否则，处理器必须先使用临时工作表对记录进行排序。在 CPU 较慢并且内存较低的设备上，这样的预先排序会造成显著的初始延迟，因此，在响应时间比较重要时，应该避免预先排序。

在使用多列索引的情况下，为了使 ORDER-BY 或 GROUP-BY 处理特定索引，ORDER-BY 或 GROUP-BY 列必须与预先指定的一组索引列匹配，而且顺序要完全相同。例如，索引 CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) 有助于优化下列查询：

... ORDER BY / GROUP BY "Last Name" ...
... ORDER BY / GROUP BY "Last Name", "First Name" ...

但对下列查询，它并没有帮助：

... ORDER BY / GROUP BY "First Name" ...
... ORDER BY / GROUP BY "First Name", "Last Name" ...

为了使 DISTINCT 操作处理多列索引，映射列表必须与所有索引列匹配，尽管顺序不必完全相同。上面的索引有助于优化下列查询：

... DISTINCT "Last Name", "First Name" ...
... DISTINCT "First Name", "Last Name" ...

但对下列查询，它并没有帮助：

... DISTINCT "First Name" ...
... DISTINCT "Last Name" ...

注意：
如果您的查询始终返回唯一行，请不要指定 DISTINCT 关键字，因为它只会增加开销。

注意：

如果您的查询始终返回唯一行，请不要指定 DISTINCT 关键字，因为它只会增加开销。

重写子查询以使用 JOIN

有时可以重写子查询以使用 JOIN，从而提高性能。创建 JOIN 的好处是，可以按与查询所定义顺序不同的顺序评估表。使用子查询的好处是，通常不必扫描子查询中的所有行就可以计算子查询表达式。例如，EXISTS 子查询可以在找到第一个符合条件的行时就返回 TRUE。

注意：
SQL Server Compact Edition 查询处理器始终会重写 IN 子查询以使用 JOIN。对包含 IN 子查询子句的查询，不必使用此方法。

例如，若要确定包含至少一个折扣率大于或等于 25% 的项的所有订单，可以使用下面的 EXISTS 子查询：

SELECT "Order ID" FROM Orders O

WHERE EXISTS (SELECT "Order ID"

FROM "Order Details" OD

WHERE O."Order ID" = OD."Order ID"

AND Discount >= 0.25)

您也可以使用 JOIN 对其进行重写：

SELECT DISTINCT O."Order ID" FROM Orders O INNER JOIN "Order Details"

OD ON O."Order ID" = OD."Order ID" WHERE Discount >= 0.25

使用 Outer JOIN 进行限制

OUTER JOIN 与 INNER JOIN 的处理方式是不同的：对于 INNER JOIN 表，优化器会尝试重新排列联接顺序，而对于 OUTER JOIN 表则不会。外部表（LEFT OUTER JOIN 中的左表和 RIGHT OUTER JOIN 中的右表）将首先访问，然后才会访问内部表。这一固定的联接顺序可能会导致执行计划不能达到最优。

注意：
SQL Server Compact Edition 查询处理器假定 INNER JOIN 包括的列上有索引（由用户或数据库创建）。

使用参数化查询

如果应用程序运行的一系列查询仅有一些常量是不同的，则可以通过使用参数化查询来提高性能。例如，若要按不同的客户返回订单，可以运行下面的查询：

SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?

通过只编译一次查询然后多次执行编译好的计划，参数化查询可以提供更好的性能。从编程上讲，必须始终保持包含缓存查询计划的命令对象。如果破坏以前的命令对象再创建新的命令对象，会破坏缓存的计划。这要求重新编译查询。如果必须交替运行多个参数化查询，则可以创建几个命令对象，每个命令对象都包含一个参数化查询的缓存执行计划。这样，就可以有效避免重新编译所有这些查询了。

仅在必要时进行查询

SQL Server Compact Edition 查询处理器是查询关系数据库中存储的数据的强大工具。但是，任何查询处理器都会造成一定程度的内部开销。在开始真正地执行计划之前，查询处理器必须编译、优化和生成执行计划。对于很快完成的简单查询，尤其是这样。因此，您自己执行查询有时可以大大提高性能。如果每一毫秒对您的关键组件都很重要，建议您考虑自己执行简单查询这一替代方法。对于复杂的大型查询，这种工作最好还是留给查询处理器去做。

例如，假设您希望查找按订单 ID 排列的一系列订单的客户 ID。有两种方法可以实现此操作。第一种方法是对于每一次查找都执行下列步骤：

打开 Orders 基表
使用特定的“Order ID”查找行
检索“Customer ID”

或者，对于每一次查找都发出以下查询：

SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>

与手动解决方案相比，基于查询的解决方案更简单但速度更慢，因为 SQL Server Compact Edition 查询处理器需要将 SQL 声明语句转换为与手动执行时相同的三个操作。随后，这三个步骤将按顺序执行。选用哪种方法将取决于在应用程序中简易性和性能哪方面更重要。

春夏秋冬