MySQL Variables table_definition_cache 数据库 参数变量解释及正确配置使用

本站中文解释

table_definition_cache参数用于控制MySQL存储表定义（比如列数据类型）的缓存大小，每个表定义的默认缓存数大小为400。

如果访问的表定义已经保存在缓存中，就更加高效，对数据库查询处理效率会有所提升。

table_definition_cache的可设置范围在1-2048，设置方式如下：

1.使用MySQL命令行：

set global table_definition_cache=1024;

2.使用MySQL配置文件：

# 将table_definition_cache设置为1024
table_definition_cache=1024

官方英文解释

table_definition_cache

The number of table definitions (from
.frm files) that can be stored in the
table definition cache. If you use a large number of tables,
you can create a large table definition cache to speed up
opening of tables. The table definition cache takes less space
and does not use file descriptors, unlike the normal table
cache. The minimum value is 400. The default value is based on
the following formula, capped to a limit of 2000:

400 + (table_open_cache / 2)

For InnoDB, the
table_definition_cache
setting acts as a soft limit for the number of table instances
in the InnoDB data dictionary cache and the
number file-per-table tablespaces that can be open at one
time.

If the number of table instances in the
InnoDB data dictionary cache exceeds the
table_definition_cache limit,
an LRU mechanism begins marking table instances for eviction
and eventually removes them from the InnoDB data dictionary
cache. The number of open tables with cached metadata can be
higher than the
table_definition_cache limit
due to table instances with foreign key relationships, which
are not placed on the LRU list.

The number of file-per-table tablespaces that can be open at
one time is limited by both the
table_definition_cache and
innodb_open_files settings.
If both variables are set, the highest setting is used. If
neither variable is set, the
table_definition_cache
setting, which has a higher default value, is used. If the
number of open tablespaces exceeds the limit defined by
table_definition_cache or
innodb_open_files, an LRU
mechanism searches the LRU list for tablespace files that are
fully flushed and not currently being extended. This process
is performed each time a new tablespace is opened. Only
inactive tablespaces are closed.