【架构师之路】《MySQL 教程》数据表设计规范_mysql表设计规范

第一设计范式

第一设计范式要求表中字段都是不可再分的，如果实体entity 中的某个属性有多个值时，必须拆分为不同的属性。

通俗理解即一个字段只存储一项信息，联系方式可能有多种（手机，邮箱，座机），则联系方式不可单独作为一个字段，应该拆分为3个字段，手机，邮箱，座机。

不恰当的结构：

合理的数据库结构：

第二设计范式

第二设计范式要求表中必须存在业务主键，并且全部非主键依赖于业务主键。第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分，为实现区分通常需要我们设计一个主键来实现（这里的主键不包含业务逻辑）。

即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。通俗理解是任意一个字段都只依赖表中的同一个字段。（涉及到表的拆分）。如下图数据表字段中 id 即为业务主键：

第三设计范式

满足第三范式（3NF）必须先满足第二范式（2NF），简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字段。就是说表的信息如果能够被推导出来，就不应该单独的设计一个字段来存放（能尽量外键 join 就用外键 join）。很多时候我们为了满足第三范式往往会把一张表分成多张表。

即满足第二范式前提，如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。 通俗解释就是一张表最多只存两层同类型信息。

如下图所示的商品表不符合第三设计范式：

如图所示，商品分类和分类描述字段冗余，每次添加相同分类商品都会使数据重复，浪费存储空间，可以将表拆分成如下三个表:

遵循数据表设计三范式可以避免字段值的重复存储，提升存储效率，节省存储空间，将各个数据之间分的更细，增加表的冗余性，为后期维护和拓展打下坚实的基础。高性能的 MySQL 数据库第一步就是从数据表合理设计开始的。

反范式化设计

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。

具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于 DML 的比例。
但是反范式化一定要适度，并且在原本已满足三范式的基础上再做调整的。

如下图所示，上面的例子可以稍微反范式化设计一下，可以减少实际数据查询的连表查询操作，提升效率：

小结

介绍了数据库设计三范式，实际工作中，只要遵循数据库设计第三范式要求即可，数据表的良好设计可以为今后更复杂的业务逻辑减少不必要的麻烦，适当反范式化设计可以提升查询效率和工作效率。

其他相关

1、库设计规范

【强制】库的名称规范控制在32个字符以内。
【强制】库的名称格式：业务系统名称_子系统名，同一模块使用的表名使用统一前缀，如qk_pec。
【强制】一般分库名称命名格式是库通配名_编号，编号从1开始递增，比如qk_pec_001，以时间进行分库的名称格式是库通配名_时间。
【强制】创建数据库时必须显式指定字符集，并且字符集只能是utf8mb4。创建数据库SQL举例：create database db1 default character set utf8mb4;。
【强制】禁用procedure、function、trigger、views、event、foreign key。因为他们消耗数据库资源，降低数据库实例可扩展性。推荐都在程序端实现。

2、表设计规范

【强制】表和列的名称必须控制在32个字符以内，表名只能使用字母、数字和下划线，一律小写，禁止拼音英文混合使用。
【强制】相关模块的表名与表名之间尽量体现join的关系，如user表和user_login表。
【强制】创建表时需指定字符集utf8mb4和存储引擎InnoDB，且必须有comment，示例见文末一个规范的建表语句
【强制】表名要求模块名强相关，如体检系统采用pec作为前缀等。
【强制】临时表用于保留中间结果集，名称必须以tmp_开头。备份表用于备份或抓取源表快照，名称必须以bak_开头，日期结尾，如bak_user_20210808。临时表和备份表定期清理。
【强制】对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行。因为alter table会产生表锁，期间会阻塞对于该表的所有写入，对于业务可能会产生极大影响。
【强制】建表时关于主键：(1)强制要求主键为id，类型为 bigint，且为auto_increment，具备业务含义的字段不要设为主键，如plan_id，product_set_id等，可建立unique key索引。因为如果设为主键且主键值为随机插入，则会导致innodb内部page分裂和大量随机I/O，性能下降。

【强制】每张表创建时包含如下字段：

	created_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',updated_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',is_valid tinyint NOT NULL DEFAULT 1 COMMENT '逻辑删除标识位。0-无效，1-有效'
1

【强制】不使用TEXT(TINYTEXT、MEDIUMTEXT、LONGTEXT)，BLOB（TINYBLOB、MEDIUMBLOB 、LONGBLOB）等大字段，因为读取大字段到内存浪费内存空间，影响系统性能。

【建议】反范式设计：把经常需要join查询的字段，在其他表里冗余一份。如user_name属性可在uc_login_account，uc_login_history等表里冗余一份，减少join查询。

3、字段设计规范

【强制】意义相同的字段，如user_id，设计时保证各表中字段类型、长度、排序集均一致，否则join时无法使用索引。
【强制】不使用enum，set。 因为它们浪费空间，且枚举值写死了，变更不方便。推荐使用tinyint。
【强制】业务中选择性很少的状态status、类型type等字段使用tinytint类型节省存储空间。
【强制】存储金钱相关字段，用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。
【强制】文本数据用varchar存储，字符数不要超过2000。
【建议】表中所有字段定义NOT NULL属性，业务可以根据需要定义DEFAULT值，或使用DEFAULT ‘’ 或0来保证空值的传入。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、条件筛选时逻辑冗余、聚合函数计算结果偏差等问题。
【建议】业务中IP地址字段推荐使用unsigned int类型，不推荐用char(15)。因为int只占4字节，而char(15)占用至少15字节。可以用如下函数相互转换：

