8.4 Optimizing Database Structure

8.4 Optimizing Database Structure

8.4.1 Optimizing Data Size

8.4.2 Optimizing MySQL Data Types

8.4.3 Optimizing for Many Tables

8.4.4 Internal Temporary Table Use in MySQL

如果你的角色是一名数据库设计人员，寻找最高效的方法去设计模式、表、列信息是你的职责。将相关项放在一起可以最小化 I/O，以便在数据量增加时保持较高的性能。高效的数据库设计可以使团队成员更容易编写高性能的应用程序代码，并使数据库能够随着应用程序的发展和重写而持续存在。

8.4.1 Optimizing Data Size

设计表以最小化它们在磁盘上的空间。较小的表在查询执行期间处理其内容时，通常需要较少的主内存。表数据的任何空间缩减也会导致索引更小，处理速度更快。

MySQL 支持许多不同的存储引擎表类型和行格式。对于每个表可以决定使用哪种存储和索引方法。为应用程序选择合适的表格式可以大大提高性能。

可以使用下面列出的技术获得更好的表性能和最小化存储空间：

Table Columns

Row Format

Indexes

Joins

Normalization

Table Columns

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

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

Row Format

InnoDB 表默认使用动态行格式创建。若要使用除动态之外的行格式，请配置 innodb_default_row_format，或在 CREATE TABLE 或 ALTER TABLE 语句中显式指定 ROW_FORMAT 选项。

紧凑的行格式系列包括紧凑的、动态的和压缩的，以增加某些操作的 CPU 使用为代价，减少了行存储空间。如果你的工作负载是典型的受缓存命中率和磁盘速度限制的工作负载，那么它可能会更快。如果这是一个罕见的情况，受 CPU 速度限制，它可能会更慢。

当使用可变长度字符集如 utf8mb3 或 utf8mb4 时，紧凑的行格式系列还可以优化 CHAR 列存储。ROW_FORMAT=REDUNDANT,CHAR(N) 占用的最大字节长度 N× 字符集。With the compact family of rows formats,InnoDB allocates a variable amount of storage in the range of N to N × the maximum byte length of the character set for these columns by stripping trailing spaces.

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

对于 MyISAM 表，如果没有任何可变长度的列 VARCHAR、TEXT 或 BLOB 列，则使用固定大小的行格式。这样更快但可能会浪费一些空间。

Indexes

表的主索引应该尽可能短。这使得识别每一行变得简单和有效。对于 InnoDB 表，主键列在每个辅助索引条目中都是重复的，所以如果有很多辅助索引，那么一个短的主键可以节省大量空间。

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

如果一个长字符串的前几个字符可以组成唯一的前缀，那么最好只索引这个列的前缀，MySQL 支持在列的最左边创建索引。更短的索引更快，不仅因为它们需要更少的磁盘空间，还因为它们在索引缓存中提供了更多的点击率，从而减少了磁盘搜索。

Joins

在某些情况下，将经常扫描的表分成两部分是有益的。如果它是动态格式表，并且可以使用较小的静态格式表在扫描表时查找相关行，那么这一点尤其重要。

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

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

Normalization

通常尽量使所有数据不冗余数据库理论中的第三范式。与其重复冗长的值如名称和地址，不如为它们分配惟一的 id，根据需要跨多个较小的表重复这些 id，并通过在 join 子句中引用 id 来连接查询中的表。

如果速度比磁盘空间和保存多个数据副本的维护成本更重要，例如在 business intelligence 场景中，你分析来自大型表的所有数据，那么你可以放松标准化规则、复制信息或创建摘要表来获得更快的速度。

8.4.2 Optimizing MySQL Data Types

8.4.2.1 Optimizing for Numeric Data

8.4.2.2 Optimizing for Character and String Types

8.4.2.3 Optimizing for BLOB Types

8.4.2.1 Optimizing for Numeric Data

对于可以表示为字符串或数字的惟一 id 或其他值，首选数值列而不是字符串列。由于大数值可以存储在比对应字符串更少的字节中，因此传输和比较它们更快，占用更少的内存。

如果使用数字数据，在许多情况下从数据库访问信息使用活动连接比访问文本文件更快。数据库中的信息可能以比文本文件更紧凑的格式存储，因此访问它涉及更少的磁盘访问。你还可以在应用程序中保存代码，因为你可以避免解析文本文件以查找行和列边界。

8.4.2.2 Optimizing for Character and String Types

对于字符和字符串列，请遵循以下准则：

如果不需要特定于语言的排序功能，可以使用二进制排序顺序进行快速比较和排序操作。可以使用二进制运算符在特定查询中使用二进制排序。

在比较来自不同列的值时，尽可能使用相同的字符集和排序规则声明这些列，以避免在运行查询时进行字符串转换。

