MySQL 5.6 Reference Manual-14.7 InnoDB Table Compression

14.7 InnoDB Table Compression

 

14.7.1 Overview of Table Compression

14.7.2 Enabling Compression for a Table

14.7.3 Tuning Compression for InnoDB Tables

14.7.4 Monitoring Compression at Runtime

14.7.5 How Compression Works for InnoDB Tables

14.7.6 Compression for OLTP Workloads

14.7.7 SQL Compression Syntax Warnings and Errors

 

By using the SQL syntax and MySQL configuration options for compression, you can create tables where the data is stored in compressed form. Compression can help to improve both raw performance and scalability. The compression means less data is transferred between disk and memory, and takes up less space on disk and in memory. The benefits are amplified for tables with secondary indexes, because index data is compressed also. Compression can be especially important for SSD storage devices, because they tend to have lower capacity than HDD devices.

通过SQL语句和MySQL的配置参数，你可以创建压缩形式的表。压缩能够帮组你该散raw的心能和可扩展性。压缩也意味着在磁盘和内存之间传输的数据量更少，占用的空间也更少。这对于secondary index更为有利，因为索引数据也是压缩的。压缩对于SSD存储磁盘尤为重要，因为它们的容量通常要小于HHD磁盘。

 

14.7.1 Overview of Table Compression

 

Because processors and cache memories have increased in speed more than disk storage devices, many workloads are disk-bound. Data compression enables smaller database size, reduced I/O, and improved throughput, at the small cost of increased CPU utilization. Compression is especially valuable for read-intensive applications, on systems with enough RAM to keep frequently used data in memory.

因为磁盘的速度要远低于内存，所以很多的工作环境都有磁盘能力的限制。压缩数据能够使得数据库的尺寸更小，并以此来减少I/O，提升吞吐量，以最小的成本增加CPU的利用率。压缩对于以读为主的应用程序更有价值，因为这样能够使得系统有足够的内存把频繁使用的数据都保持在内存里。

 

An InnoDB table created with ROW_FORMAT=COMPRESSED can use a smaller page size on disk than the usual 16KB default. Smaller pages require less I/O to read from and write to disk, which is especially valuable for SSD devices.

创建表的时候使用ROW_FORMAT=COMPRESSED，这样能够使用比默认16KB更小的数据页。使用更小的数据页也就使得从磁盘读写的时候要求更少I/O，这对SSD磁盘来说更为可贵。

 

The page size is specified through the KEY_BLOCK_SIZE parameter. The different page size means the table must be in its own .ibd file rather than in the system tablespace, which requires enabling the innodb_file_per_table option. The level of compression is the same regardless of the KEY_BLOCK_SIZE value. As you specify smaller values for KEY_BLOCK_SIZE, you get the I/O benefits of increasingly smaller pages. But if you specify a value that is too small, there is additional overhead to reorganize the pages when data values cannot be compressed enough to fit multiple rows in each page. There is a hard limit on how small KEY_BLOCK_SIZE can be for a table, based on the lengths of the key columns for each of its indexes. Specify a value that is too small, and the CREATE TABLE or ALTER TABLE statement fails.

通过KEY_BLOCK_SIZE可以指定数据页的大小。要使用不同的数据页大小也就意味着表必须要只用它自己的.ibd文件，而不是系统表空间的文件，这也就需要开启innodb_file_per_table参数。压缩的级别和KEY_BLOCK_SIZE的值是无关的。为KEY_BLOCK_SIZE指定一个更小的值能够带来I/O上的优势。但是如果这个值指定得太小了，又会使得压缩不充分的行无法放入到各自的数据页理，这样也就需要额外的损耗来重组数据页。同时KEY_BLOCK_SIZE还有一个硬限制，那就是不能小于每个索引的索引列的长度。另外这个值指定得太小，CREATE TABLE or ALTER TABLE执行的时候也会报错。

 

In the buffer pool, the compressed data is held in small pages, with a page size based on the KEY_BLOCK_SIZE value. For extracting or updating the column values, MySQL also creates a 16KB page in the buffer pool with the uncompressed data. Within the buffer pool, any updates to the uncompressed page are also re-written back to the equivalent compressed page. You might need to size your buffer pool to accommodate the additional data of both compressed and uncompressed pages, although the uncompressed pages are evicted from the buffer pool when space is needed, and then uncompressed again on the next access.

在buffer pool里，通过KEY_BLOCK_SIZE指定使用更小的数据页，这样就能够缓存更多的数据页了。为了提取和更新列的值，MySQL仍然会在buffer pool里使用未压缩的16KB的数据页。在buffer pool里面，任何的更新的未压缩的数据页都会回写到等价的压缩数据页上。因为空间的需要未压缩的数据页会被从buffer pool里驱逐出去，那你就需要调整buffer pool的大小来适应压缩及未压缩的额外数据，这样就能让未压缩的数据下次能够直接在buffer pool访问。

 

14.7.2 Enabling Compression for a Table

 

Before creating a compressed table, make sure the innodb_file_per_table configuration option is enabled, and innodb_file_format is set to Barracuda. You can set these parameters in the MySQL configuration file my.cnf or my.ini, or with the SET statement without shutting down the MySQL server.

在创建一个压缩的表之前，首先要确保开启了innodb_file_per_table配置参数，并把innodb_file_format设置为Barracuda。你可以在MySQL的配置my.cnf or my.ini里面设置这些参数，或者是通过SET语句来在线设定。

 

To enable compression for a table, you use the clauses ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE, or both in a CREATE TABLE or ALTER TABLE statement.

你可以在CREATE TABLE or ALTER TABLE语句中使用ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE来开启表的压缩。

 

To create a compressed table, you might use statements like these:

参照下面的例子创建压缩表：

 

SET GLOBAL innodb_file_per_table=1;

SET GLOBAL innodb_file_format=Barracuda;

CREATE TABLE t1

(c1 INT PRIMARY KEY)

ROW_FORMAT=COMPRESSED

KEY_BLOCK_SIZE=8;

 

• If you specify ROW_FORMAT=COMPRESSED, you can omit KEY_BLOCK_SIZE; the default page size value is used, which is half the innodb_page_size value.
• 如果指定了ROW_FORMAT=COMPRESSED，那就可以忽略KEY_BLOCK_SIZE了：使用的数据页的大小默认是innodb_page_size的一半。

 

• If you specify KEY_BLOCK_SIZE, you can omit ROW_FORMAT=COMPRESSED; compression is enabled automatically.
• 如果指定了KEY_BLOCK_SIZE，那ROW_FORMAT=COMPRESSED是可以忽略的：压缩自动就会开启的。

 

• To determine the best value for KEY_BLOCK_SIZE, typically you create several copies of the same table with different values for this clause, then measure the size of the resulting .ibd files and see how well each performs with a realistic workload.
• 要确定KEY_BLOCK_SIZE的最佳值，通常的方法就是用不同的值创建几个相同的表，再分别进行测试观察.ibd文件的大小以及实际的工作负载。

 

• The KEY_BLOCK_SIZE value is treated as a hint; a different size could be used by InnoDB if necessary. A value of 0 represents the default compressed page size, which is half of the innodb_page_size value. The KEY_BLOCK_SIZE can only be less than or equal to the innodb_page_size value. If you specify a value greater than the innodb_page_size value, the specified value is ignored, a warning is issued, and KEY_BLOCK_SIZE is set to half of the innodb_page_size value. If innodb_strict_mode=ON, specifying an invalid KEY_BLOCK_SIZE value returns an error.
• 如果需要的话InnoDB可以通过KEY_BLOCK_SIZE使用不同的大小的数据页。0代表是默认的压缩页的大小，是innodb_page_size的一半。KEY_BLOCK_SIZE只能是小于或者等于innodb_page_size的值。如果指定的值大于了innodb_page_size，那么指的值将会被忽略并返回一个警告，KEY_BLOCK_SIZE也会被设置成innodb_page_size的一半的大小。如果innodb_strict_mode=ON的话，KEY_BLOCK_SIZE指定一个无效的值的话则会报错。

 

• For additional performance-related configuration options, see Section 14.7.3, "Tuning Compression for InnoDB Tables".
• 对于配置参数带来的额外性能，可以查看Section 14.7.3, "Tuning Compression for InnoDB Tables"。

 

The default uncompressed size of InnoDB data pages is 16KB. Depending on the combination of option values, MySQL uses a page size of 1KB, 2KB, 4KB, 8KB, or 16KB for the .ibd file of the table. The actual compression algorithm is not affected by the KEY_BLOCK_SIZE value; the value determines how large each compressed chunk is, which in turn affects how many rows can be packed into each compressed page.

InnoDB默认的为压缩的数据页大小是16KB。以及相应参数的组合，MySQL可以使用的页大小有1KB, 2KB, 4KB, 8KB, or 16KB。实际的压缩算法并不会受到KEY_BLOCK_SIZE的影响；KEY_BLOCK_SIZE仅决定了每个压缩块的大小，但反过来会影响每个压缩过的数据页能够放下多少行的数据。

 

Setting KEY_BLOCK_SIZE equal to the InnoDB page size does not typically result in much compression. For example, setting KEY_BLOCK_SIZE=16 typically would not result in much compression, since the normal InnoDB page size is 16KB. This setting may still be useful for tables with many long BLOB, VARCHAR or TEXT columns, because such values often do compress well, and might therefore require fewer overflow pages as described in Section 14.7.5, "How Compression Works for InnoDB Tables".

把KEY_BLOCK_SIZE设置得等于InnoDB数据页的大小，这样通常就不会压缩多少了。例如，设置KEY_BLOCK_SIZE=16就不会产生多少的压缩，因为InnoDB普通的页大小就是16KB。但是这样设定对于有多个BLOB, VARCHAR or TEXT列还是有用的，因为这些值还是能够进行压缩的，因此也就需要更少的overflow页，正如Section 14.7.5, "How Compression Works for InnoDB Tables"所讲述的。

 

All indexes of a table (including the clustered index) are compressed using the same page size, as specified in the CREATE TABLE or ALTER TABLE statement. Table attributes such as ROW_FORMAT and KEY_BLOCK_SIZE are not part of the CREATE INDEX syntax for InnoDB tables, and are ignored if they are specified (although you see them in the output of the SHOW CREATE TABLE statement).

表的所有索引（包括clustered index）都会用相同的数据页大小进行压缩。表的属性如ROW_FORMAT和KEY_BLOCK_SIZE不是写在CREATE INDEX里面的，即使指定了也是会被忽略掉的。

 

Restrictions on Compressed Tables

 

Because MySQL versions prior to 5.1 cannot process compressed tables, using compression requires specifying the configuration parameter innodb_file_format=Barracuda, to avoid accidentally introducing compatibility issues.

因为MySQL在5.1之前的版本是不能处理压缩表的，因此要使用压缩必须要指定配置参数innodb_file_format=Barracuda，以此来避免意外的兼容性问题。

 

Table compression is also not available for the InnoDB system tablespace. The system tablespace (space 0, the ibdata* files) can contain user data, but it also contains internal system information, and therefore is never compressed. Thus, compression applies only to tables (and indexes) stored in their own tablespaces, that is, created with the innodb_file_per_table option enabled.

表压缩同样也不适用于InnoDB的系统表空间。系统表空间(space 0, the ibdata* files)包含了用户数据，但还包含了内部的系统信息，是不能压缩的。因此，表压缩只适用于存储在自己独有的表空间里表和索引，也就是说，innodb_file_per_table参数必须是要开启的。

 

Compression applies to an entire table and all its associated indexes, not to individual rows, despite the clause name ROW_FORMAT.

表压缩只适用于整个的表及其所有的索引，不能是个别的行，尽管其使用的子句的名字是ROW_FORMAT。

 

14.7.3 Tuning Compression for InnoDB Tables

 

Most often, the internal optimizations described in InnoDB Data Storage and Compression ensure that the system runs well with compressed data. However, because the efficiency of compression depends on the nature of your data, you can make decisions that affect the performance of compressed tables:

通常来说，InnoDB Data Storage and Compression章节所讲述的内部优化能够让系统在使用压缩数据的时候运行良好。然而，因为压缩的效率依赖于数据的种类，你需要注意下下列对压缩表的性能影响的内容：

 

• Which tables to compress.
• 哪些表要进行压缩。

 

• What compressed page size to use.
• 压缩使用了多大的页大小。

 

• Whether to adjust the size of the buffer pool based on run-time performance characteristics, such as the amount of time the system spends compressing and uncompressing data. Whether the workload is more like a data warehouse (primarily queries) or an OLTP system (mix of queries and DML).
• 根据当前的性能状况是否要调整buffer pool的大小，例如对压缩和非压缩的状况分别统计系统花费的时间。还有工作环境是更像数据仓库（主要进行查询）还是OLTP系统（查询和DML混合）。

 

• If the system performs DML operations on compressed tables, and the way the data is distributed leads to expensive compression failures at runtime, you might adjust additional advanced configuration options.
• 如果系统在压缩表上执行了DML操作，那么这种方式的数据的分别就有可能导致在运行时发生昂贵的压缩故障，这个时候你就要调整高级的配置参数了。

 

Use the guidelines in this section to help make those architectural and configuration choices. When you are ready to conduct long-term testing and put compressed tables into production, see Section 14.7.4, "Monitoring Compression at Runtime" for ways to verify the effectiveness of those choices under real-world conditions.

这部分的指导方针能够帮助你对这些结构和配置做出选择。当你准备长期测试并要把压缩表放入到生产上的时候，根据Section 14.7.4, "Monitoring Compression at Runtime"的方式来确认真实的效果。

 

When to Use Compression

 

In general, compression works best on tables that include a reasonable number of character string columns and where the data is read far more often than it is written. Because there are no guaranteed ways to predict whether or not compression benefits a particular situation, always test with a specific workload and data set running on a representative configuration. Consider the following factors when deciding which tables to compress.

通常情况下，在那些有合理数量的字符串列的表上或者是读操作远多于写操作的表上，压缩表都可以很好地工作。因为无法保证在某个具体的情况下压缩是否能够带来好处，所以要根据实例的情况进行测试。在决定是否要压缩的时候要考虑下面的因素。

 

Data Characteristics and Compression

 

A key determinant of the efficiency of compression in reducing the size of data files is the nature of the data itself. Recall that compression works by identifying repeated strings of bytes in a block of data. Completely randomized data is the worst case. Typical data often has repeated values, and so compresses effectively. Character strings often compress well, whether defined in CHAR, VARCHAR, TEXT or BLOB columns. On the other hand, tables containing mostly binary data (integers or floating point numbers) or data that is previously compressed (for example JPEG or PNG images) may not generally compress well, significantly or at all.

影响压缩效果的决定性因素是数据本身的性质。压缩的本质是在一个数据块内标识重复了字符串字节数。这么看来完全随机的数据是最为糟糕的。通常的数据都会有重复值的，因此压缩的效果也还是不错的。无论定义的类型是哪种：CHAR, VARCHAR, TEXT or BLOB，字符串的压缩的效果也是很好的。另一方面，如果表里的数据大部分都是二进制的数据(整型或浮点型的数字)或者已经压缩过的数据（例如JPEG or PNG的图片），那么压缩的效果通常不会明显，甚至于会一点效果都没有。

 

You choose whether to turn on compression for each InnoDB table. A table and all of its indexes use the same (compressed) page size. It might be that the primary key (clustered) index, which contains the data for all columns of a table, compresses more effectively than the secondary indexes. For those cases where there are long rows, the use of compression might result in long column values being stored "off-page", as discussed in Section 14.9.3, "DYNAMIC and COMPRESSED Row Formats". Those overflow pages may compress well. Given these considerations, for many applications, some tables compress more effectively than others, and you might find that your workload performs best only with a subset of tables compressed.

你可以选择是否要打开每个InnoDB表的压缩功能。一个表及其所有的索引都会使用相同的压缩过数据页大小。相对于secondary index，clustered index的压缩效果更好。对于很长的行数据，压缩可能造成数据被进行"off-page"的存储，如Section 14.9.3, "DYNAMIC and COMPRESSED Row Formats"所讲述的。这些溢出的数据页的压缩效果也是很不错的。终上所述，你可以根据的你实际情况对有这些情况的表进行压缩。

 

To determine whether or not to compress a particular table, conduct experiments. You can get a rough estimate of how efficiently your data can be compressed by using a utility that implements LZ77 compression (such as gzip or WinZip) on a copy of the .ibd file for an uncompressed table. You can expect less compression from a MySQL compressed table than from file-based compression tools, because MySQL compresses data in chunks based on the page size, 16KB by default. In addition to user data, the page format includes some internal system data that is not compressed. File-based compression utilities can examine much larger chunks of data, and so might find more repeated strings in a huge file than MySQL can find in an individual page.

要决定是否要对一个特定的表进行压缩，只能进行相应的测试。你可以使用相应的工具(such as gzip or WinZip)对未压缩的.ibd数据文件进行LZ77的压缩，这样就可以粗略评估压缩的效果。MySQL的压缩比基于文件的压缩工作效果还差一点，因为MySQL是基于默认16KB的数据页进行压缩的。另外除了用户数据，其他的例如内部系统数据是不能压缩的。

 

Another way to test compression on a specific table is to copy some data from your uncompressed table to a similar, compressed table (having all the same indexes) and look at the size of the resulting .ibd file. For example:

另一种测试的方法是把为压缩的表里的数据复制到压缩过的表里面然后再看结果。例如：

 

use test;

set global innodb_file_per_table=1;

set global innodb_file_format=Barracuda;

set global autocommit=0;

 

-- Create an uncompressed table with a million or two rows.

create table big_table as select * from information_schema.columns;

insert into big_table select * from big_table;

insert into big_table select * from big_table;

insert into big_table select * from big_table;

insert into big_table select * from big_table;

insert into big_table select * from big_table;

insert into big_table select * from big_table;

insert into big_table select * from big_table;

insert into big_table select * from big_table;

insert into big_table select * from big_table;

insert into big_table select * from big_table;

commit;

alter table big_table add id int unsigned not null primary key auto_increment;

 

show create table big_table\G

 

select count(id) from big_table;

 

-- Check how much space is needed for the uncompressed table.

\! ls -l data/test/big_table.ibd

 

create table key_block_size_4 like big_table;

alter table key_block_size_4 key_block_size=4 row_format=compressed;

 

insert into key_block_size_4 select * from big_table;

commit;

 

-- Check how much space is needed for a compressed table

-- with particular compression settings.

\! ls -l data/test/key_block_size_4.ibd

 

This experiment produced the following numbers, which of course could vary considerably depending on your table structure and data:

根据实验的结果可以看到压缩的效果是十分依赖于表的结构和数据的：

 

-rw-rw---- 1 cirrus staff 310378496 Jan 9 13:44 data/test/big_table.ibd

-rw-rw---- 1 cirrus staff 83886080 Jan 9 15:10 data/test/key_block_size_4.ibd

 

To see whether compression is efficient for your particular workload:

查看压缩对你的特别的工作环境是否有效果：

 

• For simple tests, use a MySQL instance with no other compressed tables and run queries against the INFORMATION_SCHEMA.INNODB_CMP table.
• 对于简单的测试，可以使用一个没有其他压缩表的MySQL实例，然后查询INFORMATION_SCHEMA.INNODB_CMP表。

 

• For more elaborate tests involving workloads with multiple compressed tables, run queries against the INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table. Because the statistics in the INNODB_CMP_PER_INDEX table are expensive to collect, you must enable the configuration option innodb_cmp_per_index_enabled before querying that table, and you might restrict such testing to a development server or a non-critical slave server.
• 要针对多个压缩表的更为更为详尽的测试可以查询INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表。因为INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表里统计信息的收集是非常昂贵的，在 查询这个表之前必须要开启配置参数innodb_cmp_per_index_enabled，而且还要限制此类的测试。最好是只在开发环境或者非核心的slave端进行测试。

 

• Run some typical SQL statements against the compressed table you are testing.
• 在压缩表上运行一些通常 的SQL语句进行测试。

 

• Examine the ratio of successful compression operations to overall compression operations by querying the INFORMATION_SCHEMA.INNODB_CMP or INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table, and comparing COMPRESS_OPS to COMPRESS_OPS_OK.
• 通过比对INFORMATION_SCHEMA.INNODB_CMP or INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table和COMPRESS_OPS to COMPRESS_OPS_OK的查询结果可以测试整体压缩操作的成功率。

 

• If a high percentage of compression operations complete successfully, the table might be a good candidate for compression.
• 如果压缩成功的比例很高，那压缩对于这个表来说就是一个非常不错的选项。

 

• If you get a high proportion of compression failures, you can adjust innodb_compression_level, innodb_compression_failure_threshold_pct, and innodb_compression_pad_pct_max options as described in Section 14.7.6, "Compression for OLTP Workloads", and try further tests.
• 如果压缩失败的比例很高，那么可以调整如Section 14.7.6, "Compression for OLTP Workloads"锁讲述的参数innodb_compression_level, innodb_compression_failure_threshold_pct, and innodb_compression_pad_pct_max options，然后再进行测试。

 

Database Compression versus Application Compression

 

Decide whether to compress data in your application or in the table; do not use both types of compression for the same data. When you compress the data in the application and store the results in a compressed table, extra space savings are extremely unlikely, and the double compression just wastes CPU cycles.

如果要决定是在应用程序中还是表里面对数据进行压缩，那不要同时使用这两种方式对相同的数据进行压缩，这样并会节省额外的空间，两次压缩只会浪费CPU。

 

Compressing in the Database

 

When enabled, MySQL table compression is automatic and applies to all columns and index values. The columns can still be tested with operators such as LIKE, and sort operations can still use indexes even when the index values are compressed. Because indexes are often a significant fraction of the total size of a database, compression could result in significant savings in storage, I/O or processor time. The compression and decompression operations happen on the database server, which likely is a powerful system that is sized to handle the expected load.

当开启压缩后，MySQL的表会自动进行压缩，并适用于这个表的所有列和索引值。这些列仍然可进行例如LIKE的操作，而且即使是索引值已经被压缩了也仍然可以使用索引进行排序。因为索引通常会占数据库的很大一部分，所以压缩能够显著节省存储空间，I/O和处理时间。压缩和解压操作都是发生在数据库服务端的，这也就需要数据库的服务器更为强劲。

 

Compressing in the Application

 

If you compress data such as text in your application, before it is inserted into the database, You might save overhead for data that does not compress well by compressing some columns and not others. This approach uses CPU cycles for compression and uncompression on the client machine rather than the database server, which might be appropriate for a distributed application with many clients, or where the client machine has spare CPU cycles.

如果你在应用程序中压缩了诸如text的数据，在将其写入数据库之前，你能够节省部分的损耗，因为这样可以做到只压缩部分的列而不是所有的。这样的方式会使用CPU进行压缩，而且解压操作是在客户端的机器长操作的，而不是数据库的服务端，这样更适合于有很多客户端的分布式应用，或者是CPU资源比较空余的客户端机器。

 

Hybrid Approach

 

Of course, it is possible to combine these approaches. For some applications, it may be appropriate to use some compressed tables and some uncompressed tables. It may be best to externally compress some data (and store it in uncompressed tables) and allow MySQL to compress (some of) the other tables in the application. As always, up-front design and real-life testing are valuable in reaching the right decision.

当然，这些方法也是可以结合起来的。对于一些应用，可以能比较适用于压缩表和不压缩的表混用。最好的办法也有可能是在外部压缩部分数据（并存储来不压缩的表上），再让MySQL压缩部分的表。一如既往，预先的设计和实际的测试是达到最佳方式的唯一路径。

 

Workload Characteristics and Compression

 

In addition to choosing which tables to compress (and the page size), the workload is another key determinant of performance. If the application is dominated by reads, rather than updates, fewer pages need to be reorganized and recompressed after the index page runs out of room for the per-page "modification log" that MySQL maintains for compressed data. If the updates predominantly change non-indexed columns or those containing BLOBs or large strings that happen to be stored "off-page", the overhead of compression may be acceptable. If the only changes to a table are INSERTs that use a monotonically increasing primary key, and there are few secondary indexes, there is little need to reorganize and recompress index pages. Since MySQL can "delete-mark" and delete rows on compressed pages "in place" by modifying uncompressed data, DELETE operations on a table are relatively efficient.

除了要选择那些表要进行压缩以及压缩的数据页的大小之外，实际环境是性能的另一个关键因素。如果应用组要都是读操作，那只有少部分的数据页在耗尽每个数据页的"modification log"之后需要重新组织及重新压缩。如果更新主要是修改非索引列或者是那些包含BLOBs，长字符串的可能被存储在"off-page"上的列，那么这个压缩的损耗一般都是可以接受的。如果主要的修改是INSERT单调递增的主键，而且只有少部分的secondary index，那就几乎不许哟呵对索引页进行重新组织和重新压缩。自从MySQL能够"标记删除(delete-mark)"以及通过修改未压缩的数据来以"in place"的方式删除压缩页上的行，表上的DELETE操作也更为高效了。

 

For some environments, the time it takes to load data can be as important as run-time retrieval. Especially in data warehouse environments, many tables may be read-only or read-mostly. In those cases, it might or might not be acceptable to pay the price of compression in terms of increased load time, unless the resulting savings in fewer disk reads or in storage cost is significant.

一些环境里，数据加载花费的时间和运行时检索数据花费的时间一样重要。特别是数据仓库的环境里，很多的表都是只读的或者是大部分都是读的。在这种情况下，可能会无法接受因为压缩而在加载的时候花费额外的时间，除非这能节省大量的存储成本或者减少更多的磁盘读操作。

 

Fundamentally, compression works best when the CPU time is available for compressing and uncompressing data. Thus, if your workload is I/O bound, rather than CPU-bound, you might find that compression can improve overall performance. When you test your application performance with different compression configurations, test on a platform similar to the planned configuration of the production system.

根本上，只有CPU资源更为充分的情况下压缩才能以最佳的方式工作。因此，如果你的环境主要的I/O的限制而不是CPU的限制，那么压缩能够提升性能。当你用不同的压缩配置测试了应用的性能，那要在一个和生产系统平台配置相似的环境下测试。

 

Configuration Characteristics and Compression

 

Reading and writing database pages from and to disk is the slowest aspect of system performance. Compression attempts to reduce I/O by using CPU time to compress and uncompress data, and is most effective when I/O is a relatively scarce resource compared to processor cycles.

在磁盘上读写数据库页是系统中最慢的一个环节。压缩操作能够通过使用CPU时间（来压缩和解压数据）减少I/O的操作，这在高I/O负荷的环境下是非常有用的。

 

This is often especially the case when running in a multi-user environment with fast, multi-core CPUs. When a page of a compressed table is in memory, MySQL often uses additional memory, typically 16KB, in the buffer pool for an uncompressed copy of the page. The adaptive LRU algorithm attempts to balance the use of memory between compressed and uncompressed pages to take into account whether the workload is running in an I/O-bound or CPU-bound manner. Still, a configuration with more memory dedicated to the buffer pool tends to run better when using compressed tables than a configuration where memory is highly constrained.

这其中在多用户或多核CPU的环境更为有效。当压缩表的一个数据页放入到了内存里，MySQL通常会在buffer pool使用额外的内存（通常是16KB）来存储这个数据页的未压缩状态的拷贝。自适应的LRU算法会根据当前环境是I/O-bound or CPU-bound来平衡在内存里对压缩的和未压缩的数据页的的使用。当然，更多内存的配置的buffer pool可以让压缩运行得更好。

 

Choosing the Compressed Page Size

 

The optimal setting of the compressed page size depends on the type and distribution of data that the table and its indexes contain. The compressed page size should always be bigger than the maximum record size, or operations may fail as noted in Compression of B-Tree Pages.

对压缩数据页大小的优化设定依赖于数据索引的类型以及分布。压缩数据页的大小应该总是要大于最大的记录的大小，否则压缩操作可能会失败，如Compression of B-Tree Pages所讲述的。

 

Setting the compressed page size too large wastes some space, but the pages do not have to be compressed as often. If the compressed page size is set too small, inserts or updates may require time-consuming recompression, and the B-tree nodes may have to be split more frequently, leading to bigger data files and less efficient indexing.

把压缩数据页的大小设得太大则会浪费一些空间，因为它们将不再会被压缩了。如果压缩数据页设置得太小，那insert和update操作可能会花费更多的时间来重新压缩，B-tree节点也可能会分裂得更频繁，这会导致数据文件变得更大，而起索引的效率也会降低。

 

Typically, you set the compressed page size to 8K or 4K bytes. Given that the maximum row size for an InnoDB table is around 8K, KEY_BLOCK_SIZE=8 is usually a safe choice.

通常来讲，你可以把压缩页的大小设置成8K or 4K bytes。InnoDB表最大行的大小通常在8K左右，因此KEY_BLOCK_SIZE=8一般来将是个安全的设定。

 

14.7.4 Monitoring Compression at Runtime

 

Overall application performance, CPU and I/O utilization and the size of disk files are good indicators of how effective compression is for your application. This section builds on the performance tuning advice from Section 14.7.3, "Tuning Compression for InnoDB Tables", and shows how to find problems that might not turn up during initial testing.

应用的整体性能，CPU和I/O的利用率，还有磁盘的文件的空间是检查压缩效果的不错的指标。本节基于性能调优的建议来自于Section 14.7.3, "Tuning Compression for InnoDB Tables"，以及如何找出测试时未发现的问题。

 

To dig deeper into performance considerations for compressed tables, you can monitor compression performance at runtime using the Information Schema tables described in Example 14.10, "Using the Compression Information Schema Tables". These tables reflect the internal use of memory and the rates of compression used overall.

要更深入挖掘压缩表的性能，你可以使用Information Schema里的表来监控压缩的性能。这些表体现了内存的内部使用以及整体的压缩比率。

 

The INNODB_CMP table reports information about compression activity for each compressed page size (KEY_BLOCK_SIZE) in use. The information in these tables is system-wide: it summarizes the compression statistics across all compressed tables in your database. You can use this data to help decide whether or not to compress a table by examining these tables when no other compressed tables are being accessed. It involves relatively low overhead on the server, so you might query it periodically on a production server to check the overall efficiency of the compression feature.

INNODB_CMP表显示了每种(KEY_BLOCK_SIZE)使用中的压缩数据页的相关使用信息。这些信息都是系统层面的：它统计的是数据库所有压缩表的压缩统计信息。你可以使用这些数据决定是否要使用压缩表。它在服务器上涉及的损坏是比较低的，因此可以定期查询来检查压缩特性的整体效果。

 

The INNODB_CMP_PER_INDEX table reports information about compression activity for individual tables and indexes. This information is more targeted and more useful for evaluating compression efficiency and diagnosing performance issues one table or index at a time. (Because that each InnoDB table is represented as a clustered index, MySQL does not make a big distinction between tables and indexes in this context.) The INNODB_CMP_PER_INDEX table does involve substantial overhead, so it is more suitable for development servers, where you can compare the effects of different workloads, data, and compression settings in isolation. To guard against imposing this monitoring overhead by accident, you must enable the innodb_cmp_per_index_enabled configuration option before you can query the INNODB_CMP_PER_INDEX table.

INNODB_CMP_PER_INDEX表显示了个别表和索引压缩活动的相关信息。这些信息对于评估一个表或索引的压缩效果或者诊断性能问题是非常有针对性的。（因为每个InnoDB表都有对应的clustered index，正式由于这个原因MySQL在表和索引之间并没有很大的区别。）INNODB_CMP_PER_INDEX表会涉及到大量的损耗，所以它比较适合于在开发环境对比效果。为了防止意外监控带来的开销，在查询INNODB_CMP_PER_INDEX表之前你必须要开启innodb_cmp_per_index_enabled配置参数。

 

The key statistics to consider are the number of, and amount of time spent performing, compression and uncompression operations. Since MySQL splits B-tree nodes when they are too full to contain the compressed data following a modification, compare the number of "successful" compression operations with the number of such operations overall. Based on the information in the INNODB_CMP and INNODB_CMP_PER_INDEX tables and overall application performance and hardware resource utilization, you might make changes in your hardware configuration, adjust the size of the buffer pool, choose a different page size, or select a different set of tables to compress.

统计信息的关键是要考虑数量，以及压缩和解压操作花费的时间。在修改之后数据页太满而无法存放压缩过的数据的时候，MySQL会分裂B-tree的节点，之后还会对压缩成功的数量和整体的数据进行独臂。根据INNODB_CMP and INNODB_CMP_PER_INDEX表里的信息，应用的整体性能，还有硬件资源的利用率，你可以修改你的硬件配置，调整buffer pool的大小，选择不同的大小的数据页，又或者选择不同的表进行压缩。

 

If the amount of CPU time required for compressing and uncompressing is high, changing to faster or multi-core CPUs can help improve performance with the same data, application workload and set of compressed tables. Increasing the size of the buffer pool might also help performance, so that more uncompressed pages can stay in memory, reducing the need to uncompress pages that exist in memory only in compressed form.

如果压缩和解压所需要的CPU时间很高，那么使用多核的CPU能够该散性能。同样增加buffer pool的大小也能够提高性能，因为这样更多的未压缩的数据页能够存放在内存里，这就减少了需要解压的数据页在内存里以压缩的形式存在。

 

A large number of compression operations overall (compared to the number of INSERT, UPDATE and DELETE operations in your application and the size of the database) could indicate that some of your compressed tables are being updated too heavily for effective compression. If so, choose a larger page size, or be more selective about which tables you compress.

大量的压缩操作（对比数据里的DML操作）表明一些压缩的表更新得过为频繁。如果是这样的话，应该选择一个更大的数据页大小，或者让压缩表更有选择性。

 

If the number of "successful" compression operations (COMPRESS_OPS_OK) is a high percentage of the total number of compression operations (COMPRESS_OPS), then the system is likely performing well. If the ratio is low, then MySQL is reorganizing, recompressing, and splitting B-tree nodes more often than is desirable. In this case, avoid compressing some tables, or increase KEY_BLOCK_SIZE for some of the compressed tables. You might turn off compression for tables that cause the number of "compression failures" in your application to be more than 1% or 2% of the total. (Such a failure ratio might be acceptable during a temporary operation such as a data load).

"成功"压缩操作的数量(COMPRESS_OPS_OK)在所有压缩操作的数量 (COMPRESS_OPS)中的比例很高的话，那就表示系统运行良好。如果比例比较低的话，那表明MySQL会重新组织，重新压缩，分裂B-tree节点的情况更为频繁。在这种情况下，要避免对某些表的压缩，或者提高某些压缩表的KEY_BLOCK_SIZE的值。压缩失败的比例超过了1% or 2%的时候你要关闭表的压缩特性了。

 

14.7.5 How Compression Works for InnoDB Tables

 

This section describes some internal implementation details about compression for InnoDB tables. The information presented here may be helpful in tuning for performance, but is not necessary to know for basic use of compression.

这部分描述了InnoDB在内部是如何实现压缩的。这些内容对于性能的调优是非常有用的，但对于基本的压缩应用则不是必须的。

 

Compression Algorithms

 

Some operating systems implement compression at the file system level. Files are typically divided into fixed-size blocks that are compressed into variable-size blocks, which easily leads into fragmentation. Every time something inside a block is modified, the whole block is recompressed before it is written to disk. These properties make this compression technique unsuitable for use in an update-intensive database system.

一些操作系统是在文件系统层面实现压缩的。文件通常会被分割成不同大小而且大小是可变的数据块，这样就非常容易导致碎片的存在。每次一个数据库里面的内容要进行修改，那么在写之前整个数据块都要进行解压操作。这些情况就使得这种的压缩技术并不适用于以写为主的数据库系统。

 

MySQL implements compression with the help of the well-known zlib library, which implements the LZ77 compression algorithm. This compression algorithm is mature, robust, and efficient in both CPU utilization and in reduction of data size. The algorithm is "lossless", so that the original uncompressed data can always be reconstructed from the compressed form. LZ77 compression works by finding sequences of data that are repeated within the data to be compressed. The patterns of values in your data determine how well it compresses, but typical user data often compresses by 50% or more.

MySQL是通过知名的zlib库来实现压缩的，它使用的是LZ77压缩算法。这种算法是成熟的，强健的，在CPU的利用率和减少数据的尺寸上都是非常有效的。而且这种算法还是"没有失真的"，所有原始未压缩的数据都能够从压缩的格式上进行重建。LZ77压缩方式会在压缩的数据之间重复查询数据 的序列。那么数据里的模式值就决定了它是如何进行压缩的，但通常情况下用户数据能够压缩到50%甚至更多。

 

Unlike compression performed by an application, or compression features of some other database management systems, InnoDB compression applies both to user data and to indexes. In many cases, indexes can constitute 40-50% or more of the total database size, so this difference is significant. When compression is working well for a data set, the size of the InnoDB data files (the .idb files) is 25% to 50% of the uncompressed size or possibly smaller. Depending on the workload, this smaller database can in turn lead to a reduction in I/O, and an increase in throughput, at a modest cost in terms of increased CPU utilization. You can adjust the balance between compression level and CPU overhead by modifying the innodb_compression_level configuration option.

不同于应用端的或者是其他数据库管理系统的压缩，InnoDB的压缩同时适用于用户数据和索引。在很多的情况下，索引会占到整个数据库大小的40-50%的左右，甚至于更多，所以InnoDB压缩效果和其他的相比是显著的。当对一个数据集压缩的很好的时候，InnoDB数据文件(the .idb files)只会达到未压缩的25%到50%左右，甚至于更小。基于这样的情况，更小的数据库能减少I/O，增加吞吐量，以适当的成本增加CPU的利用率。你还可以通过修改innodb_compression_level配置参数来平衡压缩的级别和CPU的损耗。

 

InnoDB Data Storage and Compression

 

All user data in InnoDB tables is stored in pages comprising a B-tree index (the clustered index). In some other database systems, this type of index is called an "index-organized table". Each row in the index node contains the values of the (user-specified or system-generated) primary key and all the other columns of the table.

InnoDB表里的所有用户数据都是存储在构成B-tree索引的数据页上的。在其他的一些数据库系统里，这种索引通常称之为"索引组织表(index-organized table)"。索引节点里的每一行数据都包含主键的值（用户定义的或者是系统生成的）和表的其他列的数据。

 

Secondary indexes in InnoDB tables are also B-trees, containing pairs of values: the index key and a pointer to a row in the clustered index. The pointer is in fact the value of the primary key of the table, which is used to access the clustered index if columns other than the index key and primary key are required. Secondary index records must always fit on a single B-tree page.

InnoDB表里的secondary index也是B-tree结构的，包含了一对键值：索引key和clustered index里面指向一行记录的指针。这个指针实际上就是表的主键值，用于访问clustered index的。还有secondary index的记录必须放入到一个B-tree数据页里面。

 

The compression of B-tree nodes (of both clustered and secondary indexes) is handled differently from compression of overflow pages used to store long VARCHAR, BLOB, or TEXT columns, as explained in the following sections.

B-tree节点（包括clustered和secondary索引）的压缩在处理方式上不同于VARCHAR, BLOB, or TEXT长列对溢出页的压缩，正如下面内容所描述的。

 

Compression of B-Tree Pages

 

Because they are frequently updated, B-tree pages require special treatment. It is important to minimize the number of times B-tree nodes are split, as well as to minimize the need to uncompress and recompress their content.

因为进行频繁地更新，所以B-tree页需要进行特别处理。最小化B-tree节点分裂的次数是非常重要的，以及最小化解压和重新压缩的次数也是同样重要的。

 

One technique MySQL uses is to maintain some system information in the B-tree node in uncompressed form, thus facilitating certain in-place updates. For example, this allows rows to be delete-marked and deleted without any compression operation.

MySQL使用的一种方法是以不压缩的格式在B-tree节点里维护一些系统信息，因此facilitating certain in-place updates。例如，允许行做删除标记，这样就可以不需要任何的压缩操作就可以进行删除了。

 

In addition, MySQL attempts to avoid unnecessary uncompression and recompression of index pages when they are changed. Within each B-tree page, the system keeps an uncompressed "modification log" to record changes made to the page. Updates and inserts of small records may be written to this modification log without requiring the entire page to be completely reconstructed.

另外，MySQL在索引页更新的时候会试图避免不必要的解压和重新压缩操作。在每个B-tree数据页里面，系统会保持一个不压缩的"修改日志(modification log)"来记录下这个数据页的更新动作。小范围的更新和插入记录会被记录进这个唏嘘该日志，而不需要对整个数据页完全重建。

 

When the space for the modification log runs out, InnoDB uncompresses the page, applies the changes and recompresses the page. If recompression fails (a situation known as a compression failure), the B-tree nodes are split and the process is repeated until the update or insert succeeds.

当修改日志的空间用光了，InnoDB才会解压这个数据页，然后应用记录的修改再重新压缩数据页。如果重新压缩失败了（例如压缩故障），B-tree节点会分裂，而且这个操作会一直重复直到insert和update完成。

 

To avoid frequent compression failures in write-intensive workloads, such as for OLTP applications, MySQL sometimes reserves some empty space (padding) in the page, so that the modification log fills up sooner and the page is recompressed while there is still enough room to avoid splitting it. The amount of padding space left in each page varies as the system keeps track of the frequency of page splits. On a busy server doing frequent writes to compressed tables, you can adjust the innodb_compression_failure_threshold_pct, and innodb_compression_pad_pct_max configuration options to fine-tune this mechanism.

为了避免在类似OLTP这种以写为主的环境里频繁发生压缩失败，MySQL有时会在数据页里保留部分的空的空间，这样修改日志块满的时候还有足够的空间进行重新压缩，这也就避免了分支节点的分裂。这个空间的会随着数据页分裂的频率状况而变化的。在一个经常要写压缩表的繁忙的系统上，你可以调整innodb_compression_failure_threshold_pct, and innodb_compression_pad_pct_max配置参数来调整这个机制。

 

Generally, MySQL requires that each B-tree page in an InnoDB table can accommodate at least two records. For compressed tables, this requirement has been relaxed. Leaf pages of B-tree nodes (whether of the primary key or secondary indexes) only need to accommodate one record, but that record must fit, in uncompressed form, in the per-page modification log. If innodb_strict_mode is ON, MySQL checks the maximum row size during CREATE TABLE or CREATE INDEX. If the row does not fit, the following error message is issued: ERROR HY000: Too big row.

通常情况下，MySQL要求InnoDB表里的每个B-tree数据页至少能容纳两条记录。对于压缩表，这个要求更为放松。B-tree分支节点的叶子页（不论是主键还是secondary index）只需要能容纳一条记录，但这这条记录必须是在这个页的修改日志里的，以不压缩的形式能完全放置进去的。如果innodb_strict_mode是打开的，在CREATE TABLE or CREATE INDEX的时候MySQL还会检查行的最大长度。如果长度太大，则会报下面的错误：ERROR HY000: Too big row.

 

If you create a table when innodb_strict_mode is OFF, and a subsequent INSERT or UPDATE statement attempts to create an index entry that does not fit in the size of the compressed page, the operation fails with ERROR 42000: Row size too large. (This error message does not name the index for which the record is too large, or mention the length of the index record or the maximum record size on that particular index page.) To solve this problem, rebuild the table with ALTER TABLE and select a larger compressed page size (KEY_BLOCK_SIZE), shorten any column prefix indexes, or disable compression entirely with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPACT.

如果你在innodb_strict_mode关闭的时候建的表，随后的INSERT或者UPDATE操作创建的索引条目无法完全放入压缩的数据页，那么这个操作就报这样的错误：ERROR 42000: Row size too large。（这个错误没说是索引的记录太大，也没有提及到索引记录的长度。）要解决这样的问题，要使用ALTER TABLE并用更大的KEY_BLOCK_SIZE值重建表，或者是减小索引的前缀列，又或者是以ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPACT的方式完全闭关压缩。

 

Compressing BLOB, VARCHAR, and TEXT Columns

 

In an InnoDB table, BLOB, VARCHAR, and TEXT columns that are not part of the primary key may be stored on separately allocated overflow pages. We refer to these columns as off-page columns. Their values are stored on singly-linked lists of overflow pages.

在InnoDB表里面，BLOB, VARCHAR, and TEXT列不会成为主键的一部分，它们会被单独存储在溢出的页上。我们可以将这些列看成是off-page列。它们的值也会存储在溢出页的单项链表上。

 

For tables created in ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, the values of BLOB, TEXT, or VARCHAR columns may be stored fully off-page, depending on their length and the length of the entire row. For columns that are stored off-page, the clustered index record only contains 20-byte pointers to the overflow pages, one per column. Whether any columns are stored off-page depends on the page size and the total size of the row. When the row is too long to fit entirely within the page of the clustered index, MySQL chooses the longest columns for off-page storage until the row fits on the clustered index page. As noted above, if a row does not fit by itself on a compressed page, an error occurs.

对于以ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED建的表，BLOB, TEXT, or VARCHAR列的值会基于它们以及整行的长度被完全存储在off-page上。对于存储在off-page上的列，clustered index记录仅包含20-byte的指针指向溢出页。如果行太长而无法完全放入到clustered index的数据页里，MySQL会选择最长的列放到off-page里直到这行记录能够完全放入到clustered index的数据页里。如上所述，如果一行无法将其自身放入到一个压缩过的数据页里，那会报错。

 

Note

 

For tables created in ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, TEXT and BLOB columns that are less than or equal to 40 bytes are always stored in-line.

对于以ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED方式建的表，小于等于40 bytes的TEXT and BLOB列总是串联存储的。

 

Tables created in older versions of MySQL use the Antelope file format, which supports only ROW_FORMAT=REDUNDANT and ROW_FORMAT=COMPACT. In these formats, MySQL stores the first 768 bytes of BLOB, VARCHAR, and TEXT columns in the clustered index record along with the primary key. The 768-byte prefix is followed by a 20-byte pointer to the overflow pages that contain the rest of the column value.

老版本的MySQL创建的表使用的是Antelope文件格式，仅支持ROW_FORMAT=REDUNDANT and ROW_FORMAT=COMPACT。在这种格式里，MySQL会在沿着主键在clustered index记录里面存储BLOB, VARCHAR, and TEXT列的前768 bytes。这768-byte的前缀会紧跟在20-byte的指针的后面。

 

When a table is in COMPRESSED format, all data written to overflow pages is compressed "as is"; that is, MySQL applies the zlib compression algorithm to the entire data item. Other than the data, compressed overflow pages contain an uncompressed header and trailer comprising a page checksum and a link to the next overflow page, among other things. Therefore, very significant storage savings can be obtained for longer BLOB, TEXT, or VARCHAR columns if the data is highly compressible, as is often the case with text data. Image data, such as JPEG, is typically already compressed and so does not benefit much from being stored in a compressed table; the double compression can waste CPU cycles for little or no space savings.

如果表是COMPRESSED形式的，所有写入到overflow page的数据都会进行压缩；那也就是说，MySQL的zlib算法适用于整个数据项。除了数据，压缩的overflow page还包含未压缩的页头，构成page checksum和指向下一个overflow page的页尾。因此，高压缩的长BLOB, TEXT, or VARCHAR列能够大幅节约空间，文本形的数据基本上都是这样的。例如JPEG的图像数据通常都是已经压缩的，因此存储在压缩表里并不会带来额外的好处；多次压缩只会浪费CPU的指令。

 

The overflow pages are of the same size as other pages. A row containing ten columns stored off-page occupies ten overflow pages, even if the total length of the columns is only 8K bytes. In an uncompressed table, ten uncompressed overflow pages occupy 160K bytes. In a compressed table with an 8K page size, they occupy only 80K bytes. Thus, it is often more efficient to use compressed table format for tables with long column values.

overflow page和其他的数据页的大小相同。存储在off-page上的有10个列的一行记录会占用10个overflow page，即使列的总长度只有8K bytes。在未压缩的表里，10个未压缩的overflow page会占用160K bytes。在使用8K页大小的压缩表里，它们会占用80K bytes。因此，长的列是非常适合压缩的。

 

Using a 16K compressed page size can reduce storage and I/O costs for BLOB, VARCHAR, or TEXT columns, because such data often compress well, and might therefore require fewer overflow pages, even though the B-tree nodes themselves take as many pages as in the uncompressed form.

使用16K的压缩页大小能够减少BLOB, VARCHAR, or TEXT列的I/O成本，因为它们通常能很好地进行压缩，因此需要的overflow page也更少，即使B-tree节点自身会以未压缩的形式占用很多的数据页。

 

Compression and the InnoDB Buffer Pool

 

In a compressed InnoDB table, every compressed page (whether 1K, 2K, 4K or 8K) corresponds to an uncompressed page of 16K bytes (or a smaller size if innodb_page_size is set). To access the data in a page, MySQL reads the compressed page from disk if it is not already in the buffer pool, then uncompresses the page to its original form. This section describes how InnoDB manages the buffer pool with respect to pages of compressed tables.

在压缩的InnoDB表里，每个压缩的数据页（不管是1K, 2K, 4K or 8K）都会对应一个未压缩的16K bytes（或者是略小于innodb_page_size设定的）的数据页。要访问一个数据页里的数据，如果它不在buffer pool里那MySQL会从磁盘上读取这个压缩的数据页，然后将其解压到它的原始形式。这一部分就讲述了InnoDB如何在buffer pool管理压缩表的数据页的。

 

To minimize I/O and to reduce the need to uncompress a page, at times the buffer pool contains both the compressed and uncompressed form of a database page. To make room for other required database pages, MySQL can evict from the buffer pool an uncompressed page, while leaving the compressed page in memory. Or, if a page has not been accessed in a while, the compressed form of the page might be written to disk, to free space for other data. Thus, at any given time, the buffer pool might contain both the compressed and uncompressed forms of the page, or only the compressed form of the page, or neither.

为了最小化I/O和减少未解压的数据页，有时buffer pool会同时包含一个数据页的压缩的和未压缩的状态。当压缩的数据页要从离开内存的时候，MySQL也会从buffer pool里面驱逐未解压的数据页。又或者是如果一个数据页有段时间没被访问了，这个数据页的压缩形式会被写入到磁盘里来为其他数据节约空间。因此，在任何的时间里，buffer pool都能够同时保留一个数据页的压缩和未压缩的形式，又或者是只保留数据页的压缩形式，还有就是都不保留。

 

MySQL keeps track of which pages to keep in memory and which to evict using a least-recently-used (LRU) list, so that hot (frequently accessed) data tends to stay in memory. When compressed tables are accessed, MySQL uses an adaptive LRU algorithm to achieve an appropriate balance of compressed and uncompressed pages in memory. This adaptive algorithm is sensitive to whether the system is running in an I/O-bound or CPU-bound manner. The goal is to avoid spending too much processing time uncompressing pages when the CPU is busy, and to avoid doing excess I/O when the CPU has spare cycles that can be used for uncompressing compressed pages (that may already be in memory). When the system is I/O-bound, the algorithm prefers to evict the uncompressed copy of a page rather than both copies, to make more room for other disk pages to become memory resident. When the system is CPU-bound, MySQL prefers to evict both the compressed and uncompressed page, so that more memory can be used for "hot" pages and reducing the need to uncompress data in memory only in compressed form.

MySQL会把数据页的轨迹保持在内存里，并通过least-recently-used (LRU)列表来对其进行驱逐，所以频繁访问的热数据会趋向于驻留在内存里。当访问压缩表的时候，MySQL会使用一个自适应的LRU算法来实现对内存里压缩和未压缩数据页的平衡。这种自适应的算法对于系统是I/O-bound 还是CPU-bound很敏感的。主要的目标是避免在CPU繁忙的时候花费过多的时间处理未压缩的数据页，还有就是当CPU有多余的能力可以用于解压压缩的数据的时候避免过多的I/O操作。当系统是I/O-bound，这个算法更倾向于驱逐未压缩的数据页来让内存能保留更多的数据。当系统是CPU-bound，MySQL会同时驱逐压缩过的和未压缩的数据页，这样就有更多的内存能够用于"热(hot)"数据页以减少对于压缩数据的解压操作。

 

Compression and the InnoDB Redo Log Files

 

Before a compressed page is written to a data file, MySQL writes a copy of the page to the redo log (if it has been recompressed since the last time it was written to the database). This is done to ensure that redo logs are usable for crash recovery, even in the unlikely case that the zlib library is upgraded and that change introduces a compatibility problem with the compressed data. Therefore, some increase in the size of log files, or a need for more frequent checkpoints, can be expected when using compression. The amount of increase in the log file size or checkpoint frequency depends on the number of times compressed pages are modified in a way that requires reorganization and recompression.

在一个压缩过的数据页写入数据文件之前，MySQL会在redo log里面写一份这个数据页的拷贝。这个动作是为了确保在崩溃恢复，还有就是zlib库升级而带来兼容问题的时候redo log可用。因此，使用压缩会造成日志文件的大小增加，或者是checkpoint更为频繁。日志文件增加的数量或者是checkpoint增加频率取决于修改压缩数据的次数，因为这会要进行重新组织和重新压缩的操作。

 

Compressed tables use a different file format for the redo log and the per-table tablespaces than in MySQL 5.1 and earlier. The MySQL Enterprise Backup product supports this latest Barracuda file format for compressed InnoDB tables.

MySQL5.1及之前的版本会对redo log和per-table表空间使用不同的文件格式来压缩表。MySQL Enterprise Backup支持最新的InnoDB最新的Barracuda文件压缩格式。

 

14.7.6 Compression for OLTP Workloads

 

Traditionally, the InnoDB compression feature was recommended primarily for read-only or read-mostly workloads, such as in a data warehouse configuration. The rise of SSD storage devices, which are fast but relatively small and expensive, makes compression attractive also for OLTP workloads: high-traffic, interactive web sites can reduce their storage requirements and their I/O operations per second (IOPS) by using compressed tables with applications that do frequent INSERT, UPDATE, and DELETE operations.

通常情况下，InnoDB的压缩特性适用于只读或者以读为主的环境，例如数据仓库。但随着SSD磁盘的崛起，它们的速度更快但相对地容量比较小，价格也比较贵，这也就使得压缩技术对于OLTP环境也有吸引力了：高吞吐量的交互式网站能够减少它们的存储要求，还有通过压缩技术使得DML操作的每秒I/O操作次数(IOPS)更高。

 

Configuration options introduced in MySQL 5.6 let you adjust the way compression works for a particular MySQL instance, with an emphasis on performance and scalability for write-intensive operations:

MySQL5.6引进的配置参数能让你为个别的MySQL实例调整压缩方式，来加强写密集型操作的性能和可扩展性：

 

• innodb_compression_level lets you turn the degree of compression up or down. A higher value lets you fit more data onto a storage device, at the expense of more CPU overhead during compression. A lower value lets you reduce CPU overhead when storage space is not critical, or you expect the data is not especially compressible.
• innodb_compression_level让你能够把压缩的程度调高或降低。高的值能让你节省跟多的存储空间，在压缩的时候也会花费更多的CPU指令。低的值能让你在存储空间不是特别重要的时候减少CPU的损耗。

 

• innodb_compression_failure_threshold_pct specifies a cutoff point for compression failures during updates to a compressed table. When this threshold is passed, MySQL begins to leave additional free space within each new compressed page, dynamically adjusting the amount of free space up to the percentage of page size specified by innodb_compression_pad_pct_max
• innodb_compression_failure_threshold_pct可以在更新压缩表的时候为压缩故障指定一个起始点。当达到了这个阀值，MySQL会在每个新压缩的数据页上空出额外的空余空间，动态调整由innodb_compression_pad_pct_max指定的空闲空间百分比。

 

• innodb_compression_pad_pct_max lets you adjust the maximum amount of space reserved within each page to record changes to compressed rows, without needing to compress the entire page again. The higher the value, the more changes can be recorded without recompressing the page. MySQL uses a variable amount of free space for the pages within each compressed table, only when a designated percentage of compression operations "fail" at runtime, requiring an expensive operation to split the compressed page.
• innodb_compression_pad_pct_max能够让你调整更新的每个数据页的最大保留空间，这能让你不需要再次压缩整个数据页。高的值可以使得更多的修改不需要重新进行压缩。MySQL会在每个压缩的表里使用可变的页空余空进啊，只有达到指定的压缩失败百分比，才会进行一个昂贵的操作来分裂压缩过的数据页。

 

