MySQL 不建议使用TEXT类型

本文主要是介绍MySQL 不建议使用TEXT类型，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL广泛应用于互联网的OLTP(联机事务处理过程)业务系统中，在大厂开发规范中，经常会看到一条"不建议使用text大字段类型”。

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支持四种行格式：

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分离，保证业务系统的稳定性。

参考：三太子敖丙

这篇关于MySQL 不建议使用TEXT类型的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！