【MySQL】关于table cache的相关参数 【转】
本文来自：http://chenxu.yo2.cn/articles/mysql_table_cache.html
一、状态值和变量：
1.1
在MySQL的show status中有2个状态值：Open_tables和Opened_tables。这2个值代表的意思如下：
Open_tables ：代表当前打开表的数量。
Opened_tables：代表自从MySQL启动后，打开表的数量。
关于MySQL怎么打开关闭表的具体细节参考文档：<How MySQL Opens and Closes Tables>。
（1）对于myisam存储引擎，打开1张表需要2个文件描述符（一个.MYD文件，一个.MYI文件）。
（2）对于innodb存储引擎，开启表的独立表空间（innodb_file_per_table）打开1张表只需要1个文件描述符（一个.ibd文件）。
【MySQL Variable】
对于上面的状态值，对应的5.1.3版本后的MySQL变量参数为table_open_cache，而早期版本为：table_cache，该参数值的代表MySQL可以缓存的打开表时候的最大文件描述符。
1.2
在MySQL 5.1.3之后，还添加了2个状态值：Open_table_definitions和Opened_table_definitions。这2个值代表的意思如下：
Open_table_definitions ：代表当前缓存了多少.frm文件。
Opened_table_definitions：代表自从MySQL启动后，缓存了.frm文件的数量。
需要注意的是.frm文件是MySQL用于存放表结构的文件，对应myisam和innodb存储引擎都必须有的。
【MySQL Variable】
对于上面的状态值，对应的5.1.3版本后的MySQL变量参数为table_definition_cache，该参数值的代表MySQL可以缓存的表定义的数量。和前面的table cache不同的是，表定义的缓存占用空间很小，而且不需要使用文件描述符，也就是只要打开.frm文件，缓存表定义，然后就可以关闭.frm文件。
1.3
另外，还有2个状态值：Open_files和Opened_files。这2个值的意思同上类似：
Open_files ：代表当前打开的文件。对应存储引擎（如：innodb）使用存储引擎自己内部函数打开的话，这个值是不会增加的。
Opened_files：代表使用MySQL的my_open()函数打开过的文件数。如果不是使用这个函数打开文件的话，这个值是不会增加的。
【MySQL Variable】
对于上面的状态值，对应的MySQL变量参数为open_files_limit。
二、测试
测试的MySQL版本：
mysql> select version();
+------------+
| version() |
+------------+
| 5.1.47-log |
+------------+
1 row in set (0.00 sec)
2.1 myisam存储引擎
2.1.1
重启MySQL服务器，可以看到启动时候MySQL已经自动打开相关的系统表。
mysql> show global status like 'Open%_table_definitions';show global status like 'Open%_tables';show global status like 'Open%_files';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_table_definitions | 15 |
| Opened_table_definitions | 15 |
+--------------------------+-------+
2 rows in set (0.00 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 8 |
| Opened_tables | 15 |
+---------------+-------+
2 rows in set (0.00 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 19 |
| Opened_files | 62 |
+---------------+-------+
2 rows in set (0.00 sec)
在上面的状态输出，可以看到Open_tables的值为8，代表了当前打开了8张表，同样的通过mysqladmin status命令也可以看到：
[mysql@xentest8-vm1 test]$ mysqladmin status
Uptime: 631 Threads: 2 Questions: 14 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.22
可以通过SHOW OPEN TABLES命令查看，当前的table cache到底打开了哪些表：
mysql> show open tables;
+----------+--------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+--------------+--------+-------------+
| mysql | servers | 0 | 0 |
| mysql | db | 0 | 0 |
| mysql | host | 0 | 0 |
| mysql | columns_priv | 0 | 0 |
| mysql | user | 0 | 0 |
| mysql | procs_priv | 0 | 0 |
| mysql | event | 0 | 0 |
| mysql | tables_priv | 0 | 0 |
+----------+--------------+--------+-------------+
8 rows in set (0.00 sec)
可以看到打开了mysql库里面的8张系统表（都是myisam存储引擎），另外，还可以通过lsof（lsof的用法参见<使用lsof查找打开的文件>）查看MySQL打开的对应文件：
[zhuxu@xentest8-vm1 ~]$ sudo lsof -u mysql | grep /home/mysql/mysql-5.1.47/data/mysql/
mysqld 14683 mysql 16u REG 253,1 2048 87889757 /home/mysql/mysql-5.1.47/data/mysql/host.MYI
mysqld 14683 mysql 17u REG 253,1 0 87889758 /home/mysql/mysql-5.1.47/data/mysql/host.MYD
mysqld 14683 mysql 18u REG 253,1 2048 87889760 /home/mysql/mysql-5.1.47/data/mysql/user.MYI
mysqld 14683 mysql 19u REG 253,1 380 87889761 /home/mysql/mysql-5.1.47/data/mysql/user.MYD
mysqld 14683 mysql 20u REG 253,1 5120 87889754 /home/mysql/mysql-5.1.47/data/mysql/db.MYI
mysqld 14683 mysql 21u REG 253,1 880 87889755 /home/mysql/mysql-5.1.47/data/mysql/db.MYD
mysqld 14683 mysql 22u REG 253,1 4096 87889772 /home/mysql/mysql-5.1.47/data/mysql/tables_priv.MYI
mysqld 14683 mysql 23u REG 253,1 0 87889773 /home/mysql/mysql-5.1.47/data/mysql/tables_priv.MYD
mysqld 14683 mysql 24u REG 253,1 4096 87889775 /home/mysql/mysql-5.1.47/data/mysql/columns_priv.MYI
mysqld 14683 mysql 25u REG 253,1 0 87889776 /home/mysql/mysql-5.1.47/data/mysql/columns_priv.MYD
mysqld 14683 mysql 26u REG 253,1 4096 87889808 /home/mysql/mysql-5.1.47/data/mysql/procs_priv.MYI
mysqld 14683 mysql 27u REG 253,1 0 87889809 /home/mysql/mysql-5.1.47/data/mysql/procs_priv.MYD
mysqld 14683 mysql 28u REG 253,1 1024 87889769 /home/mysql/mysql-5.1.47/data/mysql/servers.MYI
mysqld 14683 mysql 29u REG 253,1 0 87889770 /home/mysql/mysql-5.1.47/data/mysql/servers.MYD
mysqld 14683 mysql 30u REG 253,1 2048 87889817 /home/mysql/mysql-5.1.47/data/mysql/event.MYI
mysqld 14683 mysql 31u REG 253,1 0 87889818 /home/mysql/mysql-5.1.47/data/mysql/event.MYD
2.1.2
接着打开一张myisam存储引擎的表：
mysql> select count(*) from myisam_1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> show global status like 'Open%_table_definitions';show global status like 'Open%_tables';show global status like 'Open%_files';
# 可以看到Opened_table_definitions/Opened_table_definitions/Open_tables/Opened_tables的值都加1
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_table_definitions | 16 | +1
| Opened_table_definitions | 16 | +1
+--------------------------+-------+
2 rows in set (0.00 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 9 | +1
| Opened_tables | 16 | +1
+---------------+-------+
2 rows in set (0.00 sec)
# 可以看到Open_files的值（19+2）加2，Opened_files的值（62+3）加3
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 21 | +2(MYI，MYD)
| Opened_files | 65 | +3(MYI，MYD，FRM)
+---------------+-------+
2 rows in set (0.00 sec)
mysql> show create table myisam_1\G
*************************** 1. row ***************************
Table: myisam_1
Create Table: CREATE TABLE `myisam_1` (
`a` int(11) DEFAULT NULL,
KEY `idx_myisam_1` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show open tables from test;
+----------+----------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+----------+--------+-------------+
| test | myisam_1 | 0 | 0 |
+----------+----------+--------+-------------+
1 row in set (0.00 sec)
[zhuxu@xentest8-vm1 ~]$ sudo lsof -u mysql | grep /home/mysql/mysql-5.1.47/data/test/
mysqld 14683 mysql 33u REG 253,1 1024 87851510 /home/mysql/mysql-5.1.47/data/test/myisam_1.MYI
mysqld 14683 mysql 34u REG 253,1 0 87851511 /home/mysql/mysql-5.1.47/data/test/myisam_1.MYD
# 可见，除了需要打开.frm文件（打开，缓存后，就关闭，所以Open_files没有+3，只有+2），对于myisam存储引擎，打开1张表需要2个文件描述符（一个.MYD文件，一个.MYI文件）
2.1.3
对应table cache中的缓存的表，可以通过flush tables命令来把它人工移出table cache:
mysql> flush tables myisam_1;
Query OK, 0 rows affected (0.00 sec)
mysql> show open tables from test;
Empty set (0.00 sec)
mysql> show global status like 'Open%_table_definitions';show global status like 'Open%_tables';show global status like 'Open%_files';
# 可以看到Open_table_definitions和Open_tables的值都减1
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_table_definitions | 15 | -1
| Opened_table_definitions | 16 |
+--------------------------+-------+
2 rows in set (0.00 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 8 | -1
| Opened_tables | 16 |
+---------------+-------+
2 rows in set (0.00 sec)
# 可以看到Open_files的值（21-2）减2
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 19 | -2 (MYI，MYD)
| Opened_files | 65 |
+---------------+-------+
2 rows in set (0.00 sec)
[zhuxu@xentest8-vm1 ~]$ sudo lsof -u mysql | grep /home/mysql/mysql-5.1.47/data/test/ | wc -l
0
2.2 innodb存储引擎
2.2.1
打开一张innodb存储引擎的表：
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> select count(*) from t0001;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.01 sec)
mysql> show global status like 'Open%_table_definitions';show global status like 'Open%_tables';show global status like 'Open%_files';
# 可以看到Opened_table_definitions/Opened_table_definitions/Open_tables/Opened_tables的值都加1
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_table_definitions | 16 | +1
| Opened_table_definitions | 17 | +1
+--------------------------+-------+
2 rows in set (0.00 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 9 | +1
| Opened_tables | 17 | +1
+---------------+-------+
2 rows in set (0.00 sec)
# 可以看到Open_files的值不变（即innodb使用存储引擎自己内部函数打开的话，这个值是不会增加）
# Opened_files的值（65+1）加1（需要打开.frm文件，缓存后，就关闭）
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 19 | +0
| Opened_files | 66 | +1
+---------------+-------+
2 rows in set (0.00 sec)
mysql> show open tables from test;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test | t0001 | 0 | 0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)
mysql> show create table t0001\G
*************************** 1. row ***************************
Table: t0001
Create Table: CREATE TABLE `t0001` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[zhuxu@xentest8-vm1 ~]$ sudo lsof -u mysql | grep /home/mysql/mysql-5.1.47/data/test/
mysqld 14683 mysql 33uW REG 253,1 98304 87851074 /home/mysql/mysql-5.1.47/data/test/t0001.ibd
2.2.2
对于innodb存储引擎，table cache对其影响其实不是很大，innodb有自己的数据字典，可以缓存相关表信息。
PS：
看完上面的知识点之后，也可以继续看 http://www.cnblogs.com/zuoxingyu/archive/2012/10/17/2727843.html 这篇关于物理删除文件（MYISAM）的恢复方法。
