SQL.数据库性能调整开发人员

凯文在全堆栈,桌面和独立游戏开发中有20多年。他最近专注于PostgreSQL,JavaScript,Perl和Haxe。

SQL.性能调整可能是一个令人难以置信的困难任务,特别是在使用大规模数据时,即使是最小的变化也可能对性能产生显着的(正面或负面)。

在中型和大公司中,大多数SQL性能调整将由数据库管理员(DBA)处理。但相信我,有 大量开发人员 那里必须执行DBA样任务。此外,在许多我见过的公司中 有DBA,他们常常与开发人员努力工作 - 职位只是需要不同的问题解决方式,这可能导致同事之间的分歧。

在使用大规模数据时,即使最小的变化也可能对性能产生巨大影响。

最重要的是,企业结构也可以发挥作用。假设DBA团队与所有数据库放在10楼,而DEVS在15楼,甚至在完全单独的报告结构下的不同建筑物 - 在这些条件下,它肯定很难顺利地工作。

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

  1. 为开发人员提供一些开发人员侧SQL性能调整技术。
  2. 解释开发人员和DBA如何有效地合作。

SQL.性能调整(在Codebase中):索引

如果您是数据库的完整新人,甚至询问自己“什么是SQL 性能调整?“,您应该知道索引是调整在开发期间通常被忽视的SQL数据库的有效方法。在基本术语中,一个 指数 是一种数据结构,通过提供快速随机查找和有效访问有序记录,通过提供数据库表上的数据检索操作的速度。这意味着一旦创建了索引,您就可以比以前更快地选择或排序行。

索引也用于定义主键或唯一索引,这将保证没有其他列具有相同的值。当然,数据库索引是一个巨大的一个有趣的话题,我不能用这个简短描述(但是 这是一个更详细的写作)。

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

此图说明了每个开发人员应该知道的一些SQL性能调整提示。

基本上,目标是索引主要搜索和订购列。

Note that if your tables are constantly hammered by INSERT, UPDATE, and DELETE, you should be careful when indexing—you could end up 减少性能 由于所有索引都需要在这些操作之后进行修改。

此外,DBA经常在执行百万加行的批量插入之前放下SQL索引 加快插入过程。插入批次后,它们会重新创建索引。但请记住,丢弃索引会影响该表中运行的每个查询;因此,只有在使用单个大的插入时才建议使用这种方法。

SQL. Tuning:SQL Server中的执行计划

顺便说一下:SQL Server中的执行计划工具对于创建索引非常有用。

其主要功能是以图形方式显示由SQL Server查询优化器选择的数据检索方法。如果你以前从未见过他们,那就有了 详细的演练.

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

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

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

(点击放大)

SQL. Tuning:避免编码循环

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

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

你应该 避免这样的循环 in your code. For example, we could transform the above snippet by using a unique INSERT or UPDATE statement with multiple rows and values:

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)

Make sure that your WHERE clause avoids updating the stored value if it matches the existing value. Such a trivial optimization can dramatically increase SQL query performance by updating only hundreds of rows instead of thousands. For example:

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

SQL. Tuning:避免相关的SQL子查询

A 相关子查询 是使用父查询的值的。这种SQL查询往往会运行 逐行,用于外部查询返回的每一行,从而降低SQL查询性能。新的SQL开发人员经常以这种方式构建查询 - 因为它通常是轻松的路线。

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

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

In particular, the problem is that the inner query (SELECT CompanyName…) is run for 每个 row returned by the outer query (SELECT c.Name…)。 But why go over the Company again and again for every row processed by the outer query?

更高效的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 table just once, at the start, and JOIN it with the Customer table. From then on, we can select the values we need (co.CompanyName) more efficiently.

SQL. Tuning:谨慎选择

One of my favorite SQL optimization tips is to avoid SELECT *! Instead, you should individually include the specific columns that you need. Again, this sounds simple, but I see this error all over the place. Consider a table with hundreds of columns and millions of rows—if your application only really needs a few columns, there’s no sense in querying for 全部 数据。这是一种大规模的资源浪费。 (有关更多问题,请参阅 这里.)

例如:

SELECT * FROM Employees

vs.

SELECT FirstName, City, Country FROM Employees

