Mysql聚族索引与二级索引

作者：1菩提行者1

什么是聚族索引什么是二级索引他们原理有何不同这个问题一直困扰我，在听郭老师课程讲到mysql存储引擎时老师经常提到聚族索引、二级索引也让我一头雾水，似懂非懂。

这几天静下心来仔细研究，收获颇多跟大家分享一下。书本上的定义：“聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。”这句话依然不明白什么是聚族索引，简单点说：mysql的聚簇索引是指innodb引擎的特性，mysiam并没有，主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。以下是我的总结：MySQL InnoDB一定会建立聚簇索引，把实际数据行和相关的键值保存在一块，这也决定了一个表只能有一个聚簇索引，即MySQL不会一次把数据行保存在二个地方。

1)  InnoDB通常根据主键值(primary key)进行聚簇，其他索引则为二级索引，mysam引擎索主键索引以及二级索引为非聚族索引

2) 如果没有创建主键，则会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引

3) 上面二个条件都不满足，InnoDB会自己创建一个虚拟的聚集索引

下来我们详细讲讲inndb以及mysam引擎索引差别

原始数据为：

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

MYISAM是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。从MYISAM存储的物理文件我们能看出，MYISAM引擎的索引文件（.MYI）和数据文件(.MYD)是相互独立的。

而InnoDB按聚簇索引的形式存储数据，所以它的数据布局有着很大的不同。它存储数据的结构大致如下：

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

INNODB的二级索引与主键索引有很大的不同。InnoDB的二级索引的叶子包含主键值，而不是行指针(row pointers)，这减小了移动数据或者数据页面分裂时维护二级索引的开销，因为InnoDB不需要更新索引的行指针。其结构大致如下：

INNODB和MYISAM的主键索引与二级索引的对比：

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

总结

 【聚簇索引】

  聚簇索引是innoDB引擎特性，顺序结构与数据存储物理结构一致的一种索引，并且一个表的聚簇索引只能有唯一的一条；一般聚族索引为主键创建的索引，对于其他索引则为二级索引。innoDB二级索引叶子节点存放key字段+主键值。然后通过主键值再找数据。mysam主键索引与二级索引一致没有区别。

【非聚簇索引】

  辅助索引 、复合索引、前缀索引等可以统称为二级索引，非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引。