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

背景

对接多个外部接口，需要保存请求参数以及返回参数，方便消息的补偿，因为多个外部接口，多个接口字段都不统一，整体使用一个大字段（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字节，其余的都存放在溢出段中:

知识点五：mysql在操作数据的时候，以page为单位，不管是更新，插入，删除一行数据，都需要将那行数据所在的page读到内存中，然后在进行操作，这样就存在一个命中率的问题，如果一个page中能够相对的存放足够多的行，那么命中率就会相对高一些，性能就会有提升；

查询一下字段的长度：

可以知道这个字段的平均长度大约在2.5kb

查看一下mysql的row_format

根据知识点四可以知道：数据页中只存放20字节，其余的都存放在溢出段中

实际：

1、innodb的data page默认是16K，在新数据写入的时候，会预留1/16的空间，用于后续的新纪录写入，减少频繁的新增怕个的开销

2、每个data page，至少要存储2行，因此理论上行的最大长度是8K，实际上因为因为一些的innodb内部数据结构导致每行要小于8K

3、结合上面的两点，为了保障良好的顺序写入，每个innodb最好有个自增的id，而且一个page页最好的填充率是1/2到15/16

4、当page少于两行，innodb会进行收缩，尽可能的释放空间，最主要的两种就是上面的知识点一和知识点四

结合上面的4点，我们知道一行的最大长度是2.5K远远小于8K，所以大字段的数据都会存到数据段中，而不会溢出到off page中，因此我们可以看出主要是由于大字段缓存到data page中，内存利用率很差，造成了大量的随机读。

主要的应对策略：

1、拆表，将大字段拆到另一个表中

2、索引，从访问密度较小的数据页改为访问密度很大的索引页，随机io转换为顺序io，同时内存命中率大大提升；

总结：核心思想是让单个page能够存放足够多的行，不断的提示内存的命中率

来源：https://blog.csdn.net/u013099854/article/details/115007969