MySQL 表缓存性能设置

• table_open_cache = 4096
  • 控制全局打开表数(注意是表的数据文件，且使用了文件描述符)，通常是有多少个表，把表数量乘以两三倍就可以了(每一个 sql 执行线程至少需要打开一个缓存表，这个参数就是控制所有 SQL 执行线程可打开缓存表的数量，应该与最大连接数max_connections 以及每个连接执行关联查询中所涉及表的最大个数（用 N 来表示）来设定：即,max_connections*N 作为该参数的值)。table_definition_cache 变量和 table_open_cache 设置一样大（如果在 show processlist 里经常发现 opening table 那么可能就是这两个变量设置小了（要注意，调整这两个变量的时候，也要注意系统 open-files-limit 的设置和innodb_open_files、open_files_limit 变量），另外，状态变量Table_open_cache_hits 和 Table_open_cache_misses 也可以观察到 ，这两个状态变量是 5.6.x 开始才有）。另外还有 4 个变量（5.6.x之前的版本可以使用下面 4 个变量查看）：o 默认值为 2000，整型值。全局变量，动态变量，取值范围为1~524288。

    show status like 'Open%_table%';

    Open_table_definitions | 512|

    Open_tables | 512 |

    Opened_table_definitions | 51 |

    Opened_tables | 51 |

  • # Open_tables => opening #当前在 table_open_cache 中缓存着的表数量，当这个值接近table_open_cache 时，就表示缓存数量已经被用完了，再有表打开时 opend_tables 变量就会增加# Opened_tables => opened_total #实例启动以来，每次 SQL 查询都需要重新打开表，执行完后再把这个表关掉，而无法使用到 table_open_cache 的表数量# 缓存机制当 MySQL 访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存；如果还没有被缓存，但是在 MySQL 表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区；如果表缓存满了，则会按照一定的规则将当前未用的表释放，或者临时扩大表缓存来存放，使用表缓存的好处是可以更快速地访问表中的内容。

    # 参数调优

    一般来说，可以通过查看数据库运行峰值时间的状态值 Open_tables 和 Opened_tables ，判断是否需要增加 table_cache 的值（其中 open_tables 是当前打开的表的数量，Opened_tables 则是已经打开的表的数量）。即如果 open_tables 接近 table_cache 的时候，并且 Opened_tables 这个值在逐步增加，那就要考虑增加这个值的大小了。还有就是Table_locks_waited 比较高的时候，也需要增加 table_cache。

    # 比较适合的值：

    Open_tables / Opened_tables >= 0.85

    Open_tables / table_cache <= 0.95

    # 在 mysql 默认安装情况下，table_cache 的值在 2G 内存以下的机器中的值默认时 256 到512，如果机器有 4G 内存,则默认这个值是 2048，但这决意味着机器内存越大，这个值应该越大，因为 table_cache 加大后，使得 mysql 对 SQL 响应的速度更快了，不可避免的会产生更多的死锁（dead lock），这样反而使得数据库整个一套操作慢了下来，严重影响性能。所以平时维护中还是要根据库的实际情况去作出判断，找到最适合你维护的库的 table_cache 值。

    # 清空缓存

    mysql >flush tables;

• table_definition_cache = 4096
  • 与 table_open_cache 作用类似，但是是缓存表定义文件.frm 的数量。5.6.8 之前的默认值是 400，之后版本默认是自动计算的，计算公式（400 + (table_open_cache / 2)），表定义缓存比table_open_cache 使用更少的空间，且不使用文件描述符。o 对于 InnoDB 来说，table_definition_cache 可以对 InnoDB 数据字典缓存中打开的表实例数量进行软限制。即，如果打开的表实例的数量超过 table_definition_cache 设置，则 LRU 机制标记表实例并最终从数据字典缓存中删除。该限制有助于缓解大量内存被极少使用的表实例占用的情况，注意：具有缓存元数据的表实例数可能高于 table_definition_cache 定义的限制，因为带有外键关系的 InnoDB 系统表实例和具有父子关系的表实例不会放在 LRU 列表上，无法通过从内存中逐出这些表实例来减少table_definition_cache 的数量。o 此外，table_definition_cache 对单个 InnoDB 文件一次可以打开表的数量也具有软限制，且 innodb_open_files 也同时控制这个数量。如果同时设置了 table_definition_cache 和innodb_open_files，则使用两者最高值设置。如果两者都没有显式设置，则使用具有较高缺省值的 table_definition_cache。如果打开的表空间文件句柄数超过 table_definition_cache 或innodb_open_files 定义的限制，则 LRU 机制在表空间文件 LRU列表中搜索已完全刷新并且当前长时间未使用的文件。每次打开新的表空间时都会执行此过程。如果存在“非活动”表空间就会执行关闭表空间文件。o 默认值为-1，表示自动计算，整型值。全局变量，动态变量，固定值取值范围为：400~524288。
• table_open_cache_instances = 32
  • table_open_cache 实例的数量，5.6.6 版本新增，默认情况下，table_open_cache_instances 的值为 1，表示只有一个table_open_cache 实例，此时，DML、DDL 语句访问表时，需要把整个 table_open_cache 实例锁住，可以通过table_open_cache_instances 设置大于 1 的值来将 table_open_cache划分为多个实例。这样 DML 访问就不需要把所有的 table_open_cache实例锁住，在一些场景下可以提高性能。但 DDL 语句仍然会把所有的table_open_cache 实例锁住。o 默认值为 1（在 MySQL 5.7.8 及其之后的版本中默认值为16），整型值，全局变量，只读变量，取值范围为 1~64