mysql变长类型字段varchar值更新变长或变短底层文件存储原理

        为了搞清楚MySQL对于可变长度字段值修改时，如何高效操作数据文件的机制。之前一直模糊不清，网上也搜不到现成的答案。经过多方资料搜集整理。写出此文供大家一起参阅。由于涉及众多非常底层的知识，我假设读者已经对操作系统和磁盘存取有一定的基础知识。文中如有疏漏，还请大佬指正。

　　为了探究这个问题，我们要先来回顾一下我之前的一篇文章《文件随机或顺序读写原理深入浅出》讲的文件存储的底层原理知识。如下图所示。一个文件的数据是以块为单位存储到物理磁盘的随机位置，这是由操作系统负责管理的，用户程序无权决定。所以在文件视图层面我们连续存储的数据，映射到物理磁盘层面就是随机位置了。图中是假设磁盘块大小为32KB，则文件对应的数据偏移地址存储到对应的物理块中示意图。

　　我们现在假设MySQL的一张表对应一个数据文件。那么表里的数据按行存储，则行与行之间的数据被紧密的连续填充到上图“数据文件视图”中，并被以块为单位随机存储到了物理磁盘上。这样遍历表时，就可以从文件0地址开始依次读取到所有数据，行与行之间的间隔符就是普通的换行符。每一行中的字段都按照表结构定义中的字段长度读取即可。这里先假设表中的字段都是定长类型的。这样就不会有什么问题。即便是更新某个字段的值，则可以直接使用随机文件读取方法定位到字段的偏移地址写入新值即可覆盖旧值。

　　那么现在问题来了，如果表中某字段是可变长类型的如varchar(50)。数据插入时假设值为“月光冷锋”，后面有个更新操作，需要将值改为“月光冷锋的博客”。此时会发生什么呢？由于是可变长度的字段类型，文件中该行的该字段实际占用空间就是四个字符，而不是50个字符。且行之间数据紧密排列存储。现在值要变成7个字符，则我们无法通过简单的随机文件存取定位到该字段覆盖旧值，这样会出现严重的问题，会覆盖其他字段或行的值。一种古老的文件修改方法是，先将要修改的位置后面的数据都转移到一个临时文件中，然后修改现在的位置处的值，最后再把临时文件的内容拼接回原文件，从而实现修改操作。显然这种方式无法应用到频繁更新的数据库上。这种方式代价巨大，不过大多数文件都是只读的，极少更新。所以应用也挺广泛的。比如音频、视频文件。

　　那么MySQL是如何解决这类问题的呢？首先我们如果把表数据文件看成一个可以任意发挥的平台，我们不在像其他类型文件那样直接将数据一个挨着一个紧密的写入文件的偏移地址中。MySQL使用页这个概念重新对文件视图划分，也就是一个页对应一段文件的偏移地址。如下图所示。MySQL页大小默认16kB，刚好对应图中两个页对应到一个物理块32kb大小。MySQL的页使用双向链表方式组织。

 

　　下面我们来看下如果可变长度varchar类型的字段值变长了，文件里的数据该怎么存储呢？因为表中数据刚开始插入时，可变长度字段值都是根据实际长度存储下来的，且行与行之间数据也是紧密连续存放在文件地址中的（再次强调一下，不是连续存放在物理磁盘上的）。那么现在值变长了，原来的位置无法扩展出新的空间出来，所以无法覆盖存放到原来的位置上。此时MySQL就会使用页分裂的方法扩展字段变长的空间。

　　比如假设行10的数据存放在页③的位置，如下图2所示。行11也是存放在页③的位置，且他们两行都把页③填满了。现在更新行10的某个变长字段值，由“月光冷锋”改成“月光冷锋的博客”。MySQL将新创建一个页⑥出来（相应的原数据文件也要变大增长），把原来页③的内容，行10和行11的数据重新生成排列一遍存储到页③和页⑥中，同时将原来的页链表结构重新修改其前驱和后继页节点的指针就OK了。如下图3所示。

图2        

 图3                                                                          

　　MySQL就是通过这种技巧，实现了修改数据文件时，不必像传统修改文件那样付出昂贵代价。这种方式虽然解决了修改文件时避免大规模移动数据的弊端，但是读取这些数据时，却无法像传统存取方式那样，直接从文件偏移地址0开始顺序读取。而是要根据页的链表结构顺序读取。需要不断的计算和移动文件偏移量指针，好在这个过程不会花费多少代价。但是会带来另外一个比较严重的问题就是页空洞，也称为碎片。上面行11有部分字段数据已经转移到了页⑥中，显然页⑥是没有存满的。行12是存在页④中的，这样就产生了碎片问题，浪费了文件的一些地址空间，这些空洞存的都是特殊占位符，也要占据真实的物理磁盘空间。随着更新删除操作越来越多，碎片也会越来越多，所以有必要定期进行表的碎片整理，这样可以收缩表文件占据的磁盘空间。也可以降低页链表的长度，从而节省一些寻址操作代价。

　　如果可变长字段值由大变小，则原来的字段值地址空间足够了，也就不需要新加页了，只需要重新整理排列一下当前更新的行数据即可。使得变成字段的值占用实际空间即可。至于留下的页碎片问题，MySQL也有相应的机制做合并优化操作。我这里不做深究。