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

一、数据库环境划分

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

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

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

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

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

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

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

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

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

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

· 所有表、字段均应增加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='账户表';

· 每张表都需要定义主键，核心业务表推荐用全局唯一字段（雪花算法、有序UUID）做主键，比如id BIGINT UNSIGNED，禁止使用varchar类型作为主键语句设计

· 自增字段数据类型建议为8字节的bigint，不要采用4字节的int。且自增在 MySQL 8.0 版本前有回溯问题，请考虑是否业务有影响。

· 表必须包含create_time和update_time字段，即表必须包含创建时间和修改时间的字段，便于审计、跟踪记录、ETL任务

· 除了自增字段外，其他字段要用尽量少的存储空间来进行数据存放。能用int就不用char或varchar，能用tinyint的就不用int，比如取值范围为0-80时，使用TINYINT UNSIGNED

· 使用UNSIGNED存储非负数值

· 浮点类型数据使用DECIMAL，而不是精确性不够好的FLOAT和DOUBLE

· 存储时间字段时存储年使用YEAR类型、存储日期使用DATE类型、存储时间（精确到秒）使用TIMESTAMP类型

· 编写好的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列除了需要额外的字节作为判断标志位之外，在使用where .. not in、where ... !=语句或者有运算符参与查询时，遇到NULL值返回永远为空，会使查询结果出错

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

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

· 数据库进行规范化的同时要综合考虑数据库性能问题，范式化虽然可以减少数据冗余，但是会带来大量的关联查询，在一定程度上影响数据库的性能

· 尽量不使用JSON类型，如果有这种需求建议修改为TEXT字段。因为MySQL对于JSON数据会有一个自动的排序操作，影响性能。

· 数据库中不要存放图片、文件等类型的数据，而是存放到专门的存储系统重。一是因为如果查询的时候没有做好相关过滤会导致查询大量不必要的数据，二是备份的时候也会占用过多空间，三是如果需要删除这些数据，无法方便的回收这部分空间