关于InnoDB存储引擎text和blob类型的优化

mysql>desc db_page;
+-----------------+----------------+----------------+---------------+-------------------+-----------------+
| Field           | Type           | Null           | Key           | Default           | Extra           |
+-----------------+----------------+----------------+---------------+-------------------+-----------------+
| id              | int(11)        | NO             | PRI           |                   | auto_increment  |
| title           | varchar(100)   | NO             |               |                   |                 |
| name            | varchar(100)   | YES            |               |                   |                 |
| content         | text           | YES            |               |                   |                 |
+-----------------+----------------+----------------+---------------+-------------------+-----------------+
mysql>show variables like "innodb_file_format";
+-------------------------+-----------------+
| Variable_name           | Value           |
+-------------------------+-----------------+
| innodb_file_format      | Barracuda       |
+-------------------------+-----------------+
mysql>show table status like "db_page" \G
*************************** 1. row ***************************
           Name: db_page
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 3
    Create_time: 2017-03-07 13:30:19
    Update_time: 
     Check_time: 
      Collation: utf8_general_ci
       Checksum: 
 Create_options: 
        Comment: 
   Block_format: Original

在 msyql 5.7.9 及以后版本，默认行格式由innodb_default_row_format变量决定，它的默认值是DYNAMIC，也可以在 create table 的时候指定ROW_FORMAT=DYNAMIC。

注意，如果要修改现有表的行模式为compressed或dynamic，必须先将文件格式设置成Barracuda：set global innodb_file_format=Barracuda;，再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;去修改才能生效，否则修改无效却无提示

二、对TEXT/BLOB这类大字段类型的影响

2.1 compact

变长大字段类型包括blob,text,varchar，其中varchar列值长度大于某数N时也会存溢出页，在latin1字符集下N值可以这样计算：innodb的块大小默认为16kb，由于innodb存储引擎表为索引组织表，树底层的叶子节点为一双向链表，因此每个页中至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8k，减去其它列值所占字节数，约等于N。对于InnoDB，内存是极为珍贵的，如果把768字节长度的blob都放在数据页，虽然可以节省部分IO，但是能缓存行数就变少，也就是能缓存的索引值变少了，降低了索引效率

2.2 dynamic

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里面可能会同时存储数据的压缩版和非压缩版，所以也多占用部分内存。这里 MySQL 5.6 Manual innodb-compression-internals 讲的十分清楚。

另外，由于ROW_FORMAT=DYNAMIC 和 ROW_FORMAT=COMPRESSED 是从 ROW_FORMAT=COMPACT 变化来的，所以他们处理 CHAR类型存储的方式和 COMPACT 一样。

三. 对TEXT/BLOB型字段存取优化

mysql的 io 以page为单位，因此不必要的数据（大字段）也会随着需要操作的数据一同被读取到内存中来，这样带来的问题由于大字段会占用较大的内存（相比其他小字段），使得内存利用率较差，造成更多的随机读取。从上面的分析来看，我们已经看到性能的瓶颈在于由于大字段存放在数据页中，造成了内存利用较差，带来过多的随机读，那怎么来优化掉这个大字段的影响

3.1 压缩&合并

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

b、可以把大字段用COMPRESS()压缩后再存为BLOB，或者在发送到MySQL前在应用程序中进行压缩

c、一张表有多个类blob字段，把它们组合起来如<TEXT><f_big_col1>long..</f_big_col1> <f_content>long..</f_content></TEXT>，再压缩存储

d、如果预期长度范围varchar就满足，就避免使用TEXT

3.2 拆分

将主表拆分为一对一的两个关联表，将大字段单独放到另外一张表后，单行长度变的非常的小，page的行密度相比原来的表大很多，这样就能够缓存足够多的行，buffer pool的命中率就会提高，应用程序需要额外维护的是一张大字段的子表，还可以通过覆盖索引来优化，将索引和原表结构分开，从访问密度较小的数据页改为访问密度很大的索引页，随机io转换为顺序io

总结：还是让单个page能够存放足够多的行，不断的提示内存的命中率，从数据库底层存储的原理出发，能够更深刻的优化数据库

综上，如果在实际业务中，确实需要在InnoDB表中存储BLOB、TEXT、长VARCHAR列时，有下面几点建议：

• 尽可能将所有数据序列化、压缩之后，存储在同一个列里，避免发生多次off-page

• 如果预期长度范围varchar就满足，就避免使用TEXT

• 如果无法将所有列整合到一个列，可以退而求其次，根据每个列最大长度进行排列组合后拆分成多个子表，尽量是的每个子表的总行长度小于8KB，减少发生off-page的频率

关于InnoDB存储引擎text和blob类型的优化

标签：总结   对象   for   性能   name   自增列   date   自适应   变化