Mysql-----聚集索引和辅助索引（非聚集索引）

目录

一、索引

二、聚集索引和非聚集索引

 2.1 实例讲解聚集索引

2.2  实例讲解非聚集索引

---

一、索引

数据库只做两件事情：存储数据、检索数据。而索引是在你存储的数据之外，额外保存一些路标（一般是B+树），以减少检索数据的时间。

一张表可以建立任意多个索引，每个索引可以是任意多个字段的组合。索引可能会提高查询速度（如果查询时使用了索引），但一定会减慢写入速度，因为每次写入时都需要更新索引，所以索引只应该加在经常需要搜索的列上，不要加在写多读少的列上。

 

二、聚集索引和非聚集索引

（聚集索引=聚簇索引。非聚集索引=辅助索引=二级索引=普通索引。佛了，相同的东西一堆不同的名字）

聚簇索引和非聚簇索引的区别的和各自的特点：

• 聚集索引的叶子结点就是数据结点。
• 非聚集索引的叶子结点仍然是索引结点，只不过有指向对应数据块的指针。
• 聚集索引的顺序，就是数据在硬盘上的物理顺序。一般情况下，主键就是默认的聚集索引，聚集索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式地定义一个主键来作为聚集索引。
• 一张表只允许存在一个聚集索引。因为真实数据的物理顺序只能有一种。（这种顺序就是B+树叶子结点形成的链表的顺序）

 2.1 实例讲解聚集索引

先创建一个数据表：

其中pId为主键。另有字段name和birthday。

这里我们说的是InnoDB引擎，那主键索引也是一个聚集索引，底层结构是B+树。

下面一张图是简略的图

图示很清楚，上半部分是主键形成的B+树，下半部分是磁盘上存储的真实数据。

当我们执行以下语句：

select * from user where pId = 11

那么执行流程如下：

如上图所示，从根开始，经过3次查找，就可以找到真实数据。

如果不使用索引，那就要在磁盘上，逐行扫描，直到找到数据位置。显然，使用索引速度会很快。

 

2.2  实例讲解非聚集索引

接下来给name字段加非聚集索引。

看看给name添加索引之后，结构图是什么样子的？

从上面图中，我们可以看到根据索引name新生成了一棵B+树。我们每加一个索引，就会增加表的体积， 占用磁盘存储空间。

并且这里还有一点变化，叶子节点，非聚簇索引的叶子节点并不是真实数据，它的叶子节点依然是索引节点，存放的是该索引字段的值以及对应的主键索引(聚簇索引)。

查询过程，我们执行如下语句：

 select * from user where name = 'lisi'

我们现在加了非聚集索引之后，这条sql语句怎么去查询呢？

再来一张图：

通过上图我们看到：

1. 先从非聚簇索引树开始查找
2. 找到name为lisi的叶子节点，根据lisi的主键pId，再去聚簇索引B+树中去找
3. 最后拿到pId对应的行数据。

 再看下面的这一条语句，在查询的时候在非聚簇索引上就找到了要的name值，那么就不会再去聚簇索引上查找了。效率更高。

 select name from user where name = 'lisi'

这个就是 覆盖索引 了，即 select 后面跟的字段 全部都建了索引，所以只需要在非聚集索引上找就行了，没必要再回表(回去聚集索引找)。

总结：

通过上面的例子，我们看到多加一个索引，就会多生成一颗非聚簇索引树。所以说，索引不能乱加。在做插入操作的时候，需要同时维护这几颗树的变化！如果索引太多，插入性能就会下降!