自学内容网 自学内容网
自学内容网 > 正文

150道MySQL高频面试题，学完吊打面试官--聚簇索引与非聚簇索引

🕗 发布于 2024-11-06 20:42 mysql  数据库  学习  笔记  面试  

前言

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

聚簇索引与非聚簇索引b+树实现有什么区别？

聚簇索引

特点：

  • 数据物理存储顺序：聚簇索引决定了数据在磁盘上的物理存储顺序。表中的数据行按照聚簇索引的键顺序存储。
  • 主键默认：如果没有显式地指定聚簇索引，MySQL会自动使用主键（Primary Key）作为聚簇索引。如果表中没有主键，MySQL会选择第一个唯一非空索引作为聚簇索引。如果连这样的索引都没有，MySQL会隐式地创建一个内部行ID来作为聚簇索引。
  • 唯一性：聚簇索引必须是唯一的。
  • 查询效率：因为数据行和索引在一起，所以基于聚簇索引的查询效率很高，尤其是范围查询。

优点：

  • 数据访问更快 ，因为索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
  • 聚簇索引对于主键的排序查找和范围查找速度非常快。
  • 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库可以从更少的数据块中提取数据，节省了大量的IO操作 。

缺点：

  • 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
  • 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

限制：

  • 只有InnoDB引擎支持聚簇索引，MyISAM不支持聚簇索引。
  • 由于数据的物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。
  • 如果没有为表定义主键，InnoDB会选择非空的唯一索引列代替。如果没有这样的列，InnoDB会隐式的定义一个主键作为聚簇索引。
  • 为了充分利用聚簇索引的聚簇特性，InnoDB中表的主键应选择有序的id，不建议使用无序的id，比如UUID、MD5、HASH、字符串作为主键，无法保证数据的顺序增长。

示例

假设有一个用户表 users，包含 id（主键）、name 和 age 列：

CREATE TABLE users (  
    id INT PRIMARY KEY,  
    name VARCHAR(100),  
    age INT  
);

默认情况下，id 列是聚簇索引。当你插入数据时，数据行会按照 id 的顺序存储在磁盘上。

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30);  
INSERT INTO users (id, name, age) VALUES (2, 'Bob', 25);  
INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 35);

查询

SELECT * FROM users WHERE id = 2;

这个查询会很快，因为数据行和索引在一起，只需要一次磁盘I/O操作。

非聚簇索引

特点

  • 辅助索引：非聚簇索引是辅助索引，它独立于数据行的物理存储顺序。
  • 键顺序：非聚簇索引存储的是索引键和指向数据行的指针（或行ID）。
  • 多列索引：可以在一个表中创建多个非聚簇索引，这些索引可以是单列或多列的。
  • 性能：因为非聚簇索引需要额外的指针查找数据行，所以查询性能通常比聚簇索引慢。

示例

在 users 表上创建一个非聚簇索引：

CREATE INDEX idx_age ON users(age);

查询

SELECT * FROM users WHERE age = 25;

这个查询首先会在非聚簇索引 idx_age 中查找符合条件的索引键（age = 25），然后通过这些索引键对应的指针找到数据行。这通常需要两次磁盘I/O操作：一次是查找索引，一次是查找数据行。

（二级索引、辅助索引）

聚簇索引，只能在搜索条件是主键值时才发挥作用，因为B+树中的数据都是按照主键进行排序的，如果我们想以别的列作为搜索条件，那么需要创建非聚簇索引。

例如，以c2列作为搜索条件，那么需要使用c2列创建一棵B+树，如下所示：
在这里插入图片描述这个B+树与聚簇索引有几处不同：

  • 页内的记录是按照从c2列的大小顺序排成一个单向链表 。

  • 页和页之间也是根据页中记录的c2列的大小顺序排成一个双向链表 。

  • 非叶子节点存储的是记录的c2列+页号。

  • 叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。

一张表可以有多个非聚簇索引：

