五种提高 SQL 性能的方法
(http://topic.csdn.net/u/20080509/10/c9bee345-3f57-4632-a84d-1673b1e96bf8.html)

有时， 为了让应用程序运行得更快，所做的全部工作就是在这里或那里做一些很小调整。啊，但关键在于确定如何进行调整！迟早您会遇到这种情况：应用程序中 的 SQL 查询不能按照您想要的方式进行响应。它要么不返回数据，要么耗费的时间长得出奇。如果它降低了报告或您的企业应用程序的速度，用户必须等待的 时间过长，他们就会很不满意。就像您的父母不想听您解释为什么在深更半夜才回来一样，用户也不会听你解释为什么查询耗费这么长时间。（“对不起，妈妈，我 使用了太多的 LEFT JOIN。”）用户希望应用程序响应迅速，他们的报告能够在瞬间之内返回分析数据。就我自己而言，如果在 Web 上冲浪时某个 页面要耗费十多秒才能加载（好吧，五秒更实际一些），我也会很不耐烦。 

为了解决这些问题，重要的是找到问题的根源。那么，从哪里开始呢？根本原因通常在于数据库设计和访问它的查询。在本月的专栏中，我将讲述四项技术，这些技 术可用于提高基于 SQL Server? 的应用程序的性能或改善其可伸缩性。我将仔细说明 LEFT JOIN、CROSS JOIN 的使用以 及 IDENTITY 值的检索。请记住，根本没有神奇的解决方案。调整您的数据库及其查询需要占用时间、进行分析，还需要大量的测试。这些技术都已被证 明行之有效，但对您的应用程序而言，可能其中一些技术比另一些技术更适用。 

从 INSERT 返回 IDENTITY 
我决定从遇到许多问题的内容入手：如何在执行 SQL INSERT 后检索 IDENTITY 值。通常，问题不在于如何编写检索值的查询，而在于在哪 里以及何时进行检索。在 SQL Server 中，下面的语句可用于检索由最新在活动数据库连接上运行的 SQL 语句所创建 的 IDENTITY 值： 

SELECT @@IDENTITY这个 SQL 语句并不复杂，但需要记住的一点是：如果这个最新的 SQL 语句不是 INSERT，或者您针对 非 INSERT SQL 的其他连接运行了此 SQL，则不会获得期望的值。您必须运行下列代码才能检索紧跟在 INSERT SQL 之后且位于同一 连接上的 IDENTITY，如下所示： 

INSERT INTO Products (ProductName) VALUES ('Chalk') 
SELECT @@IDENTITY在一个连接上针对 Northwind 数据库运行这些查询将返回一个名称为 Chalk 的新产品的 IDENTITY 值。所以，在使用 ADO 的 Visual Basic? 应用程序中，可以运行以下语句： 

Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _ 
(ProductName) VALUES ('Chalk');SELECT @@IDENTITY") 
lProductID = oRs(0)此代码告诉 SQL Server 不要返回查询的行计数，然后执行 INSERT 语句，并返回刚刚为这个新行 创建的 IDENTITY 值。SET NOCOUNT ON 语句表示返回的记录集有一行和一列，其中包含了这个新的 IDENTITY 值。如果没有 此语句，则会首先返回一个空的记录集（因为 INSERT 语句不返回任何数据），然后会返回第二个记录集，第二个记录集中包含 IDENTITY 值。 这可能有些令人困惑，尤其是因为您从来就没有希望过 INSERT 会返回记录集。之所以会发生此情况，是因为 SQL Server 看到了这个行计数 （即一行受到影响）并将其解释为表示一个记录集。因此，真正的数据被推回到了第二个记录集。当然您可以使用 ADO 中 的 NextRecordset 方法获取此第二个记录集，但如果总能够首先返回该记录集且只返回该记录集，则会更方便，也更有效率。 

此方法虽然有效，但需要在 SQL 语句中额外添加一些代码。获得相同结果的另一方法是在 INSERT 之前使用 SET NOCOUNT ON 语 句，并将 SELECT @@IDENTITY 语句放在表中的 FOR INSERT 触发器中，如下面的代码片段所示。这样，任何进入该表 的 INSERT 语句都将自动返回 IDENTITY 值。 

CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS 
SELECT @@IDENTITY 
GO 
触发器只在 Products 表上发生 INSERT 时启动，所以它总是会在成功 INSERT 之后返回一个 IDENTITY。使用此技术，您可以始终以相同的方式在应用程序中检索 IDENTITY 值。 

内嵌视图与临时表 
某些时候，查询需要将数据与其他一些可能只能通过执行 GROUP BY 然后执行标准查询才能收集的数据进行联接。例如，如果要查询最新五个定单的有关 信息，您首先需要知道是哪些定单。这可以使用返回定单 ID 的 SQL 查询来检索。此数据就会存储在临时表（这是一个常用技术）中，然后 与 Products 表进行联接，以返回这些定单售出的产品数量： 

CREATE TABLE #Temp1 (OrderID INT NOT NULL, _ 
OrderDate DATETIME NOT NULL) 
INSERT INTO #Temp1 (OrderID, OrderDate) 
SELECT    TOP 5 o.OrderID, o.OrderDate 
FROM Orders o ORDER BY o.OrderDate DESC 
SELECT    p.ProductName, SUM(od.Quantity) AS ProductQuantity 
FROM    #Temp1 t 
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID 
INNER JOIN Products p ON od.ProductID = p.ProductID 
GROUP BY p.ProductName 
ORDER BY p.ProductName 
DROP TABLE #Temp1这些 SQL 语句会创建一个临时表，将数据插入该表中，将其他数据与该表进行联接，然后除去该临时表。这会导致此查 询进行大量 I/O 操作，因此，可以重新编写查询，使用内嵌视图取代临时表。内嵌视图只是一个可以联接到 FROM 子句中的查询。所以，您不用 在 tempdb 中的临时表上耗费大量 I/O 和磁盘访问，而可以使用内嵌视图得到同样的结果： 

