MySQL学习从零开始--第四部分

什么是b树

• B树是一种多路平衡查找树，用于高效地组织和查询大量数据，适合磁盘存储系统，常用于数据库，文件系统的索引结构
• 特点
  • 一棵m阶B树，每个节点最多有m个子节点
  • 每个节点最多有m-1个键（key）
  • 所有叶子节点处于同一层
  • 所有关键字有序
  • 节点中的关键字将其子树划分成区间，使得检索效率更高
  • 插入、删除时保证平衡。

b树的根节点是如何分裂出新节点的，1层的b树，根节点分裂出的新节点是不是叶子结点

• 当根节点存满后插入新数据：根节点被分裂成两个节点。中间的key提升为新根节点。原来的一层树增长为两层。根节点分裂出的新节点不是叶子节点

2层的b树，插入数据时，是插入根节点还是叶子节点

• 两层B树结构为：根节点+叶子节点。
• 插入操作始终发生在叶子节点
• 查找路径是从根开始，一直走到叶子

如果叶子节点的数据满了是会向下拆分，还是向上拆分，或者其他什么方式

• 叶子节点数据满时，会向上拆分
• 拆分叶子节点为两个节点，中间关键字上升到父节点
• 若父节点因此满了，则继续向上拆分，直到根节点，树会增高
• 不存在 “向下拆分”，因叶子节点是最底层，无子节点

b树中在叶子节点中的数据，可能会移动到根节点中吗？为什么

• 可能。但不是数据内容上移，而是用于分裂的“中间键”上移。真正的数据一般还是保留在原处或复制到新节点，根节点只保留索引键

b树中的节点除了索引信息外，会携带数据信息吗？为什么这样设计，有什么优缺点

• 会携带数据信息
• 优点：减少磁盘 I/O 次数。若数据直接存在节点中，查询到索引时可同时获取数据，无需额外访问其他节点，查询效率高（尤其随机查询）
• 缺点：数据分布在多个层级，范围查询效率低

什么是b+树，与b树有什么区别

• B + 树是 B 树的变种，优化了范围查询和数据存储效率

• 特点	B树	B+树
  数据存储	所有节点都可以存数据	只有叶子节点存数据
  范围查询	需要多层遍历	叶子节点链表支持顺序遍历，效率高
  索引	数据和索引混合	索引与数据分离，索引更密集
  查询效率	查询单个值快	范围查询更快、更加稳定

b+ 树的“扇出”是什么意思？扇出数量大有什么好处？

• 扇出指的是每个节点可以容纳的最大子节点数
• 扇出数量大的好处：减少树的高度，降低查询时的磁盘I/O次数。提高索引覆盖范围，单次节点加载可获得更多索引信息

b+树中的索引信息是不是会有冗余，这个冗余有什么作用

• b+树中的索引信息会有冗余
• 作用：支持高效的范围查询，索引更密集，冗余的key值用于导航用，不存储数据

b+树中叶子节点之间的链表有什么作用

• 用于支持高效的范围查询

mysql默认存储引擎使用的b树还是b+树

• MySQL 的默认存储引擎 InnoDB 使用的是 B+树 作为索引结构；

• 主键索引（聚集索引）在叶子节点存储整行数据；

• 非主键索引（辅助索引）叶子节点存储主键值

创建一个表格，用于记录学生成绩，要求记录姓名，语文，数学，英语，小组，插入30条随机数据，每10人为一个小组，共3个小组，没科成绩最高100分，最低0分。

• 

• 生成的学生成绩
  

按照语文分数进行排名，要求输出排名，分数，姓名，小组名

select id,chinese,name,team from student_scores order by chinese desc ;

计算查询表中的总分，并进行排名，要求输入排名，小组，姓名，总分

SELECT RANK() OVER (ORDER BY (chinese + math + english) DESC) 排名,team ,name ,(chinese + math + english) 总分 
FROM student_scores;

计算查询各个小组的总分平均分，并对小组进行排名，输出排名及小组的平均分

select rank() OVER (order by avg(chinese + math + english) DESC) 排名,team 小组,round(avg(chinese + math + english), 2) 平均总分 from student_scores group by team;

查询每个小组中数学最高分的学生姓名和分数

select team,name,math from student_scores s where math=(select max(math) 
from student_scores where team=s.team);

查询所有总分大于所在小组平均分的学生

select * from student_scores a where (chinese+math+english)>(select avg(chinese+math+english) 
from student_scores where team=a.team);

查询每个小组中各科成绩都最高的学生姓名和成绩

SELECT team, name, chinese, math, english
FROM student_scores s
WHERE chinese = (SELECT MAX(chinese) FROM student_scores WHERE team = s.team)
  AND math = (SELECT MAX(math) FROM student_scores WHERE team = s.team)
  AND english = (SELECT MAX(english) FROM student_scores WHERE team = s.team);

按小组对学生进行分组，并计算每个小组中，每科成绩高于 80 分的学生数量

select  team,
  SUM(case when chinese > 80 THEN 1 ELSE 0 END) 语文高分人数,
  SUM(case when math > 80 THEN 1 ELSE 0 END) 数学高分人数,
  SUM(case when english > 80 THEN 1 ELSE 0 END) 英语高分人数
from student_scores group by team;