InnoDB Table Compression---InnoDB表压缩

14.9.1.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.

因为处理器和高速缓存的速度提高速度超过了磁盘存储设备，所以许多工作负载都是磁盘绑定的。 数据压缩以较小的CPU利用率增加为代价，实现了较小的数据库大小，减少的I / O和改进的吞吐量。 在具有足够RAM以便将经常使用的数据保留在内存中的系统上，压缩对于读取密集型应用程序特别有价值。

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

使用ROW_FORMAT = COMPRESSED创建的InnoDB表可以使用比配置的innodb_page_size值小的磁盘页大小。 较小的页面需要更少的I / O来读取和写入磁盘，这对于SSD设备而言尤其有价值。

The compressed page size is specified through the CREATE TABLE or ALTER TABLE KEY_BLOCK_SIZE parameter. The different page size requires that the table be placed in a file-per-table tablespace or general tablespace rather than in the system tablespace, as the system tablespace cannot store compressed tables. For more information, see Section 14.6.3.2, “File-Per-Table Tablespaces”, and Section 14.6.3.3, “General Tablespaces”.

通过CREATE TABLE或ALTER TABLE KEY_BLOCK_SIZE参数指定压缩的页面大小。 不同的页面大小要求将表放置在每表文件表空间或常规表空间中，而不是系统表空间中，因为系统表空间无法存储压缩表。 有关更多信息，请参见第14.6.3.2节“每表文件表空间”和第14.6.3.3节“常规表空间”。

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值如何，压缩级别都相同。 当您为KEY_BLOCK_SIZE指定较小的值时，您将获得越来越小的页面的I / O好处。 但是，如果指定的值太小，则当数据值无法充分压缩以适合每个页面中的多行时，将需要额外的开销来重组页面。 根据表中每个索引的键列的长度，对表的KEY_BLOCK_SIZE的大小有严格的限制。 指定一个太小的值，CREATE TABLE或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 an uncompressed 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.

在缓冲池中，压缩数据保存在小页面中，其页面大小基于KEY_BLOCK_SIZE值。 为了提取或更新列值，MySQL还使用未压缩的数据在缓冲池中创建了未压缩的页面。 在缓冲池中，对未压缩页面的任何更新也将重新写回到等效的压缩页面。 您可能需要调整缓冲池的大小，以容纳压缩和未压缩页面的附加数据，尽管需要空间时会从缓冲池中清除未压缩的页面，然后在下次访问时再次将其解压缩。

 

14.9.1.2 Creating Compressed 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. The system tablespace (space 0, the .ibdata files) can contain user-created tables, but it also contains internal system data, which is never compressed. Thus, compression applies only to tables (and indexes) stored in file-per-table or general tablespaces.

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

 

Creating a Compressed Table in File-Per-Table Tablespace

在File-Per-Table 表空间中创建压缩表

To create a compressed table in a file-per-table tablespace, innodb_file_per_table must be enabled (the default in MySQL 5.6.6) and innodb_file_format must be set to Barracuda. You can set these parameters in the MySQL configuration file (my.cnf or my.ini) or dynamically, using a SET statement.

要在每个表文件表空间中创建压缩表，必须启用innodb_file_per_table（MySQL 5.6.6中的默认设置），并且innodb_file_format必须设置为Barracuda。 您可以在MySQL配置文件（my.cnf或my.ini）中或使用SET语句动态设置这些参数。

After the innodb_file_per_table and innodb_file_format options are configured, specify the ROW_FORMAT=COMPRESSED clause or KEY_BLOCK_SIZE clause, or both, in aCREATE TABLE or ALTER TABLE statement to create a compressed table in a file-per-table tablespace.

配置了innodb_file_per_table和innodb_file_format选项后，请在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT = COMPRESSED子句或KEY_BLOCK_SIZE子句，或同时指定ROW_FORMAT = COMPRESSED子句，以在每个表文件表空间中创建压缩表。

For example, you might use the following statements:

以下是创建压缩表的示例：

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;

 

Creating a Compressed Table in a General Tablespace

在通用表空间中创建压缩表

To create a compressed table in a general tablespace, FILE_BLOCK_SIZE must be defined for the general tablespace, which is specified when the tablespace is created. The FILE_BLOCK_SIZE value must be a valid compressed page size in relation to the innodb_page_size value, and the page size of the compressed table, defined by theCREATE TABLE or ALTER TABLE KEY_BLOCK_SIZE clause, must be equal to FILE_BLOCK_SIZE/1024. For example, if innodb_page_size=16384 andFILE_BLOCK_SIZE=8192, the KEY_BLOCK_SIZE of the table must be 8. For more information, see Section 14.6.3.3, “General Tablespaces”.

要在通用表空间中创建压缩表，必须为通用表空间定义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。有关更多信息，请参见第14.6.3.3节“常规表空间”。

The following example demonstrates creating a general tablespace and adding a compressed table. The example assumes a default innodb_page_size of 16K. TheFILE_BLOCK_SIZE of 8192 requires that the compressed table have a KEY_BLOCK_SIZE of 8.

下面的示例演示如何创建常规表空间并添加压缩表。 该示例假定innodb_page_size的默认值为16K。 FILE_BLOCK_SIZE为8192要求压缩表的KEY_BLOCK_SIZE为8。

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;

