MySQL创建索引的3种方式（非常详细，附带实例）

MySQL 支持多种方法在单个或多个列上创建索引：

• 在创建表的语句 CREATE TABLE 中指定索引列；
• 使用 ALTER TABLE 语句在已经存在的表上创建索引；
• 使用 CREATE INDEX 语句在已经存在的表上添加索引。

本节将详细介绍这 3 种方法。

MySQL创建表的时候创建索引

使用 CREATE TABLE 语句创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束。而不论定义哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。

创建表时创建索引的基本语法格式如下：

CREATE  TABLE  table_name [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC | DESC]

• UNIQUE、FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
• INDEX 与 KEY 为同义词，两者作用相同，用来指定创建索引；
• col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
• index_name 指定索引的名称，为可选参数，如果不指定，MySQL 默认 col_name 为索引值；
• length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• ASC 或 DESC 指定升序或者降序的索引值存储。

1) 创建普通索引

普通索引是最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。

【实例】在表 book 中的 year_publication 字段上建立普通索引，SQL 语句如下：

CREATE TABLE book
(
bookid                  INT NOT NULL,
bookname                VARCHAR(255) NOT NULL,
authors                 VARCHAR(255) NOT NULL,
info                    VARCHAR(255) NULL,
comment                 VARCHAR(255) NULL,
year_publication        YEAR NOT NULL,
INDEX(year_publication)
);

该语句执行完毕之后，使用 SHOW CREATE TABLE 查看表结构：

mysql> SHOW CREATE table book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

由结果可以看到，表 book 的 year_publication 字段上已成功建立了索引，其索引名称 year_publication 为 MySQL 自动添加的。

使用 EXPLAIN 语句查看索引是否正在使用：

mysql> EXPLAIN SELECT * FROM book WHERE year_publication=1990 \G
*************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
       table: book
        type: ref
possible_keys: year_publication
         key: year_publication
     key_len: 1
         ref: const
        rows: 1
       Extra: Using index condition

EXPLAIN 语句输出结果的各行解释如下：

• id 行是查询结果的编号；
• select_type 行指定所使用的 SELECT 查询类型，这里值为 SIMPLE，表示简单的 SELECT，不使用 UNION 或子查询。其他可能的取值有 PRIMARY、UNION、SUBQUERY 等；
• table 行指定数据库读取的数据表的名字，它们按被读取的先后顺序排列；
• type 行指定本数据表与其他数据表之间的关联关系，可能的取值有 system、const、eq_ref、ref、range、index 和 All；
• possible_keys 行给出了 MySQL 在搜索数据记录时可选用的各个索引；
• key 行是 MySQL 实际选用的索引；
• key_len 行给出索引按字节计算的长度，key_len 数值越小，表示查询速度越快；
• ref 行给出了关联关系中另一个数据表里的数据列名；
• rows 行是 MySQL 在执行这个查询时预计会从这个数据表里读出的数据行的个数；
• Extra 行提供了与关联操作有关的信息。

可以看到，possible_keys 和 key 的值都为 year_publication，表明查询时使用了索引。

2) 创建唯一索引

创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其当数据表比较庞大时。

与前面的普通索引类似，创建唯一索引的不同之处在于，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

【实例】创建一张表 t1，在表中的 id 字段上使用 UNIQUE 关键字创建唯一索引，SQL 语句如下：

CREATE TABLE t1
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);

该语句执行完毕之后，使用 SHOW CREATE TABLE 查看表结构：

mysql> SHOW CREATE table t1 \G
*************************** 1. row ***************************
      Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

由结果可以看到，id 字段上已经成功建立了一个名为 UniqIdx 的唯一索引。

3) 创建单列索引

单列索引是在数据表中的某一个字段上创建的索引，一张表中可以创建多个单列索引。前面两个例子中创建的索引都为单列索引。

【实例】创建一张表 t2，在表中的 name 字段上创建单列索引，SQL 语句如下：

CREATE TABLE t2
(
id   INT NOT NULL,
name CHAR(50) NULL,
INDEX SingleIdx(name(20))
);

该语句执行完毕之后，使用 SHOW CREATE TABLE 查看表结构：

mysql> SHOW CREATE table t2 \G
*************************** 1. row ***************************
      Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL,
  `name` char(50) DEFAULT NULL,
  KEY `SingleIdx` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

由结果可以看到，id 字段上已经成功建立了一个名为 SingleIdx 的单列索引，索引长度为 20。

4) 创建组合索引

组合索引是在多个字段上创建一个索引。

【实例】创建表 t3，在表中的 id、name 和 age 字段上建立组合索引，SQL 语句如下：

CREATE TABLE t3
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
age  INT NOT NULL,
info VARCHAR(255),
INDEX MultiIdx(id, name, age)
);

