创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创 建索引有3种方式，这3种方式分别是创建表的时候创建索引、在已经存在的表上创建索 引和使用LAERT TABLE语句来创建索引。本节将详细讲解这3种创建索引的方法。

创建表时可以直接创建索引，这种方式最简单、方便。其基本形式如下：

CREATE TABLE表名（属性名 数据类型[完整性约束条件],属性名 数据类型[完整性约束条件],
属性名 数据类型[UNIQUE | FULLTEXT | SPATIAL ] INDEX 丨 KEY [别名]（属性名1 [(长度)][ASC|DESC])
)；

其中，UNIQUE是可选参数，表示索引为唯一性索引；FULLTEXT是可选参数，表示索引为全文索引；PPATIAL也是可选参数，表示索引为空间索引；INDEX和KEY参数用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；“别名”是可选参数, 用来给创建的索引取的新名称；“属性1”参数指定索引对应的字段的名称，该字段必须 为前面定义好的字段；“长度”是可选参数，其指索引的长度，必须是字符串类型才可以使用；“ASC”和“DESC”都是可选参数，“ASC”参数表示升序排列，“DESC”参数表示降序排列。

1.创建普通索引

创建一个普通索引时，不需要加任何UNIQUE、FULLTEXT或者SPATIAL参数。

【示例7-1】下面创建一个表名为index1的表，在表中的id字段上建立索引。SQL语句如下：

CREATE TABLE index1 (id INT,name VARCHAR(20),sex BOOLEAN,INDEX (id));

运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下:

mysql> SHOW CREATE TABLE index1 \G;
*************************** 1. row ***************************
       Table: index1
Create Table: CREATE TABLE `index1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

结果可以看到，id字段上已经建立了一个名为indexl_id的索引。使用EXPLAIN语句可以查看索引是否被使用，SQL代码如下：

mysql> EXPLAIN SELECT * FROM index1 WHERE id=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index1
         type: ref
possible_keys: id
          key: id
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.02 sec)

上面结果显示，possible_keys和key处的值都为indexl_id。说明indexl」d索引已经存在，而且已经开始起作用。

2.创建唯一性索引

创建唯一性索引时，需要使用UNIQUE参数进行约束。

【示例7-2】卜面创建一个表名为index2的表，在表中的id字段上建立名为index2_id 的唯一性索引，且以升序的形式排列。SQL代码如下：

CREATE TABLE index2(id INT UNIQUE,name VARCHAR(20)，UNIQUE INDEX index2_id (id ASC);

运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下:

mysql> SHOW CREATE TABLE index2 \G;
*************************** 1. row ***************************
       Table: index2
Create Table: CREATE TABLE `index2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `index2_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

结果可以看到，id段上已经建立了一个名为index2_id的唯一性索引。这里的id字段可以没有进行唯一性约朿，也可以在该字段上成功创建唯一性索引。但是，这样可能达不到提高查询速度的目的。

3.创建全文索引

全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。而且，现在只有 MyISAM存储引擎支持全文索引。

【示例7-3】下面创建一个表名为index3的表，在表中的info字段上建立名为index3_ info的全文索引。SQL代码如下：

CREATE TABLE index3 (id INT ,info VARCHAR(20),FULLTEXT INDEX index3_info (info ))ENGINE=MyISAM;

运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下：

mysql> SHOW CREATE TABLE index3 \G;
*************************** 1. row ***************************
       Table: index3
Create Table: CREATE TABLE `index3` (
  `id` int(11) DEFAULT NULL,
  `info` varchar(20) DEFAULT NULL,
  FULLTEXT KEY `index3_info` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

结果可以看到，info字段上已经建立了一个名为index3_info的全文索引。如果表的存储引擎不是MyISAM存储引擎，系统会提示“ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes ”。

注意：目前只有MyISAM存储引擎支持全文索引，InnoDB存储引擎还不支持全文索引。因此，在创建全文索引时一定注意表的存储引擎的类型。对于经常需要索引的字符串、文字数据等信息，可以考虑存储到MyISAM存储引擎的表中。

4.创建单列索引

单列索引是在表的单个字段上创建索引。

【示例7-4】下面创建一个表名为index4的表，在表中的subject字段上建立名为 index4_st的单列索引。SQL代码如下：

CREATE TABLE index4(id INT ,subject VARCHAR(30),INDEX index4_st (subject(10)));

运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下

mysql> SHOW CREATE TABLE index4 \G;
*************************** 1. row ***************************
       Table: index4
Create Table: CREATE TABLE `index4` (
  `id` int(11) DEFAULT NULL,
  `subject` varchar(30) DEFAULT NULL,
  KEY `index4_st` (`subject`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

结果可以看到，subject字段上已经建立了一个名为index4_st的单列索弓丨。细心的读者可能会发现，subject字段长度为20，而index_t索引的长度只有10。这样做的目的还是为了提髙查询速度。对于字符型的数据，可以不用査询全部信息，而只查询其前面的若干 字符信息。

5.创建多列索引

创建多列索引是在表的多个字段上创建一个索引。

【示例7-5】下面创建一个表名为index5的表，在表中的name和sex字段上建立名为 index5_ns的多列索引。SQL代码如下:

CREATE TABLE index5 (id INT ,name VARCHAR(20), sex CHAR(4),INDEX index5_ns ( name, sex ));

运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下:

mysql> SHOW CREATE TABLE index5 \G;
*************************** 1. row ***************************
       Table: index5
Create Table: CREATE TABLE `index5` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` char(4) DEFAULT NULL,
  KEY `index5_ns` (`name`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

结果可以看到，name和sex字段上已经建立了一个名为index5_ns的单列索引。多列索引中，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。用EXPLAIN语句可以查看索引的使用情况。如果只是有name字段作为查询条件进行查询，显示结果如下：

mysql> EXPLAIN select * from index5 where name='hjh' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index5
         type: ref
possible_keys: index5_ns
          key: index5_ns
      key_len: 63
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.01 sec)

