【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操作合并为一次操作