SQL性能调优可能是一项极其困难的任务, 特别是在处理大规模数据时，即使是最微小的更改也会对性能产生巨大(积极或消极)的影响.

在中型和大型公司, 大多数SQL性能调优将由数据库管理员(DBA)处理。. But believe me, there are plenty of developers 他们必须执行类似dba的任务. 此外，我在很多公司都看到过这种情况 do have DBAs, 他们通常很难与开发人员很好地合作——这些职位只是需要不同的解决问题的模式, 哪些会导致同事之间的分歧.

除此之外，公司结构也会发挥作用. 假设DBA团队和他们所有的数据库被安排在10楼, 而开发人员在15楼, 甚至是在完全独立的报告结构下的不同大楼里——在这种情况下，要顺利地合作当然很难.

在本文中，我想完成两件事:

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

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

如果您是一个完全的数据库新手，甚至问自己“什么是SQL performance tuning?”, 您应该知道，索引是调优SQL数据库的一种有效方法，而这在开发过程中经常被忽略. In basic terms, an index 是一种通过提供快速随机查找和对有序记录的有效访问来提高数据库表上数据检索操作速度的数据结构. 这意味着一旦创建了索引，就可以比以前更快地选择或排序行.

索引还用于定义主键或唯一索引，以保证没有其他列具有相同的值. Of course, 数据库索引是一个庞大而有趣的话题，我无法用这个简短的描述来公正地对待它 这里有一篇更详细的报道).

如果您是索引新手，我建议在构建查询时使用此图:

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

请注意，如果你的桌子经常被敲打 INSERT, UPDATE, and DELETE，您在索引时应该小心—您可能会结束 decreasing performance 因为所有的索引都需要在这些操作之后进行修改.

此外，dba经常在执行百万行以上的批量插入之前删除SQL索引 加快插入过程. 插入批处理后，它们将重新创建索引. Remember, however, that dropping indexes will affect every query running in that table; so this approach is only recommended when working with a single, large insertion.

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

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

其主要功能是以图形方式显示SQL Server查询优化器选择的数据检索方法. 如果你以前没见过，那就 a detailed walkthrough.

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

之后，将出现第三个名为“执行计划”的选项卡. 您可能会看到检测到的缺失索引. 要创建它，只需右键单击执行计划并选择“缺失索引详细信息…”. It’s as simple as that!

(Click to zoom)

SQL调优:避免编码循环

想象一下这样一个场景，其中1000个查询按顺序冲击数据库. Something like:

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

You should avoid such loops in your code. 例如，我们可以使用惟一的 INSERT or UPDATE 包含多行和多值的语句:

INSERT INTO TableName (A,B,C) VALUES(1,2,3)，(4,5,6)，(7,8,9)——SQL SERVER 2008

INSERT INTO表名(A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6——SQL SERVER 2005

更新表名集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)

Make sure that your WHERE 子句避免更新与现有值匹配的存储值. 这样一个微不足道的优化可以通过只更新数百行而不是数千行来显著提高SQL查询性能. For example:

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

SQL调优:避免关联SQL子查询

A correlated subquery 是使用父查询的值的查询吗. 这种SQL查询倾向于运行 row-by-row，对外部查询返回的每一行执行一次，从而降低SQL查询性能. 新的SQL开发人员经常会遇到用这种方式构建查询的情况——因为这通常是一种简单的方法.

下面是一个相关子查询的例子:

SELECT c.Name, 
       c.City,
       查询ID = c的公司名称.CompanyID) AS CompanyName 
FROM Customer c

具体来说，问题在于内部查询(SELECT CompanyName…) is run for each 外部查询返回的行(SELECT c.Name…). But why go over the Company 对于外部查询处理的每一行，都要重复?

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

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

In this case, we go over the Company 表，在开始时只执行一次，并将其与 Customer table. 从那时起，我们可以选择我们需要的值(co.CompanyName) more efficiently.

SQL调优:选择“节省”

我最喜欢的SQL优化技巧之一是避免 SELECT *! 相反，您应该单独包括您需要的特定列. 再一次，这听起来很简单，但我看到这个错误到处都是. 考虑一个包含数百列和数百万行的表—如果您的应用程序实际上只需要几列, 查询for没有意义 all the data. 这是对资源的巨大浪费. (For more issues, see here.)

For example:

SELECT * FROM Employees

vs.

从雇员中选择姓名，城市，国家

如果您确实需要每一列，则显式列出每一列. This isn’t so much a rule, but rather, 一种防止将来出现系统错误和额外的SQL性能调优的方法. 例如，如果你在用an INSERT... SELECT... 并且源表通过添加新列而发生了更改, you might run into issues, 即使目标表不需要该列, e.g.:

