InnoDB行格式 innodb_file_format 介绍

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 |

+-----------------+

相关文章
相关标签/搜索