MySQL中的聚类索引

Question

聚集索引是否与MySQL中的实际数据分开创建和存储，如果是，为什么不能有一个以上的聚集索引。我们所需要做的就是创建另一个索引并将其存储在内存中。

Answer 1

聚集索引至少部分是对表进行物理排序和存储的方式，即磁盘上的行顺序。所以你只能有一个。但是因为它反映了行的物理布局，所以它可能比一个典型的索引更紧凑、更有性能。

更新:正如@RickJames在下面出色地指出的那样，在InnoDB (MySQL的默认引擎从5.5.5开始)中，查找通常是一个两阶段的过程。一个b树将一个次级键与一个主键相关联，第二个b树将该主键与一个数据记录的位置相关联。如果检索主键上的数据，则只需进行第二次查找。从这个意义上说，b树查找总是必要的。

此外，根据MySQL文档：

通常，聚集索引与主键同义。1

它之所以被认为是“集群”而不仅仅是主键，是因为InnoDB试图根据主键对数据记录进行排序，并为将来的记录在其数据页2中的正确位置留出空间。

因此，不仅对InnoDB主键的查询比二级索引少查找b树，而且由于磁盘上数据的物理顺序，主键b树可以大大减小。

这是合理的，即使有一种直接指向数据记录(如索引MyISAM)的二级索引的机制，它的性能也不如InnoDB的主/聚集索引。

因此，从根本上说，它是按主键(至少部分)对数据记录进行物理排序，从而阻止您从辅助索引中获得相同的性能。

Answer 2

MySQL的InnoDB为其PRIMARY KEY做了以下操作:数据记录按PK顺序排列，存储在B+Tree结构中。这允许快速的点查询和范围扫描.也就是说，树的“底部”的值包含表的所有列。

InnoDB的辅助键也在B+Tree中，但底部的值是PK列。因此，需要第二次查找才能通过辅助键获取行。

请注意，辅助键可以包含表的所有列，从而充当第二个聚集索引。缺点是，对表的任何修改都必然涉及对两个BTrees的更改。

相反，MyISAM将数据放入文件( .MYD)中，并在.MYI文件中有自己的BTree中的每个索引。每个BTree的底部是指向.MYD的指针(行号或字节偏移)。PK的实现与辅助键没有任何不同。

(注意：FULLTEXT和SPATIAL索引不在上面讨论的范围之内。)