MySQL聚集索引和非聚集索引

索引分为聚集索引和非聚集索引,mysql中不同的存储引擎对索引的底层实现可能会不同,这里只关注mysql的默认存储引擎InnoDB。
利用下面的命令可以查看默认的存储引擎

show variables like '%storage_engine%';

聚集索引:

索引中键值的逻辑顺序决定了表中相应行的物理顺序(索引中的数据物理存放地址和索引的顺序是一致的),可以这么理解:只要是索引是连续的,那么数据在存储介质上的存储位置也是连续的。
比方说:想要到字典上查找一个字,我们可以根据字典前面的拼音找到该字,注意拼音的排列时有顺序的。

打个比方:当我们想要找“啊”这个字,然后又想找“不”这个字,根据拼音来看“b”一定在”a“的后面。

聚集索引就像我们根据拼音的顺序查字典一样,可以大大的提高效率。在经常搜索一定范围的值时,通过索引找到第一条数据,根据物理地址连续存储的特点,然后检索相邻的数据,直到到达条件截至项。

非聚集索引

索引的逻辑顺序与磁盘上的物理存储顺序不同。非聚集索引的键值在逻辑上也是连续的,但是表中的数据在存储介质上的物理顺序是不一致的,即记录的逻辑顺序和实际存储的物理顺序没有任何联系。索引的记录节点有一个数据指针指向真正的数据存储位置。

非聚集索引就像根据偏旁部首查字典一样,字典前面的目录在逻辑上也是连续的,但是查两个偏旁在目录上挨着的字时,字典中的字却很不可能是挨着的。


下面是MySQL文档中关于索引的说明:文档说明

每个InnoDB表有一个特殊的指数称为聚集索引所在的行的数据存储。通常,聚集索引是主键的同义词。从查询,插入性能最好,和其他的数据库操作,必须了解InnoDB使用聚集索引来优化每个表最常见的查询和DML操作。
当你定义你的表的主键,InnoDB使用它作为聚集索引。为您创建的每个表定义一个主键。如果没有逻辑唯一的和非空的列或列集,添加一个新的自动增量列,它的值自动填充。
如果你不确定你的表的主键、唯一索引,MySQL定位第一所有键列不为空,InnoDB使用它作为聚集索引。
如果表没有主键或唯一索引InnoDB内部适用,生成一个隐藏的聚集索引为合成列包含行ID值gen_clust_index。行的ID,InnoDB分配在这样一个表中的行排序。行ID是一个6字节字段的单调增加,在插入新行。因此,行id命令的行在物理上是插入顺序。

总结如下:

  • 如果一个主键被定义了,那么这个主键就是作为聚集索引
  • 如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引
  • 如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,改列的值会随着数据的插入自增。

InnoDB引擎会为每张表都加一个聚集索引,而聚集索引指向的的数据又是以物理磁盘顺序来存储的,自增的主键会把数据自动向后插入,避免了插入过程中的聚集索引排序问题。如果对聚集索引进行排序,这会带来磁盘IO性能损耗是非常大的。

posted @ 2018-03-24 15:31  In_new  阅读(17761)  评论(0编辑  收藏  举报