首页 / 知识

关于性能：可以应用哪些通用技术来优化SQL查询？

2023-04-13 22:09:00

What generic techniques can be applied to optimize SQL queries?

哪些技术可以有效地应用于提高SQL查询的性能？是否有适用的一般规则？

使用主键
避免选择*
建立条件语句时要尽可能具体
去标准化通常可以更有效
表变量和临时表(如果有)通常会比使用大型源表更好
分区视图
使用指标和约束

了解真正的内幕-您应该能够详细了解以下概念：

索引(不仅是索引，而且实际上是如何工作的)。
聚集索引与堆分配表。
文本和二进制查询以及何时可以内联。
填充因子。
记录如何重影以进行更新/删除。
何时发生页面拆分以及原因。
统计信息，以及它们如何影响各种查询速度。
查询计划程序及其对特定数据库的工作方式(例如，在某些系统上，" select *"运行缓慢，在现代MS-Sql DB上，计划程序可以处理该查询程序)。

您可以做的最大的事情是在sql server查询分析器中查找表扫描(确保您打开"显示执行计划")。否则，MSDN和其他地方有无数的文章会提供很好的建议。

顺便说一句，当我开始学习优化查询时，我对跟踪运行了sql server query profiler，查看了生成的SQL，并试图找出为什么这是一个改进。查询分析器远非最佳，但它是一个不错的开始。

您可以考虑以下几项来优化查询性能。

确保您只有最少的数据。确保仅选择所需的列。将字段大小减小到最小。

考虑对数据库进行非规范化以减少联接

避免循环(即获取游标)，坚持设置操作。

将查询实现为存储过程，因为它是预编译的，执行起来会更快。

确保您设置了正确的索引。如果您的数据库主要用于搜索，请考虑使用更多索引。

使用执行计划来查看处理如何完成。您要避免的是表格扫描，因为这样做成本很高。

确保自动统计设置为开。 SQL需要此来帮助确定最佳执行。有关更多信息，请参见Mike Gunderloy的精彩文章。 SQL Server 2005中的统计基础

确保您的索引没有碎片。减少SQL Server索引碎片

确保您的表没有碎片。如何在SQL Server 2000和2005中检测表碎片

使用with语句处理查询过滤。
将每个子查询限制为可能的最小行数。
然后加入子查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

WITH
master AS
(
SELECT SSN, FIRST_NAME, LAST_NAME
FROM MASTER_SSN
WHERE STATE = 'PA' AND
GENDER = 'M'
),
taxReturns AS
(
SELECT SSN, RETURN_ID, GROSS_PAY
FROM MASTER_RETURNS
WHERE YEAR < 2003 AND
YEAR > 2000
)
SELECT *
FROM master,
taxReturns
WHERE master.ssn = taxReturns.ssn

with语句中的子查询可能最终与内联视图相同，
或自动生成的临时表。我在零售数据方面的工作中发现，大约有70-80％的时间可以提高性能。

100％的时间都有维护收益。

其他一些要点(我的是基于SQL Server的，因为每个数据库后端都有它自己的实现，所以它们可能对所有数据库都适用或不适用)：

避免在语句的选择部分中使用相关子查询，因为它们实际上是游标。

设计表以使用正确的数据类型，以避免必须在表上应用函数才能获取数据。例如，当您将数据存储为varchar时，进行日期数学运算就困难得多。

如果发现经常执行包含函数的联接，则需要考虑重新设计表。

如果您的WHERE或JOIN条件包含OR语句(速度较慢)，则使用UNION语句可能会提高速度。

当(并且仅当)两个陈述是互斥的并且以任何一种方式返回相同的结果时，UNION ALL比UNION更快。

通常，NOT EXISTS比NOT IN更快，或者使用ID为null的WHERE子句使用左连接的速度更快

在UPDATE查询中，添加WHERE条件以确保您不更新已经相等的值。更新10,000,000条记录和更新4条记录之间的差异可能非常大！

如果要经常查询某些值或获取较大的报告，请考虑预先计算一些值。仅在下达或调整订单时才需要完成订单中值的总和，而无需在报告中汇总1000亿个订单的结果时。预计算应在触发器中进行，以便它们始终是基础数据更改的最新信息。而且也不必只是数字，我们有一个计算字段，用于连接我们在报表中使用的名称。

警惕标量UDF，它们可能比将代码放在行中要慢。

对于大型数据集，临时表往往更快，而对于小型数据集，表变量往往更快。另外，您可以索引临时表。

在用户界面中格式化通常比在SQL中更快。

返回的数据不要超过实际需要。

这似乎很明显，但是您不相信我最终会解决这个问题的频率。不要联接到不用于过滤记录或实际调用语句的select部分中的字段之一的表。不必要的加入可能非常昂贵。

创建调用其他视图的视图是很糟糕的主意。当您只需要一次并在基础视图中创建100,000,00条记录以获取最终结果中的6条记录时，您可能会发现您正在6次加入同一张表。

在设计数据库时，请考虑不仅报告用户界面以输入数据。如果不使用数据，它是无用的，因此请考虑将其放入数据库后将如何使用它，以及如何维护或审核该数据。这通常会改变设计。 (这就是为什么让ORM设计表的想法不好的一个原因，它只考虑数据的一个用例。)影响最多数据的最复杂的查询是在报告中，因此设计更改以帮助报告可以大大加快查询速度(并简化查询)。

特定于数据库的功能实现可能比使用标准SQL更快(这是他们销售产品的方式之一)，因此请了解您的数据库功能并找出哪些功能更快。

而且因为不能说太多，所以正确使用索引，不要太多或太少。并让您的WHERE子句可修改(可以使用索引)。