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

待我称王封你为后i 2024-03-31 16:05 33阅读 0赞

## 第一设计范式 ##

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

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

**不恰当的结构：**  
![在这里插入图片描述][c81b6a81352643e6bf04d0482e715ec1.png]  
**合理的**数据库结构：  
![在这里插入图片描述][9deeeec6a70b4879b12f0cba8b5a19a5.png]

## 第二设计范式 ##

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

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

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

## 第三设计范式 ##

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

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

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

![在这里插入图片描述][865b0ec157a54d2dbf1b8ed0eac4213e.png]  
如图所示，商品分类和分类描述字段冗余，每次添加相同分类商品都会使数据重复，浪费存储空间，可以将表拆分成如下三个表:  
![在这里插入图片描述][e616cfb3c5f141f4859e6794299e7ec8.png]  
遵循数据表设计三范式可以避免字段值的重复存储，提升存储效率，节省存储空间，将各个数据之间分的更细，增加表的冗余性，为后期维护和拓展打下坚实的基础。高性能的 MySQL 数据库第一步就是从数据表合理设计开始的。

## 反范式化设计 ##

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

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

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

![在这里插入图片描述][7e16737a46c449208752eba86e619761.png]

## 小结 ##

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

## 其他相关 ##

### 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-有效'

【强制】不使用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);

### 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;值越大表示选择率越高。

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

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

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

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

【建议】单个表上的索引个数不能超过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;、

### 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='网站用户基本信息';

[c81b6a81352643e6bf04d0482e715ec1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/01131ba9f21e4be08e203dff27154be1.png
[9deeeec6a70b4879b12f0cba8b5a19a5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/fb2c578b82fd4ff89d66925a2304d83d.png
[de910d04dc114c8e855201b47de90573.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/8e80fe780dff46dc98d7827fee91d544.png
[865b0ec157a54d2dbf1b8ed0eac4213e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/044d8a5fd45549f5912c002e6c3020c4.png
[e616cfb3c5f141f4859e6794299e7ec8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/dfb695599c95486f94d3a711d43551ee.png
[7e16737a46c449208752eba86e619761.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/fda25d82a34f4f4ea8624bbcf8940772.png