提高SQL执行效率的17个技巧

由于数据库领域仍相对不成熟，每个平台上的 SQL 开发人员都在苦苦挣扎，一次又一次犯同样的错误。

当然，数据库厂商在取得一些进展，并继续在竭力处理较重大的问题。

无论 SQL 开发人员在 SQL Server、Oracle、DB2、Sybase、MySQL，还是在其他任何关系数据库平台上编写代码，并发性、资源管理、空间管理和运行速度都仍困扰着他们。

问题的一方面是，不存在什么灵丹妙药；针对几乎每条最佳实践，我都可以举出至少一个例外。

通常，开发人员找到自己青睐的方法，而懒得研究其他方法。这也许是缺乏教育的表现，或者开发人员没有认识到自己何时做错了。也许针对一组本地测试数据，查询运行起来顺畅，但是换成生产级系统，表现就差强人意。

我没有期望 SQL 开发人员成为管理员，但他们在编写代码时必须考虑到生产级环境的问题。如果他们在开发初期不这么做，数据库管理员后期会让他们返工，遭殃的就是用户。

我们说调优数据库既是门艺术，又是门科学，这是有道理的，因为很少有全面适用的硬性规则。你在一个系统上解决的问题在另一个系统上不是问题，反之亦然。

说到调优查询，没有正确的答案，但这并不意味着就此应该放弃。你可以遵循以下17条原则，有望收到很好的效果。

不要用 UPDATE 代替 CASE

这个问题很常见，却很难发觉，许多开发人员常常忽视这个问题，原因是使用 UPDATE 再自然不过，这似乎合乎逻辑。

以这个场景为例：你把数据插入一个临时表中，如果另一个值存在，需要它显示某个值。

也许你从 Customer 表中提取记录，想把订单金额超过 100000 美元的客户标记为“Preferred”。

因而，你将数据插入到表中，运行 UPDATE 语句，针对订单金额超过 100000 美元的任何客户，将 CustomerRank 这一列设为“Preferred”。

问题是，UPDATE 语句记入日志，这就意味着每次写入到表中，要写入两次。

解决办法：在 SQL 查询中使用内联 CASE 语句，这检验每一行的订单金额条件，并向表写入“Preferred”标记之前，设置该标记，这样处理性能提升幅度很惊人。

不要盲目地重用代码

这个问题也很常见，我们很容易拷贝别人编写的代码，因为你知道它能获取所需的数据。

问题是，它常常获取过多你不需要的数据，而开发人员很少精简，因此到头来是一大堆数据。

这通常表现为 WHERE 子句中的一个额外外连接或额外条件。如果你根据自己的确切要求精简重用的代码，就能大幅提升性能。

需要几列，就提取几列

这个问题类似第 2 个问题，但这是列所特有的。很容易用 SELECT* 来编写所有查询代码，而不是把列逐个列出来。

问题同样是，它提取过多你不需要的数据，这个错误我见过无数次了。开发人员对一个有 120 列、数百万行的表执行 SELECT* 查询，但最后只用到其中的三五列。

因此，你处理的数据比实际需要的多得多，查询返回结果是个奇迹。你不仅处理过多不需要的数据，还夺走了其他进程的资源。

不要查询两次（double-dip）

这是我看到好多人犯的另一个错误：写入存储过程，从一个有数亿行的表中提取数据。

开发人员想提取住在加利福尼亚州，年收入高于 4 万美元的客户信息。于是，他查询住在加利福尼亚州的客户，把查询结果放到一个临时表中。

然后再来查询年收入高于 4 万美元的客户，把那些结果放到另一个临时表中。最后他连接这两个表，获得最终结果。

你是在逗我吧？这应该用一次查询来完成，相反你对一个超大表查询两次。别犯傻了：大表尽量只查询一次，你会发现存储过程执行起来快多了。

一种略有不同的场景是，某个过程的几个步骤需要大表的一个子集时，这导致每次都要查询大表。

想避免这个问题，只需查询这个子集，并将它持久化存储到别处，然后将后面的步骤指向这个比较小的数据集。

知道何时使用临时表

这个问题解决起来要麻烦一点，但效果显著。在许多情况下可以使用临时表，比如防止对大表查询两次。还可以使用临时表，大幅减少连接大表所需的处理能力。

如果你必须将一个表连接到大表，该大表上又有条件，只需将大表中所需的那部分数据提取到临时表中，然后再与该临时表连接，就可以提升查询性能。

如果存储过程中有几个查询需要对同一个表执行类似的连接，这同样大有帮助。

预暂存数据

