MySQL(十五) 索引的分类_索引按功能分为哪四种

一.按照功能划分

按照功能划分的话，索引分为四种：

1.普通索引
2.唯一索引
3.主键索引
4.全文索引

1.1 普通索引

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
1
2
3
4
5
6

普通索引：普通索引就是最基础的索引，这种索引没有任何的约束作用，它存在的主要意义就是提高查询效率。
在上述表中：name字段就是一个普通索引。

1.2 唯一性索引

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
1
2
3
4
5
6

唯一性索引: 在普通索引的基础上增加了数据唯一性的约束。一个表中可以有多个。
在上述表中：name字段就是唯一性索引。

1.3 主键索引

主键索引则是在唯一性索引的基础上又增加了不为空的约束，则 NOT NULL+UNIQUE ,一张表中最多只有一个主键索引，但是一个主键索引中可以包含多个字段。

1.4 全文索引

一般很少用到。

二.按照功能划分

聚集索引

聚集索引在存储的时候，可以按照主键（不是必须，看情况）来排序存储数据，B+Tree 的叶子结点就是完整的数据行，查找的时候，找到了主键也就找到了完整的数据行。

在聚集索引里，表中数据行按索引的排序方式进行存储，对查找行很有效。只有当表包含聚集索引时，表内的数据行才会按找索引列的值在磁盘上进行物理排序和存储。每张表只能有一个聚集索引，原因很简单，因为数据行本身只能按一个顺序存储。当我们基于 InnoDB 引擎创建一张表的时候，都会创建一个聚集索引，每张表都有唯一的聚集索引：
01. 如果这张表定义了主键索引，那么这个主键索引就作为聚集索引。
02. 如果这张表没有定义主键索引，那么该表的第一个唯一非空索引作为聚集索引。
03. 如果这张表也没有唯一非空索引，那么 InnoDB 内部会生成一个隐藏的主键
作为聚集索引，这个隐藏的主键是一个 6 个字节的列，该列的值会随着数据的插入自增。
1
2
3
4
5

非聚集索引

非聚集索引我们一般也称为二级索引或者辅助索引，对于非聚集索引，数据库会有单独的存储空间来存放。

非聚集索引在查找的时候要经过两个步骤，例如执行select * from user where username=‘aaa’ （假设 username 字段是非聚集索引）。

1.先搜索 username 这一列索引的 B+Tree，这个 B+Tree 的叶子结点存储的不是完整的数据行，而是主键值。
2.拿着主键值再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。

所以如果我们在查询中用到了非聚集索引，那么就会搜索两棵 B+Tree，第一次搜索 B+Tree 拿到主键值后再去搜索聚集索引的 B+Tree，这个过程就是所谓的回表。

一张表只能有一个聚集索引，但可以有多个非聚集索引。使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。