前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >InnoDB行格式 innodb_file_format 介绍

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 一样。

代码语言:javascript
复制
> 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;

代码语言:javascript
复制
[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 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
文件存储
文件存储(Cloud File Storage,CFS)为您提供安全可靠、可扩展的共享文件存储服务。文件存储可与腾讯云服务器、容器服务、批量计算等服务搭配使用,为多个计算节点提供容量和性能可弹性扩展的高性能共享存储。腾讯云文件存储的管理界面简单、易使用,可实现对现有应用的无缝集成;按实际用量付费,为您节约成本,简化 IT 运维工作。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档