该语句执行完毕之后，使用 SHOW CREATE TABLE 查看表结构：

mysql> SHOW CREATE table t3 \G
*** 1. row ***
      Table: t3
CREATE Table: CREATE TABLE `t3` (
  `id` int NOT NULL,
  `name` char(30) NOT NULL,
  `age` int NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `MultiIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

由结果可以看到，id、name 和 age 字段上已经成功建立了一个名为 MultiIdx 的组合索引。

组合索引可以起到几个索引的作用，但并不是随便查询哪个字段都可以使用组合索引，而是遵从“最左前缀”原则，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。

例如，这里的组合索引由 id、name 和 age 三个字段构成，索引行中按 id、name、age 的顺序存放，那么 MySQL 可以搜索（id, name, age）、（id, name）或者 id 字段组合。如果列不构成索引最左面的前缀，那么 MySQL 不能使用局部索引，如（age）或者（name,age）组合不能使用索引。

在表 t3 中，查询 id 和 name 字段，使用 EXPLAIN 语句查看索引的使用情况：

mysql> EXPLAIN SELECT * FROM t3 WHERE id=1 AND name='joe' \G
*** 1. row ***
          id: 1
  select_type: SIMPLE
       table: t3
        type: ref
possible_keys: MultiIdx
         key: MultiIdx
     key_len: 94
         ref: const,const
        rows: 1
       Extra: Using where

可以看到，查询 id 和 name 字段时，使用了名称为“MultiIdx”的索引。如果查询（name,age）组合或者单独查询 name 和 age 字段，则结果如下：

*** 1. row ***
           id: 1
  select_type: SIMPLE
        table: t3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where

此时，possible_keys 和 key 的值均为 NULL，并没有使用在表 t3 中创建的索引进行查询。

5) 创建全文索引

FULLTEXT 索引可以用于全文搜索。只有 MyISAM 存储引擎支持 FULLTEXT 索引，并且只为 CHAR、VARCHAR 和 TEXT 列创建全文索引。全文索引总是对整个列进行索引，不支持局部（前缀）索引。

【实例】创建表 t4，在表中的 info 字段上建立全文索引，SQL 语句如下：

CREATE TABLE t4
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
age  INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;

因为 MySQL 9.0 中默认存储引擎为 InnoDB，所以创建表时需要修改表的存储引擎为 MyISAM，否则创建全文索引会出错。

语句执行完毕之后，使用 SHOW CREATE TABLE 查看表结构：

mysql> SHOW CREATE table t4 \G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int NOT NULL,
  `name` char(30) NOT NULL,
  `age` int NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `FullTxtIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

由结果可以看到，info 字段上已经成功建立了一个名为“FullTxtIdx”的全文索引。全文索引非常适合于大型数据集，对于小的数据集，它的用处比较小。

6) 创建空间索引

空间索引必须在 MyISAM 类型的表中创建，且空间类型的字段必须非空。

【实例】创建表 t5，在空间类型为 GEOMETRY 的字段上创建空间索引，SQL 语句如下：

CREATE TABLE t5
(
g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
)ENGINE=MyISAM;

该语句执行完毕之后，使用 SHOW CREATE TABLE 查看表结构：

mysql> SHOW CREATE table t5 \G
*** 1. row ***
      Table: t5
CREATE Table: CREATE TABLE `t5` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatIdx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

可以看到，表 t5 的 g 字段上创建了名称为“spatIdx”的空间索引。注意在创建时指定空间类型字段值的非空约束，并且表的存储引擎为 MyISAM。

MySQL在已经存在的表上创建索引

在已经存在的表上创建索引，可以使用 ALTER TABLE 语句或者 CREATE INDEX 语句。下面详细如何使用 ALTER TABLE 和 CREATE INDEX 语句在已知表的字段上创建索引。

1) 使用ALTER TABLE语句创建索引

使用 ALTER TABLE 语句创建索引的基本语法如下：

ALTER TABLE table_name  ADD [UNIQUE|FULLTEXT|SPATIAL]  [INDEX|KEY]
[index_name] (col_name[length],...) [ASC | DESC]

与创建表时创建索引的语法不同的是，在这里使用了ALTER TABLE 和 ADD 关键字，ADD 表示向表中添加索引。

【实例】在表 book 中的 bookname 字段上建立名为“BkNameIdx”的普通索引。
添加索引之前，使用 SHOW INDEX 语句查看指定表中创建的索引：

mysql> SHOW INDEX FROM book \G
*** 1. Row ***
        Table: book
   Non_unique: 1
     Key_name: year_publication
Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

其中各个主要参数的含义如下：

