数据库管理规范

数据库管理规范版本号：V1.0信息科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密码安全【强制】密码不少于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_表名_主表名_字段名Sequence命名：SEQ_表名_列名（或者根据需要另取名字）设计原则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最后的字段是组合索引的一部分，并且放在索引组合次序的最后，避免出现file_sort的状况，影响查询性能。正例：wherea=?andb=?orderbyc;索引：a_b_c【强制】建组合索引的时候，分辨度最高的在最左边【建议】性能测试阶段收集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/daybakLogFile=/data/mysqlbak/daybak/db.logcd/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"[$OldFile]DeleteOldFileSuccess!">>$LogFileelseecho"[$OldFile]NoOldBackupFile!">>$LogFilefi#CreateNewFileif[-f$NewFile];thenecho"[$NewFile]TheBackupFileisexists,Can'tBackup!"else/usr/local/mysql/bin/mysqldump--single-transaction-A--master-data=2--opt--all-databases>$DumpFiletarczvf$NewFile$DumpFile>>$LogFile2>&1echo"[$NewFile]BackupSuccess!">>$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-09数据库重要级别划分现在根据数据库存储数据的安全重要程度临时将数据库从高到低划分为三个级别(A级,B级,C级)A级数据库B级数据库C级数据库备份恢复增量备份，数据库恢复规定达成任意指定时间点恢复，备份最少保存本地和异地两份增量备份或全库逻辑备份，备份最少保存本地和异地两份全库逻辑备份，备份最少保存本地和异地两份高可用7X24小时高可用7X24小时高可用非7X24小时高可用，允许服务一小时以内中断审计重要数据增删改查需要审计不需要不需要数据存储每表空间独立数据文献，数据与索引独立存储每表空间独立数据文献，数据与索引独立存储不规定备份恢复测试间隔每月每季度六个月各级别数据类型指导财务交易类数据运行类数据日志统计类数据10数据库安装规范数据库安装一律采用解压安装方式，具体环节以下：mkdir-p/data/mysql/datamkdir-p/data/mysql/logsgroupaddmysqluseradd-gmysqlmysqlchown-Rmy