Documentation Home
MySQL 8.0 参考手册  / 第8章优化  / 8.4 优化数据库结构  / 8.4.3 优化多表  /  8.4.3.1 How MySQL Opens and Closes Tables

8.4.3.1 How MySQL Opens and Closes Tables

When you execute a mysqladmin status command, you should see something like this:

Uptime: 426 Running threads: 1 Questions: 11082
Reloads: 1 Open tables: 12

The Open tables value of 12 can be somewhat puzzling if you have fewer than 12 tables.

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

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

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

确保您的操作系统可以处理设置所暗示的打开文件描述符的数量 table_open_cache。如果 table_open_cache设置得太高,MySQL 可能会用完文件描述符并出现拒绝连接或无法执行查询等症状。

还要考虑到MyISAM 存储引擎需要为每个唯一的打开表提供两个文件描述符。对于分区MyISAM表,打开表的每个分区都需要两个文件描述符。(当MyISAM打开一个分区表时,它会打开这个表的每个分区,不管给定的分区是否被实际使用。请参阅 MyISAM 和分区文件描述符的使用。)要增加 MySQL 可用的文件描述符的数量,请设置open_files_limit 系统变量。请参阅 第 B.3.2.16 节,“未找到文件和类似错误”

The cache of open tables is kept at a level of table_open_cache entries. The server autosizes the cache size at startup. To set the size explicitly, set the table_open_cache system variable at startup. MySQL may temporarily open more tables than this to execute queries, as described later in this section.

MySQL closes an unused table and removes it from the table cache under the following circumstances:

  • When the cache is full and a thread tries to open a table that is not in the cache.

  • When the cache contains more than table_open_cache entries and a table in the cache is no longer being used by any threads.

  • When a table-flushing operation occurs. This happens when someone issues a FLUSH TABLES statement or executes a mysqladmin flush-tables or mysqladmin refresh command.

When the table cache fills up, the server uses the following procedure to locate a cache entry to use:

  • Tables not currently in use are released, beginning with the table least recently used.

  • If a new table must be opened, but the cache is full and no tables can be released, the cache is temporarily extended as necessary. When the cache is in a temporarily extended state and a table goes from a used to unused state, the table is closed and released from the cache.

A MyISAM table is opened for each concurrent access. This means the table needs to be opened twice if two threads access the same table or if a thread accesses the table twice in the same query (for example, by joining the table to itself). Each concurrent open requires an entry in the table cache. The first open of any MyISAM table takes two file descriptors: one for the data file and one for the index file. Each additional use of the table takes only one file descriptor for the data file. The index file descriptor is shared among all threads.

If you are opening a table with the HANDLER tbl_name OPEN statement, a dedicated table object is allocated for the thread. This table object is not shared by other threads and is not closed until the thread calls HANDLER tbl_name CLOSE or the thread terminates. When this happens, the table is put back in the table cache (if the cache is not full). See Section 13.2.4, “HANDLER Statement”.

To determine whether your table cache is too small, check the Opened_tables status variable, which indicates the number of table-opening operations since the server started:

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

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