小温谈数据库优化--数据库设计篇

简介:

三大范式:

第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,二进制字段的读写是比较慢的,而且,读取的方法也不多,大部分情况下最好不用。在sqlserver 2005尽可能使用nvarchar(max),或者varchar(max);除非必要图片尽量上传到服务器,数据库保留上传地址.

 

E、 自增字段要慎用

1.不利于数据迁移.

2.不利于分布式部署.

3.无法预知Id,为子表数据插入造成困难.

4.没有实际意义,无法让人看出这个数字到底有什么用.

F、尽可能使用定长数据类型,而不是变长数据类型

为什么不要设计过多的变长类型的数据呢?

1.对于 SQl Server 为说,变长类型的数据,在更新的时候,如果长度比以前的大,会进行页拆分。会对查询性能造成严重的影响。会增加查询时,O/I 的花费 (Cost) 页分隔越多,查询时,O/I 的开销就越大。对于变长的字段来说,有可能,这个字符的内容,存储在不同的位置。这个字段的内容,存储在不同的位置。存储在不同的页中, 它们之间有指针来关联。这种情况会造成查询时,磁头来回寻址,定位。可能你查一条记录,磁头找这条记录的这个变长字段的内容,都要去好几个页里找,才能完整的找到。这样,就造成了很大个 O/I 开销,降低了查询性能。从物理上来说,文件本来就经常容易产生碎片。再加上变长类型的页拆分。

页是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个数据页里.但是,同样,不但页拆分对增加查询时的 O/I 开销,字符不必要的太长,也会增加 O/I 开销。

2. 字段大小对表总大小有影响

SQL Server 2005单行字段总长是8060字节

3. 可变长类型是有长度限制的

 以上内容是第一篇,请大家多多发表意见!

 

 

本文转自温景良(Jason)博客园博客,原文链接:http://www.cnblogs.com/wenjl520/archive/2011/06/27/2091175.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
27天前
|
缓存 数据库 索引
面试题17: 数据库优化
面试题17: 数据库优化
|
1月前
|
缓存 监控 NoSQL
请解释一下云数据库的性能监控和优化。
请解释一下云数据库的性能监控和优化。
24 0
|
1月前
|
存储 监控 关系型数据库
Mysql数据库优化
Mysql数据库优化
|
1月前
|
缓存 数据库 索引
高效后端开发:数据库优化策略详解
数据库优化是后端开发中不可或缺的一部分。本文将从多个方面详细阐述数据库优化策略,包括数据表设计、索引优化、查询性能优化等。通过本文的学习,读者可以了解如何优化数据库,提升后端应用性能和稳定性。
24 1
|
5天前
|
SQL 监控 Java
Java Web应用中数据库连接池的配置与优化
Java Web应用中数据库连接池的配置与优化
|
7天前
|
存储 缓存 NoSQL
《优化数据库性能的关键技巧》
在当今信息爆炸的时代,数据库扮演着至关重要的角色。本文将分享一些关键的技巧,帮助开发人员优化数据库性能,提升系统的响应速度和稳定性。
|
20天前
|
监控 关系型数据库 MySQL
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
72 0
|
22天前
|
缓存 NoSQL PHP
百度搜索:蓝易云【如何使用PHP进行数据库索引优化?】
通过以上方法,你可以使用PHP进行数据库索引优化,提高数据库查询性能和整体应用性能。同时,定期维护数据库和优化查询语句也是保持数据库高性能的关键。
39 11
|
23天前
|
机器学习/深度学习 算法 数据库
深入浅出:利用Python与机器学习优化数据库性能
本文介绍了一种创新的方法,结合Python编程语言和机器学习技术,来优化数据库性能。传统的数据库性能优化方法往往依赖于数据库管理员(DBA)的经验和直觉,而本文所提出的方法通过自动化的方式,利用机器学习模型对数据库查询进行分析和优化,从而实现更高效、更智能的数据库性能管理。本文首先介绍了使用Python进行数据库操作的基础知识,然后详细阐述了如何应用机器学习算法来预测和改善数据库查询性能,最后通过一个实际案例展示了该方法的有效性。本文旨在为数据库管理员、开发者以及对数据库性能优化感兴趣的读者提供一种全新的视角和工具。
|
24天前
|
存储 大数据 数据库
深入理解数据库索引优化技术
本文将深入探讨数据库索引优化技术,包括索引的基本概念、常见的索引类型以及如何选择和创建适当的索引。通过了解索引的原理和优化策略,读者可以提高数据库查询性能并优化数据访问效率。

热门文章

最新文章