mysql聚簇索引与非聚簇索引

聚簇索引就是对磁盘上的实际数据重新组织以按照特定的一个或者多个列的值排序的算法

特点是存储数据的顺序和索引顺序一致 一般情况下主键会默认生成聚簇索引 且一张表有且只有一个聚簇索引

聚簇索引和非聚簇索引的区别是:

聚簇索引（innobe）的叶子节点就是数据节点 而非聚簇索引（myisam）的叶子节点仍然是索引文件 只是这个索引文件中包含指向对应数据块的指针

MySQL中不同的数据存储引擎对聚簇索引有不同的支持

MyISAM使用的是非聚簇索引

原始数据

存储方式

按照列值和行号来组织索引的 叶子节点中保存的实际上是指向存放数据块的指针

从物理文件中也可以看出 MyISAM的索引文件.MYI和数据文件.MYD是分开存储的 是相对独立的

对于InnoDB引擎来说，是按照聚簇索引的形式存储数据

它的每个聚簇索引的叶子节点都包含主键值、事务ID、回滚指针(用于事务和MVCC)以及余下的列。

从物理文件也可以看出 InnoDB的数据文件只有数据结构文件.frm和数据文件.idb 其中.idb中存放的是数据和索引信息 是存放在一起的

InnoDB的二级索引和主键索引也有很大的不同 二级索引存放的是主键值而不是行指针 减少了移动数据或者分裂时维护二级索引的开销，因为不需要更新索引的行指针

MyISAM和InnoDB的二级索引的对比

从图中可以看出 InnoDB二级索引的叶子节点存放的是KEY字段+主键值，因此首先通过二级索引查找到的是主键值，再根据主键值在朱建索引中查找到相应的数据文件。

而MyISAM的二级索引存放的还是列值和行号的组合 叶子节点中保存的是指向物理数据的指针，因此它的主建索引和二级索引的结构并没有任何区别，只是说主键索引的索引值是唯一且非空的，而MyISAM引擎可以不设置主键。

InnoDB引擎是必须设置主键的，需要依赖主键生成聚簇索引，因此当没有指定主键的时候，InnoDB引擎会默认寻找一个可以唯一标识每行数据的列作为主键，当这种列不存在的时候，会默认生成一个6字节整型的隐藏列作为主键

 

 

 

非聚簇索引

索引节点的叶子页面就好比一片叶子。叶子头便是索引键值。

先创建一张表：

CREATE TABLE `user` ( 
`id` INT NOT NULL ,
`name` VARCHAR NOT NULL ,
`class` VARCHAR NOT NULL);

对于MYISAM引擎，如果创建 id 和 name 为索引。对于下面查询：

select * from user where id = 1

会利用索引，先在索引树中快速检索到 id，但是要想取到id对应行数据，必须找到改行数据在硬盘中的存储位置，因此MYISAM引擎的索引 叶子页面上不仅存储了主键id 还存储着 数据存储的地址信息。如图：

像这样的索引就称为非聚簇索引。

非聚簇索引的二级索引与主键索引类似。假设我们对name添加索引，那么name的索引树叶子将是如下结构：

聚簇索引

对于 非聚簇索引 来说，每次通过索引检索到所需行号后，还需要通过叶子上的磁盘地址去磁盘内取数据（回行）消耗时间。为了优化这部分回行取数据时间，InnoDB 引擎采用了聚簇索引。 
聚簇索引，即将数据存入索引叶子页面上。对于 InnoDB 引擎来说，叶子页面不再存该行对应的地址，而是直接存储数据：

这样便避免了回行操作所带来的时间消耗。 使得 InnoDB 在某些查询上比 MyISAM 还要快！

ps. 关于查询时间，一般认为 MyISAM 牺牲了功能换取了性能，查询更快。但事实并不一定如此。多数情况下，MyISAM 确实比 InnoDB 查的快 。但是查询时间受多方面因素影响。InnoDB 查询变慢得原因是因为支持事务、回滚等等，使得 InnoDB的叶子页面实际上还包含有事务id（换句话说就是版本号） 以及回滚指针。

在二级索引方面， InnoDB 与 MyISAM 有很大区别。

InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，InnoDB 会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

假设对 InnoDB 引擎上表name字段加索引，那么name索引叶子页面则只会存储主键id：

检索时，先通过name索引树找到主索引id，再通过id在主索引树的聚簇索引叶子页面取出数据。