1 MySQL索引简述

索引是一种将数据库中单列或多列的值进行排序的结构。使用索引，可以大幅度提高查询的速度。

事物都有两面性。创建和维护索引需要耗费时间，并且该耗费时间与数据量成正比；索引需要占用物理空间，给数据的维护带来很多麻烦。

使用索引应该知道的事：

1、索引可以提高查询速度，同时却会降低写表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

2、建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

3、索引只是提高效率的一个因素，若MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

概念辨析

索引、主键和唯一性的区别：

索引是建立在一个或多个字段上的，创建索引后表的数据就按索引的一定规则排列。用于提高查询速度。

主键是一个表中特定记录的唯一标识，用来唯一标识一个记录(行)，在创建主键时DBMS会自动为这个主键字段建立一个唯一性索引。

索引的两种存储类型

• B树索引(BTree)
• 哈希索引(Hash)

B树为系统默认索引方法。

MySQL索引分类

普通索引：没有任何限制条件的索引，该索引可以在任何数据类型中创建。

唯一索引：使用UNIQUE参数可以设置唯一索引。创建该索引时，索引列的值必须唯一，但允许有空值。通过唯一索引，用户可以快速地定位某条记录，主键索引是一种特殊的唯一索引。

全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引耗时耗空间。

单列索引：只对应一个字段的索引。

多列索引：在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询，想使用该索引，用户必须使用这些字段中的一个字段。

空间索引：只能建立在空间数据类型上。这样可以提高系统获取空间数据类型的效率。仅可用于 MyISAM 表，索引的字段不能为空值。使用SPATIAL参数可以设置索引为空间索引。

查看表的索引

show index from 表名;

查询表的创建语句

通过查询表的创建语句可以查看表字段上索引的创建方式。

show create table 表名;

2 创建数据表时同时创建索引

创建索引有两种情况，

1. 在创建表时，也一起创建索引。
2. 在表已经存在的情况下，为表创建索引。

使用建表语句，创建索引，基本语法如下：

create table table_name(
    属性名 数据类型 [约束条件],
    属性名 数据类型 [约束条件],
    ......
    属性名 数据类型
    [UNIQUE | FULLTEXT | SPATIAL ]  INDEX|KEY [别名]( 属性名 [(长度)] [ASC | DESC])
);

参数说明：

UNIQUE：可选参数，表明索引为唯一性索引。

FULLTEXT：可选参数；表明索引为全文搜索。

SPATIAL：可选参数，表明索引为空间索引。

INDEX/KEY：参数用于指定字段索引，用户在选择时，只需要选择其中的一种即可；

别名：可选参数，其作用是给创建的索引取新名称；别名的参数如下：

属性名：指索引对应的字段名称，该字段必须被预先定义。

长度：可选参数，其指索引的长度，必须是字符串类型才可以使用。

ASC/DESC：可选参数，ASC表示升序排列，DESC参数表示降序排列。

2.1 创建普通索引

建普通索引，即不添加UNIQUE、FULLTEXT等任何参数。

例：创建一个表名为score的数据表，并指定该表的id字段上建立普通索引。

create table score(
  id int(11) auto_increment primary key not null,
  name varchar(50) not null,
  math int(5) not null,
  english int(5) not null,
  chinese int(5) not null,
  index(id)
);

2.2 创建唯一性索引

创建唯一性索引与创建一般索引的语法结构大体相同，但是在创建唯一索引的时候，需要使用UNIQUE参数进行约束。

例：创建一个表名为address的数据表，并指定该表的id字段上建立唯一索引。

create table address(
  id int(11) auto_increment primary key not null,
  name varchar(50),
  address varchar(200),
  UNIQUE INDEX address(id ASC)
);

虽然添加唯一索引可以约束字段的唯一性，但是有时候并不能提高用户查找速度，即不能实现优化查询目的。所以，读者在使用过程中需要根据实际情况来选择唯一索引。

2.3 创建全文索引

与创建普通索引和唯一索引不同，全文索引的创建只能作用在CHAR、VARCHAR、TEXT类型的字段上。创建全文索引需要使用FULLTEXT参数进行约束。

【例】创建一个名称为cards的数据表，并在该表的number字段上创建全文索引。其代码如下：

create table cards(
  id int(11) auto_increment primary key not null,
  name varchar(50),
  number bigint(11),
  info varchar(50),
  FULLTEXT KEY cards_number(number)
);

技巧：

只有MyISAM类型的数据表支持FULLTEXT全文索引，InnoDB或其他类型的数据表不支持全文索引。当用户在建立全文索引的时候，返回“ERROR 1283 (HY000): Column 'number' cannot be part of FULLTEXT index”的错误，则说明用户操作的当前数据表不支持全文索引，即不为MyISAM类型的数据表。

