本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告

【MySQL运维】数据库规范建议

发布:TangLu2020-12-24 16:10分类: MySQL 标签: mysql 数据库

一、数据库划分环境

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

· 开发环境:开发人员可拥有DDL\DML权限

· 测试环境:开发人员可拥有DDL\DML权限

· 生产环境:开发人员不允许直接操作数据库,仅对应用开放DML权限,禁止%网段的用户存在


二、命名规范
1、基本命名规范
· 库、表的命名要见名知意,不要超过32个字符,多个词汇以下划线分隔,词汇用英文而非拼音

· 库、表、字段全部采用小写,不要使用驼峰命名

· 避免使用到保留字,如关键字index

· 临时库、表名以tmp为前缀,以日期为后缀;备份库、表必须以bak为前缀,并以日期为后缀


2、索引命名规范
· 非唯一索引:按照“idx_索引名称”命名
· 唯一索引:按照“uniq_索引名称”命名

三、SQL规范
· 数据库字符集推荐设置为默认utf8mb4,数据库排序规则为utf8mb4_general_ci。因为utf8无法保存Emoji表情,如果JDBC连接串设置了characterEncoding为utf8或者做了上述配置仍旧无法正常插入emoji数据的情况,需要在代码中指定连接的字符集为utf8mb4。

· 单张表的索引不建议超过5个,列也不应该有太多
· 所有表、字段均应增加comment注释信息说明表、字段所代表的含义
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='账户表';


· 每张表都需要定义主键,建议将第一个id字段配置为自增主键,禁止使用varchar类型作为主键语句设计
· 表必须包含create_time和update_time字段,即表必须包含创建时间和修改时间的字段,便于审计、跟踪记录、ETL任务
· 用尽量少的存储空间来存数一个字段的数据,能用int就不用char或varchar,能用tinyint的就不用int,比如取值范围为0-80时,使用TINYINT UNSIGNED
· 使用UNSIGNED存储非负数值
· 精确浮点数使用DECIMAL替代FLOAT和DOUBLE
· 存储时间字段时存储年使用YEAR类型、存储日期使用DATE类型、存储时间(精确到秒)使用TIMESTAMP类型,· 建议使用INT UNSIGNED存储IPV4

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

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

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


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

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


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

· 尽量把所有列定义为NOT NULL并且设置默认值,因为NULL列需要额外的存储空间,且 MySQL 内部需要做特殊的处理。MySQL 难以优化 NULL 列的查询,它会使索引、索引统计更加复杂。

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


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

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

已有 0/1176 人参与

发表评论:

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

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