Microsoft SQL 服务器的最佳实践，设计和开发准则

Madhu K Nair, 2008-07-24（著）

Charles Han，2009-5-6（译）

译者序

一般我们开发的应用程序中基于数据库的占很大一部分。尽管在开发过程和测试过程中，我们编写的数据库访问/操作代码的运行性能表现非常良好。但是，当它成为产品推广给客户后可能经过逐年类月地数据累计后必然会引起应用程序的性能问题，简单说就是运行速度变得越来越慢。之后我们这些可怜的开发人员（包括本人）将会进入漫长而无聊，沉重而耗时的数据库优化过程。可能会根据应用程序添加我们认为比较不错的索引结构来改善数据库性能。这可能非常有效，而且在短时间内会显著提高数据库性能。但是再过几个月又会如何呢？必然还会变得很慢。那我们应该怎么走？往哪儿走？在这种疑惑中可以说很多开发人员痛不欲生。究其缘由是我们的数据访问/操作代码不规范引起的。其实规范的数据库操作代码比起再优秀的索引结构带来更好的性能体现。本文中主要围绕对SQL服务器的最佳实践，设计准则和数据库设计和开发时的通用准则进行探讨，希望能给开发人员带来帮助。同时我在这里声明一点：不要畏惧数据库访问/操作代码的重构。看似工程庞大但是比起性能慢而受到客户的白眼，狠下心花费几个月时间去适用下面的最佳实践或准则，这将会尽可能的消除一年或几年时间来做没法被客户讨好的事情。

前言

虽然网络上有很多可用的资源，但是我在这里将会针对SQL服务器的最佳实践，设计准则和数据库设计和开发时通用准则给一一列出来。

最佳实践

1.  使用存储过程：好处在于：

(a)  提高代码重用性。

(b)  访问控制：你可以对存储控制访问权限。

(c)  提高执行计划的重用性：虽然临时的查询也可以创建和重用执行计划，但只限于当查询内容和数据类型与上一次查询相同。不同的数据类型和查询中的额外空格都会引起新的执行计划被创建。
例：

Select  Sal from Employee where sal=$10  --Money

--And

Select  Sal from Employee where sal=10  -- Int

上述两个语句就会生成不同的执行计划，这是因为查询条件的数据类型不一致所致。

(d)  防止SQL注入攻击。

(e)  存储过程会带来更多的可读性和可控性。

2.  使用数据库对象的完整的限定名：这是非常有意义的。你必须在引用任何数据库对象时使用完整的限定名，如Ie. SchemaName.ObjectName。因为，当查询引擎在准备执行计划时，在绑定过程中往往查询引擎得判断对象是否存在。如果你声明完整的限定名则对查询引擎来说对象的判定变得方便同时易读性也会提高很多。

3.  避免在SELECT语句使用中标量函数：最近我也面对过此问题也强调过。一般来说禁止在返回大数据量的查询中使用标量函数，因为在返回大数据量的查询中标量函数运行效果就像游标一样，所以更改标量函数为内联或表值函数或视图。

4.  避免在存储过程中混合使用DML和声明临时表的DDL：这是非常重要。当你在同一个存储过程中创建一个临时表（#table）后随即对相同的标进行更改，这种DDL和DML混合使用引起存储过程被重新编译。所以，如果有一存储过程如果在每次调用中经常被重新编译，就检查是否混合使用。

5.  查询只需要的字段：在查询语句中尽量查询只需要的字段。SELECT *的使用可能造成不使用数据表中的可用索引。而且如果你查询更多的数据则说明你正在产生更多的I/O。简言之我们的目的就是减少I/O。

6.  避免使用提示：提示会妨碍查询引擎的自动优化功能。你或许发现在特定的环境中提示会给你带来更好的性能，但是当数据量在增加或环境在变化时，性能会有所不同。对提示的信息你可以参照下面的网站：http://msdn.microsoft.com/zh-cn/library/ms187713.aspx

7.  尽可能使用表变量替换临时表：你或许在存储过程中使用表变量或临时表来作为中间储存，但避免用表变量中临时储存大量数据。虽然表变量和临时表中各有优点和缺点，但通常是如果数据量比较少那么就应该使用表变量。

8.  使用SET NOCOUNT ON语句：基本上，你必须减少在网络中的数据传输量。一般数据库引擎都会像客户端反馈每个语句所影响的行数，其实这个是不必要的信息。我们可以使用SET NOCOUNT ON来避免这些反馈。此句应该在所有的存储过程中声明。

