Java面试之孔乙己拒止攻略（2）——MYSQL篇

最新推荐文章于 2024-06-05 19:09:50 发布

芝法酱

最新推荐文章于 2024-06-05 19:09:50 发布

阅读量151

点赞数

文章标签： java 面试 mysql

本文链接：https://blog.csdn.net/hataksumo/article/details/129443643

版权

一、前言

其实，我们做的大多数系统都是数据库应用系统，up主的大学专业也是这个。对于这部分知识，确实有掌握的必要。然而，实际上这部分知识并不困难，大部分知识点看一眼就会了。
本篇打算包括以下几个方面：

数据库表的设计
B+树
Mysql的缓存和sql执行过程
Mysql索引
join优化
分库分表

二、3范式与反范式设计

2.1 第一范式

所有数据列都是不可再分的原子值，也就是每个字段都不可以再拆分成更小的单位。
PS：但实际上，有些字段可以很确定的不会用作查询条件，有些时候放个json进去也未尝不可；只不过，那些信息再添加字段时，会变得十分麻烦，有新旧数据结构不同的问题

表样例：

学号	姓名	系名	系主任	课程名	分数
1	李晓明	经济系	凯因斯	高等数学	95
1	李晓明	经济系	凯因斯	大学英语	87
1	李晓明	经济系	凯因斯	普通化学	76
2	张莉莉	经济系	凯因斯	高等数学	72
2	张莉莉	经济系	凯因斯	大学英语	98
2	张莉莉	经济系	凯因斯	计算机基础	88
3	高芳芳	法律系	刘诗韵	高等数学	82
3	高芳芳	法律系	刘诗韵	法律基础	82

2.2 第二范式

在满足第一范式的基础上，非主键列完全依赖于主键列，而不是部分依赖。也就是说，一个表中的所有非主键字段必须完全依赖于主键，而不是只依赖于主键的某个部分。
简单来讲，就是符合第一范式，表中必须有主键，其他字段可由主键确定。二范式只是通过拆表来解决数据冗余。不能解决删除插入异常的问题。

表样例

学号	课程名	分数
1	高等数学	95
1	大学英语	87
1	普通化学	76
2	高等数学	72
2	大学英语	98
2	计算机基础	88
3	高等数学	82
3	法律基础	82

学号	姓名	系名	系主任
1	李晓明	经济系	凯因斯
2	张莉莉	经济系	凯因斯
3	高芳芳	法律系	刘诗韵

2.3 第三范式

在满足第二范式的基础上，所有非主键列之间没有依赖关系。也就是说，一个表中的任何非主键字段都不能依赖于其他非主键字段，而是必须依赖于主键字段。简单来说，就是拆分实体，形成主外键的关联。

表样例

学号	课程名	分数
1	高等数学	95
1	大学英语	87
1	普通化学	76
2	高等数学	72
2	大学英语	98
2	计算机基础	88
3	高等数学	82
3	法律基础	82

学号	姓名	系名
1	李晓明	经济系
2	张莉莉	经济系
3	高芳芳	法律系

系名	系主任
经济系	凯因斯
法律系	刘诗韵

2.4 实体关系与代理主键

数据库中的实体关系通常有3种，1-1,1-n,n-n。
1-1可以用主键做关联；1-n两张表，多的一方设置外键，关联少的一方；n-n得3张表，其中维护一个引用表，持有双方的外键，维护2个表的关系。
在现代数据库中，尽量给实体设置代理主键，而不要以有意义的字段作为主键。

2.5 反范式设计

然而，在实际开发中，完全按照标准的三范式设计数据库，会使得数据库有太多的表，使得关系难以理清，并且在查询时需要大量的表连接，使得程序效率骤降，sql调优变得极其困难。在我的经验中，以下情况可以违反三范式做设计。

2.5.1 不常变动的数据做适当冗余

在有些时候，数据录入到数据库中，很少做改变。比如用户的用户名，项目的code，优惠券的名称，公司的名称等。在何其关联的表中，我们可以把这些信息冗余上，可以把多数连表查询改为单表查询。不过在这些字段更新时，需要记得一起更新关联表，在SpringBoot中可以用发消息的方式进行。

2.5.2 使用级联Id

在级联Id中，有2个常见的例子。一个是鉴权中的部门系统，通常会使用一个级联code的字符串，描述从根到当前节点的全路径，比如root-eng1-dep1。这样在查询某个部门的子孙节点时可以使用like root-eng1-dep1-%。
还有一个常见的情况，就是位置信息。也就是在哪个国家，哪个省，哪个市，哪个区。我们会创建的一个Location表，并且把Id规划好，哪几位表示省，哪几位表示市，哪几位表示区。当要查找在哪个市时，只需要用相关数学运算，就可以直接用Id做查找。

2.5.3 一些明确不需要做查询的复杂字段

有些时候，有些类似备注呀，设备属性呀，收货地址信息之类的字段，很明确的知道不会用作查询，并且有较复杂的层级关系时。这时我们可以把多个属性合成一个json字段。在获取字段时主义好判空操作即可。在SpringBoot中，也支持把这种json字段直接映射成为实体。

三、B树、B+树

3.1 内存中用于查找的数据结构