Notes

注释：

• If you specify ROW_FORMAT=COMPRESSED, you can omit KEY_BLOCK_SIZE; the KEY_BLOCK_SIZE setting defaults to half the innodb_page_size value.

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

• If you specify a valid KEY_BLOCK_SIZE value, 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. For general tablespaces, keep in mind that dropping a table does not reduce the size of the general tablespace .ibd file, nor does it return disk space to the operating system. For more information, see Section 14.6.3.3, “General Tablespaces”.

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

• The KEY_BLOCK_SIZE value is treated as a hint; a different size could be used by InnoDB if necessary. For file-per-table tablespaces, 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. For general tablespaces, valid KEY_BLOCK_SIZE values depend on the FILE_BLOCK_SIZE setting of the tablespace. For more information, see Section 14.6.3.3, “General Tablespaces”.

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设置。 有关更多信息，请参见第14.6.3.3节“常规表空间”。

• 32KB and 64KB page sizes do not support compression. For more information, refer to the innodb_page_size documentation.

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

• 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 tablespace data file (.ibd file). 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对表空间数据文件（.ibd文件）使用1KB，2KB，4KB，8KB或16KB的页面大小。 实际的压缩算法不受KEY_BLOCK_SIZE值的影响； 该值确定每个压缩块的大小，这反过来又会影响每个压缩页可以打包多少行。

• When creating a compressed table in a file-per-table tablespace, 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 inSection 14.9.1.5, “How Compression Works for InnoDB Tables”. For general tablespaces, a KEY_BLOCK_SIZE value equal to the InnoDB page size is not permitted. For more information, see Section 14.6.3.3, “General Tablespaces”.

在每个表文件表空间中创建压缩表时，将KEY_BLOCK_SIZE设置为InnoDB页面大小通常不会导致太多压缩。 例如，设置KEY_BLOCK_SIZE = 16通常不会导致太多压缩，因为正常的InnoDB页面大小为16KB。 对于具有许多长BLOB，VARCHAR或TEXT列的表，此设置可能仍然有用，因为此类值通常确实压缩得很好，因此可能需要较少的溢出页，如第14.9.1.5节“ InnoDB表的压缩方式”所述。 对于常规表空间，不允许使用等于InnoDB页大小的KEY_BLOCK_SIZE值。 有关更多信息，请参见第14.6.3.3节“常规表空间”。

• 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, if specified, they will appear in the output of the SHOW CREATE TABLE statement).

使用CREATE TABLE或ALTER TABLE语句中指定的相同页面大小来压缩表的所有索引（包括聚集索引）。 诸如ROW_FORMAT和KEY_BLOCK_SIZE之类的表属性不是InnoDB表的CREATE INDEX语法的一部分，并且如果被指定则将被忽略（尽管，如果指定的话，它们将出现在SHOW CREATE TABLE语句的输出中）。

• For performance-related configuration options, see Section 14.9.1.3, “Tuning Compression for InnoDB Tables”.

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

 

Restrictions on Compressed Tables

压缩表的限制

• MySQL versions prior to 5.1 cannot process compressed tables.

5.1之前的MySQL版本无法处理压缩表。

• Compressed tables cannot be stored in the InnoDB system tablespace.

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

• General tablespaces can contain multiple tables, but compressed and uncompressed tables cannot coexist within the same general tablespace.

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

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

尽管子句名称为ROW_FORMAT，压缩仍适用于整个表及其所有关联索引，而不适用于单个行。

14.9.1.3 Tuning Compression for InnoDB Tables

为Innodb 表开启压缩

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数据存储和压缩中描述的内部优化可确保系统在压缩数据下运行良好。但是，由于压缩的效率取决于数据的性质，因此您可以做出影响压缩表性能的决策：

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

是否根据运行时性能特征（例如系统花费在压缩和解压缩数据上的时间）来调整缓冲池的大小。工作负载更像是数据仓库（主要是查询）还是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.9.1.4, “Monitoring InnoDB Table Compression at Runtime” for ways to verify the effectiveness of those choices under real-world conditions.

使用本节中的准则可帮助做出那些体系结构和配置选择。当您准备进行长期测试并将压缩表投入生产时，请参见第14.9.1.4节“在运行时监视InnoDB表压缩”，以了解在现实条件下验证这些选择的有效性的方法。

 

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还是BLOB列中定义，字符串通常压缩得都很好。 另一方面，大多数包含二进制数据（整数或浮点数）或先前已压缩的数据（例如JPEG或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 DYNAMIC Row Format. 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表打开压缩。 一个表及其所有索引使用相同（压缩）的页面大小。 包含表的所有列的数据的主键（聚集）索引可能比辅助索引更有效地压缩。 对于长行的情况，如DYNAMIC行格式中所述，使用压缩可能会导致长列值“页外”存储。 这些溢出页面可能压缩得很好。 考虑到这些考虑因素，对于许多应用程序而言，某些表的压缩比其他应用程序更有效，并且您可能会发现您的工作负载仅在压缩了一部分表的情况下表现最佳。

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.

