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

时间:2021-09-26 07:33:49

最近看到一些老应用,在表结构的设计上使用了text或者blob的字段;其中一个应用,对blob字段的依赖非常的严重,查询和更新的频率也是非常的高,单表的存储空间已经达到了近100G,这个时候,应用其实已经被数据库绑死了,任何应用或者查询逻辑的变更几乎成为不可能;

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

知识点一:在5.1中,innodb存储引擎的默认的行格式为compact(redundant为兼容以前的版本),对于blob,text,varchar(8099)这样的大字段,innodb只会存放前768字节在数据页中,而剩余的数据则会存储在溢出段中(发生溢出情况的时候适用,不溢出的时候就全都存在数据行里);
innodb使用大字段text blob的一些优化建议

知识点二: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字节,其余的都存放在溢出段中:
innodb使用大字段text blob的一些优化建议

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

有了上面的知识点,我们一起看看该应用的特点,表结构:

CREATE TABLE `xx_msg` (
`col_user` VARCHAR(64) NOT NULL,
`col_smallint` SMALLINT(6) NOT NULL,
`col_lob` longblob,
`gmt_create` datetime DEFAULT NULL,
`gmt_modified` datetime DEFAULT NULL,
PRIMARY KEY (`xxx`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

col_lob为blob字段,用于存放该用户的所有的消息,其平均长度在2.4kb左右,该表中其他剩余的字段则是非常的小,大致在60字节左右

SELECT avg(LENGTH(col_clob)) FROM (SELECT * fromxxx_msg LIMIT 30000)a;
| 2473.8472 |

该表的应用场景包括:

1) select col_user ,col_smallint,DATE_FORMAT(gmt_modified,’%Y-%m-%d’) from xx_msg;

2) update xx_msg set gmt_modified=’2012-03-31 23:16:30′,col_smallint=1,col_lob=’xxx’ where col_user=’xxx’;

3) select col_smallint from xx_msg where user=’xxx’;

可以看到由于单行的平均长度(2.5k)还远小于一个innodb page的size(16k)(当然也有存在超过8k的行),也就是知识点三中提到的,blob并不会存放到溢出段中,而是存放到数据段中去,innodb能够将一行的所有列(包括longlob)存储在数据页中:

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

在知识点五中,mysql的io以page为单位,因此不必要的数据(大字段)也会随着需要操作的数据一同被读取到内存中来,这样带来的问题由于大字段会占用较大的内存(相比其他小字段),使得内存利用率较差,造成更多的随机读取。

从上面的分析来看,我们已经看到性能的瓶颈在于由于大字段存放在数据页中,造成了内存利用较差,带来过多的随机读,那怎么来优化掉这个大字段的影响:

一.压缩:
在知识点四中,innodb提供了barracuda文件格式,将大字段完全存放在溢出段中,数据段中只存放20个字节,这样就大大的减小了数据页的空间占用,使得一个数据页能够存放更多的数据行,也就提高了内存的命中率(对于本实例,大多数行的长度并没有超过8k,所以优化的幅度有限);如果对溢出段的数据进行压缩,那么在空间使用上也会大大的降低,具体的的压缩比率可以设置key_blok_size来实现。

二.拆分:

将主表拆分为一对一的两个关联表:

CREATE TABLE `xx_msg` (
`col_user` VARCHAR(64) NOT NULL,
`col_smallint` SMALLINT(6) NOT NULL,
`gmt_create` datetime DEFAULT NULL,
`gmt_modified` datetime DEFAULT NULL,
PRIMARY KEY (`xxx`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;


CREATE TABLE `xx_msg_lob` (
`col_user` VARCHAR(64) NOT NULL,
`col_lob` longblob,
PRIMARY KEY (`xxx`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

xx_msg表由于将大字段单独放到另外一张表后,单行长度变的非常的小,page的行密度相比原来的表大很多,这样就能够缓存足够多的行,表上的多个select由于buffer pool的高命中率而受益;应用程序需要额外维护的是一张大字段的子表;

三.覆盖索引:

在上面的两个查询当中,都是查询表中的小字段,由于老的方案需要全表或者根据主键来定位表中的数据,但是还是以page为单位进行操作,blob字段存在还是会导致buffer pool命中率的下降,如果通过覆盖索引来优化上面的两个查询,索引和原表结构分开,从访问密度较小的数据页改为访问密度很大的索引页,随机io转换为顺序io,同时内存命中率大大提升;额外的开销为数据库多维护一个索引的代价;

alter table xx_msg add index ind_msg(col_user ,col_smallint,gmt_modified);

对于查询一,原来的执行计划为走全表扫描,现在通过全索引扫描来完成查询;

对于查询二,原来的执行计划为走主键PK来定位数据,现在该走覆盖索引ind_msg完成查询;

注意上面的两个查询为了稳固执行计划,需要在sql执行中加入hint提示符来强制sql通过索引来完成查询;

总结:上面三种思路来优化大字段,其核心思想还是让单个page能够存放足够多的行,不断的提示内存的命中率,尽管方法不同,但条条大路通罗马,从数据库底层存储的原理出发,能够更深刻的优化数据库,扬长避短,达到意想不到的效果。

转自:https://yq.aliyun.com/articles/9072

=======================================================
以下摘自叶老师博客:http://imysql.cn/2014/09/28/mysql-optimization-case-blob-stored-in-innodb-optimization.shtml

当行格式为DYNAMIC或COMPRESSED时,TEXT/BLOB之类的长列(long column,也有可能是其他较长的列,不一定只有TEXT/BLOB类型,看具体情况)会完全存储在一个独立的data page里,聚集索引页中只使用20字节的指针指向新的page,这就是所谓的off-page,类似ORACLE的行迁移,磁盘空间浪费较严重,且I/O性能也较差。因此,强烈不建议使用BLOB、TEXT、超过255长度的VARCHAR列类型;

当InnoDB的文件格式(innodb_file_format)设置为Antelope,并且行格式为COMPACT 或 REDUNDANT 时,BLOB、TEXT或者长VARCHAR列只会将其前768字节存储在聚集索页中(最大768字节的作用是便于创建前缀索引/prefix index),其余更多的内容存储在额外的page里,哪怕只是多了一个字节。因此,所有列长度越短越好;

在off-page中存储的BLOB、TEXT或者长VARCHAR列的page是独享的,不能共享。因此强烈不建议在一个表中使用多个长列。

==============================================================
以下是自己搜到的关于行溢出的解释:

行溢出
innodb采用B+树结构,每个数据页至少要存储两个记录(否则变成了链表),如果不能满足这个需求,则会出现行溢出,innodb只保留字段前20字节

(pre-plugin保留768字节),剩余数据分配新的数据页uncompress blob page;
行溢出只与记录长度有关系,即便行包含blob或text,若一个数据页可存储两条记录,也不会出现行溢出;
具体可参看《MySQL技术内幕-InnoDB存储引擎的》的4.4章节 innodb行记录格式;

对于pre-plugin,行溢出时保留每个字段的前768字节,如果保留的字节总数仍然超出数据页的一半大小,就会直接报错1030- Got error 139 from storage engine。譬如一个表建立11个text或varachar(1000)字段且行记录长度>8k,则会产生行溢出,保留在原数据页的记录长度=11*768>8k,此时不能再继续行溢出了,因此只能报错。