精华内容
下载资源
问答
  • 所以在实际数据库物理设计中需要综合平衡考虑后决定要建立什么样的索引。 设计2条SQL语句,这2条语句查询需求相同,但1条使用索引,另1条不使用索引。将这2条SQL语句同时提交给DBMS,比较它们在执行效果、执行速度、...
  • 数据库物理设计

    万次阅读 2018-04-22 09:08:33
    物理设计就是根据所选择的关系型数据库的特点对逻辑模型进行存储结构设计。它涉及的内容包含以下4方面:1. 定义数据库、表及字段的命名规范;2. 选择合适的存储引擎;3. 为表中的字段选择合适的数据类型;4. 建立...

    物理设计

    物理设计就是根据所选择的关系型数据库的特点对逻辑模型进行存储结构设计。它涉及的内容包含以下4方面:1. 定义数据库、表及字段的命名规范;2. 选择合适的存储引擎;3. 为表中的字段选择合适的数据类型;4. 建立数据库结构。

    定义数据库、表及字段的命名规范

    • 数据库、表及字段的命名要遵守可读性原则

    • 数据库、表及字段的命名要遵守表意性原则

    • 数据库、表及字段的命名

    存储引擎

    • MyISAM存储引擎是非事务的,锁粒度是表级的,主要应用于select,insert,不适合应用于读写操作频繁的场景,因为对于读写操作会进行锁表操作。
    • MRG_MYISAM和MyISAM差不多
    • Innodb存储引擎是支持事务,支持MVCC行级锁,适合任何场景
    • Archive存储引擎不支持事务,支持行级锁,支持insert、select,适用于随机读取、更新、删除。
    • Ndb Cluster是MySQL集群存储引擎 ,支持事务,支持行级锁,具有高可用性

    数据类型

    原则:当一个列可以选择多种数据类型时,应该优先考虑数值类型,其次是日期或二进制类型,最后是字符类型。对于相同级别的数据

    类型,应该优先选择占用空间小的数据类型。

    1. 数值类型

    数值类型可以分为整数类型和实数类型。

    image.png

    其中,M表示整数类型的最大显示宽度; 对于浮点和定点类型,M是可以存储的总位数(精度);对于字符串类型,M是最大长度。 M

    的最大允许值取决于数据类型。注意:当我们只存储两位数时,一种错误用法就是int(2),其实int(i)存储空间是由数据类型和是否是

    unsigned决定的,i只是表示显示长度。int(11)和int(1)的存储空间是没差别的, int(11)的数据长度如果小于11,则默认补充空格,如果

    int (11)字段被zerofill修饰,则默认补充0,而且字段被zerofill修饰会自动添加unsigned。因此,当我们只存储两位数时,使用tinyint才能

    达到我们简约空间的目的。

    注意:Decimal类型是精确类型的,如果我们需要存储精确数据,例如财务数据就必须使用Decimal类型,而不能使用float和double类型。

    2. 字符串类型

    字符串类型中有两种类型:char和varchar。
    image.png

    varchar(n)的存储规范
    • varchar存储变长内容,varchar需要额外的空间记录内容长度,当内容小于255字节时,需要一个额外字节,当内容大于255字节时,需要2个额外字节;

    • 在jbk编码下,char占2个字节,在UTF-8编码下,char占用3个字节;

    • MySQL每行最多存储65535个字节;

    • varchar中的第1个字节表示是否为空,第二个字节和第3个字节表示长度,剩下字节表示实际内容,因此最大可用存储65535-1-2=65532 ;

    create table t1(c varchar(n)) charset=gbk,则此处n的最大值为(65535-1-2)/2=32766
    
    create t(c int ,c2 char(30),c3 varchar(n)) charset=utf8,则此处n的最大值(65535-1-2-4-90)/3=21812

    * varchar(100)中的100指的是100个字符数量;

    • 使用场景:字符串列的最大长度比平均长度大很多字符串列很少被更新,由于varchar类型长度不固定,那么进行更新时,可

    能导致存储页的分裂,引起存储碎片;使用多字节字符集存储字符串,以UTF-8为例,存储中文需要3个字节,而存储英文或数字只需

    要1个字节。

    char(n)的存储规范
    • char类型是定长的;

    • 字符串存储在char类型的列中会删除末尾的空格(见下图);

    • char类型的最大宽度是255字节。

    • 适用场景: char类型适合存储长度近似的值,例如身份证、md5值,手机号等;char类型适合存储短字符串,例如性别字段,

    使用char(1)就比varchar(1)更节省空间,因为varchar还需要存储额外字节存储其他信息;char类型适合存储经常更新的字符序列

    由于char类型的长度是固定的,MySQL会一次性地分配存储空间,在多次更新时也不会出现页分裂的情况,减少存储碎片。

    image.png

    3. 日期类型

    image.png

    注意:timestamp占4byte,实际上是用int存储的。由于只有4byte,因此它只能显示1970-01-01 到 2038-01-19,也正是这个原因,如

    果在这个时间内的字段推荐使用timestamp。timestamp类型显示依赖于所指定的时区。除此之外,还需要注意一点,**在行的数据

    修改时可以自动修改timestamp列的值**,这个特性可以帮助我们在进行数据分析时自动提取出最新变化的数据。

    MySQL5.5 datetime类型字段不能设置默认值为now()

    MySQL5.6 datetime类型字段可以设置默认值为now()

    • from_unixtime():数字转换成时间

    • unix_timestamp():时间转换成数字

    • Date_sub/DATE_ADD:对时间进行加减

    • CURDATE():将当前日期按照’YYYY-MM-DD’

    • NOW():返回当前日期和时间值

    建立数据库结构

    1. DML(Data manipulation language,数据操作语言)

    • DML分成数据查询和数据更新两类,数据更新又分成插入、删除和修改三种操作,主要命令:select、insert、delete、update、merge、call、explain、plan

    2. DDL(Data definition language,数据定义语言)

    • DDL包括两种类型数据:数据字典以及数据类型和结构定义。主要命令:create 、alter、drop、truncate、rename、comment
    • Create,用于创建语句,用于创建表或者数据库或者存储过程或者其他内容
    create table tea(id int not null auto_increment,name varchar(6) not null,remark char(6),primary key(id));
    show create table tea;
    • Alter,一般用于添加或者修改表中的字段名或者字段定义。也可以用于修改字段顺序等。同样的也可以用于修改数据库的名字或者编码
    修改字段定义:
    ALTER TABLE emp MODIFY first_name VARCHAR(20) NOT NULL DEFAULT '-';  
    添加字段:
    alter table emp add column age int(3) not null default 0;  
    修改字段名字,同时修改定义:
    alter table emp change age age1 int(4) not null default 0;  
    在表Column的尾部追加新的column:
    alter table emp add birth date not null after empno;  
    移动column到表column的首位:
    alter table emp modify age1 int(3) not null default 0 first;  
    将某个column移动到另一个columon后面:
    alter table department change deptno deptno int(11) NOT NULL after deptname;  
    删除主键:
    alter visit_log drop primary key;  
    修改表名:
    alter table emp rename employees; 
    删除字段
    alter table guess_product_info  drop column backstyle;

    3. DCL(Data Control Language,数据控制语言)

    • 设置或更改数据库用户或角色权限,包括对基本表和视图的授权,完整性规则的描述,事务控制等内容。
    • grant
    赋予权限
    grant all privileges on *.* to 'root'@'localhost' with grant option; 授权远程用户注意不要with grant option
    grant select,update,create,delete on *.* to guest@'115.220.46.%' identified by 'test@home';创建业务用户,并要求只限制IP段
    查看权限
    Select user,password,host from mysql.user;
    Show grants for root@'localhost';
    select user,password,host ,grant_priv,super_priv from mysql.user;
    重命名用户
    rename mysql.user root@' ' to admin@' ';
    刷新权限
    flush privileges
    回收权限
    revoke select,insesrt,update,delete,create on *.* from 'guest'@'115.220.46.%';
    show grants for guest@'115.220.46.%';
    修改密码
    update mysql.user set password =password('test') where user='guest';password()函数对字符串进行MD5加密
    mysqladmin -uroot -ptest password 12345
                                        -p 旧密码  password 新密码
    
    忘记密码
    mysqld_safe --defaults-file=/etc/mysql/my3306.cnf --skip-grant-tables &
    删除用户
    drop user root@'';
    删除test数据库
    select * from mysql.db where db like '%test%'\G;
    delete from mysql.db where db like '%test%';
    删除不需要的用户
    
    mysql> select user,password,host from mysql.user;
    +------+-------------------------------------------+------------+
    | user | password                                  | host       |
    +------+-------------------------------------------+------------+
    | root |                                           | localhost  |
    | root |                                           | nbview.com |
    | root |                                           | 127.0.0.1  |
    | root |                                           | ::1        |
    |      |                                           | localhost  |
    |      |                                           | nbview.com |
    | kewy | kewy126@home                              | %          |
    | root | *08F411191A8F7130F09F0A961DB8E87983620D5B | %          |
    +------+-------------------------------------------+------------+
    8 rows in set (0.00 sec)
    mysql> delete from mysql.user where user='kewy';
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id:    59
    Current database: student_db1
    Query OK, 1 row affected (0.01 sec)
    mysql> delete from mysql.user where user='root' and host='::1';
    Query OK, 1 row affected (0.00 sec)
    mysql> delete from mysql.user where user='root' and host='nbview.com';
    Query OK, 1 row affected (0.00 sec)
    mysql> delete from mysql.user where host='nbview.com';
    Query OK, 1 row affected (0.00 sec)
    
    mysql> delete from mysql.user where host='localhost' and user is null;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from mysql.user where host='127.0.0.1' ;
    Query OK, 1 row affected (0.00 sec)
    mysql> select user,password,host from mysql.user;
    +------+-------------------------------------------+-----------+
    | user | password                                  | host      |
    +------+-------------------------------------------+-----------+
    | root |                                           | localhost |
    | root | *08F411191A8F7130F09F0A961DB8E87983620D5B | %         |
    +------+-------------------------------------------+-----------+
    2 rows in set (0.00 sec)

    4. TCL(Transaction Control Language,事务控制语言)

    • TCL用于控制事务内执行流程的语言
    • start transaction|Begin:开始一个事务
    • Savepoint:创建一个记录点,方便回滚到这个地方
    • Rollback:回滚事务
    • Commit:提交事务
    开始事务
    START TRANSACTION  
    可选:创建一个存档点
    SAVEPOINT sp  
    开始操作SQL
    select  
    insert  
    ...
    操作完成提交数据
    COMMIT  
    或者,操作失败,回滚数据(回滚到某个存档点)
    ROLLBACK  
    ROLLBACK TO sp  
    释放存档点
    RELEASE SAVEPOINT sp  

    总结

    在进行数据库物理设计时,我们需要计算每张表的存储空间, 选择存储引擎和表中的数据类型。


    image

    欢迎关注微信公众号:木可大大,所有文章都将同步在公众号上。

    展开全文
  • 数据库数据库物理设计

    千次阅读 2019-09-03 09:30:53
    为一个给定的逻辑数据模型选取一个最合适应用要求的物理结构的过程,称为数据库物理设计物理设计的目的是有效地实现逻辑模式,确定所采取的存储策略。此阶段是以逻辑设计的结构作为输入,并结合具体DBMS的特点与...

    数据库在物理设备上的存储结构与存取方法称为数据库的物理结构,它依赖于给定的计算机系统。为一个给定的逻辑数据模型选取一个最合适应用要求的物理结构的过程,称为数据库的物理设计。物理设计的目的是有效地实现逻辑模式,确定所采取的存储策略。此阶段是以逻辑设计的结构作为输入,并结合具体DBMS的特点与存储设备特性进行设计,选定数据库在物理设备上存储结构和存取方法。数据库的物理设计可分为两步:

    • 确定数据库的物理结构,在关系数据库中主要指存储结构和存取方法。

    • 对物理结构进行评价,评价的重点是时间和空间效率。

    如果评价结果满足原设计要求,则可进入物理实施阶段,否则需要重新设计或修改物理结构,有时甚至要返回逻辑设计阶段修改数据模型。

    关系模式存取方法选择

    数据库系统是多用户共享的系统,对同一个关系要建立多条存取路径才能满足多用户的多种应用要求。物理设计的任务之一就是要确定选择哪些存取方法,即建立哪些存取路径。存取方法是快速存取数据库中数据的技术。数据库管理系统一般都提供多种存取方法,常用的存取方法有3类:索引方法、聚簇(Cluster)方法和HASH方法。

    索引存取方法的选择

    在关系数据库中,索引是一个单独的、物理的数据结构,它是某个表中一列或若干列的集合和相应指向表中物理标识这些值的数据页的逻辑指针清单。索引可以提高数据的访问速度,可以确保数据的唯一性。索引存取方法就是根据应用要求确定对关系的哪些属性列建立索引、哪些属性列建立组合索引、哪些索引要设计为唯一索引等。

    • 如果一个(或一组)属性经常在查询条件中出现,则考虑在这个(或这组)属性上建立索引(或组合索引)。

    • 如果一个属性经常作为最大值或最小值等聚集函数的参数,则考虑在这个属性上建立索引。

    • 如果一个(或一组)属性经常在连接操作的连接条件中出现,则考虑在这个(或这组)属性上建立索引。

    关系上定义的索引数并不是越多越好,因为系统为维护索引要付出代价,并且查找索引也要付出代价。例如,若一个关系的更新频率很高,这个关系上定义的索引就不能太多。因为更新一个关系时,必须对这个关系上有关的索引做相应的修改。

    聚簇存取方法的选择

    为了提高某个属性或属性组的查询速度,把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块称为聚簇。创建聚簇可以大大提高按聚簇码进行查询的效率。

    设计聚簇的规则

    凡符合下列条件之一,均可以考虑建立聚簇:

    • 对经常在一起进行连接操作的关系可以建立聚簇。

    • 如果一个关系的一组属性经常出现在相等比较条件中,则该关系可建立聚簇。

    • 如果一个关系的一个或一组属性上的值的重复率很高,即对应每个聚簇码值的平均元组数不是太少,则可以建立聚簇。如果元组数太少,则聚簇的效果不明显。

    凡存在下列条件之一,均应考虑不建立聚簇:

    • 需要经常对全表进行扫描的关系。

    • 在某属性列上的更新操作远多于查询和连接操作的关系。

    使用聚簇需要注意的问题

    • 一个关系最多只能加入一个聚簇。

    • 聚簇对于某些特定应用可以明显地提高性能,但建立聚簇和维护聚簇的开销很大。

    • 在一个关系上建立聚簇,将移动关系中的元组的物理存储位置,并使此关系上的原有索引无效,必须重建。

    • 因为当一个元组的聚簇码值改变时,该元组的存储也要做相应的移动,所以聚簇码值要相对稳定,以减少修改聚簇码值所引起的维护开销。

    因此,通过聚簇码进行访问或连接是关系的主要应用,与聚簇码无关的其他访问很少或者是次要时,可以使用聚簇。当SQL语句中包含有与聚簇码有关的ORDER_BY、GROUP_BY、UNION、DISTINCT等子句或短语时,使用聚簇特别有利,可以省去对结果集的排序操作;否则很可能会适得其反。

    HASH存取方法的选择

    有些数据库管理系统提供了HASH存取方法。选择HASH存取方法的规则如下:

    • 如果一个关系的属性主要出现在等值连接条件中或相等比较选择条件中,并且满足下列两个条件之一时,则此关系可以选择HASH存取方法。

      • 一个关系的大小可预知,并且不变。

      • 关系的大小动态改变,并且所选用的DBMS提供了动态HASH存取方法。

    确定数据库的存储结构

    确定数据库的物理结构主要是指确定数据的存放位置和存储结构,包括确定关系、索引、聚簇、日志、备份等的存储安排和存储结构;确定系统配置等。

    为了提高系统性能,应该根据实际应用将数据库中数据的易变部分与稳定部分、常存取部分、存取频率较低部分分开存放。有多个磁盘的计算机可以采用下面几种存取位置的分配方案。

    • 将表和该表的索引放在不同的磁盘中。在查询时,两个磁盘驱动器并行操作,提高了物理I/O读/写的效率。

    • 将比较大的表分别放在两个磁盘上,以加快存取速度,这在多用户环境下特点有效。

    • 将日志文件与数据库的对象放在不同的磁盘上,以改进系统的性能。

    • 对于经常存取或存取时间要求高的对象应放在高速存储器上;对于存取频率小或存取时间要求低的对象(如数据库的数据备份和日志文件备份等,只在故障恢复时才使用),如果数据量很大,则可以存放在低速存储设备上。

    DBMS产品一般都提供了一些系统配置变量、存储分配参数,以供设计人员和DBS对数据库进行物理优化。在初始情况下,系统都为这些变量赋予了合理的默认值。这些初始值并不一定适合每种应用环境,在进行物理设计时,需要重新对这些变量赋值,以改善系统的性能。系统配置变量很多,例如,同时使用数据库的用户数、同时打开数据库的对象数、内存分配参数、缓冲区分配参数(使用的缓冲区长度、个数)、存储分配参数、物理块的大小、物理块装填因子、时间片大小、数据库的大小、锁的数目等。这些参数值会影响存取时间和存储空间的分配,因此在进行物理设计时,要根据应用环境确定这些参数值,以使系统性能最佳。

    (最近更新:2019年09月03日)

    展开全文
  • 数据库 - 物理设计

    万次阅读 2015-05-08 10:39:29
    为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程,就是数据库的物理设计数据库物理设计的步骤 确定数据库的物理结构,在关系数据库中主要指存取方法和存储结构 对物理结构进行评价,评价的重点是...

    数据库的物理设计

    数据库在物理设备上的存储结构与存取方法称为数据库的物理结构,它依赖于选定的数据库管理系统
    为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程,就是数据库的物理设计

    数据库物理设计的步骤
    确定数据库的物理结构,在关系数据库中主要指存取方法和存储结构
    对物理结构进行评价,评价的重点是时间和空间效率
    如果评价结果满足原设计要求,则可进入到物理实施阶段,否则,就需要重新设计或修改物理结构,有时甚至要返回逻辑设计阶段修改数据模型

    数据库物理设计的内容和方法

    设计物理数据库结构的准备工作
    对要运行的事务进行详细分析,获得选择物理数据库设计所需参数
    充分了解所用RDBMS的内部特征,特别是系统提供的存取方法和存储结构
    选择物理数据库设计所需参数
    数据库查询事务
    查询的关系
    查询条件所涉及的属性
    连接条件所涉及的属性
    查询的投影属性

    选择物理数据库设计所需参数(续)
    数据更新事务
    被更新的关系
    每个关系上的更新操作条件所涉及的属性
    修改操作要改变的属性值
    每个事务在各关系上运行的频率和性能要求

    关系数据库物理设计的内容
    为关系模式选择存取方法(建立存取路径)
    设计关系、索引等数据库文件的物理存储结构

    关系模式存取方法选择

    数据库系统是多用户共享的系统,对同一个关系要建立多条存取路径才能满足多用户的多种应用要求
    物理设计的任务之一就是要确定选择哪些存取方法,即建立哪些存取路径
    DBMS常用存取方法
    索引方法
    目前主要是B+树索引方法
    经典存取方法,使用最普遍
    聚簇(Cluster)方法
    HASH方法

    选择索引存取方法

    根据应用要求确定
    对哪些属性列建立索引
    对哪些属性列建立组合索引
    对哪些索引要设计为唯一索引

    选择索引存取方法的一般规则
    如果一个(或一组)属性经常在查询条件中出现,则考虑在这个(或这组)属性上建立索引(或组合索引)
    如果一个属性经常作为最大值和最小值等聚集函数的参数,则考虑在这个属性上建立索引
    如果一个(或一组)属性经常在连接操作的连接条件中出现,则考虑在这个(或这组)属性上建立索引
    关系上定义的索引数过多会带来较多的额外开销
     维护索引的开销
     查找索引的开销
    

    聚簇

    为了提高某个属性(或属性组)的查询速度,把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块称为聚簇

    聚簇的用途

    1. 大大提高按聚簇码进行查询的效率
      例:假设学生关系按所在系建有索引,现在要查询信息系的所有学生名单。
      信息系的500名学生分布在500个不同的物理块上时,至少要执行500次I/O操作
      如果将同一系的学生元组集中存放,则每读一个物理块可得到多个满足查询条件的元组,从而显著地减少了访问磁盘的次数

    2. 节省存储空间
      聚簇以后,聚簇码相同的元组集中在一起了,因而聚簇码值不必在每个元组中重复存储,只要在一组中存一次就行了

    聚簇的局限性

    1. 聚簇只能提高某些特定应用的性能
    2. 建立与维护聚簇的开销相当大
      对已有关系建立聚簇,将导致关系中元组移动其物理存储位置,并使此关系上原有的索引无效,必须重建
      当一个元组的聚簇码改变时,该元组的存储位置也要做相应移动

    聚簇的适用范围
    1. 既适用于单个关系独立聚簇,也适用于多个关系组合聚簇
    例:假设用户经常要按系别查询学生成绩单,这一查询涉及学生关系和选修关系的连接操作,即需要按学号连接这两个关系,为提高连接操作的效率,可以把具有相同学号值的学生元组和选修元组在物理上聚簇在一起。这就相当于把多个关系按“预连接”的形式存放,从而大大提高连接操作的效率。

    1. 当通过聚簇码进行访问或连接是该关系的主要应用,与聚簇码无关的其他访问很少或者是次要的时,可以使用聚簇。
      尤其当SQL语句中包含有与聚簇码有关的ORDER BY,GROUP BY,UNION,DISTINCT等子句或短语时,使用聚簇特别有利,可以省去对结果集的排序操作
    设计候选聚簇
    对经常在一起进行连接操作的关系可以建立聚簇
    如果一个关系的一组属性经常出现在相等比较条件中,则该单个关系可建立聚簇
    如果一个关系的一个(或一组)属性上的值重复率很高,则此单个关系可建立聚簇。即对应每个聚簇码值的平均元组数不太少。太少了,聚簇的效果不明显
    

    优化聚簇设计
    从聚簇中删除经常进行全表扫描的关系;
    从聚簇中删除更新操作远多于连接操作的关系;
    不同的聚簇中可能包含相同的关系,一个关系可以在某一个聚簇中,但不能同时加入多个聚簇
    从这多个聚簇方案(包括不建立聚簇)中选择一个较优的,即在这个聚簇上运行各种事务的总代价最小

    选择HASH存取方法的规则

    当一个关系满足下列两个条件时,可以选择HASH存取方法
    该关系的属性主要出现在等值连接条件中或主要出现在相等比较选择条件中
    该关系的大小可预知,而且不变;

    该关系的大小动态改变,但所选用的DBMS提供了动态HASH存取方法

    确定数据库的存储结构

    确定数据库物理结构的内容
    1. 确定数据的存放位置和存储结构
    关系
    索引
    聚簇
    日志
    备份
    2. 确定系统配置
    确定数据存放位置和存储结构的因素
    存取时间
    存储空间利用率
    维护代价
    这三个方面常常是相互矛盾的
    例:消除一切冗余数据虽能够节约存储空间和减少维护代价,但往往会导致检索代价的增加
    必须进行权衡,选择一个折中方案

    确定数据的存放位置

    基本原则
    根据应用情况将
    易变部分与稳定部分分开存放
    存取频率较高部分与存取频率较低部分,分开存放

    例:
    数据库数据备份、日志文件备份等由于只在故障恢复时才使用,而且数据量很大,可以考虑存放在磁带上
    如果计算机有多个磁盘或磁盘阵列 ,可以考虑将表和索引分别放在不同的磁盘上,在查询时,由于磁盘驱动器并行工作,可以提高物理I/O读写的效率 
    
    例(续):
    可以将比较大的表分别放在两个磁盘上,以加快存取速度,这在多用户环境下特别有效
    可以将日志文件与数据库对象(表、索引等)放在不同的磁盘以改进系统的性能
    

    DBMS产品一般都提供了一些存储分配参数
    同时使用数据库的用户数
    同时打开的数据库对象数
    内存分配参数
    使用的缓冲区长度、个数
    存储分配参数
    …….

    评价物理结构

    评价内容
    对数据库物理设计过程中产生的多种方案进行细致的评价,从中选择一个较优的方案作为数据库的物理结构
    评价方法(完全依赖于所选用的DBMS )
    定量估算各种方案
    存储空间
    存取时间
    维护代价
    对估算结果进行权衡、比较,选择出一个较优的合理的物理结构
    如果该结构不符合用户需求,则需要修改设计

    数据库结构建立好后,就可以向数据库中装载数据了。组织数据入库是数据库实施阶段最主要的工作。

    数据装载方法
    人工方法
    计算机辅助数据入库

    数据库的试运行

    强调两点:
    分期分批组织数据入库
    重新设计物理结构甚至逻辑结构,会导致数据重新入库。
    由于数据入库工作量实在太大,费时、费力,所以应分期分批地组织数据入库
    先输入小批量数据供调试用
    待试运行基本合格后再大批量输入数据
    逐步增加数据量,逐步完成运行评价

    数据库的转储和恢复
    在数据库试运行阶段,系统还不稳定,硬、软件故障随时都可能发生
    系统的操作人员对新系统还不熟悉,误操作也不可避免
    因此必须做好数据库的转储和恢复工作,尽量减少对数据库的破坏。

    数据库的运行与维护

    数据库试运行合格后,数据库即可投入正式运行。
    数据库投入运行标志着开发任务的基本完成和维护工作的开始
    对数据库设计进行评价、调整、修改等维护工作是一个长期的任务,也是设计工作的继续和提高。
    应用环境在不断变化
    数据库运行过程中物理存储会不断变化

    在数据库运行阶段,对数据库经常性的维护工作主要是由DBA完成的,包括:
    数据库的转储和恢复
    数据库的安全性、完整性控制
    数据库性能的监督、分析和改进
    数据库的重组织和重构造

    数据库的重组织和重构造
    重组织的形式
    全部重组织
    部分重组织
    只对频繁增、删的表进行重组织
    重组织的目标
    提高系统性能

    重组织的工作
    按原设计要求
    重新安排存储位置
    回收垃圾
    减少指针链
    数据库的重组织不会改变原设计的数据逻辑结构和物理结构

    数据库重构造
    根据新环境调整数据库的模式和内模式
    增加新的数据项
    改变数据项的类型
    改变数据库的容量
    增加或删除索引
    修改完整性约束条件

    数据库各级模式的形成
    数据库的各级模式是在设计过程中逐步形成的
    需求分析阶段综合各个用户的应用需求(现实世界的需求)
    概念设计阶段形成独立于机器特点、独立于各个DBMS产品的概念模式(信息世界模型),用E-R图来描述

    在逻辑设计阶段将E-R图转换成具体的数据库产品支持的数据模型如关系模型,形成数据库逻辑模式。然后根据用户处理的要求,安全性的考虑,在基本表的基础上再建立必要的视图(VIEW)形成数据的外模式
    在物理设计阶段根据DBMS特点和处理的需要,进行物理存储安排,设计索引,形成数据库内模式

    展开全文
  • 数据库设计(二)之物理设计

    千次阅读 2020-01-15 18:58:22
    物理设计要做什么 一.选择合适的数据库管理系统 Oracle、SqlServer、MySql及PgSQL 版权、成本考虑: 功能上的考虑: Oracle:业界内比较好的一种DBMS,性能很高,是适合大的事务操作。 操作系统上的考虑: Sql...

    物理设计要做什么

    一.选择合适的数据库管理系统 

    Oracle、SqlServer、MySql及PgSQL

    • 版权、成本考虑:

    • 功能上的考虑:

    Oracle:业界内比较好的一种DBMS,性能很高,是适合大的事务操作。

    • 操作系统上的考虑:

    SqlServerL只支持Windows系统下

    Oracle、MySql及PgSQL:可支持linux系统、windows系统

    • 开发语言上的考虑:

    如果使用.net开发语言,使用SQLServer比较好

    • 应用场景上的考虑:

    mysql及PgSQL适用于互联网项目

    Oracle、SqlServer适用于企业级项目(Oracle,如电信级、金融类,因为扩展性好,安全性高,SqlServer:中小型企业的erp)

    介绍Mysql常用的存储引擎

     

    二、定义数据库、表及字段的命名规范

    所有对象命名原则:

    1.可读性原则

     注意:大小写命名(驼峰),有的DBMS对大小写比较敏感,比如Mysql

    2.表意性原则

    对象的名字应该能够描述它所标识的对象。

    例如:表名,可以体现存储的数据内容

              对于存储过程,存储过程名称应该体现存储过程的功能

    3.长名原则

    尽可能少使用或者不使用缩写。

    适用于数据库DB名之外的任一对象

    这样的命名,当只看表名、字段名,完全不知道该表是干什么的。在开发和后期运维时,可读性很差,需要完全依赖手册。

    三.根据所选的DBMS系统选择合适的字段类型

     列的数据类型的选择:1.会影响数据存储空间的开销,2.影响数据查询的性能

    字段类型选择的原则:

     当某列可以选择多种数据类型时,应优先考虑数字类型,其次是日期或二进制类型,最后是字符类型

    对于相同级别的数据类型,应优先选择占用空间小的数据类型

     

     Mysql在MySQL内部使用4个字节INT类型来存放时间戳数据,时间戳类型,只能存储到2038年,因为现在已经2020年了,还是慎用哈。同理,int类型来存储时间也要慎用!

     以上原则主要从下面两个角度考虑:

    1.在对数据进行比较时(查询条件、join条件及排序)等操作时:同样的数据,字符处理往往比数字处理慢

     2.在数据库中,数据处理以页为单位,列的长度越小,利于性能提升

    数据库如何具体选字段类型:

    •  char与varchar如何选择?

    1.如果列中要存储的数据长度差不多一致的,应考虑用char;否则应该考虑用varchar 。如:身份证号,手机号

    2.如果列中最大数据长度小于50Byte,则一般也考虑char(如果该列很少用,则基于节省空间和减少I/O的考虑,还是选择用varchar)

    3.一般不宜定义大于50Byte的char类型列。(差不多15个字符)

    • decimal与float如何选择

     1.decimal用于存储精确数据,二float只能用于存储非精确数据。

    2.由于float的存储空间开销一般比decimal小,故非精确数据优先选择float类型

    • 时间类型如何存储

    1.使用int来存储时间字段的优缺点

    优点:字段长度比detetime小

    缺点:使用不方便,要进行函数转换

    限制:只能存储到2038-01-19 11:14:07,因为2^32为2147483648

    2.需要存储的时间粒度 

    年、月、日、小时、分、秒、周

    四、数据库设计其他注意事项:

    1.如何选择主键

    2.避免使用外键约束

    •  降低数据导入的效率,高并发环境影响很严重
    • 增加维护成本
    • 隋然不建议使用外键约束,但是相关联的列上一定要建立索引

    3.避免使用触发器

    1.降低数据导入的效率

    2.可能会出现意想不到的数据异常,尤其遇到业务需求变更

    3.使业务逻辑变得复杂 

    4.关于预留字段

     1.无法准确知道预留字段的类型。

    2.无法准确的知道预留字段中所存储的内容

    3.后期维护预留字段所要的成本,通增加一个字段所需要的成本是相同的

    4.严禁使用预留字段

     

    五.反范式化设计

     为了性能和读取效率的考虑,适当对第三范式的要求进行违反,而允许存在少量的数据冗余。以空间换时间。

     

    为什么反范式化

    1.减少表的关联数量

    2.增加数据的读取效率

    3、反范式化设计一定要适度

     

    展开全文
  • 数据库设计物理结构设计

    万次阅读 多人点赞 2018-07-03 10:58:59
    为一个给定的逻辑数据模型选取一个最适合应用要求的物理结构的过程,就是数据库物理设计。 一、数据库物理设计 确定数据库的物理结构,在关系数据库主要指存取方法和存储结构。 对物理结构进行评价,...
  • 数据库物理结构设计

    2021-03-31 16:52:46
    数据库在物理设备上的存储结构与存取方法称为数据库的物理结构,它依赖于选定的数据库管理系统 为一个给定的逻辑数据模型选取一个最合适的应用要求的物理结构的过程,就是数据库物理设计数据库物理设计通常...
  • MySQL数据库结构设计(物理设计)

    千次阅读 2021-01-19 03:45:40
    MySQL数据库物理设计涉及的内容1、定义数据库、表及字段命名规范命名要遵守可读性原则。比如使用下划线来分割不同的单词等遵守表意性原则。一看表名或字段名就知道是干什么的长名原则。尽量少使用缩写,但命名也不能...
  • 设计模式不必多,只要掌握其中关键的几个,再结合实际的业务需求,一个完整的数据库模型就可以推导出来。 这里主要介绍给大家的是四种主要必备的设计方案。
  • 关系数据库物理设计内容 为关系模式选择存取方法(建立存取路径) 设计关系、索引等数据库文件的物理存储结构
  • 数据库逻辑设计与物理设计

    千次阅读 2018-11-28 15:20:00
    应用程序数据模型设计通常分两个阶段。建立逻辑数据模型为第一阶段,它包括对应用程序...对关系数据库来讲,物理数据模型描述的是表、索引、视图、键和其它一些数据库特性。在传统的方法论中,逻辑数据模型设计往...
  • 数据库设计之物理设计

    千次阅读 2017-11-07 12:30:26
    4:反范式化设计物理设计的目标就是建立数据的表结构,所以,我们需要选择使用什么样的数据库系统来建立表结构,通常要通过需求的特点、数据库系统的特点、成本等因素来选择。 然后,建库建表的时候肯定要考虑,...
  • 数据库系统之物理设计

    千次阅读 2020-04-22 00:38:16
    数据库中数据的存放方式 一个数据库被映射到多个不同的文件,而这些文件由底层的操作系统来维护,这些文件会永久地存放在磁盘上,一个文件在逻辑上组织成记录的一个序列,这些记录映射到磁盘上。 每个文件被分成定长...
  • 数据库结构设计(逻辑设计和物理设计

    万次阅读 多人点赞 2018-11-15 12:28:27
    1、数据库结构设计的步骤 需求分析:全面了解产品设计的存储...物理设计:根据所用的数据库特点进行表结构设计 关系型数据库:Oracle、SQLServer、MySQL、postgresSQL 非关系型数据库:mongo、Redis、Hadoop ...
  • 数据库物理模型设计

    2013-04-10 08:19:59
    您会陆续看到浩浩荡荡的数据库设计实例连篇累牍,却都是利用四种基本模式设计出来的...
  • 数据库物理设计的步骤 确定数据库的物理结构 RDBMS中的主要指存取方法和存储结构 对物理结构进行评价 重点是时间和空间效率 IF 满足原设计要求 THEN 进入到物理实施阶段 ELSE ( 重新设计 OR 修改物理结构 OR 返回...
  • 可行性分析+概要设计+逻辑设计+物理设计文档。加上建好的数据库和建表语句以及ER图,各类图
  • 数据库设计物理文档 SQL Server 2005
  • 我认为讲得很不错。本章重点: 1. 数据库设计主要思想; 2. 数据库设计的步骤及其分解各项的设计方法。 。
  • 下面这位大佬写的非常棒,建议收藏 转载一个大佬的总结
  • 数据库原理(十 )- 物理结构设计前言 前言
  • 1引言 4 1.1编写目的 4 1.2背景 5 1.3定义 5 1.4参考资料 6 2数据库物理模型 7 2.1整体设计 7 2.2角色与权限管理 7 2.3消息管理 9 2.4用户信息 10 2.5分站信息表 12 2.6备份计划 13 2.7备份文件 14
  • 1.物理模型(思维导图) 2.数据库设计(sql)
  • SQL数据库设计(二) -- 物理设计

    千次阅读 2016-05-23 22:11:47
    今天主要介绍数据库设计的物理设计,上一篇文章已经讲了,数据库设计的需求分析和逻辑设计,如果你没有看到,请点击下面的连接: SQL数据库设计(一)—需求分析与逻辑设计 物理设计根据数据库自身的特点把逻辑设计转换...
  • 自己做的数据库课程设计包括需求分析、逻辑结构设计、概念设计物理结构设计数据库实施和维护、sql语句增删查改、存储过程触发器都有、还有各种图也画了
  • 文章目录0.一图总览1.数据库设计概述及六步骤简介2....物理结构设计---步骤四5.1 确定数据库物理结构5.2 物理结构进行评价6.数据库实施---步骤五7.数据库运行维护---步骤六 0.一图总览 1.数据库设计概述及
  • 目录 数据库优化的目的 数据库结构设计的步骤 数据库设计范式 数据库第一范式 数据库第二范式 ...物理设计涉及的内容 定义数据库表、及字段命名规范 选择适合的存储引擎 物理设计-数据类型的选择...
  • 数据库设计的基本步骤

    万次阅读 多人点赞 2017-08-13 20:52:16
    数据库设计的基本步骤 按照规范设计的方法,考虑数据库...在数据库设计过程中,需求分析和概念设计可以独立于任何数据库管理系统进行,逻辑设计和物理设计与选用的DAMS密切相关。   1.需求分析阶段(常用自顶向下)
  • 数据库设计物理结构设计

    万次阅读 2017-01-02 21:09:59
    数据库物理设计通常分为两步: 确定数据库的物理结构  对物理结构进行评价,评价的重点是时间和空间效率  1.确定数据库的物理结构 (1)确定数据的存储结构 确定数据库存储结构时要综合考虑存取时间、存储...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 236,785
精华内容 94,714
关键字:

数据库物理设计主要内容