对于小于 8KB 的列值，使用二进制 VARCHAR 而不是 BLOB。GROUP BY 和 ORDER BY 子句可以生成临时表，如果原始表不包含任何 BLOB 列，这些临时表可以使用内存存储引擎。

如果表包含字符串列如名称和地址，但许多查询不检索这些列，请考虑将字符串列拆分为单独的表，并在必要时使用带有外键的连接查询。当 MySQL 从一行中检索任何值时，它将读取包含该行可能还有其他相邻行的所有列的数据块。保持每行很小，只有最常用的列，这样可以在每个数据块中容纳更多的行。这种紧凑的表减少了常见查询的磁盘 I/O 和内存使用。

当你使用一个随机生成的值作为 InnoDB 表中的主键时，如果可能的话，在它前面加上一个升序值，例如当前日期和时间。当连续的主值在物理上相邻存储时，InnoDB 可以更快地插入和检索它们。

数值列通常优于等效字符串列。

8.4.2.3 Optimizing for BLOB Types

当存储包含文本数据的大 BLOB 时，请首先考虑压缩它。当整个表被压缩时，不要使用这种技术。

由于检索和显示 BLOB 值的性能需求可能与其他数据类型非常不同，因此可以将特定于 BLOB 的表放在不同的存储设备上，甚至单独的数据库实例上。例如要检索 BLOB 可能需要一个大的顺序磁盘读取，它更适合传统硬盘驱动器，而不是 SSD 设备。

二进制 VARCHAR 列有时优于等效 BLOB 列。

你可以将列值的散列存储在单独的列中，索引该列并在查询中测试散列值，而不是针对非常长的文本字符串测试是否相等。使用 MD5() 或 CRC32() 函数生成哈希值。由于哈希函数可以为不同的输入产生重复的结果，因此在查询中仍然包含一个子句和 blob_column = long_string_value，以防止错误匹配；性能优势来自较小的、易于扫描的散列值索引。

8.4.3 Optimizing for Many Tables

8.4.3.1 How MySQL Opens and Closes Tables

8.4.3.2 Disadvantages of Creating Many Tables in the Same Database

保持单个查询快速的一些技术涉及到跨多个表分割数据。当表的数量达到数千甚至数百万时，处理所有这些表的开销就成为一个新的性能考虑因素。

8.4.3.1 How MySQL Opens and Closes Tables

当你执行 mysqladmin 状态命令时，应该会看到如下所示：

Uptime： 426 Running threads： 1 Questions： 11082

Reloads： 1 Open tables： 12

如果你的表少于 12 个，那么打开的表值 12 可能有点令人费解。

MySQL 是多线程的，因此可能有许多客户机同时对给定的表发出查询。为了最小化同一个表上具有不同状态的多个客户机会话的问题，每个并发会话都独立地打开该表。这需要额外的内存，但通常会提高性能。对于 MyISAM 表，对于打开表的每个客户机，数据文件需要一个额外的文件描述符。相比之下，索引文件描述符在所有会话之间共享。

table_open_cache 和 max_connections 系统变量影响服务器保持打开的文件的最大数量。如果增加其中一个或两个值，可能会遇到操作系统对每个进程打开的文件描述符数量施加的限制。许多操作系统允许你增加打开文件的限制，尽管方法因系统而异。请参阅你的操作系统文档，以确定是否可能增加此限制以及如何增加。

table_open_cache 与 max_connections 相关。例如，对于 200 个并发运行的连接，指定表缓存大小至少为 200 * N，其中 N 是执行的任何查询中每个连接的表的最大数量。你还必须为临时表和文件保留一些额外的文件描述符。

确保操作系统能够处理 table_open_cache 设置所隐含的打开文件描述符的数量。如果 table_open_cache 设置过高，MySQL 可能会耗尽文件描述符，并出现拒绝连接或无法执行查询等症状。

还要考虑到 MyISAM 存储引擎需要为每个惟一的打开表提供两个文件描述符。要增加 MySQL 可用的文件描述符的数量，请使用 mysqld 的 --open-files-limit 启动选项。

打开表的缓存保持在 table_open_cache 条目的级别。服务器在启动时自动调整缓存大小。要显式设置大小，请在启动时设置 table_open_cache 系统变量。MySQL 可能临时打开更多的表来执行查询，如本节后面所述。

MySQL 关闭一个未使用的表，并在以下情况下从表缓存中删除它：

当缓存已满且线程试图打开不在缓存中的表时。

当缓存包含多个 table_open_cache 条目，且缓存中的表不再被任何线程使用时。

当表刷新操作发生时。当有人发出 FLUSH TABLES 语句或执行 mysqladmin 刷新表或 mysqladmin 刷新命令时，就会发生这种情况。

