关于后端:为什么阿里巴巴不建议MySQL使用Text类型

有情怀，有干货，微信搜寻【三太子敖丙】关注这个不一样的程序员。

本文 GitHub https://github.com/JavaFamily 已收录，有一线大厂面试残缺考点、材料以及我的系列文章。

前言

家喻户晓，MySQL 广泛应用于互联网的 OLTP（联机事务处理过程）业务零碎中，在大厂开发标准中，常常会看到一条 ” 不倡议应用 text 大字段类型”。

上面就从 text 类型的存储构造，引发的问题解释下为什么不倡议应用 text 类型，以及 Text 革新的倡议办法。

背景

写 log 表导致 DML 慢

问题形容

某歪有一个业务零碎，应用 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 等日志工具，没必要写入数据库。

文章最初我还会介绍几个 MySQL 我踩过 Text 相干的坑，这介绍坑之前我先介绍下 MySQLText 类型。

MySQL 中的 Text

Text 类型

text 是一个可能存储大量的数据的大对象，有四种类型：TINYTEXT, TEXT, MEDIUMTEXT,LONGTEXT，不同类型存储的值范畴不同，如下所示

其中 L 表是 text 类型中存储的理论长度的字节数。能够计算出 TEXT 类型最大存储长度 2 **16-1 = 65535 Bytes。

InnoDB 数据页

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 的行格局

Innodb 反对四种行格局：

因为 Dynamic 是 Compact 变异而来，构造大同而已，当初默认都是 Dynamic 格局；COMPRESSED 次要是对表和索引数据进行压缩，个别实用于使用率低的归档，备份类的需要，次要介绍下 REDUNDANT 和 COMPACT 行格局。

Redundant 行格局

这种格局为了兼容旧版本 MySQL。

行记录格局：

具备以下特点：

• 存储变长列的前 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 中。

Compact 行格局

这种行格局比 redundant 格局缩小了存储空间作为代价，然而会减少某些操作的 CPU 开销。如果零碎 workload 是受缓存命中率和磁盘速度限制，compact 行格局可能更快。如果你的工作负载受 CPU 速度限制，compact 行格局可能更慢，Compact 行格局被所有 file format 所反对。

行记录格局：

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 类型引发的问题

插入 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.

减少该参数的大小能够缓解报错，然而不能彻底的解决问题。

RDS 实例被锁定

背景形容

公司每个月都会做一些营销流动，有个服务 apush 流动推送，独自部署在高可用版的 RDS for MySQL 5.7，配置是 4C8G 150G 磁盘，数据库里也就 4 张表，早晨 22：00 上班走的时候，rds 实例数据应用了 50G 空间，第二天晚上 9：30 在地铁上收到钉钉告警短信，提醒 push 服务 rds 实例因为 disk is full 被 locked with —read-only，开发也反馈，利用日志报了一堆 MySQL error。

问题剖析

通过 DMS 登录到数据库，看一下那个表最大，发现有张表 push_log 占用了 100G+，看了下表构造，外面有两个 text 字段。

request text default ''comment' 申请信息 ',
response text default ''comment' 响应信息 'mysql>show  table status like'push_log'；

发现 Avg_row_length 根本都在 150KB 左右，Rows = 78w，表的大小约为 780000*150KB/1024/1024 = 111.5G。

通过主键 update 也很慢

