又谈 mysql,面试官问表结构设计要注意啥?
字段类型注意事项
数字类型
1.整型
MySQL 的整型类型所占用的存储空间及取值范围:
2. 注意 unsigned 属性
MySQL 要求 unsigned 数值相减之后依然为 unsigned,否则就会报错(
BIGINT UNSIGNED value is out of range in...
)为了避免这个错误,需要对数据库参数 sql_mode 设置为 NO_UNSIGNED_SUBTRACTION,允许相减的结果为 signed。
3. 浮点类型和高精度型
从 MySQL 8.0.17 版本开始,MySQL 将不建议使用浮点类型 Float 或 Double,高精度 DECIMAL 类型可以使用。
在海量并发的互联网业务中使用,金额字段的我们并不推荐使用 DECIMAL 类型,推荐使用整型类型。
资金以分单位代替元单位存储
类型 DECIMAL 是通过二进制实现的一种编码方式,计算效率远不如整型来的高效。因此,推荐使用 BIG INT 来存储金额相关的字段。
4. 用自增整型做主键,一律使用 BIGINT,而不是 INT
INT 的范围最大在 42 亿的级别,但是对于海量的数据存储,INT 类型的上限很快就会达到。不要为了节省 4 个字节使用 INT,否则在后期再修改表结构代价是巨大的。
当达到 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 来查看:
排序规则以 _ci 结尾,表示不区分大小写(Case Insentive)
排序规则以 _cs 表示大小写敏感
排序规则以 _bin 表示通过存储字符的二进制进行比较
其他
CHECK 约束功能
对于 性别 或 表示状态的字段,推荐使用 CHECK 约束功能。
MySQL 8.0.16 版本开始,数据库原生提供 CHECK 约束功能。
避免了使用 tinyint 类型产生的表达不清(值代表实际意义不明确)、脏数据(可能会存入其他值)的产生。
如下,约束定义列 sex 的取值范围,只能是 M 或者 F。同时,当插入非法数据时,MySQL 会显式地抛出违法约束的提示(
Check constraint 'user_chk_1' is violated.
)。
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 类型;
INT 类型也是存毫秒数,本质和 TIMESTAMP 一样,因此用 INT 不如直接使用 TIMESTAMP。
距离 TIMESTAMP 的可用最大值‘2038-01-19 03:14:07’已经很近。业务上用 TIMESTAMP 存在风险。
使用 TIMESTAMP 必须显式地设置时区,不要使用默认系统时区,否则存在性能问题,推荐在配置文件中设置参数
time_zone = '+08:00'
。
性能问题 : 则每次通过时区计算时间时,要调用操作系统底层系统函数
__tz_convert()
,这个函数需要额外的加锁操作,以确保这时操作系统时区没有修改。所以,当大规模并发访问时,由于热点资源竞争。导致性能不如 DATETIME。
三范式与反三范式
第一范式(1NF)
概念:数据表的每一列都要保持它的原子特性,也就是列不能再被分割。
第二范式(2NF)
概念:属性必须完全依赖于主键。在第一范式的基础上更进一步,解决部分依赖,目标是确保表中的每列都和主键相关。
第三范式(3NF)
概念:所有的非主属性不依赖于其他的非主属性。在第二范式的基础上更进一步,解决传递依赖,目标是确保表中的列都和主键直接相关,而不是间接相关。
反范式化
我们应从业务角度出发,设计出符合范式准则要求的表结构。
反范式化指的是通过增加冗余或重复的数据来换时间增加效率,违反第二第三范式。
反范式化可以减少关联查询时,join 表的次数。
在一些场景下,可以通过 JSON 数据类型进行反范式设计,提升存储效率。
评论