SELECT p.ProductName, 
SUM(od.Quantity) AS ProductQuantity 
FROM    ( 
SELECT TOP 5 o.OrderID, o.OrderDate 
FROM    Orders o 
ORDER BY o.OrderDate DESC 
) t 
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID 
INNER JOIN Products p ON od.ProductID = p.ProductID 
GROUP BY 
p.ProductName 
ORDER BY 
p.ProductName此查询不仅比前面的查询效率更高，而且长度更短。临时表会消耗大量资源。如果只需要将数据联接到其他查询，则可以试试使用内嵌视图，以节省资源。 

避免 LEFT JOIN 和 NULL 
当然，有很多时候您需要执行 LEFT JOIN 和使用 NULL 值。但是，它们并不适用于所有情况。改变 SQL 查询的构建方式可能会产生将一个 花几分钟运行的报告缩短到只花几秒钟这样的天壤之别的效果。有时，必须在查询中调整数据的形态，使之适应应用程序所要求的显示方式。虽然 TABLE 数 据类型会减少大量占用资源的情况，但在查询中还有许多区域可以进行优化。SQL 的一个有价值的常用功能是 LEFT JOIN。它可以用于检索第一个表 中的所有行、第二个表中所有匹配的行、以及第二个表中与第一个表不匹配的所有行。例如，如果希望返回每个客户及其定单，使用 LEFT JOIN 则可以 显示有定单和没有定单的客户。 

此工具可能会被过度使用。LEFT JOIN 消耗的资源非常之多，因为它们包含与 NULL（不存在）数据匹配的数据。在某些情况下，这是不可避免的， 但是代价可能非常高。LEFT JOIN 比 INNER JOIN 消耗资源更多，所以如果您可以重新编写查询以使得该查询不使用任 何 LEFT JOIN，则会得到非常可观的回报（请参阅图 1 中的图）。 

图 1 查询 

加快使用 LEFT JOIN 的查询速度的一项技术涉及创建一个 TABLE 数据类型，插入第一个表（LEFT JOIN 左侧的表）中的所有行，然 后使用第二个表中的值更新 TABLE 数据类型。此技术是一个两步的过程，但与标准的 LEFT JOIN 相比，可以节省大量时间。一个很好的规则是 尝试各种不同的技术并记录每种技术所需的时间，直到获得用于您的应用程序的执行性能最佳的查询。 

