什么是聚集索引、非聚集索引
1、聚集索引
索引的叶子节点包含了完整的表数据,那么这种索引就称为聚集索引
聚集索引是将索引列字段和行记录数据维护在了一起,它的叶子节点存储的是 索引列字段 + 完整的行记录数据,通过聚集索引能直接获取到整行数据
Innodb 的主键索引就是基于聚集索引实现的
例如数据库中有一张 user 表,id 为主键
那么基于这张表的主键 id 建立的聚集索引如下图所示
因为表中存储的数据是通过聚集索引组织在一起的,所以聚集索引必须要有,否则我们无法获取到表中的行数据,并且聚集索引还只能存在一个
既然聚集索引必须要有,可是有时候我们创建表的时候并没有设置主键,表照样创建成功,那么还有没有聚集索引呢?如果没有聚集索引 Innodb 的数据靠什么来组织维护呢?
- 如果存在主键,那么主键索引就是聚集索引
- 如果不存在主键,将会使用第一个唯一(UNIQUE)、非空的索引作为聚集索引
- 如果表中既没有主键索引,又没有合适的唯一索引,那么 Innodb 会自动维护一个 row_id(默认大小为 6B)来作为隐藏的聚集索引
为什么聚集索引只能存在一个呢?
是为了节省磁盘空间和保证数据的一致性,这个我们在 Innodb 的非聚集索引中那一块再讲
2、非聚集索引
非聚集索引是相比较于聚集索引来说,它是把索引和行数据分开维护,叶子节点并没有包含完整的数据记录(叶子节点的数据区存储的是聚集索引的 id 或 数据的磁盘地址)Mysql 非聚集索引底层的数据结构也是 b+ 树,例如 Myisam 的索引、Innodb 的辅助索引
Myisam 以 id 为主键建立的非聚集索引如下图
Innodb 以 age 建立的非聚集索引如下图
叶子节点存放的是 索引列的值 + 对应行记录的主键 id 值
例如要查找 select * from user where age = 41 ,它的查找过程是什么样的呢?
首先由于 age 是索引,并且 where 中使用了索引作为条件,我们需要从索引树的根节点(36) 开始,将该节点对应的 page 页从磁盘加载进内存,在内存中进行比较, 由于 41 > 36 ,根据根节点指针,搜寻到 B+ 树第二层的节点(36),再接着将第二层的节点(36) 所在的 page 从磁盘加载进内存,在内存中进行比较,由于 41 > 36,继续走右边节点来到第三层叶子节点,将叶子节点(36,41)从磁盘加载进内存,在内存中比较有没有索引值等于 41 的数据,结果找到了 41,并且同时 41 下面还挂着该记录行的主键 id(13),然后接着根据主键 id(13) 回到主键索引(聚集索引)上找到 id 为 13 的行记录,取出对应的数据即可
当通过非聚集索引来查询数据时,存储引擎会根据索引字段定位到最底层的叶子节点,并通过叶子节点获得指向主键索引的主键 id,然后通过主键 id 去主键索引(聚集索引)上找到一个完整的行记录.这个过程被称为 回表
为什么非聚集索引的叶子节点存储的是聚集索引的 id 值(或数据的磁盘地址值),直接跟聚集索引一样,把完整的数据放在非聚集索引的叶子节点不好吗,这样还不用回表查询,直接就能拿到结果
之所以这么做是为了数据的一致性和节省磁盘空间
1、假设一张表里面有 10 个索引,就要在这 10 个索引上分别维护一份相同的表数据,而数据都是存储在磁盘上的,那么磁盘就会存储 10 份相同的数据,对磁盘的压力大,尤其是现在的磁盘多是用 SSD 来存储的,价格较高
2、插入、更新、删除数据的时候,Mysql 就要同时维护 10 份一样的数据以保证数据的一致性,如果在维护数据的时候有一个出现了错误,那不就导致了数据不一致了吗,这也同时解释了为什么 Innodb 存储引擎的聚集索引只能有一个,因为只需要维护一份完整的数据就足够了
