最近看到一些老应用,在表结构的设计上使用了text或者blob的字段；其中一个应用，对blob字段的依赖非常的严重，查询和更新的频率也是非常的高，单表的存储空间已经达到了近100G，这个时候，应用其实已经被数据库绑死了，任何应用或者查询逻辑的变更几乎成为不可能；
为了清楚大字段对性能的影响，我们必须要知道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中能够相对的存放足够多的行，那么命中率就会相对高一些，性能就会有提升；
有了上面的知识点，我们一起看看该应用的特点，表结构：
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=gbkcol_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）存储在数据页中：
在知识点五中，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=gbkxx_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，此时不能再继续行溢出了，因此只能报错。