MySQL优化之大字段longtext、text引发的生产问题

背景对接多个外部接口，需要保存请求参数以及返回参数，方便消息的补偿，因为多个外部接口，多个接口字段都不统一，整体使用一个大字段（longtext）进行存储，但是当数据只有40w的时候查询速度就非常慢长达40s左右。CREATE TABLE `risk_request_log_bak` (`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID

技术王老五

15971人浏览 · 2021-03-21 11:33:07

技术王老五 · 2021-03-21 11:33:07 发布

背景

对接多个外部接口，需要保存请求参数以及返回参数，方便消息的补偿，因为多个外部接口，多个接口字段都不统一，整体使用一个大字段（longtext）进行存储，但是当数据只有40w的时候查询速度就非常慢长达40s左右。

CREATE TABLE `risk_request_log_bak` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `risk_buss_no` varchar(32) DEFAULT NULL COMMENT '',
  `buss_no` varchar(32) DEFAULT NULL COMMENT '',
  `buss_order_no` varchar(32) DEFAULT NULL COMMENT '',
  `server_name` varchar(30) DEFAULT NULL COMMENT '',
  `url` varchar(500) DEFAULT NULL COMMENT '',
  `interface_code` varchar(10) DEFAULT NULL COMMENT '',
  `request_msg` longtext COMMENT '请求参数体',
  `response_msg` longtext COMMENT '响应参数体',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `remark` varchar(50) DEFAULT NULL COMMENT '',
  `resp_time` datetime DEFAULT NULL COMMENT '响应时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=451029 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='接';

解决策略：

万金油的策略加索引，需要查询的字段risk_buss_no上添加索引，速度由原来的5min以上，变为几百ms

思考：

40w的数据就算不加索引查询的时间40s左右也是不正常的。

尝试：

`request_msg` longtext COMMENT '请求参数体',
`response_msg` longtext COMMENT '响应参数体'

这两个字段设置成64位的数据之后，查询效率明显提升

原因：

为了清楚大字段对性能的影响，我们必须要知道innodb存储引擎在底层对行的处理方式：

知识点一：在5.1中，innodb存储引擎的默认的行格式为compact(redundant为兼容以前的版本），对于blob，text，varchar(8099)这样的大字段，innodb只会存放前768字节在数据页中，而剩余的数据则会存储在溢出段中（发生溢出情况的时候适用，不溢出的时候就全都存在数据行里）；
这里写图片描述

知识点二：innodb的块大小默认为16kb，由于innodb存储引擎表为索引组织表，树底层的叶子节点为一双向链表，因此每个页中至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8k（8098字节）；

知识点三：使用了blob数据类型，是不是一定就会存放在溢出段中？通常我们认为blob，clob这类的大对象的存储会把数据存放在数据页之外，其实不然，关键点还是要看一个page中到底能否存放两行数据，blob可以完全存放在数据页中(单行长度没有超过8098字节)，而varchar类型的也有可能存放在溢出页中(单行长度超过8098字节，前768字节存放在数据页中)；

知识点四：5.1中的innodb_plugin引入了新的文件格式：barracuda（将compact和redundant合称为antelope），该文件格式拥有新的两种行格式：compressed和dynamic，两种格式对blob字段采用完全溢出的方式，数据页中只存放20字节，其余的都存放在溢出段中:
这里写图片描述