开发人员的 DBA 技能

• 下载 SQL_Scripts.zip - 2.6 KB

引言

在许多小型公司或部门中，没有专职的 DBA。在这种情况下，就需要办公室里的*某个人*来掌握数据库服务器这个“黑盒子”，让它保持运行，并在出现问题时进行处理。本文的目的是为非数据专业开发的开发者提供一个有用的信息和知识的快速回顾，使他们能够解决一些日常问题，并为一些问题做好提前准备。本文并非一篇深入的文章，而是一篇可以在咖啡休息时间快速阅读的文章，能为读者提供一些有用的起点。如果经验丰富的 DBA 愿意发表评论并帮助扩展列表，我将非常高兴。本文专门针对 MS SQL Server，但其中的概念应该适用于大多数关系数据库系统。我还附带了一些我用于收集系统信息的脚本下载，希望能对您有所帮助。 

 

核心关注点

许多项目从小处开始，随着时间的推移，复杂性会增加。当初数据库或系统设计中一个好的决定，现在可能已经不再是最佳选择。为了加快开发速度和“快速上线”，人们常常会绕过问题进行编码，而不去深入挖掘问题根源并加以解决。以下几点将帮助新的开发者 DBA 快速掌握并保持对事物的了解。

开发者 DBA 的三个核心关注点应该是系统稳定性、数据完整性/质量和速度。在此前提下，我们将探讨一些可用于这些领域的选项。

了解数据库

您需要了解数据库的一个关键方面是它的用途和架构。希望团队中有可以为您进行引导介绍的人，如果没有（或者无论如何！），您可以使用许多商业和免费的工具（我通常至少会同时使用 Visual Studio 和 MS SQL Management Studio 作为工具包）。首先，以下是一些快速掌握情况的方法：

（1）确认有良好的备份和恢复系统
   - 显然，没有数据库就没有 DBA 的工作要做了！... 我总是采取的最重要的第一步是确保有一个可靠的备份系统，并且已对数据库的恢复进行了妥善的测试。

（2）确定数据在哪里/哪些表数据最多/数据更新速度有多快
   - 我通常会运行一系列脚本来获取有关数据库状态的信息，并利用这些信息来了解情况，以及指出我需要进一步调查或询问的任何领域。找出哪些表包含的数据最多，哪些表的数据更改最频繁，哪些表存在潜在的索引问题/索引占用的空间大于实际数据等。

（3）找出表之间的关系
   - 检查数据表之间的链接可以洞察数据库的设计、某些表的重要性以及依赖关系。

（4）找出数据的输入/输出方式（数据采集/报告）。
   - 数据可以通过各种方式进入系统，确定这些路径并寻找潜在的故障点、脆弱性以及可能随着时间推移对系统造成不当压力的任何因素。

（5）调查数据如何/为何从其原始状态被转换/更改。
   - 有些系统接收数据后，会将其直接放入一系列表中和模型中，有些系统则会对其进行操作。找出这些转换区域在哪里以及它们的作用。经常在出现问题时，它们会提供线索。触发器是转换和“创造性事件”可能发生的地方之一。对一个表中的数据插入设置的触发器，可能会导致另一个看似不相关的表中的数据被删除或更新，原因可能是某个早已被遗忘的业务规则。如果您试图追踪是什么导致了更改，那么我稍后在文章中提到的 SQL-Search 工具将有所帮助。如果您专门寻找触发器，请在此处查看解决方案：链接。

（6）从第一天开始记录。
   - 如果您继承了一个未记录或记录很少的系统，那么请在进行过程中记录您的发现。这将在长期内让您更轻松，也将让后来者受益。

（7）使用内置报表。
- MS SQL Management Studio 附带了许多现成的系统报表，可以帮助您了解您正在处理的内容，并随着时间推移帮助您监控更改。通过选择数据库，右键单击，然后运行所需的报表来访问报表。
 

 

使其健壮

