Documentation Home
MySQL 8.0 参考手册  / 第8章优化  / 8.4 优化数据库结构  /  8.4.1 优化数据大小

8.4.1 优化数据大小

设计您的表以最小化它们在磁盘上的空间。通过减少写入磁盘和从磁盘读取的数据量,可以带来巨大的改进。较小的表通常需要较少的主内存,而它们的内容在查询执行期间被主动处理。表数据的任何空间减少也会导致可以更快处理的更小索引。

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

通过使用此处列出的技术,您可以获得更好的表性能并最大限度地减少存储空间:

表列

  • 尽可能使用最有效(最小)的数据类型。MySQL 有许多专门的类型可以节省磁盘空间和内存。例如,尽可能使用较小的整数类型来获得较小的表。 MEDIUMINT通常是一个更好的选择,INT因为 MEDIUMINT列使用的空间少了 25%。

  • NOT NULL如果可能 ,将列声明为。通过更好地使用索引并消除测试每个值是否为NULL. 您还可以节省一些存储空间,每列一位。如果您确实需要NULL表中的值,请使用它们。只需避免允许 NULL每列中的值的默认设置。

行格式

  • InnoDB默认情况下,表是使用 DYNAMIC行格式创建的。要使用 以外的行格式,请在or语句 中DYNAMIC配置 innodb_default_row_format或明确指定ROW_FORMAT选项。CREATE TABLEALTER TABLE

    紧凑的行格式系列(包括 COMPACTDYNAMICCOMPRESSED)以增加某些操作的 CPU 使用为代价减少了行存储空间。如果您的工作负载是受缓存命中率和磁盘速度限制的典型工作负载,则它可能会更快。如果是极少数受 CPU 速度限制的情况,它可能会更慢。

    CHAR在使用可变长度字符集(例如 utf8mb3或)时 ,紧凑的行格式系列还可以优化 列存储utf8mb4。同ROW_FORMAT=REDUNDANT, 占据× 字符集的最大字节长度。许多语言可以主要使用单字节 或 字符来编写,因此固定的存储长度通常会浪费空间。使用紧凑的行格式系列, 分配范围为到 的可变存储量CHAR(N)Nutf8mb3utf8mb4InnoDBNN× 通过去除尾随空格,这些列的字符集的最大字节长度。最小存储长度为 N字节,以便在典型情况下进行就地更新。有关更多信息,请参阅 第 15.10 节,“InnoDB 行格式”

  • 要通过以压缩形式存储表数据来进一步减少空间, 请ROW_FORMAT=COMPRESSED在创建 InnoDB表时指定,或 在现有 表上运行myisampack命令。MyISAMInnoDB压缩表是可读可写的,而MyISAM压缩表是只读的。)

  • 对于MyISAM表,如果您没有任何可变长度列(VARCHARTEXTBLOB列),则使用固定大小的行格式。这样速度更快,但可能会浪费一些空间。请参阅第 16.2.3 节,“MyISAM 表存储格式”。你可以暗示你想要固定长度的行,即使你有VARCHARCREATE TABLE选项 的列ROW_FORMAT=FIXED

索引

  • 表的主索引应尽可能短。这使得每一行的识别变得容易和高效。对于InnoDB表,主键列在每个二级索引条目中都是重复的,因此如果您有许多二级索引,则短主键可以节省大量空间。

  • 只创建提高查询性能所需的索引。索引有利于检索,但会减慢插入和更新操作。如果您主要通过搜索列的组合来访问表,请在它们上创建单个复合索引,而不是为每一列创建单独的索引。索引的第一部分应该是最常用的列。如果从表中选择时总是使用很多列,索引中的第一列应该是重复次数最多的列,以获得更好的索引压缩。

  • 如果一个长字符串列很可能在第一个字符上有一个唯一的前缀,最好只索引这个前缀,使用 MySQL 对在列的最左边部分创建索引的支持(​​见第 13.1.15 节) ,“CREATE INDEX 语句”)。较短的索引速度更快,不仅因为它们需要更少的磁盘空间,还因为它们还可以让您在索引缓存中获得更多的命中率,从而减少磁盘寻道。请参阅 第 5.1.1 节,“配置服务器”

加入

  • 在某些情况下,将一个经常扫描的表分成两个可能是有益的。如果它是动态格式表并且可以使用较小的静态格式表,在扫描表时可用于查找相关行,则尤其如此。

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

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

正常化

  • 通常,尽量保持所有数据不冗余(观察数据库理论中称为 第三范式的内容)。与其重复名称和地址等冗长的值,不如为它们分配唯一 ID,根据需要在多个较小的表中重复这些 ID,并通过在连接子句中引用 ID 来连接查询中的表。

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