MySQL字段类型TEXT和BLOB的应用

InnoDB行格式

Compact

  • Compac行格式如果blob列值长度 <= 768 bytes,就不会发生行溢出(page overflow),内容都在数据页(B-tree Node);如果列值长度 > 768字节,那么前768字节依然在数据页,而剩余的则放在溢出页(off-page),如下图:
  • 上面所讲的讲的blob或变长大字段类型包括blob,text,varchar,其中varchar列值长度大于某数N时也会存溢出页,在latin1字符集下N值可以这样计算:innodb的块大小默认为16kb,由于innodb存储引擎表为索引组织表,树底层的叶子节点为一双向链表,因此每个页中至少应该有两行记录,这就决定了innodb在存储一行数据的时候不能够超过8k,减去其它列值所占字节数,约等于N。
  • 我们知道对于InnoDB来说,内存是极为珍贵的,如果把768字节长度的blob都放在数据页,虽然可以节省部分IO,但相对来说能缓存行数就变少,也就是能缓存的索引值变少了,降低了索引效率。

Dynamic和Compressed

  • Dynamic行格式对blob采用完全行溢出,即聚集索引记录(数据页)只保留20字节的指针,指向真实存放它的溢出段地址
  • dynamic行格式,列存储是否放到off-page页,主要取决于行大小,它会把行中最长的那一列放到off-page,直到数据页能存放下两行。TEXT/BLOB列 <=40 bytes 时总是存放于数据页。这种方式可以避免compact那样把太多的大列值放到 B-tree Node,因为dynamic格式认为,只要大列值有部分数据放在off-page,那把整个值放入都放入off-page更有效
  • compressed 物理结构上与dynamic类似,但是对表的数据行使用zlib算法进行了压缩存储。在long blob列类型比较多的情况下用,可以降低off-page的使用,减少存储空间(一般40%左右),但要求更高的CPU,buffer pool里面可能会同时存储数据的压缩版和非压缩版,所以也多占用部分内存。

字符串数据类型

BINARY 和 VARBINARY

  • BINARY和VARBINARY与 CHAR和VARCHAR类型有点类似,不同的是BINARY和VARBINARY存储的是二进制的字符串,而非字符型字符串。也就是说,BINARY和VARBINARY没有字符集的概念,对其排序和比较都是按照二进制值进行对比。

  • BINARY(N)和VARBINARY(N)中的N指的是字节长度,而CHAR(N)和VARCHAR(N)中N指的是的字符长度。对于BINARY(10) ,其可存储的字节固定为10,而对于CHAR(10) ,其可存储的字节视字符集的情况而定。

BLOB and TEXT

  • 可以将BLOB 列视为VARBINARY、将 TEXT列视为VARCHAR
  • 对于索引BLOB和 TEXT列,必须指定索引前缀长度
  • BLOB和TEXT列不能有DEFAULT值
  • BLOB 可以储存图片,TEXT只能储存纯文本文件
  • 排序只对每个列的最前max_sort_length字节而不是整个字符串排序
  • MEMORY存储引擎不支持BLOB和TEXT,如果查询使用了BLOB或TEXT列并且需要使用临时表,则会使用磁盘临时表,导致性能下降(5.7.5开始,新增一个系统选项 internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为InnoDB)

存取和优化

拆分

将大字段从频繁读取和更新的表中拆分出去

压缩

将数据压缩后再存为BLOB, 使用CPU换取内存和IO

覆盖索引

常用查询字段建立索引,直接在索引上面完成查询,不需要将大字段读读取

参考
  1. InnoDB行格式对text/blob大变长字段的影响