要确定是否压缩特定表，请进行实验。 您可以使用在未压缩表的.ibd文件副本上使用实现LZ77压缩的实用程序（例如gzip或WinZip）来粗略估计数据的压缩效率。 与基于文件的压缩工具相比，MySQL压缩表的压缩量会更少，因为MySQL会根据页面大小（默认情况下为16KB）按块压缩数据。 除用户数据外，页面格式还包括一些未压缩的内部系统数据。 基于文件的压缩实用程序可以检查更大的数据块，因此在大型文件中发现的重复字符串可能比MySQL在单个页面中找到的字符串还要多。

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) in afile-per-table tablespace and look at the size of the resulting .ibd file. For example:

测试特定表压缩的另一种方法是将每张表文件空间中的一些数据从未压缩表复制到类似的压缩表（具有所有相同的索引）中，并查看生成的.ibd文件的大小 。 例如：

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表运行查询。 因为要收集INNODB_CMP_PER_INDEX表中的统计信息很昂贵，所以在查询该表之前必须启用配置选项innodb_cmp_per_index_enabled，并且您可能会将这样的测试限制在开发服务器或非关键从属服务器上。

• 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 orINFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table, and comparing COMPRESS_OPS to COMPRESS_OPS_OK.

通过查询INFORMATION_SCHEMA.INNODB_CMP或INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表，并将COMPRESS_OPS与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, andinnodb_compression_pad_pct_max options as described in Section 14.9.1.6, “Compression for OLTP Workloads”, and try further tests.

如果压缩失败的比例很高，则可以按照第14.9.1.6节“ OLTP工作负载的压缩”中所述调整innodb_compression_level，innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max选项，然后尝试进一步测试。

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.

如果在将应用程序中的文本等数据在插入到数据库中之前将其压缩，则可以通过压缩某些列而不是另一些列来节省那些压缩效果不佳的数据的开销。 这种方法使用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.

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.

除了选择要压缩的表（和页面大小）以外，工作负载是性能的另一个关键因素。如果应用程序由读取而不是更新控制，则索引页用完了MySQL为压缩数据保留的每页“修改日志”的空间后，需要重组和重新压缩较少的页面。如果更新主要更改了未索引的列或包含恰好“页外”存储的包含BLOB或大字符串的列，则压缩的开销可能是可以接受的。如果对表的唯一更改是使用单调递增主键的INSERT，并且辅助索引很少，则几乎不需要重组和重新压缩索引页。由于MySQL可以通过修改未压缩的数据来“删除标记”并“就地”删除压缩页面上的行，因此对表的DELETE操作相对有效。

对于某些环境，加载数据所需的时间与运行时检索一样重要。特别是在数据仓库环境中，许多表可能是只读的或只读的。在那些情况下，以增加加载时间为代价来付出压缩的代价可能会或可能不会，除非由此带来的减少磁盘读取次数或存储成本的节省是可观的。

从根本上讲，当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.

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时间压缩和解压缩数据来减少I / O，并且当I / O与处理器周期相比是相对稀缺的资源时，压缩是最有效的。

在具有快速，多核CPU的多用户环境中运行时，通常尤其如此。 当压缩表的页面位于内存中时，MySQL通常在缓冲池中使用额外的内存（通常为16KB）来存储页面的未压缩副本。 自适应LRU算法尝试平衡压缩页面和未压缩页面之间的内存使用，以考虑工作负载是以I / O绑定还是CPU绑定的方式运行。 尽管如此，使用压缩表时，具有更多专用于缓冲池的内存的配置往往会比内存受到高度限制的配置更好地运行。

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.

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.

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.

压缩页面大小的最佳设置取决于表及其索引包含的数据的类型和分布。 压缩的页面大小应始终大于最大记录大小，否则操作可能会失败，如“压缩B树页面”中所述。

将压缩的页面大小设置得太大会浪费一些空间，但是不必经常压缩页面。 如果将压缩的页面大小设置得太小，则插入或更新可能需要耗时的重新压缩，并且B树节点可能不得不更频繁地拆分，从而导致更大的数据文件和较低的索引编制效率。

通常，将压缩的页面大小设置为8K或4K字节。 鉴于InnoDB表的最大行大小约为8K，通常KEY_BLOCK_SIZE = 8是一个安全的选择。

 

14.9.1.4 Monitoring InnoDB Table Compression at Runtime

14.9.1.4在运行时监视InnoDB表压缩

 

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.9.1.3, “Tuning Compression for InnoDB Tables”, and shows how to find problems that might not turn up during initial testing.

整体应用程序性能，CPU和I / O利用率以及磁盘文件的大小是有效压缩应用程序的良好指标。 本节以第14.9.1.3节“为InnoDB表调整压缩”中的性能调整建议为基础，并说明如何查找在初始测试期间可能不会出现的问题。

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.1, “Using the Compression Information Schema Tables”. These tables reflect the internal use of memory and the rates of compression used overall.

要深入了解压缩表的性能注意事项，您可以使用示例14.1“使用压缩信息模式表”中所述的信息模式表在运行时监视压缩性能。 这些表反映了内存的内部使用以及总体上使用的压缩率。

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.

