数据库的设计与编码优化Word文件下载.docx

1、2NF实例分析以上设计存在以下问题a. 数据冗余，同一门课由n个学生选修，学分就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。b. 更新异常，若调整了某门课程的学分，表中所有行的学分值都要更新，否则会出现同一门课学分不同。c. 插入异常，如果计划开新课，由于没人选修，没有学号关键字，只能等有人选修才能把课程和学分存入。d. 删除异常，若学生已经结业，从当前数据库删除选修记录，与此同时，某门课程新生尚未选修，则此门课程及学分记录无法保存。原因是表列存在如下依赖关系学号 姓名、年龄、性别、系别、系办地址、系办电话课程名 课程学分学号、课程名 学科成绩解决方法拆分成3个表，使其

2、符合2NF学生 Student（学号、姓名、年龄、性别、系别、系办地址、系办电话）课程 Course（课程名、课程学分）选课关系 ChoseCourse（学号、课程名、学科成绩）3NF实例分析以上设计还存在以下问题a. 数据冗余，n个学生同属一个系，系办地址和系办电话就重复n-1次。b. 更新异常，若调整了某个系别的地址或电话，表中与该系别相关的所有行都要更新，否则会出现系别地址或电话不一致。c. 插入异常，如果计划设立一个新院系，由于没有学号关键字，只能等有人进入该院才能把院系信息存入。原因是表列还存在传递依赖非主关键字关系学号 系别 系办地址、系办电话将Student（学号、姓名、年龄、性

3、别、系别、系办地址、系办电话）表继续拆分成2个表，使其符合3NF学生 Student（学号、姓名、年龄、性别、系别）系别 Department（系别、系办地址、系办电话）至此，上面的数据表设计符合1NF、2NF、3NF ，消除了数据冗余，更新异常，插入异常与删除异常。2、 合理的冗余大系统的设计里，加入合理的冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。3、 字段的设计字段是数据库最基本的单位，其

4、设计对性能的影响很大：A、数据类型尽量用数字型，数字型的比较比字符型的快很多。B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。C、尽量不要允许NULL，除非必要，可以用NOT NULL + DEFAULT代替。NULL值会带来以下困扰。NULL 与任何值的比较都为假，比较结果select count（*） as total from Production.Product where ProductModelID22 or ProductModelID is nullNULL 被排除在集合汇总之外，比较结果select count（ProductModelID） as t

5、otal from Production.Product select count（*） as total from Production.ProductD、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多。E、真假、对错、男女等值可定义成 BIT类型，能减少存储空间与加快检索。F、一个表中所有字段的总长度尽量不要超过8KB，因为SQL Server中数据存储的基本单位是页，页的大小为 8 KB，减少数据分页能降低磁盘IO。4、 主键的设计主键是确保表不包含重复行的约束，主键同时是一个唯一索引，在实际应用中，选择最小的键组合作为主键，主键往往适合作为表的聚簇索引。在

6、有多个键的表，一般选择总的长度小的键作主键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。5、 外键的设计外键是强制引用完整性的约束，它是高效的一致性维护方法。数据库的一致性要求，依次可以用外键、CHECK约束、规则、触发器、客户端程序，离数据越近的方法效率越高。6、 索引的设计在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。A、 根据使用频率决定哪些字段需要建立索引，选择经常作为筛选条件、连接

7、条件、聚合查询、排序的字段作为索引的候选字段。B、 把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。C、 保持较窄的索引列，避免添加不必要的列。添加太多索引列对磁盘空间和索引维护性能会产生负面影响。D、多个索引可以提高一个表的查询的性能，但也不能加入太多索引，因为索引影响插入和更新的速度，加大对磁盘空间的占用和索引维护。E、根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。对数据量小的表建立多个索引可能不会产生优化效果。F、聚簇索引的效率高于非簇索引，对于聚簇索引，应保持短的索引键长度与低的重复率。7、

8、 系统设计整个系统的设计特别是系统结构设计对性能是有很大影响的，设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。二、 编码阶段 编码阶段首先是需要程序员有性能意识，也就是在实现功能同时有考虑性能的思想，数据库是能进行集合运算的工具，应该尽量的利用它，所谓集合运算实际是批量运算，就是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。下面罗列一些编程阶段需要注意的事项：1、 只返回需要的数据返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端

9、接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：A、横向来看，不要写SELECT *的语句，而是选择你需要的字段。B、纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。C、对于聚合查询，可以用HAVING子句进一步限定返回的行。2、 尽量少做重复的工作A、 控制同一语句的多次执行。B、 杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。C、 合并对同一表同一条件不同字段的多次UPDATE，比如 update Person.Contact set Fi

10、rstName=Gusta where ContactID=1update Person.Contact set LastName=Achon合并成,LastName= where ContactID=1 D、 单一的UPDATE操作不要拆成DELETE + INSERT操作的形式。3、 注意事务和锁事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作都需要利用事务来保证数据的正确性。在使用事务中需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：A、事务操作过程要尽量小，能拆分的事务要拆分开来。B、事务操作过程不应该有交互，因为交互等待的时候，事务

