InnoDB引擎表的主键选型

MySQL采用开放可插入式存储引擎架构,提供类似电源插线板的功能,其后接入的存储引擎就类似电器设备,而我们大家常用的存储以MyISAM和InnoDB为主,早期大家主要使用MyISAM引擎支持业务,随MySQL支持业务范围越来越广,存储的数据对企业越来越重要,尤其PC服务器支持的最大内存越来越大,内存的价格也越来越便宜,逐渐采用InnoDB引擎为主.二种风格迥异的存储引擎,各自内部存储算法和数据操纵实现等都竞相不同,另外InnoDB引擎与其他商业数据库产品存储引擎也不太相同,为此我们必须根据使用的存储引擎特点,设计合理的数据存储结构和数据操纵方式。本文将围绕InnoDB存储引擎的主键设计而展开,告诉大家怎样设计表的主键才是合理的做法。

讨论InnoDB引擎表的主键选型的要求之前,我们大家先简单温习下InnoDB引擎表的元数据和索引数据存储结构特点。

InnoDB引擎表的数据和索引都是存储在同一个文件中,InnoDB引擎的页大小默认为16K,且页空间使用率为15/16,为此每页能存储的数据量是有限的。主键和数据的关系是数据存储在簇索引的叶子节点中,接下来我们看下主键和数据的组织结构关系,如图1-1:

1.png

对于非簇索引,又是如何存储的呢?通过翻译官方文章,得知:

每个非簇索引的叶子节点存储的数据都包含簇索引的值,然后通过簇索引的值,可以查找到对应的元数据,我们继续看一下非簇索引的存储结构,以及与簇索引之间的关系,如图1-2:

2.png

紧接着,我们来理清楚InnoDB引擎表的簇索引为哪三类:

1.  主键也即是我们文章说的簇索引;

2.  若表中无主键,但是存在唯一索引,且字段定义为非空,则作为簇索引;

3.  表无主键,也无非空唯一索引,则内部默认隐含性创建一个长度为6个字节的字段作为簇索引字段;

mysql中每个表都有一个聚簇索引(clustered index ),除此之外的表上的每个非聚簇索引都是二级索引,又叫辅助索引(secondary indexes)。

通过上述探讨和分析,弄清楚了数据存储页的空间值和利用率、簇索引和非簇索引的存储结构,以及簇索引和元数据、簇索引和非簇索引的存储关系,以及簇索引为哪三类,那么我们建议大家一定要为InnoDB引擎表创建一个主键,没必要搞一个唯一性索引且字段定义属性非空,而不创建主键的表结构,我们再分析优秀主键具备的素质:值域范围够用且存储长度越