当表缓存填满时，服务器使用以下过程定位要使用的缓存条目：

释放当前未使用的表，从最近最少使用的表开始。

如果必须打开新表，但缓存已满且无法释放表，则缓存将根据需要临时扩展。当缓存处于临时扩展状态，且表从使用状态变为未使用状态时，将关闭该表并从缓存中释放该表。

为每个并发访问打开一个 MyISAM 表。这意味着如果两个线程访问同一个表，或者如果一个线程在同一个查询中访问该表两次例如，通过将该表连接到它自己，则需要打开该表两次。每个并发打开都需要表缓存中的一个条目。任何 MyISAM 表的第一个打开都需要两个文件描述符：一个用于数据文件，另一个用于索引文件。表的每一次额外使用都只占用数据文件的一个文件描述符。索引文件描述符在所有线程之间共享。

如果使用 HANDLER tbl_name OPEN 语句打开表，则为线程分配专用表对象。此表对象不会被其他线程共享，并且在线程调用 HANDLER tbl_name CLOSE 或线程终止之前不会关闭。发生这种情况时，表将被放回表缓存中如果缓存未满。

要确定表缓存是否太小，请检查 Opened_tables 状态变量，它指示自服务器启动以来表打开操作的数量：

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables'；

+---------------+-------+

| Variable_name | Value

|+---------------+-------+

| Opened_tables | 2741

|+---------------+-------+

如果该值非常大或增长很快，即使你没有发出很多 FLUSH TABLES 语句，也要在服务器启动时增加 table_open_cache 值。

8.4.3.2 Disadvantages of Creating Many Tables in the Same Database

如果在同一个数据库目录中有许多 MyISAM 表，则打开、关闭和创建操作都很慢。如果在许多不同的表上执行 SELECT 语句，当表缓存满时就会有一些开销，因为对于必须打开的每个表，必须关闭另一个表。可以通过增加表缓存中允许的条目数量来减少这种开销。

8.4.4 Internal Temporary Table Use in MySQL

在某些情况下，服务器在处理语句时创建内部临时表。用户无法直接控制这种情况何时发生。

服务器在以下条件下创建临时表：

对 UNION 语句的求值。对某些视图的求值，如使用 TEMPTABLE 算法、UNION 或聚合的视图。派生表的求值。公共表表达式的求值。为子查询或半连接物化创建的表。

对语句的求值，这些语句包含 ORDER BY 子句和不同的 GROUP BY 子句，或者 ORDER BY 或 GROUP BY 包含来自联接队列中第一个表以外的表的列。

不同组合订单的评估可能需要一个临时表。

对于使用 SQL_SMALL_RESULT 修饰符的查询，MySQL 使用内存中的临时表，除非查询还包含需要磁盘存储的元素稍后将进行描述。

INSERT … SELECT 语句从同一个表中进行选择和插入，MySQL 创建一个内部临时表来保存 SELECT 中的行，然后将这些行插入到目标表中。

多表更新语句的评估。

GROUP_CONCAT() 或 COUNT(DISTINCT) 表达式的求值。

窗口函数的计算，根据需要使用临时表。

要确定语句是否需要临时表，请使用 EXPLAIN 并检查额外的列，看看它是否说使用临时表。EXPLAIN 不一定会说对派生的或物化的临时表使用临时。对于使用窗口函数的语句，EXPLAIN with FORMAT=JSON 总是提供关于窗口步骤的信息。如果窗口函数使用临时表，则为每个步骤指定临时表。

当服务器创建一个内部临时表在内存或磁盘上时，它将增加 Created_tmp_tables 状态变量。如果服务器在磁盘上创建表最初或通过转换内存中的表，它将增加 Created_tmp_disk_tables 状态变量。

一些查询条件阻止使用内存中的临时表，在这种情况下，服务器使用磁盘上的表：

表中存在一个 BLOB 或文本列。但是 TempTable 存储引擎是 MySQL 8.0 中内存内部临时表的默认存储引擎。

如果使用 UNION 或 UNION ALL 则选择列表中任何长度大于 512 的字符串列二进制字符串为字节，非二进制字符串为字符。

SHOW 列和 DESCRIBE 语句使用 BLOB 作为某些列的类型，因此用于结果的临时表是磁盘上的表。

对于符合某些条件的 UNION 语句，服务器不使用临时表。相反它从临时表创建中只保留执行结果列类型转换所需的数据结构。表没有完全实例化，也没有向其写入或从中读取行；行被直接发送到客户机。结果减少了内存和磁盘需求，并且在第一行发送到客户机之前延迟更小，因为服务器不需要等到最后一个查询块被执行。EXPLAIN 和 optimizer 跟踪输出反映了这种执行策略 UNION RESULT 查询块不存在，因为该块对应于从临时表读取的部分。

