mysql 不同引擎索引异同详解 - 点滴记忆*记忆点滴
收藏本站

mysql 不同引擎索引异同详解


聚簇索引(Clustered Indexes)

聚簇索引保证关键字的值相近的元组存储的物理位置也相同(所以字符串类型不宜建立聚簇索 引,特别是随机字符串,会使得系统进行大量的移动操作),且一个表只能有一个聚簇索引。因为由存储引擎实现索引,所以,并不是所有的引擎都支持聚簇索引。 目前,只有solidDB和InnoDB支持。
聚簇索引的结构大致如下:

 

 注: 叶子页面包含完整的元组,而内节点页面仅包含索引的列(索引的列为整型)。一些DBMS允许用户指定聚簇索引,但是MySQL的存储引擎到目前为止都不支 持。InnoDB对主键建立聚簇索引。如果你不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引,InnoDB会定义 一个隐藏的主键,然后对其建立聚簇索引。一般来说,DBMS都会以聚簇索引的形式来存储实际的数据,它是其它二级索引的基础。

3.1.1、InnoDB和MyISAM的数据布局的比较
为了更加理解聚簇索引和非聚簇索引,或者primary索引和second索引(MyISAM不支持聚簇索引),来比较一下InnoDB和MyISAM的数据布局,对于如下表:


CREATE TABLE layout_test (

   col1 int NOT NULL,

   col2 int NOT NULL,

   PRIMARY KEY(col1),

   KEY(col2)

);

 假设主键的值位于1---10,000之间,且按随机顺序插入,然后用OPTIMIZE TABLE进行优化。col2随机赋予1---100之间的值,所以会存在许多重复的值。
(1)    MyISAM的数据布局
其布局十分简单,MyISAM按照插入的顺序在磁盘上存储数据,如下:

 注:左边为行号(row number),从0开始。因为元组的大小固定,所以MyISAM可以很容易的从表的开始位置找到某一字节的位置。
据些建立的primary key的索引结构大致如下:

 注:MyISAM不支持聚簇索引,索引中每一个叶子节点仅仅包含行号(row number),且叶子节点按照col1的顺序存储。
来看看col2的索引结构:

 实际上,在MyISAM中,primary key和其它索引没有什么区别。Primary key仅仅只是一个叫做PRIMARY的唯一,非空的索引而已。

(2)    InnoDB的数据布局
InnoDB按聚簇索引的形式存储数据,所以它的数据布局有着很大的不同。它存储表的结构大致如下:

 注:聚簇索引中的每个叶子节点包含primary key的值,事务ID和回滚指针(rollback pointer)——用于事务和MVCC,和余下的列(如col2)。

相 对于MyISAM,二级索引与聚簇索引有很大的不同。InnoDB的二级索引的叶子包含primary key的值,而不是行指针(row pointers),这减小了移动数据或者数据页面分裂时维护二级索引的开销,因为InnoDB不需要更新索引的行指针。其结构大致如下:

 聚簇索引和非聚簇索引表的对比:

 


    留下足迹