MySQL 5.7-8.5.1 Optimizing Storage Layout for InnoDB Tables

InnoDB is the storage engine that MySQL customers typically use in production databases where reliability and concurrency are important. InnoDB is the default storage engine in MySQL.

InnoDB是MySQL客户通常在可靠性和并发性很重要的生产数据库中使用的存储引擎。InnoDB是MySQL默认的存储引擎。

This section explains how to optimize database operations for InnoDB tables.

本节介绍如何优化InnoDB表的数据库操作。

Once your data reaches a stable size, or a growing table has increased by tens or some hundreds of megabytes, consider using the OPTIMIZE TABLE statement to reorganize the table and compact any wasted space.

一旦数据达到稳定的大小，或者正在增长的表增加了几十或几百兆字节，可以考虑使用OPTIMIZE table语句来重组表并压缩任何浪费的空间。

The reorganized tables require less disk I/O to perform full table scans. This is a straightforward technique that can improve performance when other techniques such as improving index usage or tuning application code are not practical.

重组后的表执行全表扫描所需的磁盘I/O更少。这是一种直接的技术，可以在其他技术(如改进索引使用或调优应用程序代码)不实用时提高性能。

OPTIMIZE TABLE copies the data part of the table and rebuilds the indexes.

OPTIMIZE TABLE复制表的数据部分并重新构建索引。

The benefits come from improved packing of data within indexes, and reduced fragmentation within the tablespaces and on disk.

其好处在于改进了索引中的数据打包，减少了表空间和磁盘上的碎片。

The benefits vary depending on the data in each table. You may find that there are significant gains for some and not for others, or that the gains decrease over time until you next optimize the table.

好处取决于每个表中的数据。您可能会发现，对某些表有显著的好处，而对另一些则没有，或者在您下次优化表之前，这些好处会随着时间的推移而减少。

This operation can be slow if the table is large or if the indexes being rebuilt do not fit into the buffer pool. The first run after adding a lot of data to a table is often much slower than later runs.

如果表很大，或者正在重建的索引不适合缓冲池，则该操作可能会很慢。向表中添加大量数据后的第一次运行通常比以后的运行慢得多。

In InnoDB, having a long PRIMARY KEY (either a single column with a lengthy value, or several columns that form a long composite value) wastes a lot of disk space.

在InnoDB中，主键很长(单个列有一个很长的值，或者多个列组成一个很长的复合值)会浪费大量的磁盘空间。

The primary key value for a row is duplicated in all the secondary index records that point to the same row.

一行的主键值在指向同一行的所有二级索引记录中重复。

(See Section 14.6.2.1, “Clustered and Secondary Indexes”.) Create an AUTO_INCREMENT column as the primary key if your primary key is long, or index a prefix of a long VARCHAR column instead of the entire column.

如果主键很长，则创建一个AUTO_INCREMENT列作为主键，或者索引一个长VARCHAR列的前缀，而不是整个列。

Use the VARCHAR data type instead of CHAR to store variable-length strings or for columns with many NULL values.

使用VARCHAR数据类型而不是CHAR来存储变长字符串或包含许多NULL值的列。

A CHAR(N) column always takes N characters to store data, even if the string is shorter or its value is NULL. Smaller tables fit better in the buffer pool and reduce disk I/O.

CHAR(N)列总是使用N个字符来存储数据，即使字符串更短或其值为NULL。较小的表更适合缓冲池，并减少磁盘I/O。

When using COMPACT row format (the default InnoDB format) and variable-length character sets, such as utf8 or sjis, CHAR(N) columns occupy a variable amount of space, but still at least N bytes.

当使用COMPACT行格式(默认的InnoDB格式)和可变长度字符集(如utf8或sjis)时，CHAR(N)列会占用可变数量的空间，但仍然至少有N个字节。

For tables that are big, or contain lots of repetitive text or numeric data, consider using COMPRESSED row format. Less disk I/O is required to bring data into the buffer pool, or to perform full table scans.

对于较大的表，或包含大量重复文本或数字数据的表，考虑使用COMPRESSED行格式。将数据放入缓冲池或执行全表扫描所需的磁盘I/O更少。

Before making a permanent decision, measure the amount of compression you can achieve by using COMPRESSED versus COMPACT row format.

在做出永久的决定之前，先衡量一下使用COMPRESSED和COMPACT行格式可以实现的压缩量。