insert into user(name,age,gender,info) values('thooo', 35, 'M', repeat('c',65535);
insert into user(name,age,gender,info) values('thooo11', 35, 'M', repeat('d',65535);
insert into user(name,age,gender,info) select name,age,gender,info from user;
Query OK, 6144 rows affected (5.62 sec)
Records: 6144  Duplicates: 0  Warnings: 0                                        
[root@barret] [test]>select count(*) from user;
+----------+
| count(*) |
+----------+
|    24576 |
+----------+
1 row in set (0.05 sec)

做 update 操作并跟踪。

mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> update user set info = repeat('f',65535) where id = 11;
Query OK, 1 row affected (0.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show profiles;
+----------+------------+--------------------------------------------------------+
| Query_ID | Duration   | Query                                                  |
+----------+------------+--------------------------------------------------------+
|        1 | 0.27874125 | update user set info = repeat('f',65535) where id = 11 |
+----------+------------+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show profile cpu,block io for query 1;  
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000124 | 0.000088 |   0.000035 |            0 |             0 |
| checking permissions | 0.000021 | 0.000014 |   0.000006 |            0 |             0 |
| Opening tables       | 0.000038 | 0.000026 |   0.000011 |            0 |             0 |
| init                 | 0.000067 | 0.000049 |   0.000020 |            0 |             0 |
| System lock          | 0.000076 | 0.000054 |   0.000021 |            0 |             0 |
| updating             | 0.244906 | 0.000000 |   0.015382 |            0 |         16392 |
| end                  | 0.000036 | 0.000000 |   0.000034 |            0 |             0 |
| query end            | 0.033040 | 0.000000 |   0.000393 |            0 |           136 |
| closing tables       | 0.000046 | 0.000000 |   0.000043 |            0 |             0 |
| freeing items        | 0.000298 | 0.000000 |   0.000053 |            0 |             0 |
| cleaning up          | 0.000092 | 0.000000 |   0.000092 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
11 rows in set, 1 warning (0.00 sec)

能够看到次要耗时在 updating 这一步，IO 输入次数 16392 次，在并发的表上通过 id 做 update，也会变得很慢。

group_concat 也会导致查问报错

在业务开发当中，常常有相似这样的需要，须要依据每个省份能够定点医保单位名称，通常实现如下：

select group_concat(dru_name) from t_drugstore group by province;

其中内置 group_concat 返回一个聚合的 string，最大长度由参数 group_concat_max_len（Maximum allowed result length in bytes for the GROUP_CONCAT()）决定，默认是 1024，个别都太短了，开发要求改长一点，例如 1024000。

当 group_concat 返回的后果集的大小超过 max_allowed_packet 限度的时候，程序会报错，这一点要额定留神。

MySQL 内置的 log 表

MySQL 中的日志表 mysql.general_log 和 mysql.slow_log，如果开启审计 audit 性能，同时 log_output=TABLE，就会有 mysql.audit_log 表，构造跟 mysql.general_log 大同小异。

别离看一下他们的表构造

CREATE TABLE `general_log` (`event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumblob NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

CREATE TABLE `slow_log` (`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

mysql.general_log 记录的是通过 MySQL Server 解决的所有的 SQL，包含后端和用户的，insert 比拟频繁，同时argument mediumblob NOT NULL，对 MySQL Server 性能有影响的，个别咱们在 dev 环境为了跟踪排查问题，能够开启 general_log，Production 环境禁止开启 general_log，能够开启 audit_log，它是在 general_log 的根底上做了一些 filter，比方我只须要业务账号发动的所有的 SQL，这个很有用的，很多时候须要剖析某一段时间内哪个 SQL 的 QPS，TPS 比拟高。

mysql.slow_log 记录的是执行超过 long_query_time 的所有 SQL，如果遵循 MySQL 开发标准，slow query 不会太多，然而开启了 log_queries_not_using_indexes=ON 就会有好多 full table scan 的 SQL 被记录，这时 slow_log 表会很大，对于 RDS 来说，个别只保留一天的数据，在频繁 insert into slow_log 的时候，做 truncate table slow_log 去清理 slow_log 会导致 MDL，影响 MySQL 稳定性。

倡议将 log_output=FILE，开启 slow_log，audit_log，这样就会将 slow_log，audit_log 写入文件，通过 Go API 解决这些文件将数据写入分布式列式数据库 clickhouse 中做统计分析。

Text 革新倡议

应用 es 存储

在 MySQL 中，个别 log 表会存储 text 类型保留 request 或 response 类的数据，用于接口调用失败时去手动排查问题，应用频繁的很低。能够思考写入本地 log file，通过 filebeat 抽取到 es 中，按天索引，依据数据保留策略进行清理。

应用对象存储

有些业务场景表用到 TEXT，BLOB 类型，存储的一些图片信息，比方商品的图片，更新频率比拟低，能够思考应用对象存储，例如阿里云的 OSS，AWS 的 S3 都能够，可能不便且高效的实现这类需要。

总结

因为 MySQL 是单过程多线程模型，一个 SQL 语句无奈利用多个 cpu core 去执行，这也就决定了 MySQL 比拟适宜 OLTP（特点：大量用户拜访、逻辑读，索引扫描，返回大量数据，SQL 简略）业务零碎，同时要针对 MySQL 去制订一些建模标准和开发标准，尽量避免应用 Text 类型，它岂但耗费大量的网络和 IO 带宽，同时在该表上的 DML 操作都会变得很慢。

另外倡议将简单的统计分析类的 SQL，倡议迁徙到实时数仓 OLAP 中，例如目前应用比拟多的 clickhouse，里云的 ADB，AWS 的 Redshift 都能够，做到 OLTP 和 OLAP 类业务 SQL 拆散，保障业务零碎的稳定性。

好啦以上就是本期的全部内容了，我是敖丙，你晓得的越多，你不晓得的越多，咱们下期见！

絮叨

敖丙把本人的面试文章整顿成了一本电子书，共 1630 页！

干货满满，字字精华。目录如下，还有我温习时总结的面试题以及简历模板，当初收费送给大家。

链接:https://pan.baidu.com/s/1ZQEKJBgtYle3v-1LimcSwg 明码:wjk6

我是敖丙，你晓得的越多，你不晓得的越多 ，感激各位人才的： 点赞 、 珍藏 和评论，咱们下期见！

文章继续更新，能够微信搜一搜「三太子敖丙 」第一工夫浏览，回复【 材料】有我筹备的一线大厂面试材料和简历模板，本文 GitHub https://github.com/JavaFamily 曾经收录，有大厂面试残缺考点，欢送 Star。