T-SQL查询优化乱弹

 T-SQL查询优化乱弹收藏

以下是我整理的一些在SQL Server上实现高效查询的技巧和提示。经验之谈，不妥之处请大家指正。

1. 关于索引

谈数据库查询优化必然要讲索引。索引是个大话题。想要尽可能提高数据库系统的性能，设计良好的索引，必然要深入了解索引的结构，这个可以专门开一个话题来讨论（在《SQL Server 2005技术内幕：存储引擎》一书中这个话题是用专门的一章来讲的）。这次我们主要谈查询技巧，对索引只要有一个基本的理解即可。

举个例子来理解索引：
以一本汉语字典为例，字典好比一个表，表中的每个字及其拼音、解释可以理解为字段，每个字即一行记录；字典中的字是按照拼音和笔划顺序排列的，因此拼音和笔划即字典表的聚集索引键（复合键）。字典前面的目录是非聚集索引，包括拼音索引、部首索引、笔划索引、四角号码索引等等。
由此：
一、聚集索引只能有一个。因为字典表中的字（记录）不可能既按拼音和笔划排序又按部首排序。但聚集索引并非只能建在拼音和笔划上，比如四号号码字典或是古时的辞典。
二、非聚集索引可以有多个。非聚集索引查到的只是一个指向记录存放位置的页码（这只是举例，SQLServer的具体实现略有不同）。
三、在已经被索引覆盖的字段上建索引是一种不必要的浪费。比如拼音已经被聚集索引覆盖了，前面目录中的非聚集拼音索引则是多余。（这一点后面详述）
（以下两条涉及索引的结构和关系模型的概念，可以不深究）
四、如果索引键不是唯一的，则索引结构中会引入一个附加属性来保证唯一排序。比如在部首索引中，相同部首之下的字会按笔划排序。
五、主键与聚集索引键并不等价。前者是一个数据模型的抽象概念，后者是一个数据库的物理结构。字典表的主键是字和拼音的组合键，它唯一地决定一个确定读音的汉字，这也表示字典表中的一条记录。拼音和笔划的组合聚集索引键只是字典表的物理存储方式。很多时候聚集索引键与主键是相同的，但这并不必然。

理解了索引的结构，我们就很容易明白索引对于高效查询的重要性。一个没有索引的表在物理上是一个堆（heap），即一堆无序的数据，比如一个小学生的练字本。对于小表，堆扫描也足够快，比如在小学生的练字本上找一个字，我们只能将整个表遍历一遍，这不困难；但不能想象每次查字典都要将整本字典遍历一遍。所以，在大表上通常要根据查询需要建合适的索引。
如果要查一个知道读音的字，这时可以使用聚集索引：比如“牛”字的拼音是niu2（2表示二声），我可以直接翻字典的中部（N在字母表的中部），然后根据翻到的字母去逼近目标值niu2，如果niu2这个拼音的字有多个，我可以再根据聚集索引键的第二个字段笔划顺序去找“牛”字，当然这时候通常已经定位到了一个数据页（真的是一页），扫描也是很快的，以至我们通常不会意识到使用聚集索引键的第二个字段。这便是Clustered Index Seek。
然而很多人根据读音查一个字时通常习惯于先在拼音目录找到这个字的页码再去翻相应的页，因为小学语文教字典使用方法时是这么教的。这种方法和根据部首查一个字一样，是No-clustered Index Seek + Table Lookup。因为拼音目录的页数少，查找起来会比聚集索引查找快一些，但这种方法后面要多加一步表查找，总耗时可能比较大。这样看来，学校教的这种查字典方法就好比加了一个强制使用拼音索引的查询提示（hint），让数据库引擎放弃聚集索引不用而优先使用拼音字段上的非聚集索引。

在实际的数据库中，索引发挥作用的地方并不仅限于查找。事实上，WHERE/JOIN/GROUP BY/PARTITION BY/ORDER BY/DISTINCT这些语句都会受益于索引。WHERE略过不说；JOIN基本上可以看作是一种特殊的查找；根据索引字段进行GROUP BY/PARTITION BY/ORDER BY一般效率比较高，尤其是单表查询时根据聚集索引ORDER BY；用DISTINCT去重也是一种变相查找（找重复值），因而索引也可以发挥作用。


2. SARG

在查询时为了使用索引，查询条件也需要满足一些条件，官方定义为SARG（Search-ARGument，查询参数）。SARG 运算符包括 =、>、<、>=、<=、IN、BETWEEN，有时还包括 LIKE（在进行前缀匹配时，如 LIKE 'John%'）。SARG 可以包括由 AND 联接的多个条件。