测试查询的速度时，有必要多次运行此查询，然后取一个平均值。因为查询（或存储过程）可能会存储在 SQL Server 内存中的过程缓存中，因此第一 次尝试耗费的时间好像稍长一些，而所有后续尝试耗费的时间都较短。另外，运行您的查询时，可能正在针对相同的表运行其他查询。当其他查询锁定和解锁这些表 时，可能会导致您的查询要排队等待。例如，如果您进行查询时某人正在更新此表中的数据，则在更新提交时您的查询可能需要耗费更长时间来执行。 

避免使用 LEFT JOIN 时速度降低的最简单方法是尽可能多地围绕它们设计数据库。例如，假设某一产品可能具有类别也可能没有类别。如 果 Products 表存储了其类别的 ID，而没有用于某个特定产品的类别，则您可以在字段中存储 NULL 值。然后您必须执 行 LEFT JOIN 来获取所有产品及其类别。您可以创建一个值为“No Category”的类别，从而指定外键关系不允许 NULL 值。通过执 行上述操作，现在您就可以使用 INNER JOIN 检索所有产品及其类别了。虽然这看起来好像是一个带有多余数据的变通方法，但可能是一个很有价值的 技术，因为它可以消除 SQL 批处理语句中消耗资源较多的 LEFT JOIN。在数据库中全部使用此概念可以为您节省大量的处理时间。请记住，对于您 的用户而言，即使几秒钟的时间也非常重要，因为当您有许多用户正在访问同一个联机数据库应用程序时，这几秒钟实际上的意义会非常重大。 

灵活使用笛卡尔乘积 
对于此技巧，我将进行非常详细的介绍，并提倡在某些情况下使用笛卡尔乘积。出于某些原因，笛卡尔乘积 (CROSS JOIN) 遭到了很多谴责，开发人 员通常会被警告根本就不要使用它们。在许多情况下，它们消耗的资源太多，从而无法高效使用。但是像 SQL 中的任何工具一样，如果正确使用，它们也会很 有价值。例如，如果您想运行一个返回每月数据（即使某一特定月份客户没有定单也要返回）的查询，您就可以很方便地使用笛卡尔乘积。 图 2 中 的 SQL 就执行了上述操作。 

虽然这看起来好像没什么神奇的，但是请考虑一下，如果您从客户到定单（这些定单按月份进行分组并对销售额进行小计）进行了标准的 INNER JOIN， 则只会获得客户有定单的月份。因此，对于客户未订购任何产品的月份，您不会获得 0 值。如果您想为每个客户都绘制一个图，以显示每个月和该月销售额，则 可能希望此图包括月销售额为 0 的月份，以便直观标识出这些月份。如果使用 图 2 中的 SQL，数据则会跳过销售额为 0 美元的月份，因为在定单 表中对于零销售额不会包含任何行（假设您只存储发生的事件）。 

图 3 中的代码虽然较长，但是可以达到获取所有销售数据（甚至包括没有销售额的月份）的目标。首先，它会提取去年所有月份的列表，然后将它们放入第一 个 TABLE 数据类型表 (@tblMonths) 中。下一步，此代码会获取在该时间段内有销售额的所有客户公司的名称列表，然后将它们放入另一 个 TABLE 数据类型表 (@tblCus-tomers) 中。这两个表存储了创建结果集所必需的所有基本数据，但实际销售数量除外。 第一个表中 列出了所有月份（12 行），第二个表中列出了这个时间段内有销售额的所有客户（对于我是 81 个）。并非每个客户在过去 12 个月中的每个月都购买 了产品，所以，执行 INNER JOIN 或 LEFT JOIN 不会返回每个月的每个客户。这些操作只会返回购买产品的客户和月份。 

笛卡尔乘积则可以返回所有月份的所有客户。笛卡尔乘积基本上是将第一个表与第二个表相乘，生成一个行集合，其中包含第一个表中的行数与第二个表中的行数相 乘的结果。因此，笛卡尔乘积会向表 @tblFinal 返回 972 行。最后的步骤是使用此日期范围内每个客户的月销售额总计更 新 @tblFinal 表，以及选择最终的行集。 

