数据库的设计与编码优化Word文件下载.docx
《数据库的设计与编码优化Word文件下载.docx》由会员分享，可在线阅读，更多相关《数据库的设计与编码优化Word文件下载.docx（23页珍藏版）》请在冰豆网上搜索。
2NF实例分析
以上设计存在以下问题
a.数据冗余，同一门课由n个学生选修，学分就重复n-1次；
同一个学生选修了m门课程，姓名和年龄就重复了m-1次。
b.更新异常，若调整了某门课程的学分，表中所有行的学分值都要更新，否则会出现同一门课学分不同。
c.插入异常，如果计划开新课，由于没人选修，没有学号关键字，只能等有人选修才能把课程和学分存入。
d.删除异常，若学生已经结业，从当前数据库删除选修记录，与此同时，某门课程新生尚未选修，则此门课程及学分记录无法保存。
原因是表列存在如下依赖关系
学号←姓名、年龄、性别、系别、系办地址、系办电话
课程名←课程学分
学号、课程名←学科成绩
解决方法
拆分成3个表，使其符合2NF
学生Student（学号、姓名、年龄、性别、系别、系办地址、系办电话）
课程Course（课程名、课程学分）
选课关系ChoseCourse（学号、课程名、学科成绩）
3NF实例分析
以上设计还存在以下问题
a.数据冗余，n个学生同属一个系，系办地址和系办电话就重复n-1次。
b.更新异常，若调整了某个系别的地址或电话，表中与该系别相关的所有行都要更新，否则会出现系别地址或电话不一致。
c.插入异常，如果计划设立一个新院系，由于没有学号关键字，只能等有人进入该院才能把院系信息存入。
原因是表列还存在传递依赖非主关键字关系
学号←系别←系办地址、系办电话
将Student（学号、姓名、年龄、性别、系别、系办地址、系办电话）表继续拆分成2个表，使其符合3NF
学生Student（学号、姓名、年龄、性别、系别）
系别Department（系别、系办地址、系办电话）
至此，上面的数据表设计符合1NF、2NF、3NF，消除了数据冗余，更新异常，插入异常与删除异常。
2、合理的冗余
大系统的设计里，加入合理的冗余是必要的。
冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。
从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。
3、字段的设计
字段是数据库最基本的单位，其设计对性能的影响很大：
A、数据类型尽量用数字型，数字型的比较比字符型的快很多。
B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。
C、尽量不要允许NULL，除非必要，可以用NOTNULL+DEFAULT代替。
NULL值会带来以下困扰。
NULL与任何值的比较都为假，比较结果
selectcount（*）astotalfromProduction.ProductwhereProductModelID<
>
2
2orProductModelIDisnull
NULL被排除在集合汇总之外，比较结果
selectcount（ProductModelID）astotalfromProduction.Product
selectcount（*）astotalfromProduction.Product
D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多。
E、真假、对错、男女等值可定义成BIT类型，能减少存储空间与加快检索。
F、一个表中所有字段的总长度尽量不要超过8KB，因为SQLServer中数据存储的基本单位是页，页的大小为8KB，减少数据分页能降低磁盘IO。
4、主键的设计
主键是确保表不包含重复行的约束，主键同时是一个唯一索引，在实际应用中，选择最小的键组合作为主键，主键往往适合作为表的聚簇索引。
在有多个键的表，一般选择总的长度小的键作主键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。
主键的选择还要注意组合主键的字段次序，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。
5、外键的设计
外键是强制引用完整性的约束，它是高效的一致性维护方法。
数据库的一致性要求，依次可以用外键、CHECK约束、规则、触发器、客户端程序，离数据越近的方法效率越高。
6、索引的设计
在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。
A、根据使用频率决定哪些字段需要建立索引，选择经常作为筛选条件、连接条件、聚合查询、排序的字段作为索引的候选字段。
B、把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。
C、保持较窄的索引列，避免添加不必要的列。
添加太多索引列对磁盘空间和索引维护性能会产生负面影响。
D、多个索引可以提高一个表的查询的性能，但也不能加入太多索引，因为索引影响插入和更新的速度，加大对磁盘空间的占用和索引维护。
E、根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。
对数据量小的表建立多个索引可能不会产生优化效果。
F、聚簇索引的效率高于非簇索引，对于聚簇索引，应保持短的索引键长度与低的重复率。
7、系统设计
整个系统的设计特别是系统结构设计对性能是有很大影响的，设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。
用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。
二、编码阶段
编码阶段首先是需要程序员有性能意识，也就是在实现功能同时有考虑性能的思想，数据库是能进行集合运算的工具，应该尽量的利用它，所谓集合运算实际是批量运算，就是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。
下面罗列一些编程阶段需要注意的事项：
1、只返回需要的数据
返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：
A、横向来看，不要写SELECT*的语句，而是选择你需要的字段。
B、纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。
C、对于聚合查询，可以用HAVING子句进一步限定返回的行。
2、尽量少做重复的工作
A、控制同一语句的多次执行。
B、杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。
C、合并对同一表同一条件不同字段的多次UPDATE，比如
updatePerson.ContactsetFirstName='
Gusta'
whereContactID=1
updatePerson.ContactsetLastName='
Achon'
合并成
LastName='
whereContactID=1
D、单一的UPDATE操作不要拆成DELETE+INSERT操作的形式。
3、注意事务和锁
事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作都需要利用事务来保证数据的正确性。
在使用事务中需要做到尽量避免死锁、尽量减少阻塞。
具体以下方面需要特别注意：
A、事务操作过程要尽量小，能拆分的事务要拆分开来。
B、事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。
C、事务操作过程要按同一顺序访问对象，减少死锁的发生。
以下示例为产生死锁的两个session
session1
settransactionisolationlevelrepeatableread
go
begintran
updatePerson.ContactsetPhone='
849-555-1111'
whereContactID='
1005'
waitfordelay'
00:
10'
select*fromHumanResources.EmployeewhereContactID='
committran
session2
updateHumanResources.EmployeesetHireDate='
2000-1-1'
select*fromPerson.ContactwhereContactID='
修改后的session2能避免死锁
select*fromPerson.ContactwhereContactID='
waitfordelay'
D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。
E、查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。
settransactionisolationlevelreaduncommitted
4、注意临时表和表变量的用法
在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：
A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。
B、如果需要多次用到一个大表或几个关联表的同一部分数据，考虑用临时表和表变量暂存这部分数据。
C、如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。
D、关于临时表和表变量的选择，对于较小的数据结果，或者是通过计算出来的结果集在选取的时候没有分组等聚合操作，推荐使用表变量；
对于大的数据结果，或者对后期数据的复杂处理性能要求很高，推荐使用临时表。
E、临时表在使用完后要及时对其显式删除，避免长时间占用资源。
F、临时表与表变量的区别
临时表
表变量
非簇索引
√
X
统计信息
默认值
约束
锁
事务
作用域
会话
存储过程、函数或批处理
宿主
内存
tempdb数据库
5、子查询的用法
子查询是一个SELECT查询，它嵌套在SELECT、INSERT、UPDATE、DELETE语句或其它子查询中。
任何允许使用表达式的地方都可以使用子查询。
子查询可以使编程灵活多样，但是也往往容易形成一个性能瓶颈。
如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。
相关子查询可以由IN、NOTIN、EXISTS、NOTEXISTS引入。
关于相关子查询，应该注意：
A、NOTIN、NOTEXISTS的相关子查询可以改用LEFTJOIN代替写法。
比如：
selectContactID
fromPerson.Contact
whereContactIDnotin
（selectContactIDfromHumanResources.Employee）
selecta.ContactID
fromPerson.Contacta
wherenotexists（selectContactIDfromHumanResources.EmployeewhereContactID=a.ContactID）
可以改写成：
leftjoinHumanResources.Employeebona.ContactID=b.ContactID
whereb.ContactIDisnull
B、如果子查询没有重复，IN、EXISTS的相关子查询可以用INNERJOIN代替。
selectName
fromProduction.Product
whereProductIDin
（selectProductIDfromProduction.ProductInventorywhereQuantity>
800）
selectb.Name
fromProduction.ProductInventorya
innerjoinProduction.Productbona.ProductID=b.ProductID
wherea.Quantity>
800
C、IN的相关子查询可用EXISTS代替，比如
selecta.Name
fromProduction.Producta
whereexists（select1fromProduction.ProductInventorywhereQuantity>
800andProductID=a.ProductID）
D、不要用COUNT（*）的子查询判断是否存在记录，最好用LEFTJOIN或者EXISTS，比如：
where（selectcount（*）fromHumanResources.EmployeewhereContactID=a.ContactID）=0
应该改成
where（selectcount（*）fromHumanResources.EmployeewhereContactID=a.ContactID）<
whereexists（select1fromHumanResources.EmployeewhereContactID=a.ContactID）
6、慎用游标
数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。
一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，把游标功能搬到客户端。
游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的。
很多时候可以用SQLSERVER的一些特性来代替游标，以达到提高速度的目的，只是在没有其他方法的情况下才使用游标。
A、字符串连接的例子
declare@namenvarchar（50）,@strnvarchar（2000）='
'
declarename_cursorcursorfor
selectFirstNamefromPerson.ContactwhereLastName='
Alan'
orderbyFirstName
openname_cursor
fetchnextfromname_cursorinto@name
while@@fetch_status=0
begin
set@str+=@name+'
Alan,'
fetchnextfromname_cursorinto@name
end
closename_cursor
deallocatename_cursor
select@str
可以如下修改，功能相同：
declare@strnvarchar（2000）='
select@str+=FirstName+'
fromPerson.ContactwhereLastName='
B、用CASEWHEN实现转换的例子，以下例子查询每个商品连续3年的年销售情况
declare@tbltable（ProductIDint,Qty_2002int,Qty_2003int,Qty_2004int）
insertinto@tbl（ProductID）
selectProductIDfromSales.SalesOrderDetailgroupbyProductIDorderbyProductID
declare@pidint
declarepid_cursorcursorfor
selectProductIDfrom@tbl
openpid_cursor
fetchnextfrompid_cursorinto@pid
while@@fetch_status=0
update@tbl
setQty_2002=（selectsum（a.OrderQty）
fromSales.SalesOrderDetaila
innerjoinSales.SalesOrderHeaderbona.SalesOrderID=b.SalesOrderID
wherea.ProductID=@pidandyear（b.OrderDate）=2002）
Qty_2003=（selectsum（a.OrderQty）
wherea.ProductID=@pidandyear（b.OrderDate）=2003）
Qty_2004=（selectsum（a.OrderQty）
wherea.ProductID=@pidandyear（b.OrderDate）=2004）
whereProductID=@pid
fetchnextfrompid_cursorinto@pid
closepid_cursor
deallocatepid_cursor
selectProductID,Qty_2002,Qty_2003,Qty_2004from@tbl
可以改写成：
selecta.ProductID
Qty_2002=sum（casewhenyear（b.OrderDate）=2002thena.OrderQtyend）
Qty_2003=sum（casewhenyear（b.OrderDate）=2003thena.OrderQtyend）
Qty_2004=sum（casewhenyear（b.OrderDate）=2004thena.OrderQtyend）
fromSales.SalesOrderDetaila
innerjoinSales.SalesOrderHeaderbona.SalesOrderID=b.SalesOrderID
groupbya.ProductIDorderbya.ProductID
C、引入辅助表实现转换的例子，以下例子查询每个商品上市后连续6周的周销售情况
declare@Producttable（ProductIDint,WeekNoint,Date1SmallDatetime,
升级会员 