举几个SARG查询条件的例子：
拼音 = 'niu2'：前面介绍了，这是Clustered Index Seek。
拼音 > 'niu2'：方法类似，找到niu2这个值，然后把其后的字全部列出，因为这个查询的选择性小（即要查的目标数据在整个表中比例大），其实际查询方法可能是Clustered Index Scan。
<、>=、<=同理。
IN好比是要查一组值，拼音 IN ('niu2', 'fei1', 'long2')的查询方法跟 = 差不多。而且，如果IN后面是一个子查询，数据库的查询计划可能会类似于JOIN，还可能使用子查询的表的索引。
col BETWEEN a AND b逻辑上等价于col >= a AND col <= b，不必多说。
拼音 LIKE 'ni%'的查询也不难，因为索引是前序排列的。然而如果是 拼音 LIKE '%iu2' 就没那么简单了（这种情况SQL Server 2005另有妙计）。

再举几个非SARG的反面例子：
拼音 <> 'niu2'
拼音 NOT IN (niu2, fei1, long2)
这两种情况，索引就不太容易发挥作用了。
LEN(拼音) >= 3
转繁体(汉字) = '龍'
汉字 + '寺' = '特' --（假定 + 操作是把两个汉字左右结合组成一个字）
这样的查询更恐怖。查询引擎只能老老实实地根据每条记录计算等式左边的值再跟右边的值配置，没有办法使用索引。除非查询引擎能够明白 转繁体() 的逆函数是 转简体()、+ 运算符的逆操作是 -。在查询引擎达到这样的智能之前，我们不如换一种写法：
拼音 LIKE '___%' --（刚刚说了对于LIKE运算符SQL Server 2005另有妙计，通常LIKE是优于在表字段上使用函数的）
汉字 = 转简体('龍') 或 汉字 = '龙'
汉字 = '特' - '寺' 或 汉字 = '牛'

关于AND和OR的差别，想象这两个查询条件：
拼音 = 'peng2' AND 部首 = '鸟'：可以先用索引找到peng2再查找部首是鸟字旁，这样就知道结果是“鹏”而不是“棚”。因为AND两边是交集的关系。
拼音 = 'peng2' OR 部首 = '鸟'：这就比较麻烦，查询引擎可能会先通过拼音索引找peng2再根据部首索引找鸟字旁然后把两部分并起来，也可能干脆表扫描。所以OR不是SARG。

综上，我们应该在查询条件中尽量使用SARG以便使用索引：
一、避免在表字段上使用函数或运算符。尽可能找到其反向运算在确定的条件上操作。
二、避免使用<>和NOT。当然这个很多时候无法避免，只能说尽量避免。
三、将OR表达式转化为等价的AND表达式或UNION操作。
第三条情况不多见，举个例子：
(1) SELECT * FROM Employees WHERE HireDate >= '20090101' OR (HireDate >= '20080101' AND Salary < 10000.00)
可以替换为：
(2) SELECT * FROM Employees WHERE HireDate >= '20080101' AND (HireDate >= '20090101' OR Salary < 10000.00)
或：
(3)
SELECT * FROM Employees WHERE HireDate >= '20090101'
UNION
SELECT * FROM Employees WHERE HireDate >= '20080101' AND Salary < 10000.00
其中，(2)可以先用HireDate上的索引进行过滤，(3)可以在两个查询上分别使用索引（但UNION操作的代价也比较大）。有些时候，这种改写可以提高效率。

以上讨论索引时举了汉语字典的例子。给定一个查询条件，数据库查询引擎查找表的过程，可以近似类比为一个人查字典的过程。分析一个查询条件，可以想象一下自己查字典，这样便可以对能否和如何使用索引有一个直观的理解。
（严格地说，一个人查字典的方式是多分查找，而不是数据库索引查询采取的B-树查找。但两种查找方式很类似，可以近似类比。）


3. 用面向集合的思维方式使用SQL

现在比较流行说编程思想。一般的编程语言都是采用面向过程的思维方式；当然特殊的还有面向对象、面向事件、面向服务等等，不过淘去其中概念性的说法，本质上依然是过程化的。因为程序作为一种搞定事情（Problem-Solving）的工具，自然是需要一步一步处理的，这跟人类思维的过程是一致的。编程即是思维的扩展。