2.4 创建单列索引

创建单列索引，即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数，用户在建立时只需指定单列字段名，即可创建单列索引。

例：创建名称为telephone的数据表，并指定在tel字段上建立名称为tel_num的单列索引，其代码如下：

create table telephone(
  id int(11) primary key auto_increment not null,
  name varchar(50) not null,
  tel varchar(50) not null,
  index tel_num(tel(20))
);

技巧：

数据表中的字段长度为50，而创建的索引的字段长度为20，这样做的目的是为了提高查询效率，优化查询速度。

2.5 创建多列索引

与创建单列索引相仿，创建多列索引即指定表的多个字段即可实现。

例：创建名称为information的数据表，并指定name和sex为多列索引，其代码如下：

create table information(
  id int(11) auto_increment primary key not null,
  name varchar(50) not null,
  sex varchar(5) not null,
  birthday varchar(50) not null,
  INDEX info(name,sex)
);

需要注意的是，在多列索引中，只有查询条件中使用了这些字段中的第一个字段(即上面示例中的name字段)时，索引才会被使用。

技巧：触发多列索引的条件是用户必须使用索引的第一字段，如果没有用到第一字段，则索引不起任何作用，用户想要优化查询速度，可以应用该类索引形式。

2.6 创建空间索引

创建空间索引时，需要设置SPATIAL参数。同样，必须说明的是，只有MyISAM类型表支持该类型索引。而且，索引字段必须有非空约束。

例：创建一个名称为list的数据表，并创建一个名为listinfo的空间索引。其代码如下：

create table list(
  id int(11) primary key auto_increment not null,
  goods geometry not null
  SPATIAL INDEX listinfo(goods)
)engine=MyISAM;

说明：goods字段上已经建立名称为listinfo的空间索引，其中goods字段必须不能为空，且数据类型是GEOMETRY。该类型是空间数据类型。空间类型不能用其他类型代替。否则在生成空间索引时会产生错误且不能正常创建该类型索引。

技巧：空间类型除了上述示例中提到的GEOMETRY类型外，还包括如POINT、LINESTRING、POLYGON等类型。这些空间数据类型在平常的操作中很少被用到。

3 对表创建索引

在表已存在的情况下，对表的一个或几个字段创建索引。其基本的命令结构如下所示：