这是我最爱聊的话题之一，因为这是一种经常被人忽视的老方法。

如果你有一个报表或存储过程（或一组）要对大表执行类似的连接操作，通过提前连接表，并将它们持久化存储到一个表中来预暂存数据，就可以对你大有帮助。

现在，报表可以针对该预暂存表来运行，避免大连接。你并非总是可以使用这个方法，但一旦用得上，你会发现这绝对是节省服务器资源的好方法。

请注意：许多开发人员避开这个连接问题的做法是，将注意力集中在查询本身上，根据连接创建只读视图，那样就不必一次又一次键入连接条件。

但这种方法的问题是，仍要为需要它的每个报表运行查询。如果预暂存数据，你只要运行一次连接（比如说报表前 10 分钟），别人就可以避免大连接了。

你不知道我有多喜欢这一招，在大多数环境下，有些常用表一直被连接起来，所以没理由不能先预暂存起来。

批量删除和更新

这是另一个经常被忽视的技巧，如果你操作不当，删除或更新来自大表的大量数据可能是一场噩梦。

问题是，这两种语句都作为单一事务来运行。如果你需要终结它们，或者它们在执行时系统遇到了问题，系统必须回滚（roll back）整个事务，这要花很长的时间。

这些操作在持续期间还会阻塞其他事务，实际上给系统带来了瓶颈，解决办法就是，小批量删除或更新。

这通过几个方法来解决问题：

• 无论事务因什么原因而被终结，它只有少量的行需要回滚，那样数据库联机返回快得多。
• 小批量事务被提交到磁盘时，其他事务可以进来处理一些工作，因而大大提高了并发性。

同样，许多开发人员一直固执地认为：这些删除和更新操作必须在同一天完成。事实并非总是如此，如果你在归档更是如此。

如果你需要延长该操作，可以这么做，小批量有助于实现这点；如果你花更长的时间来执行这些密集型操作，切忌拖慢系统的运行速度。

使用临时表来提高游标性能

如果可能的话，最好避免游标。游标不仅存在速度问题，而速度问题本身是许多操作的一大问题，还会导致你的操作长时间阻塞其他操作，这大大降低了系统的并发性。

然而无法总是避免使用游标，避免不了使用游标时，可以改而对临时表执行游标操作，以此摆脱游标引发的性能问题。

不妨以查阅一个表，基于一些比较结果来更新几个列的游标为例。你也许可以将该数据放入临时表中，然后针对临时表进行比较，而不是针对活动表进行比较。

然后你可以针对小得多，锁定时间很短的活动表运行单一的 UPDATE 语句。

进行这样的数据修改可大大提高并发性。最后我要说，你根本不需要使用游标，总是会有一种基于集合的解决方法。

不要嵌套视图

视图也许很方便，不过使用视图时要小心。

虽然视图有助于将庞大查询遮掩起来、无须用户操心，并实现数据访问标准化，但你很容易发现自己陷入这种困境：视图 A 调用视图 B，视图 B 调用视图 C，视图 C 又调用视图 D，这就是所谓的嵌套视图。

这会导致严重的性能问题，尤其是这两方面：

• 返回的数据很有可能比你需要的多得多。
• 查询优化器将放弃并返回一个糟糕的查询方案。

我遇到过喜欢嵌套视图的客户，这个客户有一个视图用于几乎所有数据，因为它有两个重要的连接。

问题是，视图返回的一个列里面居然有 2MB 大小的文档，有些文档甚至更大。

在运行的几乎每一次查询中，这个客户要在网络上为每一行至少多推送 2MB 的数据。自然，查询性能糟糕透顶。

没有一个查询实际使用该列！当然，该列被埋在七个视图的深处，要找出来都很难。我从视图中删除该文档列后，最大查询的时间从 2.5 小时缩短至 10 分钟。

我最后层层解开了嵌套视图（有几个不必要的连接和列），并写了一个普通的查询，结果同样这个查询的时间缩短至不到 1 秒。

使用表值函数

这是一直以来我最爱用的技巧之一，因为它是只有专家才知道的那种秘诀。

在查询的 SELECT 列表中使用标量函数时，该函数因结果集中的每一行而被调用，这会大幅降低大型查询的性能。

然而可以将标量函数转换成表值函数，然后在查询中使用 CROSS APPLY，就可以大幅提升性能，这个奇妙的技巧可以显著提升性能。

使用分区避免移动大型数据

不是每个人都能利用依赖 SQL Server Enterprise 中分区的这个技巧，但是对于能利用它的人来说，这个技巧很棒。