在数据库（无非一种特殊的应用程序）领域也不例外。各种实用的数据库系统都对SQL进行了过程化的扩展。这是现实应用开发的需要。

但SQL本身处理的是数据，这是数据库不同于其它应用程序的地方。程序的整体流程是过程化的，但在局部，处理数据需要一种面向集合的思维方式。很多从应用程序开发转到SQL编程的程序员往往没有进行这种思维转变，因而常常写出散发着“坏气味”（Bad Smell）的SQL代码。

比如某公司现在要为员工加薪，方案是薪资在10000.00以下的加10%，10000.00及以上的加5%。过程式思维的代码可能是这样：
UPDATE Employees SET Salary = Salary * 1.10 WHERE Salary < 10000.00
UPDATE Employees SET Salary = Salary * 1.05 WHERE Salary >= 10000.00
（还好没有用游标或写循环-_-|）
细想一下，这会有问题：那些薪资在10000.00以下的但是加5%之后便在10000.00以上的员工将会在加5%之后再加10%。因此正确的写法是把两个语句换一下位置。
然而SQL采用面向集合的思维自然会有所努力，它提供了CASE WHEN语句（准确说是表达式）。集合式思维的代码则是这样：
UPDATE Employees SET Salary = Salary * CASE WHEN Salary < 10000.00 THEN 1.10 ELSE 1.05 END
（可以看到CASE WHEN ... END这一整体就是一个表达式，它实现的是类似于IF ... ELSE ...语句的作用。）

有些时候需要实现一些复杂的逻辑。比如在HRMS（人力资源管理系统）中需要根据员工的入职时间、工作地点、职位、绩效、市场平均薪资等信息综合计算员工的薪资调整金额，甚至可能还要结合企业设置的计划或策略；或者是在ERP中需要对客户的姓名、住址、邮箱、电话号码等敏感信息进行一个复杂的编码加密过程；等等。这种情况下的思维逻辑可能是这样：
Foreach row In table:
blah...
blah...
blah...
（一段很长的处理）
很多时候程序员会直接把这段伪码翻译成这样的SQL：
WHILE ... --用游标循环表中每一行
BEGIN
（一段很长的处理，可能包含一些IF ... ELSE ...或者SELECT ... FROM ...甚至是另一个WHILE！！）
END
处理循环并非SQL所擅长的操作。可以想象这样代码的执行效率。

用批量操作代替循环操作，不光意味着更好的性能，而且逻辑更为直观（关系模型中的数据本身即是以集合形式存在的），有时甚至能避免逻辑上的错误。

因此，尽量把一条一条记录循环处理的逻辑写成批量处理。如果逻辑比较复杂，不妨分成多个步骤的操作，或是封装在自定义函数里。除非万不得已，否则一定不要使用循环。

有本书中讲，SQL代码中出现一次IF便要减一些分（比如-1），出现一次WHILE便要减很多分（比如-10或-50，不夸张）。可以尝试用这样的方法为自己的存储过程打分。


4. 避免在SELECT子句中使用子查询或包含查询的标量UDF

想象一个销售信息系统，很时候我们都需要根据客户编号UserID翻译出客户姓名UserName，类似如下查看订单的情况：
SELECT
  o.OrderID, ... --关于订单的信息
  CustomerName = (SELECT u.UserName FROM Users u WHERE u.UserID = o.UserID)
FROM Orders o
WHERE some_condition
或许我们还会把上面那段子查询封装在自定义函数中，从而只需：
  CustomerName = dbo.UserName(o.UserID)
这种情况下，查询引擎会采用嵌套循环的方式，根据每一条订单去查询Users表找到UserName。
相比如下代码：
SELECT
  o.OrderID, ... --关于订单的信息
  CustomerName = u.UserName
FROM Orders o
INNER JOIN Users u
ON o.UserID = u.UserID
WHERE some_condition
最坏的情况下，JOIN会采用嵌套循环的方式执行。如果Orders表和Users表上的UserID字段都建有索引，JOIN可能会以一种更高效的合并联接方式执行。对于数据量大的表，这种优势会非常明显。

所以，除非逻辑上必须，或是为了代码可读性值得付出一定的代价，否则，尽量避免在SELECT子句中使用子查询或包含查询的标量UDF。


5. 避免临时数据集过大

SQL Server处理查询语句的过程中会产生一些临时数据集，比如子查询（也包括派生表和CTE），或是每一步JOIN操作。这些临时数据集需要从磁盘中读出，会占用内存，在数据量大的时候还会部分写入tempdb，而磁盘读写是主要的性能瓶颈，占用内存也不是好事情。所以：

