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

  • 时间:
  • 浏览:0

最近看过因此 老应用,在表特性的设计上使用了text将会blob的字段;其中有二个 应用,对blob字段的依赖非常的严重,查询和更新的频率也是非常的高,单表的存储空间将会达到了近200G,你两种已经 ,应用嘴笨 将会被数据库绑死了,任何应用将会查询逻辑的变更几乎成为不将会;

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

ref:MySQL Blob Compression performance benefits

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

知识点二:innodb的块大小默认为16kb,将会innodb存储引擎表为索引组织表,树底层的叶子节点为一双向链表,因此每个页中为宜应该有两行记录,这就决定了innodb在存储一行数据的已经 非要够超过8k(20098字节);

一.压缩:

二.拆分:

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

知识点一:在5.1中,innodb存储引擎的默认的行格式为compact(redundant为兼容已经 的版本),对于blob,text,varchar(20099)曾经的大字段,innodb只会存放前768字节在数据页中,而剩余的数据则会存储在溢出段中(处于溢出情况表的已经 适用);

知识点四:5.1中的innodb_plugin引入了新的文件格式:barracuda(将compact和redundant合称为antelope),该文件格式拥有新的并也有行格式:compressed和dynamic,并也有格式对blob字段采用完正溢出的法子,数据页中只存放20字节,其余的都存装入溢出段中:

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

知识点五:mysql在操作数据的已经 ,以page为单位,不管是更新,插入,删除一行数据,都需用将那行数据所在的page读到内存中,因此在进行操作,曾经就处于有二个 命中率的什么的难题,将会有二个 page中才能相对的存放足够多的行,没人 命中率就会相对高因此 ,性能就会有提升;

ref:Handling long texts/blobs in InnoDB – 1 to 1 relationship, covering index

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

ref:   Data compression in InnoDB for text and blob fields

xx_msg表将会将大字段单独装入另外一张表后,单行长度变的非常的小,page的行密度相比曾经的表大所以,曾经就才能缓存足够多的行,表上的多个select将会buffer pool的高命中率而受益;应用应用程序运行需用额外维护的是一张大字段的子表;

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

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

在知识点五中,mysql的io以page为单位,因此从从不的数据(大字段)也会随着需用操作的数据一起去去被读取到内存中来,曾经带来的什么的难题将会大字段会占用较大的内存(相比因此 小字段),使得内存利用率较差,造成更多的随机读取。

有了上面的知识点,让当我们让当我们让当我们让当我们让当我们让当我们 一起去看看该应用的特点,表特性:

为了清楚大字段对性能的影响,让当我们让当我们让当我们让当我们让当我们让当我们 需用要知道innodb存储引擎在底层对行的正确处理法子:

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

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

从上面的分析来看,让当我们让当我们让当我们让当我们让当我们让当我们 将会看过性能的瓶颈在于将会大字段存装入数据页中,造成了内存利用较差,带来太满的随机读,那为什么来优化掉你两种大字段的影响:

ref:《innodb 技术内幕》

知识点三:使用了blob数据类型,是后该 一定就会存装入溢出段中?通常让当我们让当我们让当我们让当我们让当我们让当我们 认为blob,clob类式 的大对象的存储会把数据存装入数据页之外,嘴笨 不然,关键点还是要看有二个 page中到底可不需用存放两行数据,blob可不需用完正存装入数据页中(单行长度没人 超过20098字节),而varchar类型的后该 将会存装入溢出页中(单行长度超过20098字节,前768字节存装入数据页中);

可不需用看过将会单行的平均长度(2.5k)还远小于有二个 innodb page的size(16k)(当已经 该 处于超过8k的行),也可是我知识点三中提到的,blob从不想存装入溢出段中,可是我存装入数据段中去,innodb才能将一行的所有列(包括longlob)存储在数据页中:

该表的应用场景包括:

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

在上面的有二个 查询当中,后该 查询表中的小字段,将会老的方案需用全表将会根据主键来定位表中的数据,因此还是以page为单位进行操作,blob字段处于还是会意味buffer pool命中率的下降,将会通过覆盖索引来优化上面的有二个 查询,索引和原表特性分开,从访问密度较小的数据页改为访问密度很大的索引页,随机io转换为顺序io,一起去内存命中率大大提升;额外的开销为数据库多维护有二个 索引的代价;

三.覆盖索引:

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