关于后端:为什么阿里巴巴不建议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: 0mysql> 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。