If you really need every column, explicitly list every column. This isn’t so much a rule, but rather, a means of preventing future system errors and additional SQL performance tuning. For example, if you’re using an INSERT... SELECT... and the source table has changed via the addition of a new column, you might run into issues, even if that column isn’t needed by the destination table, e.g.:

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

Note, however, that there are some situations where the use of SELECT * could be appropriate. For example, with temp tables—which leads us to our next topic.

SQL. Tuning:明智地使用临时表(#temp)

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

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

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

该决定并不总是简单的,但这个例子会给你一个感觉,你可能想要使用临时表的情况:

Imagine a customer table with millions of records. You have to make a join on a specific region. You can achieve this by using a SELECT INTO statement and then joining with the temp table:

SELECT * INTO #Temp FROM Customer WHERE RegionID = 5
从区域r中选择r.regionname,t.name JOIN #Temp t 在t.regionid = r.regionid

(Note: some SQL developers also avoid using SELECT INTO to create temp tables, saying that this command locks the tempdb database, disallowing other users from creating temp tables. Fortunately, this is 修复了7.0及更高版本。)

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

从区域r中选择r.regionname,t.name 
JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t 
在t.regionid = r.regionid

But wait! There’s a problem with this second query. As described above, we should only be including the columns we need in our subquery (i.e., not using SELECT *)。 Taking that into account:

从区域r中选择r.regionname,t.name 
JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t 
在t.regionid = r.regionid

所有这些SQL代码段都将返回相同的数据。但是对于临时表,我们可以在临时表中创建一个索引以提高性能。有一些良好的讨论 这里 论临时表与子查询之间的差异。

最后,当您使用临时表完成时,将其删除以清除TempdB资源,而不是等待它自动删除(因为它将在您的连接终止时):

DROP TABLE #temp

SQL. Tuning:“我的记录是否存在?”

This SQL optimization technique concerns the use of EXISTS(). If you want to check if a record exists, use EXISTS() instead of COUNT(). While COUNT() scans the entire table, counting up all entries matching your condition, EXISTS() will exit as soon as it sees the result it needs. This will give you 更好的性能和更清晰的代码.

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性能调整

由于DBA与SQL Server 2016的使用很可能意识到,版本标志着重要的转变 默认和兼容性管理. As a major version, it, of course, comes with new query optimizations, but control over whether they’re used is now streamlined via sys.databases.compatibility_level.

SQL.性能调整(在办公室)

SQL.数据库管理员(DBA)和开发人员经常会在数据和非数据相关问题上进行冲突。从我的经历中得出,这里有一些提示(双方)如何相处,有效地共同努力。

当DBA和开发人员必须有效地一起工作时,SQL性能调整超出了CodeBase。

开发人员的数据库优化:

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

  2. 即使您是Ninja SQL数据建模器,请询问DBA以帮助您的关系图。他们有很多才能分享和提供。

  3. DBA不喜欢快速变化。这是自然的:他们需要将数据库整体分析,并检查从所有角度的任何变化的影响。列中的简单变化可能需要一个星期的时间来实现 - 但这是因为错误可能会使公司造成巨大损失。要有耐心!

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

SQL. Server. DBA的数据库优化:

  1. 如果您不喜欢询问您的数据库的人,请给他们一个实时状态面板。 开发人员 总是怀疑数据库的状态,这样的面板可以节省每个人的时间和能量。

  2. 帮助开发人员在测试/质量保证环境中。可以轻松模拟具有简单测试的生产服务器对现实世界数据进行简单的测试。这将是他人和自己的重要时间。

  3. 开发人员在具有经常更改的业务逻辑的系统上度过。尝试了解这个世界更加灵活,并且能够在一个关键时刻打破一些规则。

  4. SQL.数据库进化。当您必须将数据迁移到新版本时,将会到期。开发人员使用每个新版本计算出显着的新功能。而不是拒绝接受他们的变化,提前计划并准备迁移。

理解基础知识

什么是DBMS中的查询处理?

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

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

查询优化是当开发人员或数据库引擎更改查询时,即SQL Server能够更有效地返回相同的结果。有时它是一个简单的存在()而不是count(),但其他次需要用不同的方法重写查询。

SQL. Server.中的性能调整是什么?

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

在SQL中使用索引是什么?

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

为什么存在()比count()更快?

存在()在找到匹配的行后立即停止处理,而COUNT()必须计算每行,无论您是否实际需要该详细信息,那么。