NNODB_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表都表示为聚簇索引，因此MySQL在这种情况下并没有在表和索引之间进行较大区分。）INNODB_CMP_PER_INDEX表确实涉及大量开销，因此它更适合于开发服务器，您可以在其中进行比较 隔离不同工作负载，数据和压缩设置的影响。 为防止意外施加此监视开销，必须先启用innodb_cmp_per_index_enabled配置选项，然后才能查询INNODB_CMP_PER_INDEX表。

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树节点太满而无法容纳修改后的压缩数据时会对其进行拆分，因此请将“成功”压缩操作的数量与总体此类压缩操作的数量进行比较。 根据INNODB_CMP和INNODB_CMP_PER_INDEX表中的信息以及整体应用程序性能和硬件资源利用率，您可以更改硬件配置，调整缓冲池的大小，选择不同的页面大小或选择不同的表集来 压缩。

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或多核CPU可以在相同的数据，应用程序工作量和一组压缩表的情况下帮助提高性能。 增大缓冲池的大小也可能有助于提高性能，从而使更多未压缩的页面可以保留在内存中，从而减少了对仅以压缩形式存在于内存中的页面进行解压缩的需求。

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.

总体上，大量的压缩操作（与应用程序中INSERT，UPDATE和DELETE操作的数量以及数据库的大小相比）可能表明某些压缩表的更新量太大，无法进行有效的压缩。 如果是这样，请选择更大的页面大小，或者对要压缩的表有更多选择

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树节点。 在这种情况下，请避免压缩某些表，或者为某些压缩表增加KEY_BLOCK_SIZE。 您可能会关闭导致应用程序中“压缩失败”次数超过总数的1％或2％的表的压缩。 （这样的故障率在诸如数据加载之类的临时操作期间可能是可以接受的）。

 

14.9.1.5 How Compression Works for InnoDB Tables

Innodb表压缩的工作方式

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％或更多。

Note

注意：

Prior to MySQL 5.7.24, InnoDB supports the zlib library up to version 1.2.3. In MySQL 5.7.24 and later, InnoDB supports the zlib library up to version 1.2.11.

在MySQL 5.7.24之前，InnoDB支持zlib库，最高版本为1.2.3。 在MySQL 5.7.24和更高版本中，InnoDB支持zlib库直到1.2.11版本。

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 file-per-table tablespace or general tablespace .ibd 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数据文件（每表文件表空间或常规表空间.ibd文件）的大小为未压缩大小的25％至50％或更小。 根据工作负载，这个较小的数据库反过来可以导致I / O减少和吞吐量增加，而就CPU利用率的提高而言，这是一个中等成本。 您可以通过修改innodb_compression_level配置选项来调整压缩级别和CPU开销之间的平衡。

 

InnoDB Data Storage and Compression

Innodb数据存储与压缩

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.

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.

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.

InnoDB表中的所有用户数据都存储在包含B树索引（聚集索引）的页面中。 在其他一些数据库系统中，这种类型的索引称为“索引组织表”。 索引节点中的每一行都包含（用户指定的或系统生成的）主键的值以及表的所有其他列。

InnoDB表中的二级索引也是B树，包含值对：索引键和指向聚集索引中行的指针。 实际上，指针是表的主键的值，如果需要除索引键和主键以外的列，则该指针用于访问聚簇索引。 次要索引记录必须始终适合单个B树页面。

B树节点（群集索引和二级索引）的压缩与用于存储长VARCHAR，BLOB或TEXT列的溢出页面的压缩方式不同，如以下各节所述。

 

Compression of B-Tree Pages

B-Tree页的压缩

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树页面经常更新，因此需要特殊对待。 重要的是，最小化B树节点的分割次数，以及最小化解压缩和重新压缩其内容的需求。

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树节点中的某些系统信息，从而促进某些就地更新。例如，这允许对行进行删除标记和删除，而无需任何压缩操作。

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树页面中，系统保留一个未压缩的 “ 修改日志 ”以记录对该页面所做的更改。小记录的更新和插入可以写入此修改日志，而无需完全重建整个页面。

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-树节点被分割，并重复该过程，直到更新或插入成功。

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 theinnodb_compression_failure_threshold_pct, and innodb_compression_pad_pct_max configuration options to fine-tune this mechanism.

为了避免在写入密集型工作负载（例如OLTP 应用程序）中频繁出现压缩失败，MySQL有时会在页面中保留一些空白空间（填充），这样修改日志就可以很快填满，并且在有足够空间避免拆分页面的情况下重新压缩页面。随着系统跟踪页面拆分的频率，每页中剩余的填充空间量会有所不同。在繁忙的服务器上频繁写入压缩表，您可以调整innodb_compression_failure_threshold_pct和 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树页面至少可容纳两个记录。对于压缩表，此要求已放宽。B树节点的叶子页（无论是主键还是辅助索引）仅需要容纳一条记录，但是该记录必须以未压缩的形式容纳在每页修改日志中。如果 innodb_strict_mode是 ON，则MySQL在CREATE TABLE或 CREATE INDEX 期间检查最大行大小。如果该行不适合，则会发出以下错误消息：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_modeOFF 时创建表 ，并且随后的INSERT或 UPDATE语句尝试创建不适合压缩页面大小的索引条目，则操作将失败ERROR 42000: Row size too large。（此错误消息不会为该记录太大的索引命名，也不会在该特定索引页上提及索引记录的长度或最大记录大小。）要解决此问题，请使用重建表ALTER TABLE并选择一个更大的表 压缩页面大小（KEY_BLOCK_SIZE），缩短任何列前缀索引，或完全使用ROW_FORMAT=DYNAMIC或 禁用压缩ROW_FORMAT=COMPACT。