处理数据时编写健壮的代码至关重要。您应该了解的一些方面是事务、错误处理和日志。一旦我对数据库的结构和架构有了了解，我通常的下一步就是存储过程。如您所知，存储过程是预先编写的数据库脚本集合，可以按需调用。在 C# 中编写服务器端代码或在 JavaScript 中编写前端代码时，我们会小心谨慎地编写防御性代码，具备适当的错误处理，并在出现问题时进行适当的记录。数据库存储过程也一样，并且由于我们通常与用户的交互比网站的前端或 WinForms 应用程序的错误对话框少，我认为我们需要在 SQL 代码中格外小心，以确保我们能够健壮而优雅地处理问题。我会在存储过程中寻找三个方面：

（1）如果合适，脚本是否包含在事务块中？... 我通常使用事务块，当我要处理的数据是一个“全有或全无”的情况时——也就是说，所有操作都必须完美完成，否则我们就需要回滚（字面意义上）到脚本运行之前的状态。

此屏幕截图显示了选择数据、更改，然后回滚，原始数据不受影响。

有关事务的更多信息，请在此处：链接
 

（2）当脚本因数据转换错误、数据不匹配或可用性等原因可能失败时，我们需要知道发生了什么。在这种情况下，我会像在 C# 等过程化语言中使用 Try/Catch 块一样使用它。下面的示例通过尝试插入一个已存在的冲突主键来强制引发错误。

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    -- log the error!
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

错误输出示例（针对插入问题）如下：

Severity: 16    
LineNum: 2    
ErrorMessage: Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.

有关 T-SQL 中 try/catch 的更多信息，请在此处：链接。Catch 很有用，因为它可以告诉你脚本出现问题的行号，提供错误消息，并指示问题的严重程度。有关 T-SQL 错误严重性代码的更多信息，请在此处：链接。

（3）当出现问题时，我们需要记录下来。您可以使用内置的 MS SQL 错误日志记录系统（raise error with log），但这需要您不一定拥有的权限（例如，托管服务器），所以大多数情况下，我有一个自己插入数据的错误日志表。

最后，当然，您可以（而且我大多数时候也这么做）将以上所有方法结合起来，以提高您在凌晨 3 点不接到电话的几率！ （是的，我经历过……）

此示例摘自MSDN，说明了这一点。

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

注意：与大多数安全带一样，使用错误处理、事务等是有成本的——在大多数情况下是可以接受的，但最好在实施前阅读相关内容并了解其影响。

 

性能问题

当数据库出现性能问题时，可能有多种因素在起作用。缓慢可能由大量数据、低效的处理/脚本、糟糕或缺失的索引，以及操作系统/硬件（包括原始功率和系统配置）引起。在深入研究之前，并且在查看了磁盘空间、CPU 等通用问题之后，我在数据库中的首选方法通常是索引——当一个精心放置的索引让查询焕发新生时，我总是很高兴。不过，您必须非常小心——添加索引并不总是解决方案，但它常常似乎是默认答案……警惕过多的索引，因为它们可能会导致性能问题——后面会有更多介绍。Pinal Dave (链接) 的网站上有一套很棒的脚本，可以帮助您找到冗余索引，并提醒您可能的缺失索引。请在此处下载 Pinal 的脚本：链接。还有另一个需要注意的细节，它可能因为数据类型的原因，导致速度和存储空间出现问题。那就是默认使用 nVarChar，而应该使用标准的 VarChar。如果您不打算在数据库中存储扩展字符集，那么通常不需要使用 nVarChar。项目开始时这可能看起来是个好主意，但在项目发展一段时间后，您可能会后悔——仔细考虑一下！本文附带的 SQL 脚本包中有一个脚本，可以帮助您将数据库中的所有 nVarchar 转换为 Varchar。

使用索引

（1）了解如何正确使用索引

我认为大多数读者都知道索引是什么，但我总是惊讶于我遇到的许多开发者没有充分利用它们来获得最佳结果。我强烈推荐一本由 Marcus Winand 撰写的优秀书籍《SQL Performance Explained》——举例来说，这里是他的一点见解（进行测试！)

