数据库管理规范.doc

数据库管理规范版本号：V1.0信息科2017年4月数据库管理规范1 规范说明22 维护管理安全规范23设计规范34. SQL编写规范65. 数据库备份与恢复策略规范76开发测试与上线安全规范97 监控108连接池使用规范109 数据库重要级别划分1010 数据库安装规范1111 业务上线数据库部分规范121 规范说明本规范是针对数据库使用，维护管理,设计开发等.2 维护管理安全规范2。1数据库版本使用规范【强制】MYSQL使用5.6。39社区版本2.3数据库账户安全与管理规范2.3。1 密码安全【强制】l 密码不少于8位，应包含数字,字母，字符l 密码不应该和用户名一致l 删除数据库默认空用户账户l 必须修改root密码2。4账户安全管理【强制】l 为应用程序设立独立访问账户l 禁止开发工程师通过应用帐号登录生产数据库l 禁止QA申请线上账号与使用账号登陆线上环境进行测试l 数据库只有root用户具有最高管理权限l 备份数据库用户要单独建立，并专属备份恢复操作l 数据库安装后删除或锁闭不需要使用的数据库账号l 数据库管理员账户可以锁定和解锁其他用户l 数据库管理账户在空闲5分钟以上会自动退出l 数据库管理权限账号不能超过2个2。5数据库日志管理规范【强制】windows、linux下的日志管理l Windows系统应开启日志功能，记录系统和系统中各个进程的相关信息l linux的操作系统要开启系统日志l Mysql数据库如果使用了innodb引擎必须打开二进制日志l Mysql必须打开慢日志，并且将慢日志阀值设置为12秒之间l 对Mysql的错误日志等要进行定期查看,定期清理和定期备份管理2。6数据库架构规范【强制】2。6。1 mysql数据库高可用l 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.10 Sequence命名:SEQ_表名_列名（或者根据需要另取名字）3.2 设计原则3。2.1表的设计l 【强制】每个表，都必须要有主键.主键是每行数据的唯一标识,保证主键不可随意更新修改，在不知道是否需要主键的时候，请加上主键，它会为你的程序以及将来查找数据中的错误等等，提供一定的帮助；l 【强制】所有的MySQL数据库除历史原因外,都必须采用UTF8编码l 【建议】一个表的某列与另一表有关联关系的时候,请在应用程序维护外键关系，如果在数据库建立外键约束请遵循以下几点:尽量少使用外键，在高并发下数据库会增加较大开销；不要以数据操作不方便为理由而不建外键。加上外键以后,一些数据操作变得有些麻烦，但是这正是对数据一致性的保护以缺省的方式建立外键(即用delete restrict方式），以达到保护数据一致性的目的；l 【强制】外键一定要建立索引。l 【建议】单表行数超过 500万行或者单表容量超过 2GB，才推荐进行分库分表3.2。2列的设计l 【强制】字段命名必须用英文来定义,如果太长可用缩写；l 【强制】相应类型数据存入相应类型字段（如：数字=NUMBER，日期=DATE，定长字符=CHAR，变长字符=VARCHAR2等)否则会自动进行不必要的类型转换，降低性能，破坏数据完整性l 【强制】表达是与否概念的字段,必须使用 is_xxx的方式命名，数据类型是 unsigned tinyint（ 1表示是,0表示否)l 【强制】小数类型为 decimal，禁止使用 float和 doublel 【建议】varchar是可变长字符串，不预先分配存储空间，长度不要超过 5000,如果存储长度大于此值,定义字段类型为 text,独立出来一张表，用主键来对应，避免影响其它字段索引效率l 【建议】字段的宽度要在一定时间内足够用,但也不要过宽,占用过多的存储空间；l 【建议】除非必要，否则尽量不加冗余列。所谓冗余列,是指能通过其他列计算出来的列，或者是与某列表达同一含义的列，或者是从其他表复制过来的列等等.冗余列需要应用程序来维护一致性,相关列的值改变的时候，冗余列也需要随之修改，而这一规则未必所有人都知道，就有可能因此发生不一致的情况。如果是应用的特殊需要,或者是为了优化某些逻辑很复杂的查询等操作，可以加冗余列；3。2。3 索引的设计l 【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引l 【强制】超过三个表禁止 join。需要 join的字段，数据类型保持绝对一致；多表关联l 【强制】查询时,保证被关联的字段需要有索引l 【强制】在 varchar字段上建立索引时,必须指定索引长度，没必要对全字段建立索引，l 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决l 【强制】如果有 order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort的情况,影响查询性能。 正例：where a=？ and b=? order by c； 索引：a_b_cl 【强制】建组合索引的时候，区分度最高的在最左边l 【建议】性能测试阶段收集90%SQL后，统一设计建立索引l 【建议】根据实际文本区分度决定索引长度。 说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据，长度为 20的索引，区分度会高达 90以上，可以使用 count(distinct left(列名， 索引长度）/count（）的区分度来确定.4。 SQL编写规范l 【强制】禁止开发人员在SQL中添加 Hint，Hint只能由DBA审核后添加。l 【强制】禁止使用悲观锁定，即读锁 select for update。l 【强制】禁止在开发代码中使用DDL语句，比如 truncate，alter table 等。l 【强制】禁止使用与系统环境相关的mysql inside 函数，列如UUID()，USER（)，sysdate（）这些函数,这样可能会导致主从同步不一致。l 【强制】禁止在充当读角色的mysqld上执行写操作（写操作指update,delete,insert)，因为可能导致同步中断或者数据不一致。l 【强制】不要使用 count（列名)或 count（常量)来替代 count（），count()就是 SQL92定义的标准统计行数的语法,跟数据库无关，跟 NULL和非 NULL无关。l 【强制】说明：count()会统计值为 NULL的行,而 count(列名）不会统计此列为 NULL值的行l 【强制】使用 ISNULL（）来判断是否为 NULL值。注意：NULL与任何值的直接比较都为 NULLl 【强制】禁止使用存储过程,存储过程难以调试和扩展，更没有移植性l 【强制】在表查询中，一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写明l 【强制】表连接操作中，作为连接条件的字段的数据类型严格一致.l 【强制】如果表连接字段数据类型不一致，在SQL中用显示用类型转换，具体情况咨询DBA。示例正确用法1：select col1， col2 from tbl1， tbl2where tbl1.col3 = tbl2.col4；其中“tbl1。col3与“tbl2.col4”数据类型严格一致。l 【建议】必须正确开启事务并且显式关闭事务l 【建议】使用域名连接数据库。禁止线上应用使用IP地址连接数据库.l 【建议】in操作能避免则避免,若实在避免不了，需要仔细评估 in后边的集合元素数量，控制在 1000个之内。l 【建议】禁止DML语句的where条件中包含恒真条件(如：1=1）5。 数据库备份与恢复策略规范5。1 备份策略l 数据库备份需要在本地，从库，存储至少三个地方保留，确保备份不丢失l 数据库备份周期根据业务需求，默认采取每日备份，保留7天，每周周一备份，保留30天,每月一号备份,保留100天三个维度进行l 数据库备份方式采取mysqldump方式进行每日备份或根据需要进行INNODB增量备份l 数据库备份采取每天业务负载低峰时段的凌晨35点进行备份l 数据库备份用户使用专有备份用户，权限仅仅为可以进行备份和恢复l 数据库备份必须保留两个以上存储点，避免因误操作或磁盘损坏丢失备份文件l 数据备份设置保留周期,周期以外的数据库备份文件视其需要进行刻盘归档处理l 数据库备份采取每月定期验证备份文件有效性的验证测试l 备份脚本如下:#！/bin/bash This is a ShellScript For Auto DB Backup DBName=xgw-global-10 BackupPath=/data/mysqlbak/daybakLogFile=/data/mysqlbak/daybak/db。logcd /data/mysqlbak/daybakNewFile=DBName(date +Ymd%HM)。tar。gzDumpFile=DBName$(date +Ym%dH%M）。sqlOldFile=DBName$(date +Y%m%dHM date=7 days ago）.tar.gz if ! -d BackupPath ； then mkdir $BackupPathfiecho ”-” LogFileecho （date +%ym%d %H:M：%S) LogFileecho ”-” $LogFileDelete Old Fileif f OldFile ； then rm f OldFile LogFile 21 echo ”$OldFileDelete Old File Success！” LogFileelse echo ”$OldFileNo Old Backup File！ LogFilefi Create NewFileif f NewFile ； then echo NewFile The Backup File is exists,Cant Backup! else /usr/local/mysql/bin/mysqldump single-transaction A -masterdata=2 -opt all-databases $DumpFile tar czvf NewFile DumpFile LogFile 2&1 echo NewFileBackup Success!” $LogFile rm rf DumpFilefiecho ”- $LogFilecp /data/mysqlbak/daybak/NewFile /data/dbbak/sitebak/ $LogFile 2&1find /data/dbbak/sitebak/ mtime +7 exec rm -rf ；find /data/mysqlbak/daybak/* mtime +7 -exec rm rf ;5。2 恢复策略l 数据库恢复采取专有数据库恢复账号进行l 数据库恢复要先制定失败回退计划并保证其可行l 数据库如果是对已有业务数据库换进行时,必须先对现有数据库环境进行备份l 数据库恢复必须进行测试验证l 数据库恢复后要进行全面的数据和业务验证l 数据库恢复成功后，必须立即及逆行那个一次全面的数据库备份6开发测试与上线安全规范l 开发测试期间，数据库由DBA维护，所有开发账号由DBA统一建立,开发人员拥有数据库的增删改查以及库结构表结构变更权限l 测试完毕正式上线前，由DBA统一进行数据库结构及数据上线部署，所有数据库权限统一收回并按照实际需求进行授权部署l 生产数据库中应用程序账号只赋予最小增删改查权限,不赋予变更数据库设置库结构以及表结构权限，只有DBA具有最高权限l 生产数据库任何库结构表结构索引结构以及数据库对象建立删除修改都需要经过DBA操作7监控l 【强制】所有生产库必须加入监控系统,不加入监控系统不得上线生产l 【强制】MYSQL监控项有CPU，内存，I/O,MYSQL进程，MYSQL复制状态，MYSQL复制延迟，MYSQL端口，MYSQL锁等待l 【强制】MONGODB监控项有CPU，内存,I/O，MONGODB进程,MYSQL复制集状态,MONGODB端口8连接池使用规范l 建议在性能测试阶段进行连接池最优设置l 连接池必须开启autoconnect设置l 最小连接数设置为50100l 最大连接数设置为5001000l 连接池idletime设置为10000-200009 数据库重要级别划分l 目前根据数据库存储数据的安全重要程度暂时将数据库从高到低划分为三个级别（A级，B级，C级）A级数据库B级数据库C级数据库备份恢复增量备份，数据库恢复要求达到任意指定时间点恢复,备份至少保留本地和异地两份增量备份或全库逻辑备份,备份至少保留本地和异地两份全库逻辑备份，备份至少保留本地和异地两份高可用7X24小时高可用7X24小时高可用非7X24小时高可用,允许服务一小时以内中断审计重要数据增删改查需要审计不需要不需要数据存储每表空间独立数据文件，数据与索引独立存储每表空间独立数据文件,数据与索引独立存储不要求备份恢复测试间隔每月每季度半年各级别数据类型指导财务交易类数据运营类数据日志记录类数据10 数据库安装规范数据库安装一律采用解压安装方式，具体步骤如下：mkdir -p /data/mysql/datamkdir -p /data/mysql/logsgroupaddmysqluseradd g mysqlmysqlchown R mysql：mysql /data/mysqlcd /usr/localtar -zxvf mysql-5。6。36linuxglibc2.5x86_64。tar。gzln -s mysql-5。6。36linuxglibc2。5-x86_64 mysqlcd mysqlscripts/mysql_install_db -user=mysql basedir=/usr/local/mysql datadir=/data/mysql/data chown -R root .chown R mysql datachgrp R mysql 。cp supportfiles/mysql.server /etc/rc。d/init.d/mysqldcp bin/mysql /usr/bin/chkconfig add mysqld/etc/rc。d/init.d/mysqld start/usr/local/mysql/bin/safe_mysqld -skipgranttables &use mysqldelete from user where user=;use mysqlupdate mysql。user set password=password（”root”） where user=”root”；flush privileges；11 业务上线数据库部分规范l 业务上线如果涉及数据库操作，需要提前半天邮件通知DBA有数据库操作，并且将升级的脚本发给DBA，由DBA进行审查l DBA审核后,如无问题先在预发布环境进行数据库升级,升级完通知业务部门进行预发布测试l 业务部门测试没有问题后，由DBA在晚上升级时间点进行生产数据库升级9