SQL - 索引

SQL 索引

SQL 索引 是特殊的查找表，用于加速数据检索过程。它们保存指向数据库中存储数据的指针，从而更容易在数据库表中定位所需的数据记录。

SQL 索引的工作方式类似于书籍或期刊的目录。

虽然索引可以加速数据检索查询（SELECT 语句）的性能，但会减慢数据输入查询（UPDATE 和 INSERT 语句）的性能。然而，这些索引不会影响数据本身。

SQL 索引需要在数据库中占用自己的存储空间。尽管如此，用户无法物理查看它们，因为它们只是性能工具。

CREATE INDEX 语句

在 SQL 中，可以使用 CREATE INDEX 语句创建索引。该语句允许您为索引命名、指定表以及要索引的列或列，并指示索引是升序还是降序。

最好在大表中经常用于数据检索查询的列上创建索引。

语法

CREATE INDEX 的基本语法如下：

CREATE INDEX index_name ON table_name;

示例

首先，让我们创建一个名为 CUSTOMERS 的表，如下所示：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18, 2),
   PRIMARY KEY (ID)
);

现在，我们将在 CUSTOMERS 表的 AGE 列上创建索引：

CREATE INDEX idx_age
ON CUSTOMERS(AGE);

输出结果如下所示：

Query OK, 0 rows affected (0.03 sec)

索引类型

可以使用 CREATE INDEX 语句创建多种类型的索引。它们是：

• Unique Index
• Single-Column Index
• Composite Index
• Implicit Index

我们将在本教程中逐一讨论所有索引。

SQL 唯一索引

唯一索引不仅用于性能，还用于数据完整性。唯一索引不允许向表中插入任何重复值。当在数据库表上应用 PRIMARY 和 UNIQUE 约束时，它会自动创建唯一索引，以防止用户向索引表列中插入重复值。

语法

创建 SQL UNIQUE INDEX 的基本语法如下：

CREATE UNIQUE INDEX index_name
on table_name (column_name);

示例

在本示例中，我们在 ADDRESS 列上创建唯一索引：

CREATE UNIQUE INDEX idx_address
ON CUSTOMERS(ADDRESS);

产生的输出如下所示：

Query OK, 0 rows affected (0.01 sec)

SQL 单列索引

Single-Column Index 是在表的一个列上创建的。它允许数据库基于该特定列的值快速定位行，从而在按该列过滤、搜索或排序时提高查询性能。

当您在单列上创建索引时，数据库内部会构建一个数据结构（通常是 B-tree），使其查找速度比扫描整个表快得多。

语法

创建 SQL 单列索引的基本语法如下：

CREATE INDEX index_name
ON table_name (column_name);

示例

在下面的示例中，我们在 SALARY 列上创建索引：

CREATE INDEX idx_salary
ON CUSTOMERS(SALARY);

获得以下输出：

Query OK, 0 rows affected (0.02 sec)

SQL 复合索引

Composite Index（复合索引）是在表中的两个或多个列上创建的索引。当查询在 WHERE 子句、JOIN 条件或 ORDER BY 语句中共同使用多个列时，它非常有用。复合索引有助于数据库根据这些列的值组合快速定位行。

与单列索引不同，复合索引可以同时优化涉及多个列的查询。但是，复合索引中列的顺序非常重要。

Syntax

复合索引的基本语法如下：

CREATE INDEX index_name
on table_name (column1, column2);

Example

在这里，我们在 (AGE, SALARY) 列上创建了一个复合索引：

CREATE INDEX idx_age_salary
ON CUSTOMERS(AGE, SALARY);

我们得到如下输出：

Query OK, 0 rows affected (0.02 sec)

SQL 隐式索引

隐式索引是由数据库服务器在创建对象时自动创建的索引。例如，在 MySQL 数据库中，当在表上创建 primary key 和 unique 约束时，会自动创建索引。

例如，当您在列上定义 PRIMARY KEY 或 UNIQUE 约束时，数据库会自动在该列上创建一个索引，以确保值保持唯一。同样，当您定义 FOREIGN KEY 时，根据数据库系统不同，可能会在引用列上创建索引以加快查找。

SQL DROP INDEX 语句

可以使用 SQL DROP 命令删除索引。删除索引可能会影响数据库的查询性能。因此，只有在绝对必要时才应删除索引。

Syntax

删除索引的基本语法如下：

DROP INDEX index_name;

Example

在以下示例中，我们从 CUSTOMERS 表中删除 idx_salary 索引：

DROP INDEX idx_salary ON CUSTOMERS;

得到如下输出：

Query OK, 0 rows affected (0.01 sec)

何时应避免使用索引？

尽管索引旨在提升数据库性能，但在某些情况下应避免使用它们。

以下指南指出了何时应重新考虑使用索引的情况。

• 不应在小表上使用索引。
• 不应在频繁进行大量批量更新或插入操作的表上使用索引。
• 不应在包含大量 NULL 值的列上使用索引。
• 不应对频繁被操作的列进行索引。