mysql压缩表语句_mysql8 参考手册--创建压缩表

压缩表可以在 每表文件表 空间或 常规表空间中创建。表压缩不适用于InnoDB 系统表空间。系统表空间(空间0， .ibdata文件)可以包含用户创建的表，但也包含内部系统数据，该数据永远不会被压缩。因此，压缩仅适用于每个表文件或常规表空间中存储的表(和索引)。

在每表文件表空间中创建压缩表

要在每个表文件表空间中创建压缩表， innodb_file_per_table必须启用它(默认设置)。您可以在MySQL配置文件(my.cnf或 my.ini)中设置此参数，也可以使用SET 语句动态 设置此参数。

innodb_file_per_table 配置选项 后，在或 语句中指定ROW_FORMAT=COMPRESSED子句或 KEY_BLOCK_SIZE子句，或两者都指定 ，以 在每个表文件表空间中创建压缩表。 CREATE TABLEALTER TABLE

例如，您可以使用以下语句：

SET GLOBAL innodb_file_per_table=1;

CREATE TABLE t1

(c1 INT PRIMARY KEY)

ROW_FORMAT=COMPRESSED

KEY_BLOCK_SIZE=8;

在常规表空间中创建压缩表

要在常规表空间中创建压缩表， FILE_BLOCK_SIZE必须为常规表空间定义，该常规表空间是在创建表空间时指定的。该FILE_BLOCK_SIZE值必须是相对于该innodb_page_size值的有效压缩页面大小 ，并且由CREATE TABLEor 子句定义的压缩表的页面大小 必须等于 。例如，如果 表的和 ， 必须为8。有关更多信息，请参见第15.6.3.3节“常规表空间”。 ALTER TABLE KEY_BLOCK_SIZEFILE_BLOCK_SIZE/1024innodb_page_size=16384FILE_BLOCK_SIZE=8192KEY_BLOCK_SIZE

下面的示例演示如何创建常规表空间并添加压缩表。该示例假定默认 innodb_page_size值为16K。在 FILE_BLOCK_SIZE8192要求压缩表有KEY_BLOCK_SIZE8个。

mysql> CREATE TABLESPACE ts2 ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

笔记

从MySQL 8.0开始，压缩表的表空间文件是使用物理页面大小而不是InnoDB页面大小创建的，这使得空压缩表的表空间文件的初始大小小于MySQL以前的版本。

如果指定ROW_FORMAT=COMPRESSED，则可以省略KEY_BLOCK_SIZE；该 KEY_BLOCK_SIZE设置默认为innodb_page_size值的一半。

如果指定有效值KEY_BLOCK_SIZE ，则可以省略 ROW_FORMAT=COMPRESSED; 自动启用压缩。

为了确定KEY_BLOCK_SIZE,通常的最佳值， 您通常使用相同的子句为该表创建多个副本，然后测量结果.ibd文件的大小， 并查看每个文件在实际工作负载下的性能如何 。对于常规表空间，请记住删除表不会减小常规表空间.ibd文件的大小 ，也不会将磁盘空间返回给操作系统。有关更多信息，请参见 第15.6.3.3节“常规表空间”。

该KEY_BLOCK_SIZE值被视为提示；InnoDB如有必要，可以使用其他大小 。对于每表文件表空间，KEY_BLOCK_SIZE只能小于或等于该 innodb_page_size值。如果指定的值大于该 innodb_page_size值，则将忽略指定的值，并发出警告，并将 KEY_BLOCK_SIZE其设置为该innodb_page_size值的一半 。如果为 innodb_strict_mode=ON，则指定无效KEY_BLOCK_SIZE值将返回错误。对于常规表空间，有效值 KEY_BLOCK_SIZE取决于FILE_BLOCK_SIZE表空间的 设置。有关更多信息，请参见 第15.6.3.3节“常规表空间”。

InnoDB支持32KB和64KB页面大小，但是这些页面大小不支持压缩。有关更多信息，请参阅 innodb_page_size 文档。

InnoDB 数据页 的默认未压缩大小为16KB。根据选项值的组合，MySQL对表空间数据文件(.ibdfile)使用1KB，2KB，4KB，8KB或16KB的页面大小。实际的压缩算法不受该KEY_BLOCK_SIZE值的影响 ；该值确定每个压缩块的大小，这反过来又影响到每个压缩页面可以包装多少行。

在每个表文件表空间中创建压缩表时，设置KEY_BLOCK_SIZE等于InnoDB 页面大小通常不会导致太多压缩。例如，设置 KEY_BLOCK_SIZE=16通常不会导致太多压缩，因为正常 InnoDB页面大小为16KB。此设置可能仍然是许多长条桌有用的 BLOB， VARCHAR或 TEXT列，因为这些值经常做压缩得很好，因此可能需要较少的溢出页中描述 第15.9.1.5，“如何压缩适用于InnoDB表”。对于常规表空间，不允许使用KEY_BLOCK_SIZE等于InnoDB页面大小的值。有关更多信息，请参见 第15.6.3.3节“常规表空间”。

表中的所有索引(包括 聚集索引)都使用相同的页面大小进行压缩，如CREATE TABLEor ALTER TABLE语句中所指定 。表属性(例如ROW_FORMAT和) KEY_BLOCK_SIZE不是表CREATE INDEX语法的 一部分 InnoDB，如果指定了表属性 ，则会将其忽略(尽管如果指定，它们将出现在SHOW CREATE TABLE语句的输出中)。

有关与性能相关的配置选项，请参见 第15.9.1.3节“为InnoDB表调整压缩”。

压缩表的限制

压缩表不能存储在 InnoDB系统表空间中。

通用表空间可以包含多个表，但是压缩表和未压缩表不能在同一通用表空间内共存。

尽管有子句name，压缩也适用于整个表及其所有相关索引，而不适用于单个行ROW_FORMAT。

InnoDB不支持压缩的临时表。当 innodb_strict_mode启用(默认值)， CREATE TEMPORARY TABLE如果返回错误 ROW_FORMAT=COMPRESSED或 KEY_BLOCK_SIZE指定。如果 innodb_strict_mode禁用此选项，则会发出警告，并使用非压缩的行格式创建临时表。相同的限制适用于ALTER TABLE对临时表的操作。