Open_tables Opened_tables table_open_cache

最新推荐文章于 2023-10-12 13:35:49 发布

kf_panda

最新推荐文章于 2023-10-12 13:35:49 发布

阅读量1.6k

点赞数

分类专栏： MYSQL索引&SQL优化&服务器优化

MYSQL索引&SQL优化&服务器优化专栏收录该内容

26 篇文章

订阅专栏

本文介绍如何通过调整MySQL的table_open_cache参数来提高数据库连接速度。当发现Opened_tables数值较大且持续增长时，可能是因为table_open_cache设置过小，导致频繁打开表文件，从而影响性能。通过增大table_open_cache值可以有效解决此问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天开发说应用链接数据库比较慢，定位后发现是table_open_cache太小了引起的问题。

如果你发现Opened_tables比较大并且一直再增大，很可能是table_open_cache设置太小了，导致每次链接数据库都要去打开表，这也会造成链接数据库比较慢。

Open_tables : The number of tables that are open.

Opened_tables : The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

table_open_cache :The number of open tables for all threads. Increasing this value increases the number of file descriptors 
that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. 
If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and 
reopened), then you should increase the value of the table_open_cache variable. For more information about the table cache,

测试：

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 4     |
+------------------+-------+
1 row in set (0.00 sec)

mysql>  show global status like 'open%tables%'; 
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Open_tables   | 4        |
| Opened_tables | 11412131 |
+---------------+----------+
2 rows in set (0.00 sec)
mysql> exit



[root@slave159 bin]# mysql -uroot -h192.168.60.204 -p1234 pva1
-- 说明：3s左右才登陆
mysql>  show global status like 'open%tables%'; 
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Open_tables   | 4        |
| Opened_tables | 11413673 |
+---------------+----------+
2 rows in set (0.01 sec)

mysql> select 11413673-11412131;
+-------------------+
| 11413673-11412131 |
+-------------------+
|              1542 |
+-------------------+
1 row in set (0.00 sec)


mysql> set global table_open_cache=2000;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

[root@slave159 bin]# mysql -uroot -h192.168.60.204 -p1234 pva1
-- 说明：3s左右才登陆
mysql> show global status like 'open%tables%'; 
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Open_tables   | 574      |
| Opened_tables | 11433986 |
+---------------+----------+
mysql> exit
Bye

[root@slave159 bin]# mysql -uroot -h192.168.60.204 -p1234 pva1
-- 说明：1s内就登陆了
mysql> show global status like 'open%tables%'; 
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Open_tables   | 574      |
| Opened_tables | 11433986 |
+---------------+----------+