CREATE [UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name ON table_name(属性 [(length)] [ ASC | DESC]);

参数说明：

UNIQUE(唯一索引)、FULLTEXT(全文索引)、SPATIAL(空间索引)：为可选参数，指定索引类型。

index_name为索引名称，该参数作用是给用户创建的索引赋予新的名称；

table_name为表名，即指定创建索引的表名称。

属性参数，指定索引对应的字段名称。该字段必须已存在，若该数据表中不存在用户指定的字段，则系统会提示异常；

length为可选参数，用于指定索引长度。

ASC和DESC参数，指定数据表的排序顺序。

与建立数据表时创建索引相同，在已建立的数据表中创建索引同样包含6种索引方式。

说明：create index语句无法创建primary key 索引。

3.1 创建普通索引

例：该表中创建名称为stu_info的普通索引，在命令提示符中输入如下命令：

create INDEX stu_info ON studentinfo(sid);

3.2 创建唯一索引

在已经存在数据表中建立唯一索引的命令如下：

CREATE UNIQUE INDEX 索引名 ON 数据表名称(字段名称);

其中UNIQUE是用来设置索引唯一性的参数，该表中的字段名称既可以存在唯一性约束，也可以不存在唯一性约束。

例：下面在index1表中的 cid字段上建立名为index1_id的唯一性索引。SQL代码如下：

CREATE UNIQUE INDEX index1_id ON index1(cid);

输入上述命令后，应用SHOW CREATE TABLE语句查看该数据表的结构。其运行结果如图7.10所示。

3.3 创建全文索引

在MySQL中，为已经存在的数据表创建全文索引的命令如下：

CREATE FULLTEXT INDEX 索引名 ON 数据表名称(字段名称);

其中，FULLTEXT用来设置索引为全文索引。操作的数据表类型必须为MyISAM类型。字段类型必须为VARCHAR、CHAR、TEXT等类型。

例：下面在index2表中的 info字段上建立名为index2_info的全文索引。SQL代码如下：

CREATE FULLTEXT INDEX index2_info ON index2(info);

输入上述命令后，应用SHOW CREATE TABLE语句查看该数据表的结构。

3.4 创建单列索引

与建立数据表时创建单列索引相同，用户可以设置单列索引。其命令结构如下：

CREATE INDEX 索引名 ON 数据表名称(字段名称(长度));

设置字段名称长度，可以优化查询速度，提高查询效率。

例：下面在index3表中的 address字段上建立名为index3_addr的单列索引。Address字段的数据类型为varchar(20),索引的数据类型为char(4)。

CREATE INDEX index3_addr ON index3(address(4));

输入上述命令后，应用SHOW CREATE TABLE语句查看该数据表的结构。

3.5 创建多列索引

建立多列索引与建立单列索引类似。其主要命令结构如下：

CREATE INDEX 索引名 ON 数据表名称(字段名称1,字段名称2…);

与建立数据表时创建多列索引相同，当创建多列索引时，用户必须使用第一字段作为查询条件，否则，索引不能生效。

例：下面在index4表中的name和address字段上建立名为index4_na的多列索引。

CREATE INDEX index4_na ON index4(name,address);

输入上述命令后，应用SHOW CREATE TABLE语句查看该数据表的结构。

3.6 创建空间索引

建立空间索引，用户需要应用SPATIAL参数作为约束条件。其命令结构如下：

CREATE SPATIAL  INDEX 索引名 ON 数据表名称(字段名称);

其中，SPATIAL用来设置索引为空间索引。用户要操作的数据表类型必须为MyISAM类型。并且字段名称必须存在非空约束。否则将不能正常创建空间索引。

4 通过修改表添加索引

修改已经存在表上的索引。可以通过ALTER TABLE语句为数据表添加索引，其基本结构如下：

ALTER TABLE table_name ADD [ UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name(属性名 [(length)] [ASC | DESC]);

该参数与以上参数意义相同，不再赘述。

4.1 添加普通索引

在studentinfo 表中添加名称为timer的普通索引，在命令提示符中输入如下命令：

alter table studentinfo ADD INDEX timer (time(20));

技巧：从功能上看，修改数据表结构添加索引与在已存在数据表中建立索引所实现功能大体相同，二者均是在已经建立的数据表中添加或创建新的索引。所以，用户在使用的时候，可以根据个人需求和实际情况，选择适合的方式向数据表中添加索引。

4.2 添加唯一索引

与已存在的数据表中添加索引的过程类似，在数据表中添加唯一索引的命令结构如下所示：

ALTER TABLE 表名 ADD UNIQUE INDEX 索引名称(字段名称);

其中， ALTER语句一般是用来修改数据表结构的语句，ADD为添加索引的关键字；UNIQUE是用来设置索引唯一性的参数，该表中的字段名称既可以存在唯一性约束，也可以不存在唯一性约束。

4.3 添加全文索引

在MySQL中，为已经存在的数据表添加全文索引的命令如下：

ALTER TABLE 表名 ADD  FULLTEXT INDEX 索引名称(字段名称);

其中，ADD是添加的关键字，FULLTEXT用来设置索引为全文索引。操作的数据表类型必须为MyISAM类型。字段类型同样必须为VARCHAR、CHAR、TEXT等类型。

4.4 添加单列索引

与建立数据表时创建单列索引相同，用户可以设置单列索引。其命令结构如下：

ALTER TABLE 表名 ADD  INDEX 索引名称(字段名称(长度));

同样，用户可以设置字段名称长度，以便优化查询速度。提高执行效率。

4.5 添加多列索引

添加多列索引与建立单列索引类似。其主要命令结构如下：

ALTER TABLE 表名 ADD  INDEX 索引名称(字段名称1,字段名称2…);

使用ALTER修改数据表结构同样可以添加多列索引。与建立数据表时创建多列索引相同，当创建多列索引时，用户必须使用第一字段作为查询条件，否则，索引不能生效。

4.6 添加空间索引

添加空间索引，用户需要应用SPATIAL参数作为约束条件。其命令结构如下：

ALTER TABLE 表名 ADD  SPATIAL INDEX 索引名称(字段名称);

其中，SPATIAL用来设置索引为空间索引。用户要操作的数据表类型必须为MyISAM类型。并且字段名称必须存在非空约束。否则将不能正常创建空间索引。该类别索引并不常用，所以，对于初学者来说，这需要了解该索引类型即可。

5 删除索引

在MySQL中，创建索引后，若用户不再需要索引，则可以删除指定表的索引。

已经被建立且不常使用的索引，一方面可能会占用系统资源，另一方面也可能导致更新速度下降，这极大地影响了数据表的性能。所以在用户不需要该表的索引时，可以手动删除指定索引。

删除索引可以通过DROP语句来实现。其基本的命令如下：

DROP INDEX 索引名称 ON 表名;

例：删除名称为address的数据表中存在唯一索引“address”。在命令提示符中继续输入如下命令：

DROP INDEX id ON address