面向开发人员的 SQL 数据库性能调优

已发表: 2022-03-11

SQL 性能调优可能是一项非常困难的任务，尤其是在处理大规模数据时，即使是最微小的变化也会对性能产生巨大的（正面或负面）影响。

在大中型公司中，大多数 SQL 性能调整将由数据库管理员 (DBA) 处理。 但请相信我，有很多开发人员必须执行类似 DBA 的任务。 此外，在我见过的许多确实有 DBA 的公司中，他们常常难以与开发人员很好地合作——这些职位只需要不同的问题解决模式，这可能会导致同事之间的分歧。

在处理大规模数据时，即使是最细微的变化也会对性能产生巨大影响。

最重要的是，公司结构也可以发挥作用。 假设 DBA 团队和他们的所有数据库都放在 10 楼，而开发人员在 15 楼，或者甚至在完全独立的报告结构下的不同建筑物中——在这种情况下，肯定很难顺利合作。

在这篇文章中，我想完成两件事：

  1. 为开发者提供一些开发者端的 SQL 性能调优技巧。
  2. 解释开发人员和 DBA 如何有效地合作。

SQL 性能调优（在代码库中）：索引

如果您是数据库的新手，甚至问自己“什么是 SQL 性能调优？”，您应该知道索引是调优 SQL 数据库的有效方法，但在开发过程中经常被忽视。 基本上，索引是一种数据结构，它通过提供快速随机查找和有序记录的高效访问来提高对数据库表的数据检索操作的速度。 这意味着一旦您创建了索引，您就可以比以前更快地选择或排序您的行。

索引还用于定义主键或唯一索引，这将保证没有其他列具有相同的值。 当然，数据库索引是一个非常有趣的话题，我不能用这个简短的描述来公正地说明（但这里有更详细的文章）。

如果您不熟悉索引，我建议您在构建查询时使用此图：

此图说明了每个开发人员都应该知道的一些 SQL 性能调优技巧。

基本上，目标是索引主要的搜索和排序列。

请注意，如果您的表经常受到INSERT 、 UPDATE和DELETE的影响，则在编制索引时应该小心——最终可能会降低性能，因为在这些操作之后需要修改所有索引。

此外，DBA 经常在执行超过百万行的批量插入之前删除他们的 SQL 索引，以加快插入过程。 插入批次后，他们会重新创建索引。 但是请记住，删除索引会影响该表中运行的每个查询。 因此，仅在使用单个大插入时才建议使用此方法。

SQL 调优：SQL Server 中的执行计划

顺便说一句：SQL Server 中的执行计划工具可用于创建索引。

它的主要功能是以图形方式显示 SQL Server 查询优化器选择的数据检索方法。 如果您以前从未见过它们，这里有详细的演练。

要检索执行计划（在 SQL Server Management Studio 中），只需在运行查询之前单击“包括实际执行计划”（CTRL + M）。

之后，将出现名为“执行计划”的第三个选项卡。 您可能会看到检测到的缺失索引。 要创建它，只需右键单击执行计划并选择“Missing Index Details...”。 就这么简单！

此屏幕截图演示了 SQL 数据库的一种性能调整技术。

（点击放大）

SQL 调优：避免编码循环

想象一个场景，其中 1000 个查询按顺序敲击您的数据库。 就像是：

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

你应该避免在你的代码中出现这样的循环。 例如，我们可以通过使用具有多行和多值的唯一INSERT或UPDATE语句来转换上述代码段：

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

确保您的WHERE子句在与现有值匹配时避免更新存储的值。 这种微不足道的优化可以通过仅更新数百行而不是数千行来显着提高 SQL 查询性能。 例如：

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

SQL 调优：避免相关的 SQL 子查询

相关子查询是使用来自父查询的值的查询。 这种 SQL 查询倾向于逐行运行，对于外部查询返回的每一行执行一次，从而降低 SQL 查询性能。 新的 SQL 开发人员经常会以这种方式构建他们的查询，因为这通常是最简单的方法。

这是一个相关子查询的示例：

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

特别是，问题在于内部查询（ SELECT CompanyName… ）针对外部查询（ SELECT c.Name… ）返回的每一行运行。 但是为什么要对外部查询处理的每一行一遍又一遍地检查Company呢？

一种更有效的 SQL 性能调优技术是将相关子查询重构为连接：

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

在这种情况下，我们在开始时只检查Company表一次，然后将其与Customer表连接。 从那时起，我们可以更有效地选择我们需要的值（ co.CompanyName ）。

SQL 调优：谨慎选择

我最喜欢的 SQL 优化技巧之一是避免SELECT * ！ 相反，您应该单独包含您需要的特定列。 同样，这听起来很简单，但我到处都能看到这个错误。 考虑一个包含数百列和数百万行的表——如果您的应用程序真的只需要几列，那么查询所有数据是没有意义的。 这是对资源的巨大浪费。 （有关更多问题，请参阅此处。 ）