大多数人没有意识到 SQL Server 中的所有表都是分区的。如果你喜欢，可以把一个表分成多个分区，但即使简单的表也从创建那一刻起就分区了。

然而，它们是作为单个分区创建的。如果你在运行 SQL Server Enterprise，已经可以随时享用分区表的优点了。

这意味着你可以使用 SWITCH 之类的分区功能，归档来自仓库加载的大量数据。

举个实际例子，去年我碰到过这样一个客户：该客户需要将数据从当日的表复制到归档表中；那样万一加载失败，公司可以迅速用当日的表来恢复。

由于各种原因，无法每次将表的名称改来改去，所以公司每天在加载前将数据插入到归档表中，然后从活动表删除当日的数据。

这个过程一开始很顺利，但一年后，复制每个表要花 1 个半小时，每天要复制几个表，问题只会越来越糟。

解决办法是抛弃 INSERT 和 DELETE 进程，使用 SWITCH 命令。

SWITCH 命令让该公司得以避免所有写入，因为它将页面分配给了归档表。

这只是更改了元数据，SWITCH 运行平均只要两三秒钟，如果当前加载失败，你可以通过 SWTICH 将数据切换回到原始表。

如果你非要用 ORM，请使用存储过程

ORM 是我经常炮轰的对象之一。简而言之，别使用 ORM（对象关系映射器）。

ORM 会生成世界上最糟糕的代码，我遇到的几乎每个性能问题都是由它引起的。

相比知道自己在做什么的人，ORM 代码生成器不可能写出一样好的 SQL。但是如果你使用 ORM，那就编写自己的存储过程，让 ORM 调用存储过程，而不是写自己的查询。

我知道使用 ORM 的种种理由，也知道开发人员和经理都喜欢 ORM，因为它们有助于产品迅速投向市场。但是如果你看一下查询对数据库做了什么，就会发现代价太高了。

存储过程有许多优点，首先，你在网络上推送的数据少得多。如果有一个长查询，那么它可能在网络上要往返三四趟才能让整个查询到达数据库服务器。

这不包括服务器将查询重新组合起来并运行所花的时间；另外考虑这点：查询可能每秒运行几次或几百次。

使用存储过程可大大减少传输的流量，因为存储过程调用总是短得多。另外，存储过程在 Profiler 或其他任何工具中更容易追踪。

存储过程是数据库中的实际对象，这意味着相比临时查询（ad-hoc query），获取存储过程的性能统计数字要容易得多，因而发现性能问题、查明异常情况也要容易得多。

此外，存储过程参数化更一致，这意味着你更可能会重用执行方案，甚至处理缓存问题，要查明临时查询的缓存问题很难。

有了存储过程，处理边界情况（edge case），甚至增加审计或变更锁定行为变得容易多了。存储过程可以处理困扰临时查询的许多任务。

几年前，我妻子理清了 Entity Framework 的一个两页长的查询，该查询花了 25 分钟来运行。

她化繁为简，将这个大型查询改写为 SELECT COUNT(*) fromT1，这不是开玩笑。

那些只是要点，我知道，许多 .NET 程序员认为业务逻辑不适宜放在数据库中，这大错特错。

如果将业务逻辑放在应用程序的前端，仅仅为了比较就得将所有数据传送一遍，那样不会有好的性能。

我有个客户将所有逻辑保存在数据库的外面，在前端处理一切。该公司将成千上万行数据发送到前端，以便能够运用业务逻辑，并显示所需的数据。

这个过程花了 40 分钟，我把存储过程放在后端，让它从前端调用；页面在三秒钟内加载完毕。

当然，有时逻辑适宜放在前端上，有时适宜放在数据库中，但是 ORM 总是让我上火。

不要对同一批次的许多表执行大型操作

这个似乎很明显，但实则不然。我会用另一个鲜活的例子，因为它更能说明问题。

我有一个系统存在大量的阻塞，众多操作处于停滞状态。结果查明，每天运行几次的删除例程在删除显式事务中 14 个表的数据。处理一个事务中的所有 14 个表意味着，锁定每个表，直到所有删除完成。

解决办法就是，将每个表的删除分解成单独的事务，以便每个删除事务只锁定一个表。

这解放了其他表，缓解了阻塞，让其他操作得以继续运行。你总是应该把这样的大事务分解成单独的小事务，以防阻塞。

不要使用触发器

这个与前一个大体一样，但还是值得一提。触发器的问题：无论你希望触发器执行什么，都会在与原始操作同一个的事务中执行。

如果你写一个触发器，以便更新 Orders 表中的行时将数据插入到另一个表中，会同时锁定这两个表，直到触发器执行完毕。

