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