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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 反范式化可以减少关联查询时,join表的次数

字段类型注意事项

数字类型

1.整型

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

类型 所占空间 范围(signed) 范围(unsigned)
tinyint 1 -128~127 0~255
smallint 2 -32768~32767 0~65535
mediumint 3 -8388608~8388607 0~16777215
int 4 -2147483648~2147483647 0~4294967295
bigint 8 -9223372036854775808~-9223372036854775807 0~18446744073709551615

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


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 SQL 关系型数据库
MySQL进阶突击系列(03) MySQL架构原理solo九魂17环连问 | 给大厂面试官的一封信
本文介绍了MySQL架构原理、存储引擎和索引的相关知识点,涵盖查询和更新SQL的执行过程、MySQL各组件的作用、存储引擎的类型及特性、索引的建立和使用原则,以及二叉树、平衡二叉树和B树的区别。通过这些内容,帮助读者深入了解MySQL的工作机制,提高数据库管理和优化能力。
|
10天前
|
存储 关系型数据库 MySQL
美团面试:MySQL为什么 不用 Docker部署?
45岁老架构师尼恩在读者交流群中分享了关于“MySQL为什么不推荐使用Docker部署”的深入分析。通过系统化的梳理,尼恩帮助读者理解为何大型MySQL数据库通常不使用Docker部署,主要涉及性能、管理复杂度和稳定性等方面的考量。文章详细解释了有状态容器的特点、Docker的资源隔离问题以及磁盘IO性能损耗,并提供了小型MySQL使用Docker的最佳实践。此外,尼恩还介绍了Share Nothing架构的优势及其应用场景,强调了配置管理和数据持久化的挑战。最后,尼恩建议读者参考《尼恩Java面试宝典PDF》以提升技术能力,更好地应对面试中的难题。
|
3月前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
4月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
28天前
|
存储 SQL 关系型数据库
MySQL 面试题
MySQL 的一些基础面试题
|
3月前
|
SQL 缓存 关系型数据库
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴因未能系统梳理MySQL缓存机制而在美团面试中失利。为此,尼恩对MySQL的缓存机制进行了系统化梳理,包括一级缓存(InnoDB缓存)和二级缓存(查询缓存)。同时,他还将这些知识点整理进《尼恩Java面试宝典PDF》V175版本,帮助大家提升技术水平,顺利通过面试。更多技术资料请关注公号【技术自由圈】。
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
|
3月前
|
SQL 算法 关系型数据库
面试:什么是死锁,如何避免或解决死锁;MySQL中的死锁现象,MySQL死锁如何解决
面试:什么是死锁,死锁产生的四个必要条件,如何避免或解决死锁;数据库锁,锁分类,控制事务;MySQL中的死锁现象,MySQL死锁如何解决
|
3月前
|
SQL 关系型数据库 MySQL
美团面试:Mysql如何选择最优 执行计划,为什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴面试美团时遇到了关于MySQL执行计划的面试题:“MySQL如何选择最优执行计划,为什么?”由于缺乏系统化的准备,小伙伴未能给出满意的答案,面试失败。为此,尼恩为大家系统化地梳理了MySQL执行计划的相关知识,帮助大家提升技术水平,展示“技术肌肉”,让面试官“爱到不能自已”。相关内容已收录进《尼恩Java面试宝典PDF》V175版本,供大家参考学习。
|
4月前
|
SQL 关系型数据库 MySQL
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
尼恩,一位40岁的资深架构师,通过其丰富的经验和深厚的技術功底,为众多读者提供了宝贵的面试指导和技术分享。在他的读者交流群中,许多小伙伴获得了来自一线互联网企业的面试机会,并成功应对了诸如事务ACID特性实现、MVCC等相关面试题。尼恩特别整理了这些常见面试题的系统化解答,形成了《MVCC 学习圣经:一次穿透MYSQL MVCC》PDF文档,旨在帮助大家在面试中展示出扎实的技术功底,提高面试成功率。此外,他还编写了《尼恩Java面试宝典》等资料,涵盖了大量面试题和答案,帮助读者全面提升技术面试的表现。这些资料不仅内容详实,而且持续更新,是求职者备战技术面试的宝贵资源。
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
|
4月前
|
SQL 关系型数据库 MySQL
美团面试:mysql 索引失效?怎么解决? (重点知识,建议收藏,读10遍+)
本文详细解析了MySQL索引失效的多种场景及解决方法,包括破坏最左匹配原则、索引覆盖原则、前缀匹配原则、`ORDER BY`排序不当、`OR`关键字使用不当、索引列上有计算或函数、使用`NOT IN`和`NOT EXISTS`不当、列的比对等。通过实例演示和`EXPLAIN`命令分析,帮助读者深入理解索引失效的原因,并提供相应的优化建议。文章还推荐了《尼恩Java面试宝典》等资源,助力面试者提升技术水平,顺利通过面试。