创建压缩表
压缩表可以在每表文件表空间或常规表空间中创建。表压缩不适用于InnoDB 系统表空间。系统表空间(空间0,.ibdata文件)可以包含用户创建的表,但也包含内部系统数据,该数据永远不会被压缩。因此,压缩仅适用于每个表文件或常规表空间中存储的表(和索引)。
在每表文件表空间中创建压缩表
要在每个表文件表空间中创建压缩表,innodb_file_per_table
必须启用它(默认设置)。您可以在MySQL配置文件(my.cnf
或my.ini
)中设置此参数,也可以使用SET
语句动态设置此参数。
innodb_file_per_table
配置选项后,在或语句中指定ROW_FORMAT=COMPRESSED
子句或KEY_BLOCK_SIZE
子句,或两者都指定,以在每个表文件表空间中创建压缩表。CREATE TABLE
ALTER TABLE
例如,您可以使用以下语句:
SET GLOBAL innodb_file_per_table=1;CREATE TABLE t1 (c1 INTPRIMARY KEY )ROW_FORMAT =COMPRESSED KEY_BLOCK_SIZE =8;
在常规表空间中创建压缩表
要在常规表空间中创建压缩表,FILE_BLOCK_SIZE
必须为常规表空间定义,该常规表空间是在创建表空间时指定的。该FILE_BLOCK_SIZE
值必须是相对于该innodb_page_size
值的有效压缩页面大小,并且由CREATE TABLE
or 子句定义的压缩表的页面大小必须等于。例如,如果表的和,必须为8。有关更多信息,请参见“通用表空间”。ALTER TABLE
KEY_BLOCK_SIZE
FILE_BLOCK_SIZE/1024
innodb_page_size=16384
FILE_BLOCK_SIZE=8192
KEY_BLOCK_SIZE
下面的示例演示如何创建常规表空间并添加压缩表。该示例假定默认innodb_page_size
值为16K。在FILE_BLOCK_SIZE
8192要求压缩表有KEY_BLOCK_SIZE
8个。
mysql>CREATE TABLESPACE `ts2`ADD DATAFILE 'ts2.ibd'FILE_BLOCK_SIZE = 8192Engine =InnoDB; mysql>CREATE TABLE t4 (c1 INTPRIMARY KEY )TABLESPACE ts2ROW_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
文件的大小,也不会将磁盘空间返回给操作系统。有关更多信息,请参见“通用表空间”。 - 该
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
表空间的设置。有关更多信息,请参见“通用表空间”。 InnoDB
支持32KB和64KB页面大小,但是这些页面大小不支持压缩。有关更多信息,请参阅innodb_page_size
文档。InnoDB
数据页的默认未压缩大小为16KB。根据选项值的组合,MySQL对表空间数据文件(.ibd
file)使用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
页面大小的值。有关更多信息,请参见“通用表空间”。 - 表中的所有索引(包括聚集索引)都使用相同的页面大小进行压缩,如
CREATE TABLE
orALTER TABLE
语句中所指定。表属性(例如ROW_FORMAT
和)KEY_BLOCK_SIZE
不是表CREATE INDEX
语法的一部分InnoDB
,如果指定了表属性,则会将其忽略(尽管如果指定,它们将出现在SHOW CREATE TABLE
语句的输出中)。 - 有关与性能相关的配置选项,请参见“调整InnoDB表的压缩”。
压缩表的限制
- 压缩表不能存储在
InnoDB
系统表空间中。 - 通用表空间可以包含多个表,但是压缩表和未压缩表不能在同一通用表空间内共存。
- 尽管有子句name,压缩也适用于整个表及其所有相关索引,而不适用于单个行
ROW_FORMAT
。 InnoDB
不支持压缩的临时表。当innodb_strict_mode
启用(默认值),CREATE TEMPORARY TABLE
如果返回错误ROW_FORMAT=COMPRESSED
或KEY_BLOCK_SIZE
指定。如果innodb_strict_mode
禁用此选项,则会发出警告,并使用非压缩的行格式创建临时表。相同的限制适用于ALTER TABLE
对临时表的操作。