mysql 聚簇索引表_mysql 聚簇索引和辅助索引

众所周知，mySQL 有两种常见的存储引擎。一种是 MyISAM，一种是 InnoDB。

而mysql现在默认的存储引擎是InnoDB,那么为什么它选InnoDB作为存储引擎呢?网上很多回答会告诉是因为只有InnoDB使用了聚簇索引,那么聚簇索引究竟是啥东西?为了解答这个问题,笔者写下了这篇文章,除此之外也在文中解答了其他一些问题.

主键索引和聚簇索引是啥关系?一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

没有主键时，会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引

如果以上两个都不满足那innodb自己创建一个虚拟的聚集索引

注意!用户不能在有主键的情况下指定非主键列为聚簇索引

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。比如，InnoDB的聚簇索引使用B+Tree的数据结构存储索引和数据。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引(不过，覆盖索引可以模拟多个聚簇索引的情况)。

术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。

聚簇索引的二级索引：叶子节点不会保存引用的行的物理位置，而是保存行的主键值。

聚簇索引的列一定要是递增的!

对于聚簇索引的存储引擎，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的，如果主键不是自增id，可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

辅助索引

辅助索引(secondary index，也称二级索引)，叶子节点并不包含行记录的全部数据，叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签。该书签用来告诉Innodb存储引擎哪里可以找到与索引相对应的行数据。由于Innodb存储引擎是索引组织表，因此Innodb存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页。因此一共需要6次逻辑IO访问以得到最终的一个数据页。从二级索引回到主键的过程称之为“回表”，在数据量大的情况下开销还是很大。

聚簇索引和非聚簇索引的图示 如下:

MyISAM是没有聚簇索引的,无论主键还是其他列都是用的非聚簇索引,并且它的叶子节点存储的也不是所有列数据,而是磁盘物理地址,这里不再展开说.

索引和操作系统页的关系

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

为了达到这个目的，磁盘按需读取，要求每次都会预读的长度一般为页的整数倍。而且数据库系统将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。并把B-tree中的m值设的非常大，就会让树的高度降低，有利于一次完全载入。

什么影响了B+树高度?

IO次数取决于B+数的高度h。假设当前数据表的数据量为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小。而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

引用