9.  在连接时不要变更SET 选项：如果在连接或其它地方更改SET选项会很可能引起存储过程的重编译。相应的信息参照下面连接：http://technet.microsoft.com/zh-cn/library/cc966425(en-us).aspx

10. EXISTS 对 IN： IN操作可以轻易的被更为相联查询中更加被优化了的EXISTS所替换。但是你会发现IN在校数据量表中的表现更好。

11. 保持事物处理尽可能短暂执行：死锁通常是不规范查询的产物。你必须保持食物处理尽可能短暂执行使避免死锁。同时也要在存储过程中以相同排序规则来引用对象。

12. 避免在事务处理中间用户输入：在事务处理中间不要接受任何用户输入。

13. 避免在数据库中作前段工作：字符串处理，排序和转换等这些操作可以很轻松的在客户端中完成，所以你应该避免在数据库中作此类操作。这是因为，你应该对数据层，数据访问层（DAL）以及业务逻辑层之间的分工一定要明确。比如你可以很容易在客户端对行数进行计数，但如果在数据库中实现将会使用可以避免的函数或额外的连接。

14. 避免在SELECT语句中使用函数：在查询中使用任何函数比如CONVERT，CAST，ISNULL等，可能会忽略数据表中可用的索引。

15. 不要使用EXEC(‘STRING’)，使用 sp_executesql：如果可以的话你应该试着避免使用动态SQL。如果没有任何其他选择，则用sp_executesql不要用EXEC(‘STRING’)。这是因为EXEC语句容易受到SQL注入攻击而且也不是可以重用执行计划的参数化查询。

16. 使用适当大小的输入参数：这也是避免SQL注入攻击的步骤之一，而且也会减少内存空间的使用。

17. 不要使用sp_前缀来给存储过程命名：命名规则是很重要的。不要使用SP_前缀来给存粗过程来命名（比如：sp_some_sp_name）。因为此命名规则是被SQL服务器的系统存储过程所使用。

18. 尽可能使用WHERE条件：基本上，你应该在查询中减少读取的行数。

19. 避免不操作：避免使用<>,NOT IN NOT EXISTS等操作，这是因为它会引起表扫描。查询引擎只能到最后一行数据扫描完成后才能保证没有符合的数据。

20. 避免游标遍历：在集成化操作中，通常遍历操作是可以避免的。

21. 避免使用LIKE ‘% %’：如果你在查询值域的两边使用%，则查询会引起表扫描。这是应该避免的。如果应用程序需要很多文本搜索最好是使用全文索引。

22. 不要使用WITH Recompile：使用WITH Recompile会在每次调用存储过程的时候引起重新编译。你必须避免使用此命令。

23. 链接重要性：当你链接两个数据表的时候注意下面几点

(a)  避免使用不操作（<>, NOT IN）。

(b)  避免LIKE操作。

设计准则

1.  创建Covering Indexes：创建Covering Indexes。Covering Index会在它的叶结点中包含所有查询所需要的索引。Covering 包含所有在使用SELECT，WHERE，ORDER BY，JOIN等语句的字段。

例：

Select Col1,Col2 From YourTableName Where Col3=1 Order by Col4.

在上面提到的查询中的Covering Index是Col1 + Col2 + Col3 + Col4。（注：在创建索引时最有可能查询得到的字段应该设为索引的第一个字段。）

2.  移除无用的索引：在SQL Server 2005中可以很方便的查找不用的索引。数据表中的索引太多或太少两者都是不好的。如果在数据表中存在无用/不用的索引的话插入/更新语句会暂用更多的性能，同时我们都知道索引也是要占用空间。

3.  创建索引时将最常查询到的字段设为索引的第一个字段：索引应该在适当的分析后才可以创建。通常你必须在创建索引时把最常查询到的字段设为索引的第一个字段。

4.  格式化存储过程和查询：你必须拥有每个对象（存储过程/函数/视图）的固定格式或模板，而且所有人（开发组的所有人）应该遵循定义的固定格式。同样如果查询语句拥有很好的格式的话它的可读性就会很高。

5.  在定向插入的表中使用标识列作为聚集索引以避免页面被拆分：这是属于设计和数据建模范畴。如果你有产生很多插入数据的数据表（类似日志表），那就必须使用标识列（不断增加）作为聚集索引。这样有助于消除页面被拆分。虽然我还没有碰到过，但可能会引起页面热点问题（即所有的事务都去争夺数据表的部分页面）。

