MySQL Index 索引

时间:2019-02-04 12:52:27  来源:igfitidea点击:

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

MySQL中的索引是什么?

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

创建一个索引

有两种创建索引的方法。

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

语法

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

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语句创建索引的语法为:

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中创建索引的示例。该语句将同时创建表和索引。

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语句创建索引。

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)
);
CREATE INDEX contacts_idx
  ON contacts (last_name, first_name);

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

创建唯一索引

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

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)
);

或者

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)
);
CREATE UNIQUE INDEX contacts_idx
  ON contacts (last_name, first_name);

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

删除索引

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

语法

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

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

删除索引示例

DROP INDEX contacts_idx
  ON contacts;

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

重命名索引

语法

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

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或更高版本中重命名索引的语法为:

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

重命名索引示例

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

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

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

ALTER TABLE contacts
  RENAME INDEX contacts_idx TO contacts_new_index;