【PHP面试题21】MySQL中聚集索引和非聚集索引的应用

文章目录

• 一、前言
• 二、聚集索引
• • 2.1 定义
  • 2.2 应用场景
  • 2.3 举例
• 三、非聚集索引
• • 3.1 定义
  • 3.2 应用场景
  • 3.3 举例
• 四、聚集索引 VS 非聚集索引
• • 总结

---

一、前言

本文已收录于PHP全栈系列专栏：PHP面试专区。-
计划将全覆盖PHP开发领域所有的面试题，对标资深工程师/架构师序列，欢迎大家提前关注锁定。

在MySQL数据库中，索引是用于优化查询语句的一种数据结构，它可以帮助数据库快速地定位到需要查询的数据。MySQL中常见的索引类型包括B树索引、哈希索引和全文索引等，其中B树索引又分为聚集索引和非聚集索引。

二、聚集索引

2.1 定义

聚集索引（Clustered Index）是指将数据存储在物理磁盘上的同时，确定其在磁盘的物理存储顺序。在MySQL中，每个表只能拥有一个聚集索引，而该索引通常是主键索引或唯一索引。

2.2 应用场景

• 查询单条记录：当使用主键查询时，由于聚集索引确定了数据的物理存储顺序，数据库可以直接通过二分查找快速定位到需要查询的数据，从而提高了查询效率。

• 唯一性约束：由于聚集索引是唯一索引的特例，因此它可以用于实现唯一性约束。当插入重复的数据时，数据库会通过聚集索引进行唯一性检查，以避免数据的重复插入。

2.3 举例

假设我们有一张名为user的用户表，包含id、name和age三个字段。其中，id字段是主键，可以使用聚集索引实现如下：

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(20),
  age INT
) ENGINE=InnoDB;

三、非聚集索引

3.1 定义

非聚集索引（Nonclustered Index）是指将数据存储在物理磁盘上的同时，维护一份索引副本，记录所需要查询的列值和对应的行指针。在MySQL中，一个表可以拥有多个非聚集索引。

3.2 应用场景

1. 处理大量的查询：当需要处理大量的查询时，非聚集索引可以提高查询效率。例如，在用户表中查询符合某些条件的所有记录时，非聚集索引可以帮助数据库快速确定需要查询的数据，并返回对应的行指针。
2. 覆盖索引：在某些情况下，如果查询语句所需要的所有数据都可以从非聚集索引中获取，那么该索引就可以作为覆盖索引使用，从而避免了读取数据表的操作，提高了查询效率。

3.3 举例

假设我们要查询用户表中年龄在20岁以下的所有用户信息，可以使用非聚集索引实现如下：

CREATE INDEX age_idx ON user (age);
SELECT * FROM user WHERE age < 20;

四、聚集索引 VS 非聚集索引

在MySQL中，聚集索引和非聚集索引都能提高查询效率。不过，它们也有一些不同点：

1. 存储方式：聚集索引将数据直接存储在磁盘上，而非聚集索引只维护一份索引副本。
2. 查询效率：在处理单条记录的查询时，聚集索引更高效；而在处理大量查询时，非聚集索引更快捷。
3. 数据冗余：由于聚集索引将数据直接存储在磁盘上，因此每个表只能拥有一个聚集索引，同时也会产生大量的冗余数据；而非聚集索引可以维护多个索引副本，避免了数据冗余的问题。

需要注意的是，聚集索引和非聚集索引都有各自的应用场景，在实际应用中需要根据具体情况进行选择。

总结

本文介绍了MySQL中的索引类型包括聚集索引和非聚集索引，它们分别适用于不同的应用场景。聚集索引适用于查询单条记录和唯一性约束，而非聚集索引适用于处理大量查询和覆盖索引。在选择索引类型时，需要根据具体情况进行权衡和选择。