设计-Varchar的善变你懂吗
【缘起】
大家平时在进行数据库设计的时候，往往需要为各种字符类型的列字段进行长度设置，最常见的一般就是用户账号表，其中有：账号、密码、昵称、姓名、手机号码等，全部都是字符类型的列字段。在进行字符类型的列字段设计时，通常都使用的是Varchar类型。
我们知道Varchar类型为变长类型，在创建表的时候，可以指定Varchar列字段能存储的字符长度(个数)，那么究竟要设置多大的字符长度值，才是比较合适的呢？是不是Varchar列字段的长度设置的越大就越好呢？设置了不同的字符长度后，Varchar列字段会像我们所预期的那样良好工作吗？
接下来，我们就来看看，Varchar类型的列字段，在设置了不同的字符长度后，究竟会发生怎样的变化。^_^
【存储限制】
首先我们知道MySQL数据库中，Varchar列类型最大可以存放65535字节。那么理论上就应该可以存放65535个字符。但是，这是真的吗？真的可以存放65535个字符吗？
我们看下图：
如上图，通常我们使用的数据库的字符集为utf-8，从错误消息中可以看出，my_varchar列类型所允许的最大(max)长度为21845，而utf-8字符集的每个字符占用3个字节，所以得出21845 × 3 = 65535。
那么长度为21845的Varchar列字段，可以被正确创建吗？我们看下图：
如上图，长度为21845的Varchar列字段又创建失败了，从错误消息可以看出，Varchar列类型的字段，除了存储字符之外，还需要额外的2个字节来存储列数据的字符长度。所以 (65535 - 2) ÷ 3 = 21844，我们来看下长度为21844的Varchar列字段，能否被正确创建，如下图：
OK，长度为21844的Varchar列字段，创建成功了！那么如果我想在1个表中，创建多个长度为21844的列字段，能否成功呢？我们接着往下操作：
Opps，我们看到在1个表中，创建2个长度为21844的Varchar列字段失败了。从错误信息可以看出，数据行的总大小不能超过65535字节，也就是说65535长度指的是所有Varchar列的长度总和，如果Varchar列的长度总和超出这个长度，也就是utf-8字符集的21844的长度，依然无法创建成功。
既然2个21844长度的Varchar列字段无法创建成功，那么创建1个长度为21844的Varchar列字段和1个Int列字段，能否创建成功呢？看下图：
依然创建失败了，从错误信息中再次说明了，数据行的总大小不能超过65535字节。也就是说，不只是Varchar列字段的总和长度不能超过65535字节，而是表中各种列类型字段的最大存储长度总和，不能超过65535字节！这里Int占用了4个字节 21844 × 3 + 4 = 65536，超出了1个字节，所以无法创建成功。
【页类型变化】
通过上面的例子，我们知道Varchar类型的列字段，最大能存放65532字节。但是MySQL中InnoDB存储引擎的页大小为16KB，即16384字节。怎么能存放65532字节的数据呢？
在一般情况下，InnoDB存储引擎的数据都是存放在页类型为B-tree node中，这样能够有效提高数据的检索和命中效率。如果插入1条大小为65532字节的数据后，该条数据还会存放在B-tree node页类型中吗？
我们看下面的例子：
如上图，我们创建t_max_len_varchar示例表，其中my_varchar列字段的长度为21844。然后插入1条数据为，重复了21844个“中”(utf-8: E4 B8 AD) 字。
接下来，我们要查看t_max_len_varchar的表空间信息，来确定该条数据是否放在了，页类型为B-tree node的数据页中。在查看页类型之前，我们先来看下InnoDB是如何来标识各种页类型的。如下：
如上图，我们看到在每个数据页的文件头的第24个字节(4+4+4+4+8=24)开始，后面2个字节(即25、26字节)表示数据页的类型。下面是各数据页类型对应的标识值：
如上图，在MySQL的InnoDB源码中，用不同的数值来标识出各种不同的页类型。这里我们主要记住，B-tree node的标识值为17855，十六进制为：45 BF；Uncompressed BLOB page的标识值为10，十六进制为：A。
有了上面的这些信息，就能看出t_max_len_varchar的表空间中，数据所在的页类型了。如下：
如上图，我们看到c000(十进制为49152)所在的位置是第4页的起始位置，计算方法：(49152÷1024÷16)+1=4(页)。第25、26字节的数值为：45 bf，表示的页类型为B-tree node。“中”字的utf-8编码为：E4 B8 AD。但是在其下方的数据区，并没有找到“中”字的utf-8字节码。
那我们的数据究竟去哪里了呢？我们接着往下看：
如上图，在位置为10000的第65页，找到了所插入的数据！第25、26字节所表示的页类型的标识值为00 0a( 十进制为10 )，即：Uncompressed BLOB page！
本来应该存放在B-tree node页类型的Varchar类型的列字段，怎么插入数据后，会变成Uncompressed BLOB page页类型的BLOB字段了呢！？究竟多长的Varchar列字段是保存在B-tree node页中的呢？
我们知道InnoDB存储引擎中的表，是索引组织的，即B+Tree的结构，这样每个数据页中，至少应该有两条行记录( 否则失去了B+Tree的意义，变成链表了 )。
因此，如果页中只能存放下一条行记录，那么InnoDB存储引擎会自动将行放到Uncompressed BLOB page页中。请看下面示例：
这里创建t_big_len_varchar示例表，my_varchar的Varchar列字段长度为2800。接下来分别插入'中'(E4 B8 AD)、'国'(E5 9B BD) 这2条数据，如下：
在utf-8字符集下，2条数据就占用 2800×3×2=16800(字节)。这就超出了1个页的16KB(16384字节)。我们查看t_big_len_varchar的表空间，如下：
如上图，在位置c000所在的第4页，标识值为45 bf的B-tree node页下方，并没有找到'中'(E4 B8 AD)、'国'(E5 9B BD)这2个字的字节码。
继续往下找：
如上图，分别在位置10000的第5页和位置14000的第6页，找到了'中'、'国'的数据。其页类型标识值都为00 0a(十进制为10)，即都是Uncompressed BLOB page页！
但是，如果页中能至少存放下两行记录，那Varchar列类型的行数据，就不会放到Uncompressed BLOB page页中去。InnoDB存储引擎会将数据行放到B-tree node页中。那么Varchar列字段的长度应该设置为多长，才能刚好在1个B-tree node数据页中，存放下2行数据呢？见下图：
如上图，我们发现在每个B-tree node页的开始，会有120个字节的页头信息，这里c000-c077就是页头信息；每行数据的开头，会有27字节的行记录头信息，这里从c078到c092就是第1条数据的行记录头信息；在页尾fff4的位置，会有4个字节的页目录信息(Page Directory)，这里fff4-fff7表示页目录的信息( 通常1条数据占1个页槽位，每个页槽位占2个字节)；最后面的8个字节就是数据页的尾部信息，这里fff7-ffff是尾部信息。
那么16KB(十进制为16384)的页空间，如果想要刚好存放下2条Varchar列类型的数据，Varchar列字段的长度应该为：( 16384 - 120(页头) - 27×2(行记录信息) - 2*2(页目录) - 8(页尾) ) ÷ 3(utf8字节) ÷ 2 = 2699(长度)。
请看下面示例：
这里创建t_small_len_varchar示例表，my_varchar的Varchar列字段长度为2699。接下来分别插入'中'(E4 B8 AD)、'国'(E5 9B BD) 这2条数据，如下：
接着我们查看t_small_len_varchar的表空间，如下：
如上图，在位置c000所在的第4页，标识值为45 bf的B-tree node页下方，
我们找到了'中'(E4 B8 AD)、'国'(E5 9B BD)这2个字的字节码，这就说明了此时的行记录数据，都是存放在B-tree node页中了，而不是在Uncompressed BLOB page页中了！那么在数据查询遍历的时候，就能够利用B-tree的页特性，提高Varchar数据列的读取性能了！
PS：B-tree node页的组成比较复杂， 其中120字节的页头信息，又包含文件头+页头+页根信息；27字节的行记录头，又包含字段长度、NULL标志位、记录头、行ID、事务ID、回滚指针等信息；记录头的字节长度和页目录的字节长度并不固定，这里只是一个例子，实际设计中还是要多做实践研究。关于B-tree node页的结构组成，请大家自行脑补，这里就不再详细阐述了。
【表空间变化】
通过上面的例子，我们知道了Varchar类型的列字段，并不总是如预期的那样存储在B-tree node数据页中，而是会根据Varchar列字段的总长度，是否超过一定的长度阈值，而决定是否将数据存放在含有索引特性的B-tree node页中，或者是存放在BLOB列类型的Uncompressed BLOB page页中。在utf-8字符集下，决定这一变化的Varchar的行记录总长度值为：2699。
那么有的朋友会问了：“是不是我在设置一个表的Varchar列字段的总长度，不超过2699，就可以尽可能长的、随意地设置表的Varchar字段长度了呢？”又或者说：“我不清楚产品经理以及客户对该字段的存储需求，为了将来的扩展和冗余，我将Varchar列字段的长度设置的很长，或者是预期长度的2倍，这样可以吗？”
那么究竟在一个表中，Varchar列字段的长度到底要设置为多长才合适呢？在相同表结构的情况下，是不是Varchar字段的长度设置的越长就越好呢？究竟设置长了和设置短了，Varchar类型列在存储的时候，又会发生怎样的变化呢？
让我们看下面的例子：
如上图，我们创建了2个列字段一模一样，但是Varchar列字段长度不一样的表，t_complex_small_varchar和t_complex_big_varchar。其中2个表都有1个Int类型的id自增主键，3个Varchar类型的字段列：str_word、str_num、str_sentence。
不同的是，t_complex_small_varchar表的3个Varchar字段的长度比较小，分别为：str_word(20)、str_num(20)、str_sentence(50)。
而t_complex_big_varchar表的3个Varchar字段的长度比较大，分别为：str_word(500)、str_num(500)、str_sentence(1000)。
接下来，我们向2个表中分别插入2条相同的数据，其中str_word为重复10次的'中'字(E4 B8 AD)、str_num为重复10次的'8'(utf-8编码:38)、str_sentence为重复20次的'国'字(E5 9B BD)。如下：
如上图，虽然t_complex_small_varchar表和t_complex_big_varchar表的列字段一模一样，但是2个表的Varchar列长度不同。但是我们分别向2个表插入了相同字符长度的数据：'中'*10、'8'*10、'国'*20。
那么这2个表的数据，在表空间中的存储结构是否一样呢？接着往下看：
如上图，在位置c000所在的第4页(B-tree node)的下面，从c092-c173就是我们的2条'中'*10、'8'*10、'国'*20的数据了。
可以看出，虽然t_complex_small_varchar表和t_complex_big_varchar表的Varchar列类型的字段长度不同，但是在存储相同字符长度的情况下，2个表的表空间存储结构是完全一样的！
那么如果我们分别向2个表中，多插入一些相同的数据，这2个表的表空间存储结构，还会是一样的吗？
下面为了方便测试，分别创建2个存储过程，用来分别向2个表批量插入指定条数的数据，插入的内容依然是上面的'中'*10、'8'*10、'国'*20。如下：
如上图，创建了2个存储过程，用来分别向2个表插入相同的数据。接下来分别调用这2个存储过程add_complex_small_varchar和add_complex_big_varchar，向2个表插入500条数据。如下：
加上之前分别插入的2条数据，目前2个表分别有502条数据，并且存储的都是完全相同的'中'*10、'8'*10、'国'*20。我们再看下2个表的表空间结构，是否会发生变化，如下：
如上图，在位置10000所在的第5页(B-tree node)，分别从10092所在的位置开始，t_complex_small_varchar表和t_complex_big_varchar表的表空间存储结构就是一样的了！(不同的只是每行记录头信息的不同而已，不影响数据本身存储)
我们再看一下2个表空间的数据底部，如下：
在位置228d3的第9页，t_complex_small_varchar表和t_complex_big_varchar表的最后1条数据，也是一模一样的了！由于'中'和'国'分别占用3个字节，数字'8'占用1个字节，每个表的502条数据总共占用的存储空间为：(10×3+10×1+20×3)×502=50200字节。需要占用50200÷1024÷16=3(页)。由于数据是从位置10000所在的第5页开始存储的，加上502条数据需要占用d的3页，再加上一些额外的存储开销，刚好存储到228d3所在的第9页，每1页都是用B-tree node页来进行存储的，十分紧凑高效。
这再次说明了，在相同列字段而Varchar列长度不同的2个表中，存储2个表都能容纳下的相同字符长度的数据后，2个表的表空间存储结构也是相同的！
也就是说，在存储表可以容纳相同字符长度的数据时，表空间的存储结构，不会因为Varchar列字段的长度不同，而产生不同的存储结构和空间占用。
那么在什么时候，不同长度的Varchar列字段，在存储的时候，表空间会发生剧烈的变化呢？
我们继续往下操作：
如上图，我们分别修改t_complex_small_varchar表和t_complex_big_varchar表中id>=60并且id<=120的数据的字符长度，将'中'、'8'、'国'的字符重复次数，改为t_complex_small_varchar表中设置的3个列字段长度的最大值，即：str_wrod(20)、str_num(20)、str_sentence(50)。
接下来，我们看下2个表的表空间产生了什么变化，如下：
如上图，从位置13b92所表示的0 00 00 3c(十进制60)，就是主键id所表示的id为60的行数据了，右边下面可以看到str_num字段已经改为了20个'8'了，即'8'*20。
这里发现在该条数据的头部13b89的位置，t_complex_big_varchar表由于Varchar长度超过255的缘故，比t_complex_small_varchar表多了1个'80'的字节进行占位。所以后面知道id为120的每1条数据，t_complex_big_varchar的行数据开头部分，都会有1个'80'字符的长度占位符，这就使得Varchar长度超过255的t_complex_big_varchar表的每条数据，都会比t_complex_small_varchar表多1-3个字节的Varchar字段长度占位字节！
继续往下找：
这里在t_complex_small_varchar表的17e75位置和t_complex_big_varchar的17bbd位置，分别找到了id为'00 00 00 78'(十进制120)的修改的最后1条行数据。
从目前2个表的表空间存储结构情况来看，虽然t_complex_small_varchar的id范围60~120的行数据的Varchar列字段存储的长度达到了最大值，但是因为改动的行数据字符数并不算很大，所以2个表空间的存储结构变化不算很明显。
那么如果此时，把t_complex_big_varchar的列字段数据，改为其自身的列最大长度，表空间的结构还会这么紧凑排列吗？
继续往下操作：
如上图，此时t_complex_big_varchar表中id为60~120的行数据，Varchar列字段字符长度，改为了其Varchar列字段建表时，所设置的最大长度值。即：str_word(500)、str_num(500)、str_sentence(1000)。
接下来我们查看其表空间，如下：
上面是其与 t_complex_small_varchar(左边)表空间的对比图，这里从最左边的文件红色区域就能看出来，右边的t_complex_big_varchar的表空间存储结构变大了许多！
那么其和之前'中'*20、'8'*20、'国'*50(同t_complex_small_varchar表Varchar列字段的长度最大值)，在表空间存储结构上，又有怎样的变化呢？如下：
上面是其与之前的t_complex_big_varchar的表空间对比图，从最左边的文件红色区域可以看出，虽然修改的是相同id(60~120)范围的Varchar数据字段，但是由于Varchar列字段的长度设置的很大，在全部填满字符后，表空间的存储结构和位置发生了巨大的变化！
让我们找到这2个表空间的最后1条数据，如下：
对比发现，之前的t_complex_big_varchar表的最后1条数据存储到了25e00的位置(第9页)，而修改为Varchar列字段最大值后的最后1条数据，存储到了827c1的位置(第32页)，这里就多出来了23页。
那我们所修改的Varchar占满后的id范围为60~120的这61条数据，占用空间为：(500×3+500×1+1000×3)×61÷1024÷16=18(页)。23-18=5页，粗略估算，就多出来了5×16k=80k的存储空间占用！
最后，把t_complex_small_varchar表和t_complex_big_varchar表的全部502条数据，全部修改为Varchar列字段的长度最大值，如下：
再看下t_complex_small_varchar表和t_complex_big_varchar表的表空间占用情况，如下：
如上图，t_complex_small_varchar(左侧)表空间在Varchar列字段填满的情况下，存储结构依然相对紧凑，最后1条数据，在位置33c20(第12页)就存下了。而文件最左侧的红色区域的对比图，t_complex_big_varchar(右侧)表空间的存储变化和占用，就发生了天翻地覆的变化！
接下来，对比各自表与上一次修改(id为60-120)的表空间存储对比，首先对比t_complex_small_varchar表，如下：
如上图，虽然全部数据修改后的t_complex_small_varchar表在存储空间上与之前比发生了增长变化，但是整体表空间的存储结构依然比较紧凑，数据页中空白区并不多，表空间既没有发生很大的膨胀增长，又能保证高效的数据查询效率。
我们再看t_complex_big_varchar表空间的前后对比，如下：
如上图，发现表空间里面数据页出现了很多空白区，数据存储结构发生了巨大的变化，最左侧的红色区域对比图，发现全部Varchar列字段修改为字符长度占用最大值后，t_complex_big_varchar(右侧)的表空间发生了整体的数据重排和空间膨胀行为！
我们分别看一下最后的t_complex_small_varchar表和t_complex_big_varchar表的最后1条数据的位置，如下：
如上图，t_complex_small_varchar表最后1条的数据位置在33c30的位置(第12页)，t_complex_big_varchar表的最后1条数据的位置在3dd41c的位置(第247页)。
t_complex_small_varchar的Varchar列字段全部填充满后，所占用的页数为：(20×3+20×1+50×3)×502=105420字节。需要占用105420÷1024÷16=6(页)。
t_complex_big_varchar的Varchar列字段全部填充满后，所占用的页数为：(500×3+500×1+1000×3)×502=2510000字节，2510000÷1024÷16=153(页)。
粗略估算出，t_complex_small_varchar表，Varchar列字段填满后，多占用了12-6=6页，就是6×16k=96k。而t_complex_big_varchar表，多占用了247-153=94页，就是94×16k=1504k！
上面的例子还只是502条数据的测试，那么随着数据量的增多，很明显Varchar列类型的字段长度值设置的越大，一旦发生了大长度的字符修改填充，数据表的表空间结构就会进行重排，从而导致表空间大小不断膨胀变大，浪费了磁盘的存储空间，同时还降低了数据遍历检索的性能，实在是得不偿失！
【总结】
本篇主要针对MySQL数据库设计中，Varchar列类型的字段的长度，设置不同的字符长度参数值后，InnoDB存储引擎在存储Varchar数据的时候，页类型和表空间存储结构，产生怎样的变化，进行了对比和分析。
经过不同的示例分析，我们知道了在utf-8字符集的情况下，由于每个字符占3字节的存储空间，并且每个Varchar列类型还需要额外1~2个字节表示列字段的字符长度大小，所以Varchar所能存储的最大长度为21844！
1个表的列字段结构中，包含Varchar列字段在内，所有字段列的最大存储空间的合计大小，不能超过65535字节！
在InnoDB存储引擎中，1个数据页大小为16kb。如果Varchar列字段的长度值大于2699，那么1个数据页中就无法存放下2条Varchar(2699)长度的数据，这时InnoDB存储引擎就会把插入的Varchar数据，存放在BLOB列类型的Uncompressed BLOB page页中！这个页类型的存储是链表结构的，因此数据查询遍历的性能就会降低！
如果Varchar列字段的长度值不大于2699，16KB(十进制为16384)的页空间，就能存放下至少2条Varchar列类型的数据，那么InnoDB存储引擎就会把插入的Varchar数据，存放在B-tree node数据页中，那么在数据查询遍历的时候，就能够利用B-tree的页特性，提高Varchar数据列的读取性能了！
最后我们分析了在2个表中，相同的Varchar字段列个数，但Varchar列字段的长度值不同时。如果向2个表都插入Varchar列字段长度能够容纳下的，相同的字符数据的时候，InnoDB存储引擎对不同Varchar列长度的表空间的数据存储和结构处理，是相同的。只不过在Varchar列字段的长度值大于255的表空间中，每条数据会额外多出来1个字节的Varchar长度占位符。
但是在进行Varchar列字段的数据修改时，如果Varchar列字段的长度值设置的较小，并且修改后的数据字符占用长度与修改前的数据字符占用长度相差不大的情况下，数据表空间的数据存储结构依然能够保持紧凑，数据页中空白区不会过分增多，表空间通常不会发生膨胀增长，从而保证了高效的数据查询效率！
如果Varchar列字段的长度值设置的较大时，如果修改后的数据字符占用长度比修改前的数据字符占用长度要大很多的话，数据表的表空间结构就会发生数据重排，从而导致表空间大小不断膨胀变大，浪费了磁盘的存储空间，同时还降低了数据遍历检索的性能！
那么在实际的MySQL建表的过程中，Varchar列字段的长度是否应该有冗余呢？冗余多少合适呢？较长的、内容长度差异较大的字符存储需求，又应该如何设计Varchar字段列呢？
阿K认为，在Varchar列字段的设计时，应该充分和产品经理沟通，深入理解需求，找出存储字符长度固定的或存储长度变化不大的列字段，可以考虑为其设置长度刚好的Varchar长度值，如：32位长度的用户id、6~20个字符的用户名、8位长度的商品编码等。这些长度几乎不会变动的字段，都可以考虑设置Varchar列长度为对应的32、20、8的固定长度。
如果考虑将来可能存在的Varchar列字段长度不够，想要设置冗余长度的话，这里建议是冗余长度，不要超过原Varchar列字段长度的1/3(3分之1)，即：32的长度冗余到41、20冗余到26、8冗余到10，毕竟在utf-8字符集的情况下，每个字符要占用3个字节，冗余过多依然会造成表空间问题。
如果存储的字符需求较长，且每条数据的内容长度差异较大，建议是另外单独建立关联子表来存储Varchar列字段长度较大的数据。因为这样设计后，大长度的Varchar列字段发生频繁插入变更后，即便发生了频繁大量的表空间变动，也不会影响主表的表空间结构，主表的表空间存储结构依然可以保持紧凑。这样既保证了主表的数据查询性能，又不会降低主表的数据IO(吞吐)。
而数据长度较大的子表，可以采用缓存的方式，在数据操作完成后，将数据存入缓存或NoSQL中，如：Redis、MongoDB等。这样就能同时提高子表的数据查询读取性能，又降低了MySQL的IO压力，是比较常用的一种方案。
没有想到，1个小小的Varchar字段长度设置，就能产生如此多的变化和影响。本篇仅抛砖引玉的讲解了几个Varchar字段的变化例子。实际开发设计中，情况多种多样，作为开发人员的您，又是怎样看待和思考Varchar的长度特性的呢？
欢迎留言与阿K进行讨论交流，分享您的Varchar设计的心得。希望本篇文章对您有所帮助，谢谢！
PS：本篇在进行表空间分析的时候，由于表空间文件过大，无法全部截图列出，感兴趣的朋友，欢迎在我的Gitee上下载表空间文件进行自行分析。在实际设计开发中，建议建立完数据表后，最好先插入一些测试数据，观察一下数据表空间的变化，不断尝试、不断总结、不断优化。最终设计出最合适的Varchar字段方案来。
【示例】
本文中提到的MySQL的示例文件，在阿K的Gitee中都可以找到，链接如下：
