SQL Server 查询优化

查询优化在关系数据库中有着多么重要的地位，相信每个读者都有深刻的认识吧。 SQL Server 提供查询优化器，充分了解和利用这一优化器，有助于提高数据库应用系统的整体性能。这里从索引和查询语句两方面介绍一些常用的 SQL Server 性能优化的方法和途径。

1. 在数据库设计时，应考虑下列因素对查询效率的影响

① 主键列的长度应尽量小，若能使用 SmallInt 就不使用 Int 类型。如学生表，若能用学号当主键，就不要用身份证号码当主键。又如 Identity 列，如果数据表记录不超过 3 万条，就应该使用 SmallInt 而不必使用 Int 。
② 对于长度固定的字符型字段（如身份证号码），不应使用变长得 varchar 或nvarchar ，而应该使用定长的 char 或 nchar 类型。而对于长度不固定的字符型列（如地址），则可以使用varchar 或 nvarchar 类型。
③ 在定义和设计表结构时，若某列的值可有可无，最好给它一个默认值，并设成 NOT NULL 。因为 SQL Server 在存放和查询包含 NULL 的数据表时，会花费额外的运算时间。另外，还要尽量避免在 WHERE 子句中对列进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描。例如：
SELECT * FROM Products Where UnitPrice IS NULL
可以在 UnitPrice 上设置默认值 0 ，确保表中的 UnitPrice 列没有 NULL 值，然后进行下列查询：
SELECT * FROM Products WHERE UnitPrice=0
④ 若一个数据表中的列过多，可以考虑将其垂直切割成两个以上的数据表，并用同名的主键连接起来，这样可以避免在存取数据时加载过多的数据，或者在修改数据时造成互相锁定或锁定过久的局面。

2. 适当地建立索引

① 对外键列应尽量建立索引，即使很少被连接也应该建立索引。
② 对常被查询或排序的列应该建立索引，例如，在常被当作 WHERE 子句条件查询的列上建立索引，可以大大提高检索的效率。
③ 对使用率低的列不要建立索引，以免浪费硬盘空间。对于内容重复性高的列（如性别），一般也不宜创建索引；反之，对于重复性低（如姓名）的列，创建索引可以明显地提高数据检索的效率。
④ 一个数据表中不宜对过多列建立索引，否则反而会影响到 INSERT 、 UPDATE 、 DELETE的性能，尤其是以 OLTP （联机事务处理）为主的数据库应用系统，创建太多的索引反而会影响整个系统的性能。
⑤ 若数据表存放的数据很少，就不必刻意建立索引，否则数据库按照存放索引的树状结构去搜寻索引中的数据，可能反而比扫描整个数据表还慢。
⑥ 若查询时符合条件的数据很多，则通过非聚集索引搜寻的性能反而可能不如对整个数据表进行逐笔扫描。

3. 在 WHERE 子句中，索引的使用技巧

①应尽量避免在 WHERE 子句中对列进行表达式操作，这将导致全表扫描。例如：
SELECT * FROM Products Where UnitPrice/2=5
应改为
SELECT * FROM Products Where UnitPrice=5*2
②在 WHERE 中使用运算符 != 、 <> 、 !> 、 !< 、NOT 、 NOT EXISTS 、 NOT IN 、 NOT LIKE ，常会使查询优化器可能无法有效地使用索引，因此最好使用其它运算符和语法进行改写。
③ 通常，使用 LIKE 和 % 进行模糊查询时，即使在某个列建立索引，但以常量字符开头才会使用到索引，若以通配符 (%) 开头则不会使用索引。例如，虽然 CustomerID 列建有索引，但下列查询语句中 LIKE ‘%D’ 不会使用到索引。
SELECT * FROM Orders WHERE CustomerID LIKE ‘D%’ --能使用索引
SELECT * FROM Orders WHERE CustomerID LIKE ‘%D’ --不能使用索引
④在 AND 运算中，只要有一个条件能使用索引，就可以大幅提升查询速度。但在 OR 运算中，则需要所有的条件都能使用索引时，才能提升查询速度。例如：
SELECT * FROM Orders WHERE CustomerID=‘BERGS’ AND Freight=615.60
这条语句可以使用 CustomerID 列上建立的索引（假定实验所说条件已具备，下同）。但是下面这条语句就无法使用CustomerID 列上的索引。
SELECT * FROM Orders WHERE CustomerID=‘BERGS’ OR Freight=615.60
对于上述的 OR 运算的查询语句，我们可用 UNION 组合查询适当地改善性能。例如，
上述查询语句可以改写为如下形式。
SELECT * FROM Orders WHERE CustomerID='BERGS ’
UNION ALL
SELECT * FROM Orders WHERE Freight=615.60
这时第一个查询能使用索引，但第二个查询仍只能扫描整个表。但即使这样，也可以明显提高查询性能。
⑤在大容量的数据表中使用函数对性能影响是很大的。如果有 10 万条记录，那么在查询时就需要调用函数 10 万次，这是必须避免的。在系统开发初期往往感觉不出差异，但当系统运行一段时间数据持续累积后，这些语法细节所造成的性能问题就会逐步表现出来。例如：
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2008 and MONTH(OrderDate)=7
应改为
SELECT * FROM Orders WHERE OrderDate between ‘2008-7-1’ and ‘2008-7-31’
同样，
SELECT * FROM Orders WHERE LEFT(CustomerID,1) = ‘D’
应改为
SELECT * FROM Orders WHERE CustomerID LIKE ‘D%’

