写点什么

又谈 mysql,面试官问表结构设计要注意啥?

作者:微客鸟窝
  • 2021 年 11 月 18 日
  • 本文字数:2690 字

    阅读完需:约 9 分钟

又谈mysql,面试官问表结构设计要注意啥?

字段类型注意事项

数字类型

1.整型

MySQL 的整型类型所占用的存储空间及取值范围:


2. 注意 unsigned 属性

  • MySQL 要求 unsigned 数值相减之后依然为 unsigned,否则就会报错(BIGINT UNSIGNED value is out of range in...)

  • 为了避免这个错误,需要对数据库参数 sql_mode 设置为 NO_UNSIGNED_SUBTRACTION,允许相减的结果为 signed。


SET sql_mode='NO_UNSIGNED_SUBTRACTION';
复制代码

3. 浮点类型和高精度型

  1. 从 MySQL 8.0.17 版本开始,MySQL 将不建议使用浮点类型 Float 或 Double,高精度 DECIMAL 类型可以使用。

  2. 在海量并发的互联网业务中使用,金额字段的我们并不推荐使用 DECIMAL 类型,推荐使用整型类型。


  • 资金以分单位代替元单位存储

  • 类型 DECIMAL 是通过二进制实现的一种编码方式,计算效率远不如整型来的高效。因此,推荐使用 BIG INT 来存储金额相关的字段。

4. 用自增整型做主键,一律使用 BIGINT,而不是 INT

  1. INT 的范围最大在 42 亿的级别,但是对于海量的数据存储,INT 类型的上限很快就会达到。不要为了节省 4 个字节使用 INT,否则在后期再修改表结构代价是巨大的。

  2. 当达到 INT 上限后,再次进行自增插入时,会报重复错误。

字符串类型

char 和 varchar

  • CHAR(N) 用来保存固定长度的字符,N 的范围是 0 ~ 255,注意,N 表示的是字符,不是字节

  • VARCHAR(N) 用来保存变长字符,N 的范围为 0 ~ 65536, N 表示字符。

  • 超出 65536 个字符时,可以使用更大的字符类型 TEXT 或 BLOB,两者最大存储长度为 4G,其区别是 BLOB 没有字符集属性,纯属二进制存储。

字符集

  • 常见的字符集有 GBK、UTF8,一般我们会把默认字符集设置为 UTF8。但是某些 emoji 表情字符无法在 UTF8 字符集下存储,所以推荐把 MySQL 的默认字符集设置为 UTF8MB4

  • 修改列字符集的命令应该使用


ALTER TABLE ... CONVERT TO CHARSET ...


才能将已经存在的列的字符集进行修改。

排序规则

排序规则(Collation)是比较和排序字符串的一种规则,每个字符集都会有默认的排序规则,可以使用命令 SHOW CHARSET 来查看:


mysql> SHOW CHARSET LIKE 'utf8%';+---------+---------------+--------------------+--------+| Charset | Description   | Default collation  | Maxlen |+---------+---------------+--------------------+--------+| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 || utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci |      4 |+---------+---------------+--------------------+--------+

mysql> SHOW COLLATION LIKE 'utf8mb4%';+----------------------------+---------+-----+---------+----------+---------+---------------+| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |+----------------------------+---------+-----+---------+----------+---------+---------------+| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD || utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD || utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD || utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD || utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |......
复制代码


  • 排序规则以 _ci 结尾,表示不区分大小写(Case Insentive)

  • 排序规则以 _cs 表示大小写敏感

  • 排序规则以 _bin 表示通过存储字符的二进制进行比较

其他

CHECK 约束功能

对于 性别 或 表示状态的字段,推荐使用 CHECK 约束功能。


  • MySQL 8.0.16 版本开始,数据库原生提供 CHECK 约束功能。

  • 避免了使用 tinyint 类型产生的表达不清(值代表实际意义不明确)、脏数据(可能会存入其他值)的产生。

  • 如下,约束定义列 sex 的取值范围,只能是 M 或者 F。同时,当插入非法数据时,MySQL 会显式地抛出违法约束的提示(Check constraint 'user_chk_1' is violated.)。


CREATE TABLE User (    id BIGINT NOT NULL AUTO_INCREMENT,    name VARCHAR(255) NOT NULL,    sex CHAR(1) NOT NULL,    password VARCHAR(1024) NOT NULL,    CHECK (sex = 'M' OR sex = 'F'),    PRIMARY KEY(id));
复制代码

JSON 类型

从 MySQL 5.7 版本开始支持JSON 类型,无须预定义字段,很方便的对产品进行描述。


  • JSON 类型比较适合存储一些修改较少、相对静态的数据。

  • MySQL 8.0.17 版本开始支持 Multi-Valued Indexes,用于在 JSON 数组上创建索引,通过函数 member of、json_contains、json_overlaps 可以快速检索索引数据。

  • 存储的 JSON 内容,上限是 1G。

日期类型

DATETIME

从 MySQL 5.6 版本开始,DATETIME 类型支持毫秒,DATETIME(N) 中的 N 表示毫秒的精度。

TIMESTAMP

  • TIMESTAMP 时间戳类型,存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。

  • MySQL 中,由于类型 TIMESTAMP 占用 4 个字节,因此其存储的时间上限只能到‘2038-01-19 03:14:07’。

  • 若带有毫秒时,类型 TIMESTAMP 占用 7 个字节,而 DATETIME 无论是否存储毫秒信息,都占用 8 个字节。

选择

推荐日期类型使用 DATETIME,而不是 TIMESTAMP 和 INT 类型;


  1. INT 类型也是存毫秒数,本质和 TIMESTAMP 一样,因此用 INT 不如直接使用 TIMESTAMP。

  2. 距离 TIMESTAMP 的可用最大值‘2038-01-19 03:14:07’已经很近。业务上用 TIMESTAMP 存在风险。

  3. 使用 TIMESTAMP 必须显式地设置时区,不要使用默认系统时区,否则存在性能问题,推荐在配置文件中设置参数 time_zone = '+08:00'


  • 性能问题 : 则每次通过时区计算时间时,要调用操作系统底层系统函数 __tz_convert(),这个函数需要额外的加锁操作,以确保这时操作系统时区没有修改。所以,当大规模并发访问时,由于热点资源竞争。导致性能不如 DATETIME。

三范式与反三范式

第一范式(1NF)

概念:数据表的每一列都要保持它的原子特性,也就是列不能再被分割。

第二范式(2NF)

概念:属性必须完全依赖于主键。在第一范式的基础上更进一步,解决部分依赖,目标是确保表中的每列都和主键相关。

第三范式(3NF)

概念:所有的非主属性不依赖于其他的非主属性。在第二范式的基础上更进一步,解决传递依赖,目标是确保表中的列都和主键直接相关,而不是间接相关。

反范式化

我们应从业务角度出发,设计出符合范式准则要求的表结构。


  • 反范式化指的是通过增加冗余或重复的数据来换时间增加效率,违反第二第三范式。

  • 反范式化可以减少关联查询时,join 表的次数。

  • 在一些场景下,可以通过 JSON 数据类型进行反范式设计,提升存储效率。

用户头像

微客鸟窝

关注

还未添加个人签名 2019.11.01 加入

公众号《微客鸟窝》笔者,目前从事web后端开发,涉及语言PHP、golang。获得美国《时代周刊》2006年度风云人物!

评论

发布
暂无评论
又谈mysql,面试官问表结构设计要注意啥?