mysql的text与off-page

经常遇到这样的问题：表中的大字段列（text）怎么处理？是应该独立成一张表，还是尽量变成varchar，对性能到底有什么影响。

先来看一些知识点

1.innodb_page_size

在innodb引擎下（5.6），有一个参数innodb_page_size,默认值是16k。
（PS： 这个参数在数据库初始化的时候就要放在my.cnf中，如果已经创建了表再修改此参数，mysql就会启动不起来）

 

show VARIABLES like '%innodb_page_size%'
----------------------
innodb_page_size    16384

这个值的大小设置也有一定讲究，比如越小的话checkpoint性能越好，但是每一页所能容纳的数据行就变少，这个要根据实际情况来；而且在已有的系统上，这个值也不好更改，这里就不展开了。

2.65535

在创建一张表的时候，所有字段的长度不能超过65535。
比如我创建下面这张表，utf8（且允许为null）一个字符占3个字节，那么65535/3=2w多。也就是说我创建两个1w长的varchar字段，是可以的，超过了就会报错。

 

长度超过65535报错

 

没超过65535就正常创建成功.png

 

但是我把每个字段改的很大，发现仍然能创建成功，这是为什么呢？观察表的实际列，原来是被转成了mediumtext类型（不管是mysql的SQL引擎转的还是我用的navicat转的，没有深究，这个也不重要）。

 

字段很大的情况下也会成功

 

实际上被转成了mediumtext

总之，这说明一个知识点，就是创建表的时候每一行的总长度不能超过65535字节。
要注意这里的长度指的是普通字段的长度，就是不包含text，blob；

3.offpage

从2可以看出，text，blob字段的长度是不算在65535限制内的，实际上这类字段是存储在溢出页（off-page）中的，并且每个字段独溢出页空间，以上面的b1和b2这两列为例，他们是分别存储在不同的offpage中的。然后在聚簇索引本页只存储了20个字节的指针指向offpage——前提是innodb_file_format = Barracuda且row_format=compressed或dynamic；如果row_format=compact（innodb_file_format=Antelope或者Barracuda都支持这种压缩模式）,则聚集索引会存储768字节的前缀。
通过以下语句可以查询这些参数。

 

SHOW TABLE STATUS IN database/table;
show VARIABLES like '%format%'

4.每页必须要有2行数据

因为innodb是IOT（索引组织表），所以存储结构是B+树，那么叶子节点就是双向链表，所以每一页至少要有2行数据，按照之前我们看到的16k的innodb_page_size来算，每一行的实际数据大小不能超过8k——比创建表的时候限制的每一行不能超过65535（64k）还要小。

5.是不是所有varchar都不会溢出，所有text都会溢出？

并不是。
比如说你在compact压缩方式下，定义了一个字段char(255)，字符集是utf8的话，3255=765刚好放得下，但如果字符集是utf8mb4，那么需要的字节数是4255=1020，就放不下了，就需要把超出765的部分放在offpage中。官网原话是：

compact

 

在dynamic压缩方式下，varchar或者char超过了一定的长度（768字节，算法和compact一样），也会溢出为offpage，而text没有达到一定长度，就不会溢出。

---

回到正题，看看如下各个场景有何影响

1.查询的时候不包含text列

根据知识点3，这种情况，完全不影响查询的性能。因为text字段是放在off-page中的，仅有20k在主索引中，所以几乎不会产生什么性能上的影响。所以此时就不必把text列拆出来，因为你做的事情，mysql自己就做了，还做的比你好。不过你需要保证row_format是dynamic。所以在创建表的时候最好加上row_format=dynamic。
但是如果你用的是percona的版本，我建议把不常查询的text列独立成一张表， 然后这张新的表采用tokudb引擎和tokudb_zlib行压缩，因为这样能大大减少text的存储空间。实测在compact下3.4G的表，在toku_zlib下只有700M。tokudb引擎的特点是写入性能较好，压缩比高，缺点是qps性能要差一些，所以适合存储不常用的数据。

2.varchar(2000) vs text

你已经确定字段长度不会超过2000，所以你纠结该不该把text换成varchar(2000)。
根据知识点5，mysql其实是知道把长字段放在offpage中还是聚集索引中的，所以我建议直接设置成text，让mysql自己去决定。
当然如果只有200个字符的话，就不要用text去替代varchar(200)了。

3.表中有不止一个text字段，一个查询中同时需要查询他们

根据知识点3，n列都放在不同的off-page列，所以需要读取n次。所以尽量不要同时查询多个text。

4.如果你想对text字段建前缀索引来做like查询

这种情况下你应该选择compact压缩。