innodb_strict_mode is not applicable to general tablespaces, which also support compressed tables. Tablespace management rules for general tablespaces are strictly enforced independently of innodb_strict_mode. For more information, see Section 13.1.19, “CREATE TABLESPACE Statement”.

innodb_strict_mode不适用于也支持压缩表的常规表空间。通用表空间的表空间管理规则独立于严格执行 innodb_strict_mode。有关更多信息，请参见第13.1.19节“ CREATE TABLESPACE语句”。

 

Compressing BLOB, VARCHAR, and TEXT Columns

压缩BLOB，VARCHAR和TEXT列

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，和 TEXT列不属于主键的一部分可以被存储在单独分配 溢出页。我们将这些列称为 页外列。它们的值存储在溢出页面的单链接列表中。

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或 ROW_FORMAT=COMPRESSED，的值 BLOB， TEXT或 VARCHAR列可以取决于它们的长度和整个行的长度存储完全关闭页。对于页面外存储的列，聚集索引记录仅包含指向溢出页面的20字节指针，每列一个。是否在页面外存储任何列取决于页面大小和行的总大小。当行太长而无法完全容纳在聚集索引页面中时，MySQL选择最长的列进行页外存储，直到该行适合聚集索引页面为止。如上所述，如果一行本身不适合压缩页面，则会发生错误。

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或 ROW_FORMAT=COMPRESSED， TEXT并且 BLOB是小于或等于40个字节的列总是存储在行。

 

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和 ROW_FORMAT=COMPACT。在这些格式中，MySQL存储第一768个字节的 BLOB， VARCHAR和TEXT列在聚簇索引记录与主密钥一起。768字节的前缀后跟一个20字节的指针，该指针指向包含其余列值的溢出页面。

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格式时，写入溢出页面的所有数据都按“ 原样 ”压缩；也就是说，MySQL将zlib压缩算法应用于整个数据项。除数据外，压缩的溢出页面还包含未压缩的头和尾，其中包括页面校验和以及到下一个溢出页面的链接等。因此，如果数据具有高度可压缩性（对于文本数据通常如此），则对于更长的或 列BLOB，可以获得非常显着的存储节省 。图像数据，例如TEXTVARCHARJPEG，通常已经被压缩，因此从压缩表中存储不会带来太多好处；双重压缩会浪费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.

溢出页面的大小与其他页面相同。即使列的总长度仅为8K字节，包含十列的页外存储的行也会占用十个溢出页。在未压缩的表中，十个未压缩的溢出页占用160K字节。在页面大小为8K的压缩表中，它们仅占用80K字节。因此，对于具有长列值的表，使用压缩表格式通常更为有效。

For file-per-table tablespaces, 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. General tablespaces do not support a 16K compressed page size (KEY_BLOCK_SIZE). For more information, see Section 14.6.3.3, “General Tablespaces”.

对于 file-per-talbe 的表空间，采用16K压缩页面大小可以降低存储和I / O开销BLOB， VARCHAR或 TEXT列，因为这些数据往往压缩得很好，因此可能需要更少的溢出页，即使在B树节点本身占用的页面与未压缩形式的页面一样多。常规表空间不支持16K压缩页面大小（KEY_BLOCK_SIZE）。有关更多信息，请参见 第14.6.3.3节“常规表空间”。

 

Compression and the InnoDB Buffer Pool

压缩和Innodb缓冲池

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 ifinnodb_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还是8K）都对应于16K字节的未压缩页（如果innodb_page_size设置，则为较小的页 ）。为了访问页面中的数据，MySQL从磁盘读取压缩的页面（如果它不在 缓冲池中），然后将页面解压缩为原始形式。本节介绍如何InnoDB针对压缩表的页面管理缓冲池。

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.

为了为其他必需的数据库页面腾出空间，MySQL可以 从缓冲池中逐出未压缩的页面，而将压缩后的页面保留在内存中。或者，如果一段时间未访问页面，则该页面的压缩形式可能会写入磁盘，以释放其他数据的空间。因此，在任何给定时间，缓冲池都可能包含页面的压缩形式和未压缩形式，或者仅包含页面的压缩形式，或者都不包含。

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使用最近使用最少的（LRU）列表来跟踪哪些页面保留在内存中以及哪些页面被逐出，以便 热（频繁访问）的数据倾向于保留在内存中。当访问压缩表时，MySQL使用自适应LRU算法来实现内存中已压缩和未压缩页面的适当平衡。此自适应算法对系统是在I / O限制还是 CPU限制下运行非常敏感方式。目的是避免在CPU繁忙时花费过多的处理时间来解压缩页面，并避免在CPU具有可用于解压缩压缩页面（可能已经在内存中）的空闲周期时执行过多的I / O。当系统受I / O限制时，该算法更喜欢驱逐页面的未压缩副本，而不是两个副本，以腾出更多空间让其他磁盘页面驻留在内存中。当系统受CPU限制时，MySQL倾向于退出压缩和未压缩的页面，以便更多的内存可用于“ 热 ”页面，从而减少了仅以压缩形式解压缩内存中数据的需求。

 

