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

小白的大数据之旅

发布于 2024-11-20 19:22:56

3010

前言

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