查询性能优化 (SQL Server Compact Edition)
您可以通过优化所用查询来提高 SQL Server 2005 Compact Edition (SQL Server Compact Edition) 应用程序性能。下列各节概述了可以用来优化查询性能的方法。
优化索引
创建有用的索引是提高查询性能的最重要方法之一。有用的索引可帮助减少在查找数据时所需使用的磁盘 I/O 操作数和系统资源量。
若要创建有用的索引,您必须了解下列知识:数据的使用方式,查询的类型及其运行频率,以及查询处理器如何使用索引快速查找数据。
当选择要创建何种索引时,请检查您的关键查询,其性能将对用户体验产生极大的影响。需要创建专门辅助这些查询的索引。在添加索引后,重新运行查询以查看是否提高了性能。如果未提高,则删除索引。
与大多数性能优化方法一样,此方法也有一些缺点。例如,使用较多索引时,SELECT 查询的运行速度很可能会更快。但是,DML(INSERT、UPDATE 和 DELETE)操作的速度将显著减慢,因为对于每个操作都必须维护更多的索引。因此,如果您的查询主要包含 SELECT 语句,则使用较多的索引是非常有帮助的。如果您的应用程序需要执行许多 DML 操作,就有必要控制创建的索引数。
SQL Server Compact Edition 包括对显示计划的支持,显示计划可以帮助评估和优化查询。除了 SQL Server Compact Edition 使用的只是一部分运算符之外,SQL Server Compact Edition 使用与 SQL Server 2005 相同的显示计划架构。有关详细信息,请参阅位于 https://schemas.microsoft.com/sqlserver/2004/07/showplan/ 的 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 关键字,因为它只会增加开销。 |
重写子查询以使用 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 声明语句转换为与手动执行时相同的三个操作。随后,这三个步骤将按顺序执行。选用哪种方法将取决于在应用程序中简易性和性能哪方面更重要。