InnoDB引擎表的主键选型

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

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

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

图 1-1

    对于非簇索引，又是如何存储的呢？通过翻译官方文章，得知：每个非簇索引的叶子节点存储的数据都包含簇索引的值，然后通过簇索引的值，可以查找到对应的元数据，我们继续看一下非簇索引的存储结构，以及与簇索引之间的关系，如图1-2：

图 1-2

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

l  主键也即是我们文章说的簇索引；

l  若表中无主键，但是存在唯一索引，且字段定义为非空，则作为簇索引；

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

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

l  主键的值域够用且长度越短，将产生三个方面的优势

(1).     相同数据行数的表，数据容量越小，占用磁盘空间越少，为此可以节约物理或逻辑IO和减少内存占用；

(2).     普通索引的长度也将更短，可以减少通过普通索引搜索数据的物理或逻辑IO；

(3).     减少索引数据存储的页块裂变，从而提高页的利用率，以及减少物理IO；

l  主键的唯一性

(1).     主键的值要求必须唯一，且非空；

(2).     主键值每次插入或修改，都必须判断是否有相同值，为此减少索引值需要比对的长度，可以提高性能，或者间接地让其转换成比对数值大小的方式，提高其比对判断的效率；

l  主键值插入的有序性，将产生二个方面的优势

(1).     主键存储的块与块之间是有序的，然后块内有也是有序的，主键值的有序插入，可以减少块内的排序，节约磁盘物理IO；

(2).     主键值的有序性，可以提高数据舒顺序取速度，提高服务器的吞吐量，也可以节约物理IO；

上述讨论的数据存储结构及关系，以及主键字段要求的素质等理论知识之外，我们还需要考虑实际生产环境的业务、架构等综合因素，我们实际生产环境可能会使用四类属性作为主键：

(1).     自增序列；

(2).     UUID()函数生成的随机值；

(3).     用户注册的唯一性帐号名称，字符串类型，一般长度为：40个字符；

(4).     基于一套机制生成类似自增的值，比如序列生成器；

那么我们接下来，再分析下这四类属性各自作为表主键的优缺点：

(1).     自增序列：从小到大 或从大到小的顺序模式增加新值；数据类型也利于进行主键值比较；存储空间占用也相对最小，一般设置为：4个字节的INT类型或 8个字节的BIGINT类型；若是想进行数据水平拆分的话，也可以借助设置mysqld实例的2个参数：auto_increment_increment 和 auto_increment_offset；另外，唯一缺点就是自增序列是一个表级别的全局锁，在5.0系列大规模并发写的时候，因锁释放机制的问题容易出现瓶颈，但是5.1系列做了改进，基本上不存在此问题；

(2).     UUID()函数：值为随机性+固定部分，其值产生是无序的，且同一台服务器上产生的值相同部分为77.8%；产生的值字符个数为36，按utf8编码计算，占用的存储空间为36个字节；对于数据水平拆分支持，无需特殊设置；

(3).     使用用户注册的帐号名称，字符串类型，其值的产生依赖用户输入，为此数据基本上为无序增加，字符串的长度也是不定的，只能通过前段技术控制最短最大长度值的限制，对水平拆分支持，无需做特殊设置；

(4).     序列生成器的架构，类似自增序列，不过需要借助额外的开发工作量，以及提供一个第三方的服务，可以规避自增序列的字增全局锁的问题，提高并发，对数据水平拆分可以更好地支持；

(5).     双主复制架构的概率性碰到的场景：主服务器的数据执行成功，而没有复制到在线备用服务器时，出问题的概率确实存在，其他类型的做法，也必须人工干涉解决，都无简单且合理的自动化办法，以上四种办法都无法规避；

通过四种属性值作为主键的优缺点分析，以及对比前面我们阐述的主键需要的优秀素质，若是不考虑水平拆分的问题，带来额外设置上的麻烦，则自增序列是最佳的主键字段选择；用户的注册帐号本身要求唯一性且非空的场景下，则可以作为主键字段的选择；若是考虑水平拆分的问题，则采用自增序列生成器的架构，非常易用和可靠的实现方式，产生的值是最佳主键字段的选择；

【结束】

使用什么类型的字段属性作为主键，最关键核心的要考虑存储引擎：如何存储元数据、如何检索元数据、如何维护其内部的索引组织结构，以及我们要实现的业务是什么，最后共同决定我们，如何设计一张用于存储数据的表，以及决定操纵数据的SQL语句如何编写，再结合业务特点就决定了我们的索引如何创建，建议大家多关注InnoDB引擎内部实现原理和机制，可以阅读官方提供的一个文档InnoDB引擎内部实现，以及多分析和关注业务特点。

本文转自http://www.mysqlops.com/2011/09/10/innodb-primary.html