例如：

 SELECT * FROM Employees

对比

SELECT FirstName, City, Country FROM Employees

如果您确实需要每一列，请明确列出每一列。 这与其说是一条规则，不如说是一种防止未来系统错误和额外的 SQL 性能调整的方法。 例如，如果您正在使用INSERT... SELECT...并且源表已通过添加新列发生更改，您可能会遇到问题，即使目标表不需要该列，例如：

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

为了避免 SQL Server 出现这种错误，您应该单独声明每一列：

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

但是请注意，在某些情况下使用SELECT *可能是合适的。 例如，使用临时表——这将我们引向下一个主题。

SQL 调优：临时表的明智使用 (#Temp)

临时表通常会增加查询的复杂性。 如果您的代码可以以简单、直接的方式编写，我建议避免使用临时表。

但是，如果您有一个存储过程，其中包含一些无法通过单个查询处理的数据操作，则可以使用临时表作为中介来帮助您生成最终结果。

当您必须连接一个大表并且该表存在条件时，您可以通过将数据传输到临时表中来提高数据库性能，然后在该表上进行连接。 您的临时表的行数将少于原始（大）表，因此连接将更快完成！

这个决定并不总是直截了当的，但这个例子会让你对可能想要使用临时表的情况有所了解：

想象一个包含数百万条记录的客户表。 您必须加入特定区域。 您可以通过使用SELECT INTO语句然后加入临时表来实现此目的：

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

（注意：一些SQL开发者也避免使用SELECT INTO创建临时表，说这个命令会锁定tempdb数据库，不允许其他用户创建临时表。幸运的是，这在7.0及更高版本中已修复。）

作为临时表的替代方案，您可以考虑使用子查询作为表：

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

可是等等！ 第二个查询有问题。 如上所述，我们应该只在子查询中包含我们需要的列（即，不使用SELECT * ）。 考虑到这一点：

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

所有这些 SQL 片段都将返回相同的数据。 但是对于临时表，我们可以，例如，在临时表中创建一个索引来提高性能。 关于临时表和子查询之间的区别，这里有一些很好的讨论。

最后，当您完成临时表时，将其删除以清除 tempdb 资源，而不是等待它被自动删除（就像您与数据库的连接终止时一样）：

 DROP TABLE #temp

SQL 调优：“我的记录存在吗？”

这种 SQL 优化技术涉及EXISTS()的使用。 如果要检查记录是否存在，请使用EXISTS()而不是COUNT() 。 当COUNT()扫描整个表，计算所有符合条件的条目时， EXISTS()将在看到它需要的结果后立即退出。 这将为您提供更好的性能和更清晰的代码。

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

对比

IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

使用 SQL Server 2016 进行 SQL 性能调优

正如使用 SQL Server 2016 的 DBA 可能知道的那样，该版本标志着默认值和兼容性管理的重要转变。 作为主要版本，它当然带有新的查询优化，但现在通过sys.databases.compatibility_level简化了对是否使用它们的控制。

SQL 性能调优（在办公室）

SQL 数据库管理员 (DBA) 和开发人员经常在数据和非数据相关问题上发生冲突。 根据我的经验，这里有一些关于如何有效相处和合作的技巧（适用于双方）。

当 DBA 和开发人员必须有效地合作时，SQL 性能调整超出了代码库的范围。

鸣叫

开发人员的数据库优化：

  1. 如果您的应用程序突然停止工作，则可能不是数据库问题。 例如，也许您有网络问题。 在指责 DBA 之前先调查一下！

  2. 即使您是一名忍者 SQL 数据建模师，也请让 DBA 帮助您绘制关系图。 他们有很多东西要分享和提供。

  3. DBA 不喜欢快速变化。 这是很自然的：他们需要从整体上分析数据库，并从各个角度检查任何更改的影响。 列中的一个简单更改可能需要一周时间才能实施——但这是因为一个错误可能会成为公司的巨大损失。 耐心点！

  4. 不要要求 SQL DBA 在生产环境中进行数据更改。 如果您想访问生产数据库，您必须对自己的所有更改负责。

SQL Server DBA 的数据库优化：

  1. 如果您不喜欢人们向您询问有关数据库的问题，请给他们一个实时状态面板。 开发人员总是对数据库的状态持怀疑态度，这样的面板可以节省每个人的时间和精力。

  2. 在测试/质量保证环境中帮助开发人员。 通过对真实世界数据的简单测试，可以轻松模拟生产服务器。 这对于他人和您自己来说都将大大节省时间。

  3. 开发人员整天都在处理业务逻辑经常变化的系统。 试着理解这个世界更加灵活，能够在关键时刻打破一些规则。

  4. SQL 数据库不断发展。 有一天，您必须将数据迁移到新版本。 开发人员依靠每个新版本的重要新功能。 不要拒绝接受他们的更改，而是提前计划并为迁移做好准备。

相关： SQL 索引解释，Pt。 1，铂。 2，和铂。 3