Mysql 简明教程

MySQL - Create Index

数据库索引提高了数据库表中操作的速度。它们可以在一个或多个列上创建,既为快速进行随机查找提供依据,也为高效排序记录访问提供依据。

A database index improves the speed of operations in a database table. They can be created on one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

实际上,索引是一种特殊的查找表,它持有对实际表中每条记录的一个指针。

Practically, indexes are a special type of lookup tables, that hold a pointer to each record into the actual table.

在两种情况下我们可以在 MySQL 表上创建索引:在创建一个新表时,以及在现有的表上。

We can create indexes on a MySQL table in two scenarios: while creating a new table and on an existing table.

Creating Indexes on New Table

如果我们要在一个新表上定义一个索引,我们使用 CREATE TABLE 语句。

If we want to define an index on a new table, we use the CREATE TABLE statement.

Syntax

以下是创建一个新表索引的语法:

Following is the syntax to create an index on a new table −

CREATE TABLE(
 column1 datatype PRIMARY KEY,
 column2 datatype,
 column3 datatype,
 ...
 INDEX(column_name)
);

Example

在这个例子中,我们创建一个新表 CUSTOMERS ,并使用 CREATE TABLE 查询为其其中的一个列添加了索引:

In this example, we are create a new table CUSTOMERS and adding an index to one of its columns using the following CREATE TABLE query −

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

为了验证索引是否已被定义,我们使用 DESC 语句检查表定义。

To verify whether the index has been defined or not, we check the table definition using the following DESC statement.

DESC CUSTOMERS;

Output

已显示的表结构将包含在 ID 列上的一个 MUL 索引,如下所示:

The table structure displayed will contain a MUL index on the ID column as shown −

Creating Indexes on Existing Table

要创建一个现有的表索引,我们使用以下 SQL 语句:

To create an index on existing table, we use the following SQL statements −

  1. With CREATE INDEX Statement

  2. With ALTER Command

CREATE INDEX Statement

CREATE INDEX 语句的基本语法如下:

The basic syntax of the CREATE INDEX statement is as follows −

CREATE INDEX index_name ON table_name;

在以下示例中,让我们在 CUSTOMERS 表上创建一个索引。我们此处使用 CREATE INDEX 语句:

In the following example, let us create an index on CUSTOMERS table. We are using CREATE INDEX statement here −

CREATE INDEX NAME_INDEX ON CUSTOMERS (Name);

为了检查表上是否创建索引,我们使用 DESC 语句显示表结构,如下所示:

To check if the index is created on the table or not, let us display the table structure using DESC statement as shown below −

DESC CUSTOMERS;

Output

正如我们可以在下表中看到的那样,在 CUSTOMERS 表的 NAME 列上创建一个复合索引。

As we can see in the table below, a composite index is created on the 'NAME' column of CUSTOMERS table.

ALTER…​ ADD Command

以下是 ALTER 语句的基本语法:

Following is the basic syntax of ALTER statement −

ALTER TABLE tbl_name ADD INDEX index_name (column_list);

让我们在以下示例中使用 ALTER TABLE…​ADD INDEX 语句为 CUSTOMERS 表添加索引:

Let us use ALTER TABLE…​ ADD INDEX statement in the following example to add an index to the CUSTOMERS table −

ALTER TABLE CUSTOMERS ADD INDEX AGE_INDEX (AGE);

Output

正如我们可以在下表中看到的那样,在 CUSTOMERS 表的 AGE 列上创建一个复合索引。

As we can see in the table below, another composite index is created on the 'AGE' column of CUSTOMERS table.

Simple and Unique Index

唯一索引是多个同时创建的行中无法创建的索引。以下是创建唯一索引的语法:

A unique index is the one which cannot be created on two rows at once. Following is the syntax to create a unique index −

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

Example

以下示例在表 temp 上创建唯一索引:

Following example creates a unique index on the table temp −

CREATE UNIQUE INDEX UNIQUE_INDEX ON CUSTOMERS (Name);

Composite Indexes

我们还可以在多个列上创建索引,并且它被称为复合索引,创建复合索引的基本语法如下:

We can also create an index on more than one column and it is called a composite index the basic syntax to create a composite index is as follows −

CREATE INDEX index_name
on table_name (column1, column2);

Example

以下查询在上述创建表中的 ID 和 Name 列上创建复合索引:

Following query creates a composite index on the ID and Name columns of the above created table −

CREATE INDEX composite_index on CUSTOMERS (ID, Name);

Creating an Index Using Client Program

除了使用 SQL 查询,我们还可以使用客户端程序在 MySQL 表上创建索引。

In addition to using SQL queries, we can also create an index on a table in a MySQL database using a client program.

Syntax

以下是使用多种编程语言在 MySQL 数据库中创建索引的语法 -

Following are the syntaxes to create an index in a MySQL database using various programming languages −

Example

以下是该操作在各种编程语言中的实现 −

Following are the implementations of this operation in various programming languages −