MySQL-索引：聚集索引、覆盖索引、组合索引、前缀索引、唯一索引

---

MySQL-索引：聚集索引、覆盖索引、组合索引、唯一索引

1、聚集索引

在数据库中，聚集索引决定了表数据行的物理存储顺序。在InnoDB存储引擎中，每个表都有一个聚簇索引。

一个表只能有一个聚集索引，并且通常建议选择那些具有唯一性、访问频繁且高度排序的列作为聚集索引。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 假设这是主键
    username VARCHAR(50),
    email VARCHAR(255),
    created_at TIMESTAMP
);

1
2
3
4
5
6
7

在InnoDB存储引擎中，默认情况下id作为聚集索引。这意味着表中的行是按id的升序物理存储的，并且每个叶子节点包含了完整的行数据（包括username, email, created_at）。当你执行如下查询时，可以直接利用聚集索引来快速定位到指定id的数据行

SELECT * FROM users WHERE id = 123;

1
2

2、覆盖索引

覆盖索引是指在查询过程中，只需要通过索引就能获取到所有需要的数据，而不需要再回到表中去查找行记录，这样可以减少磁盘I/O，显著提高查询性能。
如果一个查询语句所涉及的所有字段都出现在某个索引中，那么这个索引就被称作覆盖索引。不仅包括查询列，还包括结果集中所需的其他列（如在SELECT列表中的列）。

对于上述users表，如果我们创建了一个包含所有查询列的复合索引：

CREATE INDEX idx_username_email ON users (username, email);
1

执行查看sql：

SELECT username, email FROM users WHERE username = 'tom';

1
2

这个查询可以完全使用索引 idx_username_email 来完成，因为索引包含了查询所需的所有列，无需回表获取其他列数据，这就是一个覆盖索引的例子。

3、组合索引

同样以上述的idx_username_email为例，它是一个基于username和email两个列创建的组合索引。当执行如下的查询时，由于遵循了最左前缀原则，可以有效利用该索引：

-- 可以利用索引
SELECT * FROM users WHERE username = 'Bob' AND email LIKE '%example.com';

-- 不会利用索引（仅使用了索引的一部分）
SELECT * FROM users WHERE email LIKE '%example.com';

1
2
3
4
5
6

4、唯一索引

在users表中，id列已经是一个主键，自动带有唯一约束。如果还需要确保邮箱地址的唯一性，可以添加一个唯一索引：

ALTER TABLE users ADD UNIQUE INDEX idx_unique_email (email);

1
2