Mysql数据库基础06-索引

1. 简介

• 索引的作用：提高查询效率。

• 没有索引，查询会慢的原因：因为要进行全表扫描，相当于遍历所有数据记录后，才返回查询结果。

• 创建索引后，查询变快的原因：形成一个索引的数据结构，比如二叉树、b树、b+树，利用数据结构提高查找效率。

• 创建索引的代价：（1）索引会占用内存资源，牺牲一些内存空间（2）降低dml（update、insert、delete）语句的效率。

2.索引的类型

索引主要有下面几种类型：

• 主键索引（primary key）
• 唯一索引（unique）
• 普通索引（index）
• 全文索引（fulltext）

（1）主键索引：对某个列，创建主键约束（primary key）时，自动将该列创建为主键索引（primary key）。即，该列既是主键也是索引，不能重复不能为空。

（2）唯一索引：类似于主键索引，当对某个列，创建唯一约束（unique）时，自动将该列创建为唯一索引（unique）。即，该列不能重复、是唯一的，同时也是唯一索引。

（3）普通索引：实际开发中，用得最多。很多时候，我们查找的列，往往会有重复数据，此时我们既想查找速度快，又想兼容重复数据的存在。比如，一个表中的name这一列，我们按名字搜索，名字是允许重名存在的，所以这时可能就要考虑普通索引了。

（4）全文索引：适用于myisam存储引擎。一般开发中，不适用mysql自带的全文索引，而是使用Solr和ElasticSearch。

2. 索引的创建

2.1 创建唯一索引

格式如下：

CREATE UNIQUE INDEX 索引名 ON `表名` (`列名`);

eg，在 student 这个表的 id 列上添加一个唯一索引 id_index 。

CREATE UNIQUE INDEX id_index ON `student` (`id`);

2.2 创建普通索引

• 第1种方式：

CREATE INDEX 索引名 ON `表名` (`列名`);

eg，在 student 这个表的 name 列上添加一个索引 name_index 。

CREATE INDEX name_index ON `student` (`name`);

• 第2种方式：

ALTER TABLE `表名` ADD INDEX 索引名 (`列名`);

eg，在 student 这个表的 class 列上添加一个索引 class_index 。

ALTER TABLE `student` ADD INDEX class_index (`class`);

2.3 创建主键索引

• 第1种方式：
  在创建表的时候，直接指定某列为 primary key，创建主键索引的同时也创建了主键约束。

• 第2种方式：

ALTER TABLE `表名` ADD PRIMARY KEY (`列名`);

eg，在 student 这个表的 global_no 列上添加一个主键索引 。

ALTER TABLE `student` ADD PRIMARY KEY (`global_no`);

3. 索引的删除

（1）删除普通索引

格式如下：

DROP INDEX 索引名 ON `表名`;

eg，删除 student 这个表的索引 name_index 。

DROP INDEX name_index ON `student`;

（2）删除主键索引
格式如下：

ALTER TABLE `表名` DROP PRIMARY KEY;

eg，删除 student 这个表的主键索引 。

ALTER TABLE `student` DROP PRIMARY KEY;

4. 索引的查看

• 第1种查看方式：

SHOW INDEX FROM `表名`;

• 第2种查看方式：

SHOW INDEXES FROM `表名`;

• 第3种查看方式：

SHOW KEYS FROM `表名`;

• 第4种查看方式：

DESC `表名`;

5. 索引的创建规则

（1）较为频繁作为查询条件的字段，应该创建索引。

（2）唯一性太差的字段，不适合单独创建索引。

（3）更新非常频繁的字段不适合创建索引。

（4）不会出现在 where 子句中的字段（即：不会作为查询条件的字段），不该创建索引。