• Table 表示创建索引的表；
• Non_unique 表示索引非唯一，1 代表是非唯一索引，0 代表是唯一索引；
• Key_name 表示索引的名称；
• Seq_in_index 表示该字段在索引中的位置，如果是单列索引，则该值为 1；组合索引，则为每个字段在索引定义中的顺序；
• Column_name 表示定义索引的列字段；
• Sub_part 表示索引的长度；
• Null 表示该字段是否能为空值；
• Index_type 表示索引类型。

可以看到，表 book 中已经存在了一个索引，即前面定义的名称为“year_publication”的索引，该索引为非唯一索引。

下面使用 ALTER TABLE 在 bookname 字段上添加索引，SQL 语句如下：

ALTER TABLE book ADD INDEX BkNameIdx( bookname(30) );

使用 SHOW INDEX 语句查看表中的索引：

mysql> SHOW INDEX FROM book \G
*** 1. Row ***
        Table: book
   Non_unique: 1
     Key_name: year_publication
Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
   Index_type: BTREE
     Comment:
Index_comment:

*** 2. Row ***
        Table: book
   Non_unique: 1
     Key_name: BkNameIdx
Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
    Sub_part: 30
      Packed: NULL
        Null:
   Index_type: BTREE
     Comment:
Index_comment:

可以看到，现在表中有了两个索引，一个为 year_publication；另一个为通过 ALTER TABLE 语句添加的名称为“BkNameIdx”的索引，该索引为非唯一索引，长度为 30。

【实例】在表 book 的 bookId 字段上建立名称为“UniqidIdx”的唯一索引，SQL 语句如下：

ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );

使用 SHOW INDEX 语句查看表中的索引：

mysql> SHOW INDEX FROM book \G
*** 1. Row ***
        Table: book
   Non_unique: 0
     Key_name: UniqidIdx
Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
   Index_type: BTREE
     Comment:
Index_comment:

可以看到 Non_unique 属性值为 0，表示名称为 UniqidIdx 的索引为唯一索引，说明创建唯一索引成功。

【实例】在表 book 的 comment 字段上建立单列索引，SQL 语句如下：

ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );

使用 SHOW INDEX 语句查看表中的索引：

*** 3. Row ***
       Table: book
   Non_unique: 1
    Key_name: BkcmtIdx
Seq_in_index: 1
  Column_name: comment
   Collation: A
  Cardinality: 0
    Sub_part: 50
      Packed: NULL
        Null: YES
   Index_type: BTREE
     Comment:
Index_comment:

可以看到，在表 book 的 comment 字段上建立了名称为“BkcmtIdx”的索引，长度为 50，在查询时，只需要检索前 50 个字符。

【实例】在表 book 的 authors 和 info 字段上建立组合索引，SQL 语句如下：

ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(30),info(50) );

使用 SHOW INDEX 语句查看表中的索引：

mysql> SHOW INDEX FROM book \G
*** 4. Row ***
       Table: book
   Non_unique: 1
    Key_name: BkAuAndInfoIdx
Seq_in_index: 1
  Column_name: authors
   Collation: A
  Cardinality: 0
    Sub_part: 30
      Packed: NULL
        Null:
   Index_type: BTREE
     Comment:
Index_comment:
*** 5. Row ***
       Table: book
   Non_unique: 1
    Key_name: BkAuAndInfoIdx
Seq_in_index: 2
  Column_name: info
   Collation: A
  Cardinality: 0
    Sub_part: 50
      Packed: NULL
        Null: YES
   Index_type: BTREE
     Comment:
Index_comment:

可以看到，名称为“BkAuAndInfoIdx”的索引由两个字段组成，authors 字段长度为 30，在组合索引中的序号为 1，该字段不允许为 NULL；info 字段长度为 50，在组合索引中的序号为 2，该字段可以为 NULL。

【实例】创建表 t6，并在表 t6 上使用 ALTER TABLE 创建全文索引。
首先创建表 t6，SQL 语句如下：

CREATE TABLE t6
(
id    INT NOT NULL,
info  CHAR(255)
) ENGINE=MyISAM;

注意，需要修改 ENGINE 参数为 MyISAM，MySQL 的默认存储引擎 InnoDB 不支持全文索引。

然后使用 ALTER TABLE 语句在 info 字段上创建全文索引：

ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );

最后使用 SHOW INDEX 语句查看索引：

mysql> SHOW index from t6 \G
** 1. Row ***
       Table: t6
  Non_unique: 1
    Key_name: infoFTIdx
Seq_in_index: 1
Column_name: info
   Collation: NULL
Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: FULLTEXT
    Comment:
ndex_comment:

可以看到，表 t6 中已经创建了名称为“infoFTIdx”的索引，该索引在 info 字段上创建，类型为 FULLTEXT，允许为 NULL。

