众所周知，MySQL广泛应用于互联网的OLTP（联机事务处理过程）业务系统中，在大厂开发规范中，经常会看到一条"不建议使用text大字段类型”。
下面就从text类型的存储结构，引发的问题解释下为什么不建议使用text类型，以及Text改造的建议方法。
案例
问题描述
某歪有一个业务系统，使用RDS for MySQL 5.7的高可用版本，配置long_query_time=1s，添加慢查询告警，我第一反应就是某歪又乱点了。
我通过监控看CPU， QPS，TPS等指标不是很高，最近刚好双十一全站都在做营销活动，用户量稍微有所增加。 某歪反馈有些原本不慢的接口变的很慢，影响了正常的业务，需要做一下troubleshooting。
问题分析
我从慢查询告警，可以看到有一些insert和update语句比较慢，同时告警时段的监控，发现IOPS很高，达到了70MB/s左右，由于RDS的CloundDBA功能不可用，又没有audit log功能，troubleshooting比较困难，硬着头皮只能分析binlog了。
配置了max_binlog_size =512MB，在IOPS高的时段里，看下binlog的生成情况。
需要分析为什么binlog写这么快，最有可能原因就是insert into request_log表上有text类型，request_log表结构如下（demo）
CREATE TABLE request_log (` `id bigint(20) NOT NULL AUTO_INCREMENT,` `log text,` `created_at datetime NOT NULL,` `status tinyint(4) NOT NULL,` `method varchar(10) DEFAULT NULL,` `url varchar(50) DEFAULT NULL,` `update_at datetime DEFAULT NULL,` `running_time tinyint(4) DEFAULT '0',` `user_id bigint(20) DEFAULT NULL,` `type varchar(50) DEFAULT NULL,` `PRIMARY KEY (id)` `) ENGINE=InnoDB AUTO_INCREMENT=4229611 DEFAULT CHARSET=utf8`
分析binlog：
$ mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000539|egrep "insert into request_log"
满屏幕都是看不清的内容，翻了半天没翻完。
基本上已经确定是写入request_log的log字段引起的，导致binlog_cache频繁的flush，以及binlog过度切换，导致IOPS过高，影响了其他正常的DML操作。
问题解决
跟开发同学沟通后，计划在下一个版本修复这个问题，不再将request信息写入表中，写入到本地日志文件，通过filebeat抽取到es进行查询，如果只是为了查看日志也可以接入grayLog等日志工具，没必要写入数据库。
text是一个能够存储大量的数据的大对象，有四种类型：TINYTEXT, TEXT, MEDIUMTEXT,LONGTEXT，不同类型存储的值范围不同，如下所示
| Data Type | Storage Required |
其中L表是text类型中存储的实际长度的字节数。可以计算出TEXT类型最大存储长度2**16-1 = 65535 Bytes。
Innodb数据页由以下7个部分组成：
| 内容 | 占用大小 | 说明 |
|---|
内容占用大小说明
说明：File Trailer只有一个FiL_Page_end_lsn部分，占用8字节，前4字节代表该页的checksum值，最后4字节和File Header中的FIL_PAGE_LSN，一个页是否发生了Corrupt，是通过File Trailer部分进行检测，而该部分的检测会有一定的开销，用户可以通过参数innodb_checksums开启或关闭这个页完整性的检测。
从MySQL 5.6开始默认的表存储引擎是InnoDB，它是面向ROW存储的，每个page(default page size = 16KB)，存储的行记录也是有规定的，最多允许存储16K/2 - 200 = 7992行。
Innodb支持四种行格式：
| 行格式 | Compact存储特性 | 增强的变长列存储 | 支持大前缀索引 | 支持压缩 | 支持表空间类型 |
|---|
行格式Compact存储特性增强的变长列存储支持大前缀索引支持压缩支持表空间类型
由于Dynamic是Compact变异而来，结构大同而已，现在默认都是Dynamic格式；COMPRESSED主要是对表和索引数据进行压缩，一般适用于使用率低的归档，备份类的需求，主要介绍下REDUNDANT和COMPACT行格式。
这种格式为了兼容旧版本MySQL。
行记录格式：
| Variable-length offset list | record_header | col1_value | col2_value | ……. | text_value |
|---|
具有以下特点：
存储变长列的前768 Bytes在索引记录中，剩余的存储在overflow page中，对于固定长度且超过768 Bytes会被当做变长字段存储在off-page中。
索引页中的每条记录包含一个6 Bytes的头部，用于链接记录用于行锁。
聚簇索引的记录包含用户定义的所有列。另外还有一个6字节的事务ID（DB_TRX_ID）和一个7字节长度的回滚段指针(Roll pointer)列。
如果创建表没有显示指定主键，每个聚簇索引行还包括一个6字节的行ID（row ID）字段。
每个二级索引记录包含了所有定义的主键索引列。
一条记录包含一个指针来指向这条记录的每个列，如果一条记录的列的总长度小于128字节，这个指针占用1个字节，否则2个字节。这个指针数组称为记录目录（record directory）。指针指向的区域是这条记录的数据部分。
固定长度的字符字段比如CHAR(10)通过固定长度的格式存储，尾部填充空格。
固定长度字段长度大于或者等于768字节将被编码成变长的字段，存储在off-page中。
一个SQL的NULL值存储一个字节或者两个字节在记录目录（record dirictoty）。对于变长字段null值在数据区域占0个字节。对于固定长度的字段，依然存储固定长度在数据部分，为null值保留固定长度空间允许列从null值更新为非空值而不会引起索引的分裂。
对varchar类型，Redundant行记录格式同样不占用任何存储空间，而CHAR类型的NULL值需要占用空间。
其中变长类型是通过长度 + 数据的方式存储，不同类型长度是从1到4个字节（L+1 到 L + 4），对于TEXT类型的值需要L Bytes存储value，同时需要2个字节存储value的长度。同时Innodb最大行长度规定为65535 Bytes，对于Text类型，只保存9到12字节的指针，数据单独存在overflow page中。
这种行格式比redundant格式减少了存储空间作为代价，但是会增加某些操作的CPU开销。如果系统workload是受缓存命中率和磁盘速度限制，compact行格式可能更快。如果你的工作负载受CPU速度限制，compact行格式可能更慢，Compact 行格式被所有file format所支持。
行记录格式：
| Variable-length field length list | NULL标志位 | record_header | col1_value | col2_value | ……. | text_value |
|---|
Compact首部是一个非NULL变长字段长度的列表，并且是按列的顺序逆序放置的，若列的长度小于255字节，用1字节表示；若大于255个字节，用2字节表示。变长字段最大不可以超过2字节，这是因为MySQL数据库中varchar类型最大长度限制为65535，变长字段之后的第二个部分是NULL标志位，表示该行数据是否有NULL值。有则用1表示，该部分所占的字节应该为1字节。
所以在创建表的时候，尽量使用NOT NULL DEFAULT ''，如果表中列存储大量的NULL值，一方面占用空间，另一个方面影响索引列的稳定性。
具有以下特点：
索引的每条记录包含一个5个字节的头部，头部前面可以有一个可变长度的头部。这个头部用来将相关连的记录链接在一起，也用于行锁。
记录头部的变长部分包含了一个表示null 值的位向量(bit vector)。如果索引中可以为null的字段数量为N，这个位向量包含 N/8 向上取整的字节数。比例如果有9-16个字段可以为NULL值，这个位向量使用两个字节。为NULL的列不占用空间，只占用这个位向量中的位。头部的变长部分还包含了变长字段的长度。每个长度占用一个或者2个字节，这取决了字段的最大长度。如果所有列都可以为null 并且制定了固定长度，记录头部就没有变长部分。
对每个不为NULL的变长字段，记录头包含了一个字节或者两个字节的字段长度。只有当字段存储在外部的溢出区域或者字段最大长度超过255字节并且实际长度超过127个字节的时候会使用2个字节的记录头部。对应外部存储的字段，两个字节的长度指明内部存储部分的长度加上指向外部存储部分的20个字节的指针。内部部分是768字节，因此这个长度值为 768+20， 20个字节的指针存储了这个字段的真实长度。
NULL不占该部分任何空间，即NULL除了占用NULL标志位，实际存储不占任何空间。
记录头部跟着非空字段的数据部分。
聚簇索引的记录包含了所以用户定于的字段。另外还有一个6字节的事务ID列和一个7字节的回滚段指针。
如果没有定于主键索引，则聚簇索引还包括一个6字节的Row ID列。
每个辅助索引记录包含为群集索引键定义的不在辅助索引中的所有主键列。如果任何一个主键列是可变长度的，那么每个辅助索引的记录头都有一个可变长度的部分来记录它们的长度，即使辅助索引是在固定长度的列上定义的。
固定长度的字符字段比如CHAR(10)通过固定长度的格式存储，尾部填充空格。
对于变长的字符集，比如uft8mb3和utf8mb4， InnoDB试图用N字节来存储 CHAR(N)。如果CHAR(N)列的值的长度超过N字节，列后面的空格减少到最小值。CHAR(N)列值的最大长度是最大字符编码数 x N。比如utf8mb4字符集的最长编码为4，则列的最长字节数是 4*N。
插入text字段导致报错
创建测试表
[root@barret] [test]>create table user(id bigint not null primary key auto_increment, -> name varchar(20) not null default '' comment '姓名', -> age tinyint not null default 0 comment 'age', -> gender char(1) not null default 'M' comment '性别', -> info text not null comment '用户信息', -> create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', -> update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' -> ); Query OK, 0 rows affected (0.04 sec)
插入测试数据
root@barret] [test]>insert into user(name,age,gender,info) values('moon', 34, 'M', repeat('a',1024*1024*3));
ERROR 1406 (22001): Data too long for column 'info' at row 1
[root@barret] [test]>insert into user(name,age,gender,info) values('sky', 35, 'M', repeat('b',1024*1024*5));
ERROR 1301 (HY000): Result of repeat() was larger than max_allowed_packet (4194304) - truncated错误分析
[root@barret] [test]>select @@max_allowed_packet; +----------------------+ | @@max_allowed_packet | +----------------------+ | 4194304 | +----------------------+ 1 row in set (0.00 sec)
max_allowed_packet控制communication buffer最大尺寸，当发送的数据包大小超过该值就会报错，我们都知道，MySQL包括Server层和存储引擎，它们之间遵循2PC协议，Server层主要处理用户的请求：连接请求—>SQL语法分析—>语义检查—>生成执行计划—>执行计划—>fetch data；存储引擎层主要存储数据，提供数据读写接口。
max_allowed_packet=4M，当第一条insert repeat('a',1024*1024*3)，数据包Server执行SQL发送数据包到InnoDB层的时候，检查数据包大小没有超过限制4M，在InnoDB写数据时，发现超过了Text的限制导致报错。第二条insert的数据包大小超过限制4M，Server检测不通过报错。
引用AWS RDS参数组中该参数的描述
max_allowed_packet: This value by default is small, to catch large (possibly incorrect) packets. Must be increased if using large TEXT columns or long strings. As big as largest BLOB.
增加该参数的大小可以缓解报错，但是不能彻底的解决问题。
原文链接: https://www.yukx.com/alitec/article/details/2307.html 优科学习网为什么阿里巴巴不建议MySQL使用Text类型？
