聚集索引/非聚集索引

https://blog.csdn.net/u014496330/article/details/53056271

 

B+树索引可以分为聚集索引和辅助索引。聚簇索引是按照数据存放的逻辑地址为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

 

聚集索引 

聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。 

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。 

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。 

辅助索引中，叶结点的data域存放的是对应记录的主键的key。 

对于建立辅助索引的表需要先根据辅助索引找到相应的主键，再根据主键在聚集索引中找到相应的记录集。

 

非聚集索引 

非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。 

主键索引中，叶节点的data域存放的是数据记录的地址，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。（MYISAM采用此种索引方式）。

 

区别

 

聚集索引表里数据物理存储顺序和主键索引的顺序一致，所以如果新增数据是离散的，会导致数据块趋于离散，而不是趋于顺序。而非聚集索引表数据写入的顺序是按写入时间顺序存储的。

 

聚簇索引索引的叶节点就是数据节点；而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

 

1、定义 + 区别

聚集索引将一张表中的多行数据按排好的顺序进行物理存储，一个表只能建一个；
非聚集索引是指向表中的数据并独立于表的一种物理存储结构，一个表可以建多个；
可见，建立非聚集索引需要冗余的表空间，好处就是可以建立多个，但速度要比聚集索引慢。

 

 

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，聚集索引表记录的排列顺序与索引的排列顺序一致，优点是查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后。聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。建议使用聚集索引的场合为：
　　a.此列包含有限数目的不同值；
　　b.查询的结果返回一个区间的值；
　　c.查询的结果返回某值相同的大量结果集。
    1、您最频繁使用的、用以缩小查询范围的字段上；
    2、您最频繁使用的、需要排序的字段上。

 

非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。建议使用非聚集索引的场合为：
　　a.此列包含了大量数目不同的值；
　　b.查询的结束返回的是少量的结果集；
　　c.order   by   子句中使用了该列。

 

  

2、聚集索引：
  该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

如果用 新华字典 作例子来一个例子的话。
  [拼音]就可以看作是聚集索引
    例如 吖、阿、啊 在字典的最前面。
    左、作、坐 在字典的最后面。
    拼音[逻辑顺序]很接近，在字典中页数的位置[物理顺序]也很接近。

适用场合：
  含有大量非重复值的列
  使用BETWEEN,>,>=,<或<=返回一个范围值的列
  被连续访问的列
  返回大型结果集的查询
  经常被使用连接或GROUP BY子句的查询访问的列



非聚集索引：
  非聚集索引与聚集索引一样有 B 树结构，但是有两个重大差别：
  数据行不按非聚集索引键的顺序排序和存储。
  非聚集索引的叶层不包含数据页。
  相反，叶节点包含索引行。每个索引行包含非聚集键值以及一个或多个行定位器，
  这些行定位器指向有该键值的数据行（如果索引不唯一，则可能是多行）。

如果用 新华字典 作例子来一个例子的话。
  [笔画]就可以看作是非聚集索引
    例如 化 仇 仃 仅 仂 这几个字，都是 单人旁，笔画数相同的。
    笔画[逻辑顺序]很接近，在字典中页数的位置[物理顺序]则在不同的位置上。

适用场合：
  含有大量非重复值的列
  非连续访问的列
  返回小型结果集的查询