1. 聚集索引

定义参考维基百科:

英文解释:
Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.

中文解释:
聚集索引将数据块按照特定的物理顺序排序来匹配聚簇索引的顺序,这使得所有的行数据在物理上的存储按照聚簇索引的顺序来存储。因此给定一个表,只能有一个聚簇索引(显然物理存储顺序只能有一个)。当数据的访问顺序与聚簇索引顺序是一致的时候, 聚簇索引可以提升整体的数据查询效率。

innodb与聚簇索引

在oracle中可以指定别的列作为聚簇索引,但是mysql只能主键上建造

mysql innodb只能选取主键作为聚簇索引。在InnoDB 中,数据文件本身就是索引文件,B+Tree的叶子节点上的data就是数据本身。对于聚簇索引来说 B+树叶子节点即数据页。索引的顺序与数据行物理块存储顺序保持一致。

PS:一般来说,如果碰到不规则数据插入时,聚簇索引会造成频繁的页分裂(B+树叶子节点分裂),插入速度变慢。但是在innodb中只能使用主键当聚簇索引,所以不会出现这样的问题。

下图演示的是聚集索引, 可以看到叶子节点就是物理上有序的pages

2. 非聚集索引

一种索引,该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

非聚集索引的结构如下图:可以看到叶子节点并不是物理上连续的pages,还需要再进行一次数据的路由

3. 复合索引

如果在两上以上的列上创建一个索引,则称为复合索引。

4. 索引使用心得

  1. 根据业务和查询需求配置不同类型的索引可以提升查询效率
  2. 避免过度使用索引。索引的创建是有代价的。

5. 使用聚集索引和非聚集索引的场景

参考资料:

  1. 聚集索引和非聚集索引