聚集索引、辅助索引简要解析

最新推荐文章于 2024-08-04 17:50:08 发布

丶此去经年

最新推荐文章于 2024-08-04 17:50:08 发布

阅读量876

点赞数 5

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/rnhhhh/article/details/115256890

版权

聚集索引、辅助索引简要解析

B+树简要介绍

B+树是B树的一种变形，是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。其叶子结点用于存放所有的数据，叶子结点以上各层作为索引使用。所有的数据按照键值的大小顺序存放在同一层的叶子节点上，用各叶子节点的指针进行连接。

B+树结构

B+树的插入使用了拆分页(split)、旋转(Rotation)等操作，在尽量减少磁盘操作的前提下保持B+树的平衡。

B+树的删除使用填充因子(fill factor)来控制，同样为了保持数据的有序和树的平衡。

这些操作较为复杂，此处不详细介绍。

聚集索引

InnoDB存储引擎表是索引组织表，表中数据按照主键顺序存放。聚集索引是指数据库表行中数据的物理顺序与索引的逻辑顺序相同，于是就按照每张表的主键构造一棵B+树，在叶子节点存放所有的行记录。并且这些数据也是索引的一部分。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。

叶子节点称为数据页，存放的是完整的行记录；非叶子节点，即非数据页，是索引页，存放的仅仅是键值以及只想数据页的指针（偏移量），并没有完整的行记录。

聚集索引

聚集索引确定了表中数据的物理顺序。页、记录，都是通过双向链表连接的。如果真的强行按照顺序存放，如数组，将会使维护成本变得很高。所以这里说的物理顺序，指的是通过指针一个一个连接起来，并不是严格的物理上的连续。

多数情况下，查询优化器会采用聚集索引。第一个好处，因为它能够在叶子节点上直接找到数据。而在辅助索引中，叶子节点上并不会存放全部数据，只是一个“指针”，还需要再去聚集索引中查找，更耗费时间。

还有一个好处是，对于聚集索引的列的排序查找和范围查找速度很快。

排序查找：例如

SELECT * FROM t ORDER BY id LIMIT 10;

虽然使用了ORDER BY 进行了排序，实际上并不会进行filesort操作，而是直接通过双向链表找到最后一个数据页，取出10条记录。

范围查询：例如

SELECT * FROM t WHERE id >10 AND id<10000;

这条语句执行中，通过叶子节点的上层中间节点即可得到页的范围，然后直接读取数据页就行了。对这条语句执行EXPLAIN可以在rows列中得到这个查询结果的预估返回行数。

默认情况下，会在主键上建立聚集索引。但在实际开发中，对查找较为频繁的字段，尤其是范围查找，建立聚集索引会有更好的效果。

辅助索引

聚集索引指向的是实际内容本身的顺序，而辅助索引是人为的添加上去的特定顺序的一些标记，通过这些标记就可以沿着聚集索引找到实际内容。

在辅助索引（即非聚集索引）中，叶子节点存放的是键值和书签（告诉InnoDB存储引擎对应行数据的位置，即聚集索引键），并没有行记录的完整数据。

辅助索引是相对聚集索引的一个较为独立的部分，不会对聚集索引的组织有影响，所以一张表可以有多个辅助索引。

就如上文中提到的，在使用辅助索引来查找数据时，InnoDB存储引擎会遍历辅助索引，在叶子节点的书签上得到指向聚集索引的键，再用聚集索引找到最终的数据行。举个例子，如果一个辅助索引树高度为3，聚集索引树高度为3，那么需要在辅助索引树中找3次，再在聚集索引树中找3次，共计6次逻辑IO访问来得到最终的数据行。

辅助索引与聚集索引关系