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能够存放足够多的行,不断的提示内存的命中率

 

 

 

MYSQL的日志顺序读写,数据文件随机读写以及linux底层原理

innodb使用大字段text,blob的一些优化建议

优化InnoDB表BLOB列的存储效率

行记录格式(Compact、Redundant、Compressed、Dynamic)、行溢出数据、CHAR的行结构存储

本文链接http://www.dzjqx.cn/news/show-617500.html