你好,我是程序员Alan,很高兴遇见你。
这篇文章我会详细讲一下设计表结构时我会重点关注的地方,助你少走弯路。
数字类型
这里需要重点关注一下范围,不需要记得非常清楚,但是要有一个大概的印象,对边界问题要敏感。
另外不推荐使用数据库的浮点类型,否则在计算时,由于精度类型问题,会导致最终的计算结果出错,这是因为MySQL 之前的版本中的浮点类型 Float 和 Double,不是高精度。
更重要的是,从 MySQL 8.0.17 版本开始,当创建表用到类型 Float 或 Double 时,会抛出下面的警告:MySQL 提醒用户不该用上述浮点类型,甚至提醒将在之后版本中废弃浮点类型。
在实际的开发中,我们常常会使用整型类型来作为表的主键,即用来唯一标识一行数据。整型结合属性 auto_increment,可以实现自增功能,但在表结构设计时用自增做主键,希望你特别要注意以下两点,若不注意,可能会对业务造成灾难性的打击:
- 用 BIGINT 做主键,而不是 INT;
- 自增值并不持久化,可能会有回溯现象(MySQL 8.0 版本前)。
INT 的范围最大在 42 亿的级别,在真实的互联网业务场景的应用中,一些流水表、日志表,很容易达到最大值。
因此,用自增整型做主键,一律使用 BIGINT,而不是 INT。不要为了节省 4 个字节使用 INT,当达到上限时,再进行表结构的变更,将是巨大的负担与痛苦。
我所在的公司就遇到过线上环境出现INT类型达到上线导致服务异常的问题,当时运维同学的做法是修改数据库表结构(字段类型 INT -> BIGINT),你猜这个简单的操作会造成什么问题?表锁死!表锁死又导致了一系列相关请求全部卡死!举个和项目无关的例子,助你感受一下问题的严重性,想象一下现在是春运期间,检票进站的时候,检票服务出现异常,大量旅客滞留在候车室的场景
字符串类型
MySQL 数据库的字符串类型我最常使用的是 CHAR、VARCHAR。
CHAR 和 VARCHAR 的定义
CHAR(N) 用来保存固定长度的字符,N 的范围是 0 ~ 255,请牢记,N 表示的是字符,而不是字节。VARCHAR(N) 用来保存变长字符,N 的范围为 0 ~ 65536, N 表示字符。
在超出 65536 个字符的情况下,可以考虑使用更大的字符类型 TEXT 或 BLOB,两者最大存储长度为 4G,其区别是 BLOB 没有字符集属性,纯属二进制存储。
MySQL 数据库的 VARCHAR 字符类型,最大能够存储 65536 个字符,所以在 MySQL 数据库下,绝大部分场景使用类型 VARCHAR 就足够了。
日期类型
MySQL 数据库中常见的日期类型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因为业务绝大部分场景都需要将日期精确到秒,所以在表结构设计中,常见使用的日期类型为DATETIME 和 TIMESTAMP。
DATETIME
类型 DATETIME 最终展现的形式为:YYYY-MM-DD HH:MM:SS,固定占用 8 个字节。
从 MySQL 5.6 版本开始,DATETIME 类型支持毫秒,DATETIME(N) 中的 N 表示毫秒的精度。例如,DATETIME(6) 表示可以存储 6 位的毫秒值。同时,一些日期函数也支持精确到毫秒,例如常见的函数 NOW、SYSDATE。
用户可以将 DATETIME 初始化值设置为当前时间,并设置自动更新当前时间的属性。
/
CREATETABLEUser ( idBIGINTNOTNULLAUTO_INCREMENT, created_dateDATETIME(6) NOTNULLDEFAULTCURRENT_TIMESTAMP(6), updated_dateDATETIME(6) NOTNULLDEFAULTCURRENT_TIMESTAMP(6) ONUPDATECURRENT_TIMESTAMP(6), PRIMARYKEY(id) );
TIMESTAMP
除了 DATETIME,日期类型中还有一种 TIMESTAMP 的时间戳类型,其实际存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。在 MySQL 中,由于类型 TIMESTAMP 占用 4 个字节,因此其存储的时间上限只能到‘2038-01-19 03:14:07’。
同类型 DATETIME 一样,从 MySQL 5.6 版本开始,类型 TIMESTAMP 也能支持毫秒。与 DATETIME 不同的是,若带有毫秒时,类型 TIMESTAMP 占用 7 个字节,而 DATETIME 无论是否存储毫秒信息,都占用 8 个字节。
类型 TIMESTAMP 最大的优点是可以带有时区属性,因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区,那么类型 TIMESTAMP 是一种不错的选择。比如新闻类的业务,通常用户想知道这篇新闻发布时对应的自己国家时间,那么 TIMESTAMP 是一种选择。
另外,有些国家会执行夏令时。根据不同的季节,人为地调快或调慢 1 个小时,带有时区属性的 TIMESTAMP 类型本身就能解决这个问题。
参数 time_zone 指定了当前使用的时区,默认为 SYSTEM 使用操作系统时区,用户可以通过该参数指定所需要的时区。
DATETIME vs TIMESTAMP vs INT,怎么选?
在做表结构设计时,对日期字段的存储,开发人员通常会有 3 种选择:DATETIME、TIMESTAMP、INT。
INT 类型就是直接存储 '1970-01-01 00:00:00' 到现在的毫秒数,本质和 TIMESTAMP 一样,因此用 INT 不如直接使用 TIMESTAMP。
当然,有些同学会认为 INT 比 TIMESTAMP 性能更好。但是,由于当前每个 CPU 每秒可执行上亿次的计算,所以无须为这种转换的性能担心。更重要的是,在后期运维和数据分析时,使用 INT 存储日期,是会让 DBA 和数据分析人员发疯的,INT的可运维性太差。
也有的同学会热衷用类型 TIMESTEMP 存储日期,因为类型 TIMESTAMP 占用 4 个字节,比 DATETIME 小一半的存储空间。
但若要将时间精确到毫秒,TIMESTAMP 要 7 个字节,和 DATETIME 8 字节差不太多。另一方面,现在距离 TIMESTAMP 的最大值‘2038-01-19 03:14:07’已经很近,这是需要开发同学好好思考的问题。
总的来说,我建议你使用类型 DATETIME。 对于时区问题,可以由前端或者服务这里做一次转化,不一定非要在数据库中解决。
不要忽视 TIMESTAMP 的性能问题
前面已经提及,TIMESTAMP 的上限值 2038 年很快就会到来,那时业务又将面临一次类似千年虫的问题。另外,TIMESTAMP 还存在潜在的性能问题。
虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多,这并不会带来直接的性能问题。但是如果使用默认的操作系统时区,则每次通过时区计算时间时,要调用操作系统底层系统函数 __tz_convert(),而这个函数需要额外的加锁操作,以确保这时操作系统时区没有修改。所以,当大规模并发访问时,由于热点资源竞争,会产生两个问题。
- 性能不如 DATETIME: DATETIME 不存在时区转化问题。
- 性能抖动: 海量并发时,存在性能抖动问题。
为了优化 TIMESTAMP 的使用,强烈建议你使用显式的时区,而不是操作系统时区。比如在配置文件中显示地设置时区,而不要使用系统时区.
[mysqld]time_zone ="+08:00"
站在巨人的肩膀上
- 姜承尧——MySQL实战宝典
- 菜鸟教程
如果对您有帮助,欢迎关注我的微信公众号和我交流:ProgrammerAlan