数据库设计和编码规范分享 .pdf

《数据库设计和编码规范分享 .pdf》由会员分享，可在线阅读，更多相关《数据库设计和编码规范分享 .pdf（37页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库设计和编码规范Version 1.0名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 37 页 -目录1简介.1.1读者对象 .1.2目的.2数据库命名规范 .2.1规范总体要求.2.2数据库对象命名规范.2.3变量命名规范.3数据库设计规范 .3.1选择有效的设计工具.3.2表的设计 .3.2.1遵守范式要求.3.2.2字段设计 .3.2.3适当的合理的冗余.3.2.4注意大类型的字段设计.3.3表关系和约束设计.3.3.1主键设计 .3.3.2 外键设计 .3.3.3 检查约束 .3.4索引的设计 .3.4.1聚集索引和非聚集索引.3.4.2索引的初始创建原则.3.4.3

2、索引的注意事项.3.4.4索引的后期维护工作.3.5物理存储设计.3.5.1日志文件另外存放.3.5.2存储空间的设计.4T-SQL 编码规范 .4.1书写基本规范.4.2使用可搜索参数（WHERE使用原则）.4.3少用触发器和禁用游标.4.4联合查询尽可能使用UNION ALL.4.5尽可能避免的地方.4.6避免返回和使用多余的数据.4.7操作符优化 .4.8数据库事务处理原则.4.9最少次数的访问表.4.10避免隐含的数据类型转换.名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 37 页 -4.11表变量、临时表和公用表达式的用法.4.12正确地判断记录是否存在.4.13注意

3、自定义标量函数的影响.4.14避免编写复杂的TSQL 语句 .4.15应用程序层防止执行大块的TSQL 语句 .4.16对数据库大表的处理方案.4.17SP_EXECUTESQL代替 EXEC.4.18存储过程的一些建议.5如何进行质量控制 .5.1规范的制定、认可和实施.5.2讨论和检查工作.5.3对制定的规范不断完善.5.4讨论和制定公共模板.5.4.1SELECT 语句.5.4.2JOIN 语句.5.4.3子查询 .5.4.4INSERT 语句 .5.4.5UPDATE 语句.5.4.6DELETE 语句 .5.4.7CASE 语句.5.4.8IF 语句.5.4.9WHILE 语句 .E

4、XISTS 语句.变量声明 .变量赋值 .创建表及约束索引.存储过程 .带输出参数的存储过程.视图 .物化视图 .自定义标量函数.自定义表值函数（多语句）.自定义表值函数（内联）.索引整理 .数据库事务格式.名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 37 页 -1简介1.1 读者对象此文档说明书供开发部全体成员阅读。1.2 目的一个合理的数据库结构设计是保证系统性能的基础。一个好的规范让新手容易进入状态且少犯错，保持团队支持顺畅，系统长久使用后不至于紊乱，让管理者易于在众多对象中，获取所需或理清问题。同时，定义标准程序也需要团队合作，讨论出大家愿意遵循的规范。随着时间演进，

5、还需要逐步校订与修改规范，让团队运行更为顺畅。2数据库命名规范团队开发与管理信息系统讲究默契，而制定服务器、数据库对象、变量等命名规则是建立默契的基本。命名规则是让所有的数据库用户，如数据库管理员、程序设计人员和程序开发人员，可以直观地辨识对象用途。而命名规则大都约定俗成，可以依照公司文化、团队习惯修改并落实。2.1 规范总体要求1.避免使用系统产品本身的惯例，让用户混淆自定义对象和系统对象或关键词。例如，存储过程不要以 sp_或 xp_开头，因为SQL SERVER 的系统存储过程以sp_开头，扩展存储过程以xp_开头。2.不要使用空白符号、运算符号、中文字、关键词来命名对象。名师资料总结-

