SQL - CREATE INDEX 语句

什么是 SQL Index？

SQL 中的index是一种数据库对象，它可以提高表上数据检索的速度。它的工作方式类似于书中的索引，允许数据库快速定位所需信息，而无需扫描整个表。

当处理大型数据集以及涉及搜索、过滤或排序的查询时，索引非常有用。

SQL CREATE INDEX 语句

SQL 中的CREATE INDEX语句用于在表的一个或多个列上创建索引。索引允许数据库比扫描整个表更快地找到和访问行，从而提高SELECT查询和连接操作的性能。

索引在大表且频繁查询的场景下特别有用。然而，它们也会占用存储空间，并且在底层数据发生变化时必须更新索引，这可能会减慢INSERT、UPDATE和DELETE操作的速度。

语法

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

CREATE INDEX index_name
ON table_name (column_name);

其中，

• index_name 指定要创建的索引名称。
• table_name 指定要为其创建索引的表名称。
• (column_name1, column_name2...column_nameN) 是创建索引的一个或多个列的名称。

示例

要在数据库表上创建索引，首先需要创建一个表。因此，在此示例中，我们使用以下查询创建一个名为CUSTOMERS的表：

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 4),
   PRIMARY KEY(ID));
);

然后，使用以下查询向 CUSTOMERS 表插入一些值：

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(2, 'Khilan', '25', 'Delhi', 1500),
(3, 'Kaushik', '23', 'Kota', 2000),
(4, 'Chaitali', '25', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(6, 'Komal', '22', 'Hyderabad', 9000),
(7, 'Muffy', '24', 'Indore', 5500);

表创建完成后，使用以下查询为 CUSTOMERS 表中的名为NAME的列创建索引：

CREATE INDEX index_name ON CUSTOMERS(NAME);

执行上述查询后，得到的输出如下：

Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

验证

以下 SHOW INDEX 查询用于显示现有表上创建的所有索引。

SHOW INDEX FROM CUSTOMERS;

在得到的结果列表中，您可以找到列名称 NAME，以及 ID 在索引列表中。

CREATE UNIQUE INDEX

UNIQUE INDEX是一种特殊的索引类型，它确保索引列（或列组）中的所有值都是唯一的。它可以防止重复条目，类似于UNIQUE约束的工作方式。

如果您尝试向具有唯一索引的列插入重复值，数据库将返回错误。

语法

CREATE 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.02 sec)

在多个字段上创建 SQL 索引

SQL 允许我们在多个列上创建索引。此类索引被称为 composite index 或 multi-column index。当查询涉及多个列的条件时，它非常有用。

语法

在多个字段上创建索引的语法如下：

CREATE INDEX index_name
ON table_name (column1, column2, ...);

示例

假设我们经常使用 AGE 和 NAME 搜索 CUSTOMERS 表。我们可以按以下方式创建 composite index：

CREATE INDEX idx_age_name
ON CUSTOMERS (AGE, NAME);

这允许数据库引擎基于 AGE 和 NAME 值的组合快速定位行。以下是获得的结果：

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

验证

现在，让我们使用以下 SHOW INDEX 查询列出在 CUSTOMERS 表上创建的所有索引：

SHOW INDEX FROM CUSTOMERS;

如您所见，您可以在索引列表中找到列名 NAME 和 AGE，以及 ID (PRIMARY KEY)。

何时应该创建索引？

索引应该谨慎创建，因为它们会占用额外的存储空间，并可能减慢 INSERT、UPDATE 和 DELETE 操作。它们最适合在以下情况下使用：

• 您经常使用 WHERE 条件查询大表。
• 您经常在表之间执行 JOIN 操作。
• 您运行涉及 ORDER BY 或 GROUP BY 子句的查询。
• 您需要使用 UNIQUE INDEX 在列上强制唯一性。

SQL 中的 DROP INDEX 语句

DROP INDEX 语句用于从表中删除现有索引。一旦索引被删除，数据库将不再使用它来加速查询，所有查询将回退到普通的表扫描。

语法

DROP INDEX 语句的语法在不同 SQL 数据库之间略有不同。以下是在 MySQL 数据库中删除索引的基本语法：

DROP INDEX index_name ON table_name;

以下是在 SQL Server/Oracle 中删除索引的基本语法：

DROP INDEX index_name;

示例：在 MySQL 中删除索引

以下是在 MySQL 数据库中从 CUSTOMERS 表删除索引 idx_name 的示例：

DROP INDEX idx_name ON CUSTOMERS;

我们得到以下输出：

Query OK, 0 rows affected (0.01 sec)

示例：在 SQL Server 中删除索引

以下示例在 SQL Server 中从 CUSTOMERS 表删除索引 idx_name：

DROP INDEX idx_name ON CUSTOMERS;

输出将确认语句成功执行：

Command(s) completed successfully.

关于 SQL CREATE INDEX 语句的重要要点

以下是您应该了解的关于 SQL CREATE INDEX 语句的一些重要要点：

• 索引主要用于通过减少数据库需要扫描的数据量来提高 SELECT 查询和 join 操作的速度。
• 索引不会更改表中的实际数据，而是创建一个单独的数据结构，使查找更快。
• 创建过多索引会减慢 INSERT、UPDATE 和 DELETE 操作，因为每当数据更改时，索引也需要更新。
• 索引会占用数据库中的额外存储空间，因此仅在需要时才创建。
• PRIMARY KEY 或 UNIQUE constraint 会自动在指定列上创建索引。
• 索引可以创建在单个列上或多个列上（composite indexes），以优化按多个字段过滤或排序的查询。
• 在经常用于 WHERE 子句、JOIN 条件或 ORDER BY 操作的列上创建索引是个好主意。