又谈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 数据类型进行反范式设计，提升存储效率。

图片及部分相关技术知识点来源于网络搜索，侵权删！

参考资料：

https://kaiwu.lagou.com/course/courseInfo.htm?courseId=869#/detail/pc?id=7321

https://blog.csdn.net/weixin_29044713/article/details/113558059

https://blog.csdn.net/weixin_43524620/article/details/86482149