江苏龙网

  1. 主页 > 生活百科 > >

为啥阿里巴巴不建议MySQL使用Text类型？( 三 )

mysqlText类型

  • 如果没有定于主键索引，则聚簇索引还包括一个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>showtable 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: 6144Duplicates: 0Warnings: 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: 1Changed: 1Warnings: 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)


    推荐阅读


    上一篇：泰迪狗喜欢什么 泰迪犬有几种

    下一篇：海尔波轮洗衣机怎么清理里面的脏东西 小天鹅波轮洗衣机怎么清理里面的脏东西