6.2 创建和查看索引
创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有3种方式，分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。本节将详细讲解这3种创建索引的方法。
6.2.1 普通索引
所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。
创建一个普通索引时，不需要加任何UNIQUE、FULLTEXT或者SPATIAL参数。MySQL所支持的存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。
提示
在创建索引时，可以指定索引的长度，这是因为不同存储引擎定义了表的最大索引数和最大索引长度。
1. 创建表时直接创建
创建表时可以直接创建索引，这种方式最简单、方便。MySQL创建普通索引通过SQL语句INDEX来实现，其基本形式如下：
CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
……
propnamen typen
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
[indexname](propname1 [(length)] [ASC|DESC]));
其中，参数UNIQUE是可选参数，表示索引为唯一性索引；参数FULLTEXT是可选参数，表示索引是全文索引；参数SPATIAL也是可选参数，表示索引为空间索引；参数INDEX和KEY是用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；参数indexname是索引名字；参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列。
【示例6-1】在数据库school中，创建班级表t_class时在字段classno上创建索引。
（1）创建班级表t_class时指定索引，具体SQL语句如下，执行结果如图6-1所示。
CREATE TABLE t_class(
classno INT(4),
cname VARCHAR(20),
loc VARCHAR(40),
INDEX index_classno(classno));
（2）为了检验班级表t_class中索引是否创建成功，执行SQL语句SHOW CREATE TABLE，具体SQL语句如下，执行结果如图6-2所示。
SHOW CREATE TABLE t_class \G;
图6-1 创建班级表
图6-2 查看班级表信息
（3）为了检验班级表t_class中的索引是否被使用，执行SQL语句EXPLAIN，具体SQL语句如下，执行结果如图6-3所示。
EXPLAIN SELECT * FROM t_class WHERE classno=1\G;
图6-3 查看索引是否启用
图6-2的执行结果显示，已经在班级表t_class中创建了一个名为index_classno的索引，其所关联的字段为classno。图6-3的执行结果显示，由于字段possible_keys和key处的值都为所创建的索引名index_classno，说明该索引已经存在，而且已经开始启用。
2. 在已经存在的表上创建
可以在已存在的表上通过CREATE语句创建索引，语法形式如下：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX indexname
ON tablename (propname [(length)] [ASC|DESC]);
其中，参数UNIQUE是可选参数，表示索引为唯一性索引；参数FULLTEXT是可选参数，表示索引为全文索引；参数SPATIAL也是可选参数，表示索引为空间索引；参数INDEX是用来指定字段为索引的；参数indexname是新创建的索引的名字；参数tablename是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；参数propname指定索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，表示索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列。
在上述例子中，如果表t_class已存在，可通过如下语句创建索引：
CREATE INDEX index_classno ON t_class(classno);
3. 通过ALTER TABLE语句创建
可以通过SQL语句ALTER来创建索引，其语法形式如下：
在上述语句中，参数tablename是需要创建索引的表；关键字IDNEX或KEY用来指定创建普通索引；参数indexname用来指定所创建的索引名；参数propname用来指定索引所关联的字段的名称；参数length用来指定索引的长度；参数ASC用来指定升序排序；参数DESC用来指定降序排序。
在上述例子中，如果表t_class已存在，可以通过如下语句创建索引：
ALTER TABLE t_class ADD INDEX index_classno(classno);
6.2.2 唯一索引
所谓唯一索引，就是在创建索引时，限制索引的值必须是唯一的。通过该类型的索引可以更快速地查询某条记录。在MySQL中，根据创建索引方式，可以分为自动索引和手动索引两种：
- 自动索引，是指在数据库表里设置完整性约束，该表会被系统自动创建索引。
- 手动索引，是指手动在表上创建索引。当设置表中的某个字段设置主键或唯一完整性约束时，系统就会自动创建关联该字段的唯一索引。
1. 创建表时直接创建
在MySQL中创建唯一索引通过SQL语句UNIQUE INDEX来实现，其语法形式如下：
在上述语句中，比普通索引多了一个SQL关键字UNIQUE，其中UNIQUE INDEX或UNIQUE KEY表示创建唯一索引。
【示例6-2】创建表时创建唯一索引。
（1）将示例6-1中的创建普通索引改为创建唯一索引，其SQL语句如下，执行结果如图6-4所示。
图6-4 创建表t_class
（2）为了检验数据库表t_class中的索引是否创建成功，执行SQL语句SHOW CREATE TABLE，具体SQL语句如下，执行结果如图6-5所示。
SHOW CREATE TABLE t_class \G;
图6-5 查看表t_class信息
2. 在已经存在的表上创建
在MySQL中创建唯一索引除了通过SQL语句UNIQUE INDEX来实现外，还可以通过SQL语句CREATE UNIQUE INDEX来实现，其语法形式如下：
CREATE UNIQUE INDEX indexname
ON tablename(propname1 [(length)] [ASC|DESC])
在上述语句中，关键字CREATE UNIQUE INDEX用来创建唯一索引，参数indexname是索引名，参数tablename是表名。
在示例6-2中，如果表已存在，可通过CREATE语句直接创建索引：
CREATE UNIQUE INDEX index_classno ON t_class(classno);
3. 通过ALTER TABLE语句创建
在MySQL中创建唯一索引除了通过SQL语句ALTER来实现，其语法形式如下：
在上述语句中，关键字UNIQUE KEY或KEY用来指定创建唯一索引，参数indexname用来指定所创建的索引名；参数tablename是表名；参数propname用来指定索引所关联的字段的名称；参数length用来指定索引的长度；参数ASC用来指定升序排序；参数DESC用来指定降序排序。
在示例6-2中，如果表已存在，也可以通过ALTER语句创建索引：
ALTER TABLE t_class ADD UNIQUE INDEX index_classno(classno);
6.2.3 全文索引
全文索引主要关联在数据类型为CHAR、VARCHAR和TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。
MySQL从3.23.23版本开始支持全文索引，只能在存储引擎为MyISAM的数据表上创建全文索引。在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，就以区分大小写的搜索方式执行。
1. 创建表时直接创建
在MySQL中创建全文索引通过SQL语句FULLTEXT INDEX实现，其语法形式如下：
CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
……
propnamen typen
FULLTEXT INDEX|KEY [indexname](propname1 [(length)] [ASC|DESC]) );
在上述语句中比创建普通索引多一个SQL关键字FULLTEXT，其中FULLTEXT INDEX或FULLTEXT KEY表示创建全文索引。
【示例6-3】执行SQL语句FULLTEXT INDEX，在数据库school的班级表t_class的字段loc上创建全文索引。
（1）执行SQL语句UNIQUE INDEX，在创建班级表t_class时，在字段classno上创建唯一索引，具体SQL语句如下，执行结果如图6-6所示。
图6-6 创建表t_class
（2）为了检验班级表t_class中全文索引是否创建成功，执行SQL语句SHOW CREATE TABLE，具体SQL语句如下，执行结果如图6-7所示。
SHOW CREATE TABLE t_class \G;
（3）为了检验班级表t_class中索引是否被使用，执行SQL语句EXPLAIN，具体SQL语句如下，执行结果如图6-8所示。
EXPLAIN SELECT * FROM t_class WHERE cname=’beijign’ \G;
图6-7 查看表信息
图6-8 查看索引是否被启用
2. 在已经存在的表上创建
在MySQL中创建全文索引除了通过SQL语句FULLTEXT INDEX来实现外，还可以通过SQL语句CREATE FULLTEXT INDEX来实现，其语法形式如下：
CREATE FULLTEXT INDEX indexname
ON tablename(propname1 [(length)] [ASC|DESC])
在上述语句中，关键字CREATE FULLTEXT INDEX表示用来创建全文索引。
如果示例6-3中的表已存在，可通过如下语句创建全文索引：
CREATE FULLTEXT INDEX ON t_class(loc);
3. 通过ALTER TABLE语句创建
除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句ALTER来实现，其语法形式如下：
ALTER TABLE tablename
ADD FULLTEXT INDEX|KEY indexname(propname [(length)] [ASC|DESC])
在上述语句中，关键字FULLTEXT INDEX或KEY用来指定创建全文索引；参数indexname表示索引名；参数propname指定索引所关联的字段的名称；参数length用来指定索引的长度；参数ASC用来指定升序排序；参数DESC用来指定降序排序。
在示例6-3中，如果表已存在，可通过如下语句创建全文索引：
ALTER TABLE t_class ADD FULLTEXT INDEX index_loc(loc);
6.2.4 多列索引
所谓多列索引，是指在创建索引时所关联的字段不是一个字段，而是多个字段，虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。
1. 创建表时直接创建
在MySQL中创建多列索引通过SQL语句INDEX来实现，其语法形式如下：
在上述语句中，关联的字段至少大于一个字段。
【示例6-4】执行SQL语句INDEX，在数据库school中，在表t_class的cname和loc字段创建多列索引，具体步骤如下：
（1）执行SQL语句INDEX，在创建班级表t_class时，在字段cname和字段loc上创建多列索引，具体SQL语句如下，执行结果如图6-9所示。
CREATE TABLE t_class(
classno INT(4),
cname VARCHAR(20),
loc VARCHAR(40),
KEY index_cname_loc(cname,loc));
图6-9 创建表t_class
（2）为了检验班级表t_class中多列索引是否创建成功，执行SQL语句SHOW CREATE TABLE，具体SQL语句如下，执行结果如图6-10所示。
SHOW CREATE TABLE t_class \G
（3）为了检验班级表t_class中的索引是否被使用，执行SQL语句EXPLAIN，具体SQL语句如下，执行结果如图6-11所示。
EXPLAIN SELECT * FROM t_class WHERE cname=’beijing’ \G
图6-10 查看班级表信息
图6-11 创建索引是否被启用
图6-11的执行结果显示，字段possible_keys和key处的值都为所创建的索引名index_cname_loc，说明该索引已经存在，而且已经开始启用。
2. 在已经存在的表上创建
在MySQL中创建多列索引，除了可以在创建表时实现外，还可以为已经存在的表设置多列索引，其语法形式如下：
在上述语句中比创建普通索引多关联了几个字段。
在示例6-4中，如果表已存在，可通过如下语句创建多列索引：
CREATE INDEX index_cname_loc ON t_class(cname,loc);
3. 通过ALTER TABLE语句创建
在MySQL中创建多列索引，除了可以在创建表时实现外，还可以为已经存在的表设置多列索引，其语法形式如下：
在上述语句中比创建普通索引多关联了几个字段。
在示例6-4中，如果表已存在，可通过如下语句创建多列索引：
ALTER TABLE t_class ADD INDEX index_cname_loc(cname,loc);