数据库设计和编码规范.docx
《数据库设计和编码规范.docx》由会员分享，可在线阅读，更多相关《数据库设计和编码规范.docx（42页珍藏版）》请在冰点文库上搜索。
数据库设计和编码规范
数据库设计和编码规范
Version
简介
读者对象
此文档说明书供开发部全体成员阅读。
目的
一个合理的数据库结构设计是保证系统性能的基础。
一个好的规范让新手容易进入状态且少犯错，保持团队支持顺畅，系统长久使用后不至于紊乱，让管理者易于在众多对象中，获取所需或理清问题。
同时，定义标准程序也需要团队合作，讨论出大家愿意遵循的规范。
随着时间演进，还需要逐步校订与修改规范，让团队运行更为顺畅。
数据库命名规范
团队开发与管理信息系统讲究默契，而制定服务器、数据库对象、变量等命名规则是建立默契的基本。
命名规则是让所有的数据库用户，如数据库管理员、程序设计人员和程序开发人员，可以直观地辨识对象用途。
而命名规则大都约定俗成，可以依照公司文化、团队习惯修改并落实。
规范总体要求
1.避免使用系统产品本身的惯例，让用户混淆自定义对象和系统对象或关键词。
例如，存储过程不要以sp_或xp_开头，因为SQLSERVER的系统存储过程以sp_开头，扩展存储过程以xp_开头。
2.不要使用空白符号、运算符号、中文字、关键词来命名对象。
3.名称不宜过于简略，要让对象的用途直观易懂，但也不宜过长，造成使用不方
便。
4.不用为数据表内字段名称加上数据类型的缩写。
5.名称中最好不要包括中划线
6.禁止使用［拼音］+［英语］的方式来命名数据库对象或变量。
数据库对象命名规范
我们约定，数据库对象包括表、视图（查询）、存储过程（参数查询）、函数、约
束。
对象名字由前缀和实际名字组成，长度不超过30。
避免中文和保留关键字，做到
简洁又有意义。
前缀就是要求每种对象有固定的开头字符串，而开头字符串宜短且字
数统一。
可以讨论一下对各种对象的命名规范，通过后严格按照要求实施。
例如:
对象
命名规范
数据库
数据库名：
［项目英文名称］+DB数据文件：
［数据库名称］+日志文件：
［数据库名称］+
表
前缀T+［表名］;单词首写字母为大写，其余全部小写。
示范：
TCustomer
表字段
不需要前缀，直接用英文单词或缩写，单词首字母为大写，其余为小与。
例如：
UserName，如果是两个单词的首与字母缩与，统一用大与，
比如：
UserID
主键所在字段
不要用ID。
一律用表名+ID（如果表名太长的话，米用缩写用各单词的首写字母组合）
存储过程
用P_前缀+［功能描述］（首单词大写，其余下写）例如：
PGetAIICorps
视图
用前缀V_+［视图名称］
例如：
VAccount
自定义标量函数
前缀F_+［功能描述］（首单词大写，其余下写）例如：
FGetEWSourceName
自定义表值函数
前缀TF_+［功能描述］（首单词大写，其余下写）
主键
PK_［表名］例如：
PKTExAccount
外键
用FK［主表名］字段表表示（考虑到名字会比较长，突出主表）例如：
FKTOrderOrderlD
默认值约束
用DF_［表名］_［字段名］表示例如：
DFTOrderType
检查约束
用CK_［表名］_［字段名］表示例如：
CKTCustomerMail
唯一性约束
用UQ_［表名］」字段名］表示
例如：
UQTCustomerCode
聚集索引
用DX_［表名］」字段名］表示
例如：
DXTCachetID
其它索引
用IX_［表名］_［字段名］表示（字段名较多时，取前面两个即可）
例如：
IX_TCachet_CName_CorplD
变量命名规范
1.数据列参数
命名格式为@+［列名称］。
示例：
@EmployeelD@employee_id
2.非数据列参数
在参数无法跟列名称进行关联时，使用能够反映该参数功能的英文单词或单词组合，采
用Pascal样式命名。
示例：
@WorkType@work_type
数据库设计规范
好的数据库架构设计对系统运行的性能起着很大的作用，所以要在开始时就要引起
重视。
为了保证数据库设计的高效必须安排时间对设计结果进行评审，这一环节必不
可少。
选择有效的设计工具
数据库设计工具：
PowerDesigner、ERStudio、Rose、MicrosoftVisio。
项目开始前要确定使用哪种设计工具。
（另有开发插件：
RedGate系列（SQL
Prompt））
选择的工具要便于讨论便入生成脚本导入数据库。
设计通过后要形成文档，并且这个结构设计文档要存档，签入VSS基线库中。
在进行数据库设计时，应随时进行数据字典的维护。
（字段要求写说明）
表的设计
表设计在数据库设计中占据有十分重要的地位。
表是实际存储数据的对象。
除了要注
重表结构设计，字段的设计之外还要注意表之间关系的设计。
遵守范式要求
通常，合理的规范化会最小化数据异常和减少数据的冗余。
为了更新数据的正确与
快速，在设计的初始阶段多采用三范式设计数据库表。
第一范式强调的是列的原子性，即列不能够再分成其他几列。
第二范式包含两层意思，一是表必须有一个主键；二是非主键列必须完全依赖于主
键，且不能只依赖于主键的一部分。
（尽量少使用复合主键）第三范式需要确保数据表中的所有非主键列直接与主键列相关，而不能直接依赖于非主键列。
字段设计
1.尽量避免可为空的列。
虽然在个别情况下，允许空值可能是有用的，但是应尽量少用。
这是因为需要对它们进行特殊处理，从而会增加数据操作的复杂性和增加CPU额外的逻辑判断。
很多情况
下可以考虑用默认值0或空字符串（''）来代替NULL值。
所以字段应该有NOTNULL的限制。
2.Unicode的选择。
nvarchar和nchar相应比varchar和char要占用更多的存储空间。
设计的原则是：
如果确保存储的内容只是纯英文和数字，用char/varchar。
如果含有中文字符或其它多国语言，用nchar/nvarchar。
3.字段长度要精确，遵守“必须、够用”的原则。
精确的长度设计既能完整的描述数据，又可以节省存储空间。
积小成大，当数据表中的数据有很多记录的时候，这种存储空间的优势就能体现得十分明显。
存储空间越紧凑，分配的页面就越少，在同样大小的内存空间中就可以存储更多的页面，这样操
作数据的效率就会提高。
例如能用char（10）的就不要用char（20）,提高存储的利用率
和系统性能，但同时也要兼顾扩展性和可移植性。
字段类型
存储空间
补充说明
bigint
8字节
-2A63（-9,223,372,036,854,775,808）到2A63-1
（9,223,372,036,854,775,807）
int
4字节
-2A31（-2,147,483,648）至U2人31-1（2,147,483,647）
smallint
2字节:
-2A15（-32,768）至U2人15-1（32,767）
tinyint
1字节
0到255
decimal（9,2）
5字节
decimal（9,2）前面的9为精度，后面为小数位。
当精度位于1~9之间时，占5字节。
当精度位于10~19之间时，占9
字节。
注意，numeric在功能上等价于decimalo
decimal（19,2）
9字节
money
8字节
-922,337,203,685,到922,337,203,685,
smallmoney
4字节丁
-214,至U214,
datetime
8字节
精确到毫秒。
例如：
2014-03-0717:
25:
存储范围：
1753年1月1日到9999年12月31日
smalldatetime
4字节
精确到分钟，例如：
2014-03-0717:
24:
00
存储范围是：
1900年1月1日到2079年6月6日
uniqueidentifier
16字节
uniqueidentifier数据类型可存储16字节的一进制值，其
作用与全局唯一标识符（GUID）—样。
（CHAR（36））
bit
1字节
取值范围：
0或1。
char（n）
N字节:
varchar（n）
实际存储的每个字符占1字节
nchar（n）
2xN字节
nvarchar（n）
实际存储的每个字符占2字节
在存储空间一样的情况下，字符串数据类型需要字符串匹配操作，这通常比整数匹配操
作的开销要大。
所以尽量选择整数作为字段类型。
适当的合理的冗余
降低范式标准的一个重要原因是为了在检索数据时少连接表从而提供一个性能优势。
或是预先汇总计算结果并存放起来，或是将相同字段内容一式多份地放在多个表中，这样数据的冗余会增加开发人员的工作量和业务判断。
（最好是对有冗余的字段要
另外用文档统一说明）
完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后,有计划地加入冗余是必要的。
冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。
冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。
从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。
数据库设计阶段，对必要的冗余处理可以事先安排设计，如果在代码实现阶段发现一些必要的冗余字段可以及早提出来考虑。
注意大类型的字段设计
如果设计过程中发现表中存在大类型（可存储2G）的字段时，要慎重考虑，因为这样的字段会造成单一数据页存放不了几条记录。
而过多的页面也会在查询扫描时带来性能影响。
一般的做法是将XML、IMAGE、VARCHAR（MAX）、NVARCHAR（MAX）或TEXT类型的字段切割到另外的数据表，而后与主数据表一对一连接。
因为这些大型数据访问缓慢，修改时可能造成记录锁定较久。
且在大多数的使用状态下，查询一般字段内容时可能根本用不到这些字段。
这些列的存在会增加表的页面数，不分割出去容易会影响其它字段的修改和查询。
VARCHAR（MAX）、NVARCHAR（MAX）字段如果实际长度在8000以下，这个值将被作为常规的变长数据类型来对待，如果超过8000个字节，SQLServer将该值作为TEXT来存储处理。
如果该表数据量比较大时，一定要考虑大字段分离设计原则。
少用TEXT和IMAGE，二进制字段的读写是比较慢的。
表关系和约束设计正确处理表间关系。
一对多、一对一、多对多等关系。
主外键关系是保证数据完整性的一个重要机制。
维护数据的正确性。
尽量采用提供的约束，如主外键、检查、默认值、不可NULL等。
尽可能不要通过程序或存储过程、触发器等机制来运行，毕竟SQL
SERVER约束是在内部以优化过的二进制程序代码来实现的，而其它方式效率当然不
如直接设置的约束高。
还有，能够确定具有唯一值的字段上尽量加上唯一性约束。
一些约束在客户端判断的确是可以减少服务器的资源，但是不能完全保证数据的错误产生。
而且用数据库使用域和参照完整性有时候还能帮助优化器减少查询执行时间。
域和参照完整性帮助优化器分析有效的数据值而不需要物理访问数据，这减少了查询时间。
主键设计
所有的表必须设置主键。
主键跟聚焦索引没有什么关系,但主键必须要有索引。
主键的选择原则：
1.字段值唯一。
2.不可NULL。
3.字段大小尽量最小。
4.字段值不常变更。
5.不建议用复合主键。
主健值过大会影响外健数据表的大小。
如果主键是聚集索引，由于所有非聚集索引都会存储聚集索引的键值，所以主键值过大，还将导致其他索引结构的效率不佳（页面数）。
主键关乎着数据的正确性与完整性。
而聚焦索引是从数据的运行效率出发。
虽然主键跟聚集索引是两回事，但基于主键的上述特性，所以主键往往适合作为表的聚集索引，这也是微软的默认做法。
但一些没有意义的ID做聚集索引的意义不大，这时候需要在创建表的时候给主键指定为唯一的非聚集索引。
--主键约束（非聚集索引）：
ALTERTABLE[dbo].[TCustomer]ADDCONSTRAINTPK_TCustomerPRIMARYKEYNONCLUSTERE（DID）;
选择GUID做为主键时在系统对接、移值和代码编写下都提供了很大的方便，但它是建立在牺牲性能的基础上。
在实际运用中，如果对于用36字符的GUID当作主键时，应当注意的问题如下：
1.GUID是无序的，所以不适合用来做聚集索引。
否则会引起频繁的页面移动而产生大量的碎片。
2.GUID类型的存储可以由char（36）改为uniqueidentifier类型（16个字节），以节省存储空间。
3.对于有关联的表之间，考虑程序方便可用使用GUID做为主键，但对于独立的表，还是以INT类型的字段做为主键来设计。
所以设计阶段要分清哪些必须用GUID来做主键。
外键设计
外键的存在会在处理数据时带来麻烦，但实际上这点恰恰是它的好处。
外键的存在就最高效的一致性维护方法。
所以在表设计时要考虑主外键的设计。
如果决定使用外键约束，那么所有人必须遵守严格执行。
外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。
检查约束
约束除了主外键约束、唯一性约束和默认值约束外，还有一类叫检查约束。
检查约束是一个识别SQLServer表中每行可接受的列值的规则，检查约束帮助实施域的完整性，域完整性定义了数据库表中列的有效值，检查约束可以验证单列的域完整性，也可以验证多列的域完整性，在单个列上可以有多个检查约束，如果插入或更新的数据违反了检查约束，数据库引擎将暂时停止INSERT和UPDATE操作。
CREATETABLE（
IDINT,
CodeVARCHA（R20）,
SexCHAR
（1）CONSTRAINText_Sex_CKCHECK（Sex='F'ORSex='M'）,
--Sex列创建相应的约束，其值只能是'F'或'M'值。
ExperienceINTCONSTRAINText_Experience_CKCHECK（Experience>=0）
--Experience列创建相应的约束，其值必须>=0
）;索引的设计
索引是一把双刃剑，它通常可以加快数据检索数据的同时，往往又会带来额外的资源开销（在insert、update和delete使用时）。
有时候这个开销代价甚至超过了查询优化带来的好处。
所以，索引的创建是门艺术，要在工作中不断的积累经验和不断的总结。
一般来说，建立索引要看数据使用的方式，也就是说那些访问数据的SQL语句经常使用，针对这些经常使用的SQL语句创建有效的索引还是值得的，但过多的索引又是对于OLTP（在线事务）数据库是不利的。
聚集索引和非聚集索引
每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。
聚集索引和数据是混为一体的，而非聚集索引是与数据独立分开的。
其实，我们的汉语字典的正文本身就是一个聚集索引。
比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。
同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。
也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。
我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
如果您认识某个字，您可以快速地从自典中查到这个字。
但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字（非聚集索引查找），然后根据这个字后的页码直接翻到某页来找到您要找的字（书签查找）。
但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。
很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。
我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。
通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。
--聚集索引查找，没有书签查找开销
SELECT*FROM[dbo].[TOrder]
WHEROErderID=1
ORDERBYOrderID;
--非聚集索引查找
SELECTUserID,OrderIDFROM[dbo].[TOrder]
WHERUEserID=1
ORDERBYUserID;
--非聚集索引查找+书签查找
SELECTUserID,OrderID,OrderPriceFROM[dbo].[TOrder]
WHERUEserID=1
ORDERBYUserID;
索引的初始创建原则
如果处在数据库项目的开始，而且不确定如何对索引建模，可以使用不加思考或默认索引模式作为开始。
一旦能够根据实际事务信息重新评估数据库后，再调整索引。
所以在系统的初始上线阶段一般只考虑创建最少的、最必要的索引。
1.所有表要有聚集索引，如果没有合适的字段，那么暂时在主键上创建聚集索引。
2.所有外键上创建索引。
3.可预知的用来频繁查找的字段上创建索引。
4.小表可以不需要特意去创建索引。
有主键就好。
索引的注意事项
1.一个经常插入更新的表不要加太多索引，因为索引影响插入和更新的速度。
2.所有非聚集索引包含聚集索引键值，创建非聚集索引时不要再包含进来。
3.如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。
唯一索引除了可以保证数据的正确性外还可能帮助优化器生成更高效的执行计划。
因为在唯一索引中每行都是唯一的，一旦找到一行，SQLServer不必进一步查找其他匹配的行。
4.索引不只是带来查询优化，对于更新操作，索引有时候优化查询带来的好处会超过索引维护的开销。
所以索引有某些情况下会缩短整个数据更新的时间。
因为有时候，表扫描带来的开销会远大于更新操作本身的开销。
（先查找后更新）
5.尽可能地选择那些小数据类型的列来创建索引，大的索引键值增加了索引页面的数量，从而增加了索引所需要的内存和磁盘活动数量。
6.经常有范围查询（between,>,<，>=,<=）或用来作条件返回很多列和orderby、groupby发生的列，可考虑建立聚集索引；（分区字段是时间类型的话，适合聚集索引）
7.非聚集索引在需要从一个大表上读取少量的行时最有用。
当匹配返回的记录数过多时，需要用到的书签查找（键查找）的开销将会变得很大。
所以像性别这样的字段不要创建非聚集索引。
低选择性的列只能配合其它字段创建复合非聚集索引。
8.多个字段创建组合索引时要尽量使关键查询形成索引覆盖，其第一个列一定是使用最频繁的列；但包含的列不能太多，不能有大类型的字段。
9.缺乏合适的索引也是造成阻塞、死锁的原因。
10.频繁更新的列不适合创建聚集索引。
11.主键就是聚集索引，极端错误的，是对聚集索引的一种浪费。
虽然SQLSERVER
默认是在主键上建立聚集索引的。
显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。
使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。
（尤其是分区表，适合时间做聚集索引）
索引的后期维护工作索引创建后不就是完事了的，一定要定期观察索引在实际工作环境中的使用情况。
及时阻止索引对系统带来的负面影响。
总的来说应该考虑如下几点：
1.去掉使用率低的索引。
2.合理的改善索引，使索引更有效的被利用到。
3.创建缺失的必要的索引。
4.考虑索引碎片的问题。
索引碎片率过大时，查询得不到优化。
由于表上有过度地插入、修改和删除操作，索引页被分成多块就形成了索引碎片，如果索引碎片严重，那么扫描索引的时间就会变长，甚至导致索引不可用，因此数据检索操作就慢下来了。
如果碎片小于10%~20%，碎片不太可能会成为问题，如果索引碎片在20%~40%，碎片可能成为问题，但是可以通过索引重组来消除索引解决，大规模的碎片（当碎片大于40%），可能要求索引重建。
--查看某个表的碎片情况（整理数据的碎片，是整理聚集索引的碎片）
--结果看LogicalFramentation字段
DBCCSHOWCONTIG（'[dbo].[TLog]'）WITHFAST,TABLERESULT,SALL_INDEXE,SNO_INFOMS;GS总之，索引的后期跟踪是不断持续的过程。
为了搭建高性能的系统环境，就必须定期有效的跟踪索引。
物理存储设计除了重视逻辑对象的设计，还需要考虑数据库的物理设计。
在并发要求很高、并发用户数很多的情况下，这一设计对数据库的性能起到十分关键的作用。
数据库物理文件一般不要存放在C盘，因为系统重装对C盘破坏最大。
日志文件另外存放
查询数据库的页，可以看到，由于页的ID不连续，所以数据文件内部的读写是随机的。
而日志文件的读写是顺序的，所以两者放在同一个硬盘上，会造成硬盘驱动器一会随机，一会顺序，效率会比较低。
将数据文件和日志分离存储在不同的物理硬盘上。
这样的好处是确保数据的安全，避免单点失效。
二是确保数据库的性能。
同样备份文件也在不同的磁盘上。
存储空间的设计
正确评估和测算数据库的物理空间需求。
因为数据库采用预先分配存储空间的方法。
存储空间的分配操作是一个非常消耗资源的操作。
所以设计人员需要评估数据空间的可能增长率，将数据库的空间增长方式设置为恰当好处，这样就可以在空间和效率之间取得均衡。
设计要考虑的内容有：
1.数据库文件和日志文件初始值的设计。
2.数据库文件和日志文件以多大的比例增长。
（不要用默认的1M或10%）要设置成按固定大小增长，这样就能避免一次增长太多或者太少所带来的不必要的影响。
建议对比较小的数据库，设置一次增长50MB到100MB。
对大的数据库，设置一次增长200MB到800MB。
3.对于生产数据库，推荐的设置是开启数据库自动增长和不限制大小，以防数据库空间用尽导致应用程序失败。
4.在系统一段时间稳定后，可以采取日志备份的机制使得数据库日志文件大小固
定下来，不再持续增长。
事务日志备份可以截断日志，在检查点发生时会清空
日志，这样会在已有的空间内重新记录日志，而不用分配新的空间
5.分
升级会员 