select inet_aton('192.168.2.12'); 
select inet_ntoa(3232236044);
1
2

4、索引设计规范

【强制】InnoDB表必须主键为id bigint auto_increment primary key,且主键值禁止被更新。
【强制】主键的名称以pk_开头，唯一键以uk_开头，普通索引以idx_开头，一律使用小写格式，以字段的名称或缩写作为后缀，如：idx_user_id
【强制】InnoDB存储引擎表，索引类型必须为BTREE；MEMORY表可以根据需要选择HASH或者BTREE类型索引。
【强制】在建立索引时，多考虑建立联合索引，并把选择率最高的字段放在最前面。
选择率计算：

	select count(distinct user_id) / count(user_id) from user;值越大表示选择率越高。
1

【强制】建表或加索引时，保证表里互相不存在冗余索引。对于MySQL来说，如果表里已经存在联合索引key(a,b)，则索引key(a)为冗余索引，需要删除。
【强制】不要在索引字段进行数学运算和函数运算，会导致索引失效。如：

where unix_timestamp(cr_time)>=1552579200    错误写法
where cr_time > from_unixtime(1552579200)    正确写法
1
2

【建议】合理使用覆盖索引，减少磁盘IO；如：

		select code,name from tab where code=’xxx’;若code不是主键，可建立覆盖索引idx_code_name(code,name)。
1

【建议】单个表上的索引个数不能超过7个，组合索引字段数不超过5个。
【建议】在多表join的SQL里，保证被驱动表的连接列上有索引，这样join执行效率最高。

5、分库分表、分区表

【强制】分区表的分区字段（partition-key）必须有索引，或者是组合索引的首列。
【强制】单个分区表中的分区（包括子分区）个数不能超过1024。
【强制】上线前RD或者DBA必须指定分区表的创建、清理策略。
【强制】访问分区表的SQL必须包含分区键。
【强制】对于分区表执行alter table操作，必须在业务低峰期执行。
【强制】采用分库策略的，库的数量不能超过1024。
【强制】采用分表策略的，表的数量不能超过4096。
【强制】单个分区文件不超过2G，总大小不超过50G。建议总分区数不超过20个。
【强制】单个分表不超过500W行，ibd文件大小不超过2G，这样才能让数据分布式变得性能更佳。
【强制】水平分表尽量用取模方式，日志、报表类数据建议采用日期进行分表。

6、SQL规范

【强制】禁止跨db的join语句。因为这样可以减少模块间耦合，为未来数据库拆分奠定坚实基础。
【强制】SELECT语句必须指定具体字段名称，禁止写成*。因为select *会将不该读的数据也从MySQL里读出来，造成网卡压力。且表字段一旦更新，可能造成系统报错。
【强制】insert语句指定具体字段名称，不要写成insert into t1 values(…)，道理同上。
【强制】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。因为union all不需要去重，节省数据库资源，提高性能。
【强制】对相同表的多次ALTER操作必须合并为一次操作。如：
ALTER TABLE tb1
ADD COLUMN user_id int(0) NULL AFTER is_valid,
ADD COLUMN user_name varchar(50) NULL AFTER user_id,
ADD INDEX idx_user_id(user_id) USING BTREE;
【强制】除字典表或小表（数据量1000内）允许全表扫描，其他所有查询及DML语句必须有where条件，查询数据量不要超过表行数的5%，否则可能不会利用索引。
【强制】生产环境代码中SQL禁止使用hint，如sql_no_cache，force index，ignore key，straight join等。
【强制】编写SQL时注意字段类型，防止出现隐式转换，如字段类型为int，SQL：select id from table where id=‘1’。
【强制】禁止在业务的更新类SQL语句中使用join，比如update t1 join t2…。
【强制】禁止使用 order by rand()。
【建议】order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据。如where a=1 order by b可以利用key(a,b)。
【建议】线上环境，多表join不要超过3个表。
【建议】不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用join来代替子查询。
【建议】in值列表限制在500以内。例如select… where userid in(….500个以内…)，这么做是为了减少底层扫描，减轻数据库压力从而加速查询。
【建议】避免使用not in, not like, <> ，!=等负向查询，以及 like ‘%%’ 的模糊查询。
【建议】分页查询，当limit起点较高时，可先用过滤条件进行过滤。如

	select a,b,c from t1 limit 10000,20;优化为: 

	select a,b,c from t1 where id>10000 limit 20;、
1
2
3

7、实例：一个规范的建表语句

CREATE TABLE `uc_user` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `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) DEFAULT '0' COMMENT '性别',
  `short_introduce` varchar(150) DEFAULT '' COMMENT '一句话介绍自己，最多50个汉字',
  `user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',
  `user_register_ip` int(11) NOT NULL COMMENT '用户注册时的源ip',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` varchar(32) NOT NULL COMMENT '创建人',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `updated_by` varchar(32) NOT NULL COMMENT '更新人',
  `is_valid` tinyint(4) NOT NULL DEFAULT '1' COMMENT '逻辑删除标识位。0-删除，1-正常',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id` (`user_id`),
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='网站用户基本信息';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21