数据库性能优化二:数据库表优化

转载 2015年07月07日 22:34:14
数据库优化包含以下三部分,数据库自身的优化,数据库表优化,程序操作优化.此文为第二部分

  数据库性能优化二:数据库表优化

 

  优化①:设计规范化表,消除数据冗余

  数据库范式是确保数据库结构合理,满足各种查询需要、避免数据库操作异常的数据库设计方式。满足范式要求的表,称为规范化表,范式产生于20世纪70年代初,一般表设计满足前三范式就可以,在这里简单介绍一下前三范式

先给大家看一下百度百科给出的定义:

第一范式(1NF)无重复的列

  所谓第一范式(1NF)是指在关系模型中,对域添加的一个规范要求,所有的域都应该是原子性的,即数据库表的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等非原子数据项。   

第二范式(2NF)属性

  在1NF的基础上,非码属性必须完全依赖于码[1NF基础上消除非主属性对主码的部分函数依赖] 

第三范式(3NF)属性

1NF基础上,任何非主属性不依赖于其它非主属性[2NF基础上消除传递依] 

通俗的给大家解释一下(可能不是最科学、最准确的理解)

  第一范式:属性(字段)的原子性约束,要求属性具有原子性,不可再分割;
  第二范式:记录的惟一性约束,要求记录有惟一标识,每条记录需要有一个属性来做为实体的唯一标识。
  第三范式:属性(字段)冗余性的约束,即任何字段不能由其他字段派生出来,在通俗点就是:主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们,当然外键除外)

如果数据库设计达到了完全的标准化,则把所有的表通过关键字连接在一起时,不会出现任何数据的复本(repetition)。标准化的优点是明显的,它避免了数据冗余,自然就节省了空间,也对数据的一致性(consistency)提供了根本的保障,杜绝了数据不一致的现象,同时也提高了效率。 

 

优化②:适当的冗余,增加计算列

  数据库设计的实用原则是:在数据冗余和处理速度之间找到合适的平衡点 

满足范式的表一定是规范化的表,但不一定是最佳的设计。很多情况下会为了提高数据库的运行效率,常常需要降低范式标准:适当增加冗余,达到以空间换时间的目的。比如我们有一个表,产品名称,单价,库存量,总价值。这个表是不满足第三范式的,因为总价值”可以由单价乘以数量得到,说明金额是冗余字段。但是,增加总价值”这个冗余字段,可以提高查询统计的速度,这就是以空间换时间的作法。合理的冗余可以分散数据量大的表的并发压力,也可以加快特殊查询的速度,冗余字段可以有效减少数据库表的连接,提高效率。

其中"总价值"就是一个计算列,在数据库中有两种类型:数据列和计算列,数据列就是需要我们手动或者程序给予赋值的列,计算列是源于表中其他的数据计算得来,比如这里的"总价值"

SQL中创建计算列:

create table table1
 (
   number decimal(18,4),
   price money,
   Amount as number*price --这里就是计算列
 ) 

也可以再表设计中,直接手动添加或修改列属性即可:如下图

 

是否持久性,我们也需要注意:

如果是'否',说明这列是虚拟列,每次查询的时候计算一次,而且那么它是不可以用来做check,foreign keynot null约束。 

如果是'是',就是真实的列,不需要每次都计算,可以再此列上创建索引等等。

 

优化③:索引

索引是一个表优化的重要指标,在表优化中占有极其重要的成分,所以将单独写一章”SQL索引一步到位“去告诉大家如何建立和优化索引

 

优化④:主键和外键的必要性

主键与外键的设计,在全局数据库的设计中,占有重要地位。 因为:主键是实体的抽象,主键与外键的配对,表示实体之间的连接。

主键:根据第二范式,需要有一个字段去标识这条记录,主键无疑是最好的标识,但是很多表也不一定需要主键,但是对于数据量大,查询频繁的数据库表,一定要有主键,主键可以增加效率、防止重复等优点。

主键的选择也比较重要,一般选择总的长度小的键,小的键的比较速度快,同时小的键可以使主键的B树结构的层次更少。
主键的选择还要注意组合主键的字段次序,对于组合主键来说,不同的字段次序的主键的性能差别可能会很大,一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

外键:外键作为数据库对象,很多人认为麻烦而不用,实际上,外键在大部分情况下是很有用的,理由是:外键是最高效的一致性维护方法

数据库的一致性要求,依次可以用外键、CHECK约束、规则约束、触发器、客户端程序,一般认为,离数据越近的方法效率越高。
谨慎使用级联删除和级联更新,级联删除和级联更新作为SQL SERVER 2000当年的新功能,在2005作了保留,应该有其可用之处。我这里说的谨慎,是因为级联删除和级联更新有些突破了传统的关于外键的定义,功能有点太过强大,使用前必须确定自己已经把握好其功能范围,否则,级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。

 

优化⑤:存储过程、视图、函数的适当使用

很多人习惯将复杂操作都放在应用程序层,但如果你要优化数据访问性能,将SQL代码移植到数据库上(使用存储过程,视图,函数和触发器)也是一个很大的改进原因如下:

1. 存储过程减少了网络传输、处理及存储的工作量,且经过编译和优化,执行速度快,易于维护,且表的结构改变时,不影响客户端的应用程序 

2、使用存储过程,视图,函数有助于减少应用程序中SQL复制的弊端,因为现在只在一个地方集中处理SQL

3、使用数据库对象实现所有的TSQL有助于分析TSQL的性能问题,同时有助于你集中管理TSQL代码,更好的重构TSQL代码

 

优化⑥:传说中的‘三少原则’

①:数据库的表越少越好

②:表的字段越少越好

③:字段中的组合主键、组合索引越少越好

当然这里的少是相对的,是减少数据冗余的重要设计理念。

 

优化⑦:分割你的表,减小表尺寸

  如果你发现某个表的记录太多,例如超过一千万条,则要对该表进行水平分割。水平分割的做法是,以该表主键的某个值为界线,将该表的记录水平分割为两个表。

如果你若发现某个表的字段太多,例如超过八十个,则垂直分割该表,将原来的一个表分解为两个表

 

优化⑧:字段设计原则

字段是数据库最基本的单位,其设计对性能的影响是很大的。需要注意如下:

A、数据类型尽量用数字型,数字型的比较比字符型的快很多。

B、 数据类型尽量小,这里的尽量小是指在满足可以预见的未来需求的前提下的。

C、 尽量不要允许NULL,除非必要,可以用NOT NULL+DEFAULT代替。

D、少用TEXTIMAGE,二进制字段的读写是比较慢的,而且,读取的方法也不多,大部分情况下最好不用。

E、 自增字段要慎用,不利于数据迁移

 

 

  以上可能部分文章借鉴了其他的网络文章,本文仅为学习使用,转载请注明出处

                                   --------------AK(老K):2012-12-28  

数据库性能优化详解

出处: https://www.cnblogs.com/easypass/archive/2010/12/ 08/1900127.html 1.数据库...
  • yzllz001
  • yzllz001
  • 2017年02月03日 17:38
  • 37716

数据库 设计与优化

三、设计技巧1.分类拆分数据量大的表。对于经常使用的表(如某些参数表或代码对照表),由于其使用频率很高,要尽量减少表中的记录数量。2.索引设计。在索引设计中,索引字段应挑选重复值较少的字段;在对建有复...
  • halemyan
  • halemyan
  • 2008年09月09日 16:26
  • 1873

《数据库索引设计优化》读书笔记(一)

第3章 SQL处理过程 练习 3.1 为SQL3.7中所示的查询设计尽可能好的索引: -- SQL 3.7 SELECT LNAME, FNAME, CNO FROM CUST WHE...
  • wzy0623
  • wzy0623
  • 2016年02月01日 14:56
  • 2022

数据库SQL优化大总结之 百万级数据库优化方案

网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。 这篇文章我花费了大量的时间查找资料、修改、排版,希望大家阅读之后,感觉...
  • zhushuai1221
  • zhushuai1221
  • 2016年06月23日 09:43
  • 25413

mysql 达到1亿级别的表如何设计优化

单表一亿?还是全库1亿? 1.首先可以考虑业务层面优化,即垂直分表。 垂直分表就是把一个数据量很大的表,可以按某个字段的属性或使用频繁程度分类,拆分为多个表。 如有多种业务类型,每种...
  • libaineu2004
  • libaineu2004
  • 2017年05月26日 16:19
  • 1101

数据库设计优化(来自十年经验架构师的课堂)

昨天听了一节公开课,上课的人是一个有十年左右开发经验的架构师,讲的是数据库的设计优化以及SQL语句优化,讲的很浅显易懂,在这里先把数据库设计优化分享给大家。 1.适度违反范式。 如果严格遵循三大范...
  • L_BestCoder
  • L_BestCoder
  • 2017年05月07日 09:20
  • 556

MYSQL设计优化

本文将从各方面介绍优化mysql设计的一些方式。
  • jiayichendddd
  • jiayichendddd
  • 2015年06月10日 17:40
  • 1170

数据库设计和优化

1.      主键&外键 键和外键是把多个表组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能和可用性都有着决定性的影响。必须将数据库模式从理论上的逻辑设计转换为实际的物理设计...
  • kingmicrosoft
  • kingmicrosoft
  • 2014年07月25日 11:11
  • 1061

数据库优化方案

SQL语句优化方案 1. 高效地进行SQL语句设计: 通常情况下,可以采用下面的方法优化SQL对数据操作的表现: (1)减少对数据库的查询次数,即减少对系统资源的请求,使用快照和显形图等分布...
  • axin66ok
  • axin66ok
  • 2012年08月21日 15:10
  • 39910

MySQL性能优化四之数据库结构优化

1.选择合适数据类型 1.1.使用可以存储你数据类型的最小数据类型。 1.2.使用简单的数据类型。 1.3.尽量使用NOT NULL定义字段。 1.4.尽量少用大的类型(text),非用不可使...
  • moHedong
  • moHedong
  • 2016年08月29日 12:12
  • 865
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据库性能优化二:数据库表优化
举报原因:
原因补充:

(最多只允许输入30个字)