Compression and the InnoDB Redo Log Files

压缩和InnoDB重做日志文件

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将页面的副本写入重做日志（如果自上次将其写入数据库以来已对其进行了重新压缩）。这样做是为了确保重做日志可用于 崩溃恢复，即使在极少数情况下zlib库已升级且更改引起压缩数据的兼容性问题也是如此。因此，日志文件的大小有所增加 ，或者需要更频繁的 检查点，在使用压缩时可以预期。日志文件大小或检查点频率的增加量取决于以需要重组和重新压缩的方式修改压缩页面的次数。

Compressed tables require the Barracuda file format. To create a compressed table in a file-per-table tablespace, innodb_file_per_table must be enabled and innodb_file_format must be set to Barracuda. There is no dependence on the innodb_file_format setting when creating a compressed table in a general tablespace. For more information, see Section 14.6.3.3, “General Tablespaces”. The MySQL Enterprise Backup product supports the Barracuda file format.

压缩表需要 Barracuda 文件格式。要在每表文件表空间中创建压缩表，innodb_file_per_table必须启用该表 并将innodb_file_format 其设置为Barracuda。innodb_file_format在常规表空间中创建压缩表时，不依赖于 设置。有关更多信息，请参见第14.6.3.3节“常规表空间”。在 MySQL企业备份产品支持 的Barracuda文件格式。

 

14.9.1.6 Compression for OLTP Workloads

14.9.1.6 OLTP工作负载的压缩

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 websites 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 DELETEoperations.

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:

• 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_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_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_log_compressed_pages lets you disable writing of images of re-compressed pages to the redo log. Re-compression may occur when changes are made to compressed data. This option is enabled by default to prevent corruption that could occur if a different version of the zlib compression algorithm is used during recovery. If you are certain that the zlib version will not change, disable innodb_log_compressed_pages to reduce redo log generation for workloads that modify compressed data.

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.

传统上，InnoDB 压缩功能主要推荐用于只读或以只读为主的 工作负载，例如在 数据仓库 配置中。崛起SSD 存储设备，这是速度快，但比较小，价格昂贵，使得压缩也有吸引力的OLTP工作负载：高流量，交互式网站可以降低他们的存储需求，每秒（其I / O操作IOPS通过使用压缩的表）那些频繁的应用程序 INSERT， UPDATE和 DELETE操作。

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_log_compressed_pages 允许您禁用将重新压缩页面的图像写入重做日志。对压缩数据进行更改时，可能会发生重新压缩。默认情况下启用此选项，以防止zlib在恢复过程中使用不同版本的压缩算法时可能发生的损坏 。如果确定 zlib版本不会更改，请禁用 innodb_log_compressed_pages 以减少修改压缩数据的工作负载的重做日志生成。

因为处理压缩数据有时涉及同时将页面的压缩版本和未压缩版本同时保留在内存中，所以在将压缩与OLTP样式的工作负载一起使用时，请准备增加innodb_buffer_pool_size 配置选项的值 。

 

14.9.1.7 SQL Compression Syntax Warnings and Errors

14.9.1.7 SQL压缩语法警告和错误

 

This section describes syntax warnings and errors that you may encounter when using the table compression feature with file-per-table tablespaces and general tablespaces.

本节介绍将表压缩功能与每表文件表 空间和常规表空间一起使用时可能遇到的语法警告和错误 。

 

SQL Compression Syntax Warnings and Errors for File-Per-Table Tablespaces

每表文件表空间的SQL压缩语法警告和错误

When innodb_strict_mode is enabled (the default), specifying ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE in CREATE TABLE or ALTER TABLE statements produces the following error if innodb_file_per_table is disabled or if innodb_file_format is set to Antelope rather than Barracuda.

当innodb_strict_mode设置为 enabled（默认值），指定 ROW_FORMAT=COMPRESSED或 KEY_BLOCK_SIZE在CREATE TABLE或ALTER TABLE 语句如果产生以下错误innodb_file_per_table被禁用，或者如果 innodb_file_format被设定为 Antelope，而不是 Barracuda。

ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option

 

Note

The table is not created if the current configuration does not permit using compressed tables.

这些消息仅是警告，不是错误，并且创建表时没有进行压缩，就像未指定选项一样。

 

When innodb_strict_mode is disabled, specifying ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE in CREATE TABLE or ALTER TABLE statements produces the following warnings if innodb_file_per_table is disabled.

如果innodb_strict_mode禁用，则在禁用时在or 语句中指定ROW_FORMAT=COMPRESSEDor 会产生以下警告 。 KEY_BLOCK_SIZECREATE TABLEALTER TABLEinnodb_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. | +---------+------+---------------------------------------------------------------+

Similar warnings are issued if innodb_file_format is set to Antelope rather than Barracuda.

如果innodb_file_format设置为 Antelope而不是 Barracuda， 则会发出类似的警告。

 

Note

These messages are only warnings, not errors, and the table is created without compression, as if the options were not specified.