11、并未结束，可能锁定了很多资源。C、事务操作过程要按同一顺序访问对象，减少死锁的发生。以下示例为产生死锁的两个sessionsession 1set transaction isolation level repeatable readgobegin tran update Person.Contact set Phone= 849-555-1111 where ContactID=1005 waitfor delay 00:10 select * from HumanResources.Employee where ContactID=commit transession 2 update H

12、umanResources.Employee set HireDate=2000-1-1 select * from Person.Contact where ContactID=修改后的session 2 能避免死锁select * from Person.Contact where ContactID=waitfor delay D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。E、查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。set transaction isolation level read

13、uncommitted4、 注意临时表和表变量的用法在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。B、如果需要多次用到一个大表或几个关联表的同一部分数据，考虑用临时表和表变量暂存这部分数据。C、如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。D、关于临时表和表变量的选择，对于较小的数据结果，或者是通过计算出来的结果集在选取的时候没有分组等聚合操作，推荐使用表变量；对于大的数据结果，或者对后期数据的复杂处理性能要求很高，推荐使用临时表。E、临时表在使用完后要及时对其

14、显式删除，避免长时间占用资源。F、临时表与表变量的区别临时表表变量非簇索引X统计信息默认值约束锁事务作用域会话存储过程、函数或批处理宿主内存tempdb数据库5、 子查询的用法子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询可以使编程灵活多样，但是也往往容易形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以由IN、NOT IN、EXISTS、NOT EXISTS引入。关于相关子查询，应该注意：A、NOT IN、NOT EXI

15、STS的相关子查询可以改用LEFT JOIN代替写法。比如：select ContactIDfrom Person.Contact where ContactID not in （select ContactID from HumanResources.Employee）select a.ContactIDfrom Person.Contact awhere not exists（select ContactID from HumanResources.Employee where ContactID=a.ContactID）可以改写成： left join HumanResources.Em

16、ployee b on a.ContactID=b.ContactIDwhere b.ContactID is nullB、 如果子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。select Namefrom Production.Productwhere ProductID in （select ProductID from Production.ProductInventory where Quantity 800）select b.Namefrom Production.ProductInventory a inner join Production.P

17、roduct b on a.ProductID=b.ProductIDwhere a.Quantity 800 C、 IN的相关子查询可用EXISTS代替，比如select a.Namefrom Production.Product awhere exists（select 1 from Production.ProductInventory where Quantity 800 and ProductID=a.ProductID ）D、不要用COUNT（*） 的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS，比如：where （select count（*） from Hu

18、manResources.Employee where ContactID=a.ContactID）=0应该改成where （select count（*） from HumanResources.Employee where ContactID=a.ContactID）where exists（select 1 from HumanResources.Employee where ContactID=a.ContactID）6、 慎用游标数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客

19、户端的一个循环实现的功能，所以，大部分情况下，把游标功能搬到客户端。游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的。很多时候可以用SQL SERVER的一些特性来代替游标，以达到提高速度的目的，只是在没有其他方法的情况下才使用游标。A、字符串连接的例子declare name nvarchar（50） , str nvarchar（2000）=declare name_cursor cursor for select FirstName from Person.Contact where LastName=Alan order by

20、 FirstNameopen name_cursorfetch next from name_cursor into namewhile fetch_status=0begin set str+=name+ Alan, fetch next from name_cursor into nameendclose name_cursordeallocate name_cursorselect str可以如下修改，功能相同：declare str nvarchar（2000）=select str+=FirstName+ from Person.Contact where LastName=B、 用

21、CASE WHEN 实现转换的例子，以下例子查询每个商品连续3年的年销售情况 declare tbl table（ProductID int,Qty_2002 int,Qty_2003 int,Qty_2004 int）insert into tbl（ProductID ）select ProductID from Sales.SalesOrderDetail group by ProductID order by ProductIDdeclare pid intdeclare pid_cursor cursor for select ProductID from tblopen pid_cu

22、rsorfetch next from pid_cursor into pidwhile fetch_status = 0 update tbl set Qty_2002 =（ select sum（a.OrderQty） from Sales.SalesOrderDetail a inner join Sales.SalesOrderHeader b on a.SalesOrderID=b.SalesOrderID where a.ProductID=pid and year（b.OrderDate）=2002） ,Qty_2003 =（ select sum（a.OrderQty） whe

23、re a.ProductID=pid and year（b.OrderDate）=2003） ,Qty_2004 =（ select sum（a.OrderQty） where a.ProductID=pid and year（b.OrderDate）=2004） where ProductID=pid fetch next from pid_cursor into pidclose pid_cursordeallocate pid_cursorselect ProductID,Qty_2002,Qty_2003,Qty_2004 from tbl可以改写成：select a.ProductI

24、D ,Qty_2002=sum（case when year（b.OrderDate）=2002 then a.OrderQty end） ,Qty_2003=sum（case when year（b.OrderDate）=2003 then a.OrderQty end） ,Qty_2004=sum（case when year（b.OrderDate）=2004 then a.OrderQty end）from Sales.SalesOrderDetail a inner join Sales.SalesOrderHeader b on a.SalesOrderID=b.SalesOrderIDgroup by a.ProductID order by a.ProductID C、引入辅助表实现转换的例子，以下例子查询每个商品上市后连续6周的周销售情况declare Product table（ProductID int,WeekNo int ,Date1 SmallDatetime,