mysql 大字段 blob_MySQL字段类型TEXT和BLOB的应用

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 和 VARBINARYBINARY和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

覆盖索引

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