InnoDB行格式 innodb_file_format 介绍

保持热爱奔赴山海

发布于 2019-09-17 14:24:15

1.1K0

发布于 2019-09-17 14:24:15

文章被收录于专栏：数据库相关

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 |
+-----------------+

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2018/08/01 ，如有侵权请联系 cloudcommunity@tencent.com 删除

http