如果由于笛卡尔乘积占用的资源可能会很多，而不需要真正的笛卡尔乘积，则可以谨慎地使用 CROSS JOIN。例如，如果对产品和类别执行 了 CROSS JOIN，然后使用 WHERE 子句、DISTINCT 或 GROUP BY 来筛选出大多数行，那么使 用 INNER JOIN 会获得同样的结果，而且效率高得多。如果需要为所有的可能性都返回数据（例如在您希望使用每月销售日期填充一个图表时），则笛 卡尔乘积可能会非常有帮助。但是，您不应该将它们用于其他用途，因为在大多数方案中 INNER JOIN 的效率要高得多。 

拾遗补零 
这里介绍其他一些可帮助提高 SQL 查询效率的常用技术。假设您将按区域对所有销售人员进行分组并将他们的销售额进行小计，但是您只想要那些数据库中标 记为处于活动状态的销售人员。您可以按区域对销售人员分组，并使用 HAVING 子句消除那些未处于活动状态的销售人员，也可以在 WHERE 子句中 执行此操作。在 WHERE 子句中执行此操作会减少需要分组的行数，所以比在 HAVING 子句中执行此操作效率更高。HAVING 子句中基于行的 条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。 

另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表，来代替使用 GROUP BY 子句。在这种情况下，使 用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数（SUM、COUNT、MAX 等）的情况下再使用 GROUP BY。另外， 如果您的查询总是自己返回一个唯一的行，则不要使用 DISTINCT 关键字。在这种情况下，DISTINCT 关键字只会增加系统开销。 

您已经看到了，有大量技术都可用于优化查询和实现特定的业务规则，技巧就是进行一些尝试，然后比较它们的性能。最重要的是要测试、测试、再测试。在此专栏 的将来各期内容中，我将继续深入讲述 SQL Server 概念，包括数据库设计、好的索引实践以及 SQL Server 安全范例。 

 

SQL SERVER性能优化综述
http://blog.csdn.net/Haiwer/archive/2008/08/25/2826881.aspx

近期因工作需要，希望比较全面的总结下SQL SERVER数据库性能优化相关的注意事项，在网上搜索了一下,发现很多文章,有的都列出了上百条,但是仔 细看发现，有很多似是而非或者过时(可能对SQL SERVER6.5以前的版本或者ORACLE是适用的)的信息，只好自己根据以前的经验和测试结果进 行总结了。

我始终认为，一个系统的性能的提高，不单单是试运行或者维护阶段的性能调优的任务，也不单单是开发阶段的事情，而是在整个软件生命周期都需要注意，进行有效工作才能达到的。所以我希望按照软件生命周期的不同阶段来总结数据库性能优化相关的注意事项。

一、             分析阶段

一般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力，但是，我们必须注意，性 能是很重要的非功能性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能有各种需求的量化的指标。

另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是OLTP（联机事务处理系统）和OLAP（联机分析处理系统）。

二、             设计阶段

设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程—数据库设计。

在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效率的代码，为整个系统的性能打下良好的基础。

以下是性能要求设计阶段需要注意的：

1、 数据库逻辑设计的规范化

数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：

第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。 

第2规范: 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。

第3规范: 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。

更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。

2、 合理的冗余

完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。

冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。

冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

3、 主键的设计

主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的，这个在下面索引的叙述。

在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。

主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

4、 外键的设计

外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：

外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。

谨慎使用级联删除和级联更新，级联删除和级联更新作为SQL SERVER 2000当年的新功能，在2005作了保留，应该有其可用之处。我这里说的谨 慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可 能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。

5、 字段的设计

字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：

A、数据类型尽量用数字型，数字型的比较比字符型的快很多。

B、 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。

C、 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。

D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。

E、 自增字段要慎用，不利于数据迁移。



6、 数据库物理存储和环境的设计

在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。

这里需要注意文件组的作用，适用文件组可以有效把I/O操作分散到不同的物理硬盘，提高并发能力。

7、 系统设计

整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的OLTP系统，可以选择C/S结构、三层的C/S结构等，不同的系统结构其性能的关键也有所不同。

系统设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。

8、 索引的设计

在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。

关于索引的选择，应改主意：

A、              根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。

B、              根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。

C、              把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。

D、              一个表不要加太多索引，因为索引影响插入和更新的速度。



三、             编码阶段