一、只选择需要的字段。如果只需要使用表中的几个字段，就不要SELECT * FROM ...。

二、尽早过滤。比如：
SELECT ...
FROM A
INNER JOIN B ON A.col1 = B.col1
AND A.col0 = some_value /*位置一*/
INNER JOIN C ON B.col2 = C.col2
INNER JOIN ...
WHERE A.col0 = some_value /*位置二*/
位置一和位置二的两个条件只要写一个就可以了。而且逻辑上二者是等价的（只要A和B之间是内联接）。但是由于临时数据集的问题，有些情况下写在位置一的性能会好于写在位置二。大概是SQL Server的查询引擎还不够聪明。因为从理论上讲，查询引擎的物理处理过程并不等同于SQL代码的逻辑解释过程。好的查询引擎应该能够明白位置一和位置二的逻辑等价性，从而采用同样的执行计划。
这一条仅供参考。SQL Server的未来版本或许会解决此类问题。在实际开发中，如果需要由此引发的性能问题，就需要在代码可读化和执行性能之间做一个平衡了。

三、如子查询的数据集过大，考虑插入临时表，分步骤查询。但要考虑到临时表的索引问题。


6. 避免使用动态SQL

动态SQL功能很强大，但是难以调试和维护（字符串拼接、无语法高亮）、效率低（难以重用执行计划）、安全性差（SQL注入）。除非功能上必须，否则尽量避免使用动态SQL。

假如真的需要使用动态SQL，使用sp_executesql的方式优于EXEC()的方式。因为前者有些时候可以重用执行计划而改善性能，而且允许传参，数据类型上更安全。


7. 锁定和并发

对于多用户的数据库系统，并发问题也是影响性能的一部分。如果因为事务的锁定导致大量的等待和阻塞，性能上的损失可能会远大于局部语句级的改进。这也是个大话题（而且又是《存储引擎》一书的专门一章）。

简单来说，在设计数据库时便需要根据系统业务的需要设置合理的并发模型和事务隔离机制。并发模型是数据库级的设定，包括数据库选项 ALLOW_SNAPSHOT_ISOLATION / READ_COMMITTED_SNAPSHOT，这是需要数据库设计师和DBA考虑的部分；事务隔离机制可以在会话级指定，用户要执行查询时可以用 SET TRANSACTION ISOLATION LEVEL语句修改设置。

比如某些数据统计工作，对数据库以查询为主，不希望查询导致表锁定影响业务操作，那最好在查询的每个表上加提示WITH (NOLOCK)，或是在查询最初执行SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED（可以在SSMS设置：工具->选项->查询执行/SQL Server/高级）。这样避免了查询表时申请共享锁。

而对于SQL程序开发来说，需要保证事务尽可能短。在相同的隔离机制下，至少尽量缩短了锁定时间。


8. 磁盘读写（补充内容）

学计算机的人自然都了解阶梯式的存贮器性能/成本平衡：CPU的寄存器和Cache、内存、磁盘、磁带，在这个列表中，越往后的存贮设备越是价格便宜、容量更大，而读写性能则越差。下面这个列表可以对它们的性能差异有一个直观的认识：

各种操作的计时，2001年夏天在一台典型的1GHz PC上完成：
　　执行单条指令　　　　　　　　　　　　1 纳秒 = (1/1,000,000,000) 秒
　　从L1缓存中取一个word　　　　　　　　2 纳秒
　　从主内存中取一个word　　　　　　　　10 纳秒
　　从连续的磁盘位置中取一个word　　　　200 纳秒
　　从新的磁盘位置中取一个word（寻址）　8,000,000纳秒 = 8毫秒

也就是说，从平均执行时间上说，让CPU跑上上百条指令，或是让数据在内存里折腾若干个来回，也比不上读写磁盘上的数据。

对数据库查询来说，扫描数据、写数据、写日志，这些都是恐怖的性能瓶颈。可以参看上面“避免临时数据集过大”一节所说。

通过以下两个会话级选项，可以查看SQL语句执行时的时间和IO统计。
SET STATISTICS IO ON
SET STATISTICS TIME ON

在搭建数据库服务器时，一般的业务数据库的数据和日志文件通常会选择存放在RAID1或RAID5上，但tempdb最好另外放在RAID0上，以减少频繁的磁盘读写开销。

（完）