4. 谨慎地使用子查询

对于子查询，如果一个查询能用 JOIN 完成，那么最好使用 JOIN 。除了 JOIN 的语法比较容易理解之外，在大多数情况下， JOIN 的性能比子查询要好。
①例如，将 Orders 表中数据全部检索出来，并自动添加一个编号。如果使用排名函数 ROW_NUMBER ，查询执行效率很高，只需要几个毫秒。
SELECT OrderID, ROW_NUMBER() OVER(ORDER BY OrderID) AS ’ RowNo’ FROM Orders
但是，如果使用子查询或辅以 AS 关键词的衍生表，那么可以用下列语句实现：
SELECT OrderID, (SELECT COUNT(*) FROM Orders as a WHERE a.OrderID <= b.OrderID) as ‘RowNo’ FROM Orders as b ORDER BY RowNo
这是一个相关子查询，外部查询的每一笔订单，都要等内部查询扫描整个数据表并作比对和计数，因此 1280 笔订单每一笔都要重复扫描整个数据表 1280 次，所耗用的时间将大幅提高。对于上万条记录的数据表，使用 ROW_NUMBER 函数查询时间不到 1 秒钟，而使用相关子查询，则需要 1 分钟左右的时间。
②IN 和 NOT IN 也要慎用，否则会导致全表扫描。例如：
SELECT * From Products WHERE ProductID IN(1,2,3)
可以用 BETWEEN…AND 替换：
SELECT * FROM Products WHERE ProductID BETWEEN 1 AND 3
③通常情况下， EXISTS 的查询效率要高于 IN 。例如：
SELECT * FROM Products WHERE SupplierID IN
(SELECT SupplierID FROM Suppliers WHERE Country=‘USA’)
可以用下面的语句替换：
SELECT * FROM Products as a WHERE EXISTS
(SELECT 1 FROM Suppliers b WHERE b.SupplierID=a.SupplierID and Country=‘USA’)

5. 多表连接的性能分析

SQL Server 在执行多表连接时可能会非常耗费资源，因此在没有必要时尽量不要使用多表连接。因为连接的表越多，性能下降越严重。为此，在很多情况下，我们需要在数据冗余、数据一致性和与查询性能之间找到一个适当的平衡。也就是说，为了提高数据查询的速度，可以适当地浪费数据库中数据存储空间，在多个表中重复存储相同的数据，以避免在数据检索时进行多表连接等操作。

6. 谨慎地使用游标

尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过 1 万行，那么就应该考虑改写。在使用基于游标的方法之前，应先寻找基于结果集的解决方案来解决问题，因为结果集的方法通常更会有效。
与使用结果集的方法相比，游标并不是不可使用。对小型数据集使用 FAST_FORWARD游标通常要优于其他逐行处理方法，尤其是在多表连接时，更是如此。在开发数据库应用时，可以对基于游标的方法和基于结果集的方法进行比较，选择查询性能较高的那一种。

7. 其他查询技巧

① 对于列很多的表，应尽量避免使用星号（*）返回所有的列，即尽量避免使用 SELECT * FROM tablename ，而应该指定具体的列名，以免浪费服务器的 I/O 资源。
② DISTINCT 、 ORDER BY 子句会让数据库进行额外的运算。此外使用组合查询时，如
果没有必要剔除重复的数据，使用 UNION ALL 比 UNION 性能更好，因为后者会加入类似DISTINCT 的算法。
③尽可能用存储过程取代应用程序直接存取数据表。存储过程除了经过事先编译、性能较好以外，也可节省 SQL 语句传递的网络频宽，方便业务逻辑的重复使用。
④尽可能在数据来源层过滤数据。使用 SELECT 语法时，在数据返回至前端之前设定WHERE 等过滤条件。虽然在一些数据库应用开发工具（如 C++ 、 DELPHI 、 ASP.NET 、 JSP等）也可以对数据进行筛选和排序，但会额外消耗数据库的系统资源、 Web 服务器的内存以及网络频宽。因此，最好的办法是在数据库和数据源的层面上，就先使用 SQL 条件或存储过程筛选出所需要的数据。

8. 热烈欢迎各位大佬补充：