6、精品资料欢迎下载-名师精心整理-第 4 页，共 37 页 -3.名称不宜过于简略，要让对象的用途直观易懂，但也不宜过长，造成使用不方便。4.不用为数据表内字段名称加上数据类型的缩写。5.名称中最好不要包括中划线。6.禁止使用 拼音+英语 的方式来命名数据库对象或变量。2.2 数据库对象命名规范我们约定，数据库对象包括表、视图（查询）、存储过程（参数查询）、函数、约束。对象名字由前缀和实际名字组成，长度不超过30。避免中文和保留关键字，做到简洁又有意义。前缀就是要求每种对象有固定的开头字符串，而开头字符串宜短且字数统一。可以讨论一下对各种对象的命名规范，通过后严格按照要求实施。例如：对象命名规范

7、数据库数据库名：项目英文名称+DB 数据文件：数据库名称+_Data.mdf 日志文件：数据库名称+_Log.ldf 表前缀 T+表名;单词首写字母为大写，其余全部小写。示范：TCustomer 表字段不需要前缀，直接用英文单词或缩写，单词首字母为大写，其余为小写。例如：UserName，如果是两个单词的首写字母缩写，统一用大写，比如:UserID 主键所在字段不要用 ID。一律用表名+ID(如果表名太长的话，采用缩写用各单词的首写字母组合)存储过程用 P_前缀+功能描述 (首单词大写，其余下写)例如：P_GetAllCorps 视图用前缀 V_+视图名称 例如：V_Account 自定义标量

8、函数前缀 F_+功能描述 (首单词大写，其余下写)例如：F_GetEWSourceName 自定义表值函数前缀 TF_+功能描述 (首单词大写，其余下写)主键PK_ 表名 例如：PK_TExAccount 外键用 FK_ 主表名 _字段表表示（考虑到名字会比较长，突出主表）例如：FK_TOrder_OrderID 默认值约束用 DF_表名 _ 字段名 表示例如：DF_TOrder_Type 名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 37 页 -检查约束用 CK_ 表名 _字段名 表示例如：CK_TCustomer_Mail 唯一性约束用 UQ_表名 _字段名 表示例如：UQ

9、_TCustomer_Code 聚集索引用 DX_ 表名 _字段名 表示例如：DX_TCachet_ID 其它索引用 IX_ 表名 _ 字段名 表示(字段名较多时，取前面两个即可)例如：IX_TCachet_CName_CorpID 2.3 变量命名规范1.数据列参数命名格式为+列名称。示例：EmployeeID employee_id 2.非数据列参数在参数无法跟列名称进行关联时，使用能够反映该参数功能的英文单词或单词组合,采用 Pascal 样式命名。示例：WorkType work_type 3数据库设计规范好的数据库架构设计对系统运行的性能起着很大的作用，所以要在开始时就要引起重视。为

