150道MySQL高频面试题，学完吊打面试官--关于索引的五道大厂面试题，跳槽面试很重要

前言

本专栏为150道MySQL大厂高频面试题讲解分析，这些面试题都是通过MySQL8.0官方文档和阿里巴巴官方手册还有一些大厂面试官提供的资料。 MySQL应用广泛，在多个开发语言中都处于重要地位，所以最好都要掌握MySQL的精华面试题，这也是面试官最喜欢问的，现在面试官在面试的时候更关心的是某个技术点的深度，所以专栏的内容也会从底层开始讲解，本专栏会一直不断的进行更新，欢迎大家一起交流学习。

gongzhonghao【小白的大数据之旅】

一个b+树中大概能存放多少条索引记录？

一个B+树中大概能存放的索引记录数量取决于多个因素，包括B+树的高度、阶数（即每个节点能包含的最大关键字数或子节点数）、以及每个关键字或数据记录的大小等。

• 真实环境中一个页存放的记录数量是非常大的（默认16KB），假设指针与键值忽略不计（或看做10个字节），数据占 1 kb 的空间：
• 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 16 条记录。
• 如果B+树有2层，最多能存放 1600×16=25600 条记录。
• 如果B+树有3层，最多能存放 1600×1600×16=40960000 条记录。
• 如果存储千万级别的数据，只需要三层就够了

B+树的非叶子节点不存储用户记录，只存储目录记录，相对B树每个节点可以存储更多的记录，树的高度会更矮胖，IO次数也会更少。

索引记录数量的估算

以一个三层、阶数为4的B+树为例，它能够存放的索引记录数量是相当可观的。具体来说，如果每个节点（包括叶子节点和非叶子节点）都能充分利用其存储空间，并且假设数据记录的大小适中，那么一个这样的B+树可能能够存放数十亿条索引记录。这是一个非常粗略的估算，实际情况会根据具体的数据库实现和配置有所不同。

影响因素

• B+树的高度：B+树的高度是影响其存储能力的重要因素。高度较低的B+树意味着从根节点到叶子节点的路径较短，因此访问速度较快。但是，随着高度的增加，B+树能够存储的索引记录数量也会显著增加。
• 阶数（m）：阶数m定义了每个节点最多能包含的关键字数或子节点数。阶数越大，每个节点能包含的关键字就越多，从而能够支持的索引记录数量也就越多。
• 数据记录的大小：数据记录的大小也会影响B+树的存储能力。如果数据记录较大，那么每个节点能存储的关键字数量就会减少，从而限制了B+树的存储能力。相反，如果数据记录较小，那么每个节点能存储的关键字数量就会增加。
• 磁盘存储单元的大小：磁盘存储数据的最小单元（如扇区、块或页）也会影响B+树的存储能力。较大的存储单元意味着每个节点能包含更多的数据，从而提高了B+树的存储能力。

使用B+树存储的索引crud执行效率如何？

创建（Create）

• 效率：高。在B+树中，创建新记录通常只需要在最底层的叶子节点新增一条记录。由于新增记录不会改变树的整体结构，因此创建操作的时间复杂度为O(log n)，其中n是树的高度。
• 原因：B+树的叶子节点存储了所有数据的引用，而非叶子节点仅存储键的信息。这种结构使得在新增记录时，可以顺序地将记录写入叶子节点，无需进行复杂的树结构调整。

读取（Read）

• 效率：高。读取操作通过索引可以快速定位到数据所在的叶子节点，时间复杂度同样为O(log n)。
• 原因：B+树的有序性和平衡性保证了查找路径的短且稳定。此外，由于所有叶子节点通过链表相连，便于进行范围查找，进一步提高了查询效率。

更新（Update）

• 效率：相对较低。更新操作可能涉及数据移动，因为需要找到要更新的记录并将其替换为新的记录。如果更新导致节点分裂或合并，还需要调整树的结构。
• 原因：B+树需要保持平衡性，因此在更新操作时可能需要进行额外的调整工作。此外，如果更新的记录较大，可能还需要考虑节点的存储空间和分裂问题。

删除（Delete）

• 效率：相对较低。删除操作同样可能涉及数据移动和树结构的调整。特别是当删除操作导致节点下溢时，需要合并相邻节点或向父节点借数据来保持平衡。
• 原因：与更新操作类似，B+树在删除时需要保持其有序性和平衡性。这可能导致额外的调整工作，从而影响删除操作的效率。

总结

• 优势：B+树在创建和读取操作上表现出色，时间复杂度低且稳定。其有序性和平衡性使得查找路径短且高效，特别适用于数据库索引等需要频繁查找的场景。
• 劣势：更新和删除操作可能涉及数据移动和树结构的调整，效率相对较低。特别是在数据频繁变更的场景下，B+树的调整工作可能会成为性能瓶颈。

因此，在使用B+树作为索引数据结构时，应根据业务需求选择适当的索引策略。例如，在需要频繁查找而更新和删除操作较少的场景下，B+树是一个很好的选择。而在数据频繁变更的场景下，可能需要考虑其他更适合的索引结构或优化策略。

什么是自适应哈希索引？

自适应哈希索引（Adaptive Hash Index，AHI）是一种用于快速访问数据库中数据的索引结构，它是InnoDB存储引擎中的一种特性。

定义与原理

