加快SQL查询的九种优秀实践

译者 | 陈峻

审校 | 重楼

如您所知，SQL多年来一直是开发和查询数据库的主要语言。在编程实践中，人们逐渐积累了各种在使用过程中的小技巧。下面，让我们来看看有关如何编写出更高效的SQL查询的9种优秀实践。

1.只检索需要的列

对于那些所谓的数据库开发老司机而言，他们会有一个常见的SQL习惯：在编写查询代码时，频繁地使用SELECT *，一次性列出所有可能需要的数据列。显然，如果查询一个存储了一百多列的数据表的所有列，您可以想象会发生什么？毕竟在真实的系统应用环境中，这样的数据表屡见不鲜，而且它们并非总是可以通过重新设计和优化，来合理化其结构。那么，您是否考虑过采取简单点的方法呢？其实，我们可以只选择列的子集，以避免在查询过程中占用不必要的资源，并提高执行的效率。

当然，在进行查询的原型设计时，使用SELECT *是没有太大问题的，但是一旦进入生产阶段，具体的查询就应该只请求那些实际将会使用到的数据列。

2.使用CASE代替UPDATE进行有条件的列更新

在编程过程中，开发人员也会经常使用UPDATE ...WHERE，来根据数据表中的某一列的值，设置另一列的值。例如，UPDATE Users SET Users.Status="遗留" WHERE Users.ID<1000。不可否认，这种方法既简单又直观，但是它有时也会增加不必要的步骤。例如，如果您需要先向某个表中插入数据，然后使用UPDATE来更改数据，那么这便是两个独立的事务。不过，当你有数百万行数据时，此类“徒增”的额外事务就会产生大量不必要的操作。

对于一些大规模操作而言，更好的解决方案是：在查询中使用内联CASE语句，在插入操作过程中设置列的值。如此，我们便可以一次性地同时处理初始插入和修改数据了。

3.尽量减少大表查询

就系统开销而言，对于任何体量数据表的查询，都不是“免费”的。而对于那些拥有数亿、甚至数十亿行的数据表的查询，更是如此。为此，我们需要尽可能地将那些对于大体量数据表的查询，合并为最少的离散操作。例如，如果我们想对一个数据表先按照某一列进行查询，然后再按照另一列予以查询。那么我们便可以首先将其合并为一个查询，然后确保你后续要查询的列拥有了覆盖索引（Covering Index）。

如果您发现自己必须从一张大的数据表中获取相同的数据子集，并需要对其运行较小的查询，那么您可以将其子集持久化到其他地方，并对其进行查询，从而为当前和后续其他操作提速。这也将引出下一项优秀实践。

4.为数据设置预分级（Pre-stage）

假设您或组织中的其他人经常需要执行报表或存储过程。而这些报表或存储过程又需要通过连接几张大的数据表，来汇总大量的数据。那么，您与其每次都重新运行连接，不如将其预分级到专门用于此目的的数据表中。据此，报表或程序便可以针对该表一次性地共同完成其操作，从而为自己（和他人）节省大量的工作。此外，如果您有足够的资源，而且数据库也能够提供支持的话，也可以使用内存表，来进一步实现加速。

5.分批进行删除和更新

试想，您需要在一张数十亿行级的数据表中清除数百万行。虽然最简单的方法莫过于在事务中运行DELETE。但这样一来，整张表就会在此过程中被锁定，直至事务完成。

而复杂一些的方法是分批执行删除（或更新）操作。此类操作可以与其他事务交错进行。由于每个事务都会变得更小，更易于管理，因此其他事务也可以在该操作前后或操作期间“见缝插针”地执行。

在实际应用中，此举将成为任务队列的良好用例。它不但可以跟踪跨会话操作的进度，而且允许其以低优先级的状态，在后台被操作执行。

6.使用临时表提高指针性能

有过开发经验的程序员都知道：指针的使用会导致应用的速度变慢，甚至会阻碍到其他操作。与此同时，那些依赖指针的操作，几乎都可以用其他方法来完成。因此，在大多数情况下，我们应该避免使用指针。

话说回来，如果您由于某种原因不得不使用指针的话，临时表则可以减少由指针带来的性能问题。例如，如果您需要遍历某个数据表，并根据计算结果更改某一列的话，则可以将待更新的候选数据放入临时表中，用指针来遍历该临时表，然后在一次性的操作中，应用所有的更新。当然，此方式还可以将指针的某个处理分成多个批次。

7.使用表值（table-valued）函数而非标量（scalar）函数

由于标量函数可以将计算封装到类似存储过程的SQL代码段中，因此开发人员的通常做法是：将标量函数的结果作为SELECT查询中的某一列去返回。不过，您可以使用表值函数来进行代替，并在查询中使用CROSS AppLY来获得更好的性能。

8.使用分区以避免大量数据移动

SQL Server Enterprise提供了一种“分区（partitioning）”功能，可以将数据库表分割成多个分区。也就是说，如果你有一张表需要经常归档到另一个表中，那么就可以避免使用INSERT/DELETE来移动数据，而直接使用SWITCH来代替。

我们可以假想一个场景，如果有一张表需要每天都被清空至一张归档表中。那么，我们就可以使用SWITCH，简单地将日常表中的页面，分配到该归档表中，从而执行清空和复制操作。与手动复制和删除相比，该切换过程所需的时间要少得多。Cathrine Wilhelmsen提供了如何以这种方式使用分区的精彩教程，您可以通过链接--https://www.cathrinewilhelmsen.NET/table-partitioning-in-sql-server-partition-switching/，进行参考。

9.使用存储过程提高性能，使用ORM带来便利

ORMs，即：对象关系映射器（object-relational mappers）是一套能以编程的方式生成SQL代码的软件工具包。它们允许您使用应用程序的编程语言及其隐喻（Metaphors），来开发和维护查询。

由于ORM可能产生低效、有时甚至无法被代码优化，而备受诟病。同时，它们也会降低开发人员学习SQL、以及理解查询内容的积极性。许多数据库开发人员原则上并不喜欢ORM，他们在需要通过手动编写查询，以获得最佳性能时，往往无所适从。

相反，对于经常被调用、需要良好性能、不常被更改、以及需要数据库分析工具对性能进行检测的查询而言，使用存储过程是最为合理的。与临时查询相比，大多数数据库更容易获得存储过程的汇总统计信息。数据库的查询规划器也更容易对存储过程进行优化。

不过，将更多的数据库逻辑移入存储过程的缺点是：逻辑与数据库的耦合更加紧密。存储过程可能会从性能优势变为巨大的技术债（Technical Debt）。如果您后续准备迁移到另一种数据库技术的话，那么更改ORM的目标会比重写所有存储过程要容易得多。毕竟应用程序的数据库部分的编写方式，与应用逻辑的耦合度不高。相反，ORM倒是能够使得编写和维护数据库代码更加容易。此外，我们可以检查由ORM生成的代码，以进行优化，而且查询缓存也能够允许我们重用那些最常被生成的查询。

总之，如果您觉得应用程序端的可维护性更重要的话，那就请使用ORM；如果您需要在数据库方面具有更好的性能的话，则请使用存储过程。

译者介绍

陈峻（Julian Chen），51CTO社区编辑，具有十多年的IT项目实施经验，善于对内外部资源与风险实施管控，专注传播网络与信息安全知识与经验。

原文标题：SQL unleashed: 9 ways to speed up your SQL queries，作者：Serdar Yegulalp