MySQL 聚簇索引

https://yq.aliyun.com/articles/142879

https://blog.csdn.net/bigtree_3721/article/details/51335479

因为存储引擎负责实现索引,因此不是所有的存储引擎都支持聚簇索引。

一些数据库服务器允许选择哪个索引作为聚簇索引,但直到本书写作之前,还没有任何一个MySQL内建的存储引擎支持这一点。InnoDb将通过主键聚集数据。

如果没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB值聚集在同一个页面中的记录。包含相邻键值的页面可能会相距很远。

聚簇索引可能对性能有帮助,但也可能导致严重的性能问题。所以需要考虑聚簇索引,尤其是将表的存储引擎从InnoDB 该成其他的引擎的时候 (返回来也一样) 。

数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中,因此从聚簇索引中获取数据通常比非聚簇索引中快。

二级索引 (非聚簇索引) 可能比想象的要更大,因为在二级索引的子节点包含了最优一个几点可能让人有些疑惑,为什么二级索引需要两次索引查找？答案在于二级索引中保存的"行指针"的实质。要记住,二级索引叶子节点保存的不是只想物理位置的指针,而是行的主键值。

InnoDB 的数据分布。因为InnoDB支持聚簇索引,索引使用非常不同的方式存储同样的数据。在InnoDB中,聚簇索引"就是"表,所以不像myISAM那样需要独立的行存储。聚簇索引的每一个叶子节点都包含了主键值、事务id,用于事务和MVCC的回滚指针。这样的策略减少了当前出现行移动或者数据页分裂是二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的存储空间,存储,换来的好处是,InnoDB在移动行时,无需更新二级索引中的这个指针。InnoDB 的非叶子节点包含了索引列和一个纸箱下级节点的指针 (下级节点可以是叶子节点,也可以是非叶子节点) 。这对聚簇索引和二级索引都使用。

聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。相比之下,聚簇索引适合排序,非聚簇索引 (也叫二级索引) 不适合用在排序的场合。

因为聚簇索引本身已经是按照物理顺序放置的,排序很快。非聚簇索引则没有按序存放,需要额外消耗资源来排序。

当你需要取出一定范围内的数据时,用聚簇索引也比用非聚簇索引好。

另外,二级索引需要两次索引查找,而不是一次才能取到数据,因为存储引擎第一次需要通过二级索引找到索引的叶子节点,从而找到数据的主键,然后在聚簇索引中用主键再次查找索引,再找到数据。

辅助索引

在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据叶,再通过数据叶中的Page Directory找到数据行。

复合索引

由多列创建的索引称为符合索引,在符合索引中的前导列必须出现在where条件中,索引才会被使用

前缀索引

当索引的字符串列很大时,创建的索引也就变得很大,为了减小索引体积,提高索引的扫描速度,就用索引的前部分字串索引,这样索引占用的空间就会大大减少,并且索引的选择性也不会降低很多。而且是对BLOB和TEXT列进行索引,或者非常长的VARCHAR列,就必须使用前缀索引,因为MySQL不允许索引它们的全部长度。

使用:

列的前缀的长度选择很重要,又要节约索引空间,又要保证前缀索引的选择性要和索引全长度选择性接近。

唯一索引

唯一索引比较好理解,就是索引值必须唯一,这样的索引选择性是最好的

主键索引

主键索引就是唯一索引,不过主键索引是在创建表时就创建了,唯一索引可以随时创建。

说明

主键和唯一索引区别

主键是主键约束+唯一索引
主键一定包含一个唯一索引,但唯一索引不是主键
唯一索引列允许空值,但主键列不允许空值
一个表只能有一个主键,但可以有多个唯一索引

Contents

MySQL 聚簇索引