这些消息仅是警告，不是错误，并且创建表时没有进行压缩，就像未指定选项一样。

 

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.

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.

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

在“ 非严格的 ”行为可让您导入 mysqldump文件到不支持压缩的表，即使源数据库包含压缩表。在那种情况下，MySQL创建表 ROW_FORMAT=COMPACT而不是阻止操作。

要将转储文件导入新数据库，并重新创建表，使其与原始数据库中的表相同，请确保服务器为配置参数innodb_file_format和 配置了正确的设置 innodb_file_per_table。

KEY_BLOCK_SIZE仅当ROW_FORMAT指定为 COMPRESSED或省略时，才允许 该属性。KEY_BLOCK_SIZE与其他 ROW_FORMAT值一起指定会生成警告，您可以使用查看SHOW WARNINGS。但是，该表是未压缩的。指定的内容 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.

Table 14.6, “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.

如果运行 innodb_strict_mode启用状态，则a KEY_BLOCK_SIZE与ROW_FORMAT=“COMPRESSED”以外的其他 任何值组合都会 生成错误（不是警告），并且不会创建表。

表14.6“ ROW_FORMAT和KEY_BLOCK_SIZE选项” 概述了或 与一起使用的ROW_FORMAT和 KEY_BLOCK_SIZE选项 。 CREATE TABLEALTER TABLE

 

Table 14.6 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	File-per-table tablespaces require 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	File-per-table tablespaces require innodb_file​_format=Barracuda	Compresses the table and indexes using zlib
KEY_BLOCK_​SIZE=n	File-per-table tablespaces require innodb_file​_format=Barracuda	Specifies compressed page size of 1, 2, 4, 8 or 16 kilobytes; impliesROW_FORMAT=COMPRESSED. For general tablespaces, a KEY_BLOCK_SIZE value equal to the InnoDB page size is not permitted.

 

表14.6 ROW_FORMAT和KEY_BLOCK_SIZE选项

选项	使用说明	描述
ROW_FORMAT=​REDUNDANT	MySQL 5.0.3之前使用的存储格式	效率低于ROW_FORMAT=COMPACT; 向后兼容
ROW_FORMAT=​COMPACT	自MySQL 5.0.3起的默认存储格式	在聚集索引页中存储768字节长列值的前缀，其余字节存储在溢出页中
ROW_FORMAT=​DYNAMIC	每表文件表空间要求 innodb_file​_format=Barracuda	将值存储在聚集索引页面中（如果合适）；如果不是，则仅存储指向溢出页面的20字节指针（无前缀）
ROW_FORMAT=​COMPRESSED	每表文件表空间要求 innodb_file​_format=Barracuda	使用zlib压缩表和索引
KEY_BLOCK_​SIZE=n	每表文件表空间要求 innodb_file​_format=Barracuda	指定压缩的页面大小为1、2、4、8或16 KB；暗示ROW_FORMAT=COMPRESSED。对于常规表空间，不允许使用KEY_BLOCK_SIZE等于InnoDB页面大小的值。

 

Table 14.7, “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.

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:

表14.7“当InnoDB严格模式为OFF时，创建/更改表警告和错误” 总结了在CREATE TABLEor ALTER TABLE语句上使用配置参数和选项的某些组合所发生的错误情况 ，以及这些选项如何出现在输出中SHOW TABLE STATUS。

当innodb_strict_mode是 OFF时，MySQL创建或改变表，但如下所示忽略某些设置。您可以在MySQL错误日志中看到警告消息。当 innodb_strict_mode为时 ON，这些指定的选项组合会生成错误，并且不会创建或更改表。要查看错误条件的完整描述，请发出以下 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.7 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 orKEY_BLOCK_SIZE is specified	Ignored for file-per-table tablespaces unless bothinnodb_file_format=Barracuda and innodb_file_per_table are enabled. General tablespaces support all row formats (with some restrictions) regardless of innodb_file_format andinnodb_file_per_table settings. See Section 14.6.3.3, “General Tablespaces”.	the default row format for file-per-table tablespaces; the specified row format for general tablespaces
Invalid KEY_BLOCK_SIZE is specified (not 1, 2, 4, 8 or 16)	KEY_BLOCK_SIZE is ignored	the specified row format, or the default row format
ROW_FORMAT=COMPRESSED and valid KEY_BLOCK_SIZEare specified	None; KEY_BLOCK_SIZE specified is used	COMPRESSED
KEY_BLOCK_SIZE is specified with REDUNDANT, COMPACTor DYNAMIC row format	KEY_BLOCK_SIZE is ignored	REDUNDANT, COMPACT or DYNAMIC
ROW_FORMAT is not one of REDUNDANT, COMPACT, DYNAMICor COMPRESSED	Ignored if recognized by the MySQL parser. Otherwise, an error is issued.	the default row format or N/A

 

表14.7 InnoDB严格模式为OFF时的CREATE / ALTER TABLE警告和错误