6.  在索引中使用合适的fillfactor属性：这是避免页面被拆分举措中非常重要的。通常事务用数据表可以保持80-90的fillfactor属性值。

7.  平衡规范化与非规范化：你必须在规范化和非规范化之间有一个折衷处理。在数据冗余的成本上，非规范化可以给你带来更好的性能。

8.  主键的大小和联合主键：你必须减少主键的大小。这是因为，在关系数据库中，你可能在其他数据表中使用外键来参照其主键。如果正好你的主键包含多列（联合主键）或主键的大小太大，则你在有意识或无意识的增加空间的使用。如果联合主键包含多于3列的话你最好使用代用键比如标识列作为主键。

9.  不要修改系统对象：如果你的应用程序需要调整一些系统对象那你就有麻烦了。系统对象的结构只能由微软以各种版本和补丁的形式更改，所以应避免任何建模。

数据类型的选择准则

作为数据库架构师，我确信当设计数据表的时候选用适当的数据类型的重要性。如果你先作适当的数据分析后再选择相应的数据类型，你就能控制数据行、页面、数据表的大小从而全面提高数据库的性能。你在设计数据表的时候应考虑下面几点：

1.  倘若你的数据库能支持基于WEB的应用的话最好使用UNICODE，这样便于应用程序的可扩展性。（UNICODE（NCHAR，NVARCHAR）的每个字符占用2字节而ASCII（CHAR，VARCHAR）则占用1字节。）

2.  如果你的应用程序支持多语言则应使用UNICODE。

3.  如果你计划在数据库中包含CLR数据类型（SQL Server 2005）则应使用UNICODE数据类型。因为，如果CLR数据类型是要消耗数据的那就只能是UNICODE。

4.  如果是数值列，应分析一下其列的值域范围后选用相应的数据类型。比如，你有一张以部门编号为主键的部门数据表，而且你知道最多数据行数为20到30行。在这种情况下建议为其字段选择TINYINT数据类型。通常从数据储存观点上，不建议在没有任何对字段的值域范围的分析过程下在数值字段中一直使用INT数据类型。

5.  说明/评价/备注等字段可能在所有数据行中或有或没有数据。像这种字段还是使用变量型数据类型为好（比如VARCHAR或NVARCHAR）。

6.  如果你确信某一字段不得为空值，同时包含或多或少几乎相同大小的数据，则使用固定数据类型比如CHAR或NCHAR。值得一提的是这是非常重要。如果你使用固定数据类型且字段可以为空，则即使没有任何数据（即为NULL）其字段照样消耗空间。

7.  如果其字段长度小于20个字符，使用固定长度数据类型，如NCHAR或CHAR。

8.  我看到有很多应用程序尽管货币数据类型支持应用程序所需要的数据精度但却使用Decimal来保存货币类数据。在这里我的观点是，如果你只需要4位精度数据，就使用货币数据类型。

9.  只有在不可避免时使用唯一标识字段作为主键和聚合索引。因为唯一标识字段需要16字节的空间。

通用准则

1.  编写ANSI标准代码：你必须编写作为评估你的应用程序的标准化代码。这样迁移到下一个版本就不会发生问题。不要使用不推荐的功能。比如，有一个DBCC命令来重建索引，但是在SQL Server 2005中为了标准化，可以使用ALTER INDEX命令做同样的事情。

2.  不要将包含架构的错误信息输出到你的前端：我看到有些人给出非常详细的错误信息，比如“在那个那个数据库中的那个那个数据表中没有这些数据”等，这种特别详细的消息会引起黑客注意。

3.  向应用程序适用适当的必须的隔离级别：这是非常有意义的。使用任意隔离级别之前，你必须清楚其中的含义。所有应用程序不能承受READ UNCOMMITTED隔离级别，这是因为它可能导致数据不一致的问题，比如脏读，幻读，更新丢失等。WITH NOLOCK其实只不过是声明使用READ UNCOMMITTED隔离级别罢了。

4.  在源代码管理中保存数据库对象脚本：我们都意识到此类问题但通常会被忽略。这会在容错和管理多名开发人员在同一个项目中开发中是很重要的一点。

Last edited Aug 4 2008 at 7:27 PM by madhuottapalam, version 10

[原文] ：http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Best practices , Design and Development guidelines for Microsoft SQL Server&referringTitle=Home