Open_tables Opened_tables table_open_cache

今天开发说应用链接数据库比较慢，定位后发现是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 |
+---------------+----------+