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

在中型和大型公司中,大多数 SQL 性能调优将由数据库管理员(DBA)处理。但请相信我,有很多开发人员(https://www.toptal.com/sql),他们必须执行类似DBA的任务。此外,在许多公司中,我见过有DBA,他们往往很难与开发人员合作 - 这些职位只需要不同的问题解决方式,这可能会导致同事之间的分歧。

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

最重要的是,企业结构也可以发挥作用。假设 DBA 团队位于 10 楼,所有数据库都在,而开发人员在 15 楼,甚至在完全独立的报告结构下的不同建筑物中 - 在这些条件下很难顺利合作。  在本文中,我想完成两件事:

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

SQL 性能调优(在代码库中):索引

如果你是数据库的全新成员,甚至会问自己“什么是 SQL [性能调优](https://www.toptal.com/sql-server/how-to-tune-microsoft-sql-server-for-性能)?“,您应该知道索引是调整SQL数据库的有效方法,在开发过程中经常被忽略。在基本术语中,[index](https://en.wikipedia.org/wiki/Database_index)是一种数据结构,它通过提供快速随机查找和有序访问有序记录来提高数据库表上数据检索操作的速度。这意味着,一旦创建了索引,就可以比以前更快地选择或排序行。

索引还用于定义主键或唯一索引,以确保没有其他列具有相同的值。当然,数据库索引是一个非常有趣的话题,我无法用这个简短的描述来做到这一点(但是[这里有更详细的报道](http://stackoverflow.com/questions/1108/how-does -database 索引工作/ 1130#1130))。

如果您不熟悉索引,我建议您在构建查询时使用此图表:  This diagram illustrates a few SQL performance tuning tips every developer should know.

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

请注意,如果您的表经常被敲打INSERT, UPDATE, 和DELETE, 索引时应该小心 - 因为所有索引需要,你最终会降低[性能下降](http://stackoverflow.com/questions/141232/how-many-database-indexes-is-too-many/141243#141243)在这些操作之后进行修改。

此外,DBA 经常删除其 SQL 索引,然后执行百万行以上的批量插入[加快插入过程](http://stackoverflow.com/questions/13700575/is-a-good-practice-to-disable-指数先于插入的一对多-记录上-SQL-SE)。插入批处理后,它们会重新创建索引。但请记住,删除索引会影响该表中运行的每个查询;因此,只有在使用单个大插入时才建议使用此方法。

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

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

它的主要功能是以图形方式显示 SQL Server 查询优化器选择的数据检索方法。如果你以前从未见过它们,那就是[详细的演练](https://youtu.be/lH2_SI04PWQ)。

要检索执行计划(在 SQL Server Management Studio 中),只需在运行查询之前单击“包含实际执行计划”(CTRL + M)。

之后,将出现名为“执行计划”的第三个选项卡。您可能会看到检测到的缺失索引。要创建它,只需右键单击执行计划并选择“缺少索引详细信息…”。就这么简单!

This screenshot demonstrates one of the performance tuning techniques for your SQL database.

(点击放大

SQL 调优:避免编码循环

想象一下,1000 个查询按顺序锤击您的数据库的场景。就像是:

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

您应该[在代码中避免此类循环](http://codeutopia.net/blog/2010/10/07/optimizing-sql-removing-queries-inside-loops/)。例如,我们可以使用唯一的转换上面的代码段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

[相关子查询](https://en.wikipedia.org/wiki/Correlated_subquery)是使用父查询中的值的子查询。这种SQL查询倾向于[逐行]运行(http://technet.microsoft.com/en-us/library/ms187638(v = sql.105).aspx),对于每行返回一次外部查询,从而降低 SQL 查询性能。新的 SQL 开发人员经常被抓住以这种方式构建他们的查询 - 因为它通常是简单的路径。

以下是相关子查询的示例:

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

特别是,问题是内部查询(SELECT CompanyName…) 为外部查询返回的* each *行运行(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 调优:选择 Sparingly

我最喜欢的 SQL 优化技巧之一是避免SELECT *! 相反,您应该单独包含所需的特定列。再次,这听起来很简单,但我看到这个错误到处都是。考虑一个包含数百列和数百万行的表 - 如果您的应用程序只需要几列,那么查询* all *数据就没有意义。这是对资源的巨大浪费。 (有关更多问题,请参阅[此处](http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful#answer-3639964)。

例如:

SELECT * FROM Employees

vs.

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)

[临时表](http://www.tutorialspoint.com/sql/sql-temporary-tables.htm)通常会增加查询的复杂性。如果您的代码可以用简单,直接的方式编写,我建议避免使用临时表。

但是如果你有一个带有一些数据操作的存储过程*不能用单个查询来处理,你可以使用临时表作为中介来帮助你生成最终结果。

当您必须加入一个大表并且所述表上有条件时,您可以通过在临时表中传输数据,然后在* that *上进行连接来提高数据库性能。您的临时表将比原始(大)表具有更少的行,因此连接将更快完成!

这个决定并不总是直截了当,但是这个例子将让您了解可能需要使用临时表的情况:

想象一下拥有数百万条记录的客户表。您必须在特定区域进行联接。你可以通过使用 a 来实现这一点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 及更高版本中修复](http://stackoverflow.com/questions/1302670/sql-server-select-into-and-blocking-with-temp-tables#answer-1302787)。)

作为临时表的替代方法,您可以考虑将子查询用作表:

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 片段都将返回相同的数据。但是使用临时表,我们可以在临时表中创建一个索引来提高性能。有一些很好的讨论[这里](http://stackoverflow.com/questions/11169550/is-there-a-speed-difference-between-cte-subquery-and-temp-tables/11169910#11169910)之间的差异临时表和子查询。

最后,当你完成临时表时,删除它以清除 tempdb 资源,而不是等待它被自动删除(就像你终止与数据库的连接时一样):

DROP TABLE #temp

SQL 调优:“我的记录存在吗?”

这种 SQL 优化技术涉及到的使用EXISTS(). 如果要检查记录是否存在,请使用EXISTS() 代替COUNT(). 而COUNT() 扫描整个表格,计算符合条件的所有条目,EXISTS() 一看到它需要的结果就会退出。这将为您提供[更好的性能和更清晰的代码](http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx)。

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

vs.

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

SQL Server 2016 的 SQL 性能调优

由于使用 SQL Server 2016 的 DBA 可能知道,该版本标志着[默认和兼容性管理]的重要转变(https://www.sqlshack.com/query-optimizer-changes-in-sql-server-2016-explained /)。作为一个主要版本,它当然会带来新的查询优化,但现在可以通过简化控制它们是否被使用sys.databases.compatibility_level.

SQL 性能调优(在 Office 中)

SQL 数据库管理员(DBA)和开发人员经常会遇到与数据和非数据相关的问题。根据我的经验,这里有一些关于如何相处和有效合作的提示(双方)。

SQL performance tuning goes beyond the codebase when DBAs and developers have to work together effectively.

开发人员的数据库优化:

1.

如果您的应用程序突然停止工作,则可能不是数据库问题。例如,您可能遇到网络问题。在指责 DBA 之前调查一下!

2.

即使您是忍者 SQL 数据建模者,也请 DBA 帮助您处理关系图。他们有很多东西要分享和提供。

3.

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

4.

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

SQL Server DBA 的数据库优化:

1.

如果您不喜欢人们向您询问数据库,请为他们提供实时状态面板。 [开发人员](https://www.toptal.com/sql-server)总是怀疑数据库的状态,这样的面板可以节省每个人的时间和精力。

2.

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

3.

开发人员整天都在经常更改业务逻辑的系统上花费一整天。尝试理解这个世界更灵活,并能够在关键时刻打破一些规则。

4.

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

了解基础知识

什么是 DBMS 中的查询处理?

像 SQL Server 这样的数据库管理系统必须将您提供的 SQL 查询转换为他们必须执行的实际指令,以读取或更改数据库中的数据。在处理之后,数据库引擎然后还尝试在可能的情况下自动优化查询。

什么是 SQL Server 中的查询优化?

查询优化是指开发人员或数据库引擎以这样的方式更改查询,即 SQL Server 能够更有效地返回相同的结果。有时候使用 EXISTS()而不是 COUNT()很简单,但有时候需要用不同的方法重写查询。

什么是 SQL Server 中的性能调优?

性能调优包括查询优化,SQL 客户端代码优化,数据库索引管理,以及在另一种意义上,开发人员和 DBA 之间更好的协调。

SQL 中的索引有什么用?

索引跟踪表数据的目标子集,以便可以更快地完成选择和排序,而服务器不必查看该表的每个最后一位数据。

为什么 EXISTS()比 COUNT()更快?

EXISTS()一找到匹配的行就会停止处理,而 COUNT()必须计算每一行,无论你最后是否真的需要这个细节。

关于作者

[Rodrigo Koch,巴西](https://www.toptal.com/resume/rodrigo-koch)

会员自 2012 年 6 月 24 日起

Rodrigo 是使用 C#的.NET Web 应用程序的 Microsoft 认证专家。他的双重国籍使他成为德国和巴西开发商的宝贵资源。他具有强大的开发和故障排除技能,并且具有匹配的组合;他广泛的知名客户包括雀巢,Chartis 保险和卡西欧巴西。如今,他在三星拉丁美洲工作。 [[点击继续…]](https://www.toptal.com/resume/rodrigo-koch)