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;
Creating Indexes on Existing Table
要创建一个现有的表索引,我们使用以下 SQL 语句:
To create an index on existing table, we use the following SQL statements −
-
With CREATE INDEX Statement
-
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);
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,...);
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);
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.