8.4.1 优化数据大小

设计 table 以最小化它们在磁盘上的空间。通过减少写入磁盘和从磁盘读取的数据量，这可以带来巨大的改进。较小的 table 通常需要较少的主内存，而在查询执行期间对它们的内容进行有效处理时。table 数据的任何空间减少都会导致索引变小，从而可以更快地处理索引。

MySQL 支持许多不同的存储引擎(table 类型)和行格式。对于每个 table，您可以决定使用哪种存储和索引方法。为您的应用程序选择适当的 table 格式可以大大提高性能。参见第 14 章，InnoDB 存储引擎和第 15 章，备用存储引擎。

通过使用此处列出的技术，可以提高 table 的性能，并最大程度地减少存储空间：

• Table Columns

• Row Format

• Indexes

• Joins

• Normalization

Table Columns

• 尽可能使用最有效(最小)的数据类型。 MySQL 具有许多专门的类型，可以节省磁盘空间和内存。例如，如果可能，使用较小的整数类型以获得较小的 table。与INT相比，MEDIUMINT通常是更好的选择，因为MEDIUMINT列使用的空间要少 25％。

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

Row Format

• 默认情况下，InnoDBtable 是使用DYNAMIC行格式创建的。要使用DYNAMIC以外的行格式，请配置innodb_default_row_format，或在CREATE TABLE或ALTER TABLE语句中显式指定ROW_FORMAT选项。

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

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

• 为了通过压缩形式存储 table 数据来进一步减少空间，请在创建InnoDBtable 时指定ROW_FORMAT=COMPRESSED，或者在现有MyISAMtable 上运行myisampack命令。 (InnoDB压缩 table 是可读写的，而MyISAM压缩 table 是只读的.)

• 对于MyISAMtable，如果没有任何变长列(VARCHAR，TEXT或BLOB列)，则使用固定大小的行格式。这样比较快，但可能会浪费一些空间。参见第 15.2.3 节“ MyISAMtable 存储格式”。即使具有CREATE TABLE选项ROW_FORMAT=FIXED的VARCHAR列，您也可以暗示要具有固定长度的行。

Indexes

• table 的主索引应尽可能短。这使得识别每一行变得容易且有效。对于InnoDBtable，主键列在每个辅助索引条目中重复，因此如果您有许多辅助索引，则较短的主键可节省大量空间。

• 仅创建需要提高查询性能的索引。索引很适合检索，但是会降低插入和更新操作的速度。如果您主要通过搜索列的组合来访问 table，请在 table 上创建单个组合索引，而不是为每个列创建单独的索引。索引的第一部分应该是最常用的列。如果从 table 中选择时总是使用很多列，则索引中的第一列应该是重复次数最多的列，以更好地压缩索引。

• 如果长字符串列很可能在第一个字符数上具有唯一的前缀，则最好使用 MySQL 支持在该列的最左侧创建索引的方式仅对此前缀进行索引(请参见第 13.1.14 节“ CREATE INDEX 语句”)。索引越短越快，这不仅是因为它们需要较少的磁盘空间，而且还因为它们还会使索引缓存中的命中次数增加，从而减少了磁盘寻道。参见第 5.1.1 节“配置服务器”。

Joins

• 在某些情况下，将经常扫描的 table 分成两部分可能会有所帮助。如果它是动态格式 table，并且可以使用较小的静态格式 table(在扫描 table 时可以用来查找相关行)，则尤其如此。

• 在具有相同数据类型的不同 table 中声明具有相同信息的列，以加快基于相应列的联接。

• 使列名保持简单，以便您可以在不同的 table 中使用相同的名称并简化联接查询。例如，在名为customer的 table 中，使用列名name而不是customer_name。为了使您的名称可移植到其他 SQL Server 中，请考虑使名称短于 18 个字符。

Normalization

• 通常，请尝试使所有数据保持非冗余(请注意数据库理论中称为第三范式的内容)。不必重复冗长的值(例如名称和地址)，而是给它们分配唯一的 ID，在多个较小的 table 中根据需要重复这些 ID，然后通过引用 join 子句中的 ID 在查询中联接这些 table。

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