数据库管理规范

数据库管理规范数据库管理规范版本号:VI、0信息科2017年4月

数据库管理规范TOC\o"1-5"\h\z\o"CurrentDocument"1规范说明 1.2维护管理安全规范 \o"CurrentDocument"3设计规范 2\o"CurrentDocument"4、SQL编写规范 5\o"CurrentDocument"5、数据库备份与恢复策略规范 6.\o"CurrentDocument"6开发测试与上线安全规范 工\o"CurrentDocument"7监控 7\o"CurrentDocument"8连接池使用规范 8\o"CurrentDocument"9数据库重要级别划分 810数据库安装规范 \o"CurrentDocument"11业务上线数据库部分规范 9规范说明本规范就是针对数据库使用,维护管理,设计开发等。维护管理安全规范2、1数据库版本使用规范【强制】MYSQL使用5、6、39社区版本2、3数据库账户安全与管理规范2、3、1密码安全【强制】密码不少于8位,应包含数字,字母,字符密码不应该与用户名一致删除数据库默认空用户账户必须修改root密码2、4账户安全管理【强制】为应用程序设立独立访问账户禁止开发工程师通过应用帐号登录生产数据库禁止QA申请线上账号与使用账号登陆线上环境进行测试数据库只有root用户具有最高管理权限备份数据库用户要单独建立,并专属备份恢复操作数据库安装后删除或锁闭不需要使用的数据库账号数据库管理员账户可以锁定与解锁其她用户数据库管理账户在空闲5分钟以上会自动退出数据库管理权限账号不能超过2个2、5数据库日志管理规范【强制】windows、linux下的日志管理Windows系统应开启日志功能记录系统与系统中各个进程的相关信息linux的操作系统要开启系统日志Mysql数据库如果使用了innodb引擎必须打开二进制日志Mysql必须打开慢日志，并且将慢日志阀值设置为1-2秒之间对Mysql的错误日志等要进行定期查瞧，定期清理与定期备份管理2、6数据库架构规范【强制】2、6、1mysql数据库高可用•MYSQL采用复制+第三方心跳软件或者第三方集群架构（经测试后）实现近7X24小时高可用性要求，数据库架构必须能够实现故障自动迁移满足业务7X24小时持续服务要求3设计规范3、1命名规范【强制】3、1、1表的命名表的名称在T_后增加一个或者多个后缀，表名、字段名必须使用小写字母或数字;禁止出现数字开头,禁止两个下划线中间只出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。3、1、2视图命名:V_相关表名（或者根据需要另取名字）3、1、3存储过程命名:PRO_存储过程名（用英文表达存储过程意义）3、1、4函数命名:FUN_函数名称（用英文表达函数作用）3、1、5触发器命名:TRI_触发器名称（用英文表达触发器作用）3、1、6索引命名：I_表名.字段名（如果存在多字段索引，取每字段前三个字符加下划线组合,如:在hyid,hyname,hymobile上建立联合索引，命名：I—表名_hyi_hyn_hym,如果前三个截取字符相同,就从字段名称中不同的字符开始取三个字符加下划线组合,如:在zhyid,zhyname,zhymobile上建立联合索弓|,命名：I_表_id_nam_mob）3、1、7唯一索引命名:UI_表名.字段名（如果存在多字段唯一索引，取每字段前三个字符加下划线组合，如：在hyid,hyname,hymobile上建立唯一索引，命名：UI_表名_hyi_hyn_hym,如果前三个截取字符相同，就从字段名称中不同的字符开始取三个字符加下划线组合,如:在zhyid,zhyname,zhymobile上建立唯一索引，命名：UI_表_id_nam_mob）3、1、8主键命名:PK_表名一字段名（如果存在多字段主键,取每字段前三个字符加下划线组合，如：在hyid,hyname,hymobile上建立主键，命名：PK_表名_hyi_hyn_hym,如果前三个截取字符相同,就从字段名称中不同的字符开始取三个字符加下划线组合,如：在zhyid,zhyname,zhymobile上建立主键,命名：PK_表_id_nam_mob）3、1、9外键命名:FK_表名-主表名—字段名3.1.10Sequence命名:SEQ_表名一列名（或者根据需要另取名字）3.2设计原则3、2、1表的设计【强制】每个表，都必须要有主键。主键就是每行数据的唯一标识,保证主键不可随意更新修改,在不知道就是否需要主键的时候,请加上主键,它会为您的程序以及将来查找数据中的错误等等,提供一定的帮助;【强制】所有的MySQL数据库除历史原因外,都必须采用UTF8编码【建议】一个表的某列与另一表有关联关系的时候，请在应用程序维护外键关系，如果在数据库建立外键约束请遵循以下几点：尽量少使用外键,在高并发下数据库会增加较大开销;不要以数据操作不方便为理由而不建外键。加上外键以后,一些数据操作变得有些麻烦,但就是这正就是对数据一致性的保护以缺省的方式建立外键（即用deleterestrict方式），以达到保护数据一致性的目的；【强制】外键一定要建立索引。【建议】单表行数超过500万行或者单表容量超过2GB,才推荐进行分库分表3、2、2列的设计【强制】字段命名必须用英文来定义，如果太长可用缩写；【强制】相应类型数据存入相应类型字段（如:数字=>NUMBER,日期,DATE,定长字符,CHAR,变长字符=>VARCHAR2等）否则会自动进行不必要的类型转换,降低性能,破坏数据完整性【强制】表达就是与否概念的字段,必须使用is_xxx的方式命名,数据类型就是unsignedtinyint（1表示就是,0表示否）【强制】小数类型为decimal,禁止使用float与double【建议】varchar就是可变长字符串，不预先分配存储空间,长度不要超过5000,如果存储长度大于此值,定义字段类型为text,独立出来一张表,用主键来对应,避免影响其它字段索引效率【建议】字段的宽度要在一定时间内足够用，但也不要过宽，占用过多的存储空间;【建议】除非必要,否则尽量不加冗余列。所谓冗余列，就是指能通过其她列计算出来的列,或者就是与某列表达同一含义的列,或者就是从其她表复制过来的列等等。冗余列需要应用程序来维护一致性,相关列的值改变的时候,冗余列也需要随之修改,而这一规则未必所有人都知道,就有可能因此发生不一致的情况。如果就是应用的特殊需要,或者就是为了优化某些逻辑很复杂的查询等操作,可以加冗余列;3、2、3索引的设计【强制】业务上具有唯一特性的字段,即使就是组合字段,也必须建成唯一索引【强制】超过三个表禁止join。需要join的字段，数据类型保持绝对一致;多表关联【强制】查询时，保证被关联的字段需要有索引【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引,【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决【强制】如果有orderby的场景，请注意利用索引的有序性。orderby最后的字段就是组合索引的一部分,并且放在索引组合顺序的最后,避免出现的情况,影响查询性能。正例:wherea二？andb二？orderbyc;索弓|:2_5_。【强制】建组合索引的时候，区分度最高的在最左边【建议】性能测试阶段收集90%SQL后,统一设计建立索引【建议】根据实际文本区分度决定索引长度。说明:索引的长度与区分度就是一对矛盾体,一般对字符串类型数据,长度为20的索引，区分度会高达90%以上，可以使用count(distinctleft(列名,索引长度))/count(*)的区分度来确定。4、SQL编写规范【强制】禁止开发人员在SQL中添加Hint,Hint只能由DBA审核后添加。【强制】禁止使用悲观锁定，即读锁select…forupdate。【强制】禁止在开发代码中使用DDL语句，比如truncate,altertable…等。【强制】禁止使用与系统环境相关的mysqlinside函数洌如UUID(),USER(),sysdate()这些函数,这样可能会导致主从同步不一致。【强制】禁止在充当读角色的mysqld上执行写操作(写操作指update,delete,insert),因为可能导致同步中断或者数据不一致。【强制】不要使用count(列名)或count(常量)来替代count(),count()就就是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL与非NULL无关。【强制】说明:count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行【强制】使用ISNULL()来判断就是否为NULL值。注意:NULL与任何值的直接比较都为NULL【强制】禁止使用存储过程，存储过程难以调试与扩展，更没有移植性【强制】在表查询中,一律不要使用*作为查询的字段列表，需要哪些字段必须明确写明【强制】表连接操作中，作为连接条件的字段的数据类型严格一致。【强制】如果表连接字段数据类型不一致，在SQL中用显示用类型转换,具体情况咨询DBA。示例正确用法1:selectcol1,col2fromtbl1,tbl2wheretbl1、col3=tbl2、col4;其中“tbl1、col3”与“tbl2、col4”数据类型严格一致。【建议】必须正确开启事务并且显式关闭事务【建议】使用域名连接数据库。禁止线上应用使用IP地址连接数据库。【建议】in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量,控制在1000个之内。•【建议】禁止DML语句的where条件中包含恒真条件(如:1=1)5、数据库备份与恢复策略规范5、1备份策略数据库备份需要在本地,从库,存储至少三个地方保留,确保备份不丢失数据库备份周期根据业务需求,默认采取每日备份,保留7天,每周周一备份,保留30天,每月一号备份,保留100天三个维度进行数据库备份方式采取mysqldump方式进行每日备份或根据需要进行INNODB增量备份数据库备份采取每天业务负载低峰时段的凌晨3-5点进行备份数据库备份用户使用专有备份用户,权限仅仅为可以进行备份与恢复数据库备份必须保留两个以上存储点,避免因误操作或磁盘损坏丢失备份文件数据备份设置保留周期,周期以外的数据库备份文件视其需要进行刻盘归档处理数据库备份采取每月定期验证备份文件有效性的验证测试备份脚本如下:#!/bin/bash#ThisisaShellScriptForAutoDBBackupDBName=xgw-global-10-BackupPath=/data/mysqlbak/daybakLogcd/data/mysqlbak/daybakNewFile=$DBName$(date+%Y%m%d-%H-%M)、tar、gzDumpFile=$DBName$(date+%Y%m%d-%H-%M)、sql#OldFile=$DBName$(date+%Y%m%d-%H-%M--date='7daysago')、tar、gz#if[!-d$BackupPath];thenmkdir$BackupPathfiecho" ">>$LogFileecho$(date+"%y-%m-%d%H:%M:%S")>>$LogFileecho" ">>$LogFile#DeleteOldFileif[-f$OldFile];thenrm-f$OldFile>>$LogFile2>&1echo"[$OldOld!">>$LogFileelseecho"[$OldOldBackupFile!">>$LogFilefi#CreateNewFileif[-f$NewFile];thenecho"[$NewFile]TheBackupexists,Can'tBackup!"else/usr/local/mysql/bin/mysqldump--single-transaction-A--master-data=2--opt--all-databases>$DumpFiletarczvf$NewFile$DumpFile>>$LogFile2>&1echo"[$NewSuccess!">>$LogFilerm-rf$DumpFilefiecho" ">>$LogFilecp/data/mysqlbak/daybak/$NewFile/data/dbbak/sitebak/>>$LogFile2>&1find/data/dbbak/sitebak/*-mtime+7-execrm-rf{}\;find/data/mysqlbak/daybak/*-mtime+7-execrm-rf{}\;5、2恢复策略数据库恢复采取专有数据库恢复账号进行数据库恢复要先制定失败回退计划并保证其可行数据库如果就是对已有业务数据库换进行时,必须先对现有数据库环境进行备份数据库恢复必须进行测试验证数据库恢复后要进行全面的数据与业务验证数据库恢复成功后,必须立即及逆行那个一次全面的数据库备份6开发测试与上线安全规范开发测试期间，数据库由DBA维护，所有开发账号由DBA统一建立,开发人员拥有数据库的增删改查以及库结构表结构变更权限测试完毕正式上线前,由DBA统一进行数据库结构及数据上线部署,所有数据库权限统一收回并按照实际需求进行授权部署生产数据库中应用程序账号只赋予最小增删改查权限,不赋予变更数据库设置库结构以及表结构权限，只有DBA具有最高权限生产数据库任何库结构表结构索引结构以及数据库对象建立删除修改都需要经过DBA操作7监控【强制】所有生产库必须加入监控系统，不加入监控系统不得上线生产【强制】MYSQL监控项有CPU,内存,I/O,MYSQL进程,MYSQL复制状态,MYSQL复制延迟,MYSQL端口,MYSQL锁等待【强制】MONGODB监控项有CPU,内存,I/O,MONGODB进程,MYSQL复制集状态,MONGODB端口8连接池使用规范建议在性能测试阶段进行连接池最优设置连接池必须开启autoconnect设置最小连接数设置为50-100最大连接数设置为500-1000连接池idletime设置为10000-200009数据库重要级别划分•目前根据数据库存储数据的安全重要程度暂时将数据库从高到低划分为三个级别(A级,B级,C级)A级数据库B级数据库C级数据库备份恢复增量备份,数据库恢复要求达到任意指定时间点恢复，备份至少保留本地与异地两份增量备份或全库逻辑备份，备份至少保留本地与异地两份全库逻辑备份，备份至少保留本地与异地两份高可用7X24小时高可用7X24小时高可用非7X24小时高可用，允许服务一小时以内中断审计重要数据增删改查需要审计不需要不需要数据存储每表空间独立数据文件,数据与索引独立存储每表空间独立数据文件,数据与索引独立存储不要求备份恢复测试间隔每月每季度半年各级别数据类型指导财务交易类数据运营类数据日志记录类数据数据库安装规范数据库安装一律采用解压安装方式,具体步骤如下:mkdir-p/data/mysql/datamkdir-p/data/mysql/logsgroupaddmys