在这里插入图片描述

区别总结

数据物理存储：

  • 聚簇索引决定了数据的物理存储顺序。
  • 非聚簇索引存储的是索引键和指向数据行的指针。

索引结构：

  • 聚簇索引的B+树叶子节点存储的是完整的数据行。
  • 非聚簇索引的B+树叶子节点存储的是指向数据行的指针（或行ID）。

查询性能：

  • 聚簇索引的查询效率通常更高，尤其是范围查询。
  • 非聚簇索引需要额外的指针查找，性能相对较低。

数量：

  • 每个表只能有一个聚簇索引。
  • 每个表可以有多个非聚簇索引。

B+树中聚簇索引的查找（匹配）逻辑

查找逻辑

  • 从根节点开始遍历：当需要查找某个主键值时，查询操作从B+树的根节点开始。在根节点中，将目标主键值与节点中的主键值进行比较，以确定下一步的遍历方向（向左或向右）。
  • 逐层向下遍历：根据比较结果，查询操作继续向下遍历至相应的子节点。在每一层中，都重复进行主键值的比较和遍历方向的确定，直至到达叶子节点。
  • 在叶子节点中查找：当查询操作到达叶子节点时，叶子节点中存储的是实际的数据记录。此时，需要按照主键值的顺序在叶子节点中进一步查找匹配的数据记录。如果找到匹配的主键值，则可以直接读出该数据记录。

注意事项

  • 唯一性：聚簇索引的索引列（即主键）通常是唯一的，以保证数据记录的唯一性和索引的有效性。
  • 存储引擎支持：不同的数据库存储引擎对聚簇索引的支持程度可能不同。例如，在MySQL中，InnoDB存储引擎支持聚簇索引，而MyISAM存储引擎则不支持。

在这里插入图片描述

B+树中非聚簇索引的查找（匹配）逻辑

查找逻辑

从根节点开始查找：

当需要查找某个关键字时，查询操作从B+树的根节点开始。在根节点中，将目标关键字与节点中的关键字进行比较，以确定下一步的遍历方向（向左或向右）。

逐层向下遍历：

根据比较结果，查询操作继续向下遍历至相应的子节点。在每一层中，都重复进行关键字的比较和遍历方向的确定，直至到达叶子节点。

在叶子节点中查找匹配项：

当查询操作到达叶子节点时，叶子节点中存储的是数据的地址或主键值。此时，需要按照关键字的顺序在叶子节点中进一步查找匹配的数据地址或主键值。

根据地址或主键值访问数据：

  • 如果在叶子节点中找到了匹配的数据地址，则直接根据该地址访问数据文件，以获取完整的数据记录。
  • 如果在叶子节点中找到了匹配的主键值，则需要根据该主键值去访问主键索引文件（即聚簇索引），以获取完整的数据记录。这个过程通常被称为“回表查询”。

非聚簇索引的查找效率

非聚簇索引的查找效率受到多个因素的影响，包括B+树的高度、节点中的关键字数量、磁盘I/O次数等。由于非聚簇索引需要额外的指针或地址来访问实际的数据文件，因此其查找效率通常比聚簇索引低。然而，在需要频繁访问非主键列的情况下，非聚簇索引仍然可以显著提高查询效率。

举例

**例如：**根据c2列的值查找c2=4的记录，查找过程如下：

  1. 根据根页面44定位到页42（因为2 ≤ 4 < 9）
  2. 由于c2列没有唯一性约束，所以c2=4的记录可能分布在多个数据页中，又因为 2 ≤ 4 ≤ 4，所以确定实际存储用户记录的页在页34和页35中。
  3. 在页34和35中定位到具体的记录。
  4. 但是这个B+树的叶子节点只存储了c2和c1（主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。
  5. like 张%

在这里插入图片描述

在这里插入图片描述


原文地址：https://blog.csdn.net/qq_51431069/article/details/143574664

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024