编码阶段是本文的重点，因为在设计确定的情况下，编码的质量几乎决定了整个系统的质量。

编码阶段首先是需要所有程序员有性能意识，也就是在实现功能同时有考虑性能的思想，数据库是能进行集合运算的工具，我们应该尽量的利用这个工具，所谓集合 运算实际是批量运算，就是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。关于思想和意识，很难说得很清楚，需要在编程过程中 来体会。

下面罗列一些编程阶段需要注意的事项：

1、 只返回需要的数据

返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：

A、横向来看，不要写SELECT *的语句，而是选择你需要的字段。

B、 纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。

C、 注意SELECT INTO后的WHERE子句，因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。

D、对于聚合查询，可以用HAVING子句进一步限定返回的行。

2、 尽量少做重复的工作

这一点和上一点的目的是一样的，就是尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：

A、              控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。

B、              减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。

C、              杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。

D、              合并对同一表同一条件的多次UPDATE，比如

UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’ 

UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’ 


这两个语句应该合并成以下一个语句

UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’  
WHERE EMP_ID=’ VPA30890F’
E、               UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。

F、               不要写一些没有意义的查询，比如

SELECT * FROM EMPLOYEE WHERE 1=2

3、 注意事务和锁

事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：

A、事务操作过程要尽量小，能拆分的事务要拆分开来。

B、 事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。

C、 事务操作过程要按同一顺序访问对象。

D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。

E、 尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER优化器选择的锁类 型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。

F、 查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。

4、 注意临时表和表变量的用法

在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：

A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。

B、 如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。

C、 如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。

D、其他情况下，应该控制临时表和表变量的使用。

E、 关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。

F、 关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，我们做过测试，一般情况 下，SELECT INTO会比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO会锁定TEMPDB的系 统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使 用CREATE TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT INTO。

G、  注意排序规则，用CREATE TABLE建立的临时表，如果不指定字段的排序规则，会选择TEMPDB的默认排序规则，而不是当前数据库的排序 规则。如果当前数据库的排序规则和TEMPDB的排序规则不同，连接的时候就会出现排序规则的冲突错误。一般可以在CREATE TABLE建立临时表时 指定字段的排序规则为DATABASE_DEFAULT来避免上述问题。

5、 子查询的用法

子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。

子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。

如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。

关于相关子查询，应该注意：

A、NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。比如：



SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID NOT IN 
(SELECT PUB_ID 
FROM TITLES 
WHERE TYPE = 'BUSINESS') 
可以改写成：

SELECT A.PUB_NAME 
FROM PUBLISHERS A LEFT JOIN TITLES B 
ON        B.TYPE = 'BUSINESS' AND 
A.PUB_ID=B. PUB_ID 
WHERE B.PUB_ID IS NULL


SELECT TITLE 
FROM TITLES 
WHERE NOT EXISTS 
(SELECT TITLE_ID 
FROM SALES 
WHERE TITLE_ID = TITLES.TITLE_ID)
可以改写成：

SELECT TITLE 
FROM TITLES LEFT JOIN SALES 
ON SALES.TITLE_ID = TITLES.TITLE_ID 
WHERE SALES.TITLE_ID IS NULL
B、 如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。比如：

SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID IN 
(SELECT PUB_ID 
FROM TITLES 
WHERE TYPE = 'BUSINESS')
可以改写成：

SELECT DISTINCT A.PUB_NAME 
FROM PUBLISHERS A INNER JOIN TITLES B 
ON        B.TYPE = 'BUSINESS' AND 
A.PUB_ID=B. PUB_ID

C、 IN的相关子查询用EXISTS代替，比如

SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID IN 
(SELECT PUB_ID 
FROM TITLES 
WHERE TYPE = 'BUSINESS')
可以用下面语句代替：

SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE EXISTS 
(SELECT 1 
FROM TITLES 
WHERE TYPE = 'BUSINESS' AND 
PUB_ID= PUBLISHERS.PUB_ID)
D、不要用COUNT(*)的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS，比如有人写这样的语句：

SELECT JOB_DESC FROM JOBS 
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0
应该改成：

SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE  
ON EMPLOYEE.JOB_ID=JOBS.JOB_ID 
WHERE EMPLOYEE.EMP_ID IS NULL


