关于MySql建表时数据类型选取的几点建议

·  阅读 62

持续创作,加速成长!这是我参与「掘金日新计划 · 6 月更文挑战」的第3天,点击查看活动详情

1、不推荐使用整数类型的属性 Unsigned

MySql支持的数字类型有 int,smallint ,tinyint ,mediumint ,bigint等,在整型类型中,有signedunsigned属性,表示的是其取值范围,默认是 signed,不建议去刻意修改该属性为 unsigned,因为SQL有时候返回的结构并不是程序想要的

类型占用空间最小值~最大值(signed)最小值~最大值(unsigned)|
tinyint1-128~1270~255
smallint2-32768~327670~65535
mediumint3-8388608~83886070~16777215
int4-2147483648~21474836470~4294967295
bigint8-9223372036854775808~92233720368547758070~18446744073709551615

如果你设置类型属性为 unsigned的话

created table safe(
   id int,
   sale_count int unsigned default null
)

//这时候你这样查询,这时候如果 sale_count - 10000的值小于0,这时候就会报错了,因为
//int类型unsigned属性的范围是0~4294967295,这时候你 -10000小于0,就超出了范围
update safe set sale_count = sale_count - 10000 where id = 1
复制代码

所以不建议刻意去修改该属性

2、自增主键使用BIGINT,不要使用INT

在一些数据量不大,并且可预测的小型系统来说,可以使用INT类型做主键,但是在一些大型系统中,比如流水,日志表等,很快就会达到INT的最大值,如果达到最大值那你只能对表结构进行变更,将是巨大的负担与痛苦,所以不要为了节省4个字节的存储空间而使用INT,推荐使用BIGINT

3、MySql8.0之前主键回塑问题

假设你现在数据库的主键是自增的

//插入三条记录
insert into user (name) values('a');
insert into user (name) values('b');
insert into user (name) values('c');

//这时候你数据库的记录有3条,并且id = 1,2,3

//删除一条记录
delete user where id = 3;

//这时候你再插入一条记录,由于主键是自增的,所以下一条记录的 id = 4
insert into user (name) values('d');

//但是如果你现在数据库重启了,你再插入这条记录,
//你会发现这条记录的id =3,而不是等于4,这就是主键回溯问题
insert into user (name) values('d');
复制代码

会出现在这个问题主要是在低版本的MySql不会对自增主键做持久化处理,可以升级到高版本的MySql,如果不能升级就不要使用自增主键了,可以使用字符串类型的

4、金额类型建议使用INT,不要使用Decimala类型

在海量互联⽹业务的设计标准中,并不推荐⽤ DECIMAL 类型,⽽是更推荐将 DECIMAL 转化为整型类型。也就是说,资⾦类型更推荐使⽤⽤分单位存储,⽽不是⽤元单位存储。如1元在数据库中⽤整型类型 100 存储。

⾦额字段的取值范围如果⽤ DECIMAL 表示的,如何定义⻓度呢?因为类型 DECIMAL 是个变⻓字段,若要定义⾦额字段,则定义为 DECIMAL(8,2) 是远远不够的。这样只能表示存储最⼤值为 999999.99,百万级的资⾦存储。⽤户的⾦额⾄少要存储百亿的字段,⽽统计局的 GDP ⾦额字段则可能达到数⼗万亿级别。⽤类型 DECIMAL 定 义,不好统⼀。

另外重要的是,类型 DECIMAL 是通过⼆进制实现的⼀种编码⽅式,计算效率远不如整型来的⾼效。因此,推荐使⽤ BIGINT 来存储⾦额相关的字段。

字段存储时采⽤分存储,即便这样 BIGINT 也能存储千兆级别的⾦额。这⾥,1兆 = 1万亿。 这样的好处是,所有⾦额相关字段都是定⻓字段,占⽤ 8 个字节,存储⾼效。另⼀点,直接通过整型计算,效率更⾼。 注意,在数据库设计中,我们⾮常强调定⻓存储,因为定⻓存储的性能更好。

4、不要使用Double,float类型

因为Double,Float都不是高精度,也不是SQL标准的类型,所以不推荐使用,否则在计算时会由于精度问题,会导致最终结果出错,后续的高版本MySql也不再支持这二种类型

5、CHAR 和 VARCHAR 虽然分别⽤于存储定⻓和变⻓字符,但对于变⻓字符集(如 GBK、UTF8MB4),其本质是⼀样的,都是变⻓,设计时完全可以⽤ VARCHAR 替代 CHAR

CHAR(N)用来保存固定长度的字符,N的范围是0~255,这里的N表示的是字符,不是字节, VARCHAR(N)用来保存变长字符,N的范围是0~65536,这里N表示的是字符,不是字节,如果超出65536个字符可以使用Text或者是Blob,二者最大长度为4G,区别就是BLOB没有字符集的属性,纯二进制存储

另外对于不同的字符集,比如GBK字符集,一个字符可以存储2个字节,而UTF8MB4字符集一个字符可以存储4个字节,所以从底层存储内核来说CHAR和VARCHAR都是变长存储

6、⽤户性别,运⾏状态等有限值的列,MySQL 8.0.16 版本直接使⽤ CHECK 约束机制,之前的版本可使⽤ ENUM 枚举字符串类型,外加SQL_MODE 的严格模式

在设计表结构的时候,比如性别,该记录状态等都会使用tinyint类型,然后0:男,1:女。或者0:有效,1:已删除,2:已禁用等等,

使用这些会有什么问题呢?比如状态值,要是没有写注释,后面接手该项目的人怎么知道这些状态值对应什么呢??而且除了1,2,3,tinyint还可以存储4,5之类的数据,如果存错了,后续清理数据代价就有点大了

在MySql8.0版本之前我们可以使用ENUM字符串枚举类型,只允许有限的定义值插入,如果将参数SQL_MODE设置为严格模式,插入不符合的数据就会报错

create Table: CREATE TABLE `User` (
 `id` bigint NOT NULL AUTO_INCREMENT,
 `sex` enum('M','F') COLLATE utf8mb4_general_ci DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB
复制代码

在MySql8.0之后,可以使用check约束功能,可以约束用户的输入

create Table: CREATE TABLE `User` (
 `id` bigint NOT NULL AUTO_INCREMENT,
 `sex` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT `user_chk_1` CHECK (((`sex` = _utf8mb4'M') or (`sex` = _utf8mb4'F')))
) ENGINE=InnoDB

复制代码

7、推荐⽇期类型使⽤ DATETIME,⽽不是 TIMESTAMP 和 INT 类型

由于TIMESTAMP只占用4个字节,所以存储的时间的上限只能到 2038-01-19 03:14:07,到时候到了这一天再进行表结构更改就麻烦了。

但是TIMESTAMP最大的优点是带有时区属性,本质是从毫秒转换而来的,如果你的业务对应不同的国家时区,那么类型TIMESTAMP是一种不错的选择,但还是有一个时间上限的问题存在 而使用INT类型就直接存储1970-01-01 00:00:00到现在的毫秒数,本质和TIMESTAMP一样,因此使用INT还不如直接使用TIMESTAMP

8、使⽤ TIMESTAMP一定要显示的设置时区,不要使用默认系统时区

虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多,这并不会带来直接的性能问题。但是如果使⽤默认的操作系统时区,则每次通过时区计算时间时,要调⽤操作系统底层系统函数__tz_convert(),⽽这个函数需要额外的加锁操作,以确保这时操作系统时区没有修改。所以,当⼤规模并发访问时,由于热点资源竞争,会产⽣两个问题。性能不如DATETIME,DATETIME不存在时区转换的问题。海量并发时,存在性能抖动

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改