10、了保证数据库设计的高效必须安排时间对设计结果进行评审，这一环节必不可少。3.1 选择有效的设计工具数据库设计工具：Power Designer、ER Studio、Rose、Microsoft Visio。项目开始前要确定使用哪种设计工具。(另有开发插件:RedGate系列(SQL Prompt)选择的工具要便于讨论便入生成脚本导入数据库。设计通过后要形成文档，并且这个结构设计文档要存档，签入VSS 基线库中。名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 37 页 -在进行数据库设计时，应随时进行数据字典的维护。(字段要求写说明)3.2 表的设计表设计在数据库设计中占据有十分重

11、要的地位。表是实际存储数据的对象。除了要注重表结构设计，字段的设计之外还要注意表之间关系的设计。3.2.1 遵守范式要求通常，合理的规范化会最小化数据异常和减少数据的冗余。为了更新数据的正确与快速，在设计的初始阶段多采用三范式 设计数据库表。第一范式强调的是列的原子性，即列不能够再分成其他几列。第二范式包含两层意思，一是表必须有一个主键；二是非主键列必须完全依赖于主键，且不能只依赖于主键的一部分。(尽量少使用复合主键)第三范式需要确保数据表中的所有非主键列直接与主键列相关，而不能直接依赖于非主键列。3.2.2 字段设计1.尽量避免可为空的列。虽然在个别情况下，允许空值可能是有用的，但是应尽量少

12、用。这是因为需要对它们进行特殊处理，从而会增加数据操作的复杂性和增加CPU额外的逻辑判断。很多情况下可以考虑用默认值0或空字符串()来代替 NULL 值。所以字段应该有NOT NULL 的限制。2.Unicode的选择。nvarchar和 nchar 相应比varchar和 char 要占用更多的存储空间。设计的原则是：如果确保存储的内容只是纯英文和数字，用char/varchar。如果含有中文字符或其它多国语言，用 nchar/nvarchar。3.字段长度要精确，遵守“必须、够用”的原则。名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 37 页 -精确的长度设计既能完整的描述

13、数据，又可以节省存储空间。积小成大，当数据表中的数据有很多记录的时候，这种存储空间的优势就能体现得十分明显。存储空间越紧凑，分配的页面就越少，在同样大小的内存空间中就可以存储更多的页面，这样操作数据的效率就会提高。例如能用 char(10)的就不要用 char(20),提高存储的利用率和系统性能，但同时也要兼顾扩展性和可移植性。字段类型存储空间补充说明bigint 8 字节-263(-9,223,372,036,854,775,808)到263-1(9,223,372,036,854,775,807)int 4 字节-231(-2,147,483,648)到 231-1(2,147,483,6

14、47)smallint 2 字节-215(-32,768)到 215-1(32,767)tinyint 1 字节0 到 255 decimal(9,2)5 字节decimal(9,2)前面的 9 为精度，后面为小数位。当精度位于19 之间时，占5 字节。当精度位于1019 之间时，占 9字节。注意，numeric 在功能上等价于decimal。decimal(19,2)9 字节money 8 字节-922,337,203,685,477.5808 到 922,337,203,685,477.5807 smallmoney 4 字节-214,748.3648 到 214,748.3647 dat

15、etime 8 字节精确到 3.33 毫秒。例如：2014-03-07 17:25:39.450 存储范围：1753 年 1 月 1 日到9999 年 12 月 31 日smalldatetime 4 字节精确到分钟，例如：2014-03-07 17:24:00 存储范围是：1900 年 1 月 1 日到2079 年 6 月 6 日uniqueidentifier 16 字节uniqueidentifier 数据类型可存储16 字节的二进制值，其作用与全局唯一标识符(GUID)一样。(CHAR(36)bit 1 字节取值范围：0 或 1。char(n)N 字节varchar(n)实际存储的每个

16、字符占1 字节nchar(n)2xN 字节nvarchar(n)实际存储的每个字符占2 字节在存储空间一样的情况下，字符串数据类型需要字符串匹配操作，这通常比整数匹配操作的开销要大。所以尽量选择整数作为字段类型。3.2.3 适当的合理的冗余降低范式标准的一个重要原因是为了在检索数据时少连接表从而提供一个性能优势。或是预先汇总计算结果并存放起来，或是将相同字段内容一式多份地放在多个表中，这样数据的冗余会增加开发人员的工作量和业务判断。(最好是对有冗余的字段要另外用文档统一说明)完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入名师资料总结-精品资料欢迎下载-名

17、师精心整理-第 8 页，共 37 页 -冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。数据库设计阶段，对必要的冗余处理可以事先安排设计，如果在代码实现阶段发现一些必要的冗余字段可以及早提出来考虑。3.2.4 注意大类型的字段设计如果设计过程中发现表中存在大类型(可存储 2G)的字段时，要慎重考虑，因为这样的字段会造成单一数据页存放不了几条

18、记录。而过多的页面也会在查询扫描时带来性能影响。一般的做法是将XML、IMAGE、VARCHAR(MAX)、NVARCHAR(MAX)或 TEXT 类型的字段切割到另外的数据表，而后与主数据表一对一连接。因为这些大型数据访问缓慢，修改时可能造成记录锁定较久。且在大多数的使用状态下，查询一般字段内容时可能根本用不到这些字段。这些列的存在会增加表的页面数，不分割出去容易会影响其它字段的修改和查询。VARCHAR(MAX)、NVARCHAR(MAX)字段如果实际长度在8000 以下，这个值将被作为常规的变长数据类型来对待，如果超过8000 个字节，SQL Server将该值作为TEXT 来存储处理。

19、如果该表数据量比较大时，一定要考虑大字段分离设计原则。少用 TEXT 和 IMAGE，二进制字段的读写是比较慢的。3.3 表关系和约束设计正确处理表间关系。一对多、一对一、多对多等关系。主外键关系是保证数据完整性的一个重要机制。维护数据的正确性。尽量采用提供的约束，如主外键、检查、默认值、不可NULL 等。尽可能不要通过程序或存储过程、触发器等机制来运行，毕竟SQL SERVER 约束是在内部以优化过的二进制程序名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 37 页 -代码来实现的，而其它方式效率当然不如直接设置的约束高。还有，能够确定具有唯一值的字段上尽量加上唯一性约束。一些

20、约束在客户端判断的确是可以减少服务器的资源，但是不能完全保证数据的错误产生。而且用数据库使用域和参照完整性有时候还能帮助优化器减少查询执行时间。域和参照完整性帮助优化器分析有效的数据值而不需要物理访问数据，这减少了查询时间。3.3.1 主键设计所有的表必须设置主键。主键跟聚焦索引没有什么关系,但主键必须要有索引。主键的选择原则：1.字段值唯一。2.不可 NULL。3.字段大小尽量最小。4.字段值不常变更。5.不建议用复合主键。主健值过大会影响外健数据表的大小。如果主键是聚集索引，由于所有非聚集索引都会存储聚集索引的键值，所以主键值过大，还将导致其他索引结构的效率不佳（页面数）。主键关乎着数据的

21、正确性与完整性。而聚焦索引是从数据的运行效率出发。虽然主键跟聚集索引是两回事，但基于主键的上述特性，所以主键往往适合作为表的聚集索引，这也是微软的默认做法。但一些没有意义的ID 做聚集索引的意义不大，这时候需要在创建表的时候给主键指定为唯一的非聚集索引。-主键约束(非聚集索引)：ALTERTABLEdbo.TCustomerADDCONSTRAINT PK_TCustomerPRIMARYKEYNONCLUSTERED(ID);选择 GUID 做为主键时在系统对接、移值和代码编写下都提供了很大的方便，但它是建立在牺牲性能的基础上。在实际运用中，如果对于用36 字符的 GUID 当作主键时，应当

22、注意的问题如下：1.GUID 是无序的，所以不适合用来做聚集索引。否则会引起频繁的页面移动而产生大量的碎片。2.GUID 类型的存储可以由char(36)改为 uniqueidentifier类型（16 个字节），以节省存储空间。3.对于有关联的表之间，考虑程序方便可用使用GUID 做为主键，但对于独立的表，还是以INT名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 37 页 -类型的字段做为主键来设计。所以设计阶段要分清哪些必须用GUID 来做主键。3.3.2 外键设计外键的存在会在处理数据时带来麻烦，但实际上这点恰恰是它的好处。外键的存在就最高效的一致性维护方法。所以在表设

23、计时要考虑主外键的设计。如果决定使用外键约束，那么所有人必须遵守严格执行。外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK 约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。3.3.3 检查约束约束除了 主外键约束、唯一性约束和默认值约束外，还有一类叫检查约束。检查约束是一个识别SQLServer表中每行可接受的列值的规则，检查约束帮助实施域的完整性，域完整性定义了数据库表中列的有效值，检查约束可以验证单列的域完整性，也可以验证多列的域完整性，在单个列上可以有多个检查约束，如果插入或更新的数据违反了检查约束，数据库引擎将暂时停止INSERT 和

24、 UPDATE 操作。CREATETABLE dbo.TEmployee(ID INT,Code VARCHAR(20),Sex CHAR(1)CONSTRAINT Text_Sex_CK CHECK(Sex=FOR Sex=M),-Sex 列 创建相应的约束，其值只能是F 或M 值。Experience INT CONSTRAINT Text_Experience_CK CHECK(Experience=0)-Experience列创建相应的约束，其值必须=0);3.4 索引的设计索引是一把双刃剑，它通常可以加快数据检索数据的同时，往往又会带来额外的资源开销（在insert、update和

25、delete使用时）。有时候这个开销代价甚至超过了查询优化带来的好处。所以，索引的创建是门艺术，要在工作中不断的积累经验和不断的总结。一般来说，建立索引要看数据使用的方式，名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 37 页 -也就是说那些访问数据的SQL 语句经常使用，针对这些经常使用的SQL 语句创建有效的索引还是值得的，但过多的索引又是对于OLTP（在线事务）数据库是不利的。3.4.1 聚集索引和非聚集索引每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。聚集索引和数据是混为一体的，而非聚集索引是与数据独立分开的。其实，我们的汉语字典的正文本身就是一个聚集索

26、引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。如果您认识某个字，您可以快速地从自典中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查

27、到您要找的字（非聚集索引查找），然后根据这个字后的页码直接翻到某页来找到您要找的字（书签查找）。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672 页，检字表中“张”的上面是“驰”字，但页码却是63 页，“张”的下面是“弩”字，页面是 390 页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所

28、需要的页码。通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。-聚集索引查找，没有书签查找开销SELECT*FROM dbo.TOrder 名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 37 页 -WHERE OrderID=1 ORDER BY OrderID;-非聚集索引查找SELECT UserID,OrderID FROM dbo.TOrder WHERE UserID=1 ORDER BY UserID;-非聚集索引查找+书签查找SELECT UserID,OrderID,OrderPriceFROM dbo.TOrder WHERE UserID=1

29、 ORDER BY UserID;3.4.2 索引的初始创建原则如果处在数据库项目的开始，而且不确定如何对索引建模，可以使用不加思考或默认索引模式作为开始。一旦能够根据实际事务信息重新评估数据库后，再调整索引。所以在系统的初始上线阶段一般只考虑创建最少的、最必要的索引。1.所有表要有聚集索引，如果没有合适的字段，那么暂时在主键上创建聚集索引。2.所有外键上创建索引。3.可预知的用来频繁查找的字段上创建索引。4.小表可以不需要特意去创建索引。有主键就好。3.4.3 索引的注意事项1.一个经常插入更新的表不要加太多索引，因为索引影响插入和更新的速度。2.所有非聚集索引包含聚集索引键值，创建非聚集索

30、引时不要再包含进来。3.如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。唯一索引除了可以保证数据的正确性外还可能帮助优化器生成更高效的执行计划。因为在唯一索引中每行都是唯一的，一旦找到一行，SQL Server不必进一步查找其他匹配的行。4.索引不只是带来查询优化，对于更新操作，索引有时候优化查询带来的好处会超过索引维护的开销。所以索引有某些情况下会缩短整个数据更新的时间。因为有时候，表扫描带来的开销会远大于更新名师资料总结-精品资料欢迎下载-名师精心整理-第 13 页，共 37 页 -操作本身的开销。（先查找后更新）5.尽可能地选择那些小数据类型的列来创建索引，大的索引键值增

31、加了索引页面的数量，从而增加了索引所需要的内存和磁盘活动数量。6.经常有 范围查询（between,=,=2001-07-01AND OrderDate5WHEREOrderDate=DATEADD(DAY,-5,GETDATE()WHERE LEFT(AccountName,3)=fanWHERE AccountName LIKEfan%WHERE id+9 id WHERE id id 9 WHERE CodeLIKE%Core%WHERE CodeLIKECore%NOT IN(SELECTIDFROM)LEFTJOINbWHERE b.IDISNULL 或NOT EXISTS(SELE

32、CT*FROM b)WHERE IDIN(2,3,4,5,6)WHERE ID=2 AND ID=6WHEREFirstName+LastName=fan you;WHEREFirstName=fanANDLastName=you名师资料总结-精品资料欢迎下载-名师精心整理-第 18 页，共 37 页 -WHERE Status 10WHEREStatus 10NOT IN、LEFT JOIN、NOT EXISTS 和 IN、INNER JOIN、EXISTS 的效率问题要具体情况具体分析。一般情况下推荐使用相关子查询（EXISTS）和连接的方式。SQL Server从左到右处理表，这个在技术

33、内幕上有。而where语句中最能快速筛选数据的列应该放在最前面，也就是最接近where子句的地方。但在SQL SERVER 2005 后来的版本中，优化器会帮你自动优化的。4.3 少用触发器和禁用游标触发器触发器在时间久远后不易维护,触发器不会立即让开发人员看到。而且触发器内容太过复杂，还会存在潜在的性能瓶颈。所以我们统一规定不允许使用触发器，应使用其它办法来代替触发器。对之前已经存在的触发器也应该抽时间去替换它。项目负责人还需要检查是否产生了新的触发器并及时纠正。游标SQL Server适合在数据集（多行）上进行操作，游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特

34、别是内存和锁资源）的消耗是非常大的。4.4 联合查询尽可能使用UNION ALL UNION操作依次执行所有的SELECT 语句，将所有的结果集合并为一个结果集。将对结果集进行排序，并过滤掉重复的记录。可见联合查询的效率很低的，除非在必要的情况下才使用。如果允许结果集存在重复，或预知结果集根本不可能重复时一定要用UNION ALL来代替。4.5 尽可能避免的地方下面这些操作在使用前，可以重新思考下业务和检查一下逻辑，看是否可以避免。名师资料总结-精品资料欢迎下载-名师精心整理-第 19 页，共 37 页 -DISTINCT关键字如果 SELECT 语句中查询的 字段很多，则使用该关键字段反而会

35、大大降低查询效率。因为查询字段很多时，如果使用筛选重复关键字，数据库引擎需要花费大量的时间对所有字段进行比较，过滤掉重复的记录，因此影响了查询效率。Order By Order By 等语句尽量是查询结果需要才使用，因为太忙都需要做额外的计算。不管你使用TOP 返回少量记录，但 Order By 都会事先排完序的。嵌套视图嵌套视图是当一个视图调用另一个视图时，另一个视图调用更多的视图。这可能导致非常容易混淆的代码，因为视图掩盖了被执行的操作。虽然查询可能非常简单，执行计划和随后SQL 引擎的操作可能非常复杂并且代价很高。同样，嵌套的用户自定义函数也是如此。嵌套自定义函数注意表值函数分内联表值函

36、数和多语句表值函数。多语句表值函数是用表变量返回的，对此用执行计划查看时，优化器不能够很好地估计执行用户定义函数的成本（不适合返回大量数据行）。所以必须引起注意，有时候引用的自定义函数可以隐藏性能问题，所以应该分析内部的语句。4.6 避免返回和使用多余的数据返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的压力，其害处是显而易见的，避免这类事件需要注意：1.横向来看，不要写SELECT*的语句，而是选择你需要的字段。2.纵向来看，合理写WHERE 子句，不要写没有WHERE 的 SQL 语句。通用视

37、图带来的干扰比如可能只要用到TProduct 表就可以了，但却用 VProduct视图，而 VProduct 视图可能存在更多的表连接，甚至带来了不必要的性能问题。返回多余的列返回多余的列可能会造成书签查找操 作，本来有时候非聚集索引能解决的，还额外的使用了书签查找。更不要允许出现SELECT*这样的语句。但在相关子查询中使用EXISTS(SELECT*)可以放心使用*。因为 EXISTS 只关心行是否存在，不关心特定属性，优化器将会忽略子查询中的SELECT 列表。SETNOCOUNT ON存储过程内部用SET NOCOUNT ON;否则每次执行完就会向客户端发送消息，禁用它们能够减少网络流

38、量。名师资料总结-精品资料欢迎下载-名师精心整理-第 20 页，共 37 页 -4.7 操作符优化1.IN 和 EXISTS 性能有外表和内表区分的，但是在大数据量的表中推荐用EXISTS 代替 IN。2.NOT IN 不走索引的是绝对不能用的，可以用NOT EXISTS 代替。3.IS NULL 或 IS NOT NULL操作索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可，例如 4.操作符（不等于）不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。用其它相同功能的操作运算代替，

39、如 a0 改为 a 0 OR a 0 同样 a 改为 a OR a 高版本的数据库引擎可能会对上面的问题进行自动优化，但养成好的习惯也是必要的。4.8 数据库事务处理原则事务过程中可能包含查询语句和修改语句，对于查询语句所申请的共享锁会在查询结束时释放，而对于修改语句申请的独占锁会持续到事务结束。所以你需要认真规划事务：1.保持事务范围尽可能地小。不必要的语句尽量要拿到事务之外。2.尽早提交事务。所以事务涉及到的SQL 语句不能过于复杂。3.避免事务不能结束。使用 SETXACT_ABORTON 确保事务出现错误时中止或回滚。4.消除读写阻塞。比如使用脏读（表名后加WITH(NOLOCK)）。

40、(减少锁开销)如果不是重要的、特别敏感的数据，允许脏读可以避免一定程度的阻塞，加快查询速度。5.事务操作过程尽量要按同一顺序访问表对象。6.提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。名师资料总结-精品资料欢迎下载-名师精心整理-第 21 页，共 37 页 -7.尽量不要指定锁类型和索引，SQL SERVER 允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER 优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。4.9 最少次数的访问表

41、尽量少做重复的工作，最少次数的访问表。1.如果可以只访问一次表，就没有必要再次访问表。有的是存储过程内部语句太多，写在后面没有留意的问题。所以需要审查自己写的存储过程。UPDATE dbo.TEMPLOYEE SET FNAME=HAIWER WHERE EMP_ID=VPA30890F;UPDATE dbo.TEMPLOYEE SET LNAME=YANG WHERE EMP_ID=VPA30890F;2.使用公用表达式CTE，可以多次引用，从而减少基础表的访问次数。3.使用ROWCOUNT判断影响的表行数从而避免表多次返问。4.10 避免隐含的数据类型转换-Code本来是 NVARCHAR

42、(36)类型DECLAREcodeVARCHAR(36);SETcode=0D32C649-A584-4629-8EE3-DDF26A61F9C1;SELECT*FROM dbo.TTable1WHERE ID=code;在执行计划中看到，导致需要额外的操作对值进行转换之后才能用于比较。在 Unicode字符前面使用N 前缀，避免引起数据的不一致。DECLAREcodeNVARCHAR(36);SETcode=N0D32C649-A584-4629-8EE3-DDF26A61F9C1;SELECT*FROM dbo.TTable1WHERE ID=code;所以存储过程或TSQL 语句中变量类

43、型的定义要以数据库中表字段设计类型为依据。4.11 表变量、临时表和公用表达式的用法1.如果表中的行数在100 以下，推荐使用表变量。但是 SQL 引擎并不能为表变量建立统计信息，当表变量存储过多的数据并且又和其它表进行join时,优化引擎会错误估计表变量的行数，认为少数的名师资料总结-精品资料欢迎下载-名师精心整理-第 22 页，共 37 页 -几十行记录导致性能往往会很差（所以这也是多语句表值函数不适合返回大表的原因）。而临时表会创建统计数据，通过实际的行数生成执行计划。2.表变量仅仅在当前的批处理中有效，作用域很小(不能跨 GO),并且在批处理结束后自动被清除。表变量较临时表产生更少的存

44、储过程重编译。表变量的事务仅仅在更新数据时生效，持续时间比临时表短，事务回滚不会影响表变量。而临时表是正常的事务长度，比表变量持续时间长。局部临时表只在当前会话中有效，这也包括嵌套的存储过程。总之，表变量使用较少的日志和加锁资源，在存储过程内部，临时表属于中间缓存数据表需要编译执行计划，而表变量不需要重新编译。3.表变量也可以创建索引。-表变量创建时就要定义为索引或主键。因为一旦建立表变量后就无法对其进行DDL 语句操作。DECLARE MyTableVariable TABLE(RowID INT PRIMARYKEY CLUSTERED);-表变量的列上创建唯一约束以及如何建立复合索引。D

45、ECLARE temp TABLE(RowID INT NOTNULL,ColA INT NOTNULL,ColB CHAR(1)UNIQUE,PRIMARYKEY CLUSTERED(RowID,ColA);INSERTINTO MyTableVariable VALUES(1);INSERTINTO MyTableVariable VALUES(2);SELECT*FROM MyTableVariable WHERE RowID=1;4.一般来说，表变量和CTE 放内存，临时表放硬盘，但证明表变量也会放硬盘的。也会在硬盘创建对象。相对而言临时表主要是多了I/O 时间，但少了对内存资源的占用

46、。数据量较大的时候，由于对内存资源的消耗较少，使用临时表比表变量有更好的性能。（数据量大时，表变量产生的执行计划也不准确）。DECLARE tb TABLE(id INT)SELECT*FROM tempdb.sys.tables-有一个临时表goSELECT*FROM tempdb.sys.tables-没了goCREATE TABLE#t(id INT);SELECT*FROM tempdb.sys.tables名师资料总结-精品资料欢迎下载-名师精心整理-第 23 页，共 37 页 -有一个临时表goSELECT*FROM tempdb.sys.tables-还有go5.SQL Serv

47、er在用户自定义函数UDF 不允许使用临时表，这种情况下必须使用表变量。6.如果使用到了临时表，在使用完成后应及时将所有的临时表显式删除。7.关于临时表产生使用SELECT INTO 和 CREATE TABLE+INSERT INTO的选择，我们做过测试，一般情况下，SELECT INTO 会比 CREATE TABLE+INSERT INTO的方法快很多，但是SELECT INTO会锁定TEMPDB 的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATE TABLE+INSERT I

48、NTO，而大数据量的单个语句使用中，使用SELECT INTO。8.公用表表达式（CTE）：定义在内存中保存的临时存储结果集对象，不产生 I/O，不需要按照表变量这样定义，使用方法和表类似。可以自己引用，也可以再查询中被多次引用。CTE 所带来最大的好处是代码可读性的提升,这是良好代码的必须品质之一。使用递归CTE 可以更加轻松愉快的用优雅简洁的方式实现复杂的查询。数据量大时，CTE 的性能要比临时表差，而且也消耗大量的内存。所以存储大量数据时，推荐使用临时表。4.12 正确地判断记录是否存在推荐用相关子查询判断记录是否存在。-推荐这种方式来判断表数据是否存在IF EXISTS(SELECT*

49、FROM dbo.TUser)BEGINPRINT();END4.13 注意自定义标量函数的影响用户自定义函数非常适合于封闭标量计算。但是正因为封闭方面的要求使得它们迟早会用在较大的、报表类型结果集中。这时，有一个隐藏的性能影响。查询会在结果集增长到某个程度时，开销变得名师资料总结-精品资料欢迎下载-名师精心整理-第 24 页，共 37 页 -很大。所以对于2 万条以上记录，严格禁止使用函数。在使用自定义函数，要确保以下两点：1.引用函数的语句要保证使用WHERE 谓词或 TOP 将结果限制在一个较小的结果集。2.自定义函数内部的语句要保证查询效率，最好能用上索引。而一般视图包含的可能是个大结

50、果集。所以当视图中嵌入了用户自定义函数时，运行它们很可能会需要大量的成本。4.14 避免编写复杂的TSQL 语句要保证每个SQL 语句尽量简单，语句过于复杂时，生成的执行计划也会复杂，不能保证执行的有效性，所以应用分割成几个部分去执行。如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。如果需要多次用到一个大表的同一部分数据，考虑用临时表暂存这部分数据。如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。4.15 应用程序层防止执行大块的TSQL 语句大批量的 TSQL 语句会增加网络传输、处理及存储的工作量。4.16 对数据库大表的处理方案对于经常