Mysql优化 之修改大表为压缩表

在开发中，创建了需要备份60天数据的表‘machine_n01_history_archive’，为了提高备份效率，对该表进行特殊设计：

设计该表为压缩表

Mysql开发手册中指出，“In particular, compressed tables use less disk space and so require less disk I/O to read and write the data. Compression is available for all kinds of workloads with InnoDB tables, and for read-only MyISAM tables.”

并且指出压缩表不能用于系统表空间，如

Compressed tables can be created in file-per-table tablespaces or in general tablespaces. Table compression is not available for the InnoDB system tablespace. 

如何设置mysql innodb 表的压缩（Creating a Compressed Table in File-Per-Table Tablespace）：

第一，mysql的版本需要大于5.5；

 

第二，查看当前InnoDB的表空间；可以使用SHOW VARIABLES; 命令，查看innodb_file_per_table选项是否开启？如果没有开启，表示innodb将被创建于系统表空间中。可以在my.cnf or my.ini中修改该选项，也可以使用命令SET GLOBAL innodb_file_per_table=1;进行修改。

第三，create table或者alter talble 增加 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; 根据经验，一般压缩比例可以达到30%-40%。

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

如果指定有效的KEY_BLOCK_SIZE值，则可以省略ROW_FORMAT = COMPRESSED； 压缩会自动启用。
（innodb_file_format=Barracuda）

CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;