如果你需要在更新后将数据插入到另一个表中，要将更新和插入放入到存储过程中，并在单独的事务中执行。

如果你需要回滚，就很容易回滚，不必同时锁定这两个表。与往常一样，事务要尽量短小，每次不要锁定多个资源。

不要在 GUID 上聚类

这么多年后，我难以相信我们居然还在为这个问题而苦恼。但我仍然每年遇到至少两次聚类 GUID。

GUID（全局唯一标识符）是一个 16 字节的随机生成的数字。相比使用一个稳定增加的值（比如 DATE 或 IDENTITY），按此列对你表中的数据进行排序导致表碎片化快得多。

几年前我做过一项基准测试，我将一堆数据插入到一个带聚类 GUID 的表中，将同样的数据插入到另一个带 IDENTITY 列的表中。

GUID 表碎片化极其严重，仅仅过了 15 分钟，性能就下降了几千个百分点。

5 小时后，IDENTITY 表的性能才下降了几个百分点，这不仅仅适用于 GUID，它适用于任何易失性列。

如果只需查看数据是否存在，就不要计数行

这种情况很常见，你需要查看数据存在于表格中，根据这番检查的结果，你要执行某个操作。

我经常见到有人执行 SELECT COUNT（*）FROMdbo.T1来检查该数据是否存在：

1. SET @CT=(SELECT COUNT(*) FROM
2. dbo.T1);
3.    If@CT>0
4.    BEGIN
5.    <Do something>
6. END

这完全没必要，如果你想检查数据是否存在，只要这么做：

1. If EXISTS (SELECT 1 FROM dbo.T1)
2.  BEGIN
3.  <Do something>
4.  END

不要计数表中的一切，只要取回你找到的第一行。SQL Server 聪明得很，会正确使用 EXISTS，第二段代码返回结果超快。

表越大，这方面的差距越明显。在你的数据变得太大之前做正确的事情。调优数据库永不嫌早。

实际上，我只是在我的其中一个生产数据库上运行这个例子，针对一个有 2.7 亿行的表。

第一次查询用时 15 秒，包含 456197 个逻辑读取，第二次查询不到 1 秒就返回结果，只包含 5 个逻辑读取。

然而如果你确实需要计数表的行数，表又很大，另一种方法就是从系统表中提取，SELECT rows fromsysindexes 将为你获得所有索引的行数。

又由于聚类索引代表数据本身，所以只要添加 WHERE indid = 1，就能获得表行，然后只需包含表名称即可。

所以，最后的查询是：

1. SELECT rows from sysindexes where object_name(id)=’T1’and indexid =1

在我 2.7 亿行的表中，不到 1 秒就返回结果，只有 6 个逻辑读取，现在性能不一样了。

不要进行逆向搜索

以简单的查询 SELECT * FROMCustomers WHERE RegionID <> 3 为例。

你不能将索引与该查询结合使用，因为它是逆向搜索，需要借助表扫描来逐行比较。

如果你需要执行这样的任务，可能发现如果重写查询以使用索引，性能会好得多。

该查询很容易重写，就像这样：

1. SELECT * FROM Customers WHERE RegionID<3 UNION ALL SELECT * FROM Customers WHERE RegionID

这个查询将使用索引，所以如果你的数据集很大，其性能会远胜过表扫描版本。

当然，没有什么是那么容易的，也许性能更糟，所以使用之前先试一下。它百分之百管用，虽然涉及太多的因素。

最后，我意识到这个查询违反了第 4 条规则：不要查询两次，但这也表明没有硬性规则。虽然我们在这里查询两次，但这么做是为了避免开销很大的表扫描。

你无法一直运用所有这些技巧，但如果牢记它们，有一天你会用它们来解决一些大问题。

要记住的最重要一点是，别将我说的话当成教条。在你的实际环境中试一下，同样的解决办法不是在每种情况下都管用，不过我排查糟糕的性能时一直使用这些方法，而且屡试不爽。

本文文字及图片出自 database.51cto.com

你也许感兴趣的：

• 译 | 如何更优雅的写出你的SQL语句
• 为什么说 LINQ 胜过 SQL
• SQL与NoSQL（MongoDB）数据库的对应关系图表
• 像写SQL一样编写Java数据应用
• 一次非常有意思的 SQL 优化经历
• 格式化 SQL 来提高效率
• MySQL编程中的6个重要的实用技巧
• SQL/NoSQL两大阵营激辩:谁更适合大数据
• 【外评】电脑从哪里获取时间？
• 【外评】为什么 Stack Overflow 正在消失？