SELECT JOB_DESC FROM JOBS 
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0
应该改成：

SELECT JOB_DESC FROM JOBS 
WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)
6、 慎用游标

数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功能搬到客户端。

游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。

另外，我们可以用SQL SERVER的一些特性来代替游标，达到提高速度的目的。

A、字符串连接的例子

这是论坛经常有的例子，就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把JOB_ID=10的EMPLOYEE的FNAME连接在一起，用逗号连接，可能最容易想到的是用游标：

DECLARE @NAME VARCHAR(20) 
DECLARE @NAME VARCHAR(1000) 
DECLARE NAME_CURSOR CURSOR FOR 
SELECT FNAME FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID 
OPEN NAME_CURSOR 
FETCH NEXT FROM RNAME_CURSOR INTO @NAME 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @NAMES = ISNULL(@NAMES+’,’,’’)+@NAME 
FETCH NEXT FROM NAME_CURSOR  INTO @NAME  
END 
CLOSE NAME_CURSOR 
DEALLOCATE NAME_CURSOR
可以如下修改，功能相同：

DECLARE @NAME VARCHAR(1000) 
SELECT @NAMES = ISNULL(@NAMES+’,’,’’)+FNAME 
FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID
B、 用CASE WHEN 实现转换的例子

很多使用游标的原因是因为有些处理需要根据记录的各种情况需要作不同的处理，实际上这种情况，我们可以用CASE WHEN语句进行必要的判断处理，而且CASE WHEN是可以嵌套的。比如:

表结构:

CREATE TABLE 料件表( 
料号           VARCHAR(30), 
名称           VARCHAR(100), 
主单位         VARCHAR(20), 
单位1         VARCHAR(20), 
单位1参数      NUMERIC(18,4), 
单位2         VARCHAR(20), 
单位2参数      NUMERIC(18,4) 
) 

GO 

CREATE TABLE 入库表( 
时间               DATETIME, 
料号               VARCHAR(30), 
单位               INT, 
入库数量           NUMERIC(18,4), 
损坏数量           NUMERIC(18,4) 
) 

GO
其中，单位字段可以是0，1，2，分别代表主单位、单位1、单位2，很多计算需要统一单位，统一单位可以用游标实现：

DECLARE @料号     VARCHAR(30), 
@单位   INT, 
@参数      NUMERIC(18,4), 

DECLARE CUR CURSOR FOR 
SELECT 料号,单位 FROM 入库表 WHERE 单位 <>0 
OPEN CUR 
FETCH NEXT FROM CUR INTO @料号,@单位 
WHILE @@FETCH_STATUS<>-1 
BEGIN 
IF @单位=1 
BEGIN 
SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号) 
UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR 
END 
IF @单位=2 
BEGIN 
SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号) 
UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR 
END 
FETCH NEXT FROM CUR INTO @料号,@单位 
END 
CLOSE CUR 
DEALLOCATE CUR
可以改写成：

UPDATE A SET  
数量=CASE A.单位 WHEN 1 THEN      A.数量*B. 单位1参数 
WHEN 2 THEN         A.数量*B. 单位2参数 
ELSE A.数量 
END,                   
损坏数量= CASE A.单位 WHEN 1 THEN    A. 损坏数量*B. 单位1参数 
WHEN 2 THEN         A. 损坏数量*B. 单位2参数 
ELSE A. 损坏数量 
END, 
单位=1  
FROM入库表 A, 料件表 B 
WHERE    A.单位<>1      AND 
A.料号=B.料号
C、 变量参与的UPDATE语句的例子

SQL ERVER的语句比较灵活，变量参与的UPDATE语句可以实现一些游标一样的功能，比如：

在

SELECT A,B,C,CAST(NULL AS INT) AS 序号 
INTO #T 
FROM 表 
ORDER BY A ,NEWID()
产生临时表后，已经按照A字段排序，但是在A相同的情况下是乱序的，这时如果需要更改序号字段为按照A字段分组的记录序号，就只有游标和变量参与的UPDATE语句可以实现了，这个变量参与的UPDATE语句如下：

