现在的位置: 首页 > 综合 > 正文

SQLSERVER查询性能优化

2012年02月01日 ⁄ 综合 ⁄ 共 7307字 ⁄ 字号 评论关闭
文章目录
优化索引

创建有用的索引是提高查询性能的最重要方法之一。有用的索引可帮助减少在查找数据时所需使用的磁盘 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://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。有两种方法可以实现此操作。第一种方法是对于每一次查找都执行下列步骤:

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

或者,对于每一次查找都发出以下查询:

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

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

抱歉!评论已关闭.