前言

有一小阵子没有更新技术文章了,今天我们继续MySQL系列,今天要说的是MySQL表结构设计。在我的工作经历当中,就踩过很多这方面的坑,在之前的文章《MySQL数据表设计踩过的坑》中,也谈到了一些坑,但总有一种,只是指出了问题,却没有给出解决方案的感觉,今天我们就来讲讲MySQL表如何设计才能避免踩坑。

选择合适的存储引擎

在表结构设计中,首先我们就要选择一个合适的存储引擎,我们在之前的文章《谈谈MySQL存储引擎》就简述了每个存储引擎的区别,在选择存储引擎时,我们应该根据自己的业务场景来选型存储引擎,如:需要OLTP(在线事务处理)的应用,就应该选择InnoDB存储引擎,如果仅仅用作存储数据,允许数据不一致,数据仓库等场景时,可用MyISAM存储引擎,如果你不知道如何选择,那么使用InnoDB存储引擎就对了。

选择合适的字符集

这里之前也说过一次了,主要是为了保存emoji表情,例如: 微信昵称,就有很多带有emoji表情的,这里我们使用utf8mb4字符集,千万不要使用blob类型来存储。

主/外键类型

众所周知,主键的设定是非常重要的,在主键的选择上,应该满足以下几个条件:唯一性 (必要条件)

非空性

有序性

可读性

可扩展性

主键的唯一性和非空性,就不多说了,其中有序性就有不少好处。例如: 查询时,为有序IO,就可提高查询效率,存储的顺序也是有序的,往远了看,分库分表也是有好处的。因此,我建议使8字节无符号的bigint(20)作为主键的数据类型

如下:1

2

3

4create table t_base_user(

id bigint(20) unsigned not null primary key auto_increment;

....

)

这里有几个注意点:主外键的数据类型一定要一致!

每个表中的主键命名保持一致!

(我就吃过这种细节上的亏)。

无符号与有符号的区别:

有符号允许存储负数,无符号只允许从年初正数,无符号最小值为0,最大值根据类型不同而不同。

问题一: 为什么不使用varchar类型作主键

答: varchar类型会对性能有影响,其中varchar也不是有序的,可读性也不好,这里不建议使用。

(之前我就用过UUID作为主键,可读性简直了….当时数据量较小,还感觉不到性能)。

PS: 主键其实是一个比较大的话题,为什么这么说呢,其实上面的方案也是有缺点的,适用于主从架构,对于多主呢?此时的主键是不是就会有重复的情况产生了,这也就是为什么一些大厂有自己的ID生成中间件的原因了,这里不做深入讨论,我们下次单独写一篇文章来说。

外键约束

外键约束用来保证数据完整性的,这一点我很赞同,但是我不建议在数据库表中加外键约束,因为在数据表中添加外键约束,会影响性能,例如: 每一次修改数据时,都要在另外的一张表中执行查询。我的建议是:在应用层,也就是代码层面,来维持外键关系。

选择合适的类型

这里指的是数据列的数据类型,在选择合适的数据类型时,我们应满足以下条件:尽量选择小,简单的数据类型。

保持可读性。

尽量避免Null

我们尽可能选择小的数据类型,这样会有很多好处,比如服务端处理效率,传输等都会快些。这里给出几个常见的数据类型设计:状态类型用 tinyint,例如 性别等。

时间日期使用datetime,timesamp类型,我比较喜欢使用datetime类型,(可读性高些)。

尽量不要使用text和blob数据类型,特别是blob,

设计逻辑删除

这个字段主要用作业务上的逻辑删除,在现在数据为金的时代,数据不建议做物理删除。原因有以下几点:可恢复,物理删除一旦删除,即不可恢复。

逻辑删除数据可用数据分析等。

例如:1deleted tinyint not null default 1;

创建时间&修改时间

这些主要是用作业务上的字段,每个表中,都应该使用,在查询,以及问题查找定定位时有诸多好处,例如: 查询最近三个月的登录用户,这时就可用创建时间来查询等等,

如:1

2

3created_time datetime not null default now();

updated_time datetime not null default now() comment ''

添加合适的索引

索引说过很多次了,这里就不再详细说了,可参考之前的文章《写会MySQL索引》,

这里需要强调的是,在创建时间上,我的建议是,都加上索引。

添加注释

添加注释,这是非常重要的,其中包括表注释,字段注释。主要是为了后期表结构的维护,我相信你对着数据表中那么多字段,如果没有注释的话,你是很难一下子知道是什么意思的,即使你是该表结构的设计者,十天半个月过去后,你还记得吗?

简单实例:1

2

3

4

5

6

7

8

9

10

11create table t_base_user(

id bigint(20) UNSIGNED not null primary key auto_increment comment "主键",

name varchar(50) character set utf8mb4 comment "",

created_time datetime not null default now() comment "创建时间",

updated_time datetime not null default now() comment "修改时间",

deleted tinyint not null default 1 comment "逻辑删除 0正常数据 1删除数据"

)engine=innodb charset=utf8 comment "用户表";

//添加索引

alter table t_base_user idx_created_time(created_time);

以上表结构仅作演示,实际表结构设计时,会更复杂,但思路是一样的。

小结:

下面精简一下:选择合适的存储引擎,一般使用InnoDB。

选择合适的字符集,无emoji使用utf8,有emoji使用utf8mb4。

主键使用bigint(20),主外键类型一致。

选择合适的类型。

添加逻辑删除,创建,修改时间。

添加表,字段注释

添加索引

以上是表结构设计时的一些注意事项,也算是,我在MySQL表结构设计的一点点积累吧,就算是抛砖引玉了,有不同意见的小伙伴,都可在留言区留言,我们旨在设计好一个不给自己挖坑的表结构。