DECLARE @A INT 
DECLARE @序号 INT 
UPDATE #T SET 
@序号=CASE WHEN A=@A THEN @序号+1 ELSE 1 END, 
@A=A, 
序号=@序号
D、如果必须使用游标，注意选择游标的类型，如果只是循环取数据，那就应该用只进游标（选项FAST_FORWARD），一般只需要静态游标（选项STATIC）。

E、 注意动态游标的不确定性，动态游标查询的记录集数据如果被修改，会自动刷新游标，这样使得动态游标有了不确定性，因为在多用户环境下，如果其他进程或者本身更改了纪录，就可能刷新游标的记录集。

7、 尽量使用索引

建立索引后，并不是每个查询都会使用索引，在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引，索引的选择和使 用方法是SQLSERVER的优化器自动作的选择，而它选择的根据是查询语句的条件以及相关表的统计信息，这就要求我们在写SQL语句的时候尽量使得优化 器可以使用索引。

为了使得优化器能高效使用索引，写语句的时候应该注意：

A、不要对索引字段进行运算，而要想办法做变换，比如

SELECT ID FROM T WHERE NUM/2=100

应改为:

SELECT ID FROM T WHERE NUM=100*2



SELECT ID FROM T WHERE NUM/2=NUM1

如果NUM有索引应改为:

SELECT ID FROM T WHERE NUM=NUM1*2

如果NUM1有索引则不应该改。



发现过这样的语句：

SELECT 年,月,金额 FROM 结余表 
WHERE 100*年+月=2007*100+10
应该改为：

SELECT 年,月,金额 FROM 结余表 
WHERE 年=2007 AND 
月=10


B、 不要对索引字段进行格式转换

日期字段的例子：

WHERE CONVERT(VARCHAR(10), 日期字段,120)=’2008-08-15’

应该改为

WHERE日期字段〉=’2008-08-15’         AND   日期字段<’2008-08-16’



ISNULL转换的例子：

WHERE ISNULL(字段,’’)<>’’应改为:WHERE字段<>’’

WHERE ISNULL(字段,’’)=’’不应修改

WHERE ISNULL(字段,’F’) =’T’应改为: WHERE字段=’T’

WHERE ISNULL(字段,’F’)<>’T’不应修改

C、 不要对索引字段使用函数

WHERE LEFT(NAME, 3)='ABC' 或者WHERE SUBSTRING(NAME,1, 3)='ABC'

应改为: 

WHERE NAME LIKE 'ABC%'



日期查询的例子：

WHERE DATEDIFF(DAY, 日期,'2005-11-30')=0应改为:WHERE 日期 >='2005-11-30' AND 日期 <'2005-12-1‘

WHERE DATEDIFF(DAY, 日期,'2005-11-30')>0应改为:WHERE 日期 <'2005-11-30‘

WHERE DATEDIFF(DAY, 日期,'2005-11-30')>=0应改为:WHERE 日期 <'2005-12-01‘

WHERE DATEDIFF(DAY, 日期,'2005-11-30')<0应改为:WHERE 日期>='2005-12-01‘

WHERE DATEDIFF(DAY, 日期,'2005-11-30')<=0应改为:WHERE 日期>='2005-11-30‘

D、不要对索引字段进行多字段连接

比如：

WHERE FAME+ ’.’+LNAME=‘HAIWEI.YANG’ 

应改为: 

WHERE FNAME=‘HAIWEI’ AND LNAME=‘YANG’

8、 注意连接条件的写法

多表连接的连接条件对索引的选择有着重要的意义，所以我们在写连接条件条件的时候需要特别的注意。

A、多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。

B、 连接条件尽量使用聚集索引

C、 注意ON部分条件和WHERE部分条件的区别



9、 其他需要注意的地方

经验表明，问题发现的越早解决的成本越低，很多性能问题可以在编码阶段就发现，为了提早发现性能问题，需要注意：

A、程序员注意、关心各表的数据量。

B、 编码过程和单元测试过程尽量用数据量较大的数据库测试，最好能用实际数据测试。

C、 每个SQL语句尽量简单

D、不要频繁更新有触发器的表的数据

E、 注意数据库函数的限制以及其性能

10、              学会分辩SQL语句的优劣

自己分辨SQL语句的优劣非常重要，只有自己能分辨优劣才能写出高效的语句。

