MySQL(五)--聚簇索引与非聚簇索引_聚簇索引的数据存放在叶子节点中马

MySQL(五)–聚簇索引与非聚簇索引

聚簇索引：

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。当表有聚簇索引时，它的数据行实际上存放在索引的叶子节点中。

聚簇表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以，一个表只能有一个聚簇索引。

因为是存储引擎负责实现索引，，因此不是所有的存储引擎都支持聚簇索引。

平时习惯逛图书馆的童鞋可能比较清楚，如果你要去图书馆借一本书，最开始是去电脑里面查书名然后根据书名来定位藏书在那个区，哪个书柜，哪一行，第多少本。。。清晰明确，一目了然，因为藏书的结构与图书室的位置，书架的顺序，书本的摆放顺序与书籍的编号都是从大到小一致的顺序摆放的，所以很容易找到。比如，你的目标藏书在C区2柜3排5仓，那么你走到B区你就很快知道前面就快到了C区了，你直接奔着2柜区就能找到了。 这就是雷同于聚簇索引的功效了，聚簇索引，实际存储的顺序结构与数据存储的物理机构是一致的，所以通常来说物理顺序结构只有一种，那么一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引，当然有人说我不想拿主键作为聚簇索引，我需要用其他字段作为索引，当然这也是可以的，这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引，然后再设置主键，这样就木有问题啦。

总而言之，聚簇索引是顺序结构与数据存储物理结构一致的一种索引，并且一个表的聚簇索引只能有唯一的一条；

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。

聚簇索引的数据和主键索引存储在一起。

聚簇索引的数据是根据主键的顺序保存。因此**适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度**。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂（BTree插入时的一个操作），严重影响性能。

在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。

聚簇索引将数据存储与索引放到了一块儿，找到索引也就找到了数据。

以上图为聚簇索引的数据分布

从图中可以看出，叶子页包含了行的全部数据，但是节点页只包含了索引项。

非聚簇索引：

将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，MyISAM通过key-buffer把索引先缓存到内存中，当需要访问数据时（通常索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这就是为什么索引不再key-buffer命中时，速度慢的原因。

非聚簇索引是通过MyISAM存储引擎来实现的。

非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。

非聚簇索引的数据表和索引表是分开存储的。非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。

聚簇索引与非聚簇索引的对比图：

首先介绍一下基础的概念。在索引的分类中，我们可以按照索引的键是否为主键来分为**“主索引”和“辅助索引”**，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

为什么需要用到辅助索引？因为前面我们介绍了，查询语句如果想要使用索引，是需要满足最左匹配原则的。有时候我们的查询并不会使用到主键列，所以需要在其它列建立索引，即辅助索引。

为了更好的理解聚簇索引与非聚簇索引，举例如下：

（其中id为主索引，Name为辅助索引）


关于以上例子，做几点如下说明：

1，由于行数据和叶子节点存储在一起，这样主键和行数据是在一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2，辅助索引使用主键作为“指针”而不是使用地址值作为指针的好处是：减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当做指针会让辅助索引占用更多的空间，换来的好处是：==InnoDB在移动时无须更新辅助索引中的这个“指针”。==也就是说，行的位置会随着数据库里数据的修改而发生改变使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引都不受影响。

InnoDB与MyISAM：

MyISAM：

MyISAM按照数据插入的顺序存储在磁盘上，所以MyISAM可以从表的开头跳过所需的字节找到需要的行。

MyISAM引擎的数据存储方式如图：

可以看出，MyISAM中的主键索引和其他索引在结构上是没有什么不同的。

InnoDB：

说起聚簇索引，我们自然而然的就想起了InnoDB这个存储引擎，InnoDB这个存储引擎是实现了聚簇索引。

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。

注：聚簇索引中的每个叶子节点包含主键值、事务ID、回滚指针(rollback pointer用于事务和MVCC）和余下的列(如col2)。

该图显示了整个表，而不是只有索引。因为在InnoDB中，聚簇索引就是表，所以不像MyISAM那样需要独立的行存储。

还有一点和MyISAM不同的是：InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略就减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当做指针会让二级索引占用更多的空间，换来的好处是：InnoDB在移动时无须更新二级索引中的这个“指针”。

聚簇索引中，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这就意味着：二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找对应的其他信息（比如事务ID，回滚指针等等。。）。

InnoDB和MyISAM的主键索引与二级索引的对比：

InnoDB的二级索引的叶子节点存放的是key字段+主键值。因此，通过二级索引查询首先查到的是主键值，然后InnoDB在根据查到的主键值通过主键索引找到相应的数据块儿。而MyISAM的二级索引叶子节点存放的还是列值与行号的组合，叶子节点中保存的是数据的物理地址。所以可以看出MyISAM主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做primary的唯一，非空的索引，且MyISAM引擎可以不设主键。

聚簇的优点：

• 可以把相关的数据保存在一起
• 数据访问更快（聚簇索引将索引和数据保存在同一个B-Tree上，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快）。也就是找到了索引就找到了数据。
• 使用覆盖索引扫描的查询可以直接使用页节点的主键值。

聚簇的缺点：

A：聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，聚集索引也没有什么优势了

B：插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到innodb表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表

C：更新聚集索引列的代价很高，因为会强制innodb将每个被更新的行移动到新的位置

D：基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题，当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间

E：聚集索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

F：二级索引可能比想象的更大，因为在二级索引的叶子节点包含了引用行的主键列。

G：二级索引访问需要两次索引查找，而不是一次

感谢并参考：

https://www.javazhiyin.com/42676.html

https://blog.csdn.net/tfstone/article/details/81197921?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase