创建与使用索引

---

一、索引的概念及优点

1.1 概念

  索引是根据数据表中的一个字段或若干个字段按照一定顺序建立的与源表记录行之间的对应关系表。一个字段上的索引包含了该字段的所有值，和字段值形成了一一对应的关系。在字段上创建了索引之后，查找数据时可以直接根据该字段上的索引查找对应记录行的位置，从而快速地找到数据。

  索引是一种重要的数据对象，能够提供数据的查询效率，使用索引还可以确保列的唯一性，从而保证数据的完整性。

  在MySQL数据库中，可以在数据表中建立一个或多个索引，以提供多种存取路径，快速定位数据的存储位置。

1.2 索引的类型

（1）普通索引

  普通索引（Index）是最基本的索引类型，可以加快对数据的访问，该类索引没有唯一性限制，即索引字段允许存在重复值。创建普通索引的关键字是Index。

（2）唯一索引

  唯一（Unique）索引的字段值要求唯一，不能出现重复值，创建唯一索引的关键字是Unique。

（3）主键索引

  主键索引是专门为主键字段创建的索引，也属于唯一索引的一种，每个数据表只能有一个主键，创建主键索引时使用“Primary Key”关键字。

（4）全文索引

  MySQL支持全文（Fulltext）索引，全文索引只能在Varchar或Text类型的字段上创建，并且只能在MyISAM表中创建。它可以通过Create Table命令创建，也可以通过Alter Table或Create Index命令创建。

  由于索引是作用在字段上的，因此，索引可以由单个字段组成，也可以由多个字段组成。单个字段组成的索引称为单字段索引，多个字段组成的索引称为组合索引。

二、创建索引的语法格式

2.1 创建索引的方法

（1）创建数据表时创建索引

创建数据表时可以直接创建索引，这种方式最方便，其语法格式如下：

Create Table <表名>(
	<字段名> <数据类型> [<完整性约束条件>],
	...
	[Unique|Fulltext|Spatial]
	Index | Key [<索引名>] (<字段名>[<长度n>][Asc|Desc])
);

-- 例如，在创建职工表
Create Table 职工(
	职工编号 Varchar(20) Primary Key Not Null,
	姓名 Varchar(20) Unique Not Null,
	性别 Char(1) Null,
	部门名称 Varchar(20) Null
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

（2）在已有数据表中创建索引

  在已经存在的数据表中，可以直接为数据表中的一个或几个字段创建索引，其语法格式如下：

Create [Unique|Fulltext|Spatial] Index<索引名>
	On <表名> (<字段名>[<长度n>][Asc|Desc]);
1
2

（3）使用Alter Table语句创建索引

  在已经存在的数据表中，可能使用“Alter Table”语句直接在数据表中的一个或几个字段上创建索引，其语法格式如下：

Alter Table<表名> Add [Unique|Fulltext|Spatial]
		Index <索引名> (<字段名>[<长度n>][Asc|Desc]);

-- 例如，假如职工表中没有主键，那么就使用该语句来创建主键
Alter Table 职工 Add Primary Key(职工编号(20));
1
2
3
4
5

说明如下：

1. 索引类型：Unique表示创建的是唯一索引，Fulltext表示创建的是全文索引，Spatial表示创建的是空间索引；
2. 索引名必须符合MySQL的标识符命名规范，一个数据表中的索引名称必须是唯一的；
3. 字段名表示创建索引的字段，长度表示使用字段的前n个字符创建索引，这可使索引文件大大减小，从而节省磁盘空间。Text和Blob字段必须使用前缀索引；
4. Asc表示索引按升序排列，默认为Asc。Desc表示索引按降序排列；
5. 可以在一个索引的定义上包含多个字段，这些字段中间使用半角逗号“,”分隔，但它们属于同一个数据表。

2.2 查看索引的命令

  索引创建完成后，我们可以使用SQL语句查看已经存在的索引，查看索引语句的语法格式如下：

Show Index From <表名>;

-- 例如，查看职工表中的索引
Show Index From 职工;
1
2
3
4

2.3 删除索引的方法

删除索引可以使用Drop语句，也可以使用Alter语句。

（1）使用Drop语句删除

使用Drop语句删除索引的语法格式如下：

Drop Index <索引名> On <表名>;
1

（2）使用Alter语句删除

使用Alter语句删除索引的语法格式如下：

Alter Table <表名> Drop Index <索引名>;
1

删除主键索引语句的语法格式如下：

Alter Table <表名> Drop Primary Key;
1

  由于一张数据表中只有一个主键，所以可以利用以上语句直接删除主键，而没有必要写明主键名称。