本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告
  • 首页
  • MySQL
  • 【MySQL运维】生产环境下的数据库规范建议

【MySQL运维】生产环境下的数据库规范建议

发布:TangLu2020-11-9 13:37分类: MySQL 标签: mysql 数据库

一、数据库划分环境

需要保证各个环境的MySQL服务器有对应的用户权限,权限划分明确

· dev:开发环境。开发人员可拥有DDL\DML权限,但需要保证不影响其他开发同事。

· test:测试环境。开发人员可拥有DDL\DML权限。
· online:生产环境。开发人员不允许直接在线上环境进行数据库操作,如果需要操作必须找DBA进行操作并进行相应记录,禁止进行压力测试。

二、命名规范
基本命名规则
使用有意义的英文词汇,词汇中间以下划线分隔。(不要用拼音)
只能使用英文字母,数字,下划线,并以英文字母开头。
库、表、字段全部采用小写,不要使用驼峰式命名。
避免用ORACLE、MySQL的保留字,如desc,关键字如index。
命名禁止超过32个字符,须见名之意,建议使用名词不是动词
数据库,数据表一律使用前缀
临时库、表名必须以tmp为前缀,并以日期为后缀
备份库、表必须以bak为前缀,并以日期为后缀
为什么库、表、字段全部采用小写?
在 MySQL 中,数据库和表对就于那些目录下的目录和文件。因而,操作系统的敏感性决定数据库和表命名的大小写敏感。
Windows下是不区分大小写的。
Linux下大小写规则
数据库名与表名是严格区分大小写的;
表的别名是严格区分大小写的;
列名与列的别名在所有的情况下均是忽略大小写的;
变量名也是严格区分大小写的;
如果已经设置了驼峰式的命名如何解决?需要在MySQL的配置文件my.ini中增加 lower_case_table_names = 1即可。
表命名
同一个模块的表尽可能使用相同的前缀,表名称尽可能表达含义。所有日志表均以 log_ 开头
字段命名
表达其实际含义的英文单词或简写。布尔意义的字段以is_作为前缀,后接动词过去分词。
各表之间相同意义的字段应同名。各表之间相同意义的字段,以去掉模块前缀的表名_字段名命名。
外键字段用表名_字段名表示其关联关系。
表的主键一般都约定成为id,自增类型,是别的表的外键均使用xxx_id的方式来表明。
索引命名
非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名
唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名
约束命名
主键约束:pk_表名称。
唯一约束:uk_表名称_字段名。(应用中需要同时有唯一性检查逻辑。)
表设计规范
表引擎取决于实际应用场景;日志及报表类表建议用myisam,与交易,审核,金额相关的表建议用innodb引擎。如无说明,建表时一律采用innodb引擎
默认使用utf8mb4字符集,数据库排序规则使用utf8mb4_general_ci,(由于数据库定义使用了默认,数据表可以不再定义,但为保险起见,建议都写上
为什么字符集不选择utf8,排序规则不使用utf8_general_ci
采用utf8编码的MySQL无法保存占位是4个字节的Emoji表情。为了使后端的项目,全面支持客户端输入的Emoji表情,升级编码为utf8mb4是最佳解决方案。对于JDBC连接串设置了characterEncoding为utf8或者做了上述配置仍旧无法正常插入emoji数据的情况,需要在代码中指定连接的字符集为utf8mb4。
所有表、字段均应用 comment 列属性来描述此表、字段所代表的真正含义,如枚举值则建议将该字段中使用的内容都定义出来。
如无说明,表中的第一个id字段一定是主键且为自动增长,禁止在非事务内作为上下文作为条件进行数据传递。禁止使用varchar类型作为主键语句设计。
如无说明,表必须包含create_time和modify_time字段,即表必须包含记录创建时间和修改时间的字段
如无说明,表必须包含is_del,用来标示数据是否被删除,原则上数据库数据不允许物理删除。
用尽量少的存储空间来存数一个字段的数据
能用int的就不用char或者varchar
能用tinyint的就不用int
使用UNSIGNED存储非负数值。
不建议使用ENUM、SET类型,使用TINYINT来代替
使用短数据类型,比如取值范围为0-80时,使用TINYINT UNSIGNED
存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE
时间字段,除特殊情况一律采用int来记录unix_timestamp
存储年使用YEAR类型。
存储日期使用DATE类型。
存储时间(精确到秒)建议使用TIMESTAMP类型,因为TIMESTAMP使用4字节,DATETIME使用8个字节。
建议使用INT UNSIGNED存储IPV4。
尽可能不使用TEXT、BLOB类型
禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。建议使用其他方式存储(TFS/SFS),MySQL只保存指


1、编写好的SQL先explain再执行,观察SQL是否有使用索引

explain select * from user where id=10086 or name='tanglu'; 

2、操作delete或者update语句加limit。一方面可以降低SQL写错的代价,如果误操作了数据也只是少部分,通过binlog恢复更方便;另一方面还可以避免长事务锁表,影响相关业务无法使用;最后还能防止数据量大时CPU跑高的问题。
delete from euser where age > 30 limit 200

3、建表时所有表和字段都应该有注释信息,方便后期维护
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表'; 

4、SQL变更操作先在测试环境执行,并提前准备好回滚方案


5、创建表的时候,要求统一加上主键、create_time、update_time字段。主键的作用性不用多说,而创建时间和更新时间常用于审计、跟踪记录、ETL任务等


6、如果语句存在where、order by、group by子句,查看多表关联的列是否已加索引,优先考虑组合索引
select * from user where address ='深圳' order by age ;
alter table user add index idx_address_age (address,age); 

7、修改或删除重要数据前先备份,先备份,先备份


8、避免在索引字段上使用计算、NOT(!=、<>)、IN、NOT IN、IS NULL、IT NOT NULL、%模糊查询、数据类型隐式转换等情况,否则会导致索引失效
select * from user where userid =123;  #不加单引号时MySQL会把数据类型做隐式转换,导致索引失效
select * from user where userid ='123';  

9、尽量把所有列定义为NOT NULL

10、索引命名要规范,如主键索引名为pk_字段名(即primary key);唯一索引命名为uk _字段名;普通索引名则为idx_字段名。


11、如果一次性需要修改或更新大量数据需分多次进行。避免产生大事务、主从延迟、CPU跑高等问题


12、对同一个表的多次alter操作合并为一次操作

温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,谢谢合作!
et_highlighter51
版权所有:《Linux运维技术学习站点
文章标题:《【MySQL运维】生产环境下的数据库规范建议
除非注明,文章均为 《Linux运维技术学习站点》 原创
转载请注明本文短网址:http://www.linuxe.cn/post-637.html  [生成短网址]

已有 0/215 人参与

发表评论:

欢迎分享Linux运维技术学习站点

欢迎使用手机扫描访问本站,还可以关注微信哦~