为您推荐:
精华内容
最热下载
问答
  • 5星
    14.58MB weixin_42460570 2021-03-08 18:21:17
  • 5星
    118KB qq_44629109 2020-12-31 22:54:13
  • 5星
    46.38MB weixin_40228600 2021-03-26 09:10:00
  • 5星
    552KB m0_52957036 2020-11-23 09:37:58
  • 5星
    641KB m0_52957036 2020-01-17 13:10:09
  • 5星
    75.83MB qq_43417276 2021-05-27 17:10:29
  • 5星
    299KB m0_52957036 2020-12-05 16:53:15
  • 5星
    118.23MB qq_21548021 2020-12-04 12:38:16
  • 5星
    965KB qq_43176127 2021-02-09 16:11:28
  • 5星
    645KB weixin_44573410 2021-01-30 15:23:12
  • 1.1数据库环境配置原则1.1.1操作系统环境:对于中小型数据库系统,采用linux操作系统比较合适,对于数据库冗余要求负载均衡能力要求较高的系统,可以采用Oracle9iRAC的集群数据库的方法,集群节点数范围在2—64个。...

    1.1数据库环境配置原则

    1.1.1操作系统环境:

    对于中小型数据库系统,采用linux操作系统比较合适,对于数据库冗余要求负载均衡能力要求较高的系统,可以采用Oracle9iRAC的集群数据库的方法,集群节点数范围在2—64个。对于大型数据库系统,可以采用SunSolarisSPARC64位小型机系统或HP9000系列小型机系统。RAD5适合只读操作的数据库,RAD1适合OLTP数据库

    1.1.2内存要求

    对于linux操作系统下的数据库,由于在正常情况下Oracle对SGA的管理能力不超过1.7G。所以总的物理内存在4G以下。SGA的大小为物理内存的50%—75%。对于64位的小型系统,Oracle数据库对SGA的管理超过2G的限制,SGA设计在一个合适的范围内:物理内存的50%—70%,当SGA过大的时候会导致内存分页,影响系统性能。

    1.1.3交换区设计

    当物理内存在2G以下的情况下,交换分区swap为物理内存的3倍,当物理内存>2G的情况下,swap大小为物理内存的1—2倍。

    1.1.4其他环境变量参考Oracle相关的安装文档和随机文档。

    1.2数据库设计原则

    1.2.1数据库SID

    数据库SID是唯一标志数据库的符号,命名长度不能超过5个字符。对于单节点数据库,以字符开头的5个长度以内字串作为SID的命名。对于集群数据库,当命名SID后,各节点SID自动命名为SIDnn,其中nn为节点号:1,2,…,64。例如rac1、rac2、rac24。

    1.2.2数据库全局名

    数据库全局名称:

    .domain

    1.2.3数据库类型选择

    对于海量数据库系统,采用datawarehouse的类型。对于小型数据库或OLTP类型的数据库,采用TransactionProcessing类型。

    1.2.4数据库连接类型选择

    Oracle数据库有专用服务器连接类型和多线程服务器MTS连接类型。对于批处理服务,需要专用服务器连接方式,而对于OLTP服务则MTS的世界数据报告连接方式比较合适。由于采用MTS后,可以通过配置网络服务实现某些特定批处理服务采用专用服务器连接方式,所以数据库设计时一般采用MTS类型。

    1.2.5数据库SGA配置

    数据库SGA可以采用手工配置或按物理内存比例配置,在数据库初始设计阶段采用按比例配置方式,在实际应用中按系统调优方式修改SGA。

    1.2.6数据库字符集选择

    为了使数据库能够正确支持多国语言,必须配置合适的数据库字符集,采用UTF8字符集。

    注意:如果没有大对象,在使用过程中进行语言转换没有什么影响,具体过程如下(切记设定的字符集必须是ORACLE支持,不然不能start)

    SQL>shutdownimmediate;

    SQL>startupmount;

    SQL>altersystemenablerestrictedsession;

    SQL>altersystemsetjob_queue_processes=0;

    SQL>数据库程序设计报告alterdatabaseopen;

    SQL>alterdatabasecharactersetinternal_usewe8iso8859p1;

    SQL>shutdownimmediate;

    SQL>startup

    1.2.7数据库其他参数配置

    1.2.7.1DB_FILES

    Db_files是数据库能够同时打开的文件数量,默认值是200个。当数据库规划时文件数量FILES接近或超过200个时候,按以下估计值配置:

    DB_FILES=FILES*1.5

    1.2.7.2Db_block_size

    一个extent要是5个blocks的倍数为好,如:一个blocks是4096字节,那一个extent就是2M、4M或8M为好。Db_block_size是数据库最小物理单元,一旦数据库创建完成,该参数无法修改,db_block_size按以下规则调整:

    数据仓库类型:db_block_size尽可能大,采用8192或16384

    OLTP类型:db_block_size用比较小的取值范围:2048或4096

    Blocks推荐是系统操作的块倍数(裸设备块大小是512字节,NTFS是4K,使用8K的方式在大部分系统上通用)。

    1.2.8数据库控制文件配置

    1.2.8.1控制文件镜象

    多个控制文件存放在不同的物理位置。

    1.2.8.2控制文件配置

    控制文件中参数设置,最大的数据文件数量不能小于数据库参数db_files。

    1.2.9数据库日志文件配置

    1.2.9.1日志文件大小

    日志文件的大小由数据库事务处理量决定,在设计过程中,确保每20分钟切换一个日志文件。所以对于批处理系统,日志文件大小为几百M到几G的大小。对于OLTP系统,日志文件大小为几百M以内。

    1.2.9.2日志文件组数量

    对于批处理系统,日志文系统数据库设计件组为5—10组;对于OLTP系统,日志文件组为3—5组,每组日志大小保持一致;对于集群数据库系统,每节点有各自独立的日志组。

    1.2.9.3日志成员数量

    为了确保日志能够镜象作用,每日志组的成员为2个。

    1.2.10数据库回滚段配置

    在Oracle9i数据库中,设计Undo表空间取代以前版本的回滚段表空间。

    Undo表空间大小的设计规范由以下公式计算:

    Undospace=UR*UPS*db_block_size+冗余量

    UR:表示在undo中保持的最长时间数(秒),由数据库参数UNDO_RETENTION值决定。

    UPS:表示在undo中,每秒产生的数据库块数量。

    例如:在数据库中保留2小时的回退数据,假定每小时产生200个数据库块。则Undospace=2*3600*200*4K=5.8G

    1.2.11数据库临时段表空间配置

    数据库临时段表空间根据实际生产环境情况调整其大小,表空间属性为自动扩展。

    1.2.12数据库系统表空间配置

    系统表空间大小1G左右,除了存放数据库数据字典的数据外,其他数据不得存储在系统表空间。

    1.3数据库表空间设计原则

    1.3.1表空间大小定义原则

    当表空间大小小于操作系统对最大文件限制时,表空间由一个文件组成。如果表空间大小大于操作系统对最大文件限制时,该表空间由多个数据文件组成,表空间的总大小为估算为:

    Tablespace+sum(数据段+索引段)*150%。

    1.3.2表空间扩展性设计原则

    表空间数据文件采用自动扩展的方式,扩展容量快大小按2的整数倍(1M、2M、4M、8M、16M、32M、64M)进行扩展,创建表空间时尽量采用nologing选项。表空间的最大限制一般采用unlimited,除非确切知道表空间数据文件的最大使用范围。(一般windows32位系统的文件最大2G,64位的unix系统系统文件最大128G,但也要注意文件格式设定的文件大小),建议最大为2G。表空间采用local管理方式,例如:

    CREATETABLESPACETBS_USERINFO

    DATAFILE

    '/oradata/tbs_userinfo.dbf'

    SIZE8M

    REUSE

    AUTOEXTENDON

    NEXT2M

    MAXSIZEUNLIMITED

    NOLOGGING

    EXTENTMANAGEMENT

    LOCAL

    AUTOALLOCATE

    SEGMENTSPACEMANAGEMENTAUTO;

    1.4裸设备的使用

    一个scsi设备可以14个分区,unix操作系统256个分区,性能比文件系统方式高15%左右,空间大于要小于(实际分区大小减两个ORACLE的数据块),比如100M,大于为100000K,推荐在unix使用软连接(ln)方式把裸设备形成文件,用加入表空间时加resue选项,当然也可只接把设备加入表空间,移动裸设备使用dd命令

    对于windows平台,oracle提供软连接工具,实现裸设备的使用,计算一条记录的长度

    2数据库逻辑设计原则

    2.1命名规范

    2.1.1表属性规范

    2.1.1.1表名

    前缀为Tbl_。数据表名称必须以有特征含义的单词或缩写组成,中间可以用“_”分割,例如:tbl_pstn_detail。表名称不能用双引号包含。

    2.1.1.2表分区名

    前缀为p。分区名必须有特定含义的单词或字串。

    例如:tbl_pstn_detail的分区p2004100101表示该分区存储2004100101时段的数据。

    2.1.1.3字段名

    字段名称必须用字母开头,采用有特征含义的单词或缩写,不能用双引号包含。

    2.1.1.4主键名

    前缀为PK_。主键名称应是前缀+表名+构成的字段名。如果复合主键的构成字段较多,则只包含第一个字段。表名可以去掉前缀。

    2.1.1.5外键名

    前缀为FK_。外键名称应是前缀+外键表名+主键表名+外键表构成的字段名。表名可以去掉前缀。

    2.1.2索引

    4.1.2.1普通索引

    前缀为IDX_。索引名称应是前缀+表名+构成的字段名。如果复合索引的构成字段较多,则只包含第一个字段,并添加序号。表名可以去掉前缀。

    2.1.2.2主键索引

    前缀为IDX_PK_。索引名称应是前缀+表名+构成的主键字段名,在创建表时候用usingindex指定主键索引属性。

    2.1.2.3唯一所以

    前缀为IDX_UK_。索引名称应是前缀+表名+构成的字段名。

    2.1.2.4外键索引

    前缀为IDX_FK_。索引名称应是前缀+表名+构成的外键字段名。

    2.1.2.5函数索引

    前缀为IDX_func_。索引名称应是前缀+表名+构成的特征表达字符。

    2.1.2.6蔟索引

    前缀为IDX_clu_。索引名称应是前缀+表名+构成的簇字段。

    2.1.3视图

    前缀为V_。按业务操作命名视图。

    2.1.4实体化视图

    前缀为MV_。按业务操作命名实体化视图。

    2.1.5存储过程

    前缀为Proc_。按业务操作命名存储过程

    2.1.6触发器

    前缀为Trig_。触发器名应是前缀+表名+触发器名。

    2.1.7函数

    前缀为Func_。按业务操作命名函数

    2.1.8数据包

    前缀为Pkg_。按业务操作集合命名数据包。

    2.1.9序列

    前缀为Seq_。按业务属性命名。

    2.1.10表空间

    2.1.10.1公用表空间

    前缀为Tbs_。根据存储的特性命名,例如:tbs_parameter。

    2.1.10.2专用表空间

    Tbs__nn。该表空间专门存储指定的某一个表,或某一表的若干个分区的数据

    2.1.11数据文件

    nn.dbf。nn=1,2,3,4,…等。

    2.1.12普通变量

    前缀为Var_。存放字符、数字、日期型变量。

    2.1.13游标变量

    前缀为Cur_。存放游标记录集。

    2.1.14记录型变量

    前缀为Rec_。存放记录型数据。

    2.1.15表类型变量

    前缀为Tab_。存放表类型数据。

    2.1.16数据库链

    前缀为dbl_。表示分布式数据库外部链接关系。

    2.2命名

    2.2.1语言

    命名应该使用英文单词,避免使用拼音,特别不应该使用拼音简写。命名不允许使用中文或者特殊字符。

    英文单词使用用对象本身意义相对或相近的单词。选择最简单或最通用的单词。不能使用毫不相干的单词来命名

    当一个单词不能表达对象含义时,用词组组合,如果组合太长时,采用用简或缩写,缩写要基本能表达原单词的意义。

    当出现对象名重名时,是不同类型对象时,加类型前缀或后缀以示区别。

    2.2.2大小写

    名称一律大写,以方便不同数据库移植,以及避免程序调用问题。

    2.2.3单词分隔

    命名的各单词之间可以使用下划线进行分隔。

    2.2.4保留字

    命名不允许使用SQL保留字。

    2.2.5命名长度

    表名、字段名、视图名长度应限制在20个字符内(含前缀)。

    2.2.6字段名称

    同一个字段名在一个数据库中只能代表一个意思。比如telephone在一个表中代表“电话号码”的意思,在另外一个表中就不能代表“手机号码”的意思。

    不同的表用于相同内容的字段应该采用同样的名称,字段类型定义。

    2.3数据类型

    2.3.1字符型

    固定长度的字串类型采用char,长度不固定的字串类型采用varchar。避免在长度不固定的情况下采用char类型。如果在数据迁移等出现以上情况,则必须使用trim()函数截去字串后的空格。

    2.3.2数字型

    数字型字段尽量采用number类型。

    2.3.3日期和时间

    2.3.3.1系统时间

    由数据库产生的系统时间首选数据库的日期型,如DATE类型。

    2.3.3.2外部时间

    由数据导入或外部应用程序产生的日期时间类型采用varchar类型,数据格式采用:YYYYMMDDHH24MISS。

    2.3.3.3大字段

    如无特别需要,避免使用大字段(blob,clob,long,text,image等)。

    2.3.3.4唯一键

    对于数字型唯一键值,尽可能用系列sequence产生。

    2.4设计

    2.4.1范式

    如无性能上的必须原因,应该使用关系数据库理论,达到较高的范式,避免数据冗余,但是如果在数据量上与性能上无特别要求,考虑到实现的方便性可以有适当的数据冗余,但基本上要达到3NF.如非确实必要,避免一个字段中存储多个标志的做法。如11101表示5个标志的一种取值。这往往是增加复杂度,降低性能的地方。

    2.4.2表设计

    2.4.2.1逻辑段设计原则

    2.4.2.1.1Tablespace

    每个表在创建时候,必须指定所在的表空间,不要采用默认表空间以防止表建立在系统表空间上导致性能问题。对于事务比较繁忙的数据表,必须存放在该表的专用表空间中。

    2.4.2.1.2Pctused

    默认pctused导致数据库物理空间利用率非常低40%左右;对于update比较少或update不导致行增大的表,pctused可设置在60—85之间;对于update能够导致行增大的表,update设置在40—70之间

    2.4.2.1.3Initrans

    对于需要并行查询或者在RAC数据库中需要并行处理的表,initrans设置为2的倍数,否则,不设该值。

    2.4.2.1.4Storage

    2.4.2.1.4.1Initial

    尽量减少表数据段的extents数量,initial的大小尽量接近数据段的大小64K,128K,…,1M,2M,4M,8M,16M,…,等按2的倍数进行圆整。例如表或分区数据段大小为28M,则initial取32M。

    2.4.2.1.4.2Next

    表或分区扩展extents的大小,按上述方法进行圆整。当表或分区数据段无法按Initial接近值进行圆整的情况下,其大小可以按Initial+Next进行圆整。此时,必须设置Minextents=2。例如:表或分区数据段大小为150M,则Initial=128M;Next=32M,Minextents=2。

    2.4.2.1.4.3Minextents

    该参数表示表创建时候Extents的初始数量,一般取1—2。

    2.4.2.1.4.4Pctincrease

    表示每个扩展Extents的增长率,设置pctincrease=0能够获得较好的存储性能。

    2.4.2.2特殊表设计原则

    2.4.2.2.1分区表

    对于数据量比较大的表,根据表数据的属性进行分区,以得到较好的性能。如果表按某些字段进行增长,则采用按字段值范围进行范围分区;如果表按某个字段的几个关键值进行分布,则采用列表分区;对于静态表,则采用hash分区或列表分区;在范围分区中,如果数据按某关键字段均衡分布,则采用子分区的复合分区方法。

    2.4.2.2.2聚蔟表

    如果某几个静态表关系比较密切,则可以采用聚蔟表的方法。

    2.4.2.3完整性设计原则

    2.4.2.3.1主键约束

    关联表的父表要求有主健,主健字段或组合字段必须满足非空属性和唯一性要求。对于数据量比较大的父表,要求指定索引段。

    2.4.2.3.2外键关联

    对于关联两个表的字段,一般应该分别建立主键、外键。实际是否建立外键,根据对数据完整性的要求决定。为了提高性能,对于数据量比较大的标要求对外健建立索引。对于有要求级联删除属性的外键,必须指定ondeletecascade。

    2.4.2.3.3NULL值

    对于字段能否null,应该在sql建表脚本中明确指明,不应使用缺省。由于NULL值在参加任何运算中,结果均为NULL。所以在应用程序中必须利用nvl()函数把可能为NULL值得字段或变量转换为非NULL的默认值。例如:NVL(sale,0)。

    2.4.2.3.4Check条件

    对于字段有检查性约束,要求指定check规则。

    2.4.2.3.5触发器

    触发器是一种特殊的存储过程,通过数据表的DML操作而触发执行,起作用是为确保数据的完整性和一致性不被破坏而创建,实现数据的完整约束。

    触发器的before或after事务属性的选择时候,对表操作的事务属性必须与应用程序事务属性保持一致,以避免死锁发生。在大型导入表中,尽量避免使用触发器。

    2.4.2.4注释

    表、字段等应该有中文名称注释,以及需要说明的内容。

    2.4.3索引设计

    对于查询中需要作为查询条件的字段,可以考虑建立索引。最终根据性能的需要决定是否建立索引。对于复合索引,索引字段顺序比较关键,把查询频率比较高的字段排在索引组合的最前面。在分区表中,尽量采用local分区索引以方便分区维护。

    除非时分区local索引,否则在创建索引段时候必须指定指定索引段的tablespace、storage属性,具体参考4.4.2.1内容。

    2.4.4视图设计

    视图是虚拟的数据库表,在使用时要遵循以下原则:

    从一个或多个库表中查询部分数据项;

    为简化查询,将复杂的检索或字查询通过视图实现;

    提高数据的安全性,只将需要查看的数据信息显示给权限有限的人员;

    视图中如果嵌套使用视图,级数不得超过3级;

    由于视图中只能固定条件或没有条件,所以对于数据量较大或随时间的推移逐渐增多的库表,不宜使用视图;可以采用实体化视图代替。

    除特殊需要,避免类似Select*from而没有检索条件的视图;

    视图中尽量避免出现数据排序的SQL语句。

    2.4.5包设计

    存储过程、函数、外部游标必须在指定的数据包对象PACKAGE中实现。存储过程、函数的建立如同其它语言形式的编程过程,适合采用模块化设计方法;当具体算法改变时,只需要修改需要存储过程即可,不需要修改其它语言的源程序。当和数据库频繁交换数据是通过存储过程可以提高运行速度,由于只有被授权的用户才能执行存储过程,所以存储过程有利于提高系统的安全性。

    存储过程、函数必须检索数据库表记录或数据库其他对象,甚至修改(执行Insert、Delete、Update、Drop、Create等操作)数据库信息。如果某项功能不需要和数据库打交道,则不得通过数据库存储过程或函数的方式实现。在函数中避免采用DML或DDL语句。

    在数据包采用存储过程、函数重载的方法,简化数据包设计,提高代码效率。存储过程、函数必须有相应的出错处理功能。

    2.4.6安全性设计

    4.4.6.1管理默认用户

    在生产环境中,必须严格管理sys和system用户,必须修改其默认密码,禁止用该用户建立数据库应用对象。删除或锁定数据库测试用户scott。

    2.4.6.2数据库级用户权限设计

    必须按照应用需求,设计不同的用户访问权限。包括应用系统管理用户,普通用户等,按照业务需求建立不同的应用角色。

    用户访问另外的用户对象时,应该通过创建同义词对象synonym进行访问。

    2.4.6.3角色与权限

    确定每个角色对数据库表的操作权限,如创建、检索、更新、删除等。每个角色拥有刚好能够完成任务的权限,不多也不少。在应用时再为用户分配角色,则每个用户的权限等于他所兼角色的权限之和。

    2.4.6.4应用级用户设计

    应用级的用户帐号密码不能与数据库相同,防止用户直接操作数据库。用户只能用帐号登陆到应用软件,通过应用软件访问数据库,而没有其它途径操作数据库。

    2.4.6.5用户密码管理

    用户帐号的密码必须进行加密处理,确保在任何地方的查询都不会出现密码的明文。

    2.5SQL编写

    2.5.1字符类型数据

    SQL中的字符类型数据应该统一使用单引号。特别对纯数字的字串,必须用单引号,否则会导致内部转换而引起性能问题或索引失效问题。利用trim(),lower()等函数格式化匹配条件。

    2.5.2复杂sql

    对于非常复杂的sql(特别是有多层嵌套,带子句或相关查询的),应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使用程序实现。

    USER_TAB_COMMENTS数据字典

    Commenton可加注解

    2.5.3高效性

    2.5.3.1避免In子句

    使用In或notIn子句时,特别是当子句中有多个值时,且查询数据表数据较多时,速度会明显下降。可以采用连接查询或外连接查询来提高性能。

    Char比varchar查询时高询

    在进行查询及建立索引时,char比varchar的效率要高,当然varchar在存储上比char要好

    2.5.3.2避免嵌套的Select子句

    这个实际上是In子句的特例。

    2.5.3.3避免使用Select*语句

    如果不是必要取出所有数据,不要用*来代替,应给出字段列表,注:不含selectcount(*)。

    2.5.3.4避免不必要的排序

    不必要的数据排序大大的降低系统性能。

    2.5.4健壮性

    2.5.4.1Insert语句

    使用Insert语句一定要给出要插入值的字段列表,这样即使更改了表结构加了字段也不会影响现有系统的运行。

    2.5.4.2Count(*)、Count(*)、count(distinctid)的区别

    Selectcount(*)fromtesttab

    得到表testtab的记录数

    selectcount(id)fromtesttab

    得到表testtabid字段非空记录数

    selectcount(distinctid)fromtesttab

    得到表testtabid字段值非相同记录数

    2.5.4.3Notnull为字段类型性质的约束

    本约束功能在后期无语法使期失效,可使用修改字段类型方式

    altertablemodify字段名类型notnull

    altertablemodify字段名类型

    2.5.4.4外键值可用null的问题

    外键列如没有明确说明notnull,可插入null记录(而null是在外部表的记录中没有的),如无可插null记录的想法,要对外键字段加notnull约束。

    2.5.4.5序列sequence跳号的问题

    sequence因回滚,系统崩溃(使用cache内的值将认为已用),多表引用都将使其跳号,所以不能用于为连续序号utl_row.cast_to_row

    2.5.4.6unicn\intersect\minus使用ordeyby的注意事项

    以上语句进行连表操作,而表同表的字段顺序的类型相同但字段标题名可不同,使用ordeyby时后面如果是字段名,要求所有的表的字段标题名相同,否则用字段的顺序号

    selectid,name,yearfromuser1

    union

    selectno,name,to_number(null)yearfromuser2

    orderby1,name,year

    2.5.5安全性

    2.5.5.1Where条件

    无论在使用Select,还是使用破坏力极大的Update和Delete语句时,一定要检查Where条件判断的完整性,不要在运行时出现数据的重大丢失。如果不确定,最好先用Select语句带上相同条件来果一下结果集,来检验条件是否正确。

    2.5.6完整性

    有依赖关系的表,例如主外键关系表,在删除父表时必须级联删除其子表相应数据,或则按照某种业务规则转移该数据。9I中表中字段缩小及变类型,字段为空或表空,varchar和char长度不变可任意改,字段名和表名可字段可用ALTERTABLEtableSETUNUSED(column)设定为不可用,注意无命令再设为可用

    3备份恢复设计原则

    3.1数据库exp/imp备份恢复

    Oracle数据库的Exp、Imp提供了数据快速的备份和恢复手段,提供了数据库级、用户级和表级的数据备份恢复方式。这种方法一般作为数据库辅助备份手段。

    3.1.1数据库级备份原则

    在数据库的数据量比较小,或数据库初始建立的情况下采用。不适合7*24的在线生产环境数据库备份。

    3.1.2用户级备份原则

    在用户对象表数据容量比较小、或则用户对象初始建立的情况下使用。

    3.1.3表级备份原则

    主要在以下场合采用的备份方式:

    参数表备份

    静态表备份

    分区表的分区备份。

    3.2数据库冷备份原则

    数据库冷备份必须符合以下原则:

    数据库容量比较小。

    数据库允许关闭的情况。

    3.3Rman备份恢复原则

    这种方式适用于7*24环境下的联机热备份情形。

    3.3.1Catalog数据库

    单独建立备份恢复用的数据库实例,尽可能与生产环境的数据库分开,确保catalog与生产数据库的网络连接良好。在9I系统使用良好的备份策略以可,支持完全使用控制文件保存catalog信息,备份策略如下:

    backupspfileformat'/data/backup/%d_SPFILE_%T_%s_%p.bak';

    sql"altersystemarchivelogcurrent";

    backuparchivelogallformat'/data/backup/%d_ARC_%T_%s_%p.bak'deleteallinput;

    backupcurrentcontrolfileformat'/data/backup/%d_CTL_%T_%s_%p.bak';

    在spfile、控制文件、数据库全丢的情况下可通过下面的方式恢复

    RMAN>connecttarget

    connectedtotargetdatabase(notstarted)

    RMAN>svc 连接数据库tartup

    RMAN>restorespfilefrom'/data/backup/COMMDB_SPFILE_20030411_9_1.bak';

    SQL>startup

    ORA-00205:errorinidentifyingcontrolfile,checkalertlogformoreinfo

    RMAN>restorecontrolfilefrom'd:\DB92_CTL_20031113_9_1.BAK';

    Moutdatabase:

    RMAN>recoverdatabase;

    RMAN>alterdatabaseopenresetlogs;

    注意:对数据库设定控制文件保存备份信息为365天,具体语句如下。

    altersystemsetcontrol_file_record_keep_time=365SCOPE=BOTH;

    3.3.2ArchiveLog

    设置ArchiveLog的位置,确保存储介质有足够的空间来保留指定时间内archivelog的总量。建设定期对RMAN进行全备份,删除冗余归档日志文件。

    3.3.3全备份策略

    对于小容量数据库,可以采用全备份策略。对于大容量数据库,必须制定全备份策略方案,备份时对archivelog进行转储,同时冷备份catalog数据库。

    3.3.4增量备份策略

    对于大容量数据库数据报告怎么写,必须制定增量备份、累积备份和全备份的周期,备份时对archivelog进行转储,同时冷备份catalog数据库。

    3.3.5恢复原则

    采用Rman脚本进行数据库恢复。数据库恢复有以下几种:

    3.3.5.1局部恢复

    主要用于恢复表空间、数据文件,一般不影响数据库其他操作。

    3.3.5.2完全恢复

    数据库恢复到故障点,由catalog当前数据库决定。

    3.3.5.3不完全恢复

    恢复到数据库的某一时间点或备份点。

    恢复catalog数据库。

    恢复数据库controlfile。

    恢复到数据库某一时间点。

    重设日志序列。

    3.4备用数据库原则

    数据库系统在以下情况下可以考虑采用备用数据库dataguard原则:

    数据库容量适中。

    数据库严格要求7*24不间断,或间断时间要求控制在最小范围内。

    数据库要求有异地备份冗余。

    3.5一些小数据库应用系统设计经验

    使用oemc的oms时,首选项要求是节点和数据库分别加入系统用户(如:administrator)和数据库DBA用户(system)。节点的系统用户必须有批处理作业登录的权限

    agent不能启动,lisnter修改后都要手动删除oracle\ora9\network\agent中的*.q文件

    oracle\admin\my9i\bdump中是用户的出错日志

    改变表的空间的方式altertablehr.ssssmoveTABLESPACEexample(要重建索引);或用imp导入时,设定导入用户只有某一表空间的使用权,无RESOURCE角色和UNLIMITEDTABLESPACE权限

    aletersystemsetlog_checkpoint_to_alter=true,后可报警文件发现checkpoint的起动和结束时间。

    3.6系统调优知识

    3.6.1.1生成状态报表(statspack的使用)

    使用(存放位置@\rdbms\admin\)的文件生成报表用户

    @\rdbms\admin\Spcreate.sql建表

    将timed_statistics设定true

    使用生成的perfstat用户登录,执行以下语句手动收集信息

    Exexstatspack.snap

    Execstatspack.snap(I_SNAP_LEVEL=>0,I_MODEFY_PRAMETER=>TRUE)0级,最少10最大

    使用下面的语句生成状态报表

    @\rdbms\admin\Spreport.sql

    其他相关文件

    deletestats$snapshot;清原来记录数据

    @\rdbms\admin\Saputo.sql

    selectjobfromuser_jobs取用户作业号

    execdbms_remove(作业号)

    timed_statistics=true要求

    @\rdbms\admin\spdaccess数据库使用教程rop.sql;

    3.6.1.2sql追踪

    设定全部用户跟踪

    altersystemsetsql_trace=true;

    用户级别跟踪

    altersessionsetsql_trace=true;

    用户的跟踪文件生成在admin\{pid}\udump\{pid}_ora_{SPID}.trc中,spid从下面语句得到

    SELECTb.namebkpr,s.username,p.spid,s.sid,s.serial#FROMv$bgprocessb,v$sessions,v$processpWHEREp.addr=b.paddr(+)ANDp.addr=s.paddrands.username=user;

    DBA对特定用户跟踪

    execdbms_system_set_Sql_trace_in_session(sid,serial#,true)

    信息从下面得到

    SELECTb.namebkpr,s.username,p.spid,s.sid,s.serial#,osuser,s.program

    FROMv$bgprocessb,v$sessions,v$processp

    WHEREp.addr=b.paddr(+)

    ANDp.addr=s.paddr;

    /*p.spid用于sql_trace时日志编号,dbms_system.set_sql_trace_in_session(sid,erial#,true)*/

    用户的跟踪文件生成在admin\{pid}\udump中

    系统的跟踪文件生成在admin\{pid}\bdump\alert_{pid}.log

    tkprof.exe将log文件生成格式化文本

    在avRd(ms)20以上说明表空间使用过用频繁,考虑将表分开其他表空间上

    系统变量fast_start_mttr_target的值要大到不产生log等待,当然也可通过加log组使其不等待

    reaolog大小应为每30分钟切换一次

    建议表空间的利用率不超80%

    bufferhit要达80%以上为好

    3.6.1.3内存调整

    一般的内存分配原则

    SGA50%(其中80%DATABUFFER,15%SHAREPOOL,5其他)

    PGA30%

    OS20%

    例如:2G的WINDOWS的平台,OS300M,SAG1.2G,PGA500M

    内存分配的基本单位

    SGA《=128M 4M

    SGA》128M  64位系统16M,32M系统8M

    动态分配时总值不可大于sga_max_size

    通过V$SGA_DYNAMIC_FREE_MEMORY取空闲内存空间

    在缩小时如果内存空间实际在应用中,CPU利用率将达100%,最后将语句出错。

    V$SGASTAT 可看实际的使用情况

    Redologbuffer一般在5M内,可通过v$sessuon_wait看是否等,v$sysstat

    可也通过报警文件看是否等切换,方法可加组。可通过nologging(数据库也要设定支持nologging)方法减少日志文件产生量。

    java_pool没有设定时,使用shared_pool_size

    3.6.1.3.1shared_pool

    本缓冲区用于sql语句,plsql等的对象保存

    Cursor_sharing{Exact|Similar|force}游标共享设定

    Force方式适用OLTP数据库,Exact方式适合数据仓库,similar为智能方式

    hardparses硬SQL语句分析,每秒要底于100次,小要加大shared_pool

    softparse软SQL语句分析,OLTP要达90%以上,小要加大shared_pool

    不建议用无命名PLSQL段

    如果有大PLSQL(存储过程)对象可强制保存于内存,也可加大SHARED_POOL_RESERVED_SIZE,大小不可过SHARED_POOL_SIZE的50%,不然实例不能起动

    3.6.1.3.2db_cache

    本缓冲区用于数据库数据对象保存

    db_cache_advice为on,可以提出通过企业管理器看到系统建议

    通过select*fromv$system_event进行系统查看。

    发现存在freebufferwaits,说明不能将databuffer及时写入datafile;

    可通过增加加CPU后,加db_writer_processes=CPU数改善。

    也可设disk_asynch_io为true,使用异步IO(前提同要操作系统支持)db_writer_processes=1时(只有一个CPU的情况下),也可通加大dbwr_io_slaves来改善。db_writer_processes>1,不可用本功能

    调整效果排序:异步IO>CPU>dbwr_io_slaves

    BufferBusyWaits大说明出现IO冲突

    BufferBusyWaits大和dbbock大说明全表扫描多,说明数据不能读入,可加大

    db_cache_size来改善.

    Undo block大要加大回滚段(手动管理方式,9I默认是自动管理)

    undoheader大要加大回滚段(手动管理方式,9I默认是自动管理)

    db_cache命中率99%,不是唯一因素,关系是不要出现等待。建议达90%以上。

    内存使用建议:

    系统可以设三个缓冲区,建表时可设定用那个缓冲区(默认在db_cache_size)

    db_cache_size   (默认区)

    db_keep_cache_size(常访问,小于db_keep_cache_size的10%的表可放于本区)

    db_recycle_cache_size(一个事物完成后常时间不再使用,或两倍大小于缓冲区)

    3.6.2排序的优化

    9I为专用服务器时系统变量workarea_size_policy设定为auto,statistics_level设定为TYPICAL可获取v$pga_target_advice中的优化建议。参数pga_aggregate_target值为所有连接用户可用排序内存。

    9I为共享服务器时workarea_size_policy设定为menaul,sort_area_size值为每用户排序内存。

    如果内存不足将使用TEMP表空间进行排序,排序使用比率disk/meme应小于5%

    尽量少用排序,如果使用排序功能,尽量在字段上加索引进行优化。

    SQL分析模式:RBO(基于规则)方案小表(驱动表)放在最后,优先使用索引,对SQL语句要求严格(8I以前的模式);CBO(基于开销)根据统计值进行选择开销最少,性能最优的最佳方式进行,但本方式DBA(使用analyzetable语句)要定期进行分析统计.系统设定通过optimizer_mode系统参数

    说明:指定优化程序的行为。如果设置为RULE,就会使用基于规则的优化程序,除非查询含有提示。如果设置为CHOOSE,就会使用基于成本的优化程序,除非语句中的表不包含统计信息。ALL_ROWS或FIRST_ROWS

    始终使用基于成本的优化程序。

    值范围:RULE|CHOOSE|FIRST_ROWS|ALL_ROWS

    默认值:CHOOSE

    {rule(RBO)|choose(自动选择)|fist_rows|fist_rows_n|all_row}

    3.6.3统计信息

    进行某表的统计分析

    EXECUTEdbms_stats.gather_table_stats('HR','EMPLOYEES');

    查看结果

    SELECTnum_rows,blocks,empty_blocksasempty,

    avg_space,chain_cnt,avg_row_len

    FROMdba_tables

    WHEREowner='HR'

    ANDtable_name='EMPLOYEES';

    4设计工具

    统一使用sybasepowerdesigner设计工具,在该工具上完成物理模型的设计。所有的数据库对象尽可能在物理模型上进行设计,而且每个物理模型都要有相应的文字描述。

    所有的数据库对象变更以数据库物理模型为基准。为了避免字符敏感问题,产生的脚本以大写字母为标准。

    展开全文
    weixin_39621819 2021-01-19 01:00:49
  • [职责分离原则]职责分离原则是指在设计的时候应当考虑到数据的产生,聚合使用等原则,每个系统干自己能干的事情,每个系统只干自己的事情。一个数据表应该放在哪个系统中,通常取决于几点:1. 谁产生这个信息:通常...

    [职责分离原则]

    职责分离原则是指在设计的时候应当考虑到数据的产生,聚合使用等原则,每个系统干自己能干的事情,每个系统只干自己的事情。一个数据表应该放在哪个系统中,通常取决于几点:

    1. 谁产生这个信息:通常情况下谁产生了这个数据应当对此数据负责;也就是考虑该数据的创建,发展,销毁等全生命周期的定义,并将这个定义维护起来提供给消费者作为消费原则;

    2. 谁最经常使用这个信息:如果某个系统最经常使用这个数据,最经常去修改某个数据,也应该由该系统来负责保存维护该数据;

    3. 遵守高内聚,低耦合的考虑:在存放数据的时候如果考虑到数据使用原则导致了相关度非常高的数据存放在多个地方,需要多个系统来维护这个数据就有可能导致系统间的耦合性增强,应当尽量避免。

    在我们设计数据库表间的关系的时候也应当遵守相同原则,职责分离降低耦合,但同时要考虑到性能情况,做到适当冗余而不导致修改逻辑复杂。

    举个最常见贴子与评论的例子:CREATE TABLE `wanted_post` (

    `id` int(10) NOT NULL AUTO_INCREMENT,

    `puid` int(10) unsigned NOT NULL,

    `user_id` int(10) NOT NULL COMMENT '发贴用户的id',

    `username` varchar(50) NOT NULL COMMENT '发贴用户的用户名',

    `city` smallint(4) NOT NULL COMMENT '所在城市',

    `ip` bigint(14) NOT NULL COMMENT '发帖人的ip',

    `district_id` tinyint(2) NOT NULL COMMENT '所在区域的id',

    `district_name` varchar(20) NOT NULL COMMENT '行政区名字',

    `street_id` tinyint(2) NOT NULL COMMENT '所在街道(地标)的id',

    `street_name` varchar(20) NOT NULL COMMENT '小区名字',

    `title` varchar(255) NOT NULL COMMENT '帖子的标题',

    `description` text NOT NULL COMMENT '帖子详情描述',

    `post_at` int(11) NOT NULL COMMENT '用户发帖时间,数据创建的时间,使用整型存储',

    `refresh_at` int(11) NOT NULL COMMENT '帖子被修改的时间,整型存储',

    `show_time` int(11) NOT NULL COMMENT '帖子显示时间',

    `age_max` int(11) NOT NULL DEFAULT '0' COMMENT '招聘最小年龄',

    `age_min` int(11) NOT NULL DEFAULT '0' COMMENT '招聘最大年龄',

    `post_refresh_at` int(11) NOT NULL COMMENT '刷新时间',

    PRIMARY KEY (`id`),

    UNIQUE KEY `idx_puid` (`puid`),

    KEY `user_id_index` (`user_id`),

    KEY `post_at_index` (`post_at`),

    KEY `refresh_at_index` (`refresh_at`),

    KEY `show_time_index` (`show_time`)

    ) ENGINE=InnoDB AUTO_INCREMENT=55295 DEFAULT CHARSET=utf8 COMMENT='招聘帖子表'CREATE TABLE `wanted_post_comment_99` (

    `id` int(11) NOT NULL AUTO_INCREMENT,

    `puid` int(10) unsigned NOT NULL,

    `user_id` int(10) NOT NULL COMMENT '评论用户ID',

    `post_at` int(11) NOT NULL COMMENT '评论时间',

    `detail` text NOT NULL COMMENT '评论详情',

    PRIMARY KEY (`id`),

    KEY `user_id_index` (`user_id`),

    KEY `puidid_index` (`puid`)

    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='招聘评论分表99'

    由于评论表数据量很大,在预先做好分表,按贴子puid分成100张子表,那么当前详情页涉及sql如下:select * from wanted_post where puid=xxxx;

    select * from wanted_post_comment_99 where puid=xxxx;

    这是一个简化的模型,评论多了,还要涉及分页,不可能一次性全取出来。对于上面的场景,严格尊守高内聚,低耦合的原则,不会存储冗余数据。相比较还有一种文档型数据库,例如mongo,就可以将评论与贴子存放在一起,访问的时候只需一次顺序IO操作。整体来讲表设计,要按照职责划分原则。

    [在线处理与分析分离]

    1. 为了保障线上数据处理的性能,将一些分析相关的数据及分析结果,应当使用单独的库来进行存储,避免在数据分析的时候导致业务数据吞吐量下降,引起系统问题。

    2. 专门用于存放离线报表数据,并提供线上数据查询方法,建议将统计结果,汇总的数据都从在线处理数据库中移走。

    对于上面的wanted_post求职贴子表,在线处理只能是用户在操作:浏览,修改,删除,分别对应如下sql:select * from wanted_post where puid=xxxxx;

    update wanted_post set xxx=xxx where puid=xxxx;

    delete from wanted_post where puid=xxxx;

    同样,对于后台统计来讲,都是些聚合操作,非常消耗性能,例如查看某一用户发贴量:select count(*) from wanted_post where user_id=xxxx;

    上面举个通用的例子,原则上要将在线用户请求和后台统计请求分开。简单来讲,对于这种需求处理如下:

    将请求指向不同slave ,这种方法简单高效,缺点是数据量增大就玩不转。

    建立离线报表库,专门存放统计结果,这样将计算与展示异步处理,缺点是对于实时业务响应不好。

    实时拉取mysql row binlog,做数据的异构处理(tungsten, canal),将增量结果处理后(storm),保存在数据库中,基本实时。

    [事务与日志分离]

    用户生成内容和用户行为日志要分开,这一点很好理解,举两个例子:

    游戏DB里存放玩家的基础信息,装备,属性,好友列表等等,这些放到数据库里面。但是玩家的行为日志,比如消耗金币,今天下过哪些副本,买过什么顶级装备,这些属于行为日志,应该单独存放并分析处理。

    对于web用记,有好多用户置顶,刷新,竞价,展示等行为,要求实时并且量很大,一定要和贴子分开。

    行为日志,需要做分析处理,并且由于时效性不宜存储在mysql中,后期维护就是地雷。

    [历史可追溯]

    在数据库设计的时候为了保障数据是可追溯的,应当遵循一些简单的约定,事后方便数据的查询和统计:

    1. 对于状态数据,应当设计相应状态的字段来保存该数据的最后状态,同时记录下来该数据的初始创建人,时间以及该数据的最后修改人和修改时间;所以在交易数据(如订单合同),广告数据,账户表等都应该默认有状态(status),创建人(creator/creator_name),创建时间(created_at),最后修改人(modifier/modifier_name),最后修改时间(modified_at)等字段用来表明数据的当前状态,创建信息及修改信息。

    2. 针对需要跟踪每次修改的数据,需要在数据发生变化的时候记录一张日志表,用于记录该数据发生变化的全生命周期。针对只需要关注关键字段变化的情况,则日志表中只需要记录关键字段变化即可,但操作人,操作类型,时间应当准确记录,日志表数据一旦生成不允许进行修改。如用户账户的充值流水,消费流水都是一些业务紧相关的日志。而审核日志,操作记录等日志则属于与业务关联较小的日志。

    3. 针对所有历史需要保留的数据则需要每次变化都生成一个新的版本,比如类目信息等,对原始数据永远只做insert操作,不做delete及update操作。但这种情况仅限于极端数据历史要求极高的情况下使用。

    展开全文
    weixin_31960565 2021-01-18 21:26:17
  • 一,数据库设计规范1.数据库命名规范采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;例如:user,stat,log,也可以wifi_user,wifi_stat,wifi_log给数据库加个前缀;2.数据表命名规范采用26个...

    一,数据库设计规范1.数据库命名规范

    采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;

    例如:user, stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀;

    2.数据表命名规范

    采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;

    命名简洁明确,多个单词用下划线'_'分隔;

    3.数据表字段命名规范

    采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;

    命名简洁明确,多个单词用下划线'_'分隔;

    例如:user_login表段 user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time;  4.字段类型规范

    用尽量少的存储空间来存数一个字段的数据;  例如:能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);

    IP地址最好使用int类型;

    固定长度的类型最好使用char,例如:邮编;

    能使用tinyint就不要使用smallint,int;

    最好给每个字段一个默认值,最好不能为null;

    5.数据库表索引规范

    命名简洁明确,例如:user_login表user_name字段的索引应为user_name_index唯一索引;

    6.简单熟悉数据库范式(三大范式)

    第一范式(1NF):字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);  例如:姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段;

    第二范式(2NF):一个表必须有主键,即每行数据都能被唯一的区分;

    第三范式(3NF):一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段;

    二.MYSQL数据库设计原则1.核心原则

    不在数据库做运算或写业务逻辑(存储过程,触发器);  控制列数量(字段少而精,字段数建议在20以内);  平衡范式与冗余(效率优先;往往牺牲范式) ; 拒绝3B(拒绝大sql语句:big sql、拒绝大事物:bigtransaction、拒绝大批量:big batch);

    2.字段类原则

    用好数值类型(用合适的字段类型节约空间); 字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);

    避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);

    少用text类型(尽量使用varchar代替text字段);

    3.索引类原则

    合理使用索引(改善查询,减慢更新,索引一定不是越多越好);

    字符字段必须建前缀索引;  不在索引做列运算;

    innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);  减少使用外键(由程序保证约束);

    4.sql类原则

    sql语句尽可能简单;简单的事务;   避免使用trig/func(触发器、函数不用客户端程序取而代之);

    不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性);

    条件查询or改为IN;OR改写为UNION(mysql的索引合并很弱智);

    避免负向%;  如id !=1;少用连接join;

    ===========================================================

    三.mysql分区分表

    (1)简介mysql数据库中的数据是以文件的形式存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看)。

    使用myisam引擎的一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。使用innoDB引擎的/mysql/data/数据库名目录下一张表有一个frm文件存放数据结构,其他的数据部分全部都存在在/mysql/data目录下的ibdata文件中。

    (2)分表分区是什么?

    分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,我们可以称为子表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构文件。这些子表可以分布在同一块磁盘上,也可以在不同的机器上。

    分区就是把一张表的数据分成N多个区域,分区后,表面上还是一张表,但数据散列到多个位置根据数据量的大小,结合实际业务。

    (3)分区

    mysql5.1及以上支持分区功能

    分区方式有:range分区,list分区,hash分区,key分区,子分区。

    ①range分区

    建表的时候添分区按照id的范围CREATE TABLE IF NOT EXISTS `user` (

    id int ........

    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1   -PARTITION BY RANGE (id) (

    PARTITION p0 VALUES LESS THAN (3),                                                                           PARTITION p1 VALUES LESS THAN (6),                                                                           PARTITION p2 VALUES LESS THAN MAXVALUE,                            );

    对现有的表进行分区alter table aa partition by RANGE(id)(

    PARTITION p1 VALUES less than (1),

    PARTITION p2 VALUES less than (5),   - PARTITION p3 VALUES less than MAXVALUE);

    //删除一个分区

    alter table aa drop partition p2;

    ②list分区

    LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值,而RANGE分 区是从属于一个连续区间值的集合。如下:

    官方文档规定  LIST 分区是,分区字段必须包含在主键字段内CREATE TABLE IF NOT EXISTS `list_part` (  `id` int(11) NOT NULL COMMENT '用户ID',

    `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',

    ) ENGINE=INNODB  DEFAULT CHARSET=utf8  AUTO_INCREMENT=1   -PARTITION BY LIST (province_id) (

    PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),    PARTITION p1 VALUES IN (9,10,11,12,16,21),  PARTITION p2 VALUES IN (13,14,15,19),    PARTITION p3 VALUES IN (17,18,20,22,23,24)   );

    ③hash分区

    HASH分区主要用来确保数据在预先确定数目的分区中平均分布,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以 及指定被分区的表将要被分割成的分区数量。CREATE TABLE IF NOT EXISTS `hash_part` (     `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',     `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',      `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',      PRIMARY KEY (`id`)    ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1    PARTITION BY HASH(id)   -> PARTITIONS 3;

    ④key分区

    按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用 户定义的表达式,而KEY分区的 哈希函数是由MySQL 服务器提供。CREATE TABLE IF NOT EXISTS `key_part` (      `news_id` int(11) NOT NULL  COMMENT '新闻ID',     `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',      `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',      `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'   ) ENGINE=INNODB  DEFAULT CHARSET=utf8    PARTITION BY LINEAR HASH(YEAR(create_time))

    PARTITIONS 3;

    ⑤子分区

    子分区是分区表中每个分区的再次分割,子分区既可以使用HASH希分区,也可以使用KEY分区。这 也被称为复合分区(composite partitioning)。1,如果一个分区中创建了子分区,其他分区也要有子分区2,如果创建了了分区,每个分区中的子分区数必有相同3,同一分区内的子分区,名字不相同,不同分区内的子分区名子可以相同(5.1.50不适用)CREATE TABLE IF NOT EXISTS `sub_part` (     `news_id` int(11) NOT NULL  COMMENT '新闻ID',     `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',      `u_id`  int(11) NOT NULL DEFAULT 0s COMMENT '来源IP',      `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'    ) ENGINE=INNODB  DEFAULT CHARSET=utf8    PARTITION BY RANGE(YEAR(create_time))  SUBPARTITION BY HASH(TO_DAYS(create_time))(    PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),    PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),    PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3));

    ⑥分区管理⑦⑧⑨⑩

    1,删除分区    alter table user drop partition p4;  2,新增分区

    range添加新分区 alter table user add partition(partition p4 values less than MAXVALUE);

    list添加新分区

    alter table list_part add partition(partition p4 values in (25,26,28));

    hash重新分区    alter table hash_part add partition partitions 4;

    key重新分区

    alter table key_part add partition partitions 4;

    展开全文
    weixin_32211599 2021-01-20 00:50:13
  • 数据库设计,一个软件项目成功的基石。很多从业人员都认为,数据库设计其实不那么重要。现实中的情景也相当雷同,开发人员的数量是数据库设计人员的数倍。多数人使用数据库中的一部分,所以也会把数据库设计想的如此...

    数据库设计,一个软件项目成功的基石。很多从业人员都认为,数据库设计其实不那么重要。现实中的情景也相当雷同,开发人员的数量是数据库设计人员的数倍。多数人使用数据库中的一部分,所以也会把数据库设计想的如此简单。其实不然,数据库设计也是门学问。

    从笔者的经历看来,笔者更赞成在项目早期由开发者进行数据库设计(后期调优需要DBA)。根据笔者的项目经验,一个精通OOP和ORM的开发者,设计的数据库往往更为合理,更能适应需求的变化,如果追其原因,笔者个人猜测是因为数据库的规范化,与OO的部分思想雷同(如内聚)。而DBA,设计的数据库的优势是能将DBMS的能力发挥到极致,能够使用SQL和DBMS实现很多程序实现的逻辑,与开发者相比,DBA优化过的数据库更为高效和稳定。如标题所示,本文旨在分享一名开发者的数据库设计经验,并不涉及复杂的SQL语句或DBMS使用,因此也不会局限到某种DBMS产品上。真切地希望这篇文章对开发者能有所帮助,也希望读者能帮助笔者查漏补缺。

    一 Codd的RDBMS12法则——RDBMS的起源

    Edgar Frank Codd(埃德加·弗兰克·科德)被誉为“关系数据库之父”,并因为在数据库管理系统的理论和实践方面的杰出贡献于1981年获图灵奖。在1985年,Codd 博士发布了12条规则,这些规则简明的定义出一个关系型数据库的理念,它们被作为所有关系数据库系统的设计指导性方针。

    信息法则 关系数据库中的所有信息都用唯一的一种方式表示——表中的值。

    保证访问法则 依靠表名、主键值和列名的组合,保证能访问每个数据项。

    空值的系统化处理 支持空值(NULL),以系统化的方式处理空值,空值不依赖于数据类型。

    基于关系模型的动态联机目录 数据库的描述应该是自描述的,在逻辑级别上和普通数据采用同样的表示方式,即数据库必须含有描述该数据库结构的系统表或者数据库描述信息应该包含在用户可以访问的表中。

    统一的数据子语言法则 一个关系数据库系统可以支持几种语言和多种终端使用方式,但必须至少有一种语言,它的语句能够一某种定义良好的语法表示为字符串,并能全面地支持以下所有规则:数据定义、视图定义、数据操作、约束、授权以及事务。(这种语言就是SQL)

    视图更新法则 所有理论上可以更新的视图也可以由系统更新。

    高级的插入、更新和删除操作 把一个基础关系或派生关系作为单个操作对象处理的能力不仅适应于数据的检索,还适用于数据的插入、修改个删除,即在插入、修改和删除操作中数据行被视作集合。

    数据的物理独立性 不管数据库的数据在存储表示或访问方式上怎么变化,应用程序和终端活动都保持着逻辑上的不变性。

    数据的逻辑独立性 当对表做了理论上不会损害信息的改变时,应用程序和终端活动都会保持逻辑上的不变性。

    数据完整性的独立性 专用于某个关系型数据库的完整性约束必须可以用关系数据库子语言定义,而且可以存储在数据目录中,而非程序中。

    分布独立性 不管数据在物理是否分布式存储,或者任何时候改变分布策略,RDBMS的数据操纵子语言必须能使应用程序和终端活动保持逻辑上的不变性。

    非破坏性法则 如果一个关系数据库系统支持某种低级(一次处理单个记录)语言,那么这个低级语言不能违反或绕过更高级语言(一次处理多个记录)规定的完整性法则或约束,即用户不能以任何方式违反数据库的约束。

    二 关系型数据库设计阶段

    (一)规划阶段

    规划阶段的主要工作是对数据库的必要性和可行性进行分析。确定是否需要使用数据库,使用哪种类型的数据库,使用哪个数据库产品。

    (二)概念阶段

    概念阶段的主要工作是收集并分析需求。识别需求,主要是识别数据实体和业务规则。对于一个系统来说,数据库的主要包括业务数据和非业务数据,而业务数据的定义,则依赖于在此阶段对用户需求的分析。需要尽量识别业务实体和业务规则,对系统的整体有初步的认识,并理解数据的流动过程。理论上,该阶段将参考或产出多种文档,比如“用例图”,“数据流图”以及其他一些项目文档。如果能够在该阶段产出这些成果,无疑将会对后期进行莫大的帮助。当然,很多文档已超出数据库设计者的考虑范围。而且,如果你并不精通该领域以及用户的业务,那么请放弃自己独立完成用户需求分析的想法。用户并不是技术专家,而当你自身不能扮演“业务顾问”的角色时,请你选择与项目组的相关人员合作,或者将其视为风险呈报给PM。再次强调,大多数情况,用户只是行业从业者,而非职业技术人员,我们仅仅从用户那里收集需求,而非依赖于用户的知识。

    记录用户需求时,可以使用一些技巧,当然这部分内容有些可能会超出数据库设计人员的职责:

    努力维护一系列包含了系统设计和规格说明信息的文档,如会议记录、访谈记录、关键用户期望、功能规格、技术规格、测试规格等。

    频繁与干系人沟通并收集反馈。

    标记出你自己添加的,不属于客户要求的,未决内容。

    与所有关键干系人尽快确认项目范围,并力求冻结需求。

    此外,必须严谨处理业务规则,并详细记录。在之后的阶段,将会根据这些业务规则进行设计。

    当该阶段结束时,你应该能够回答以下问题:

    需要哪些数据?

    数据该被怎样使用?

    哪些规则控制着数据的使用?

    谁会使用何种数据?

    客户想在核心功能界面或者报表上看到哪些内容?

    数据现在在哪里?

    数据是否与其他系统有交互、集成或同步?

    主题数据有哪些?

    核心数据价值几何,对可靠性的要求程度?

    并且得到如下信息:

    实体和关系

    属性和域

    可以在数据库中强制执行的业务规则

    需要使用数据库的业务过程

    (三)逻辑阶段

    逻辑阶段的主要工作是绘制E-R图,或者说是建模。建模工具很多,有不同的图形表示方法和软件。这些工具和软件的使用并非关键,笔者也不建议读者花大量时间在建模方法的选择上。对于大多数应用来说,E-R图足以描述实体间的关系。建模关键是思想而不是工具,软件只是起到辅助作用,识别实体关系才是本阶段的重点。

    除了实体关系,我们还应该考虑属性的域(值类型、范围、约束)

    (四)实现阶段

    实现阶段主要针对选择的RDBMS定义E-R图对应的表,考虑属性类型和范围以及约束。

    (五)物理阶段

    物理阶段是一个验证并调优的阶段,是在实际物理设备上部署数据库,并进行测试和调优。

    三 设计原则

    (一)降低对数据库功能的依赖

    功能应该由程序实现,而非DB实现。原因在于,如果功能由DB实现时,一旦更换的DBMS不如之前的系统强大,不能实现某些功能,这时我们将不得不去修改代码。所以,为了杜绝此类情况的发生,功能应该有程序实现,数据库仅仅负责数据的存储,以达到最低的耦合。

    (二)定义实体关系的原则

    当定义一个实体与其他实体之间的关系时,需要考量如下:

    牵涉到的实体 识别出关系所涉及的所有实体。

    所有权 考虑一个实体“拥有”另一个实体的情况。

    基数 考量一个实体的实例和另一个实体实例关联的数量。

    关系与表数量

    描述1:1关系最少需要1张表。

    描述1:n关系最少需要2张表。

    描述n:n关系最少需要3张表。

    (三)列意味着唯一的值

    如果表示坐标(0,0),应该使用两列表示,而不是将“0,0”放在1个列中。

    (四)列的顺序

    列的顺序对于表来说无关紧要,但是从习惯上来说,采用“主键+外键+实体数据+非实体数据”这样的顺序对列进行排序显然能得到比较好的可读性。

    (五)定义主键和外键

    数据表必须定义主键和外键(如果有外键)。定义主键和外键不仅是RDBMS的要求,同时也是开发的要求。几乎所有的代码生成器都需要这些信息来生成常用方法的代码(包括SQL文和引用),所以,定义主键和外键在开发阶段是必须的。之所以说在开发阶段是必须的是因为,有不少团队出于性能考虑会在进行大量测试后,在保证参照完整性不会出现大的缺陷后,会删除掉DB的所有外键,以达到最优性能。笔者认为,在性能没有出现问题时应该保留外键,而即便性能真的出现问题,也应该对SQL文进行优化,而非放弃外键约束。

    (六)选择键

    1 人工键与自然键

    人工健——实体的非自然属性,根据需要由人强加的,如GUID,其对实体毫无意义;自然健——实体的自然属性,如身份证编号。

    人工键的好处:

    键值永远不变

    永远是单列存储

    人工键的缺点:

    因为人工键是没有实际意义的唯一值,所以不能通过人工键来避免重复行。

    笔者建议全部使用人工键。原因如下:

    在设计阶段我们无法预测到代码真正需要的值,所以干脆放弃猜测键,而使用人工键。

    人工键复杂处理实体关系,而不负责任何属性描述,这样的设计使得实体关系与实体内容得到高度解耦,这样做的设计思路更加清晰。

    笔者的另一个建议是——每张表都需要有一个对用户而言有意义的自然键,在特殊情况下也许找不到这样一个项,此时可以使用复合键。这个键我在程序中并不会使用其作为唯一标识,但是却可以在对数据库直接进行查询时使用。

    使用人工键的另一根弊端,主要源自对查询性能的考量,因此选择人工键的形式(列的类型)很重要:

    自增值类型 由于类型轻巧查询效率更好,但取值有限。

    GUID 查询效率不如值类型,但是取值无限,且对开发人员更加亲切。

    2 智能健与非智能键

    智能键——键值包含额外信息,其根据某种约定好的编码规范进行编码,从键值本身可以获取某些信息;非智能键,单纯的无意义键值,如自增的数字或GUID。

    智能键是一把双刃剑,开发人员偏爱这种包含信息的键值,程序盼望着其中潜在的数据;数据库管理员或者设计者则讨厌这种智能键,原因也是很显然的,智能键对数据库是潜在的风险。前面提到,数据库设计的原则之一是不要把具有独立意义的值的组合实现到一个单一的列中,应该使用多个独立的列。数据库设计者,更希望开发人员通过拼接多个列来得到智能键,即以复合主键的形式给开发人员使用,而不是将一个列的值分解后使用。开发人员应该接受这种数据库设计,但是很多开发者却想不明白两者的优略。笔者认为,使用单一列实现智能键存在这样一个风险,就是我们可能在设计阶段无法预期到编码规则可能会在后期发生变化。比如,构成智能键的局部键的值用完而引起规则变化或者长度变化,这种编码规则的变化对于程序的有效性验证与智能键解析是破坏性的,这是系统运维人员最不希望看到的。所以笔者建议如果需要智能键,请在业务逻辑层封装(使用只读属性),不要再持久化层实现,以避免上述问题。

    (七)是否允许NULL

    关于NULL我们需要了解它的几个特性:

    任何值和NULL拼接后都为NULL。

    所有与NULL进行的数学操作都返回NULL。

    引入NULL后,逻辑不易处理。

    那么我们是否应该允许列为空呢?笔者认为这个问题的答案受到我们的开发语言的影响。以C#为例,因为引入了可空类型来处理数据库值类型为NULL的情形,所以是否允许为空对开发者来说意义并不大。但有一点必须注意,就是验证非空必须要在程序集进行处理,而不该依赖于DBMS的非空约束,必须确保完整数据(所有必须的属性均被赋值)到达DB(所谓的“安全区”,我们必须定义在多层系统中那些区域得到的数据是安全而纯净的)。

    (八)属性切割

    一种错误想法是,属性与列是1:1的关系。对于开发者,我们公开属性而非字段。举个例子来说,对于实体“员工”有“名字”这一属性,“名字”可以再被分解为“姓”和“名”,对于开发人员来说,显然第二种数据结构更受青睐(“姓”和“名”作为两个字段)。所以,在设计时我们也应该根据需要考虑是否切割属性。

    (九)规范化——范式

    当笔者还在大学时,范式是学习关系型数据库时最头疼的问题。我想也许会有读者仍然不理解范式的价值,简单来说——范式将帮助我们来保证数据的有效性和完整性。规范化的目的如下:

    消灭重复数据。

    避免编写不必要的,用来使重复数据同步的代码。

    保持表的瘦身,以及减从一张表中读取数据时需要进行的读操作数量。

    最大化聚集索引的使用,从而可以进行更优化的数据访问和联结。

    减少每张表使用的索引数量,因为维护索引的成本很高。

    规范化旨在——挑出复杂的实体,从中抽取出简单的实体。这个过程一直持续下去,直到数据库中每个表都只代表一件事物,并且表中每个描述的都是这件事物为止。

    1 规范化实体和属性(去除冗余)

    1NF:每个属性都只应表示一个单一的值,而非多个值。

    需要考虑几点:

    属性是原子性的 需要考虑熟悉是否分解的足够彻底,使得每个属性都表示一个单一的值。(和“(三)列意味着唯一的值”描述的原则相同。)分解原则为——当你需要分开处理每个部分时才分解值,并且分解到足够用就行。(即使当前不需要彻底分解属性,也应该考虑未来可能的需求变更。)

    属性的所有实例必须包含相同数量的值 实体有固定数量的属性(表有固定数量的列)。设计实体时,要让每个属性只有固定数量的值与其相关联。

    实体中出现的所有实体类型都必须不同

    当前设计不符合1NF的“臭味”:

    包含分隔符类字符的字符串数据。

    名字尾端有数字的属性。

    没有定义键或键定义不好的表。

    2 属性间的关系(去除冗余)

    2NF-实体必须符合1NF,每个属性描述的东西都必须针对整个键(可以理解为oop中类型属性的内聚性)。

    当前设计不符合2NF的“臭味”:

    重复的键属性名字前缀(设计之外的数据冗余) 表明这些值可能描述了某些额外的实体。

    有重复的数据组(设计之外的数据冗余) 这标志着属性间有函数依赖型。

    没有外键的复合主键 这标志着键中的键值可能标识了多种事物,而不是一种事物。

    3NF-实体必须符合2NF,非键属性不能描述其他非键属性。(与2NF不同,3NF处理的是非键属性和非键属性之间的关系,而不是和键属性之间的关系。

    当前设计不符合3NF的“臭味”:

    多个属性有同样的前缀。

    重复的数据组。

    汇总的数据,所引用的数据在一个完全不同的实体中。(有些人倾向于使用视图,我更倾向于使用对象集合,即由程序来完成。)

    BCNF-实体满足第一范式,所有属性完全依赖于某个键,如果所有的判定都是一个键,则实体满足BCNF。(BCNF简单地扩展了以前的范式,它说的是:一个实体可能有若干个键,所有属性都必须依赖于这些键中的一个,也可以理解为“每个键必须唯一标识实体,每个非键熟悉必须描述实体。”

    3 去除实体组合键中的冗余

    4NF-实体必须满足BCNF,在一个属性与实体的键之间,多值依赖(一条记录在整个表的唯一性由多个值组合起来决定的)不能超过一个。

    当前设计不符合4NF的“臭味”:

    三元关系(实体:实体:实体)。

    潜伏的多值属性。(如多个手机号。)

    临时数据或历史值。(需要将历史数据的主体提出,否则将存在大量冗余。)

    4 尽量将所有关系分解为二元关系

    5NF-实体必须满足4NF,当分解的信息无损的时候,确保所有关系都被分解为二元关系。

    5NF保证在第四范式中存在的任何可以分解为实体的三元关系都被分解。有的三元关系可以在不丢失信息的前提下被分解为二元关系,当分解为两个二元关系的过程要丢失信息时,关系被宣称为处于第四范式中。所以,第五范式建议是,最好把现有的三元关系都分解为3个二元关系。

    需要注意的是,规范化的结果可能是更多的表,更复杂的查询。因此,处理到何种程度,取决于性能和数据架构的多方考量。建议规范化到第四范式,原因是5NF的判断太过隐晦。例如:表X(老师,学生,课程)是一个三元关系,可以分解为表A(老师,学生),表B(学生,课程),表C(老师,课程)。表X表示某个老师是上某个学生的某个课程的老师;表A表示老师教学生;表B表示学生上课;表C表示老师教课。单独看是无法发现问题的,但是从数据出发,"表X=表A+表B+表C"并不一定成立,即不能通过连接构建分解前的数据。因为可能有多种组合,丧失了表X反馈出的业务规则。这种现象,容易在设计阶段被忽略,但好在在开放阶段会被显现,而且并不经常发生。

    推荐做法:

    尽可能地遵守上述规范化原则。

    所有属性描述的都应该是体现被建模实体的本质的内容。

    至少必须有一个键,它唯一地标识和描述了所建实体的本质。

    主键要谨慎选择。

    在逻辑阶段能做多少规范化就做多少(性能不是逻辑阶段考虑的范畴)。

    (十)选择数据类型(MS SQL 2008)

    MS SQL的常用类型:

    精确数字

    不会发生精度损失

    bit tinyint smallint int bigint decimal

    近似数字

    对于极值可能发生精度损失

    float(N) real

    日期和时间

    date time smalldatetime datetime datetime2 datetimeoffset

    二进制数据

    bingary(N) varbinary(N) varbinary(max)

    字符(串)数据

    char(N) varchar(N) varchar(max) nchar(N) nvarchar(N) nvarchar(max)

    存储任意数据

    sql_variant

    时间戳

    timestamp

    GUID

    uniqueidentifier

    XML

    不要试图使用该类型规避1NF

    xml

    空间数据

    geometry geography

    层次数据

    heirarchyid

    MS SQL中不在支持的或糟糕的类型选择

    image:被varbinary(max)取代。

    text和ntext:被varchar(max)和nvarchar(max)取代。

    money和smallmoney:开发过程中不好用,建议使用decimal。

    常用类型选择:

    类型选择的最基本规则是选择满足需要的最轻的类型,因为这样查询更快。

    bool

    建议使用bit而非char(1),因为开发语言对其支持觉好,可以直接映射为bool或bool?。

    大值数据

    使用所有备选类型中最小的那种,类型越大,查询越慢,当字节大于8000时,应使用max。

    主键

    自增主键根据预期范围选择int或bigint,GUID使用uniqueidentifier而非varchar(N)。

    (十一)优化并行

    设计DB时就应该考虑到对并行进行优化,比如,MS SQL中的timestamp类型就是极好的选择。

    四 命名规则

    表——“模块名_表名”。表名最好不要用复数,原因是在使用ORM框架开发时,代码生成器根据DB生成类定义,表生成了某个实例的类型定义,而不是实例集合。表名不要太长。原因之一,某些软件对表名最大长度有限制;原因之二,使用代码生成器往往会根据表名生产类型名称,之后懒人会直接使用这一名称,如果将太长的名称跨网络边界显然不是明智之举。

    字段——bool类型用“Is”、“Can”、“Has”等表示;日期类型命名必须包含“Date”;时间类型必须包含“Time”。

    存储过程——使用“proc_”前缀。

    视图——使用“view_”前缀。

    触发器——使用“trig_”前缀。

    f68f2add0b68e4f9810432fce46917b7.png

    本文原创发布php中文网,转载请注明出处,感谢您的尊重!

    展开全文
    weixin_42577243 2021-02-02 05:15:03
  • weixin_39922147 2020-12-19 18:20:06
  • qq_45783047 2021-08-17 14:42:37
  • tqy903 2020-12-28 03:05:41
  • weixin_35389904 2021-02-08 12:14:59
  • weixin_29150195 2021-02-02 00:41:38
  • qq_38085704 2021-09-16 00:31:00
  • weixin_31351409 2021-07-16 00:34:07
  • weixin_43902447 2021-03-19 20:09:51
  • weixin_42421331 2021-01-28 03:15:10
  • weixin_39819974 2021-01-19 23:37:11
  • wx_13554753101 2021-06-14 20:38:25
  • u012432276 2021-05-25 21:14:36
  • han1202012 2021-08-23 00:04:38
  • myRealization 2021-12-07 23:15:50
  • yiminghd2861 2021-04-01 15:35:32
  • qq_19968255 2021-05-22 09:23:07
  • u011656924 2021-03-26 09:47:09
  • qq_46139801 2021-06-01 21:24:36
  • weixin_40228600 2021-06-13 11:04:08
  • weixin_36048512 2021-01-18 21:09:53
  • weixin_45277161 2021-03-26 14:12:09
  • u011457868 2021-04-20 10:45:26
  • lvlei19911108 2021-04-16 12:02:46
  • weixin_31832223 2021-01-18 21:42:07
  • weixin_35672450 2021-01-21 04:31:23

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 47,750
精华内容 19,100
关键字:

数据库设计原则文档型