问题：以下 SQL 从性能角度来看是好习惯还是坏习惯？

搜索 2012 年的所有行

CREATE INDEX tbl_idx ON tbl (date_column);
SELECT text, date_column
 FROM tbl
WHERE datepart(yyyy, date_column) = 2012;

答案：这是坏习惯。

原因：将表列包装在函数中会使索引对该查询失效。

为连续周期编写查询，作为显式范围条件

SELECT text, date_column
  FROM tbl
 WHERE date_column >= CAST('2012-01-01' AS DATE)
   AND date_column <  CAST('2013-01-01' AS DATE)

 

（2）使用 SQL Management Studio 索引提示

MS SQL Management Studio 中的索引提示功能是一个非常有用的工具，可以帮助识别可能加速操作的缺失索引。我构建了一个包含 10 万条记录的示例数据库，包含父/子虚拟数据，没有索引。当我们在这两个表之间运行简单的 JOIN 查询时，如果选择“显示执行计划”，Studio 将显示一个提示，表明它认为有缺失的索引。
 

 

深入研究

# 问题不在数据库时

在陷入深入分析之前，我总是沿着从数据查询到数据输出（这可能是屏幕、用户或报告打印）的整个管道进行检查。例如，在 Web 应用程序中，我发现数据传输良好……直到*到达服务器端 C# 代码*，那里才是瓶颈。让我们看一个我经常看到的例子。

假设我们有一个客户及其月度销售额之间的一对多关系。如果我们想获取一个月的客户销售列表，我们可能会在 SQL 中这样做：

Select C.CustomerName, S.TotalSaleValue
from Customers C
join Sales S on S.CustomerID = C.CustomerID
where (S.SalesDate >= X) and (S.SalesDate <= Y)

简洁明了（我们也可以使用“between”关键字来处理日期条件）。

然而，我在 C#、Java 等中看到过这种模式的变体。

DatabaseEntities ent = new DatabaseEntities();
var Customers = ent.Customers.all():
foreach(var customer in Customers)
{
   var Sales = ent.Sales.where(s => s.CustomerID == customer.CustomerID ....<etc> )
}

这在后端数据库中被翻译为：一个查询获取客户列表，然后为每个客户，另一个查询获取他们的个人销售列表。效率极低，当然也更慢。在这种情况下，补救措施是在 C# 中使用正确的 JOIN，从而生成一个单一的高效查询并发送到服务器。

这里的重点是，我们不应总是假设数据库缓慢的问题就出在数据库本身——可能有理由认为问题出在其他地方——我们的工作就是排除这些可能性。
 

# 当提取数据本身花费太长时间时

（1）预处理/反规范化

有时，添加索引和优化查询只能做到一定程度。问题在于数据可能经过了高度规范化，从大量充满深层数据的不同位置提取数据需要花费大量时间。在这种情况下，最佳选择可能是“预处理”数据或对其进行反规范化。例如，如果您的查询提供的数据用于实时数据透视表，而该表当前由一系列复杂的 JOIN/视图/存储过程提供，那么一个好的解决方案可能是创建一个存储过程，在夜间收集所需数据，并将其放入一个宽的反规范化表中，该表可以被数据透视表数据提供程序瞬间读取。这种概念是数据仓库和数据市场的基础。这种策略可以非常有效地用于汇总型报表/仪表板，如季度销售、存档指标等。关于预处理的最后一点是，为了速度，您不仅可以通过预处理数据来节省宝贵的时间，还可以通过格式来节省。我的意思是，如果您使用 JSON 通过网络将数据发送到网页或移动应用程序，那么为什么不准备好以其 JSON 结构存储数据呢？在数据收集后，真的有必要在请求时即时转换它吗？... 例如，我曾非常有效地使用 MongoDB，通过在夜间或当离散数据更改时将 SQL 数据馈送给它，仅用于为基于 Web 的报表预处理本地 JSON 格式的数据。

（2）立即执行，而不是等待……

