InnoDB表压缩
InnoDB表压缩，它支持在file_per_table表空间或普通表空间中的InnoDB表。使用ROW_FORMAT=COMPRESSED属性与CREATE Table或ALTER Table一起启用表压缩。
表压缩概述
InnoDB表压缩，它支持在file_per_table表空间或普通表空间中的InnoDB表。使用ROW_FORMAT=COMPRESSED属性与CREATE Table或ALTER Table一起启用表压缩。
表压缩概述
由于处理器和缓存内存的速度比磁盘存储设备提高得更快，因此许多工作负载都是磁盘绑定的。数据压缩可以实现更小的数据库大小、更少的I/O和更高的吞吐量，而增加CPU利用率的代价很小。压缩对于读密集型应用程序尤其有价值，因为系统有足够的RAM将经常使用的数据保存在内存中。
使用ROW_FORMAT=COMPRESSED创建的InnoDB表可以在磁盘上使用比配置的innodb_page_size值更小的页面大小。更小的页面需要更少的I/O来读写磁盘，这对于SSD设备来说特别有价值。
压缩后的页面大小通过CREATE TABLE或ALTER TABLE KEY_BLOCK_SIZE参数指定。不同的页面大小要求将表放在file-per-table表空间或通用表空间中，而不是放在系统表空间中，因为系统表空间不能存储压缩表。
无论KEY_BLOCK_SIZE值如何，压缩级别都是相同的。当您为KEY_BLOCK_SIZE指定较小的值时，您将获得越来越小的页面所带来的I/O好处。但是，如果指定的值太小，则在数据值无法压缩到足以容纳每个页中的多行时，会有重新组织页的额外开销。表的KEY_BLOCK_SIZE可以有多小是有严格限制的，这取决于每个索引的键列的长度。如果指定的值太小，CREATE TABLE或ALTER TABLE语句将失败。
在缓冲池中，压缩数据保存在小页面中，页面大小基于KEY_BLOCK_SIZE值。为了提取或更新列值，MySQL还使用未压缩的数据在缓冲池中创建一个未压缩的页面。在缓冲池中，对未压缩页面的任何更新也会被重新写回等效的压缩页面。您可能需要调整缓冲池的大小，以容纳压缩和未压缩页面的附加数据，尽管在需要空间时将未压缩页面从缓冲池中取出，然后在下一次访问时再次解压缩。
创建压缩表
压缩表可以在file-per-table表空间中创建，也可以在一般表空间中创建。InnoDB系统表空间不支持表压缩。系统表空间(空间0，.ibdata文件)可以包含用户创建的表，但它也包含内部系统数据，这些数据从未被压缩过。因此，压缩只适用于存储在file-per-table或一般表空间中的表(和索引)。
在file-per-table表空间中创建压缩表
要在file-per-table表空间中创建压缩表，innodb_file_per_table必须启用(MySQL 5.6.6默认设置)，innodb_file_format必须设置为Barracuda。您可以在MySQL配置文件(my.cnf或my.ini)中设置这些参数，也可以使用set语句动态设置这些参数
在配置了innodb_file_per_table和innodb_file_format选项后，在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT=COMPRESSED或KEY_BLOCK_SIZE子句，或两者都指定，以在file-per-table表空间中创建压缩表。
例如，你可以使用以下语句:
mysql> SET GLOBAL innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE t1
-> (c1 INT PRIMARY KEY)
-> ROW_FORMAT=COMPRESSED
-> KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.01 sec)
在通用表空间中创建压缩表
要在通用表空间中创建压缩表，必须为通用表空间定义FILE_BLOCK_SIZE，该值在创建表空间时指定。FILE_BLOCK_SIZE值必须是相对于innodb_page_size值的有效压缩页大小，并且由CREATE table或ALTER table KEY_BLOCK_SIZE子句定义的压缩表的页大小必须等于FILE_BLOCK_SIZE/1024。例如，innodb_page_size=16384, FILE_BLOCK_SIZE=8192，则表的KEY_BLOCK_SIZE必须为8。
下面的示例演示创建一个通用表空间和添加一个压缩表。本例innodb_page_size的默认值为16K。FILE_BLOCK_SIZE为8192要求压缩表的KEY_BLOCK_SIZE为8。
mysql> show variables like 'innodb_page_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.02 sec) mysql> CREATE TABLESPACE ts3 ADD DATAFILE 'ts3.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts3 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; Query OK, 0 rows affected (0.01 sec)
注意:
.如果指定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可以使用不同的大小。对于file-per-table表空间，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设置。
.32k和64k页面大小不支持压缩。
.InnoDB数据页的默认未压缩大小是16KB。根据选项值的组合，MySQL使用1KB、2KB、4KB、8KB或16KB的页大小来存放表空间数据文件(.ibd文件)。实际的压缩算法不受KEY_BLOCK_SIZE值的影响;该值决定每个压缩块的大小，进而影响每个压缩页中可以装入多少行。
.当在file-per-table表空间中创建压缩表时，将KEY_BLOCK_SIZE设置为InnoDB页面大小通常不会导致太多压缩。例如，设置KEY_BLOCK_SIZE=16通常不会导致太多的压缩，因为正常的InnoDB页面大小是16KB。此设置对于具有许多长BLOB、VARCHAR或TEXT列的表可能仍然有用，因为这些值通常可以很好地压缩，因此可能需要更少的溢出页。对于一般的表空间，KEY_BLOCK_SIZE值等于InnoDB页面大小是不允许的。
.表的所有索引(包括聚集索引)使用相同的页面大小进行压缩，这在CREATE table或ALTER table语句中指定。表属性如ROW_FORMAT和KEY_BLOCK_SIZE不是InnoDB表的CREATE INDEX语法的一部分，如果指定了，它们将被忽略(尽管，如果指定了，它们将出现在SHOW CREATE Table语句的输出中)。
压缩表的限制
.5.1之前的MySQL版本不能处理压缩表。
.压缩表不能存储在InnoDB系统表空间中。
.一般表空间可以包含多个表，但是压缩表和未压缩表不能在同一个表空间中共存。
.压缩适用于整个表及其所有关联索引，而不是单独的行，尽管子句名为ROW_FORMAT。
InnoDB表压缩调优
大多数情况下，InnoDB数据存储和压缩中描述的内部优化可以确保系统在压缩数据下运行良好。但是，由于压缩的效率取决于数据的性质，因此您可以做出影响压缩表性能的决定:
.要压缩哪些表。
.要使用的压缩页面大小。
.是否根据运行时性能特征(例如系统压缩和解压缩数据所花费的时间)调整缓冲池的大小。工作负载更像数据仓库(主要是查询)还是OLTP系统(查询和DML的混合)。
.如果系统在压缩表上执行DML操作，并且数据分布的方式在运行时导致代价高昂的压缩失败，那么您可能需要调整其他高级配置选项。
何时使用压缩
一般来说，压缩在包含合理数量的字符串列的表上效果最好，并且数据的读取次数远远多于写入次数。因为没有保证的方法来预测压缩是否有利于特定情况，所以总是使用在代表性配置上运行的特定工作负载和数据集进行测试。在决定压缩哪些表时，请考虑以下因素。
数据特性与压缩
在压缩数据文件时，决定压缩效率的一个关键因素是数据本身的性质。回想一下，压缩的工作原理是识别数据块中重复的字节串。完全随机的数据是最糟糕的情况。典型的数据通常有重复的值，因此压缩效果很好。无论是定义在CHAR、VARCHAR、TEXT还是BLOB列中的字符串，通常压缩效果都很好。另一方面，主要包含二进制数据(整数或浮点数)或之前压缩过的数据(例如JPEG或PNG图像)的表通常不能很好地压缩，或者显著压缩，或者根本压缩不了。
你可以选择是否为每个InnoDB表开启压缩。一张表及其所有索引使用相同的(压缩的)页面大小。可能是包含表中所有列数据的主键(聚集)索引比辅助索引压缩更有效。对于那些行很长的情况，使用压缩可能会导致长列值被存储在“页外”，那些溢出的页面可以很好地压缩。考虑到这些因素，对于许多应用程序，有些表的压缩比其他表的压缩更有效，并且您可能会发现只有在压缩了一部分表的情况下，您的工作负载才能达到最佳性能。
要确定是否要压缩一个特定的表，可以进行实验。通过使用对未压缩表的.ibd文件副本实现LZ77压缩的实用程序(如gzip或WinZip)，您可以粗略估计数据压缩的效率。与基于文件的压缩工具相比，MySQL压缩表的压缩更少，因为MySQL根据页面大小(默认为16KB)分块压缩数据。除了用户数据之外，页面格式还包括一些未压缩的内部系统数据。基于文件的压缩工具可以检查更大的数据块，因此可能会在一个巨大的文件中发现更多重复的字符串比MySQL在单个页面中所能找到的多。
在特定表上测试压缩的另一种方法是将一些数据从未压缩的表复制到一个类似的、压缩的表(具有相同的索引)中，并查看生成的.ibd文件的大小。
例如:
mysql> use undo
Database changed
mysql> SET GLOBAL innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SET GLOBAL autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE big_table AS SELECT * FROM information_schema.columns;
Query OK, 3249 rows affected, 1 warning (0.52 sec)
Records: 3249 Duplicates: 0 Warnings: 1
mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 3249 rows affected (0.05 sec)
Records: 3249 Duplicates: 0 Warnings: 0
mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 6498 rows affected (0.10 sec)
Records: 6498 Duplicates: 0 Warnings: 0
mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 12996 rows affected (0.20 sec)
Records: 12996 Duplicates: 0 Warnings: 0
mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 25992 rows affected (0.51 sec)
Records: 25992 Duplicates: 0 Warnings: 0
mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 51984 rows affected (0.78 sec)
Records: 51984 Duplicates: 0 Warnings: 0
mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 103968 rows affected (1.40 sec)
Records: 103968 Duplicates: 0 Warnings: 0
mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 207936 rows affected (2.84 sec)
Records: 207936 Duplicates: 0 Warnings: 0
mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 415872 rows affected (5.72 sec)
Records: 415872 Duplicates: 0 Warnings: 0
mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 831744 rows affected (11.46 sec)
Records: 831744 Duplicates: 0 Warnings: 0
mysql> INSERT INTO big_table SELECT * FROM big_table;
Query OK, 1663488 rows affected (22.86 sec)
Records: 1663488 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment;
Query OK, 0 rows affected (48.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE big_table\G
*************************** 1. row ***************************
Table: big_table
Create Table: CREATE TABLE `big_table` (
`TABLE_CATALOG` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`COLUMN_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
`COLUMN_DEFAULT` longtext CHARACTER SET utf8,
`IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',
`DATA_TYPE` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
`COLLATION_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
`COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,
`COLUMN_KEY` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',
`EXTRA` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',
`PRIVILEGES` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',
`COLUMN_COMMENT` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT '',
`GENERATION_EXPRESSION` longtext CHARACTER SET utf8 NOT NULL,
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3326977 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> select count(id) from big_table;
+-----------+
| count(id) |
+-----------+
| 3326976 |
+-----------+
1 row in set (1.09 sec)
mysql> \! ls -l /mysqldata/mysql/undo/big_table.ibd
-rw-r-----. 1 mysql mysql 708837376 Jul 6 18:06 /mysqldata/mysql/undo/big_table.ibd
mysql> CREATE TABLE key_block_size_4 LIKE big_table;
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO key_block_size_4 SELECT * FROM big_table;
Query OK, 3326976 rows affected (2 min 52.11 sec)
Records: 3326976 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> \! ls -l /mysqldata/mysql/undo/key_block_size_4.ibd
-rw-r-----. 1 mysql mysql 155189248 Jul 6 18:11 /mysqldata/mysql/undo/key_block_size_4.ibd
这个实验产生了以下数字，当然，根据您的表结构和数据，这些数字可能会有很大的不同:
-rw-r-----. 1 mysql mysql 708837376 Jul 6 18:06 /mysqldata/mysql/undo/big_table.ibd
-rw-r-----. 1 mysql mysql 155189248 Jul 6 18:11 /mysqldata/mysql/undo/key_block_size_4.ibd
要查看压缩对您的特定工作负载是否有效:
.对于简单的测试，使用一个没有其他压缩表的MySQL实例，并对INFORMATION_SCHEMA.INNODB_CMP表进行查询。
.对于涉及多个压缩表的工作负载的更复杂的测试，可以对INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表进行查询。因为收集INNODB_CMP_PER_INDEX表中的统计信息非常昂贵，所以你必须在查询这张表之前启用配置选项innodb_cmp_per_index_enabled，并且你可以将这样的测试限制在开发服务器或非关键的从服务器上。
.对正在测试的压缩表运行一些典型的SQL语句。
.通过查询INFORMATION_SCHEMA.INNODB_CMP或INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表，并比较COMPRESS_OPS和COMPRESS_OPS_OK,可以查看成功压缩操作与总体压缩操作的比率。
.如果成功完成的压缩操作的百分比很高，则该表可能是压缩的一个很好的候选者。
.如果压缩失败的比例很高，您可以调整innodb_compression_level、innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max选项，并尝试进一步的测试。
数据库压缩与应用程序压缩
决定压缩应用程序中的数据还是表中的数据;不要对同一数据同时使用两种压缩方式。当压缩应用程序中的数据并将结果存储在压缩表中时，几乎不可能节省额外的空间，而双重压缩只会浪费CPU周期。
在数据库中压缩
当启用时，MySQL表压缩是自动的，并且适用于所有列和索引值。这些列仍然可以使用LIKE之类的操作符进行测试，即使索引值被压缩了，排序操作仍然可以使用索引。因为索引通常占数据库总大小的很大一部分，所以压缩可以显著节省存储、I/O或处理器时间。压缩和解压缩操作发生在数据库服务器上，这可能是一个强大的系统，其大小可以处理预期的负载。
应用程序压缩
如果在应用程序中压缩文本等数据，在将其插入数据库之前，通过压缩某些列而不压缩其他列，可以为压缩效果不好的数据节省开销。这种方法使用CPU周期在客户机机器上而不是数据库服务器上进行压缩和解压缩，这可能适用于具有许多客户机的分布式应用程序，或者客户机机器有空闲CPU周期的情况。
混合方法
当然，也可以结合使用这两种方法。对于某些应用程序，可能适合使用一些压缩表和一些非压缩表。最好从外部压缩一些数据(并将其存储在未压缩的表中)，并允许MySQL压缩应用程序中的其他表。与往常一样，预先设计和真实的测试对于做出正确的决定是有价值的。
工作负载特征和压缩
除了选择要压缩的表(和页面大小)之外，工作负载是性能的另一个关键决定因素。如果应用程序主要是读操作，而不是更新操作，那么在索引页耗尽MySQL为压缩数据维护的每页“修改日志”的空间后，需要重新组织和重新压缩的页面就会更少。如果更新主要是更改非索引的列，或者那些包含blob或大字符串的列，它们恰巧存储在“页外”，那么压缩的开销是可以接受的。如果对表的唯一更改是使用单调递增主键的插入，并且辅助索引很少，那么就不需要重新组织和重新压缩索引页。由于MySQL可以通过修改未压缩的数据“就地”删除压缩页上的行，因此表上的删除操作是相对高效的。
对于某些环境，加载数据所需的时间可能与运行时检索同样重要。特别是在数据仓库环境中，许多表可能是只读的或大多数情况下是只读的。在这些情况下，以增加加载时间为代价进行压缩可能是可接受的，也可能是不可接受的，除非由此减少磁盘读取或存储成本的节省非常显著。
基本上，当CPU时间可以用于压缩和解压缩数据时，压缩工作得最好。因此，如果您的工作负载是I/O绑定的，而不是cpu绑定的，您可能会发现压缩可以提高整体性能。当您使用不同的压缩配置测试应用程序性能时，请在与生产系统的计划配置类似的平台上进行测试。
配置特点及压缩
从磁盘读写数据库页面是系统性能最慢的方面。压缩试图通过使用CPU时间来压缩和解压缩数据来减少I/O，当I/O相对于处理器周期来说是一种稀缺资源时，这种方法最有效。
当运行在具有快速多核cpu的多用户环境中时，情况尤其如此。当压缩表的一页在内存中时，MySQL通常会使用额外的内存，通常是16KB，在缓冲池中用于页面的未压缩副本。自适应LRU算法试图平衡压缩页和未压缩页之间的内存使用，以考虑工作负载是以I/ o绑定方式还是cpu绑定方式运行。尽管如此，在使用压缩表时，为缓冲池提供更多内存的配置往往比内存高度受限的配置运行得更好。
选择压缩页面大小
压缩页大小的最佳设置取决于表及其索引包含的数据的类型和分布。压缩后的页面大小应该总是大于最大记录大小，否则操作可能会失败，正如b树页面压缩中提到的那样。
将压缩页的大小设置得太大会浪费一些空间，但并不需要经常压缩页。如果压缩页大小设置得太小，插入或更新可能需要耗时的重新压缩，并且b树节点可能需要更频繁地分割，导致数据文件更大和索引效率更低。
通常，您可以将压缩页面大小设置为8K或4K字节。考虑到InnoDB表的最大行大小大约是8K, KEY_BLOCK_SIZE=8通常是一个安全的选择。
在运行时监控InnoDB表压缩
应用程序的整体性能、CPU和I/O利用率以及磁盘文件的大小都可以很好地反映压缩对应用程序的有效性。基于“InnoDB表的压缩调优”中的性能调优建议，并展示如何发现在初始测试中可能不会出现的问题。
为了更深入地了解压缩表的性能考虑因素，你可以使用INFORMATION_SCHEMA方案中的表来监控运行时的压缩性能。这些表反映了内存的内部使用情况和总体的压缩率。
INNODB_CMP表报告了每个压缩页大小(KEY_BLOCK_SIZE)的压缩活动信息。这些表中的信息是系统范围的:它总结了数据库中所有压缩表的压缩统计信息。当没有其他压缩表被访问时，通过检查这些表，可以使用这些数据来帮助决定是否要压缩表。它在服务器上的开销相对较低，所以你可以定期在生产服务器上查询它，以检查压缩特性的整体效率。
INNODB_CMP_PER_INDEX表报告了关于单个表和索引的压缩活动的信息。这些信息更有针对性，对于评估压缩效率和诊断一次一个表或索引的性能问题更有用。(因为每个InnoDB表都表示为一个聚集索引，MySQL在这个上下文中没有对表和索引做很大的区分。)INNODB_CMP_PER_INDEX表确实涉及大量的开销，因此它更适合于开发服务器，在那里你可以单独比较不同工作负载、数据和压缩设置的影响。为了防止意外造成这种监控开销，用户必须在查询INNODB_CMP_PER_INDEX表之前启用innodb_cmp_per_index_enabled配置选项。
要考虑的主要统计数据是执行压缩和解压缩操作的数量和时间。由于MySQL在修改后会在b树节点满到无法包含压缩后的数据时拆分b树节点，因此需要将“成功”的压缩操作的数量与此类操作的总体数量进行比较。根据INNODB_CMP和INNODB_CMP_PER_INDEX表中的信息以及应用程序的整体性能和硬件资源利用率，您可以更改硬件配置，调整缓冲池的大小，选择不同的页面大小，或者选择不同的表集进行压缩。
如果压缩和解压缩所需的CPU时间非常多，那么在相同的数据、应用程序工作负载和压缩表集的情况下，改用更快的CPU或多核CPU可以帮助提高性能。增加缓冲池的大小也可能有助于提高性能，这样更多未压缩的页可以驻留在内存中，减少了对仅以压缩形式存在于内存中的页进行压缩的需求。
总体上大量的压缩操作(与应用程序中的插入、更新和删除操作的数量以及数据库的大小相比)可能表明某些压缩表的更新过于频繁，无法进行有效的压缩。如果是这样，请选择更大的页面大小，或者对要压缩的表更有选择性。
如果“成功”的压缩操作数量(COMPRESS_OPS_OK)占压缩操作总数(COMPRESS_OPS)的比例很高，那么系统可能性能良好。如果这个比率很低，那么MySQL会频繁地重组、重新压缩和分裂b树节点。在这种情况下，请避免压缩某些表，或者增大某些已压缩表的KEY_BLOCK_SIZE。如果你关闭了表的压缩，导致应用程序中的“压缩失败”数量超过了总数的1%或2%，那么你可以关闭表的压缩。(在数据加载等临时操作期间，这样的失败率是可以接受的)。
如何压缩InnoDB表
本节描述一些关于InnoDB表压缩的内部实现细节。这里提供的信息可能对性能调优有帮助，但对于压缩的基本使用来说没有必要知道。
压缩算法
有些操作系统在文件系统级别实现压缩。文件通常被划分为固定大小的块，然后被压缩为可变大小的块，这很容易导致碎片。每次数据块中的某些内容被修改时，整个数据块都会被重新压缩，然后再写入磁盘。这些特性使得这种压缩技术不适合在更新密集型数据库系统中使用。
MySQL在著名的zlib库的帮助下实现了压缩，该库实现了LZ77压缩算法。该压缩算法成熟、稳定，在CPU利用率和数据压缩方面都是有效的。该算法是“无损”的，因此原始未压缩的数据总是可以从压缩的形式中重构出来。LZ77压缩的工作原理是找出在要压缩的数据中重复出现的数据序列。数据中值的模式决定了它的压缩效果，但典型的用户数据通常会压缩50%或更多。
InnoDB只支持zlib库到1.2.3版本。
与应用程序执行的压缩或其他一些数据库管理系统的压缩特性不同，InnoDB压缩同时适用于用户数据和索引。在许多情况下，索引占数据库总大小的40-50%或更多，因此这种差异是显著的。当数据集的压缩工作正常时，InnoDB数据文件的大小(file-per-table表空间或一般的表空间.idb文件)是未压缩大小的25%到50%，甚至可能更小。根据工作负载的不同，这个较小的数据库可以反过来减少I/O，增加吞吐量，但在增加CPU利用率方面代价不大。您可以通过修改innodb_compression_level配置选项来调整压缩级别和CPU开销之间的平衡。
InnoDB数据存储和压缩
InnoDB表中的所有用户数据都存储在由b树索引(聚集索引)组成的页中。在其他一些数据库系统中，这种类型的索引被称为“索引组织表”。索引节点中的每一行包含(用户指定的或系统生成的)主键和表中所有其他列的值。
InnoDB表中的辅助索引也是b树，包含成对的值:索引键和指向聚集索引中的某一行的指针。指针实际上是表的主键的值，如果需要索引键和主键以外的列，则用于访问聚集索引。辅助索引记录必须始终能够放置在单个b树页上。
b树节点(集群索引和辅助索引)的压缩与用于存储长VARCHAR、BLOB或TEXT列的溢出页的压缩处理不同，以下各节将对此进行解释。
压缩b树页面
由于b树页经常更新，因此需要特殊处理。重要的是尽量减少b树节点拆分的次数，以及尽量减少解压缩和重新压缩它们的内容的需要。
MySQL使用的一种技术是在b树节点中以未压缩的形式维护一些系统信息，从而方便某些就地更新。例如，这允许在不进行任何压缩操作的情况下删除标记的行。
此外，当索引页发生变化时，MySQL试图避免不必要的解压缩和重压缩。在每个B-tree页面中，系统保存一个未压缩的“修改日志”来记录对页面所做的更改。小记录的更新和插入可以写入该修改日志，而不需要完全重建整个页面。
当修改日志的空间耗尽时，InnoDB解压缩页面，应用更改并重新压缩页面。如果重新压缩失败(这种情况称为压缩失败)，则分割b树节点，并重复该过程，直到更新或插入成功。
为了避免在写密集的应用(比如OLTP应用)中频繁发生压缩失败，MySQL有时会在页面中预留一些空白空间(填充)，这样修改日志就会很快填满，并且在有足够空间避免拆分页面时重新压缩页面。随着系统跟踪页面拆分的频率，每个页面中剩余的填充空间的大小也会变化。在一个繁忙的服务器上对压缩表进行频繁的写操作，你可以调整innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max配置选项来微调这种机制。
一般来说，MySQL要求InnoDB表中的每个B-tree页至少能容纳两条记录。对于压缩表，这个要求已经放宽了。b树节点的叶子页(无论是主键还是辅助索引)只需要容纳一条记录，但该记录必须以未压缩的形式容纳到每个页面的修改日志中。如果innodb_strict_mode是ON, MySQL会在CREATE TABLE或CREATE INDEX时检查最大行大小。如果行不合适，则会发出以下错误消息:error HY000: Too big row。
如果你在innodb_strict_mode关闭时创建表，并且后续的INSERT或UPDATE语句试图创建一个不适合压缩页面大小的索引项，操作将失败，ERROR 42000:Row size too large。(此错误消息没有指出哪个索引的记录太大，也没有提到该索引记录的长度或该特定索引页上的最大记录大小。)要解决这个问题，用ALTER table重建表并选择一个更大的压缩页大小(KEY_BLOCK_SIZE)，缩短任何列前缀索引，或完全禁用压缩ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPACT。
Innodb_strict_mode不适用于普通表空间，普通表空间也支持压缩表。一般表空间的表空间管理规则是严格执行的，独立于innodb_strict_mode。
压缩BLOB、VARCHAR和TEXT列
在InnoDB表中，不属于主键的BLOB、VARCHAR和TEXT列可能会存储在单独分配的溢出页上。我们把这些列称为页外列。其值存储在溢出页的单链表上。
对于以ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPRESSED创建的表，BLOB、TEXT或VARCHAR列的值可以完全存储在页外，这取决于它们的长度和整行的长度。对于存储在页外的列，聚集索引记录只包含指向溢出页的20字节指针，每个列一个。是否有列存储在页外取决于页大小和行总大小。当一行太长，不能完全装入聚集索引页时，MySQL选择最长的列进行页外存储，直到该行装入聚集索引页为止。如上所述，如果压缩页中某一行不适合它自己，就会发生错误。
对于以ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPRESSED创建的表，小于或等于40字节的TEXT和BLOB列总是行内存储。
在旧版本的MySQL中创建的表使用羚羊文件格式，它只支持ROW_FORMAT= redundancy和ROW_FORMAT=COMPACT。在这些格式中，MySQL将BLOB、VARCHAR和TEXT列的前768字节与主键一起存储在聚集索引记录中。768字节的前缀后面是一个20字节的指针，指向包含该列剩余值的溢出页。
在表采用压缩格式时，写入溢出页的所有数据都“按原样”压缩;也就是说，MySQL对整个数据项应用zlib压缩算法。除了数据之外，压缩溢出页面还包含一个未压缩的头部和尾部，其中包含一个页面校验和和指向下一个溢出页面的链接。因此，如果数据是高度可压缩的，那么较长的BLOB、TEXT或VARCHAR列可以显著节省存储空间，文本数据通常就是这种情况。图像数据，如JPEG，通常已经被压缩，因此不能从存储在压缩表中获得太多好处;双重压缩可能会浪费CPU周期，但节省的空间很少或根本没有。
溢出页的大小与其他页相同。一个包含10列且存储在页外的行会占用10个溢出页，即使所有列的总长度只有8K字节。在未压缩的表中，10个未压缩的溢出页占用160K字节。在一个页面大小为8K的压缩表中，它们只占用80K字节。因此，对于with的表，使用压缩表格式通常更有效长列值。
对于file-per_table表空间，使用16K的压缩页大小可以减少BLOB、VARCHAR或TEXT列的存储和I/O成本，因为这些数据通常压缩得很好，因此可能需要更少的溢出页，即使b树节点本身占用与未压缩形式相同的页。一般的表空间不支持16K的压缩页大小(KEY_BLOCK_SIZE)。
压缩和InnoDB缓冲池
在一个压缩的InnoDB表中，每个压缩页(无论是1K、2K、4K还是8K)对应一个16K字节的未压缩页(如果设置了innodb_page_size，则更小)。为了访问页面中的数据，MySQL会从磁盘中读取压缩过的页面(如果它不在缓冲池中)，然后将页面解压缩为原始形式。本节描述了InnoDB如何管理压缩表的页的缓冲池。
为了尽量减少I/O和减少解压缩页面的需要，缓冲池有时同时包含数据库页面的压缩形式和未压缩形式。为了给其他需要的数据库页面腾出空间，MySQL可以从缓冲池中移除未压缩的页面，而将压缩的页面留在内存中。或者，如果页有一段时间没有被访问，则可能将该页的压缩格式写入磁盘，以便为其他数据释放空间。因此，在任何给定的时间，缓冲池可能同时包含页的压缩形式和未压缩形式，或者只包含页的压缩形式，或者两者都不包含。
MySQL使用最近最少使用(least-recently-used, LRU)列表来跟踪哪些页应该保存在内存中，哪些页应该清除，这样热的(频繁访问的)数据就倾向于保存在内存中。当访问压缩表时，MySQL使用自适应LRU算法来实现内存中压缩页和未压缩页的适当平衡。该算法对系统是I/O密集型运行还是cpu密集型运行非常敏感。目标是避免在CPU繁忙时花费过多的处理时间来解压缩页，并避免在CPU有空闲周期可用于解压缩已压缩页(可能已经在内存中)时进行过多的I/O操作。当系统处于I/O绑定时，该算法倾向于移除一个页面的未压缩副本，而不是两个副本，以便为其他磁盘页面腾出更多空间成为内存驻留。当系统使用CPU 绑定时，MySQL倾向于同时移除压缩和未压缩的页面，这样就可以将更多的内存用于“热”页面，从而减少
只对压缩格式的数据进行解压的需求。
压缩和重做日志文件
在将压缩页面写入数据文件之前，MySQL将页面的一个副本写入重做日志(如果它从上次写入数据库以来已经被重新压缩过)。这样做是为了确保重做日志对于崩溃恢复是可用的，即使在zlib库升级的情况下，该更改也会引入与压缩数据的兼容性问题。因此，在使用压缩时，日志文件的大小可能会增加，或者需要更频繁的检查点。日志文件大小或检查点频率的增加量，取决于以需要重新组织和重新压缩的方式修改压缩页的次数。
压缩表需要Barracuda文件格式。要在一个以文件为表的表空间中创建一个压缩表，必须启用innodb_file_per_table，并且必须将innodb_file_format设置为Barracuda。在普通表空间中创建压缩表时，不依赖于innodb_file_format设置。
OLTP工作负载压缩
传统上，InnoDB压缩特性主要被推荐用于只读或以读为主的工作负载，例如在数据仓库配置中。SSD存储设备速度很快，但相对较小和昂贵，这使得压缩对于OLTP工作负载也很有吸引力:高流量、交互式的网站可以通过对频繁插入、更新和删除操作的应用程序使用压缩表来减少它们的存储需求和每秒I/O操作(IOPS)。
MySQL 5.6引入的配置选项允许你针对特定的MySQL实例调整压缩的工作方式，重点是写密集型操作的性能和可伸缩性:
.Innodb_compression_level允许你提高或降低压缩程度。值越高，存储设备上的数据就越多，但压缩时的CPU开销也越大。当存储空间不是很重要，或者你希望数据不是特别可压缩时，较小的值可以减少CPU开销。
.innodb_compression_failure_threshold_pct压缩表更新失败的截止点。当超过这个阈值时，MySQL开始在每个新的压缩页面中留下额外的空闲空间，动态调整空闲空间的数量，直到innodb_compression_pad_pct_max指定的页面大小的百分比
.innodb_compression_pad_pct_max允许您调整每个页面中保留的最大空间大小，以记录对压缩行的更改，而不需要再次压缩整个页面。该值越高，在不重新压缩页面的情况下可以记录的更改越多。MySQL为每个压缩表中的页面使用的空闲空间是可变的，只有当压缩操作在运行时“失败”时，需要执行昂贵的操作来分割压缩页面。
.Innodb_compression_pad_pct_max允许您调整每个页面中保留的最大空间大小，以记录对压缩行的更改，而不需要再次压缩整个页面。该值越高，在不重新压缩页面的情况下可以记录的更改越多。MySQL为每个压缩表中的页面使用的空闲空间是可变的，只有当压缩操作在运行时“失败”时，需要执行昂贵的操作来分割压缩页面。
.innodb_log_compressed_pages允许你禁止将重新压缩的页面的图像写入重做日志。当对压缩数据进行更改时，可能会发生重新压缩。默认启用此选项是为了防止在恢复过程中使用不同版本的zlib压缩算法时发生损坏。如果你确定zlib版本不会改变，禁用innodb_log_compressed_pages来减少修改压缩数据的工作负载的重做日志生成。
因为在处理压缩数据时，有时需要在内存中同时保存一个页面的压缩版本和未压缩版本，所以在使用oltp风格的工作负载时，要准备好增加innodb_buffer_pool_size配置选项的值。
SQL压缩语法警告和错误
在使用file-per-tabl表空间和通用表空间的表压缩特性时可能遇到的语法警告和错误。
针对每个表文件的表空间的SQL压缩语法警告和错误
当innodb_strict_mode被启用时(默认)，在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT=COMPRESSED或KEY_BLOCK_SIZE,如果innodb_file_per_table被禁用或者innodb_file_format被设置为Antelope而不是Barracuda会产生如下错误。
ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option
如果当前配置不允许使用压缩表，则不会创建该表。
当innodb_strict_mode被禁用时，在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT=COMPRESSED或KEY_BLOCK_SIZE，如果innodb_file_per_table被禁用会产生如下警告。
mysql> SHOW WARNINGS; +---------+------+---------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------+ | Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. | | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4. | | Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. | | Warning | 1478 | InnoDB: assuming ROW_FORMAT=DYNAMIC. | +---------+------+---------------------------------------------------------------+
如果innodb_file_format设置为Antelope而不是Barracuda，则会发出类似的警告。
这些消息只是警告，而不是错误，而且创建表时没有压缩，就像没有指定选项一样。
“非严格”行为允许您将mysqldump文件导入到不支持压缩表的数据库中，即使源数据库包含压缩表。在这种情况下，MySQL会在ROW_FORMAT=COMPACT中创建表，而不是阻止操作。
要将转储文件导入到一个新的数据库中，并重新创建原始数据库中的表，请确保服务器对innodb_file_format和innodb_file_per_table配置参数有适当的设置。
只有在ROW_FORMAT指定为COMPRESSED或省略时，属性KEY_BLOCK_SIZE才允许使用。使用任何其他ROW_FORMAT指定KEY_BLOCK_SIZE都会产生一个警告，用户可以通过SHOW WARNINGS查看。但是，该表没有被压缩。忽略指定的KEY_BLOCK_SIZE)。
Level Code Message Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=n unless ROW_FORMAT=COMPRESSED.
如果运行时启用了innodb_strict_mode, KEY_BLOCK_SIZE和任何ROW_FORMAT的组合都会产生一个错误，而不是一个警告，并且表不会被创建。
当innodb_strict_mode关闭时，MySQL创建或修改表，但忽略某些设置，如下所示。你可以在MySQL错误日志中看到警告消息。当innodb_strict_mode开启时，这些指定的选项组合将产生错误，表不会被创建或修改。要查看错误条件的完整描述，请执行SHOW ERRORS语句:示例:
mysql> show variables like 'innodb_strict_mode'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | innodb_strict_mode | ON | +--------------------+-------+ 1 row in set (0.02 sec) mysql> CREATE TABLE x (id INT PRIMARY KEY, c INT) ENGINE=INNODB KEY_BLOCK_SIZE=33333; ERROR 1005 (HY000): Can't create table 'test.x' (errno: 1478) mysql> SHOW ERRORS; +-------+------+-------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------+ | Error | 1478 | InnoDB: invalid KEY_BLOCK_SIZE=33333. | | Error | 1005 | Can't create table 'test.x' (errno: 1478) | +-------+------+-------------------------------------------+
当innodb_strict_mode开启时，MySQL会拒绝无效的ROW_FORMAT或KEY_BLOCK_SIZE参数并抛出错误。当innodb_strict_mode关闭时，MySQL会对忽略的无效参数发出警告而不是错误。innodb_strict_mode默认开启。
当innodb_strict_mode开启时，MySQL拒绝无效的ROW_FORMAT或KEY_BLOCK_SIZE参数。为了兼容MySQL的早期版本，默认不会启用严格模式。相反，MySQL会对被忽略的无效参数发出警告(而不是错误)。
使用SHOW TABLE STATUS是不可能看到选定的KEY_BLOCK_SIZE的。语句SHOW CREATE TABLE显示KEY_BLOCK_SIZE(即使在创建表时忽略了它)。MySQL无法显示表的实际压缩页大小。
通用表空间的SQL压缩语法警告和错误
.如果在创建表空间时没有为普通表空间定义FILE_BLOCK_SIZE，则该表空间不能包含压缩表。如果试图添加压缩表，则返回错误，如下面的例子所示:
mysql> CREATE TABLESPACE ts4 ADD DATAFILE 'ts4.ibd' Engine=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; ERROR 1478 (HY000): InnoDB: Tablespace `ts4` cannot contain a COMPRESSED table
.试图将无效KEY_BLOCK_SIZE设置的表添加到普通表空间会返回错误，如下面的例子所示:
mysql> CREATE TABLESPACE `ts5` ADD DATAFILE 'ts5.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t5 (c1 INT PRIMARY KEY) TABLESPACE ts5 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; ERROR 1478 (HY000): InnoDB: Tablespace `ts5` uses block size 8192 and cannot contain a table with physical page size 4096
对于普通表空间，表的KEY_BLOCK_SIZE必须等于表空间的FILE_BLOCK_SIZE除以1024。例如，表空间的FILE_BLOCK_SIZE为8192，则表的KEY_BLOCK_SIZE必须为8。
.尝试将一个未压缩行格式的表添加到配置为存储压缩表的一般表空间会返回一个错误，如下面的例子所示:
mysql> CREATE TABLESPACE `ts6` ADD DATAFILE 'ts6.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t6 (c1 INT PRIMARY KEY) TABLESPACE ts6 ROW_FORMAT=COMPACT; ERROR 1478 (HY000): InnoDB: Tablespace `ts6` uses block size 8192 and cannot contain a table with physical page size 16384
innodb_strict_mode不适用于一般表空间。一般表空间的表空间管理规则是严格执行的，独立于innodb_strict_mode。
