MySQL中的表压缩功能

MySQL版本:8.0.22

os:linux ubuntu

语言:c++、c

在MySQL中支持3种类型的表压缩,依次为:传统压缩、TPC压缩、字典压缩。

第一种:传统压缩

传统的表压缩方式是在MySQL5.0.7之前使用的,现在已经废弃了,因为这种方式不但没有提升数据库的效率,反而降低了效率,导致buffer pool的使用率降低了。

create table时指定压缩后表的大小,即 KEY_BLOCK_SIZE 的大小,page默认大小为16KB。压缩是按page为单位进行压缩的。

SQL语句:

create table tt 
(
    c1 int primary key,
    c2 varchar(50)
) engine = innodb KEY_BLOCK_SIZE=8; 

KEY_BLOCK_SIZE 的取值为: 1,2,4,8,16

表示一个16KB的page压缩之后是8KB。如果一个page压缩之后的大小为9KB,那么需要2个8KB的page进行存储。如果一个page压缩之后的大小为6KB,那么只需要1个8KB的page进行存储即可。

KEY_BLOCK_SIZE的大小可以是 1、2、4、8、16,表示启用页压缩,然后按照 1K、2K、4K、8K、16K 的页大小存储数据。如果设置KEY_BLOCK_SIZE的大小为0,那么MySQL自动的将KEY_BLOCK_SIZE设置为innodb_page_size/2;

缺点:一个页在缓冲池中存在两个版本,压缩前的原始版本和压缩后的版本,这样导致缓冲池中能缓存page页的数量大大减少,在buffer pool中多产生一个page。对于一个16KB的page,一个存放的是原来的16KB的页数据,另一个是压缩后的page,压缩后以8KB为例,8KB中存放的是压缩后的数据再加上redo.log日志以及file header和file tailer部分。

因此,这种压缩方式会额外的多占用一个page用于存储压缩之后的page。

参考:MySQL :: MySQL 8.0 Reference Manual :: 15.9.1.2 Creating Compressed Tables

第二种:TPC压缩

TPC是Transparent page compression的简称,也就是 透明页压缩。这种方式是主流的压缩方式。

压缩是按page为单位进行压缩的,一个page的大小默认是16KB,也就是innodb page的默认大小,用于可以通过SQL : select @@innodb_page_size;查询page的大小;下面都采用一个page为16KB为单位。

SQL语句:

create table tt
(
	c1 int primary key,
	c2 varchar(128)
) engine = innodb compression=zlib;

create table tt
(
	c1 int primary key,
	c2 varchar(128)
) engine = innodb compression=lz4;

create table tt
(
	c1 int primary key,
	c2 varchar(128)
) engine = innodb compression=none;

建表时,compression=后面指定压缩的方式,支持下面3种写法:

COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}

其中zlib和lz4是支持的压缩方式,NONE表示不压缩

一个page变成dirty page之后,后台线程就会将该dirty page flush到磁盘文件中。确定要flush到磁盘文件时,先将该page进行压缩(lz4/zlib),压缩之后以9KB为例,那么剩余的7KB(16K-9K)就会被填充为0x00,然后flush到磁盘文件后,调用文件系统空洞(Hole Punch)特性(实际上是fallocate())对文件进行“裁剪”,释放 0x00 占用的稀疏空间,实际存放到磁盘上的文件大小为7KB。

当前linux的内核以及大部分的文件系统,例如:XFS、EXT4、ZFS、btrfs、NTFS 等,都支持文件空洞特性。

查看压缩后的文件占用的存储空间大小的SQL如下:

SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql/bmsql_customer';

FILE_SIZE:表示原始文件的大小

ALLOCATED_SIZE:表示压缩之后的大小

压缩率 = ALLOCATED_SIZE / FILE_SIZE

 参考:MySQL :: MySQL 8.0 Reference Manual :: 15.9.2 InnoDB Page Compression

第三种:字典压缩

基于字典的列压缩又叫压缩字典,但只适用于Percona分支。
优点是压缩率高, 每个列的数据类型都相同;

限制条件:
该列压缩方式仅用于InnoDB/XtraDB存储引擎,数据类型支持:
(1)、BLOB (including TINYBLOB, MEDIUMBLOB, LONGBLOG)
(2)、TEXT (including TINYTEXT, MEDUUMTEXT, LONGTEXT)
(3)、VARCHAR (including NATIONAL VARCHAR)
(4)、VARBINARY
(5)、JSON
应用于不受支持的列类型或存储引擎,则会报错。

