20241228面试鸭特训营第5天

更多特训营笔记详见个人主页【面试鸭特训营】专栏

241228

1. 请详细描述 MySQL 的 B+ 树中查询数据的全过程

前置知识点

• B+树中只有叶子节点会保存数据信息，非叶子节点只保存指针
• B+树默认的叶子节点大小是16K
• 叶子节点内部存储数据信息时，是按组进行分组划分的
• 叶子节点内部会有一个页目录（本质是索引），可以通过二分查找快速定位到对应分组
• 页目录分为多个槽，每个槽指向对应的一个分组内的最大记录
• 叶子节点内部分组规定
  • 第一个分组只能有 1 条记录
  • 中间的分组有 4-8 条记录
  • 最后一个分组有 1-8 条记录

回答重点

• 根据B+树左小右大的特点，快速找到数据所在的叶子节点
• 一个叶子节点就是一页数据信息，大小为16K，可以存储多条数据行
• 叶子节点内部数据按组划分，通过对页目录二分查找快速定位到对应组
• 定位到组后，利用链表进行遍历，得到对应数据行信息

图示举例说明查找过程

定位到叶子节点过程

• 从 root节点 开始找起，根据比较 数据的中存储的索引值 与 目标值 ，确定数据落在哪个区间 ，从而确定分支
• 从上到下执行，最终定位到 目标值 所在的叶子节点

叶子节点内部定位到数据行过程

• 现要寻找 id = 33 的行数据信息
• 页目录的槽里面记录着对应数据行的最大记录信息
• 通过二分进行定位
  • 初始时， low = 0 ， high = 6 ，mid = (0+6)/2 = 3 ，获取当前 mid 对应的数据信息，值为 38
  • 目标值 33 小于当前值 38 ， low = 0 ， high = 3 ，mid = (0+3)/2 = 1 ，获取当前 mid 对应的数据信息，值为 23
  • 目标值 33 大于当前值 23 ， low = 1 ， high = 3 ，mid = (1+3)/2 = 2 ，获取当前 mid 对应的数据信息，值为 30
  • 目标值 33 大于当前值 30 ，30是 槽2 的最大值，且，目标一定在 槽1 ~ 槽3 中，且数据有序排列，可以确定目标值一定位于 槽3 中
  • 槽3 仅能获取第三组的最大值 38 ，无法获取目标值 33
  • 通过 槽2 获取到 槽2 中的最大值 30 ，遍历查找目标值 33

2. MySQL 中 count(*)、count(0)、count(1) 和 count(字段名) 有什么区别？

效率对比

• count(*) = count(0) = count(1) > count(字段名)

count() 的作用

• count(参数) 是一个函数，用于统计 参数中不是null的数量 或 参数满足特殊条件的数量

count(1)

• count(1) 的作用是，统计表中 1 个数量，具体流程如下
  • 遍历这个表，每遍历一行，就判断 1 是不是 null ，如果不是，count 的值就 +1
  • 但 1 != null 恒成立，所以说，每行都会使 count 的值 +1
  • 相当于是在表中的每行数据上都写了个 1 ，然后统计 1 的个数，不会管这个数据是否为 null

count(0)

• 将 count(1) 中的 0 换成 1 ，原理相同，效率和 count(0) 完全相同
• 再举个例子，count(‘DeerLu’) 的效率也和 count(1) 、 count(0) 完全相同

count(*)

• count(*) 会统计表中所有行的数量，包括 null 值
• 官网上面说 count(*) 和 count(1) 没有任何区别

count(字段)

• 统计指定字段中不为 null 的行数

功能方面

• count(*) ：统计表中所有行的数量，无论其是否为空值
• count(1) ：统计表中满足 1 != null 的行数，每一行都会被统计，官网说 count(*) 和 count(1) 没有任何区别
• count(字段名)：统计该字段中不为 null 的行数

效率方面

• count(*) ：较快
• count(1) ：和count(*) ：完全一致
• count(字段名)：较慢
  • 如果当前字段非空，且存在二级索引，则会优化器会采取遍历二级索引的策略
  • 具体优化方案如下
    • 在使用count()函数进行数量统计时，优化器会采取以下优化方案
    • 相同数量的 二级索引记录 比 聚簇索引 占用更少的存储空间，所以 二级索引树 比 聚簇索引树 ，遍历 二级索引树 的 I/O 成本比遍历 聚簇索引树 的 I/O 成本 更小，所以优化器优先选择遍历二级索引
    • 若当前属性没有二级索引，对该索引进行遍历统计
    • 若当前属性只有一个二级索引，优化器会使用二级索引进行遍历统计
    • 若当前属性有多个二级索引，优化器会选择 key_len 最小的二级索引进行遍历统计

搜索引擎对比

• MyISAM支持表级锁，对同一个表的操作是串行的，单独维护每一张表的总行数， count(*) 是直接获取该值，无需遍历，非常快，前提条件是不能使用 where 等过滤条件
• Innodb支持行级锁，对同一个表的操作是并行的，无法维护总行数

3. MySQL 中 varchar 和 char 有什么区别？

char

• 长度固定
• 存储字符串长度与申请长度相等
• 字符串实际长度小于申请长度时，MySQL 会字符串末尾填充空格，使其达到指定长度
• Innodb 会自动忽略 char 类型字符串后面的空格

varchar

• 长度不固定

• 存储字符串长度与字符串实际长度相等

• 存储数据时会额外增加 1 到 2 个字节，用于存储字符串的长度信息（字节长度超过 255 时，使用 2 个字节）

• 计算 varchar 类型支持的最大长度

  • 当一行数据中只有一个 varchar 类型字段时，没有其他字段占用空间，这个 varchar 字段可以达到最大程度
  • B+树默认的叶子节点大小是16K（65535字节），即最大行长度为65535字节
  • 记录当前是否为 null 值，需要 1 bit，由于本行数据中只有一个 varchar 类型字段，只需要一个 bit 记录
  • 字符长度为 1 ~ 255 时，varchar 需要用 1 个字节存储长度信息
  • 字符长度为超过 255 时，varchar 需要用 2 个字节存储长度信息
  • 若允许 null 值 ，varchar 支持的最大长度为 65535 - 2 - 1 = 65532 字节
  • 若不允许 null 值 ，varchar 支持的最大长度为 65535 - 2 = 65533 字节
  • 若使用 UTF - 8 字符集，每个字符最大占用 3 字节（但也可以是 1 字节、 2 字节或 3 字节），能存储的最大字符数大约是 65533 ÷ 3 = 21844

• 最终结论

  • 注意：定义 varchar(n) 时，n代表的是字符的个数，而不是字节数
  • varchar 允许为空时，最大可以存储 65532 个字节，不允许为空时为 65533 字节
  • 在 UTF - 8 前提条件下，varchar 最多可以存储 21844 个字符
  • 除了性别、身份证号、手机号这类长度固定的信息，一般情况下使用 varchar 优于使用 char

varchar 和 char 对比

	char	varchar
存储方式	定长字符串（可能会补空格）	变长字符串（不会补空格）
存储空间	占用所需长度个空间	根据所需长度 + 1~2 个空间
性能分析	可能会浪费一定空间 记录性别等情况可能会更快	更节省空间 记录长度信息所占空间，影响可以忽略不计
适用场景	记录长度固定信息（身份证号、手机号等）	记录长度不确定的信息（项目介绍等）