开发人员需要具备的DBA技术

背景

在一些小公司或者部门里，通常很少有专门的DBA职位。这时候就需要我们这些程序员充当业余DBA的作用，去监测和维护数据库性能。本文的目的是帮助非DBA专业的开发人员如何定位和解决日常出现数据库问题，并提供一些相关有用的工具。

大部分项目一开始的数据库都是很小的，但随着时间的推进，数据库变得越来越庞大，访问性能也越来越慢。因此优化数据库在所难免，数据库优化的三大核心：保持系统稳定、确保数据完整性和高质量、性能。

了解你的数据库

在开始优化数据库的时候，我们必须要了解自己所使用的数据库的结构和用途以及如下一些基本信息。

1. 首先，平时要进行适度的备份和还原数据库，对备份的数据进行检测，可以在不影响当前数据库的情况下，进行一些输入性质的检测操作。
2. 区分数据来源、哪张表拥有最多数据、运行速度。运行一些脚本去了解你的数据库。哪些表经常变化、哪些表需要建索引
3. 找出表之间的关系依赖
4. 找出数据是如何进来以及何时失效的
5. 调查数据如何以及为什么要进行状态的转换
6. 做记录
7. 使用内置的数据库报表

　　

如何让你的数据库更健壮

当与数据交道时，健壮的代码至关重要。需要注意的是事务、error捕获和日志的使用。当设计数据库结构和架构时，都会面临存储过程的选择。存储过程是预先写好的数据库脚本，能够被命令取消。当使用C#或者JavaScript前端写代码时，允许某些地方使用错误的处理和日志记录哪些错误操作。但存储过程就不一样了，因为相比于网站前端，它很少与用户有交互。在使用存储过程时，有三点需要留意：

1. 如果允许，请把脚本写于事务块里面

2. 使用try...catch...监测捕获错误信息

3. 记录错误信息

当错误捕获后，需要记录，你可以使用MS SQL内置的日志系统记录，但这又涉及到权限，可能你没有办法访问。你也可以创建一张日志表记录错误信息。

性能问题

通常我们都会遇到访问速度的问题。速度慢无非就是数据量过大、处理脚本执行效率低、索引创建不合理、硬件或者系统配置等问题。索引是解决性能的优先选择，但是如果拥有太多的索引就会造成性能问题。其次是字段的类型，尽量使用varchar替代nvarchar

使用索引

1. 正确使用索引

2. 使用SQL 索引提示

 

其他性能问题

1. 非数据库引起的性能问题

一般情况下，更多造成性能问题的是我们不规范和不合理的写法导致的。例如一下2个例子

用between语法比和比较来的快

通过foreach遍历循环查找单条数据，显然效率也会大打折扣。取而代之，可以使用join表关联来查找你要的结果

2. 查询复杂来源的数据消耗太多时间

a. 前期预加载和规范

一般情况下，添加索引、优化查询语句到目前为止能够很好的优化性能。还有一种情况，由于访问数据量很大，且这些大数据是从其他数据量也很大的地方集合过来的，因此它消耗的时间就很长。最好的方式就是预先读取这些数据或者去规范化这些数据。如果你的查询来源是一个pivot表，那么他可能来自一系列的关联数据、视图、存储过程，好的解决方式就是创建一个新的存储过程去搜集这些需要的数据，然后把他们存入一个更规范的数据表中方便阅读和查询。先规范化你的数据，也能够帮你省去很多宝贵的实践，不仅仅在加载数据前，也包括格式排版。比如你访问的数据需要整理成JSON格式的时候，你无需在访问的时候才做格式化操作，完全可以预先加载这些数据整理成JSON格式。

b. 多线程执行存储过程

有时候一些存储过程确实需要花费一些时间去执行，而这些执行过程并不是你所关心的。当你访问这些存储过程并等待时，就可能会面临超时的错误出现。解决方式，就是开始执行存储过程的时候，去检查他是否已经执行完毕而不是卡着线程等待它的结果。这些存储过程通常发生在一个脚本本来执行很快，但随着数据量的增加，他消耗的时间越来越久导致超时。你需要做的就是使用SqlCommand.BeginExecuteNonQuery()方法。这个方法在后台线程执行存储过程，而不影响你当前线程执行其他事情，无需等待。

 

如何导入数据

数据的导入也是开发人员需要具备的技能。MS SQL提供了内置的导入功能，可以通过text文件\数据库文件、csv或者xml。这里不做细聊

推荐常用的工具

1. DBCC

Database console commands 能够检查数据库级别或者表级别数据的完整性，重建索引的表，执行一系列的维护工作。

DBCC CheckDB: 检查数据库的逻辑和物理的完整性

DBCC CheckTable(‘TableName’) :检查表或者视图的结构完整性

DBCC ShrinkDatabase(‘DatabaseName’): 压缩数据库的物理大小

2. SP_WHO和SP_WHO2

有2个内置的系统存储过程比较有用。SP_WHO和SP_WHO2.这两个脚本可以提供当前sql连接的详细信息，包括连接该数据库的不同应用、连接类型、进程锁定的详细信息。

exec sp_who2的用法

下面2张图可以清晰地看出谁连接了数据库、当前的活动和状态，BlkBy代表进程被谁锁定了

3. SQL Server Profiler的使用

Sql Server Profiler可以实时监测你的数据库，既可以跟踪执行过的语句，也可以查看EF/Linq的语法生成后的sql语句。

4. RedGate SQL Search

安装后sql会有提示功能，可以快速丁文你的数据库、表、触发器、存储过程、索引等。相当好用

5. Brent Ozar

包含一些脚本文件，可以检测数据库的性能、索引分析、计算出最影响查询性能的地方。它还包括一些有用的检查清单。

6. Server monitor

数据库服务器的监控，在发生问题时，会抢先一步客户通过邮件通知你。

绝大部分资料来源：http://www.codeproject.com/Articles/1060867/DBA-Skills-for-developers