数据库索引

什么是数据库索引？

数据库表的索引就像实体教科书中的索引。就像查阅书的索引可以让您直接转到相关页面一样，数据库索引允许您检索特定记录，而无需遍历表中的每一行。数据库索引提高了数据检索的速度。通过创建额外的数据库结构来维护索引，数据库索引优化了对可能包含数千甚至数百万行的表进行查询。

为什么需要数据库索引

数据库索引的存在改进了对表的若干操作。以下列表描述了数据库索引的一些重要用途。

1. 从包含大量行的表中读取数据： 当执行针对表中特定行的简单 SELECT 查询时，数据库管理系统可能必须查找每个记录才能找到该行。在非常大的表（例如超过 10 万行的表）中，搜索每个记录都会导致性能不佳。如果索引包含指向特定行的指针，则可以加快速度，从而防止查询搜索整个表。

2. 提供唯一键： 大多数 SQL 数据库引擎都使用表中的主键列创建默认索引。此列期望每行都有一个唯一值。因此，当您创建用于索引的主键列时，您还会获得一个唯一键，该键可以用作 ID，在应用程序的其余部分中标识特定行。例如，包含学生注册号的表的 ID 列可以保存特定学生的唯一注册号的值。

3. 改进记录排序： 列的索引按逻辑顺序排序。因此，如果 SQL 查询在其 ORDER BY 语句中使用索引列，则以升序 (ASC) 或降序 (DESC) 顺序 ORDER 结果的查询会体验到性能的提升。

4. 加快查找速度： 除了 SELECT 查询之外，在 WHERE 子句中使用索引列还可以加快 UPDATE 和 DELETE 查询的查找速度。换句话说，当数据库系统只需要更新或删除特定行时，索引可以防止数据库系统查找表中的所有行。

数据库索引在底层如何工作

默认情况下，SQL 数据库通过扫描表来查找表中的特定行。表扫描也称为顺序扫描，意味着按顺序读取表中的每一行。然后，WHERE 子句测试查询条件的特定列。

当查询没有索引的表时，会发生这种情况。数据库索引消除了顺序读取的需要。相反，索引文件包含指向具有键值的行的指针。索引是一个排序列表，因此数据库系统更容易搜索其记录。

在聚集索引中，数据被分解为树状格式的较小节点，如下所示

在此，索引在中间级别有三个节点。每个节点都包含自己的一组数据叶。每个叶页包含 200 个数据行，为了获取特定范围内的数据，请求会遍历每个级别，仅传递通向数据的节点。例如，要检索键为 805 的行，搜索从根节点开始，并传递到第三个中间索引行，一直到叶页 801–1000。因此，与完整表扫描相比，实际的磁盘 I/O 更少。

聚集索引与非聚集索引

对于大多数 SQL 数据库（如 MySQL 和 SQL Server），索引要么是聚集索引，要么是非聚集索引。

在 MySQL 等 SQL 数据库引擎中，聚集索引也称为主索引。这部分是因为聚集索引使用主键列作为索引。由于每个表每个表只能有一个主索引，因此每个表也只能有一个聚集索引。

非聚集索引是辅助索引。非聚集索引不是使用主键列来索引数据库，而是可以从您打算在 WHERE 子句中使用的任何列创建，以加快查找速度。非聚集索引可以使用多个列，具体取决于数据库设计要求。因此，一个表可以有多个聚集索引。

如何在数据库表中创建索引

创建表索引主要有两种方法。第一种选择是使用主键列。另一种选择是从将用于搜索特定行的任何列创建索引。可以为没有数据的表创建索引；对于我们的示例，我们将重点关注没有数据的表。

如何使用主键创建索引

使用主键创建索引就像向表中添加具有主键约束的列一样简单。

要创建一个包含 ID 列的学生表，该列既充当主键又充当索引，请运行以下 SQL 查询

CREATE TABLE students(id int PRIMARY KEY, full_name varchar(100) NOT NULL, date_of_birth date);

此查询在 MySQL 数据库中创建一个名为 students 的新表。它还为新表的 ID 列生成索引。

要验证此查询是否为 students 表创建索引，请运行以下 SQL 查询

SHOW INDEX FROM students;

以下屏幕截图显示了运行 SHOW INDEX 查询的结果。

将列标记为表的主键足以创建主索引，也称为聚集索引。但是，由于 MySQL 中每个表只能有一个主键列，因此您只能对这种类型的索引使用单列。下一个示例演示如何使用多列创建索引。

如何使用任何列创建索引

您可以使用几乎任何列在表中创建新索引。此外，您可以使用多个列创建多个索引。但是，为过多列创建索引会减慢 UPDATE 等写入操作，因为您需要在写入期间更新索引。

例如，要使用上例中 students 表中的 full_name 列创建新索引，您可以运行以下查询

CREATE INDEX idx_fullname ON students (full_name);

运行查询后，您可以运行 SHOW INDEX 查询以验证是否有新索引。

SHOW INDEX 的结果应显示两个索引，如下面的屏幕截图所示。

要使用第一个或第二个示例中的索引，您可以将列 id 或 full_name 添加到 SELECT、DELETE 或 UPDATE 的 WHERE 子句中。