聚集索引 堆表

聚集与非聚集索引的区别

区别表	聚集索引	非聚集索引
个数	一个表只能有一个， 因为目录只能按照一种方法进行排序	一个表可以存在多个
连续性	逻辑，物理上连续	逻辑连续，物理不连续
对数据存储的影响	数据物理存储顺序和主键索引的顺序一致 所以如果新增数据是离散的，会导致数据块趋于离散	只是对数据列创建相应的索引， 不影响整个表的物理存储顺序.
对比二叉树	索引的叶节点就是数据节点	叶节点仍然是索引节点， 只不过有一个指针指向对应的数据块
效率上

 

 

堆表 按写入时间顺序写入，存储快随机分布；读取按命中率计算，是随机读取，开销大;适合写多读少

 

聚集索引 数据是按照索引来存储的，即索引决定数据的物理位置；每增加一条记录，更改索引，然后按照索引存储；一表一个,适合读多，写少

非聚集索引 对已有的数据创建索引表，用于检索数据，不影响整个表的物理存储顺序；一表多个,适合读多，写少

 

堆组织表(HOT)  PostgreSQL myisam(没有(聚集)索引的概念) oracle

数据写入的顺序 是按写入时间顺序写入的，存储块是随机分布的，主要是由数据库内部块的空闲情况决定；

读取是随机的，按照命中率计算，全表扫表时不见得先插入的数据先查到，无法保证被顺序读取，开销大;

效率上：插入快，读取慢(开销大).

 

索引组织表(IOT) --Innodb  ；oracle(没有聚集非聚集的区分),sql server

数据存储是把表按照索引的方式存储的，即索引决定数据的物理位置；

数据是有序的，数据的位置是预先定好的，与插入的顺序没有关系。

每增加一条记录，先在索引表中找到该记录的预留位置 添加索引(指向)，然后按照索引存储；

显示时都会按照主键的顺序。

效率上：插入慢，查询快（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入）

 

上面说过，InnoDB是聚集索引组织表，它的聚集索引选择规则是这样的：

  1).首先选择显式定义的主键索引做为聚集索引;

  2).如果没有，则选择第一个不允许NULL的唯一索引;

  3).还是没有的话，就采用InnoDB引擎内置的ROWID作为聚集索引;

  所以，对于 聚集索引（单聚集索引表） 来说，你创建主键的时候，自动就创建了主键的聚集索引。

 

 

  

微软的SQL SERVER提供了两种索引：

聚集(聚类，簇集)索引（clustered index）

主键(聚集索引) →→ 行内容

 

创建方式：create clustered index pk_test on test (a)  -- sql server中能建立 mysql，oracle不行

 

检索方式  就是类似新华字典中的 拼音检索：

字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。

定义：我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

作用：对 where 中的between ..and   <  > 的性能 有很大的提升作用

 

 

 

非聚集索引（nonclustered index）

非聚集索引 →→ 主键(聚集索引) →→ 行内容

非聚集索引 →→ 主键(聚集索引)

 

创建方式： create index ix_a on test (a) 

 

检索方式 就是类似新华字典中的 部首检索：

在部首检索表中，同一偏旁的两个字，只是逻辑上的上下关系，在正文部分中并不连续(一个第100页，

另一个第30页)。它的查找需要两个过程，先找到目录中的页码，然后该页码获取数据。

定义：我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

非聚集索引表中 不会保存行的物理位置,而是保存主键的值,通过"二级索引"获取数据。

二级索引： 非聚集索引 →→ 主键(聚集索引) →→ 行内容

就是先在非聚集索引表中，查找到主键；在根据主键找到行。

回表(二次查询)

回表的根本原因：索引其实也是个表，不过是存放了索引列的列值和rowid的表，

不回表：如果select查询的字段只有索引列，那么就直接可以从索引表中获取，

回表：如果select子句中有其他的非索引列，索引就需要到原表中找到相应的列的信息，这就叫回表。