一旦数据达到稳定大小，或者不断增长的表增加了数十或数百兆字节，请考虑使用 OPTIMIZE TABLE 语句重新组织表并压缩任何浪费的空间。重组后的表需要更少的磁盘 I/O 来执行全表扫描。这是一种简单的技术，当改进索引使用或调整应用程序代码等其他技术不可行时，它可以提高性能。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. 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.

OPTIMIZE TABLE 复制表的数据部分并重建索引。好处来自于索引内数据打包的改进，以及表空间和磁盘上碎片的减少。好处因每个表中的数据而异。您可能会发现某些人获得了显着的收益，而另一些人则没有，或者收益随着时间的推移而减少，直到您下次优化表为止。如果表很大或者正在重建的索引不适合缓冲池，则此操作可能会很慢。向表中添加大量数据后的第一次运行通常比以后的运行慢得多。OPTIMIZE TABLE copies the data part of the table and rebuilds the indexes. 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.

在 InnoDB 中，拥有较长的 PRIMARY KEY（无论是具有较长值的单个列，还是形成较长复合值的多个列）会浪费大量磁盘空间。一行的主键值在指向同一行的所有二级索引记录中重复。 （请参阅第 17.6.2.1 节“聚集索引和二级索引”。）如果您的主键很长，请创建一个 AUTO_INCREMENT 列作为主键，或者索引一个长 VARCHAR 列的前缀而不是索引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. The primary key value for a row is duplicated in all the secondary index records that point to the same row. (See Section 17.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.

使用 VARCHAR 数据类型而不是 CHAR 来存储可变长度字符串或具有多个 NULL 值。 CHAR(N) 列始终使用 N 个字符来存储数据，即使字符串较短或其值为 NULL 也是如此。较小的表更适合缓冲池并减少磁盘 I/O。Use the VARCHAR data type instead of CHAR to store variable-length strings or for columns with many NULL values. 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.

当使用 COMPACT 行格式和可变长度字符集（例如 utf8mb4 或 sjis）时，CHAR(N) 列占用的空间量不同，但仍至少 N 个字节。When using COMPACT row format and variable-length character sets, such as utf8mb4 or sjis, CHAR(N) columns occupy a variable amount of space, but still at least N bytes.

对于较大的表或包含大量重复文本或数字数据的表，请考虑使用 COMPRESSED 行格式。将数据放入缓冲池或执行全表扫描所需的磁盘 I/O 较少。在做出永久决定之前，请衡量使用 COMPRESSED 与 COMPACT 行格式可以实现的压缩量。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. Before making a permanent decision, measure the amount of compression you can achieve by using COMPRESSED versus COMPACT row format.