创建有用的索引是提高查询性能的最重要方法之一。有用的索引可帮助减少在查找数据时所需使用的磁盘 I/O 操作数和系统资源量。
若要创建有用的索引，您必须了解下列知识：数据的使用方式，查询的类型及其运行频率，以及查询处理器如何使用索引快速查找数据。
当选择要创建何种索引时，请检查您的关键查询，其性能将对用户体验产生极大的影响。需要创建专门辅助这些查询的索引。在添加索引后，重新运行查询以查看是否提高了性能。如果未提高，则删除索引。
与 大多数性能优化方法一样，此方法也有一些缺点。例如，使用较多索引时，SELECT 查询的运行速度很可能会更快。但是，DML（INSERT、UPDATE 和 DELETE）操作的速度将显著减慢，因为对于每个操作都必须维护更多的索引。因此，如果您的查询主要包含 SELECT 语句，则使用较多的索引是非常有帮助的。如果您的应用程序需要执行许多 DML 操作，就有必要控制创建的索引数。
SQL Server Compact Edition 包括对显示计划的支持，显示计划可以帮助评估和优化查询。除了 SQL Server Compact Edition 使用的只是一部分运算符之外，SQL Server Compact Edition 使用与 SQL Server 2005 相同的显示计划架构。有关详细信息，请参阅位于 的 Microsoft 显示计划架构。
下面几节介绍有关创建有用索引的其他信息。
创建高选择性索引
对 关键查询的 WHERE 子句中使用的列创建索引，通常会提高性能。但是，这取决于索引选择性的高低。选择性是指符合条件的行数与总行数之比。如果比率较低，索引就是高选择性的。 它可以筛选掉大多数行，从而大大减小结果集的大小。因此，这样的索引就是要创建的有用索引。与之相比，选择性低的索引就没有那么有用。
{wy}索引具有{zg}的选择性。只有一行可以匹配，这对只希望返回一行的查询是最有用的。例如，{wy} 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”列的索引的选择性要高。
有关使用这些存储过程的详细信息，请参阅 、 和 。
创建多列索引
多列索引是单列索引的自然扩展。对于计算与预先指定的一组键列匹配的筛选表达式，多列索引是非常有用的。例如，组合索引 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'
在创建多列查询时，应该将选择性{zg}的列放在键中的最左端。这样，在匹配多个表达式时，就可以使索引的选择性更高。
避免对小表创建索引
小表是指内容只适合一个或几个数据页的表。因为进行表扫描通常效率更高，所以要避免对非常小的表创建索引。这会节省加载和处理索引页的开销。不对非常小的表创建索引，可以避免优化器选择此类索引。
SQL Server Compact Edition 按 4 Kb 一页来存储数据。使用以下公式可以计算出近似的页数，尽管实际的页数由于存储引擎开销的原因可能会稍微多一些。
<列的总大小（字节）> * <行数>
<页数> = -----------------------------------------------------------------
4096
例如，假设一个表的架构如下：
此表包含 2820 行。按照公式，存储该表数据需要大约 16 页：
<页数> = ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15.15 页
建议始终对主键创建索引。另外，对外键创建索引通常也非常有用。这是因为通常使用主键和外键来联接表。创建这些键的索引后，优化器可以使用效率更高的索引联接算法。如果您的查询使用其他列来联接表，由于相同的原因，对这些列创建索引通常也非常有用。
在创建主键和外键的约束后，SQL Server Compact Edition 将自动为其创建索引并在优化查询时加以利用。请记住使用较小的主键和外键。这样，联接的运行速度会更快。
与筛选子句一起使用索引
索引可以用于提高某些类型的筛选子句的计算速度。虽然所有筛选子句都会减小查询的最终结果集，但是某些筛选子句还可以帮助减小所需扫描的数据量。
搜索参数 (SARG) 可指定xx匹配、值的范围或由 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 中使用索引的{dy}列或预先指定的一组列，且 SARG 指定了限制搜索范围的下限、上限或同时执行了这两者，则索引很可能是有用的。
响 应时间是查询返回{dy}条记录所用的时间。总时间是查询返回所有记录所用的时间。对于交互式应用程序，响应时间是很重要的，因为这决定了用户要等待多长时 间，才能通过看到{dy}条返回记录确定查询正在处理中。对于批处理应用程序，总时间反映了总体吞吐量。您必须确定应用程序和查询的性能标准，然后才能相应地 进行设计。
例如，假设查询返回 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 列必须与预先指定的一组索引列匹配，而且顺序要xx相同。例如，索引 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 操作处理多列索引，映射列表必须与所有索引列匹配，尽管顺序不必xx相同。上面的索引有助于优化下列查询：
- ... DISTINCT "Last Name", "First Name" ...
- ... DISTINCT "First Name", "Last Name" ...
但对下列查询，它并没有帮助：
- ... DISTINCT "First Name" ...
- ... DISTINCT "Last Name" ...
有 时可以重写子查询以使用 JOIN，从而提高性能。创建 JOIN 的好处是，可以按与查询所定义顺序不同的顺序评估表。使用子查询的好处是，通常不必扫描子查询中的所有行就可以计算子查询表达式。例如，EXISTS 子查询可以在找到{dy}个符合条件的行时就返回 TRUE。
例如，若要确定包含至少一个折扣率大于或等于 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 中的右表）将首先访问，然后才会访问内部表。这一固定的联接顺序可能会导致执行计划不能达到{zy}。
如果应用程序运行的一系列查询仅有一些常量是不同的，则可以通过使用参数化查询来提高性能。例如，若要按不同的客户返回订单，可以运行下面的查询：
SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?
通 过只编译一次查询然后多次执行编译好的计划，参数化查询可以提供更好的性能。从编程上讲，必须始终保持包含缓存查询计划的命令对象。如果破坏以前的命令对 象再创建新的命令对象，会破坏缓存的计划。这要求重新编译查询。如果必须交替运行多个参数化查询，则可以创建几个命令对象，每个命令对象都包含一个参数化 查询的缓存执行计划。这样，就可以有效避免重新编译所有这些查询了。
SQL Server Compact Edition 查询处理器是查询关系数据库中存储的数据的强大工具。但是，任何查询处理器都会造成一定程度的内部开销。在开始真正地执行计划之前，查询处理器必须编译、 优化和生成执行计划。对于很快完成的简单查询，尤其是这样。因此，您自己执行查询有时可以大大提高性能。如果每一毫秒对您的关键组件都很重要，建议您考虑 自己执行简单查询这一替代方法。对于复杂的大型查询，这种工作{zh0}还是留给查询处理器去做。
例如，假设您希望查找按订单 ID 排列的一系列订单的客户 ID。有两种方法可以实现此操作。{dy}种方法是对于每一次查找都执行下列步骤：
- 打开 Orders 基表
- 使用特定的“Order ID”查找行
- 检索“Customer ID”
或者，对于每一次查找都发出以下查询：
SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>
与 手动解决方案相比，基于查询的解决方案更简单但速度更慢，因为 SQL Server Compact Edition 查询处理器需要将 SQL 声明语句转换为与手动执行时相同的三个操作。随后，这三个步骤将按顺序执行。选用哪种方法将取决于在应用程序中简易性和性能哪方面更重要。
posted on 2010-05-27 17:13 阅读(9) 所属分类:
