Skip to main content

1.优化数据大小

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

MySQL 支持许多不同的存储引擎（表类型）和行格式。 对于每个表，可以决定使用哪种存储和索引方法。 为应用程序选择正确的表格式可以给带来巨大的性能提升。

通过使用此处列出的技术，可以获得更好的表性能并最小化存储空间：

  • 表格列
  • 行格式
  • 索引
  • 加入
  • 正常化

表格列​

尽可能使用最有效（最小）的数据类型。 MySQL 有许多专门的类型可以节省磁盘空间和内存。 例如，如果可能的话，使用较小的整数类型以获得较小的表。 MEDIUMINT 通常是比 INT 更好的选择，因为 MEDIUMINT 列使用的空间少 25%。

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

行格式​

默认情况下，InnoDB 表是使用 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 × 字符集的最大字节长度。 许多语言主要可以使用单字节utf8mb3或utf8mb4字符来编写，因此固定的存储长度通常会浪费空间。 通过行格式的紧凑系列，InnoDB 通过去除尾随空格为这些列分配可变的存储量，范围为 N 到 N × 字符集的最大字节长度。 最小存储长度为 N 字节，以便在典型情况下进行就地更新。

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

对于 MyISAM 表，如果没有任何可变长度列（VARCHAR、TEXT 或 BLOB 列），则使用固定大小的行格式。 这更快，但可能会浪费一些空间。 请参见第 18.2.3 节“MyISAM 表存储格式”。 即使您有带有 CREATE TABLE 选项 ROW_FORMAT=FIXED 的 VARCHAR 列，您也可以暗示您希望拥有固定长度的行。

索引​

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

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

如果长字符串列很可能在第一个字符上有唯一的前缀，那么最好只索引这个前缀，使用 MySQL 对在列的最左边部分创建索引的支持（参见第 15.1.15 节） ，“创建索引语句”）。 较短的索引速度更快，不仅因为它们需要更少的磁盘空间，而且因为它们还可以在索引缓存中提供更多命中，从而减少磁盘寻道。

Joins​

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

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

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

正常化（Normalization）​

通常，尝试使所有数据保持非冗余（遵守数据库理论中称为第三范式的内容）。 不要重复名称和地址等冗长的值，而是为它们分配唯一的 ID，根据需要在多个较小的表中重复这些 ID，并通过在连接子句中引用 ID 来连接查询中的表。

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