【实例】创建表 t7，并在表 t7 的空间数据类型字段 g 上创建名称为“spatIdx”的空间索引。
首先创建表 t7，SQL 语句如下：

CREATE TABLE t7 ( g GEOMETRY NOT NULL )ENGINE=MyISAM;

然后使用 ALTER TABLE 在表 t7 的 g 字段建立空间索引：

ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);

最后使用 SHOW INDEX 语句查看索引：

mysql> SHOW index from t7 \G
*** 1. Row ***
        Table: t7
   Non_unique: 1
     Key_name: spatIdx
Seq_in_index: 1
  Column_name: g
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null:
   Index_type: SPATIAL
      Comment:
Index_comment:

由结果可以看到，表 t7 的 g 字段上创建了名称为“spatIdx”的空间索引。

2) 使用CREATE INDEX语句创建索引

使用 CREATE INDEX 语句可以在已经存在的表上添加索引。在 MySQL 中，CREATE INDEX 被映射到一个 ALTER TABLE 语句上，基本语法结构为：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

可以看到，CREATE INDEX 语句和 ALTER INDEX 语句的语法基本一样，只是关键字不同。

在这里，使用相同的表 book，假设该表中没有任何索引值，创建表 book 的 SQL 语句如下：

CREATE TABLE book
(
bookid             INT NOT NULL,
bookname           VARCHAR(255) NOT NULL,
authors            VARCHAR(255) NOT NULL,
info               VARCHAR(255) NULL,
comment           VARCHAR(255) NULL,
year_publication  YEAR NOT NULL
);

读者可以将数据库中的表 book 删除，然后按上面的语句重新建立，再进行下面的操作。

【实例】在表 book 中的 bookname 字段上建立名称为“BkNameIdx”的普通索引，SQL 语句如下：

CREATE INDEX BkNameIdx ON book(bookname);

语句执行完毕之后，将在表 book 中创建名称为“BkNameIdx”的普通索引。读者可以使用 SHOW INDEX 或者 SHOW CREATE TABLE 语句查看表 book 中的索引，其索引内容与前面介绍的相同。

【实例】在表 book 的 bookId 字段上建立名称为“UniqidIdx”的唯一索引，SQL 语句如下：

CREATE UNIQUE INDEX UniqidIdx  ON book ( bookId );

语句执行完毕之后，将在表 book 中创建名称为“UniqidIdx”的唯一索引。

【实例】在表 book 的 comment 字段上建立单列索引，SQL 语句如下：

CREATE INDEX BkcmtIdx ON book(comment(50) );

语句执行完毕之后，将在表 book 的 comment 字段上建立一个名称为“BkcmtIdx”的单列索引，长度为 50。

【实例】在表 book 的 authors 和 info 字段上建立组合索引，SQL 语句如下：

CREATE INDEX BkAuAndInfoIdx ON book ( authors(20),info(50) );

语句执行完毕之后，在表 book 的 authors 和 info 字段上建立了一个名称为“BkAuAndInfoIdx”的组合索引，authors 的索引序号为 1、长度为 20，info 的索引序号为 2、长度为 50。

【实例】先删除表 t6，再重新建立表 t6，在表 t6 中使用 CREATE INDEX 语句在 CHAR 类型的 info 字段上创建全文索引。
首先删除表 t6，并重新建立该表，SQL 语句如下：

mysql> drop table t6;
Query OK, 0 rows affected (0.00 sec)
　　　　　　　
mysql> CREATE TABLE t6
    (
    id    INT NOT NULL,
    info  CHAR(255)
    ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

然后使用 CREATE INDEX 在表 t6 的 info 字段上创建名称为“infoFTIdx”的全文索引：

CREATE FULLTEXT INDEX infoFTIdx ON t6(info);

语句执行完毕之后，将在表 t6 中创建名称为“infoFTIdx”的索引，该索引在 info 字段上创建，类型为 FULLTEXT，允许为 NULL。

【实例】删除表 t7，重新创建表 t7，在表 t7 中使用 CREATE INDEX 语句在空间数据类型字段 g 上创建名称为“spatIdx”的空间索引：
首先删除表 t7，并重新建立该表，SQL 语句如下：

mysql> drop table t7;
Query OK, 0 rows affected (0.00 sec)
　　　　　　　　
mysql> CREATE TABLE t7 ( g GEOMETRY NOT NULL )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

然后使用 CREATE INDEX 语句在表 t7 的 g 字段建立空间索引：

CREATE SPATIAL INDEX spatIdx ON t7 (g);

语句执行完毕之后，将在表 t7 中创建名称为“spatIdx”的空间索引，该索引在 g 字段上创建。