InnoDB行格式 innodb_file_format 介绍

• 2019 年 10 月 4 日
• 笔记

InnoDB行格式 innodb_file_format 对TEXT/BLOB的影响：

    摘录自：

        http://seanlook.com/2016/05/18/mysql-blob-row_format/

        http://hidba.org/?p=551

        https://www.percona.com/blog/2008/01/11/mysql-blob-compression-performance-benefits/

        https://www.percona.com/blog/2012/05/30/data-compression-in-innodb-for-text-and-blob-fields/

        http://blog.opskumu.com/mysql-blob.html   # MySQL大字段溢出导致数据回写失败

2.1 compact

    在 Antelope 两种行格式下，如果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，但相对来说能缓存行数就变少，也就是能缓存的索引值变少了，降低了索引效率。

2.2 dynamic

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

    压缩过程:

当使用压缩存储的页面，当Buffer Pool载入后，会将其解压。这时，该页面在Buffer Pool中同时存在“压缩版”和“解压版”。当Buffer Pool需要驱逐这些页的时候，有两种情况会发生：如果InnoDB认为当前应用是IO-Bound，相比CPU还有额外能力来做解压操作，则InnoDB选择仅驱逐页面的“解压版”；否则InnoDB会将页面的两个版本同时驱逐出去。

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

> show variables like 'innodb_file_format';   # mysql5.6 默认row_format格式 是 Antelope  +--------------------+----------+  | Variable_name      | Value    |  |--------------------+----------|  | innodb_file_format | Antelope |  +--------------------+----------+  > show table status like 'students'G  ***************************[ 1. row ]***************************  Name            | students  Engine          | InnoDB  Version         | 10  Row_format      | Compact  Rows            | 26  Avg_row_length  | 630  Data_length     | 16384  Max_data_length | 0  Index_length    | 0  Data_free       | 0  Auto_increment  | 101  Create_time     | 2017-01-16 17:29:34  Update_time     | None  Check_time      | None  Collation       | utf8_general_ci  Checksum        | None  Create_options  |  Comment         |  > show variables like 'innodb_file_format';  # mysql5.7 默认row_format格式 是 Barracuda  +--------------------+-----------+  | Variable_name      | Value     |  +--------------------+-----------+  | innodb_file_format | Barracuda |  +--------------------+-----------+  > show table status like 'aa'G  *************************** 1. row ***************************  Name: aa  Engine: InnoDB  Version: 10  Row_format: Dynamic  Rows: 4  Avg_row_length: 4096  Data_length: 16384  Max_data_length: 0  Index_length: 0  Data_free: 0  Auto_increment: NULL  Create_time: 2017-01-14 01:37:55  Update_time: 2017-01-14 01:50:39  Check_time: NULL  Collation: utf8_general_ci  Checksum: NULL  Create_options:  Comment:

行格式对磁盘空间的占用情况

示例：

MYSQL5.7.20

create database test;

use test;

[test] 22:02:42 > show variables like '%innodb%format%';  +---------------------------+-----------+  | Variable_name             | Value     |  +---------------------------+-----------+  | innodb_default_row_format | dynamic   |  | innodb_file_format        | Barracuda |  | innodb_file_format_check  | ON        |  | innodb_file_format_max    | Barracuda |  +---------------------------+-----------+  4 rows in set (0.01 sec)  [test] 22:01:57 > show create table sbtest3 G  *************************** 1. row ***************************  Table: sbtest3  Create Table: CREATE TABLE `sbtest3` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `k` int(10) unsigned NOT NULL DEFAULT '0',  `c` char(120) NOT NULL DEFAULT '',  `pad` char(60) NOT NULL DEFAULT '',  `dd` varchar(300) NOT NULL DEFAULT 'dd' COMMENT 'dd',  PRIMARY KEY (`id`)  ) ENGINE=InnoDB AUTO_INCREMENT=1891725 DEFAULT CHARSET=utf8  1 row in set (0.00 sec)  [test] 22:01:02 > select count(*)  from sbtest3;  +----------+  | count(*) |  +----------+  |  1815808 |  +----------+  1 row in set (52.57 sec)  [root@localhost /bdata/data/3306/data/test ]# l  total 409M  -rw-r-----. 1 mysql mysql   61 2018-07-03 21:52 db.opt  -rw-r-----. 1 root  root  8.5K 2018-07-31 21:44 sbtest3.frm  -rw-r-----. 1 root  root  408M 2018-07-31 21:57 sbtest3.ibd  alter table sbtest3  ROW_FORMAT=COMPRESSED ;  [test] 22:02:44 > show create table sbtest3 G  *************************** 1. row ***************************  Table: sbtest3  Create Table: CREATE TABLE `sbtest3` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `k` int(10) unsigned NOT NULL DEFAULT '0',  `c` char(120) NOT NULL DEFAULT '',  `pad` char(60) NOT NULL DEFAULT '',  `dd` varchar(300) NOT NULL DEFAULT 'dd' COMMENT 'dd',  PRIMARY KEY (`id`)  ) ENGINE=InnoDB AUTO_INCREMENT=1891725 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED  1 row in set (0.00 sec)  [root@localhost /bdata/data/3306/data/test ]# l  total 229M  -rw-r-----. 1 mysql mysql   61 2018-07-03 21:52 db.opt  -rw-r-----. 1 root  root  8.5K 2018-07-31 21:58 sbtest3.frm  -rw-r-----. 1 root  root  228M 2018-07-31 22:00 sbtest3.ibd  算了下，压缩比率还是挺高的，改成compressed行格式后，大约少了 40% 的体积。  [test] 22:02:56 > select (408-228) / 408;  +-----------------+  | (408-228) / 408 |  +-----------------+  |          0.4412 |  +-----------------+