这些条件使工会有资格在没有临时表格的情况下进行评估

The union is UNION ALL, not UNION or UNION DISTINCT.

There is no global ORDER BY clause.

The union is not the top-level query block of an ... SELECT ...statement.

Internal Temporary Table Storage Engine

internal_tmp_mem_storage_engine 会话变量定义内存内部临时表的存储引擎。允许的值是 TempTable 默认值和内存。

TempTable 存储引擎为 VARCHAR 和 VARBINARY 列提供了高效的存储。MySQL 8.0.13 支持其他二进制大对象类型的存储。temptable_max_ram 配置选项定义了在开始以映射到内存中的临时文件的形式从磁盘分配空间之前，TempTable 存储引擎可以占用的最大随机访问内存 RAM。默认的 temptable_max_ram 设置是 1GiB。将 TempTable 存储引擎的临时文件用作内存中临时表的溢出机制由。

临时文件是在 tmpdir 变量定义的目录中创建的。

临时文件在创建并打开后立即被删除，因此在 tmpdir 目录中不可见。临时文件占用的空间由操作系统在打开临时文件时保存。当临时文件被 TempTable 存储引擎关闭或 mysqld 进程关闭时，该空间将被回收。

数据永远不会在 RAM 和临时文件之间、RAM 内部或临时文件之间移动。

如果空间在 temptable_max_ram 定义的限制内可用，则新数据存储在 RAM 中。否则新数据将存储在临时文件中。

如果在将表的一些数据写到临时文件之后，RAM 中的空间变得可用，那么剩余的表数据就有可能存储在 RAM 中。

memory/temptable/physical_ram 和 memory/temptable/physical_disk 性能模式工具可以用于监视来自内存和磁盘的 temptable 空间分配。memory/temptable/physical_ram 报告分配 RAM 的数量。memory/temptable/physical_disk 报告从磁盘分配的空间量。如果物理磁盘工具报告的值不是 0，则在某个点上达到了 temptable_max_ram 阈值。可以在诸如 memory_summary-global_by_event_name 之类的性能模式内存总结表中查询数据。

当对内存中的临时表使用内存存储引擎时，MySQL 会自动将内存中的临时表转换为磁盘上的表，如果表太大的话。内存中临时表的最大大小由 tmp_table_size 或 max_heap_table_size 值定义，以较小的值为准。这与使用 CREATE 表显式创建内存表不同。对于这样的表，只有 max_heap_table_size 变量决定表可以增长多大，并且不需要转换到磁盘上的格式。

Storage Engine for On-Disk Internal Temporary Tables

internal_tmp_disk_storage_engine 变量定义了服务器用来管理磁盘上的内部临时表的存储引擎。允许的值是 INNODB 默认值和 MYISAM。

对于公共表表达式 cte，用于磁盘上内部临时表的存储引擎不能是 MyISAM。如果 internal_tmp_disk_storage_engine=MYISAM，任何使用磁盘上临时表实现 CTE 的尝试都会出现错误。

当使用 internal_tmp_disk_storage_engine=INNODB 时，在磁盘上生成超过 INNODB 行或列的内部临时表的查询会限制返回行大小过大或列数量过多的错误。解决方案是将 internal_tmp_disk_storage_engine 设置为 MYISAM。

Internal Temporary Table Storage Format

当内存中的内部临时表由 TempTable 存储引擎管理时,包含 VARCHAR 列、VARBINARY 列或其他二进制大对象类型列 MySQL 8.0.13 支持的行在内存中由单元格数组表示，每个单元格包含一个 NULL 标志、数据长度和一个数据指针。列值在数组之后按连续顺序排列，位于内存的单个区域中，没有填充。数组中的每个单元格使用 16 字节的存储空间。当 TempTable 存储引擎超过 temptable_max_ram 限制时，开始以映射到内存中的临时文件的形式从磁盘分配空间。

当内存中的内部临时表由内存存储引擎管理时，将使用固定长度的行格式。VARCHAR 和 VARBINARY 列值被填充到最大列长度，实际上是将它们存储为 CHAR 和二进制列。

磁盘上的内部临时表由 InnoDB 或 MyISAM 存储引擎管理取决于 internal_tmp_disk_storage_engine 设置。这两个引擎都使用动态宽度行格式存储内部临时表。与使用固定长度行的磁盘表相比，列只占用所需的存储空间，从而减少了磁盘 I/O、空间需求和处理时间。

在使用内存存储引擎时，语句最初可以创建内存中的内部临时表，然后在表变得过大时将其转换为磁盘上的表。在这种情况下，可以跳过转换并首先在磁盘上创建内部临时表，从而获得更好的性能。big_tables 变量可用于强制内部临时表的磁盘存储。