MySQL open_tables和opened_tables

143次阅读

共计 2052 个字符，预计需要花费 6 分钟才能阅读完成。

一、本文涉及到的系统参数有 3 个：

table_open_cache

这个参数表示针对所有 threads 的 table cache 总和，5.6.7 之前默认是 400,5.6.8 之后是 2000。

这是个 server 层的参数，MySQL 不支持并行查询，mysql 的会话也没有 PGA 的概念，一个 thread 引用 myisam 表时需要在 server 层上创建一个 table 对象（索引也需要创建一个但是是共享的，self join 会创建 2 个, 分区表每个分区按单表对待），如果同时多个会话引用一个表也会创建多个表对象，虽然会加大内存使用量，但是却极大的减少了内部表锁的争用。

这个值的数目建议设置为 max_connections* 你的表数目，当然你可能也需要为一些临时表等对象预留，但是这个数目已经足够大啦。

那么 mysql 什么时候释放这些表对象呢？

当缓冲已满，而连接想要打开一个不在缓冲中的表时。
当缓冲数目已经超过了 table_open_cache 设置的值，mysql 开始使用 LRU 算法释放表对象。
当你用 flush tables; 语句时。

open_files_limit

这个参数表示 mysqld 可用的最大文件描述符数目，如果你遇到“Too many open files”的错误，应当考虑加大它。这个参数的默认值是 0 表示无限制（大于 5.6.7 后默认值不再为 0，参考官网），但其实他的值是与操作系统相关的，在 Unix 系统下这个值的数目不能大于 ulimit -n。

这个参数应当大于等于 table_open_cache。

innodb_open_files

这个参数只对 InnoDB 存储引擎有效，它指定了 mysql 可以同时打开的最大.ibd 文件的数目。这个参数即不影响 table_open_cache 也不受 open_files_limit 影响，是独立的只对 InnoDB 有效的。所以在默认为 InnoDB 存储引擎时可以不考虑 open_files_limit 只去设 innodb_open_files。

这 3 个参数的关系可以总结如下, 为保证性能，你应当设置为如下值：

max_connections* 你的表数目 = table_open_cache <=open_files_limit< ulimit -n

innodb_open_files<ulimit -n

二、本文涉及到的 status 参数有 2 个：open_tables 和 opened_tables

其中 open_tables 表示当前打开的 table 总和，即所有 connection 打开的 table 总数。

opened_tables 表示打开过的表的数量总和，只有 show global status 才能看到它的值。这是个计数器，Opened_tables/Uptime 的值过大说明 table_open_cache 过小，导致一些 table 对象（即下文说的 table 对象）经常会刷出 server 层，需要的时候再创建，最终导致此计数过大。

三、相关原理图

参考：http://www.cnblogs.com/xpchild/p/3780625.html 的源码解析。

如下图，有个表叫 xpchild(小屁孩 666)库中的 pp 表：

table: MySQL 会为一个 join 查询中涉及的每个表建一个 TABLE 对象，引用 innodb 表时还会创建 innodb 层的 handler，server 层的 table 指向此 handler，此 handler 指向 innodb 层的共享表字典 Dict_table_t。

table_share: MySQL 为每一张表建立一个 table_share 对象，与 frm 文件对应。

handler: 在 Innodb 层对应于每个 TABLE 对象，innodb 引擎创建一个 handler(windows 叫句柄，linux 叫 file descriptor)。

dict_table_t: innodb 为每一个 innodb 表 load 一个数据字典对象，这些对象的集合就是 innodb 中的 data dictionary。

flush tables：close 了 server 层创建的所有的 table_share 和 table，当然 innodb 层中指向 table 对象的 handler 也就无用了。

官网解释参见：https://dev.mysql.com/doc/refman/5.7/en/table-cache.html

其他可供参考的文章有：

关于表限制参数的使用：https://dba.stackexchange.com/questions/5232/mysql-table-cache-and-opened-tables

关于 SHOW OPEN TABLES 命令的使用：https://www.percona.com/blog/2008/12/14/show-open-tables-what-is-in-your-table-cache/

本文永久更新链接地址：https://www.linuxidc.com/Linux/2018-05/152410.htm

正文完

星哥说事-微信公众号