INSERT INTO Employees SELECT * FROM OldEmployees

213号电话，16层，1州，1号线
插入错误:列名或提供的值的数量与表定义不匹配.

为了避免SQL Server出现这种错误，你应该单独声明每个列:

插入员工(姓名，城市，国家)
SELECT姓名，城市名，国家名
FROM OldEmployees

但是，请注意，在某些情况下使用 SELECT * could be appropriate. 例如，使用临时表—这将引导我们进入下一个主题.

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

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

But if you have a stored procedure 用一些数据操作 cannot 用单个查询处理, 您可以使用临时表作为中介来帮助您生成最终结果.

当你必须加入一个大桌子时，桌子上有条件, 您可以通过在临时表中传输数据来提高数据库性能, and then making a join on that. 您的临时表将比原始(大)表拥有更少的行，因此连接将更快地完成!

这个决定并不总是直截了当的, 但是这个例子会给你一种你可能想要使用临时表的感觉:

想象一个包含数百万条记录的客户表. 您必须在特定区域上进行连接. 您可以通过使用 SELECT INTO 语句，然后与临时表连接:

SELECT * INTO #Temp FROM RegionID = 5的客户
SELECT r.RegionName, t.名称从区域r加入#Temp t ON t.RegionID = r.RegionID

(注意:一些SQL开发人员也避免使用 SELECT INTO to create temp tables, 说明该命令锁定了tempdb数据库, 禁止其他用户创建临时表. Fortunately, this is fixed in 7.0 and later.)

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

SELECT r.RegionName, t.Name FROM Region r 
SELECT * FROM RegionID = 5的客户 
ON t.RegionID = r.RegionID

But wait! 第二个查询有一个问题. 如上所述，我们应该只在子查询中包括我们需要的列.e., not using SELECT *). Taking that into account:

SELECT r.RegionName, t.Name FROM Region r 
连接(SELECT Name, RegionID FROM Customer, RegionID = 5)作为t 
ON t.RegionID = r.RegionID

所有这些SQL片段都将返回相同的数据. But with temp tables, we could, for example, 在临时表中创建索引以提高性能. 有一些很好的讨论 here 关于临时表和子查询之间的区别.

Finally, 当你完成了你的临时表, 删除它，清空tempdb资源, 而不是只是等待它被自动删除(当你与数据库的连接被终止时):

DROP TABLE #temp

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

这种SQL优化技术涉及到的使用 EXISTS(). 如果要检查记录是否存在，请使用 EXISTS() instead of COUNT(). While COUNT() 扫描整个表格，计算所有符合你条件的条目， EXISTS() 会在看到需要的结果后立即退出吗. This will give you 更好的性能和更清晰的代码.

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

vs.

如果存在(select firstname from employees where firstname like '% john %')
    PRINT 'YES'

SQL性能调优与SQL Server 2016

使用SQL Server 2016的dba可能已经意识到，该版本标志着一个重要的转变 默认值和兼容性管理. As a major version, it, of course, 附带新的查询优化, 但是，现在对是否使用它们的控制是通过 sys.databases.compatibility_level.

SQL性能优化(在Office中)

SQL数据库管理员(dba)和开发人员经常在与数据和非数据相关的问题上发生冲突. Drawn from my experience, 这里有一些(对双方)关于如何有效地相处和合作的建议.

Tweet

面向开发者的数据库优化:

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

2. 即使您是一个SQL数据建模高手，也可以请DBA帮助您绘制关系图. 他们有很多东西可以分享和提供.

3. dba不喜欢快速更改. 这是很自然的:他们需要从整体上分析数据库，并从各个角度检查任何更改的影响. 对一个列进行简单的更改可能需要一周的时间才能实现，但这是因为一个错误可能会给公司带来巨大的损失. Be patient!

4. 不要要求SQL dba在生产环境中更改数据. 如果您希望访问生产数据库，那么您必须对自己的所有更改负责.

SQL Server数据库优化:

1. 如果你不喜欢别人问你关于数据库的问题，给他们一个实时状态面板. Developers 总是怀疑数据库的状态，这样的面板可以节省每个人的时间和精力吗.

2. 在测试/质量保证环境中帮助开发人员. 通过对真实数据的简单测试，可以轻松地模拟生产服务器. 这将为别人和你自己节省大量的时间.

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

4. SQL databases evolve. 总有一天，您必须将数据迁移到新版本. 开发人员指望每个新版本都有重要的新功能. 与其拒绝接受他们的更改，不如提前计划并为迁移做好准备.