6.2 创建索引
MySQL支持用多种方法在单个或多个列上创建索引：在创建表的定义语句CREATE TABLE中指定索引列，使用ALTER TABLE语句在已有的表上创建索引或者使用CREATE INDEX语句在已有的表上添加索引。本节将详细介绍这三种方法。
6.2.1 创建表的时候创建索引
使用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．创建普通索引
最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。
【例6.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(11) 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=utf8
由结果可以看到，book1表的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:
1 row in set (0.05 sec)
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．创建唯一索引
创建索引的主要原因是为了减少查询索引列操作的执行时间。唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
【例6.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(11) NOT NULL,
'name' char(30) NOT NULL,
UNIQUE KEY 'UniqIdx' ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
由结果可以看到，id字段上已经成功建立了一个名为UniqIdx的唯一索引。
3．创建单列索引
单列索引是在数据表中的一个字段上创建的索引，一个表中可以创建多个单列索引。前面两个例子中创建的索引都为单列索引。
【例6.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(11) NOT NULL,
'name' char(50) DEFAULT NULL,
KEY 'SingleIdx' ('name'(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
由结果可以看到，id字段上已经成功建立了一个名为SingleIdx的单列索引，索引的长度为20。
4．创建组合索引
组合索引是在多个字段上创建的索引。
【例6.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(100))
);
上述语句执行完毕之后，使用SHOW CREATE TABLE查看表的结构：
mysql> SHOW CREATE table t3 \G
*************************** 1. row ***************************
Table: t3
CREATE Table: CREATE TABLE 't3' (
'id' int(11) NOT NULL,
'name' char(30) NOT NULL,
'age' int(11) NOT NULL,
'info' varchar(255) DEFAULT NULL,
KEY 'MultiIdx' ('id','name','age')
) ENGINE=InnoDB DEFAULT CHARSET=utf8
由结果可以看到，id、name和age字段上已经成功建立了一个名为MultiIdx的组合索引。
提示 组合索引可起到几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是要遵从“最左前缀”原则：利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如，这里由id、name和age三个字段构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索下面的字段组合：（id, name, age）、（id, name）或者id。
在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
1 row in set (0.00 sec)
可以看到，查询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的列上建立全文索引。
【例6.5】创建表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 5.6中的默认存储引擎为InnoDB，在这里创建表时需要将表的存储引擎改为MyISAM，不然创建全文索引时会出错。
上述语句执行完毕之后，使用SHOW CREATE TABLE查看表的结构：
mysql> SHOW CREATE table t4 \G
*************************** 1. row ***************************
Table: t4
CREATE Table: CREATE TABLE 't4' (
'id' int(11) NOT NULL,
'name' char(30) NOT NULL,
'age' int(11) NOT NULL,
'info' varchar(255) DEFAULT NULL,
FULLTEXT KEY 'FullTxtIdx' ('info')
) ENGINE=MyISAM DEFAULT CHARSET=utf8
由结果可以看到，info字段上已经成功建立了一个名为FullTxtIdx的FULLTEXT索引。全文索引非常适合在大型数据集中使用，对于小的数据集，它的用处可能比较小。
6．创建空间索引
空间索引必须在MyISAM类型的表中创建，且空间类型的字段不能为空。
【例6.6】创建表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=utf8
可以看到，t5表的g字段上创建了名称为spatIdx的空间索引。注意创建时要指定空间类型字段值的非空约束，并且表的存储引擎要为MyISAM。
6.2.2 在已有的表上创建索引
在已有的表中创建索引，可以使用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表示向表中添加索引。
【例6.7】在book表中的bookname字段上建立名为BkNameIdx的普通索引。
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:
上面各主要参数的含义如下。
（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语句查看表中的索引：
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:
可以看到，现在表中又新增了一个索引，即通过ALTER TABLE语句添加的名称为BkNameIdx的索引，该索引为非唯一索引，长度为30。
【例6.8】在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的索引为唯一索引，创建唯一索引成功。
【例6.9】在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字段上建立了名为BkcmgIdx的索引，长度为50，在查询时，只需要检索前50个字符。
【例6.10】在book表的authors和info字段上建立组合索引。
SQL语句如下：
ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(20),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。
【例6.11】创建表t6，在t6表上使用ALTER TABLE创建全文索引。
首先创建表t6，语句如下：
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，允许空值。
【例6.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语句查看索引：
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语句可以在已有的表上添加索引，基本语法结构如下：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name[length],…) [ASC | DESC]
可以看到CREATE INDEX语句和ALTER INDEX语句的语法基本一样，只是关键字不同。
在这里，使用相同的表book，假设该表中没有任何索引值，创建book表的语句如下：
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表删除，按上面的语句重新建立，然后进行下面的操作。
【例6.13】在book表中的bookname字段上建立名为BkNameIdx的普通索引。
SQL语句如下：
CREATE INDEX BkNameIdx ON book(bookname);
上述语句执行完毕之后，将在book表中创建名为BkNameIdx的普通索引。读者可以用SHOW INDEX或者SHOW CREATE TABLE语句查看book表中的索引，其索引内容与前面介绍的相同。
【例6.14】在book表的bookId字段上建立名为UniqidIdx的唯一索引。
SQL语句如下：
CREATE UNIQUE INDEX UniqidIdx ON book ( bookId );
语句执行完毕之后，将在book表中创建名为UniqidIdx的唯一索引。
【例6.15】在book表的comment字段上建立单列索引。
SQL语句如下：
CREATE INDEX BkcmtIdx ON book(comment(50) );
语句执行完毕之后，将在book表的comment字段上建立一个名为BkcmtIdx的单列索引，长度为50。
【例6.16】在book表的authors和info字段上建立组合索引。
SQL语句如下：
CREATE INDEX BkAuAndInfoIdx ON book ( authors(20),info(50) );
语句执行完毕之后，将在book表的authors和info字段上建立一个名为BkAuAndInfoIdx的组合索引。authors的索引序号为1，长度为20；info的索引序号为2，长度为50。
【例6.17】删除表t6，重新建立表t6，在t6表中使用CREATE INDEX语句，在CHAR类型的info字段上创建全文索引。
首先删除表t6，并重新建立该表，分别输入下面语句：
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 ON t6(info);
语句执行完毕之后，将在t6表中创建名为infoFTIdx的索引，该索引在info字段上创建，类型为FULLTEXT，允许空值。
【例6.18】删除表t7，重新创建表t7，在t7表中用CREATE INDEX语句在空间数据类型字段g上创建名为spatIdx的空间索引。
首先删除表t7，并重新建立该表，分别输入下面语句：
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字段上创建。