MySQL从入门到精通_4索引的操作

1. 为什么使用索引

数据库对象索引其实与书的目录非常相似，主要是为了提高从表中检索数据的速度。由于数据存储在数据库表中，所以索引是创建在数据库表对象上的，由表中的一个字段或多个字段生成的键组成，这些键存储在数据结构（B-数 或 哈希表）中，通过MySQL可以快速有效的查找与键值相关联的字段。根据索引的存储类型，可以将索引分为B型数索引（B TREE）和哈希索引（HASH）。

注意： InnoDB 和 MyISAM 存储引擎支持BTREE类型索引，MEMORY存储引擎支持HASH类型索引，默认为前者索引

数据库对象索引的出现，除了可以提高数据库管理系统的查找速度，而且还可以保证字段的唯一性。从而实现数据库的完整性。MySQL 支持6种索引，他们分别为 普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引

索引的创建有利也有弊，创建索引可以提高查询的速度，但过多的创建索引则会占据许多的磁盘空间。因此子啊创建索引之前，必须全很利弊。

以下情况适合创建索引：

• 经常被查询的字段，即在WHERE字句中出现的字段。
• 在分组的字段，即在GROUP BY 字句中出现的字段。
• 存在依赖关系的字表和父表之间的联合查询，即主键或外键字段
• 设置唯一完整性约束的字段

以下情况不适合创建索引：

• 在查询中很少被使用的字段
• 拥有许多重复值得字段

2.创建和查看索引

索引的操作包括创建索引、查看索引和删除索引。所谓创建索引，就是在表的一个字段或多个字段上建立索引。

2.1 创建和查看普通索引

所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。

2.1.1 创建表时创建普通索引

上述语句中，INDEX 或 KEY 参数用来指定字段为索引，“索引名”参数用来所创建索引名，“属性名1”参数用来指定索引所关联的字段名称，“长度”参数用来指定索引的长度，“ASC”参数用来指定为升序排序，“DESC”用来指定为降序排序。

2.1.2 在已经存在的表上创建普通索引

在上述语句中，CREATE INDEX 关键字用来创建索引，“索引名”参数用来指定所创建的索引名，ON 关键字用来指定所要创建索引的表名称。至于其他内容，则于“创建表时创建普通索引”的语法一致

2.1.3通过SQL语句 ALTER TABLE 创建普通索引

除了上述两种方式创建普通索引外，在MySQL数据库管理系统中创建普通索引还可以通过SQL语句 ALTER 来实现。

在上述语句中，INDEX 或 KEY 关键字用来指定创建普通索引，“索引名”参数用来指定所创建的索引名称，“属性名”参数用来指定索引所关联的字段的名称，“长度”参数用来指定索引的长度，“ASC”参数用来指定为升序排序，“DESC”参数用来指定为降序排序。

2.2创建和查看唯一索引

所谓唯一索引，就是在创建索引时，限制索引的值必须是唯一的。通过该类型的索引可以跟快速地查询某条记录，在MySQL中，根据创建索引方，可以分为自动索引和手动索引两种。

所谓自动索引，是指在数据库表里设置完整性约束时，该表会被系统自动创建索引、所谓手动索引，是指手动在表上创建索引。当设置表中的某个字段设置主键或唯一完整性约束时，系统就会自动创建关联该字段的唯一索引。

2.2.1 创建表时创建唯一索引

在上述语句中，比创建普通索引多了一个SQL关键字 UNIQUE， 其中 UNIQUE INDEX 或 UNIQUE KEY 表示创建唯一索引。

2.2.2 在已经存在的表上创建唯一索引

在MySQL数据库管理系统中，创建唯一索引除了通过SQL语句 UNIQUE INDEX来实现外，还可以通过SQL语句 CREATE UNIQUE INDEX 来实现。

在上述语句中，CREATE UNIQEU INDEX关键字用来创建唯一索引。

2.2.3通过SQL语句 ALTER TABLE 创建唯一索引

在上述语句中， UNIQUE INDEX 或 KEY 关键用来指定创建唯一索引，“索引名”参数用来指定所创建所索引名，“属性名”参数用来指定索引所关联的字段的名称，“长度”参数用来指定索引的长度，“ASC”参数用来指定为升序排序，“DESC”用来指定为降序排序

2.3 创建和查看全文索引

全文索引主要关联数据类型为CHAR、VARCHAR和TEXT的字段上，一遍能够更快加速地查询数据量较大的字符串类型的字段。

MySQL从3.23.23版本开始支持全文索引，只能在存储引擎为MyISAM的数据库表上创建全文索引。在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，则以区分大小写的搜索方式执行。

2.3.1创建表时创建全文索引

在MySQL数据库系统管理中创建全文索引通过SQL语句 FULLTEXT INDEX 来实现

在上述语句中比创建普通索引多一个SQL关键字 FULLTEXT ， 其中 FULLTEXT INDEX 或 FULLTEXT KEY 表示创建全文索引。

2.3.2在已经存在的表上创建全文索引

在MySQL数据库管理系统中创建全文索引除了通过SQL语句 FULLTEXT INDEX/KEY 来实现以外，还可以通过SQL语句 CREATE FULLTEXT INDEX 来实现。

在上述语句中， CREATE FULLTEXT INDEX 关键字表示用来创建全文索引

2.3.3通过SQL语句 ALTER TABLE 创建全文索引

除了上述两种方式来创建全文索引，在MySQL数据库管理系统中创建全文索引还可以通过SQL ALTER TABLE 来实现。

在上述语句中， FULLTEXT INDEX/KEY 关键字来指定创建全文索引，“索引名”参数用来指定所创建索引名，“属性名”参数用来指定索引所关联的字段的名称，“长度”参数用来指定索引的长度，“ASC”参数用来指定为升序排序，“DEXSC”参数用来指定为降序排序。

2.4创建和查看多列索引

所谓多列索引，是指在创建索引时，所关联的字段不是一个字段，而是多个字段，虽然可以通过所关联的字段进行查询，但是只用查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用

2.4.1创建表时创建多列索引

在MySQL数据库系统管理中创建多列索引是通过SQL语句 INDEX 来实现的。

自上述语句中创建索引时，所关联的字段至少不大于一个字段。

2.4.2在已经存在的表上创建多列索引

在MySQL数据库管理系统中穿件多列索引，除了可以在创建表时实现以外，还可以为已经存在的数据表设置多列索引。

在上述语句中比创建普通索引多关联了几个字段

2.4.3通过SQL语句 ALTER TABLE 创建多列索引

创了上述的两种方式来创建多列索引外，在MySQL数据库管理系统中创建多列索还可以通过SQL语句 ALTER 来实现。

在上述语句中，INDEX 或 KEY 关键字用来指定创建索引，用于涉及的字段为多个，所以创建为多列索引。

3删除索引

索引的操作包括创建索引、查看索引和删除索引。所谓删除索引，就是删除表中已创建的索引。之所以要删除索引，是由于这些索引会降低表的更新速度，影响数据库的性能。

3.1 删除索引的语法形式

在MySQL数据库管理系统中删除索引通过SQL语句 DROP INDEX 来实现。

在上述语句中， index_name 参数表示所要删除的索引名字，table_name 参数表示所要删除索引所在的数据表对象。