自适应哈希索引将索引键值映射到哈希表中，以快速查找记录。InnoDB存储引擎会监控对表上索引页的查询，如果发现某个索引页被频繁访问，InnoDB就会为该索引页建立一个哈希索引。这个哈希索引是基于内存构建的，因此能够提供快速的查找速度。

优点

• 快速查询：哈希表允许常数时间（O(1)）的查找操作，因此自适应哈希索引能够快速地定位记录。
• 低存储开销：哈希索引通常比B-tree索引更紧凑，因为它们不需要存储额外的指针和元数据。
• 自动构建：InnoDB存储引擎会自动监控索引页的访问情况，并为频繁访问的索引页构建哈希索引，无需人工干预。

使用场景

自适应哈希索引主要适用于以下场景：

• 等值查询频繁：如果某个列的值经常被用作等值查询的条件，并且查询频率较高，那么InnoDB存储引擎可能会为该列的值构建自适应哈希索引。
• 热点数据访问：对于经常被访问的热点数据，自适应哈希索引能够提供更快的查找速度，从而提高查询性能。
• 内存资源充足：由于自适应哈希索引是基于内存构建的，因此需要足够的内存资源来支持其构建和维护。

限制与注意事项

• 只能用于等值查询：自适应哈希索引只能用于等值比较（如=、<=>、IN等），对于范围查询、模糊查询等不能使用哈希索引。
• 占用内存资源：自适应哈希索引会占用InnoDB缓冲池的内存资源，因此需要根据实际负载情况来决定是否启用。
• 无法人工干预：自适应哈希索引的构建和管理是由InnoDB存储引擎自动完成的，用户无法直接干预其构建过程。
• LIKE运算符和%通配符的查询不会受益：对于使用LIKE运算符和%通配符的查询，自适应哈希索引无法提供加速效果。

监控与配置

• 监控自适应哈希索引：可以通过SHOW ENGINE INNODB STATUS命令来监控自适应哈希索引的状态和性能。
• 配置启用或禁用：可以通过设置innodb_adaptive_hash_index参数来启用或禁用自适应哈希索引。

什么是2-3树 2-3-4树？

多叉树（multiway tree）允许每个节点可以有更多的数据项和更多的子节点。2-3树，2-3-4树就是多叉树，多叉树通过重新组织节点，减少节点数量，增加分叉，减少树的高度，能对二叉树进行优化。

2-3树

下面2-3树就是一颗多叉树

2-3树具有如下特点：

• 2-3树的所有叶子节点都在同一层。
• 有两个子节点的节点叫二节点，二节点要么没有子节点，要么有两个子节点。
• 有三个子节点的节点叫三节点，三节点要么没有子节点，要么有三个子节点。
• 2-3树是由二节点和三节点构成的树。
• 对于三节点的子树的值大小仍然遵守 BST 二叉排序树的规则。

2-3-4树

为什么官方建议使用自增长主键作为索引？（说一下自增主键和字符串类型主键的区别和影响）

官方建议使用自增长主键作为索引，这主要基于自增长主键在数据库性能和可维护性方面的多重优势。

自增长主键作为索引的优势

性能提升：

自增长主键通常是整数类型，这使得它们在数据库中的存储和索引效率非常高。整数类型的比较和排序速度通常更快，从而提高了查询、插入和更新操作的效率。 自增长主键的值按顺序递增，这有助于减小索引的尺寸。小尺寸的索引更容易缓存，从而进一步提高查询性能。

插入效率：

由于自增长主键的值是按顺序递增的，新的记录总是在表的末尾添加，这不会导致数据页的分裂或数据的重排。这种顺序插入的方式有助于提高插入性能。

避免主键冲突：

自增长主键是唯一的，因此不会出现主键冲突的情况，这有助于保持数据的完整性。

减小碎片化：

自增长主键有助于数据在磁盘上的有序存储，从而减小了碎片化，提高了磁盘读取性能。

容易管理：

自增长主键是数据库自动生成的，不需要应用程序手动分配主键值，这减轻了应用程序的负担。

适用于复制和分片：

在复制和分片环境下，自增长主键有助于数据的一致性和均匀分布。

自增主键与字符串类型主键的区别和影响

存储效率：

自增主键通常是整数类型，占用的存储空间较小。而字符串类型主键占用的存储空间相对较大，这会影响索引的存储效率和查询性能。

索引效率：

整数类型的自增主键在索引时效率更高，因为整数比较和排序的速度通常更快。而字符串类型的主键在索引时需要进行字符串比较，这可能会降低索引效率。

插入性能：

自增主键的顺序插入方式有助于提高插入性能。而字符串类型的主键在插入时可能会导致数据页的分裂或数据的重排，从而降低插入性能。

主键冲突：

自增主键是唯一的，不会出现主键冲突的情况。而字符串类型的主键在生成时如果不注意可能会产生重复值，导致主键冲突。

数据完整性：

自增主键的唯一性有助于保持数据的完整性。而字符串类型的主键如果处理不当可能会导致数据不完整或不一致的情况。

官方建议使用自增长主键作为索引主要是基于其在性能、插入效率、避免主键冲突、减小碎片化和易于管理等方面的优势。相比之下，字符串类型主键在存储效率、索引效率、插入性能和数据完整性方面可能表现较差。因此，在数据库表设计中，如果没有特别的需求，通常建议使用自增长主键作为索引。