Sql 简明教程

SQL - Create Index

索引是一种从 SQL 数据库中快速检索数据的高效方法。它是一个数据库对象，引用存储在表中的数据，大幅提升了数据库的查询和应用程序性能。

An index is an effective way to quickly retrieve data from an SQL database. It is a database object that references the data stored in a table, which significantly improves query and application performance of a database.

即使索引有助于加速搜索查询，用户并不能直接看到这些索引的作用。

Even though indexes help accelerate search queries, users are not able to directly see these indexes in action.

What is SQL Index?

SQL index 是一个专用的查询表，用于高效地搜索或查询数据库表以检索所需数据。例如，当我们尝试使用联接从多个表检索数据时，索引会改善查询性能。

An SQL index is a special lookup table that helps in efficiently searching or querying database tables to retrieve required data. For example, when we try to retrieve data from multiple tables using joins, indexes improve the query performance.

随着数据容量的增长，索引被用于优化任何关系数据库管理系统 (RDBMS) 的查询性能。因此，最好将它们用于频繁查询的大型数据库表。

Indexes are used to optimize the query performance of any Relational Database Management System (RDBMS) as data volumes grow. Hence, they are preferred to be used on frequently queried large database tables.

Creating an SQL Index

可以使用 CREATE INDEX 语句根据 SQL 数据库表的某个或多个列创建索引。

An index can be created on one or more columns of a table in an SQL database using the CREATE INDEX statement.

Syntax

以下是 SQL 中 CREATE INDEX 语句的语法 −

Following is the syntax of CREATE INDEX statement in SQL −

CREATE INDEX index_name
ON table_name (column_name1, column_name2,... column_nameN);

在此，

Here,

index_name This specifies the name of the index that you want to create.
table_name This specifies the name of the table on which you want to create the index.
(column_name1, column_name2…column_nameN) are the names of one or more columns on which the index is being created.

Example

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

To create an index on a database table, we first need to create a table. So, in this example, we are creating a table named CUSTOMERS using the following query −

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 表中插入一些值 −

Then, insert some values into the CUSTOMERS table using the following query −

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 的列创建索引 −

Once the table is created, create an index for the column named NAME in the CUSTOMERS table using the following query −

CREATE INDEX index_name ON CUSTOMERS(NAME);

Output

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

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

Verification

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

The following SHOW INDEX query is used to display all the indexes created on an existing table.

SHOW INDEX FROM CUSTOMERS;

在获得的列表中，你可以在索引列表中找到列名称 NAME 及 ID。

In the list obtained, you can find the column name NAME, along with the ID in the list of indexes.

Table

Non_unique

Key_name

Seq_in_index

Column_name

customers

PRIMARY

customers

index_name

NAME

Creating an Index on Multiple Fields

我们还可以使用 CREATE INDEX 语句为表的多个字段（或列）创建索引。为此，你只需传递列的名称（你需要为其创建索引）即可。

We can also create an index on multiple fields (or columns) of a table using the CREATE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on).

Example

不妨让我们考虑之前创建的 CUSTOMERS 表，而不是创建一个新表。这里，我们使用以下查询在列 NAME 和 AGE 上创建索引 −

Instead of creating a new table, let us consider the previously created CUSTOMERS table. Here, we are creating an index on the columns NAME and AGE using the following query −

CREATE INDEX mult_index_data on CUSTOMERS(NAME, AGE);

Output

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

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

Verification

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

Now, let us list all the indexes that are created on the CUSTOMERS table using the following SHOW INDEX query −

SHOW INDEX FROM CUSTOMERS;

正如你所观察到的那样，你可以在索引列表中找到列名称 NAME 和 AGE 及 ID（主键）。

As you observe, you can find the column names NAME, and AGE along with ID (PRIMARY KEY), in the list of indexes.

Table

Non_unique

Key_name

Seq_in_index

Column_name

customers

PRIMARY

customers

index_name

NAME

customers

mult_index_data

NAME

customers

mult_index_data

AGE