在前面我们介绍了如何正确使用索引，调整索引是见效最快的性能调优方法，但一般而言，调整索引只会提高查询性能。除此之外，我们还可以调整数据访问代码和TSQL，本文就介绍如何以最优的方法重构数据访问代码和TSQL。

　　第四步：将TSQL代码从应用程序迁移到数据库中

　　也许你不喜欢我的这个建议，你或你的团队可能已经有一个默认的潜规则，那就是使用ORM(Object Relational Mapping，即对象关系映射)生成所有SQL，并将SQL放在应用程序中，但如果你要优化数据访问性能，或需要调试应用程序性能问题，我建议你将SQL代码移植到数据库上(使用存储过程，视图，函数和触发器)，原因如下：

　　1、使用存储过程，视图，函数和触发器实现应用程序中SQL代码的功能有助于减少应用程序中SQL复制的弊端，因为现在只在一个地方集中处理SQL，为以后的代码复用打下了良好的基础。

　　2、使用数据库对象实现所有的TSQL有助于分析TSQL的性能问题，同时有助于你集中管理TSQL代码。

　　3、将TS QL移植到数据库上去后，可以更好地重构TSQL代码，以利用数据库的高级索引特性。此外，应用程序中没了SQL代码也将更加简洁。

　　虽然这一步可能不会象前三步那样立竿见影，但做这一步的主要目的是为后面的优化步骤打下基础。如果在你的应用程序中使用ORM(如NHibernate)实现了数据访问例行程序，在测试或开发环境中你可能发现它们工作得很好，但在生产数据库上却可能遇到问题，这时你可能需要反思基于ORM的数据访问逻辑，利用TSQL对象实现数据访问例行程序是一种好办法，这样做有更多的机会从数据库角度来优化性能。

　　我向你保证，如果你花1-2人月来完成迁移，那以后肯定不止节约1-2人年的的成本。

　　OK!假设你已经照我的做的了，完全将TSQL迁移到数据库上去了，下面就进入正题吧!

　　第五步：识别低效TSQL，采用最佳实践重构和应用TSQL

　　由于每个程序员的能力和习惯都不一样，他们编写的TSQL可能风格各异，部分代码可能不是最佳实现，对于水平一般的程序员可能首先想到的是编写TSQL实现需求，至于性能问题日后再说，因此在开发和测试时可能发现不了问题。

　　也有一些人知道最佳实践，但在编写代码时由于种种原因没有采用最佳实践，等到用户发飙的那天才乖乖地重新埋头思考最佳实践。

　　我觉得还是有必要介绍一下具有都有哪些最佳实践。

　　1、在查询中不要使用“select *”

　　(1)检索不必要的列会带来额外的系统开销，有句话叫做“该省的则省”;

　　(2)数据库不能利用“覆盖索引”的优点，因此查询缓慢。

　　2、在select清单中避免不必要的列，在连接条件中避免不必要的表

　　(1)在select查询中如有不必要的列，会带来额外的系统开销，特别是LOB类型的列;

　　(2)在连接条件中包含不必要的表会强制数据库引擎检索和匹配不需要的数据，增加了查询执行时间。

　　3、不要在子查询中使用count()求和执行存在性检查

　　(1)不要使用

　　使用

　　代替;

　　(2)当你使用count()时，SQL Server不知道你要做的是存在性检查，它会计算所有匹配的值，要么会执行全表扫描，要么会扫描最小的非聚集索引;

　　(3)当你使用EXISTS时，SQL Server知道你要执行存在性检查，当它发现第一个匹配的值时，就会返回TRUE，并停止查询。类似的应用还有使用IN或ANY代替count()。

　　4、避免使用两个不同类型的列进行表的连接

　　(1)当连接两个不同类型的列时，其中一个列必须转换成另一个列的类型，级别低的会被转换成高级别的类型，转换操作会消耗一定的系统资源;

　　(2)如果你使用两个不同类型的列来连接表，其中一个列原本可以使用索引，但经过转换后，优化器就不会使用它的索引了。例如：　