结果显示，possible_keys和key的值都是index5_ns。额外信息（Extra)显示正在使用索引。这说明使用name字段进行索引时，索引index_ns已经被使用。如果只使用sex字段作为查询条件进行查询，显示结果如下：

mysql> EXPLAIN select * from index5 where sex='n' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index5
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

此时的结果显示，possible_keys和key的值都为NULL。额外信息（Extra)显示正在 使用Where条件查询，而未使用索引。

技巧:使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。因此，在优化查询速度时，可以考虑优化多列索引。

6.创建空间索引

创建空间索引时必须使用SPATIAL参数来设置。创建空间索引时，表的存储引擎必 须是MyISAM类型。而且，索引字段必须有非空约束。

【示例7-6】 F面创建一个表名为 index6的表，在表中的space字段上建立名为 index6_sp的空间索引。SQL代码如下：

CREATE TABLE index6(id INT ,space GEOMETRY NOT NULL,SPATIAL INDEX index6_sp (space ))ENGINE=MyISAM;

运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下：

mysql> SHOW CREATE TABLE index6 \G;
*************************** 1. row ***************************
       Table: index6
Create Table: CREATE TABLE `index6` (
  `id` int(11) DEFAULT NULL,
  `space` geometry NOT NULL,
  SPATIAL KEY `index6_sp` (`space`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

结果可以看到，space字段上已经建立了一个名为index_6的空间索引。值得注意的是，space字段是非空的，而且数据类型是GEOMETRY类型。这个类型是空间数据类型。空间类型包括GEOMETRY、POINT、LINESTRING和POLYGON类型等。这些空间数据类型平时很少用到。

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

在已经存在的表中，可以直接为表上的一个或几个字段创建索引。基本形式如下：

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON表名（属性名[(长度）][ASC | DESC]);

其中，UNIQUE是可选参数，表示索引为唯一性索引；FULLTEXT是可选参数，表示索引为全文索引；SPATIAL也是可选参数，表示索引为空间索引；“INDEX，，参数用来指定字段为索引的；“索引名”参数是给创建的索引取的新名称；“表名”参数是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；“属性名”参数 指定索引对应的字段的名称，该字段必须为前面定义好的字段；“长度”是可选参数，其指索引的长度必须是字符串类型才可以使用；ASC和DESC都是可选参数，ASC参数表示升序排列，DESC参数表示降序排列。

1.创建普通索引

【示例7-7】下面在example0表中的id字段上建立名为index7_id的索引。SQL代码如下：

CREATE INDEX index7_id ON exampleO (id );

在创建索引之前，先使用SHOW CREATE TABLE语句查看example0表的结构，显示如下：

mysql> SHOW CREATE TABLE example0 \G;
*************************** 1. row ***************************
       Table: example0
Create Table: CREATE TABLE `example0` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  KEY `indes7_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

查询结果显示，example0表还没有索引。下面使用CREATE INDEX语句创建索引。 CREATE INDEX语句执行结果如下：

mysql> CREATE INDEX index7_id ON exampleO (id );Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下:

mysql> show create table example0 \G;
*************************** 1. row ***************************
       Table: example0
Create Table: CREATE TABLE `example0` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  KEY `indes7_id` (`id`),
  KEY `index7_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

结果可以看到，example0表中的id字段上已经创建了一个名为index7_id的索引。这表示使用CREATE INDEX语句成功的在example0表上创建了普通索引。

2.创建唯一性索引

【示例7-8】下面在index8表中的course_id字段上建立名为index8_id的唯一性索弓丨。 SQL代码如下：

CREATE UNIQUE INDEX index8_id ON index8( course_id);

其中，index8_id为索引的名词；UNIQUE用来设置索引为唯一性索引；表index_8中 的course_id字段可以有唯一性约束，也可以没有唯一性约束。

3.创建全文索引

【示例7-9】下面在index9表中的时0字段上建立名为index9_info的全文索引。SQL代码如下：

CREATE FULLTEXT INDEX index9_info ON index9{ info);

其中，FULLTEXT用来设置索引为全文索引；表index9的存储引擎必须是MyISAM 类型;info字段必须为CHAR、VARCHAR和TEXT等类型。

4.创建单列索引

【示例7-10】下面在index10表中的address字段上建立名为index10_addr的单列索引。address字段的数据类型为VARCHAR(20),索引的数据类型为CHAR(4)。SQL代码如下：

CREATE INDEX index10_addr ON index10( address(4));

这样，查询时可以只查询address字段的前4个字符，而不需要全部查询。

5.创建多列索引

【示例7-11】下面在indexll表中的name和address字段上建立名为indexll_na的多列索引。SQL代码如下：

CREATE INDEX index11_na ON index11( name, address );

该索引创建好了以后，查询条件中必须有name字段才能使用索引。

6.创建空间索引

【示例7-12】下面在indexl2表中的line字段上建立名为indexl2_ine的多列索引。SQL代码如下：

CREATE SPATIAL INDEX index12Jine ON index12( line);

其中，SPATIAL用来设置索引为空间索引；表indeX12的存储引擎必须是MyISAM类型；line字段必须为空间数据类型，而且是非空的。

7.2.3用ALTER TABLE语句来创建索引

在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建 索引。基本形式如下：

ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名（属性名[(长度）][ASC | DESC]);

其中的参数与上面的两种方式的参数是一样的。

1.创建普通索引

【示例7-13】下面在exampleO表中的name字段上建立名为indexl3_name的索引。SQL代码如下：

ALTER TABLE exampleO ADD INDEX index13_name (name(20));

使用ALTER TABLE语句创建索引之前，先执行SHOW CREATE TABLE语句查看example0表的结构。SHOW CREATE TABLE语句执行结果如下：

mysql> show create table example0 \G;
*************************** 1. row ***************************
       Table: example0
Create Table: CREATE TABLE `example0` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  KEY `indes7_id` (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

结果显示，exampleO表上只有index7_id索引。下面执行ALTER TABLE语句创建 indexl3_name索引。ALTER TABLE语句执行结果如下：

mysql> show create table example0 \G;
*************************** 1. row ***************************
       Table: example0
Create Table: CREATE TABLE `example0` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  KEY `indes7_id` (`id`),
  KEY `index7_id` (`id`),
  KEY `index13_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

结果可以看到，name字段己经创建了一个名为indexl3_name的索引。

2.创建唯一性索引

【示例7-14】下面在index14表中的course_id字段上，建立名为index14_id的唯一性索引。SQL代码如下：

ALTER TABLE index14 ADD UNIQUE INDEX index14_id (course_id);

其中，indexl4_id为索引的名词；UNIQUE用来设置索引为唯一性索引；表indexl4 中的course_id字段可以有唯一性约束，也可以没有唯一性约束。

3.创建全文索引

【示例7-15】下面在indexl5表中的info字段上建立名为indexl5_info的全文索引。SQL代码如下：

ALTER TABLE index15 ADD FULLTEXT INDEX index15_info (info);

其中，FULLTEXT用来设置索引为全文索引；表index15的存储引擎必须是MyISAM类型；info字段必须为CHAR VARCHAR和TEXT等类型。

4.创建单列索引

【示例7-16】下面在indexl6表中的address字段上建立名为indexl6_addr的单列索引。address字段的数据类型为VARCHAR(20),索引的数据类型为CHAR(4)。SQL代码如下：

ALTER TABLE index16 ADD INDEX index16_addr( address(4));

这样，查询时可以只查询address字段的前4个字符，而不需要全部查询。

5.创建多列索引

【示例7-17】下面在indexl7表中的name和address字段上建立名为indexl7_na的多列索引。SQL代码如下：

ALTER TABLE index17 ADD INDEX index17_na( name, address);

该索引创建好了以后，査询条件中必须有name字段才能使用索引。

6.创建空间索引

【示例7-18】下面在indexl8表中的line字段上建立名为indexl8_line的多列索引。SQL代码如下：

ALTER TABLE index18 ADD SPATIAL INDEX index18_line(line);

其中，SPATIAL用来设置索引为空间索引；表indexl8的存储引擎必须是MyISAM类型；line字段必须是非空的，而11必须是空间数据类型。