设计表以最小化磁盘上的空间。这可以通过减少写入和读取磁盘的数据量来实现巨大的改进。较小的表格通常需要较少的主内存,而在查询执行期间处理表格内容时也需要较少的内存。表格数据的空间减少也将导致较小的索引,可以更快地处理。
MySQL 支持多种不同的存储引擎(表类型)和行格式。对于每个表,您可以决定使用哪种存储和索引方法。选择适合应用程序的正确表格格式可以带来巨大的性能提升。请参阅 第 17 章,InnoDB 存储引擎 和 第 18 章,替代存储引擎。
您可以使用以下技术来提高表格性能和最小化存储空间:
-
InnoDB
表默认使用DYNAMIC
行格式。如果要使用其他行格式,而不是DYNAMIC
,请配置innodb_default_row_format
,或在CREATE TABLE
或ALTER TABLE
语句中明确指定ROW_FORMAT
选项。紧凑型行格式家族,包括
COMPACT
、DYNAMIC
和COMPRESSED
,可以减少行存储空间,但增加了某些操作的 CPU 使用。如果您的工作负载是典型的,受限于缓存命中率和磁盘速度,那么它可能会更快。如果您的工作负载是罕见的,受限于 CPU 速度,那么它可能会更慢。紧凑型行格式家族还优化了
CHAR
列存储,当使用可变长度字符集,如utf8mb3
或utf8mb4
时。使用ROW_FORMAT=REDUNDANT
,CHAR(
占用N
)N
× 字符集的最大字节长度。许多语言可以主要使用单字节utf8mb3
或utf8mb4
字符,因此固定存储长度通常会浪费空间。使用紧凑型行格式,InnoDB
分配可变数量的存储空间,范围从N
到N
× 字符集的最大字节长度,为这些列剥离尾随空格。最小存储长度是N
字节,以便在典型情况下进行就地更新。有关更多信息,请参阅 第 17.10 节,“InnoDB 行格式”。 -
要进一步减少空间,可以在创建
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 节,“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.
-
如果速度比磁盘空间和维护多个数据副本的成本更重要,例如在业务智能场景中分析大表中的所有数据,可以放松标准化规则,复制信息或创建汇总表以获得更高的速度。