使用MySQL自带的压缩键功能时的SQL语句:
在创建表语句或更改表语句中增加压缩标识
(1)、CREATE语句

CREATE TABLE ... (..., foo BLOB COLUMN_FORMAT COMPRESSED, ...);


(2)、ALTER语句

ALTER TABLE ... CHANGE [COLUMN] ... COLUMN_FORMAT COMPRESSED;

ALTER TABLE ... MODIFY [COLUMN] ... COLUMN_FORMAT COMPRESSED;

还有一种是用户自定义压缩键,也就是在在建表时指定压缩和压缩键。

SQL语句如下:

SET @dictionary_data = 'wall' 'apple' 'peach' 'orange';

CREATE COMPRESSION_DICTIONARY numbers (@dictionary_data);

CREATE TABLE tt(
        c1 INT,
        c2 text COLUMN_FORMAT COMPRESSED,
        c3 BLOB COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY numbers
      ) ENGINE=InnoDB;

c2采用MySQL默认的压缩键;

c3采用用户自定义的压缩键;

不过这种方式使用起来还是有争议的,如果用户用不好压缩键导致压缩率低。

其他参考文献:MySQL :: MySQL 8.0 Reference Manual :: 13.1.20 CREATE TABLE Statement

总之:

现在主流的压缩方式为:TPC压缩和基于字典键的压缩。

如有疑问请发邮件:zgaoq@163.com

  • 1
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: MySQL Workbench 默认是英文界面,要将其设置成文界面可以按如下步骤操作: 1. 打开 MySQL Workbench 2. 在菜单栏,点击「Edit」->「Preferences」 3. 在左侧栏,找到「Appearance」并点击 4. 在「Application Language」,选择「Chinese (Simplified)」 5. 点击「Apply」 6. 点击「OK」 7. 重新启动 MySQL Workbench,即可看到界面已经变成文 注意:设置重启应用才能生效,在某些版本可能存在翻译不完整的情况。 ### 回答2: MySQL Workbench 是一款常用的数据库管理工具,它可以帮助用户创建、管理和维护 MySQL 数据库MySQL Workbench 支持多种语言,包括英文、文、日文、法文等。本文将介绍如何将 MySQL Workbench 设置为文。 第一步,下载MySQL Workbench文版安装程序 用户可以在官网或第三方软件网站上下载 MySQL Workbench 文版安装程序,然后双击运行程序进行安装。安装过程需要选择文语言,并按照提示进行下一步操作。 第二步,使用语言包将MySQL Workbench切换到文 如果用户下载的是英文版 MySQL Workbench,可以通过使用语言包来将其切换到文。用户可以在官网或第三方软件网站上下载 MySQL Workbench 文语言包,将其解压缩后复制到 MySQL Workbench 安装目录下的 share 目录,如 C:\Program Files\MySQL\MySQL Workbench 8.0 CE\share。然后在 MySQL Workbench 应用界面的编辑菜单选择首选项,在左侧菜单选择外观,然后在语言选项选择文即可。 第三步,设置MySQL Workbench的语言环境变量 如果用户希望在 MySQL Workbench 使用文和其他语言切换,可以设置语言环境变量。用户可以在系统属性选择高级选项,然后在环境变量选择新建,将变量名设置为 LANG,变量值设置为 zh_CN.UTF-8。然后重启 MySQL Workbench 以应用更改。 总结 以上就是将 MySQL Workbench 设置为文的方法,用户可以根据自己的需求选择其的一种方法进行设置。无论使用哪种方法,都能使用户更加方便地管理 MySQL 数据库。 ### 回答3: MySQL Workbench是一个功能强大的MySQL数据管理工具,它的默认界面语言是英文。但是,如果你想将其语言设置成文,只需要按照以下步骤进行操作。 首先,打开MySQL Workbench,然后点击“Edit”菜单,选择“Preferences”选项,在打开的窗口选择“Appearance”选项。 在“Appearance”选项卡下,找到“Language”一项,将其设置成“Chinese(Simplified)”。 设置完成后,点击“Apply”按钮,然后关闭窗口并重新启动MySQL Workbench。你会发现,MySQL Workbench的界面语言已经变成了文。 在工作MySQL Workbench是一个非常好用的工具,而将其设置成文也能让我们更加方便和快捷地使用它。同时,如果你还想进一步熟悉和掌握MySQL的技能,也可以参考相关的教程和资源,帮助自己更好地应用这个工具。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值