Because working with compressed data sometimes involves keeping both compressed and uncompressed versions of a page in memory at the same time, when using compression with an OLTP-style workload, be prepared to increase the value of the innodb_buffer_pool_size configuration option.

因为压缩有时候会在内存里同时保存压缩过的和未压缩的数据页版本，索引在OLTP的环境里使用压缩技术，要准备好增加innodb_buffer_pool_size配置参数的值。

 

14.7.7 SQL Compression Syntax Warnings and Errors

 

Specifying ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE in CREATE TABLE or ALTER TABLE statements produces the following warnings if the Barracuda file format is not enabled. You can view them with the SHOW WARNINGS statement.

如果未开启Barracuda 文件格式的话，那么在CREATE TABLE or ALTER TABLE里指定ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE则会产生下面的警告。你可以通过SHOW WARNINGS来查看：

 

Level	Code	Message
Warning	1478	InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning	1478	InnoDB: KEY_BLOCK_SIZE requires innodb_file_format=1
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=COMPACT.

 

Notes:

 

• By default, these messages are only warnings, not errors, and the table is created without compression, as if the options were not specified.
• 默认情况下，只会产生警告，而不会报错，如果参数为指定表会以未压缩的形式创建。

 

• When innodb_strict_mode is enabled, MySQL generates an error, not a warning, for these cases. The table is not created if the current configuration does not permit using compressed tables.
• 当开启了innodb_strict_mode，这种情况MySQL就会报错，而不是产生警告。如果当前的参数不允许使用压缩的话表是不会被创建的。

 

The "non-strict" behavior lets you import a mysqldump file into a database that does not support compressed tables, even if the source database contained compressed tables. In that case, MySQL creates the table in ROW_FORMAT=COMPACT instead of preventing the operation.

"宽松"形式能让你把mysqldump的文件导入到不支持压缩的数据库里，即使原始的表是压缩过的。在这种情况下，MySQL会创建 ROW_FORMAT=COMPACT的表而不是阻止这样的操作。

 

To import the dump file into a new database, and have the tables re-created as they exist in the original database, ensure the server has the proper settings for the configuration parameters innodb_file_format and innodb_file_per_table.

如果要把dump文件的导入到一个新的数据库里，而且对于已存在的表是会进行重建的，那么要确保innodb_file_format and innodb_file_per_table参数的设定。

 

The attribute KEY_BLOCK_SIZE is permitted only when ROW_FORMAT is specified as COMPRESSED or is omitted. Specifying a KEY_BLOCK_SIZE with any other ROW_FORMAT generates a warning that you can view with SHOW WARNINGS. However, the table is non-compressed; the specified KEY_BLOCK_SIZE is ignored).

只有ROW_FORMAT指定为COMPRESSED或者忽略的情况下KEY_BLOCK_SIZE才是有效地。在ROW_FORMAT指定的是其他值的情况下设定KEY_BLOCK_SIZE会产生警告。但是，如果表是不压缩的，对KEY_BLOCK_SIZE的设定也被会忽略掉。

 

Level	Code	Message
Warning	1478	InnoDB: ignoring KEY_BLOCK_SIZE=n unless ROW_FORMAT=COMPRESSED.

 

If you are running with innodb_strict_mode enabled, the combination of a KEY_BLOCK_SIZE with any ROW_FORMAT other than COMPRESSED generates an error, not a warning, and the table is not created.

如果是开启了innodb_strict_mode，上述情况是要报错的，而不是产生警告。

 

Table 14.4, "ROW_FORMAT and KEY_BLOCK_SIZE Options" provides an overview the ROW_FORMAT and KEY_BLOCK_SIZE options that are used with CREATE TABLE or ALTER TABLE.

下面的表格显示了ROW_FORMAT and KEY_BLOCK_SIZE参数的简介。

 

Table 14.4 ROW_FORMAT and KEY_BLOCK_SIZE Options

 

Option	Usage Notes	Description
ROW_FORMAT=​REDUNDANT	Storage format used prior to MySQL 5.0.3	Less efficient than ROW_FORMAT=COMPACT; for backward compatibility
ROW_FORMAT=​COMPACT	Default storage format since MySQL 5.0.3	Stores a prefix of 768 bytes of long column values in the clustered index page, with the remaining bytes stored in an overflow page
ROW_FORMAT=​DYNAMIC	Available only with innodb_file​_format=Barracuda	Store values within the clustered index page if they fit; if not, stores only a 20-byte pointer to an overflow page (no prefix)
ROW_FORMAT=​COMPRESSED	Available only with innodb_file​_format=Barracuda	Compresses the table and indexes using zlib
KEY_BLOCK_​SIZE=n	Available only with innodb_file​_format=Barracuda	Specifies compressed page size of 1, 2, 4, 8 or 16 kilobytes; implies ROW_FORMAT=COMPRESSED

 

Table 14.5, "CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF" summarizes error conditions that occur with certain combinations of configuration parameters and options on the CREATE TABLE or ALTER TABLE statements, and how the options appear in the output of SHOW TABLE STATUS.

Table 14.5, "CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF"概述了CREATE TABLE or ALTER TABLE里面某个组合条件生产错误的条件，以及SHOW TABLE STATUS里面是怎么显示的。

 

When innodb_strict_mode is OFF, MySQL creates or alters the table, but ignores certain settings as shown below. You can see the warning messages in the MySQL error log. When innodb_strict_mode is ON, these specified combinations of options generate errors, and the table is not created or altered. To see the full description of the error condition, issue the SHOW ERRORS statement: example:

当innodb_strict_mode关闭的时候，MySQL的建表和修改表的时候会忽略某些配置。你可以在MySQL的error log里面看到这些警告。当innodb_strict_mode开启了，这些指定的参数组合则会报错，表也不会被创建或修改。要查询错误的全部描述，可以执行SHOW ERRORS。例如：

 

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) |

+-------+------+-------------------------------------------+

 

Table 14.5 CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF

Syntax	Warning or Error Condition	Resulting ROW_FORMAT, as shown in SHOW TABLE STATUS
ROW_FORMAT=REDUNDANT	None	REDUNDANT
ROW_FORMAT=COMPACT	None	COMPACT
ROW_FORMAT=COMPRESSED or ROW_FORMAT=DYNAMIC or KEY_BLOCK_SIZE is specified	Ignored unless both innodb_file_format=Barracuda and innodb_file_per_table are enabled	COMPACT
Invalid KEY_BLOCK_SIZE is specified (not 1, 2, 4, 8 or 16)	KEY_BLOCK_SIZE is ignored	the requested row format, or COMPACT by default
ROW_FORMAT=COMPRESSED and valid KEY_BLOCK_SIZE are specified	None	COMPRESSED
KEY_BLOCK_SIZE is specified with REDUNDANT, COMPACT or DYNAMIC row format	KEY_BLOCK_SIZE is ignored	REDUNDANT, COMPACT or DYNAMIC
ROW_FORMAT is not one of REDUNDANT, COMPACT, DYNAMIC or COMPRESSED	Ignored if recognized by the MySQL parser. Otherwise, an error is issued.	COMPACT or N/A

 

When innodb_strict_mode is ON, MySQL rejects invalid ROW_FORMAT or KEY_BLOCK_SIZE parameters. For compatibility with earlier versions of MySQL, strict mode is not enabled by default; instead, MySQL issues warnings (not errors) for ignored invalid parameters.

当开启了nnodb_strict_mode，MySQL会拒绝无效的ROW_FORMAT or KEY_BLOCK_SIZE参数。为了和早前的MySQL版本兼容，strict 模式默认是未开启的，相应的，MySQL则会产生警告（不会报错）并忽略无效的设定。

 

It is not possible to see the chosen KEY_BLOCK_SIZE using SHOW TABLE STATUS. The statement SHOW CREATE TABLE displays the KEY_BLOCK_SIZE (even if it was ignored when creating the table). The real compressed page size of the table cannot be displayed by MySQL.

在SHOW TABLE STATUS里面是看不到KEY_BLOCK_SIZE的设定的，SHOW CREATE TABLE 里面可以看到（即使创建的时候这个参数被忽略了）。MySQL是无法显示出表实际的压缩页的大小的。