【MySQL】数据库开发规范

背景

之前也做了好多次数据库开发规范培训，也是时候整理一下自己写的开发规范。

开发规范

1.数据库命名规范

数据库对象（库名、表名、列名等）必须使用小写字母，必要时候使用下划线分割
数据库对象禁止使用mysql 关键字和保留关键字
数据库对象中存储相关数据的列名和列类型必须一致，例如 玩家 
id相关列 在各个表中列名和类型 bigint 均须一致
数据库对象名称须见名知意，且长度限制在12个字符内
数据库字段名表名尽量对重要字段带comment标识
索引名带上标识，唯一索引uk_，普通索引idx_

2.数据库设计规范

存储引擎优先考虑Innodb，其具备当前关系型数据的重要特性，例如事务、MVCC、间隙锁等特点
字符集推荐使用UTF8或者UTF8MB4（支持emoji表情） -  字符集介绍
不能使用外键、触发器、存储过程等
常规单表表数据量控制在5kw以下，超过考虑分表
建议默认使用自增的ID作为主键，而且表必须显式声明主键
建议带有两个字段，创建时间和最后修改时间
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP；
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP；
所有字段均定义为NOT NULL
选择合适的数据类型

数据类型如何选？
Tinyint VS Int VS BIgint

Decimal VS Float VS Double

Int VS Timestamp VS Datetime

Char VS VArchar

大字段？

大字段的危害：
1.容易打爆网卡
当select条件中带有大字段的时候，我们假设一个字段是varchar(4096) utf8mb4，那么最极端的情况下，一条记录存的字节是16k，千兆网卡每秒钟理论上限是1000Mb，那么对应到MySQL的QPS = 1000/8*1024/16 = 8000 QPS ，也就是每秒钟只能执行8000个SQL，对于数据库来说是个灾难。
2.页外查找，速度慢
3.单个binlog产生的event非常大，影响同步效率，大并发引起主从延迟

建表示例

CREATE TABLE user_info (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(11) NOT NULL COMMENT ‘用户id’
`username` varchar(45) NOT NULL COMMENT '真实姓名',
`email` varchar(30) NOT NULL COMMENT ‘用户邮箱’,
`nickname` varchar(45) NOT NULL COMMENT '昵称',
`avatar` int(11) NOT NULL COMMENT '头像',
`birthday` date NOT NULL COMMENT '生日',
`sex` tinyint(4) NOT NULL DEFAULT 0 COMMENT '性别,0男 1女',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '上一次修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_id` (`user_id`),
KEY `idx_username`(`username`),
KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息';

索引规范

重要频繁操作的SQL 必须被索引：UPDATE、DELETE、SELECT语句的WHERE 条件列；
尽可能不在MySQL 内使用ORDER BY、GROUP BY、DISTINCT和多表JOIN，若使用，须在从库运行且相关字段必须被索引，不在低基数上建立索引，例如“性别”等；
区分度最大的字段放在前面；
避免多个冗余索引；
不在索引列进行数学运算和函数运算；
不建议使用select *，避免产生代码bug；
单个索引字段不超过5个，单张表索引数量不超过5个；
对于较长的字符串使用前缀索引，前缀长度不超过8个字符；
核心SQL优先考虑覆盖索引，减少回表；
不使用负向查询，例如not in，!= ， not like 等；
复合索引需要注意遵循最左原则，匹配遇到范围查询（‘>’, ‘<‘,）非等值判断则停止使用后面的索引；

SQL规范

避免隐式转换，例如字段A数据类型为int，where A='123' - 不能使用索引；
where条件中同一字段使用’or‘，将 'or' 改写为 'in'；
where条件中不同字段使用’or‘，将 'or' 改写为 'union all'；
充分利用前缀索引；
避免使用大表的JOIN；
拒绝大SQL，需要都拆分成小SQL，禁止直接delete大量数据，大事务转换为小事务去处理
除静态表或小表（100行以内），DML语句必须有where条件，且使用索引查找；
禁止联表更新语句，如update t1,t2 where t1.id=t2.id…；
大表分页优化
eg:
--原sql
select room_id, moderator_uid, uid, source, nickname, create_time from tab order by id asc limit 200 offset 420600;
 
--优化方案
select room_id, moderator_uid, uid, source, nickname, create_time from tab where id >= (select id from mute_tab order by id limit 420600,1) limit 200;
select a.room_id, a.moderator_uid, a.uid, a.source, a.nickname, a.create_time from mute_tab a, (select id from tab order by id asc limit 200 offset 420600) b where a.id=b.id
 
--带where条件优化
select a.room_id, a.moderator_uid, a.uid, a.source, a.nickname, a.create_time from tab a, (select id from tab  where room_id =? order by id asc limit 200 offset 420600) b where a.id=b.id;
select room_id, moderator_uid, uid, source, nickname, create_time from tab where room_id = ? AND id >= (select id from tab order by id where room_id = ? limit 420600,1)  limit 200;