在内存中，我们的用于数据查找的数据结构，常常用这两种。哈希表和红黑树。
哈希表是把键算出一个哈希值，再映射到数组的某个槽中。当查找时，先计算哈希值，然后直接找到对应的槽，遍历到key值相同的元素时即可找到。这种数据结构的查找算法效率是n(1)。mysql的哈希索引，也只能在内存引擎中使用。
然而，哈希表这种数据结构，无法做排序运算，这时就需要用二叉查找树。二叉查找树保证每个节点只有2个子节点，左节点的值比自身小，右节点的值比自身大。红黑树是一个优化的二叉查找树，通过旋转等方法，使树尽可能平衡，也就让树的层数尽可能小，尽可能两边都有节点。红黑树的查找效率是lg(n)

B树与B+树

然而在数据库中，磁盘的数据读取并不是一个内存的随机访问。硬件通常一次读取一大批连续的数据。而在内存的红黑树中，一个节点才放3个数据，而磁盘一次读取可以读一个逻辑块，通常是4KB，这显然不匹配。
于是B树和B+树算法就产生了。mysql的一个节点会存放16KB的数据，并根据估算来计算每个节点存放的数据行数量。
那么，B树的定义就成了每个节点的子节点引用放在一个数组中，数组元素根据主键值有序排列。每个节点维护其处理的数据范围。
而B+数则是，只有在叶子节点存放数据，以减少回表，减少数据库IO次数。
Mysql的innodb使用的是B+树，之前的MyISAM使用的是B树，主键索引和数据分属于不同的文件。
在这里插入图片描述

四、Mysql的buffer pool

4.1 什么是buffer pool

Buffer Pool是MySQL中InnoDB存储引擎的一种内存缓存机制，用于加速对磁盘上数据的访问。具体来说，Buffer Pool是一块内存区域，其中存储了最近使用的数据页的副本，这些数据页通常是磁盘上的数据文件的一部分。在MySQL需要读取或写入磁盘上的数据时，会首先查找Buffer Pool中是否存在该数据页的副本，如果存在，则直接从内存中读取或写入数据，避免了频繁的磁盘访问。

Buffer Pool中存放的是InnoDB存储引擎的数据页，包括索引页、数据页、undo页等。其中，索引页用于存放B+树索引的节点数据，数据页用于存放表中的行数据，undo页用于存放事务回滚信息等。

Buffer Pool中存储的数据是以数据页为单位进行管理的，每个数据页的大小通常是16KB。在MySQL启动时，Buffer Pool会从操作系统中申请一块连续的内存区域作为缓存区域，并将其划分为多个数据页。当InnoDB需要访问磁盘上的数据时，它会首先查找Buffer Pool中是否存在该数据页，如果存在，则直接读取缓存中的数据；如果不存在，则从磁盘上读取该数据页，并将其存储到Buffer Pool中。

4.2 一个sql语句如何被定为到数据页上

当MySQL接收到一个SQL语句时，它会首先通过解析器将该语句分解为语法树，然后通过优化器对语法树进行分析和优化，生成一个查询计划。在生成查询计划的过程中，MySQL会根据表的统计信息、索引信息以及其他一些因素，选择最优的查询策略和执行计划。

在执行查询计划时，MySQL需要访问表中的数据页，并将其加载到Buffer Pool中。当MySQL需要访问某个数据页时，它会首先检查该数据页是否已经在Buffer Pool中缓存，如果已经缓存，则直接从Buffer Pool中读取数据；如果没有缓存，则需要从磁盘上读取该数据页，并将其存储到Buffer Pool中，然后再从Buffer Pool中读取数据。数据页中有个类似哈希表的数据结构，它使用表空间号+数据页号作为一个key，然后缓冲页对应控制块作为value。

KEY	VALUE
表空间+页号	对应控制块
表空间+页号	对应控制块
。。。	。。。

因此，MySQL并不需要事先知道哪些数据页可能会被使用，而是在查询执行的过程中动态地加载和使用数据页。如果某个数据页在查询过程中没有被使用，那么在Buffer Pool空间不足时，该数据页可能会被淘汰出缓存区，释放空间供其他数据页使用。

4.3 buffer pool的几个特性

LRU算法：
Buffer Pool中的数据页会按照最近使用时间进行排序，最近使用的数据页会被优先保留在内存中，而较长时间未使用的数据页则可能会被淘汰出内存，释放空间。这种淘汰算法被称为LRU（Least Recently Used）算法。
预读机制：
MySQL在读取磁盘上的数据页时，不仅会读取当前需要的数据页，还会预先读取相邻的一些数据页，以便在下次查询时可以直接从Buffer Pool中读取这些数据页，提高查询性能。
脏页管理：
当MySQL修改一个数据页时，该页会被标记为“脏页”，表示该页的内容已经被修改但尚未同步到磁盘上。MySQL会定期将脏页同步到磁盘上，以保证数据的持久性和一致性。如果Buffer Pool中的空间不足，MySQL可能会选择先淘汰一些脏页，以腾出空间存储新的数据页。
Buffer Pool的大小是可以通过配置参数进行调整的，key值为innodb_buffer_pool_size。如果把该值大小设置的比较大，其实就可以把mysql当成一个内存数据库用。在实际开发中，我们可以把一些常访问的数据放到一个数据库服务器中，并把该服务器配置较大内存。