Documentation Home
MySQL 8.3 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual

MySQL 8.3 Reference Manual  /  ...  /  Optimizing Data Size

10.4.1 优化数据大小

设计表以最小化磁盘上的空间。这可以通过减少写入和读取磁盘的数据量来实现巨大的改进。较小的表格通常需要较少的主内存,而在查询执行期间处理表格内容时也需要较少的内存。表格数据的空间减少也将导致较小的索引,可以更快地处理。

MySQL 支持多种不同的存储引擎(表类型)和行格式。对于每个表,您可以决定使用哪种存储和索引方法。选择适合应用程序的正确表格格式可以带来巨大的性能提升。请参阅 第 17 章,InnoDB 存储引擎第 18 章,替代存储引擎

您可以使用以下技术来提高表格性能和最小化存储空间:

表列

  • 使用尽可能高效(小)的数据类型。MySQL 有许多专门的类型可以节省磁盘空间和内存。例如,如果可能,请使用较小的整数类型以获取较小的表格。MEDIUMINT 通常比 INT 更好,因为 MEDIUMINT 列使用 25% 少的空间。

  • 如果可能,请将列声明为 NOT NULL。这可以使 SQL 操作更快,因为它可以更好地使用索引并消除测试每个值是否为 NULL 的开销。您还可以节省一些存储空间,每列一个位。如果您真的需要表格中的 NULL 值,请使用它们。只是避免每列都允许 NULL 值的默认设置。

行格式

  • InnoDB 表默认使用 DYNAMIC 行格式。如果要使用其他行格式,而不是 DYNAMIC,请配置 innodb_default_row_format,或在 CREATE TABLEALTER TABLE 语句中明确指定 ROW_FORMAT 选项。

    紧凑型行格式家族,包括 COMPACTDYNAMICCOMPRESSED,可以减少行存储空间,但增加了某些操作的 CPU 使用。如果您的工作负载是典型的,受限于缓存命中率和磁盘速度,那么它可能会更快。如果您的工作负载是罕见的,受限于 CPU 速度,那么它可能会更慢。

    紧凑型行格式家族还优化了 CHAR 列存储,当使用可变长度字符集,如 utf8mb3utf8mb4 时。使用 ROW_FORMAT=REDUNDANTCHAR(N) 占用 N × 字符集的最大字节长度。许多语言可以主要使用单字节 utf8mb3utf8mb4 字符,因此固定存储长度通常会浪费空间。使用紧凑型行格式,InnoDB 分配可变数量的存储空间,范围从 NN × 字符集的最大字节长度,为这些列剥离尾随空格。最小存储长度是 N 字节,以便在典型情况下进行就地更新。有关更多信息,请参阅 第 17.10 节,“InnoDB 行格式”

  • 要进一步减少空间,可以在创建 InnoDB 表时指定 ROW_FORMAT=COMPRESSED,或在现有的 MyISAM 表上运行 myisampack 命令。(InnoDB 压缩表是可读写的,而 MyISAM 压缩表是只读的。)

  • 对于 MyISAM 表,如果您没有任何变长列(VARCHARTEXTBLOB 列),将使用固定大小的行格式。这更快,但可能会浪费一些空间。请参阅 第 18.2.3 节,“MyISAM 表存储格式”。您可以使用 CREATE TABLE 选项 ROW_FORMAT=FIXED,即使您有 VARCHAR 列,也可以强制使用固定长度的行。

索引

  • 表的主索引应该尽可能短。这使得每行的标识变得容易和高效。对于 InnoDB 表,主键列将在每个次要索引条目中被复制,因此短主键可以在您有许多次要索引时节省大量空间。

  • 仅创建需要提高查询性能的索引。索引对检索很有帮助,但会减慢插入和更新操作。如果您经常根据多个列的组合来访问表,请创建一个组合索引,而不是每个列一个索引。索引的第一部分应该是最常用的列。如果您 总是 使用多个列来从表中选择数据,那么索引的第一列应该是具有最多重复的列,以便更好地压缩索引。

  • 如果长字符串列的前缀非常可能是唯一的,那么最好只索引该前缀,使用 MySQL 对列左侧部分的索引支持(请参阅 第 15.1.15 节,“CREATE INDEX 语句”)。较短的索引不仅因为它们需要较少的磁盘空间,还因为它们也可以在索引缓存中提供更多的命中,从而减少磁盘寻道。请参阅 第 7.1.1 节,“配置服务器”

连接

  • 在某些情况下,将经常扫描的表拆分为两个表可能是有益的。这尤其适用于动态格式表,如果可以使用较小的静态格式表来找到扫描表时的相关行。

  • 在不同的表中声明具有相同信息的列,以相同的数据类型,以加速基于相应列的连接。

  • 保持列名简单,以便在不同的表中使用相同的名称,并简化连接查询。例如,在名为 customer 的表中,使用列名 name 而不是 customer_name。为了使名称在其他 SQL 服务器上可移植,考虑将它们保持在 18 个字符以内。

标准化

  • 通常,尝试使所有数据非冗余(遵守数据库理论中的第三范式)。 Instead of repeating lengthy values such as names and addresses, assign them unique IDs, repeat these IDs as needed across multiple smaller tables, and join the tables in queries by referencing the IDs in the join clause.

  • 如果速度比磁盘空间和维护多个数据副本的成本更重要,例如在业务智能场景中分析大表中的所有数据,可以放松标准化规则,复制信息或创建汇总表以获得更高的速度。