数据库管理规范.pdf
.z.版本号：版本号：V1.0V1.0信息科信息科20172017 年年 4 4 月月.数据库管理规范数据库管理规范-数据库管理规范数据库管理规范1 1 规范说明规范说明 3 32 2 维护管理安全规范维护管理安全规范 3 33 3 设计规范设计规范 4 44.SQL4.SQL 编写规范编写规范 7 75.5.数据库备份与恢复策略规范数据库备份与恢复策略规范 8 86 6 开发测试与上线安全规范开发测试与上线安全规范 10107 7 监控监控 10108 8 连接池使用规范连接池使用规范 10109 9 数据库重要级别划分数据库重要级别划分 11111010 数据库安装规范数据库安装规范 11111111 业务上线数据库部分规范业务上线数据库部分规范 1212-可修编.-1 1 规范说明规范说明本规范是针对数据库使用，维护管理，设计开发等。2 2 维护管理安全规范维护管理安全规范2.12.1 数据库版本使用规范数据库版本使用规范【强制】MYSQL 使用 5.6.39 社区版本2.32.3 数据库账户安全与管理规范数据库账户安全与管理规范2.3.12.3.1 密码安全密码安全【强制】密码不少于 8 位，应包含数字，字母，字符密码不应该和用户名一致删除数据库默认空用户账户必须修改 root 密码2.42.4 账户安全管理账户安全管理【强制】为应用程序设立独立访问账户禁止开发工程师通过应用XX 登录生产数据库-可修编.-禁止 QA 申请线上账号与使用账号登陆线上环境进行测试数据库只有 root 用户具有最高管理权限备份数据库用户要单独建立，并专属备份恢复操作数据库安装后删除或锁闭不需要使用的数据库账号数据库管理员账户可以锁定和解锁其他用户数据库管理账户在空闲 5 分钟以上会自动退出数据库管理权限账号不能超过2 个2.52.5 数据库日志管理规范数据库日志管理规范【强制】windows、linux 下的日志管理Windows 系统应开启日志功能，记录系统和系统中各个进程的相关信息linux 的操作系统要开启系统日志Mysql 数据库如果使用了 innodb 引擎必须打开二进制日志Mysql 必须打开慢日志，并且将慢日志阀值设置为1-2 秒之间对 Mysql 的错误日志等要进行定期查看，定期清理和定期备份管理2.62.6 数据库架构规范数据库架构规范【强制】2.6.12.6.1mysqlmysql 数据库高可用数据库高可用MYSQL 采用复制+第三方心跳软件或者第三方集群架构(经测试后)实现近 7X24 小时高可用性要求，数据库架构必须能够实现故障自动迁移，满足业务7X24 小时持续服务要求3 3 设计规范设计规范3.13.1 命名规范命名规范【强制】3.1.13.1.1表的命名表的命名-可修编.-表的名称在 T_后增加一个或者多个后缀，表名、字段名必须使用小写字母或数字；禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。3.1.23.1.2 视图命名视图命名：V_相关表名（或者根据需要另取名字）3.1.33.1.3 存储过程命名存储过程命名：PRO_存储过程名（用英文表达存储过程意义）3.1.43.1.4 函数命名函数命名：FUN_函数名称（用英文表达函数作用）3.1.53.1.5 触发器命名触发器命名：TRI_触发器名称（用英文表达触发器作用）3.1.63.1.6 索引命名索引命名：I_表名_字段名(如果存在多字段索引，取每字段前三个字符加下划线组合，如：在 hyid,hyname,hymobile 上建立联合索引，命名：I_表名_hyi_hyn_hym,如果前三个截取字符相同，就从字段名称中不同的字符开始取三个字符加下划线组合，如：在zhyid,zhyname,zhymobile 上建立联合索引，命名：I_表_id_nam_mob)3.1.73.1.7 唯一索引命名唯一索引命名：UI_表名_字段名(如果存在多字段唯一索引，取每字段前三个字符加 下 划 线 组 合，如：在hyid,hyname,hymobile 上 建 立 唯 一 索 引，命 名：UI_ 表 名_hyi_hyn_hym,如果前三个截取字符相同，就从字段名称中不同的字符开始取三个字符加下划线组合，如：在 zhyid,zhyname,zhymobile上建立唯一索引，命名：UI_表_id_nam_mob)3.1.83.1.8主键命名主键命名：PK_表名_字段名(如果存在多字段主键，取每字段前三个字符加下划线组合，如：在 hyid,hyname,hymobile 上建立主键，命名：PK_表名_hyi_hyn_hym,如果前三个截取字符相同，就从字段名称中不同的字符开始取三个字符加下划线组合，如：在zhyid,zhyname,zhymobile 上建立主键，命名：PK_表_id_nam_mob)3.1.93.1.9 外键命名外键命名：FK_表名_主表名_字段名3.1.103.1.10SequenceSequence 命名命名：SEQ_表名_列名（或者根据需要另取名字）3.23.2 设计原则设计原则3.2.13.2.1 表的设计表的设计【强制】每个表，都必须要有主键。主键是每行数据的唯一标识，保证主键不可随意更新修改，在不知道是否需要主键的时候，请加上主键，它会为你的程序以及将来查找数据中的错误等等，提供一定的帮助；【强制】所有的 MySQL 数据库除历史原因外，都必须采用UTF8 编码【建议】一个表的某列与另一表有关联关系的时候，请在应用程序维护外键关系，如果-可修编.-在数据库建立外键约束请遵循以下几点：尽量少使用外键，在高并发下数据库会增加较大开销；不要以数据操作不方便为理由而不建外键。加上外键以后，一些数据操作变得有些麻烦，但是这正是对数据一致性的保护以缺省的方式建立外键（即用delete restrict 方式），以达到保护数据一致性的目的；【强制】外键一定要建立索引。【建议】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表3.2.23.2.2 列的设计列的设计【强制】字段命名必须用英文来定义，如果太长可用缩写；【强制】相应类型数据存入相应类型字段(如：数字=NUMBER，日期=DATE，定长字符=CHAR，变长字符=VARCHAR2 等）否则会自动进行不必要的类型转换，降低性能，破坏数据完整性【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1 表示是，0 表示否）【强制】小数类型为 decimal，禁止使用 float 和 double【建议】varchar 是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为 text，独立出来一 X 表，用主键来对应，避免影响其它字段索引效率【建议】字段的宽度要在一定时间内足够用，但也不要过宽，占用过多的存储空间；【建议】除非必要，否则尽量不加冗余列。所谓冗余列，是指能通过其他列计算出来的列，或者是与某列表达同一含义的列，或者是从其他表复制过来的列等等。冗余列需要应用程序来维护一致性，相关列的值改变的时候，冗余列也需要随之修改，而这一规则未必所有人都知道，就有可能因此发生不一致的情况。如果是应用的特殊需要，或者是为了优化某些逻辑很复杂的查询等操作，可以加冗余列；3.2.33.2.3 索引的设计索引的设计【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联-可修编.-【强制】查询时，保证被关联的字段需要有索引【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决【强制】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort 的情况，影响查询性能。正例：where a=?and b=?order by c;索引：a_b_c【强制】建组合索引的时候，区分度最高的在最左边【建议】性能测试阶段收集 90%SQL 后，统一设计建立索引【建议】根据实际文本区分度决定索引长度。说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名,索引长度)/count(*)的区分度来确定。4.SQL4.SQL 编写规范编写规范【强制】禁止开发人员在SQL 中添加 Hint，Hint 只能由 DBA 审核后添加。【强制】禁止使用悲观锁定，即读锁 select for update。【强制】禁止在开发代码中使用DDL 语句，比如 truncate，alter table 等。【强制】禁止使用与系统环境相关的mysql inside 函数，列如 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：select col1,col2 from tbl1,tbl2where tbl1.col3=tbl2.col4;其中“tbl1.col3”与“tbl2.col4”数据类型严格一致。【建议】必须正确开启事务并且显式关闭事务【建议】使用域名连接数据库。禁止线上应用使用IP 地址连接数据库。【建议】in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。【建议】禁止 DML 语句的 where 条件中包含恒真条件（如：1=1）5.5.数据库备份与恢复策略规范数据库备份与恢复策略规范5.15.1 备份策略备份策略数据库备份需要在本地，从库，存储至少三个地方保留，确保备份不丢失数据库备份周期根据业务需求，默认采取每日备份，保留 7 天，每周周一备份，保留30 天，每月一号备份，保留100 天三个维度进行数据库备份方式采取 mysqldump 方式进行每日备份或根据需要进行INNODB 增量备份数据库备份采取每天业务负载低峰时段的凌晨3-5 点进行备份数据库备份用户使用专有备份用户，权限仅仅为可以进行备份和恢复数据库备份必须保留两个以上存储点，避免因误操作或磁盘损坏丢失备份文件数据备份设置保留周期，周期以外的数据库备份文件视其需要进行刻盘归档处理数据库备份采取每月定期验证备份文件有效性的验证测试备份脚本如下：#!/bin/bash#This is a ShellScript For Auto DB BackupDBName=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=7 days ago).tar.gz#if !-d$BackupPath;thenmkdir$BackupPathfiecho-$LogFileecho$(date+%y-%m-%d%H:%M:%S)$LogFileecho-$LogFile#Delete Old Fileif -f$OldFile;thenrm-f$OldFile$LogFile 2&1echo$OldFileDelete Old File Success!$LogFileelseecho$OldFileNo Old Backup File!$LogFilefi#Create NewFileif -f$NewFile;thenecho$NewFile The Backup File is exists,Cant Backup!else/usr/local/mysql/bin/mysqldump-single-transaction-A-master-data=2-opt-all-databases$DumpFiletar czvf$NewFile$DumpFile$LogFile 2&1echo$NewFileBackup Success!$LogFilerm-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.25.2 恢复策略恢复策略数据库恢复采取专有数据库恢复账号进行数据库恢复要先制定失败回退计划并保证其可行-可修编.-数据库如果是对已有业务数据库换进行时，必须先对现有数据库环境进行备份数据库恢复必须进行测试验证数据库恢复后要进行全面的数据和业务验证数据库恢复成功后，必须立即及逆行那个一次全面的数据库备份6 6 开发测试与上线安全规范开发测试与上线安全规范开发测试期间，数据库由 DBA 维护，所有开发账号由 DBA 统一建立，开发人员拥有数据库的增删改查以及库结构表结构变更权限测试完毕正式上线前，由 DBA 统一进行数据库结构及数据上线部署，所有数据库权限统一收回并按照实际需求进行授权部署生产数据库中应用程序账号只赋予最小增删改查权限，不赋予变更数据库设置库结构以及表结构权限，只有 DBA 具有最高权限生产数据库任何库结构表结构索引结构以及数据库对象建立删除修改都需要经过 DBA操作7 7 监控监控【强制】所有生产库必须加入监控系统，不加入监控系统不得上线生产【强制】MYSQL 监控项有 CPU,内存，I/O，MYSQL 进程，MYSQL 复制状态，MYSQL 复制延迟，MYSQL 端口，MYSQL 锁等待【强制】MONGODB 监控项有 CPU,内存，I/O，MONGODB 进程，MYSQL 复制集状态，MONGODB 端口8 8 连接池使用规范连接池使用规范建议在性能测试阶段进行连接池最优设置连接池必须开启 autoconnect 设置最小连接数设置为 50-100最 XX 接数设置为 500-1000连接池 idletime 设置为 10000-20000-可修编.-9 9 数据库重要级别划分数据库重要级别划分目前根据数据库存储数据的安全重要程度暂时将数据库从高到低划分为三个级别(A级,B 级,C 级)备份恢复A 级数据库增量备份，数据库恢复要求达到任意指定时间点恢复，备份至少保留本地和异地两份7X24 小时高可用B 级数据库增量备份或全库逻辑备份，备份至少保留本地和异地两份C 级数据库全库逻辑备份，备份至少保留本地和异地两份高可用7X24 小时高可用非 7X24 小时高可用，允许服务一小时以内中断不需要不要求审计数据存储重要数据增删改查需要审计每表空间独立数据文件，数据与索引独立存储每月财务交易类数据不需要每表空间独立数据文件，数据与索引独立存储每季度运营类数据备份恢复测试间隔各级别数据类型指导半年日志记录类数据1010 数据库安装规范数据库安装规范数据库安装一律采用解压安装方式，具体步骤如下：mkdir-p/data/mysql/datamkdir-p/data/mysql/logsgroupadd mysqluseradd-g mysql mysqlchown-R mysql:mysql/data/mysqlcd/usr/localtar-zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz-可修编.-ln-s mysql-5.6.36-linux-glibc2.5-x86_64 mysqlcd mysqlscripts/mysql_install_db-user=mysql-basedir=/usr/local/mysql-datadir=/data/mysql/datachown-R root.chown-R mysql datachgrp-R mysql.cp support-files/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-skip-grant-tables&use mysqldelete from user where user=;use mysqlupdate mysql.user set password=password(root)where user=root;flush privileges;1111 业务上线数据库部分规范业务上线数据库部分规范业务上线如果涉及数据库操作，需要提前半天通知 DBA 有数据库操作，并且将升级的-可修编.-脚本发给 DBA，由 DBA 进行审查DBA 审核后，如无问题先在预发布环境进行数据库升级，升级完通知业务部门进行预发布测试业务部门测试没有问题后，由DBA 在晚上升级时间点进行生产数据库升级-可修编.
