Innodb引擎的表上更换主键会发生什么

数据库的问题，如果不去深入思考，可能还真的只是一知半解，以前用Oracle的时候，也是经过几年不断地去探究其内部实现的原理，才真正明白了在Oracle数据库上做某个操作或者做某个配置的合理的方法。现在又到了国产数据库和开源数据库当道的时代了，我们不仅仅需要去研究一个Oracle数据库而是要去研究数个甚至数十个数据库了，这对于DBA来说是一件十分悲哀的事情。想起当年Poder说过的我这辈子只要干好Oracle数据库优化一件事就可以了，不知道这种数据库群雄并起的时代，对我们DBA来说是好事还是坏事了。

天底下的事情就怕较真，昨天一个客户咨询如果在一个MYSQL数据库上更换PRIMARY KEY，会发生什么？所有的索引都必须重建吗？这个问题似乎答案很简单，对于普通的堆表来说，主键更换不会影响其他索引，而对于Mysql innodb这样的BTREE表来说，所有的Secondary index里存储的不是数据行的物理地址，而是主键的键值，那么一旦主键更换了，那么所有的Secondary index就都必须重建了。不过这似乎太不合理了，真的会发生这样的事情吗？如果我们的表上面有好多个Secondary index，而且表的数据量很大，这样的操作似乎太消耗资源了？Innodb会不会采取更为巧妙的方法来处理这个问题呢？从一个架构师的角度，我首先就拒绝这样的设计，如果真的是这样，Innodb更换主键的成本就太高了。

innodb是采用BTREE存储结构的。因此在一张表的数据中分为两个段，一个段是主键索引段，被称为cluster primary key，一个段是数据段。

七月份的时候我发过一篇文章，讨论了BTREE结构表的一些基本结构，实际上MYSQL、达梦等数据库都使用了这种结构。采用这种存储结构的数据库有两个特点：首先，表中以主键值的顺序物理存储行。这意味着主键不仅唯一标识一行，而且还是它的一部分。或者更确切地说，物理行是表主键的一部分；其次，二级索引条目不指向实际的行位置，每个二级索引条目中存储的都是主键的值。当查询通过二级索引读取一行时，这个值被主键用于额外的隐式查找，以定位实际的行。

基于这样的底层存储结构，在设计MYSQL表的主键的时候，也有许多优化的原则，比如说，主键的键值尽可能地短，从而提高主键地效率。另外一点是选择主键的时候，尽可能选择增长趋势的字段作为主键（比如自增字段），因为innodb的行是按照主键的顺序插入的，采用自增的主键，有助于减少大量数据插入时的碎片产生，避免不必要的PAGE分裂，从而提高高并发插入的性能。当然你的数据库的负载没有那么大的时候，这些也不会成为一个主要的问题。

实际上，对于用惯了Oracle数据库的认，我们很可能在使用MySQL数据库上犯下错误。对于大量做范围扫描的应用来说，大家可能已经忘记了索引的一个重要的属性，cluster factor，一条索引的cluster factor越大，则做范围扫描的成本越高。对于Oracle这样的HEAP存储结构的数据库来说，插入数据是是有一定的随机性的，不过总体来说还是顺序添加为主。因此如果一个索引的扫描是和数据插入的性状相类似的时候，索引的cluster factor会比较小，扫描的总体成本会比较小。而对于BTREE存储结构的表，其行存储的位置和主键索引有关，因此如果MYSQL的索引也按照ORACLE那样去计算一个CLUSTER FACTOR的话，针对于范围扫描，其性能特征会和Oracle不同。当然，随着现在硬件的发展，存储性能的提升，CPU处理能力的巨大提升，这点系统开销往往可以被我们忽略，不过对于一些高并发，高负载，超大数据量的环节，MYSQL的这个特性的差异还是需要被我们重视。我们如果采用类似Oracle优化CLUSTER FACTOR的方法来优化数据存储，是不会获得和ORACLE一样的优化效果的。关于Oracle的CLUSTER FACTOR问题，我以前在《DBA的思想天空》这本书中讨论过，有兴趣的朋友可以去翻看。

前面谈了一些似乎和今天的话题不相干的问题，我们还是回到问题本身，那就是在INNODB上更换主键，会发生什么？

实际上我们潜意识里的想法就是正确的答案，刚开始我对更换主键的想法是有点想多了。实际上，当我们在MYSQL上创建一张表的时候，如果没有指定主键，则INNODB引擎会自动在表中找到非空唯一性的字段，在上面创建一个主键索引作为CLUSTER PRIMARY KEY。如果找不到，则会创建一个6字节的自增量，用这个自增量来创建CLUSTER PRIMARY KEY，这是因为INNODB的表上必须要有一个索引段。Secondary Index中的所有索引项都要指向这个索引段。有兴趣的朋友可以做个实验，创建一张无法选出主键的表的时候，我们会发现这张表上回自动产生一个GEN_CLUSTER_INDEX的UNIQUE索引，作为该表的主键。当我们在表上添加一个主键后，这个索引就会被删除，用新的主键字段创建一个新的unique索引来替代。当当我们修改INNODB表上的主键时，老的主键和UNIQUE索引会被删除，新的索引会被创建。根据INNODB的SECONDARY INDEX的设计原理，那么在主键更换的时候，二级索引是必须重建的。这也导致了在INNODB上更换主键是一个十分高开销的操作。

最后要说的一点是，如果你真的要在INNODB上更换主键，先删除老的主键，后创建新的主键是一种不可取的操作，因为删除老主键的时候，主键索引被删除，GEN_CLUSTER_INDEX会被创建，所有的二级索引会被重建。添加新主键的时候，GEN_CLUSTER_INDEX会被删除，主键索引会被创建，二级索引要再次重建。正确的做法是：ALTER TABLE DROP PRIMARY KEY ,ADD PRIMARY KEY TEST_TABLE(TEST_COL)。

明白了上面的原理，那么当我们在修改主键的时候，遇到#1878 - Temporary file write failure.这样的问题也不会一头雾水了，你知道MYSQL在干什么。

虽然讲了这么多，似乎在这方面MYSQL的设计不是很合理，是的，不合理，不过这是权衡多种因素的结果，这种不合理是为了其他方面更为合理，因为修改主键在实际已上线的生产系统中应该是个小概率事件。我们既然要用MYSQL，就应该对此早有准备，在设计数据表的时候就认真考虑好主键的问题。