聚簇索引与非聚簇索引区别学习

聚簇索引与非聚簇索引区别学习

学习文档摘自：https://www.cnblogs.com/jiawen010/p/11805241.html
总结：
InnoDB中，表数据文件是一个B+Tree组织的一个索引结构，聚簇索引就是按照表的主键构造一棵b+树，叶子节点存放整张表的数据，这个特性决定了索引组织表中的数据也是索引的一部分
一般建表都会创建一个自增的id作为聚簇索引，没有的话会自动以表中的Unique字段为，实在没有的话，mysql也会默认创建，但索引更改的代价貌似很高昂，所以兄弟们还是自己建一下比较好。
日常生活中，我们偶尔会根据需要建立索引，通常这种索引都是辅助索引，辅助索引的作用就是为了找到主键的一个二级索引，辅助索引的叶子节点上存放的是主键，先通过辅助索引找到主键，再通过主键去找到数据的过程貌似叫做回表，如果技术可以的话尽量减少回表的操作，这样会提高查询效率。（后面记得看书 todo）
InnoDB的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引，前缀索引，唯一索引，InnoDB使用的是聚簇索引，MyISam使用的是非聚簇索引

聚簇索引

优点：

1. 数据访问更快，因为聚簇索引将索引和数据在同一个B+树中所以从聚簇索引中获取数据据对是比非聚簇的快很多的
2. 聚簇索引对于逐渐的排序查找和范围查找速度是非常快的

缺点：

1. 1.插入速度严重依赖于插入顺序，按照逐渐的顺序插入是最快的插入方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定一个自增的主键id
   2. 更新逐渐的代价很高昂，因为会导致被更新的行移动，对于InnoDB表，我们一般都会定义主键不可更新
   3. 二级索引访问需要进行两次索引查找，第一次找到主键值，第二次根据主键值找到行元素。

辅助索引（非聚簇索引）

在聚簇索引上创建的索引被称为辅助索引，辅助索引 InnoDB辅助索引的叶子节点只是键值信息和聚簇索引值，会不会这个键值信息就是不回表查询的关键呢？
辅助索引的存在不会影响聚簇索引，所以一张表可以拥有多个聚簇索引
InnoDB索引实现

1. 主键索引：
   
   叶子节点包含完整的数据记录，这种索引结构称为聚集索引，因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，如果不存在非空唯一字段与主键，Mysql自动为InnoDB表生成一个隐含的字段作为主键，类型为长整型，六个字节
2. InnoDB的辅助索引
   InnoDB的所有辅助索引引用主键作为data域
   
   可以看到字段信息在data域中也存在，可以优秀的索引的建立，再不查询全部信息的情况下，完全是可以不回表操作查到想要的数据的。还有就是主键索引不应该设置的过大，因为建立其他的索引时还会有主键索引的信息，如果过大，不管怎么做，查询效率都会很低
   用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择
   MyISAM索引实现

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址

1）主键索引：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，图myisam1是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

2）辅助索引（Secondary key）

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

问题：主键索引是聚集索引还是非聚集索引？

在Innodb下主键索引是聚集索引，在Myisam下主键索引是非聚集索引

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

聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引；二级索引的叶子节点存放的是主键值或指向数据行的指针。

由于节子节点(数据页)只能按照一颗B+树排序，故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引

注： 覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。