句法	警告或错误情况	ROW_FORMAT产生结果如下所示（SHOW TABLE STATUS ）
ROW_FORMAT=REDUNDANT	没有	REDUNDANT
ROW_FORMAT=COMPACT	没有	COMPACT
ROW_FORMAT=COMPRESSED或ROW_FORMAT=DYNAMIC或KEY_BLOCK_SIZE已指定	除非同时 启用 和，否则忽略每表文件表空间 。通用表空间支持所有行格式（有一些限制），而与 设置无关 。请参见第14.6.3.3节“常规表空间”。innodb_file_format=Barracudainnodb_file_per_tableinnodb_file_formatinnodb_file_per_table	the default row format for file-per-table tablespaces; the specified row format for general tablespaces
KEY_BLOCK_SIZE指定了无效的（不是1、2、4、8或16）	KEY_BLOCK_SIZE 被忽略	指定的行格式或默认行格式
ROW_FORMAT=COMPRESSED和有效 KEY_BLOCK_SIZE被指定	没有; KEY_BLOCK_SIZE指定使用	COMPRESSED
KEY_BLOCK_SIZE指定为REDUNDANT，COMPACT 或DYNAMIC行格式	KEY_BLOCK_SIZE 被忽略	REDUNDANT，COMPACT或DYNAMIC
ROW_FORMAT不是REDUNDANT，COMPACT，DYNAMIC或 COMPRESSED 其中的一个	如果被MySQL解析器识别，则将其忽略。否则，将发出错误。	默认行格式或不适用

 

When innodb_strict_mode is ON, MySQL rejects invalid ROW_FORMAT or KEY_BLOCK_SIZE parameters and issues errors. When innodb_strict_mode is OFF, MySQL issues warnings instead of errors for ignored invalid parameters. innodb_strict_mode is ON by default.

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.

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.

当innodb_strict_mode是 ON时，MySQL拒绝无效 ROW_FORMAT或 KEY_BLOCK_SIZE参数问题的错误。当innodb_strict_mode为时 OFF，MySQL会针对被忽略的无效参数发出警告而不是错误。 innodb_strict_mode是ON 默认。

当innodb_strict_mode是 ON时，MySQL拒绝无效 ROW_FORMAT或 KEY_BLOCK_SIZE参数。为了与早期版本的MySQL兼容，默认情况下未启用严格模式；相反，MySQL会为忽略的无效参数发出警告（不是错误）。

这是不可能看到所选择的 KEY_BLOCK_SIZE使用SHOW TABLE STATUS。该语句SHOW CREATE TABLE显示KEY_BLOCK_SIZE （即使在创建表时忽略了该语句）。MySQL无法显示该表的实际压缩页面大小。

 

SQL Compression Syntax Warnings and Errors for General Tablespaces

常规表空间的SQL压缩语法警告和错误

 

• If FILE_BLOCK_SIZE was not defined for the general tablespace when the tablespace was created, the tablespace cannot contain compressed tables. If you attempt to add a compressed table, an error is returned, as shown in the following example:

如果FILE_BLOCK_SIZE在创建表空间时未为通用表空间定义该表空间，则该表空间不能包含压缩表。如果尝试添加压缩表，则会返回错误，如以下示例所示：

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB; mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; ERROR 1478 (HY000): InnoDB: Tablespace `ts1` cannot contain a COMPRESSED table

• Attempting to add a table with an invalid KEY_BLOCK_SIZE to a general tablespace returns an error, as shown in the following example:

尝试将具有无效表的表添加 KEY_BLOCK_SIZE到常规表空间将返回错误，如以下示例所示：

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB; mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; ERROR 1478 (HY000): InnoDB: Tablespace `ts2` uses block size 8192 and cannot contain a table with physical page size 4096

For general tablespaces, the KEY_BLOCK_SIZE of the table must be equal to the FILE_BLOCK_SIZE of the tablespace divided by 1024. For example, if theFILE_BLOCK_SIZE of the tablespace is 8192, the KEY_BLOCK_SIZE of the table must be 8.

对于常规表空间， KEY_BLOCK_SIZE表的值必须等于FILE_BLOCK_SIZE表空间的值除以1024。例如，如果 FILE_BLOCK_SIZE表空间的值是8192，KEY_BLOCK_SIZE则表的值必须是8。

• Attempting to add a table with an uncompressed row format to a general tablespace configured to store compressed tables returns an error, as shown in the following example:

尝试将具有未压缩行格式的表添加到配置为存储压缩表的常规表空间中将返回错误，如以下示例所示：

mysql> CREATE TABLESPACE `ts3` ADD DATAFILE 'ts3.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB; mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts3 ROW_FORMAT=COMPACT; ERROR 1478 (HY000): InnoDB: Tablespace `ts3` uses block size 8192 and cannot contain a table with physical page size 16384

innodb_strict_mode is not applicable to general tablespaces. Tablespace management rules for general tablespaces are strictly enforced independently ofinnodb_strict_mode. For more information, see Section 13.1.19, “CREATE TABLESPACE Statement”.

For more information about using compressed tables with general tablespaces, see Section 14.6.3.3, “General Tablespaces”.

innodb_strict_mode不适用于常规表空间。通用表空间的表空间管理规则独立于严格执行 innodb_strict_mode。有关更多信息，请参见第13.1.19节“ CREATE TABLESPACE语句”。

有关将压缩表与常规表空间一起使用的更多信息，请参见第14.6.3.3节“常规表空间”。