Skip to main content

4.针对多个表进行优化

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

MySQL 如何打开和关闭表​

当执行 mysqladmin status 命令时，应该看到如下内容：

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 可用的文件描述符的数量，请设置 open_files_limit 系统变量。

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

在以下情况下，MySQL 会关闭未使用的表并将其从表缓存中删除：

  • 当缓存已满并且线程尝试打开不在缓存中的表时。
  • 当缓存包含多于 table_open_cache 条目并且缓存中的表不再被任何线程使用时。
  • 当发生表刷新操作时。 当有人发出 FLUSH TABLES 语句或执行 mysqladmin flash-tables 或 mysqladmin refresh 命令时，就会发生这种情况。

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

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

每次并发访问都会打开一个 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 值。

在同一数据库中创建多个表的缺点​

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