1. 首页
  2. 博客
  3. MySQL Index 索引

MySQL Index 索引

本教程说明如何在MySQL中创建，删除和重命名索引。

MySQL中的索引是什么？

索引是一种性能调优方法，用于更快地检索记录。索引为被索引列中出现的每个值创建一个条目。

创建一个索引

有两种创建索引的方法。

  1. 在使用CREATE TABLE语句创建表时创建索引
  2. 在创建表后使用CREATE INDEX语句创建索引

语法

在MySQL中使用CREATE TABLE语句创建索引的语法为：

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...
column_n datatype [ NULL | NOT NULL ],
INDEX index_name [ USING BTREE | HASH ]
(index_col1 [(length)] [ASC | DESC],
index_col2 [(length)] [ASC | DESC],
...
index_col_n [(length)] [ASC | DESC])
);

在MySQL中使用CREATE INDEX语句创建索引的语法为：

1
2
3
4
5
6
7
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[ USING BTREE | HASH ]
ON table_name
(index_col1 [(length)] [ASC | DESC],
index_col2 [(length)] [ASC | DESC],
...
index_col_n [(length)] [ASC | DESC]);
参数 选项
UNIQUE 可选的。UNIQUE修饰符表示索引列中的值的组合必须是唯一的。
FULLTEXT 可选的。FULLTEXT对整个列进行索引，并且不允许加前缀。InnoDB和MyISAM表支持此选项。
SPATIAL 可选的。SPATIAL修饰符对整个列进行索引，并且不允许索引列中包含NULL值。InnoDB(从MySQL 5.7开始)和MyISAM表支持这个选项。
index_name 索引的名称。
table_name 创建索引的表的名称。
index_col1，index_col2，... index_col_n 索引中使用的列。
length 可选的。如果指定，则仅索引列的前缀，而不索引整个列。对于非二进制字符串列，此值是要索引的列的给定字符数。对于二进制字符串列，此值是要索引的列的给定字节数。
ASC 可选的。指定该列的索引以升序排序。
DESC 可选的。指定该列的索引按降序排列。

创建索引示例

使用CREATE TABLE语句在MySQL中创建索引的示例。该语句将同时创建表和索引。

1
2
3
4
5
6
7
8
CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
CONSTRAINT contacts_pk PRIMARY KEY (contact_id),
INDEX contacts_idx (last_name, first_name)
);

在此示例中，我们创建了contacts表以及一个名为contacts_idx的索引，该索引由last_name和first_name列组成。

先创建表，然后使用CREATE INDEX语句创建索引。

1
2
3
4
5
6
7
CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);
1
2
CREATE INDEX contacts_idx
ON contacts (last_name, first_name);

在此示例中，CREATE TABLE语句将创建contacts表。CREATE INDEX语句将创建一个名为“ contacts_idx”的索引，该索引由last_name和first_name字段组成。

创建唯一索引

要在表上创建唯一索引，您需要在创建索引时指定UNIQUE关键字。

1
2
3
4
5
6
7
8
CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
CONSTRAINT contacts_pk PRIMARY KEY (contact_id),
UNIQUE INDEX contacts_idx (last_name, first_name)
);

或者

1
2
3
4
5
6
7
CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);
1
2
CREATE UNIQUE INDEX contacts_idx
ON contacts (last_name, first_name);

他们将在last_name和first_name字段上创建唯一索引，以便这些字段的组合必须始终包含唯一的值且没有重复。

删除索引

可以使用DROP INDEX语句在MySQL中删除索引。

语法

在MySQL中使用DROP INDEX语句删除索引的语法为：

1
2
DROP INDEX index_name
ON table_name;
参数 说明
index_name 要删除的索引的名称。
table_name 要删除的索引所在表的名称。

删除索引示例

1
2
DROP INDEX contacts_idx
ON contacts;

从contacts表中删除了一个名为contacts_idx的索引。

重命名索引

语法

在MySQL 5.6及更高版本中，使用ALTER TABLE语句重命名索引的语法为：

1
2
3
4
5
6
7
ALTER TABLE table_name
DROP INDEX index_name,
ADD INDEX new_index_name [ USING BTREE | HASH ]
(index_col1 [(length)] [ASC | DESC],
index_col2 [(length)] [ASC | DESC],
...
index_col_n [(length)] [ASC | DESC]);

在MySQL 5.7或更高版本中重命名索引的语法为：

1
2
ALTER TABLE table_name
RENAME INDEX index_name TO new_index_name;
参数 说明
table_name 索引所在表的名称。
index_name 重命名的索引的名称。
new_index_name 索引的新名称。

重命名索引示例

MySQL 5.6和更早版本：
需要使用ALTER TABLE语句首先删除旧索引，然后重新创建新索引。

1
2
3
ALTER TABLE contacts
DROP INDEX contacts_idx,
ADD INDEX contacts_new_index (last_name, first_name);

MySQL 5.7及更高版本：
使用带有RENAME INDEX子句的ALTER TABLE语句来重命名索引

1
2
ALTER TABLE contacts
RENAME INDEX contacts_idx TO contacts_new_index;

原文链接：https://codingdict.com/

热门标签