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

本文概述

• SQL性能调优(在代码库中)：索引
• 使用SQL Server 2016进行SQL性能调整
• SQL性能调整(在办公室)

SQL性能调整可能是一项非常困难的任务, 尤其是在处理大规模数据时, 即使最细微的更改也会对性能产生巨大的(正面或负面的)影响。

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

当处理大规模数据时, 即使是很小的更改也会对性能产生巨大影响。

最重要的是, 公司结构也可以发挥作用。假设DBA团队的所有数据库都位于10楼, 而开发人员则位于15楼, 甚至在完全独立的报表结构下甚至位于不同的建筑物中, 在这种情况下, 要顺利地协同工作当然也很困难。 在本文中, 我想完成两件事：

1. 为开发人员提供一些开发人员端SQL性能调优技术。
2. 说明开发人员和DBA如何有效地合作。

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调整：避免编码循环

想象一下一个场景, 其中有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)和开发人员经常在与数据和非数据相关的问题上发生冲突。根据我的经验, 这里有一些技巧(针对双方), 介绍了如何相处和有效合作。

鸣叫

开发人员的数据库优化：

1. 如果你的应用程序突然停止工作, 则可能不是数据库问题。例如, 也许你遇到网络问题。在指控DBA之前先进行调查！

2. 即使你是忍者SQL数据建模者, 也可以要求DBA帮助你处理关系图。他们有很多可以分享和提供的东西。

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

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

SQL Server DBA的数据库优化：

1. 如果你不喜欢有人向你询问有关数据库的信息, 请给他们一个实时状态面板。开发人员总是对数据库的状态感到怀疑, 这样的小组可以节省每个人的时间和精力。

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

3. 开发人员整天都在业务逻辑频繁变化的系统上花费。尝试了解这个世界变得更加灵活, 并能够在关键时刻打破一些规则。

4. SQL数据库不断发展。需要将数据迁移到新版本的日子到了。开发人员会在每个新版本中依靠重要的新功能。不要拒绝接受他们的更改, 而要提前计划并为迁移做好准备。