2025年库索引面试题及答案.docxVIP

2025年库索引面试题及答案

1.请说明B+树与B树在索引实现中的核心差异，为什么主流数据库（如MySQLInnoDB）选择B+树作为索引结构？

B+树与B树的核心差异体现在三个方面：首先，B树的每个节点（包括叶子节点）都存储数据记录指针，而B+树仅叶子节点存储完整数据或主键指针，非叶子节点仅存储索引键；其次，B+树的叶子节点通过双向链表连接，形成有序序列，B树无此结构；最后，B树的每个节点键值数量与子节点数量相等（n个键对应n+1个子节点），B+树非叶子节点键值数量等于子节点数量（n个键对应n个子节点）。

主流数据库选择B+树的原因：①磁盘IO效率更高。B+树非叶子节点不存储数据指针，单个节点可容纳更多索引键，减少树的高度，降低查询时的IO次数。例如，假设每个节点16KB，B树每个节点需存储键值+指针（假设8字节键+8字节指针），可存1024个键；B+树非叶子节点仅存键值（8字节），可存2048个键，树高降低一半。②范围查询更高效。B+树叶子节点的链表结构支持顺序扫描，无需回溯上层节点，而B树范围查询需多次中序遍历不同子树。③稳定性更好。B+树所有查询路径长度相同（从根到叶子），而B树不同键可能位于不同深度节点，查询时间波动大。

2.聚簇索引与非聚簇索引的本质区别是什么？InnoDB中一张表最多可以有多少个聚簇索引？非聚簇索引的叶子节点存储什么？

本质区别在于数据存储与索引的绑定关系：聚簇索引的键值顺序直接决定了表中数据的物理存储顺序，数据与索引存储在同一文件（如InnoDB的.ibd文件）；非聚簇索引（辅助索引）的键值顺序与数据物理顺序无关，索引单独存储，其叶子节点指向聚簇索引的键值（而非数据行的物理地址）。

InnoDB中一张表最多只能有1个聚簇索引，通常由主键自动创建；若表未显式定义主键，InnoDB会尝试使用唯一非空索引替代，若仍无则自动提供6字节的隐藏ROWID作为聚簇索引键。

非聚簇索引的叶子节点存储索引键值+对应的聚簇索引键值。例如，对user表（id为主键，name为普通索引），name索引的叶子节点存储（name,id），通过id回表查询完整数据行。

3.设计联合索引时，如何确定索引列的顺序？举例说明“最左匹配原则”的具体表现形式。

联合索引列顺序需遵循三个原则：①高频查询列前置。将查询中过滤性最强（区分度高）的列放在前面，例如用户表中按（age,gender）查询的频率高于（gender,age），则age应前置。②等值查询列前置。等值条件（=、IN）的列优先于范围查询（、、BETWEEN）的列，因为范围查询后的列无法利用索引。例如，查询条件为WHEREa=1ANDb2ANDc=3，索引（a,b,c）中c无法被利用，而索引（a,c,b）可利用a和c的等值条件。③覆盖查询需求。若查询需返回多列，可将结果列包含在索引中形成覆盖索引，减少回表。例如，查询SELECTa,b,cFROMtWHEREa=1ANDb=2，索引（a,b,c）可直接通过索引获取所有数据。

最左匹配原则指联合索引会匹配从左到右的连续列组合，支持以下匹配形式：①全前缀匹配：如索引（a,b,c）支持WHEREa=1、WHEREa=1ANDb=2、WHEREa=1ANDb=2ANDc=3；②左前缀匹配：支持WHEREa=1（匹配a列）、WHEREa=1ANDb=2（匹配a和b列），但不支持WHEREb=2（跳过a列）或WHEREa=1ANDc=3（跳过b列）；③范围查询截断：若某一列使用范围查询（如b2），则其右侧的列（c）无法使用索引。例如，索引（a,b,c）在WHEREa=1ANDb2时，仅a和b列被使用，c列无法利用索引。

4.哪些常见操作会导致索引失效？请结合具体SQL示例说明。

索引失效的常见场景及示例：

①对索引列使用函数或表达式。例如：

SELECTFROMuserWHEREYEAR(reg_time)=2023;

reg_time的索引无法使用，因为YEAR()函数改变了列的原始值，优化器无法通过索引快速定位。应改写为：

SELECTFROMuserWHEREreg_time=2023-01-01ANDreg_time2024-01-01;

②隐式类型转换。例如：

SELECTFROMuserWHEREphone

若phone字段定义为INT类型（存储为数字），而查询条件使用字符串，MySQL会将phone列转换为字符串进行比较，导致索引失效。应保持类型一致，改为：

SELECTFROMuserWHEREphone=13812