　　在这个例子中，SQL Server会将int列转换为float类型，因为int比float类型的级别低，large_table.int_column上的索引就不会被使用，但smalltable.float_column上的索引可以正常使用。

　　5、避免死锁

　　(1)在你的存储过程和触发器中访问同一个表时总是以相同的顺序;

　　(2)事务应经可能地缩短，在一个事务中应尽可能减少涉及到的数据量;

　　(3)永远不要在事务中等待用户输入。

　　6、使用“基于规则的方法”而不是使用“程序化方法”编写TSQL

　　(1)数据库引擎专门为基于规则的SQL进行了优化，因此处理大型结果集时应尽量避免使用程序化的方法(使用游标或UDF[User Defined Functions]处理返回的结果集) ;

　　(2)如何摆脱程序化的SQL呢?有以下方法：

　　- 使用内联子查询替换用户定义函数;

　　- 使用相关联的子查询替换基于游标的代码;

　　- 如果确实需要程序化代码，至少应该使用表变量代替游标导航和处理结果集。

　　7、避免使用count(*)获得表的记录数

　　(1)为了获得表中的记录数，我们通常使用下面的SQL语句：

　　这条语句会执行全表扫描才能获得行数。

　　(2)但下面的SQL语句不会执行全表扫描一样可以获得行数：

　　8、避免使用动态SQL

　　除非迫不得已，应尽量避免使用动态SQL，因为：

　　(1)动态SQL难以调试和故障诊断;

　　(2)如果用户向动态SQL提供了输入，那么可能存在SQL注入风险。

　　9、避免使用临时表

　　(1)除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;

　　(2)大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。

　　10、使用全文搜索搜索文本数据，取代like搜索

　　全文搜索始终优于like搜索：

　　(1)全文搜索让你可以实现like不能完成的复杂搜索，如搜索一个单词或一个短语，搜索一个与另一个单词或短语相近的单词或短语，或者是搜索同义词;

　　(2)实现全文搜索比实现like搜索更容易(特别是复杂的搜索);

　　11、使用union实现or操作

　　(1)在查询中尽量不要使用or，使用union合并两个不同的查询结果集，这样查询性能会更好;

　　(2)如果不是必须要不同的结果集，使用union all效果会更好，因为它不会对结果集排序。

　　12、为大对象使用延迟加载策略

　　(1)在不同的表中存储大对象(如VARCHAR(MAX)，Image，Text等)，然后在主表中存储这些大对象的引用;

　　(2)在查询中检索所有主表数据，如果需要载入大对象，按需从大对象表中检索大对象。

　　13、使用VARCHAR(MAX)，VARBINARY(MAX) 和 NVARCHAR(MAX)

　　(1)在SQL Server 2000中，一行的大小不能超过800字节，这是受SQL Server内部页面大小8KB的限制造成的，为了在单列中存储更多的数据，你需要使用TEXT，NTEXT或IMAGE数据类型(BLOB);

　　(2)这些和存储在相同表中的其它数据不一样，这些页面以B-Tree结构排列，这些数据不能作为存储过程或函数中的变量，也不能用于字符串函数，如REPLACE，CHARINDEX或SUBSTRING，大多数时候你必须使用READTEXT，WRITETEXT和UPDATETEXT;

　　(3)为了解决这个问题，在SQL Server 2005中增加了VARCHAR(MAX)，VARBINARY(MAX) 和 NVARCHAR(MAX)，这些数据类型可以容纳和BLOB相同数量的数据(2GB)，和其它数据类型使用相同的数据页;

　　(4)当MAX数据类型中的数据超过8KB时，使用溢出页(在ROW_OVERFLOW分配单元中)指向源数据页，源数据页仍然在IN_ROW分配单元中。

　　14、在用户定义函数中使用下列最佳实践

　　不要在你的存储过程，触发器，函数和批处理中重复调用函数，例如，在许多时候，你需要获得字符串变量的长度，无论如何都不要重复调用LEN函数，只调用一次即可，将结果存储在一个变量中，以后就可以直接使用了。
 

 

上一篇：最佳数据仓库体系--Hadoop

下一篇：已经是最后一篇