A、              查看SQL语句的执行计划，可以在查询分析其使用CTRL+L图形化的显示执行计划，一般应该注意百分比最大的几个图形 的属性，把鼠标移动到其上面会显示这个图形的属性，需要注意预计成本的数据，也要注意其标题，一般都是CLUSTERED INDEX SEEK 、 INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN等，其中出现SCAN说明语句有优 化的余地。也可以用语句

SET SHOWPLAN_ALL ON

要执行的语句

SET SHOWPLAN_ALL OFF

查看执行计划的文本详细信息。

B、               用事件探查器跟踪系统的运行，可疑跟踪到执行的语句，以及所用的时间，CPU用量以及I/O数据，从而分析语句的效率。

C、               可以用WINDOWS的系统性能检测器，关注CPU、I/O参数

四、             测试、试运行、维护阶段

测试的主要任务是发现并修改系统的问题，其中性能问题也是一个重要的方面。重点应该放在发现有性能问题的地方，并进行必要的优化。主要进行语句优化、索引优化等。

试运行和维护阶段是在实际的环境下运行系统，发现的问题范围更广，可能涉及操作系统、网络以及多用户并发环境出现的问题，其优化也扩展到操作系统、网络以及数据库物理存储的优化。

这个阶段的优花方法在这里不再展开，只说明下索引维护的方法：

A、              可以用DBCC DBREINDEX语句或者SQL SERVER维护计划设定定时进行索引重建，索引重建的目的是提高索引的效能。

B、               可以用语句UPDATE STATISTICS或者SQL SERVER维护计划设定定时进行索引统计信息的更新，其目的是使得统计信息更能反映实际情况，从而使得优化器选择更合适的索引。

C、               可以用DBCC CHECKDB或者DBCC CHECKTABLE语句检查数据库表和索引是否有问题，这两个语句也能修复一般的问题。

D、               

五、             网上资料中一些说法的个人不同意见

1、 “应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

SELECT ID FROM T WHERE NUM IS NULL

可以在NUM上设置默认值0，确保表中NUM列没有NULL值，然后这样查询：

SELECT ID FROM T WHERE NUM=0”

个人意见：经过测试，IS NULL也是可以用INDEX SEEK查找的，0和NULL是不同概念的，以上说法的两个查询的意义和记录数是不同的。

2、 “应尽量避免在 WHERE 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。”

个人意见：经过测试，<>也是可以用INDEX SEEK查找的。

3、 “应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

SELECT ID FROM T WHERE NUM=10 OR NUM=20

可以这样查询：

SELECT ID FROM T WHERE NUM=10

UNION ALL

SELECT ID FROM T WHERE NUM=20”

个人意见：主要对全表扫描的说法不赞同。

4、 “IN 和 NOT IN 也要慎用，否则会导致全表扫描，如：

SELECT ID FROM T WHERE NUM IN(1,2,3)

对于连续的数值，能用 BETWEEN 就不要用 IN 了：

SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3”

个人意见：主要对全表扫描的说法不赞同。

5、 “如果在 WHERE 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行 时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

SELECT ID FROM T WHERE NUM=@NUM

可以改为强制查询使用索引：

SELECT ID FROM T WITH(INDEX(索引名)) WHERE NUM=@NUM”

个人意见：关于局部变量的解释比较奇怪，使用参数如果会影响性能，那存储过程就该校除了，我坚持我上面对于强制索引的看法。

6、 “尽可能的使用 VARCHAR/NVARCHAR 代替 CHAR/NCHAR ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。”

个人意见：“在一个相对较小的字段内搜索效率显然要高些”显然是对的，但是字段的长短似乎不是由变不变长决定，而是业务本身决定。在 SQLSERVER6.5或者之前版本，不定长字符串字段的比较速度比定长的字符串字段的比较速度慢很多，所以对于那些版本，我们都是推荐使用定长字段存 储一些关键字段。而在2000版本，修改了不定长字符串字段的比较方法，与定长字段的比较速度差别不大了，这样为了方便，我们大量使用不定长字段。

7、 关于连接表的顺序或者条件的顺序的说法，经过测试，在SQL SERVER，这些顺序都是不影响性能的，这些说法可能是对ORACLE有效。

建议看看胡百敬的书：　《性能调校》