数据库优化--数据设计篇
三大范式:
第1规范:没有重复的组或多值的列,这是数据库设计的最低要求。
第2规范: 每个非关键字段必须依赖于主关键字,不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖,大部分情况下,数据库设计都应该达到第二范式。
第3规范: 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖,达到第三范式应该是系统中大部分表的要求,除非一些特殊作用的表。
更高的范式要求这里就不再作介绍了,个人认为,如果全部达到第二范式,大部分达到第三范式,系统会产生较少的列和较多的表,因而减少了数据冗余,也利于性能的提高。
完全按照三大范式规范化设计的系统几乎是不可能的,除非系统特别的小,在规范化设计后,有计划地加入冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段,不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加,也可以是为了性能的提高而增加。从性能角度来说,冗余数据库可以分散数据库压力,冗余表可以分散数据量大的表的并发压力,也可以加快特殊查询的速度,冗余字段可以有效减少数据库表的连接,提高效率。
主键的设计
主键是必要的,SQL SERVER的主键同时是一个唯一索引,而且在实际应用中,我们往往选择最小的键组合作为主键,所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的,这个在下面索引的叙述。
在有多个键的表,主键的选择也比较重要,一般选择总的长度小的键,小的键的比较速度快,同时小的键可以使主键的B树结构的层次更少。
主键的选择还要注意组合主键的字段次序,对于组合主键来说,不同的字段次序的主键的性能差别可能会很大,一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。
外键的设计
外键作为数据库对象,很多人认为麻烦而不用,实际上,外键在大部分情况下是很有用的,理由是:
外键是最高效的一致性维护方法,数据库的一致性要求,依次可以用外键、CHECK约束、规则约束、触发器、客户端程序,一般认为,离数据越近的方法效率越高。
谨慎使用级联删除和级联更新,级联删除和级联更新作为SQL SERVER 2000当年的新功能,在2005作了保留,应该有其可用之处。我这里说的谨慎,是因为级联删除和级联更新有些突破了传统的关于外键的定义,功能有点太过强大,使用前必须确定自己已经把握好其功能范围,否则,级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。
字段数据类型设计原则:
A、数据类型尽量用数字型,数字型的比较比字符型的快很多,尤其是作为主键。
B、 数据类型尽量小,这里的尽量小是指在满足可以预见的未来需求的前提下的,节省一个字节是一个字节,虽然硬盘便宜也不能浪费啊。
C、 尽量不要允许NULL,除非必要,可以用NOT NULL+DEFAULT代替。
为什么最好不要为null呢,因为在程序处理过程中,你经常要为null值进行处理,比如使用isnull进行判断,这样削弱查询的速度,还有程序中需要不断的为null值进行判断,多写了代码,减少了程序的性能。
D、少用TEXT和IMAGE,二进制字段的读写是比较慢的,而且,读取的方法也不多,大部分情况下最好不用。在SQL Server 2005尽可能使用nvarchar(max), 或者varchar(max); 除非必要图片尽量上传到服务器,数据库保留上传地址。
E、自增字段要慎用
1. 不利于数据迁移;
2. 不利于分布式部署;
3. 无法预知Id,为子表数据插入造成困难;
4. 没有实际意义,无法让人看出这个数字到底有什么用。
F、尽可能使用定长数据类型,而不是变长数据类型。
为什么不要设计过多的变长类型的数据呢?
1. 对于 SQl Server 为说,变长类型的数据,在更新的时候,如果长度比以前的大,会进行页拆分。会对查询性能造成严重的影响。会增加查询时,I/O 的花费 (Cost) 页分隔越多,查询时,I/O 的开销就越大。对于变长的字段来说,有可能这个字符的内容存储在不同的位置,这个字段的内容存储在不同的位置。存储在不同的页中, 它们之间有指针来关联。这种情况会造成查询时,磁头来回寻址,定位。可能你查一条记录,磁头找这条记录的这个变长字段的内容,都要去好几个页里找,才能完整的找到。这样,就造成了很大个 I/O 开销,降低了查询性能。从物理上来说,文件本来就经常容易产生碎片。再加上变长类型的页拆分。
页是SQL Server存储数据的基本单位,大小为8kb,可以存储表数据、索引数据、执行计划数据、分配位图、可用空间信息。页是SQL Server可以读写的最小I/O单位。即便是读取一行数据,它也要把整个页加载到缓存并从缓存中读取数据。
页拆分是这样产生的:
比如:有一个变长类型的字段 Content: nvarchar(512)。你添加一条记录,给 Content 的值是 N'ABC',那么,存储的时候,直接就存储 N'ABC' 了。当你下次 Update 这条记录的 Content 字段时,给的值是 N'ABCDEF',那么就会发生页拆分。DEF 对被存储在其它页。因为有可能上一次分配的数据页已经存储了其他行的数据对吧,对,512,只是用来限制这个字段的长度。并不与页拆分有关系。记录的物理顺序,与你 INSERT 的顺序是一致。你 INSERT 了 N条,然后再去修改第一条,这时候可能不在同一个页了。
以上结论就是把变长字段的内容加大,就会造成页拆分了。也就是说可变长类型是把一页填满,再填另一页,影响比较大的是,每次insert的时候会增加分配数据页的次数。
当然有可能造成一行数据保存在2个数据页里。但是,同样,不但页拆分对增加查询时的 I/O 开销,字符不必要的太长,也会增加 I/O 开销。
2. 字段大小对表总大小有影响
SQL Server 2005单行字段总长是8060字节。
3. 可变长类型是有长度限制的