有些类型的存储过程需要花费时间才能运行，而且您对此无能为力。但是，如果您从 .net 客户端调用这些过程并*等待*……您可能会遇到超时异常。就用户而言，系统已损坏。就数据库而言，它仍在正常工作。在这种情况下，最好启动存储过程并轮询查看它是否已完成，而不是一直等待。这种情况可能发生在脚本设计时运行很快，但随着时间推移和数据增加，运行时间越来越长的系统中。当然，您需要弄清楚这一点，但在此期间，您可以使用 SqlCommand.BeginExecuteNonQuery() 方法。这会在后台线程中启动该过程，您的代码可以继续执行其他任务，而不会惊吓到用户！

 

导入数据

MS SQL Server 包含一些优秀的内置工具，可用于导入数据，无论是文本文件、数据库文件、CSV 还是 XML。这个主题可能非常广泛，所以我写了一篇单独的文章，关于如何将 CSV 和 XML 数据导入 MS SQL Server：链接。

 

工具

（1）DBCC

'数据库控制台命令' (DBCC) 是一个非常有用的工具。您可以使用它来检查数据库或表级别的数据完整性、重新索引表以及执行各种常规维护任务。以下是 Management Studio 查询窗口中的一些亮点：

DBCC CheckDB：检查数据库的逻辑和物理完整性。
DBCC CheckTable ('TableName')：检查表或视图的结构和页的完整性。
DBCC ShrinkDatabase ('DatabaseName')：如果可能，压缩数据库的物理大小。

（2）谁！？

有两个内置的系统存储过程非常有用。它们是 SP_WHO 及其较新的兄弟 SP_WHO2。这些脚本提供了有关当前 SQL 连接、用于连接到服务器的各种应用程序（以及这些连接）、连接正在执行的工作类型、是否有任何进程阻塞其他进程的执行等详细信息。当问题开始出现时，这是我获取当前情况快照的首选方法之一。

用法： exec sp_who2

输出提供了非常有用的见解，例如谁/什么已连接、他们正在消耗哪些资源、当前操作/状态，以及如果“BlkBy”有条目——这告诉你一个进程是否被另一个进程阻塞。

（3）SQL Server Profiler

此应用程序与 SQL Management Studio 一起捆绑，允许您对数据库进行实时跟踪。该应用程序中的过滤器允许您在必要时微调您想要观察的内容。它对于查看 EF/Linq C# 代码实际上生成的内容非常有用。

 

（4）RedGate SQL Search

这个免费工具是您可能每天都会使用的——非常值得下载，无需提供电子邮件地址！
它的功能很简单——输入搜索词，它会在您的数据库中查找，涵盖表、触发器、存储过程、索引等。您可以内联查看对象，并单击直接在对象资源管理器中跳转到它。#无价 #免费 #感谢RedGate！

 

（5）Brent Ozar

Brent Ozar 的好心人制作了一套免费脚本和电子书等，非常有用。脚本包括服务器/数据库健康检查、索引分析、找出导致最大性能问题的查询等实用工具。该包还包括一个有用的基本检查清单，用于在服务器宕机时进行排查。在此处获取：链接。
 

（6）服务器监控

通常，在客户致电之前就能预知问题总是一件好事。值得查看（请必应谷歌！）各种工具来监控您的服务器，并在检测到问题时向您发送电子邮件/短信。
 

总结

一旦拥有了正确的工具包和基础知识，掌握数据库并成为一名兼职数据管理员可以非常有益。希望本文中的笔记能帮助那些继承了（迷你）DBA 工作的开发人员顺利上手。我已将一些脚本附在文章中，您可能会觉得有用。

 

历史

2015 年 12 月 2 日 - 版本 1
2016 年 1 月 21 日 - 版本 2 - 添加了关于将数据导入 SQL Server 的独立文章链接
2016 年 1 月 28 日 - 版本 3 - 增加了关于索引的进一步说明，并在可下载附件中添加了新的 SQL 脚本实用工具。

2016 年 2 月 3 日 - 版本 4 - 添加了对内置系统报表的引用