5.2　创建索引

MySQL支持多种方法在单个或多个列上创建索引：在创建表的定义语句CREATE TABLE中指定索引列，使用ALTER TABLE语句在存在的表上创建索引，或者使用CREATE INDEX语句在已存在的表上添加索引。本节将详细介绍这3种方法。

5.2.1　创建表的时候创建索引

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

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

1. 创建普通索引

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

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

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

由结果可以看到，book1表的year_publication字段上成功建立索引，其索引名称year_publication为MySQL自动添加的。使用EXPLAIN语句查看索引是否正在使用：

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

（1）select_type行指定所使用的SELECT查询类型，这里值为SIMPLE，表示简单的SELECT，不使用UNION或子查询。其他可能的取值有PRIMARY、UNION、SUBQUERY等。

（2）table行指定数据库读取的数据表的名字，它们按被读取的先后顺序排列。

（3）type行指定本数据表与其他数据表之间的关联关系，可能的取值有system、const、eq_ref、ref、range、index和all。

（4）possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。

（5）key行是MySQL实际选用的索引。

（6）key_len行给出索引按字节计算的长度，key_len数值越小，表示越快。

（7）ref行给出了关联关系中另一个数据表里的数据列的名字。

（8）rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。

（9）Extra行提供了与关联操作有关的信息。

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

2. 创建唯一索引

创建唯一索引的主要目的是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。它与前面的普通索引类似，不同的是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

【例5.2】创建一个表t1，在表中的id字段上使用UNIQUE关键字创建唯一索引。

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

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

3. 创建单列索引

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

【例5.3】创建一个表t2，在表中的name字段上创建单列索引。

表结构如下：

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

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

4. 创建组合索引

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

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

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

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

组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵从“最左前缀”：利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如，这里由id、name和age三个字段构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索的字段组合为：（id, name, age）、（id, name）或者id。如果列不构成索引最左面的前缀，MySQL就不能使用局部索引，如（age）或者（name,age）组合不能使用索引查询。

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

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

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

5. 创建全文索引

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

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

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

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

6. 创建空间索引

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

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

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

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

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

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句，本节将介绍如何使用ALTER TABLE和CREATE INDEX语句在已知的表字段上创建索引。

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

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

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

【例5.7】在book表中的bookname字段上建立名为BkNameIdx的普通索引。

添加索引之前，使用SHOW INDEX语句查看指定表中创建的索引：

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

（1）Table表示创建索引的表。

（2）Non_unique表示索引非唯一，1代表非唯一索引，0代表唯一索引。

（3）Key_name表示索引的名称。

（4）Seq_in_index表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引定义中的顺序。

（5）Column_name表示定义索引的列字段。

（6）Sub_part表示索引的长度。

（7）Null表示该字段是否能为空值。

（8）Index_type表示索引类型。

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

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

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

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

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

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

     ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );

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

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

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

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

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

可以看到，语句执行之后，在book表的comment字段上建立了名称为BkcmgIdx的索引，长度为50，在查询时，只需要检索前50个字符。

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

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

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

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

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

首先创建表t6，语句如下：

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

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

     ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );

使用SHOW INDEX语句查看索引：

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

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

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

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

     ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);

使用SHOW INDEX语句查看索引：

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

2. 使用CREATE INDEX创建索引

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

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

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

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

     CREATE INDEX BkNameIdx ON book(bookname);

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

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

     CREATE UNIQUE INDEX UniqidIdx  ON book ( bookId );

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

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

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

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

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

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

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

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

首先删除表t6，并重新建立该表，分别输入下面的语句：

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

     CREATE FULLTEXT INDEX infoFTIdx ON t6(info);

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

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

首先删除表t7，并重新建立该表，分别输入下面的语句：

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

     CREATE SPATIAL INDEX spatIdx ON t7 (g);

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