{"id":147,"date":"2011-11-30T16:43:38","date_gmt":"2011-11-30T16:43:38","guid":{"rendered":"https:\/\/shan.info\/new\/%e3%80%90mysql%e3%80%91%e5%85%b3%e4%ba%8etable-cache%e7%9a%84%e7%9b%b8%e5%85%b3%e5%8f%82%e6%95%b0\/"},"modified":"2011-11-30T16:43:38","modified_gmt":"2011-11-30T16:43:38","slug":"%e3%80%90mysql%e3%80%91%e5%85%b3%e4%ba%8etable-cache%e7%9a%84%e7%9b%b8%e5%85%b3%e5%8f%82%e6%95%b0","status":"publish","type":"post","link":"https:\/\/shan.info\/new\/%e3%80%90mysql%e3%80%91%e5%85%b3%e4%ba%8etable-cache%e7%9a%84%e7%9b%b8%e5%85%b3%e5%8f%82%e6%95%b0\/","title":{"rendered":"\u3010MySQL\u3011\u5173\u4e8etable cache\u7684\u76f8\u5173\u53c2\u6570"},"content":{"rendered":"<p><strong>\u4e00\u3001\u72b6\u6001\u503c\u548c\u53d8\u91cf\uff1a<\/strong><\/p>\n<p><strong>1.1<\/strong><\/p>\n<p>\u5728MySQL\u7684show status\u4e2d\u67092\u4e2a\u72b6\u6001\u503c\uff1a<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-status-variables.html#statvar_Open_tables\">Open_tables<\/a>\u548c<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-status-variables.html#statvar_Opened_tables\">Opened_tables<\/a>\u3002\u8fd92\u4e2a\u503c\u4ee3\u8868\u7684\u610f\u601d\u5982\u4e0b\uff1a<\/p>\n<p>Open_tables\u00a0 \uff1a\u4ee3\u8868\u5f53\u524d\u6253\u5f00\u8868\u7684\u6570\u91cf\u3002<\/p>\n<p>Opened_tables\uff1a\u4ee3\u8868\u81ea\u4eceMySQL\u542f\u52a8\u540e\uff0c\u6253\u5f00\u8868\u7684\u6570\u91cf\u3002<\/p>\n<p><!--more--><\/p>\n<p><strong>\u4e00\u3001\u72b6\u6001\u503c\u548c\u53d8\u91cf\uff1a<\/strong><\/p>\n<p><strong>1.1<\/strong><\/p>\n<p>\u5728MySQL\u7684show status\u4e2d\u67092\u4e2a\u72b6\u6001\u503c\uff1a<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-status-variables.html#statvar_Open_tables\">Open_tables<\/a>\u548c<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-status-variables.html#statvar_Opened_tables\">Opened_tables<\/a>\u3002\u8fd92\u4e2a\u503c\u4ee3\u8868\u7684\u610f\u601d\u5982\u4e0b\uff1a<\/p>\n<p>Open_tables\u00a0 \uff1a\u4ee3\u8868\u5f53\u524d\u6253\u5f00\u8868\u7684\u6570\u91cf\u3002<\/p>\n<p>Opened_tables\uff1a\u4ee3\u8868\u81ea\u4eceMySQL\u542f\u52a8\u540e\uff0c\u6253\u5f00\u8868\u7684\u6570\u91cf\u3002<\/p>\n<p>\u5173\u4e8eMySQL\u600e\u4e48\u6253\u5f00\u5173\u95ed\u8868\u7684\u5177\u4f53\u7ec6\u8282\u53c2\u8003\u6587\u6863\uff1a&lt;<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/table-cache.html\">How MySQL Opens and Closes Tables<\/a>&gt;\u3002<\/p>\n<p><span style=\"color: #ff0000;\">\uff081\uff09\u5bf9\u4e8emyisam\u5b58\u50a8\u5f15\u64ce\uff0c\u6253\u5f001\u5f20\u8868\u9700\u89812\u4e2a\u6587\u4ef6\u63cf\u8ff0\u7b26\uff08\u4e00\u4e2a.MYD\u6587\u4ef6\uff0c\u4e00\u4e2a.MYI\u6587\u4ef6\uff09\u3002<\/span><\/p>\n<p><span style=\"color: #ff0000;\">\uff082\uff09\u5bf9\u4e8einnodb\u5b58\u50a8\u5f15\u64ce\uff0c\u5f00\u542f\u8868\u7684\u72ec\u7acb\u8868\u7a7a\u95f4\uff08<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/innodb-parameters.html#sysvar_innodb_file_per_table\">innodb_file_per_table<\/a>\uff09\u6253\u5f001\u5f20\u8868\u53ea\u9700\u89811\u4e2a\u6587\u4ef6\u63cf\u8ff0\u7b26\uff08\u4e00\u4e2a.ibd\u6587\u4ef6\uff09\u3002<\/span><\/p>\n<p>\u3010MySQL Variable\u3011<\/p>\n<p>\u5bf9\u4e8e\u4e0a\u9762\u7684\u72b6\u6001\u503c\uff0c\u5bf9\u5e94\u76845.1.3\u7248\u672c\u540e\u7684MySQL\u53d8\u91cf\u53c2\u6570\u4e3a<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-system-variables.html#sysvar_table_open_cache\">table_open_cache<\/a>\uff0c\u800c\u65e9\u671f\u7248\u672c\u4e3a\uff1a<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-system-variables.html#sysvar_table_cache\">table_cache<\/a>\uff0c\u8be5\u53c2\u6570\u503c\u7684\u4ee3\u8868MySQL\u53ef\u4ee5\u7f13\u5b58\u7684\u6253\u5f00\u8868\u65f6\u5019\u7684\u6700\u5927<a href=\"http:\/\/en.wikipedia.org\/wiki\/File_descriptor\">\u6587\u4ef6\u63cf\u8ff0\u7b26<\/a>\u3002<\/p>\n<p><span id=\"more-55695\"> <\/span><\/p>\n<p><strong>1.2<\/strong><\/p>\n<p>\u5728MySQL 5.1.3\u4e4b\u540e\uff0c\u8fd8\u6dfb\u52a0\u4e862\u4e2a\u72b6\u6001\u503c\uff1a<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-status-variables.html#statvar_Open_table_definitions\">Open_table_definitions<\/a>\u548c<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-status-variables.html#statvar_Opened_table_definitions\">Opened_table_definitions<\/a>\u3002\u8fd92\u4e2a\u503c\u4ee3\u8868\u7684\u610f\u601d\u5982\u4e0b\uff1a<\/p>\n<p>Open_table_definitions\u00a0 \uff1a\u4ee3\u8868\u5f53\u524d\u7f13\u5b58\u4e86\u591a\u5c11.frm\u6587\u4ef6\u3002<\/p>\n<p>Opened_table_definitions\uff1a\u4ee3\u8868\u81ea\u4eceMySQL\u542f\u52a8\u540e\uff0c\u7f13\u5b58\u4e86.frm\u6587\u4ef6\u7684\u6570\u91cf\u3002<\/p>\n<p>\u9700\u8981\u6ce8\u610f\u7684\u662f.frm\u6587\u4ef6\u662fMySQL\u7528\u4e8e\u5b58\u653e\u8868\u7ed3\u6784\u7684\u6587\u4ef6\uff0c\u5bf9\u5e94myisam\u548cinnodb\u5b58\u50a8\u5f15\u64ce\u90fd\u5fc5\u987b\u6709\u7684\u3002<\/p>\n<p>\u3010MySQL Variable\u3011<\/p>\n<p>\u5bf9\u4e8e\u4e0a\u9762\u7684\u72b6\u6001\u503c\uff0c\u5bf9\u5e94\u76845.1.3\u7248\u672c\u540e\u7684MySQL\u53d8\u91cf\u53c2\u6570\u4e3a<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-system-variables.html#sysvar_table_definition_cache\">table_definition_cache<\/a>\uff0c\u8be5\u53c2\u6570\u503c\u7684\u4ee3\u8868MySQL\u53ef\u4ee5\u7f13\u5b58\u7684\u8868\u5b9a\u4e49\u7684\u6570\u91cf\u3002<span style=\"color: #ff0000;\">\u548c\u524d\u9762\u7684table cache\u4e0d\u540c\u7684\u662f\uff0c\u8868\u5b9a\u4e49\u7684\u7f13\u5b58\u5360\u7528\u7a7a\u95f4\u5f88\u5c0f\uff0c\u800c\u4e14\u4e0d\u9700\u8981\u4f7f\u7528\u6587\u4ef6\u63cf\u8ff0\u7b26\uff0c\u4e5f\u5c31\u662f\u53ea\u8981\u6253\u5f00.frm\u6587\u4ef6\uff0c\u7f13\u5b58\u8868\u5b9a\u4e49\uff0c\u7136\u540e\u5c31\u53ef\u4ee5\u5173\u95ed.frm\u6587\u4ef6\u3002<\/span><\/p>\n<p><strong>1.3<\/strong><\/p>\n<p>\u53e6\u5916\uff0c\u8fd8\u67092\u4e2a\u72b6\u6001\u503c\uff1a<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-status-variables.html#statvar_Open_files\">Open_files<\/a>\u548c<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-status-variables.html#statvar_Opened_files\">Opened_files<\/a>\u3002\u8fd92\u4e2a\u503c\u7684\u610f\u601d\u540c\u4e0a\u7c7b\u4f3c\uff1a<\/p>\n<p>Open_files\u00a0 \uff1a\u4ee3\u8868\u5f53\u524d\u6253\u5f00\u7684\u6587\u4ef6\u3002\u5bf9\u5e94\u5b58\u50a8\u5f15\u64ce\uff08\u5982\uff1ainnodb\uff09\u4f7f\u7528\u5b58\u50a8\u5f15\u64ce\u81ea\u5df1\u5185\u90e8\u51fd\u6570\u6253\u5f00\u7684\u8bdd\uff0c\u8fd9\u4e2a\u503c\u662f\u4e0d\u4f1a\u589e\u52a0\u7684\u3002<\/p>\n<p>Opened_files\uff1a\u4ee3\u8868\u4f7f\u7528MySQL\u7684my_open()\u51fd\u6570\u6253\u5f00\u8fc7\u7684\u6587\u4ef6\u6570\u3002\u5982\u679c\u4e0d\u662f\u4f7f\u7528\u8fd9\u4e2a\u51fd\u6570\u6253\u5f00\u6587\u4ef6\u7684\u8bdd\uff0c\u8fd9\u4e2a\u503c\u662f\u4e0d\u4f1a\u589e\u52a0\u7684\u3002<\/p>\n<p>\u3010MySQL Variable\u3011<\/p>\n<p>\u5bf9\u4e8e\u4e0a\u9762\u7684\u72b6\u6001\u503c\uff0c\u5bf9\u5e94\u7684MySQL\u53d8\u91cf\u53c2\u6570\u4e3a<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/server-system-variables.html#sysvar_open_files_limit\">open_files_limit<\/a>\u3002<\/p>\n<p><strong>\u4e8c\u3001\u6d4b\u8bd5<\/strong><\/p>\n<p>\u6d4b\u8bd5\u7684MySQL\u7248\u672c\uff1a<\/p>\n<blockquote>\n<p><span style=\"color: #999999;\">mysql&gt; <span style=\"color: #000000;\">select version();<\/span><\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014+<\/span><\/p>\n<p><span style=\"color: #999999;\">| version()\u00a0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014+<\/span><\/p>\n<p><span style=\"color: #999999;\">|<span style=\"color: #000000;\"> 5.1.47-log<\/span> |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014+<\/span><\/p>\n<p><span style=\"color: #999999;\">1 row in set (0.00 sec)<\/span><\/p>\n<\/blockquote>\n<p><strong> <\/strong><\/p>\n<p><strong>2.1 myisam<\/strong><strong>\u5b58\u50a8\u5f15\u64ce<\/strong><\/p>\n<p><strong>2.1.1<\/strong><\/p>\n<p>\u91cd\u542fMySQL\u670d\u52a1\u5668\uff0c\u53ef\u4ee5\u770b\u5230\u542f\u52a8\u65f6\u5019MySQL\u5df2\u7ecf\u81ea\u52a8\u6253\u5f00\u76f8\u5173\u7684\u7cfb\u7edf\u8868\u3002<\/p>\n<blockquote>\n<p><span style=\"color: #999999;\">mysql&gt; <span style=\"color: #000000;\">show global status like \u2018Open%_table_definitions\u2019;show global status like \u2018Open%_tables\u2019;show global status like \u2018Open%_files\u2019;<\/span><strong> <\/strong><\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_table_definitions\u00a0\u00a0 | 15\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #000000;\">| Opened_table_definitions | 15\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">2 rows in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #999999;\"><br \/> <\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| Variable_name | Value |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_tables\u00a0\u00a0 | 8\u00a0\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #000000;\">| Opened_tables | 15\u00a0 \u00a0\u00a0|<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">2 rows in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #999999;\"><br \/> <\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| Variable_name | Value |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_files\u00a0\u00a0\u00a0 | 19\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #000000;\">| Opened_files\u00a0 | 62\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">2 rows in set (0.00 sec)<\/span><\/p>\n<\/blockquote>\n<p>\u5728\u4e0a\u9762\u7684\u72b6\u6001\u8f93\u51fa\uff0c\u53ef\u4ee5\u770b\u5230Open_tables\u7684\u503c\u4e3a8\uff0c\u4ee3\u8868\u4e86\u5f53\u524d\u6253\u5f00\u4e868\u5f20\u8868\uff0c\u540c\u6837\u7684\u901a\u8fc7mysqladmin status\u547d\u4ee4\u4e5f\u53ef\u4ee5\u770b\u5230\uff1a<\/p>\n<blockquote>\n<p><span style=\"color: #999999;\">[mysql@xentest8-vm1 test]$ <span style=\"color: #000000;\">mysqladmin status<\/span><\/span><\/p>\n<p><span style=\"color: #999999;\">Uptime: 631\u00a0 Threads: 2\u00a0 Questions: 14\u00a0 Slow queries: 0\u00a0 Opens: 15\u00a0 Flush tables: 1\u00a0 <span style=\"color: #000000;\">Open tables: 8 <\/span>Queries per second avg: 0.22<\/span><\/p>\n<\/blockquote>\n<p>\u53ef\u4ee5\u901a\u8fc7<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/show-open-tables.html\">SHOW OPEN TABLES<\/a>\u547d\u4ee4\u67e5\u770b\uff0c\u5f53\u524d\u7684table cache\u5230\u5e95\u6253\u5f00\u4e86\u54ea\u4e9b\u8868\uff1a<\/p>\n<blockquote>\n<p><span style=\"color: #999999;\">mysql&gt;<span style=\"color: #000000;\"> show open tables;<\/span><\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2013+\u2014\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| Database | Table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | In_use | Name_locked |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2013+\u2014\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| mysql\u00a0\u00a0\u00a0 | servers\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">| mysql\u00a0\u00a0\u00a0 | db\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">| mysql\u00a0\u00a0\u00a0 | host\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">| mysql\u00a0\u00a0\u00a0 | columns_priv |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">| mysql\u00a0\u00a0\u00a0 | user\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">| mysql\u00a0\u00a0\u00a0 | procs_priv\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">| mysql\u00a0\u00a0\u00a0 | event\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">| mysql\u00a0\u00a0\u00a0 | tables_priv\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2013+\u2014\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\"><span style=\"color: #000000;\">8 rows in set<\/span> (0.00 sec)<\/span><\/p>\n<\/blockquote>\n<p>\u53ef\u4ee5\u770b\u5230\u6253\u5f00\u4e86mysql\u5e93\u91cc\u9762\u76848\u5f20\u7cfb\u7edf\u8868\uff08\u90fd\u662fmyisam\u5b58\u50a8\u5f15\u64ce\uff09\uff0c\u53e6\u5916\uff0c\u8fd8\u53ef\u4ee5\u901a\u8fc7lsof\uff08lsof\u7684\u7528\u6cd5\u53c2\u89c1&lt;<a href=\"http:\/\/www.ibm.com\/developerworks\/cn\/aix\/library\/au-lsof.html\">\u4f7f\u7528lsof\u67e5\u627e\u6253\u5f00\u7684\u6587\u4ef6<\/a>&gt;\uff09\u67e5\u770bMySQL\u6253\u5f00\u7684\u5bf9\u5e94\u6587\u4ef6\uff1a<\/p>\n<blockquote>\n<p><span style=\"color: #999999;\">[zhuxu@xentest8-vm1 ~]$<span style=\"color: #000000;\"> sudo lsof -u mysql | grep \/home\/mysql\/mysql-5.1.47\/data\/<span style=\"color: #ff0000;\">mysql<\/span>\/<\/span><\/span><\/p>\n<p><span style=\"color: #000000;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 16u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0 2048 87889757 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/host.MYI<\/span><\/p>\n<p><span style=\"color: #000000;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 17u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 87889758 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/host.MYD<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 18u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0 2048 87889760 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/user.MYI<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 19u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 380 87889761 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/user.MYD<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 20u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0 5120 87889754 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/db.MYI<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 21u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 880 87889755 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/db.MYD<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 22u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0 4096 87889772 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/tables_priv.MYI<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 23u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 87889773 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/tables_priv.MYD<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 24u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0 4096 87889775 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/columns_priv.MYI<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 25u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 87889776 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/columns_priv.MYD<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 26u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0 4096 87889808 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/procs_priv.MYI<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 27u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 87889809 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/procs_priv.MYD<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 28u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0 1024 87889769 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/servers.MYI<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 29u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 87889770 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/servers.MYD<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 30u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0 2048 87889817 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/event.MYI<\/span><\/p>\n<p><span style=\"color: #999999;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 31u\u00a0\u00a0 REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 87889818 \/home\/mysql\/mysql-5.1.47\/data\/mysql\/event.MYD<\/span><\/p>\n<\/blockquote>\n<p><strong><span style=\"color: #999999;\"> <\/span><\/strong><\/p>\n<p><strong>2.1.2<\/strong><\/p>\n<p>\u63a5\u7740\u6253\u5f00\u4e00\u5f20myisam\u5b58\u50a8\u5f15\u64ce\u7684\u8868\uff1a<\/p>\n<blockquote>\n<p><span style=\"color: #999999;\">mysql&gt;<span style=\"color: #000000;\"> select count(*) from myisam_1;<\/span><\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| count(*) |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">1 row in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #999999;\"><br \/> <\/span><\/p>\n<p><span style=\"color: #999999;\">mysql&gt; <span style=\"color: #000000;\">show global status like \u2018Open%_table_definitions\u2019;show global status like \u2018Open%_tables\u2019;show global status like \u2018Open%_files\u2019;<\/span><\/span><\/p>\n<p><span style=\"color: #008000;\"># \u53ef\u4ee5\u770b\u5230Opened_table_definitions\/Opened_table_definitions\/Open_tables\/Opened_tables\u7684\u503c\u90fd\u52a01<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_table_definitions\u00a0\u00a0 | 16\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #000000;\">| Opened_table_definitions | 16\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">2 rows in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #999999;\"><br \/> <\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| Variable_name | Value |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_tables\u00a0\u00a0 | 9\u00a0\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #000000;\">| Opened_tables | 16\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">2 rows in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #008000;\"># \u53ef\u4ee5\u770b\u5230Open_files\u7684\u503c\uff0819+2\uff09\u52a02\uff0cOpened_files\u7684\u503c\uff0862+3\uff09\u52a03<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| Variable_name | Value |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_files\u00a0\u00a0\u00a0 | 21\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #000000;\">| Opened_files\u00a0 | 65\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">2 rows in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #999999;\"><br \/> <\/span><\/p>\n<p><span style=\"color: #999999;\">mysql&gt; <span style=\"color: #000000;\">show create table myisam_1G<\/span><\/span><\/p>\n<p><span style=\"color: #999999;\">*************************** 1. row ***************************<\/span><\/p>\n<p><span style=\"color: #999999;\">Table: myisam_1<\/span><\/p>\n<p><span style=\"color: #000000;\">Create Table: CREATE TABLE `myisam_1` (<\/span><\/p>\n<p><span style=\"color: #000000;\">`a` int(11) DEFAULT NULL,<\/span><\/p>\n<p><span style=\"color: #000000;\">KEY `idx_myisam_1` (`a`)<\/span><\/p>\n<p><span style=\"color: #000000;\">) ENGINE=MyISAM DEFAULT CHARSET=latin1<\/span><\/p>\n<p><span style=\"color: #999999;\">1 row in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #999999;\"><br \/> <\/span><\/p>\n<p><span style=\"color: #999999;\">mysql&gt; <span style=\"color: #000000;\">show open tables from test;<\/span><\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014-+\u2014\u2014\u2014-+\u2014\u2014\u2013+\u2014\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| Database | Table\u00a0\u00a0\u00a0 | In_use | Name_locked |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014-+\u2014\u2014\u2014-+\u2014\u2014\u2013+\u2014\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| test\u00a0\u00a0\u00a0\u00a0 | myisam_1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014-+\u2014\u2014\u2014-+\u2014\u2014\u2013+\u2014\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">1 row in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #999999;\"><br \/> <\/span><\/p>\n<p><span style=\"color: #999999;\">[zhuxu@xentest8-vm1 ~]$ <span style=\"color: #000000;\">sudo lsof -u mysql | grep \/home\/mysql\/mysql-5.1.47\/data\/<span style=\"color: #ff0000;\">test<\/span>\/<\/span><\/span><\/p>\n<p><span style=\"color: #000000;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0 <span style=\"color: #ff0000;\"> 33u<\/span> REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0\u00a0 1024 87851510 \/home\/mysql\/mysql-5.1.47\/data\/test\/myisam_1.MYI<\/span><\/p>\n<p><span style=\"color: #000000;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 <span style=\"color: #ff0000;\">34u<\/span> REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0253,1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 87851511 \/home\/mysql\/mysql-5.1.47\/data\/test\/myisam_1.MYD<\/span><\/p>\n<p><span style=\"color: #008000;\"># \u53ef\u89c1\uff0c\u9664\u4e86\u9700\u8981\u6253\u5f00.frm\u6587\u4ef6\uff08\u6253\u5f00\uff0c\u7f13\u5b58\u540e\uff0c\u5c31\u5173\u95ed\uff09\uff0c\u5bf9\u4e8emyisam\u5b58\u50a8\u5f15\u64ce\uff0c\u6253\u5f001\u5f20\u8868\u9700\u89812\u4e2a\u6587\u4ef6\u63cf\u8ff0\u7b26\uff08\u4e00\u4e2a.MYD\u6587\u4ef6\uff0c\u4e00\u4e2a.MYI\u6587\u4ef6\uff09<\/span><\/p>\n<\/blockquote>\n<p><strong>2.1.3<\/strong><\/p>\n<p>\u5bf9\u5e94table cache\u4e2d\u7684\u7f13\u5b58\u7684\u8868\uff0c\u53ef\u4ee5\u901a\u8fc7<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/flush.html\">flush tables<\/a>\u547d\u4ee4\u6765\u628a\u5b83\u4eba\u5de5\u79fb\u51fatable cache:<\/p>\n<blockquote>\n<p><span style=\"color: #999999;\">mysql&gt; <span style=\"color: #000000;\">flush tables myisam_1;<\/span><\/span><\/p>\n<p><span style=\"color: #999999;\">Query OK, 0 rows affected (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #999999;\"><br \/> <\/span><\/p>\n<p><span style=\"color: #999999;\">mysql&gt; <span style=\"color: #000000;\">show open tables from test;<\/span><\/span><\/p>\n<p><span style=\"color: #999999;\"><span style=\"color: #000000;\">Empty set<\/span> (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #999999;\"><br \/> <\/span><\/p>\n<p><span style=\"color: #999999;\">mysql&gt; <span style=\"color: #000000;\">show global status like \u2018Open%_table_definitions\u2019;show global status like \u2018Open%_tables\u2019;show global status like \u2018Open%_files\u2019;<\/span><\/span><\/p>\n<p><span style=\"color: #008000;\"># \u53ef\u4ee5\u770b\u5230Open_table_definitions\u548cOpen_tables\u7684\u503c\u90fd\u51cf1<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_table_definitions\u00a0\u00a0 | 15\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">| Opened_table_definitions | 16\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">2 rows in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #999999;\"><br \/> <\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| Variable_name | Value |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_tables\u00a0\u00a0 | 8\u00a0\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">| Opened_tables | 16\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">2 rows in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #008000;\"># \u53ef\u4ee5\u770b\u5230Open_files\u7684\u503c\uff0821-2\uff09\u51cf2<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">| Variable_name | Value |<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_files\u00a0\u00a0\u00a0 | 19\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #999999;\">| Opened_files\u00a0 | <span style=\"color: #000000;\">65 <\/span>|<\/span><\/p>\n<p><span style=\"color: #999999;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #999999;\">2 rows in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #999999;\"><br \/> <\/span><\/p>\n<p><span style=\"color: #999999;\">[zhuxu@xentest8-vm1 ~]$<span style=\"color: #000000;\"> sudo lsof -u mysql | grep \/home\/mysql\/mysql-5.1.47\/data\/test\/ | wc -l<\/span><\/span><\/p>\n<p><span style=\"color: #000000;\">0<\/span><\/p>\n<\/blockquote>\n<p><strong>2.2 innodb<\/strong><strong>\u5b58\u50a8\u5f15\u64ce<\/strong><\/p>\n<p><strong>2.2.1<\/strong><\/p>\n<p>\u6253\u5f00\u4e00\u5f20innodb\u5b58\u50a8\u5f15\u64ce\u7684\u8868\uff1a<\/p>\n<blockquote>\n<p><span style=\"color: #888888;\">mysql&gt;<span style=\"color: #000000;\"> show variables like \u2018innodb_file_per_table\u2019;<\/span><\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">| Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| innodb_file_per_table | ON\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">1 row in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #888888;\">mysql&gt; <span style=\"color: #000000;\">select count(*) from t0001;<\/span><\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">| count(*) |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">1 row in set (0.01 sec)<\/span><\/p>\n<p><span style=\"color: #888888;\">mysql&gt; <span style=\"color: #000000;\">show global status like \u2018Open%_table_definitions\u2019;show global status like \u2018Open%_tables\u2019;show global status like \u2018Open%_files\u2019;<\/span><\/span><\/p>\n<p><span style=\"color: #008000;\"># \u53ef\u4ee5\u770b\u5230Opened_table_definitions\/Opened_table_definitions\/Open_tables\/Opened_tables\u7684\u503c\u90fd\u52a01<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">| Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_table_definitions\u00a0\u00a0 | 16\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #000000;\">| Opened_table_definitions | 17\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">2 rows in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">| Variable_name | Value |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_tables\u00a0\u00a0 | 9\u00a0\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #000000;\">| Opened_tables | 17\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">2 rows in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #008000;\"># \u53ef\u4ee5\u770b\u5230Open_files\u7684\u503c\u4e0d\u53d8\uff08\u5373innodb\u4f7f\u7528\u5b58\u50a8\u5f15\u64ce\u81ea\u5df1\u5185\u90e8\u51fd\u6570\u6253\u5f00\u7684\u8bdd\uff0c\u8fd9\u4e2a\u503c\u662f\u4e0d\u4f1a\u589e\u52a0\uff09<\/span><\/p>\n<p><span style=\"color: #008000;\"># Opened_files\u7684\u503c\uff0865+1\uff09\u52a01\uff08\u9700\u8981\u6253\u5f00.frm\u6587\u4ef6\uff0c\u7f13\u5b58\u540e\uff0c\u5c31\u5173\u95ed\uff09<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">| Variable_name | Value |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| Open_files\u00a0\u00a0\u00a0 | 19\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #000000;\">| Opened_files\u00a0 | 66\u00a0\u00a0\u00a0 |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">2 rows in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #888888;\">mysql&gt;<span style=\"color: #000000;\"> show open tables from test;<\/span><\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014-+\u2014\u2014-+\u2014\u2014\u2013+\u2014\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">| Database | Table | In_use | Name_locked |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014-+\u2014\u2014-+\u2014\u2014\u2013+\u2014\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #000000;\">| test\u00a0\u00a0\u00a0\u00a0 | t0001 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |<\/span><\/p>\n<p><span style=\"color: #888888;\">+\u2014\u2014\u2014-+\u2014\u2014-+\u2014\u2014\u2013+\u2014\u2014\u2014\u2014-+<\/span><\/p>\n<p><span style=\"color: #888888;\">1 row in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #888888;\">mysql&gt; <span style=\"color: #000000;\">show create table t0001G<\/span><\/span><\/p>\n<p><span style=\"color: #888888;\">*************************** 1. row ***************************<\/span><\/p>\n<p><span style=\"color: #888888;\">Table: t0001<\/span><\/p>\n<p><span style=\"color: #000000;\">Create Table: CREATE TABLE `t0001` (<\/span><\/p>\n<p><span style=\"color: #000000;\">`a` int(11) DEFAULT NULL<\/span><\/p>\n<p><span style=\"color: #000000;\">) ENGINE=InnoDB DEFAULT CHARSET=latin1<\/span><\/p>\n<p><span style=\"color: #888888;\">1 row in set (0.00 sec)<\/span><\/p>\n<p><span style=\"color: #888888;\">[zhuxu@xentest8-vm1 ~]$ <span style=\"color: #000000;\">sudo lsof -u mysql | grep \/home\/mysql\/mysql-5.1.47\/data\/test\/<\/span><\/span><\/p>\n<p><span style=\"color: #000000;\">mysqld\u00a0\u00a0\u00a0 14683 mysql\u00a0\u00a0 <span style=\"color: #ff0000;\">33uW <\/span>REG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 253,1\u00a0\u00a0\u00a0\u00a0 98304 87851074 \/home\/mysql\/mysql-5.1.47\/data\/test\/t0001.ibd<\/span><\/p>\n<\/blockquote>\n<p><strong>2.2.2<\/strong><\/p>\n<p>\u5bf9\u4e8einnodb\u5b58\u50a8\u5f15\u64ce\uff0ctable cache\u5bf9\u5176\u5f71\u54cd\u5176\u5b9e\u4e0d\u662f\u5f88\u5927\uff0cinnodb\u6709\u81ea\u5df1\u7684\u6570\u636e\u5b57\u5178\uff0c\u53ef\u4ee5\u7f13\u5b58\u76f8\u5173\u8868\u4fe1\u606f\u3002<\/p>\n<p>\u5176\u4ed6\u76f8\u5173\u8d44\u6599\uff1a<\/p>\n<p>\uff081\uff09\u5728&lt;<a href=\"http:\/\/oreilly.com\/catalog\/9780596101718\">High.Performance.MySQL<\/a>&gt;\u4e2d\u7684The InnoDB Data Dictionary\uff08P280\u9875\uff09\u4e2d\u63cf\u8ff0\uff1a<\/p>\n<p>InnoDB has its own per-table cache, variously called a table  definition cache or data dictionary, which you cannot configure. When  InnoDB opens a table, it adds a corresponding object to the data  dictionary. Each table can take up 4 KB or more of memory (although much  less space is required in MySQL 5.1). Tables are not removed from the  data dictionary when they are closed.<\/p>\n<p>The main performance issue\u2014besides memory requirements\u2014is opening and  computing statistics for the tables, which is expensive because it  requires a lot of I\/O. In contrast to MyISAM, InnoDB doesn\u2019t store  statistics in the tables permanently; it recomputes them each time it  starts. This operation is serialized by a global mutex in current  versions of MySQL, so it can\u2019t be done in parallel. If you have a lot of  tables, your server can take hours to start and fully warm up, during  which time it might not be doing much other than waiting for one I\/O  operation after another. We mention this to make sure you know about it,  even though there\u2019s nothing you can do to change it. It\u2019s normally a  problem only when you have many (thousands or tens of thousands) large  tables, which cause the process to be I\/O-bound.<\/p>\n<p>If you use InnoDB\u2019s innodb_file_per_table option (described later in  \u201cConfiguring the tablespace\u201d on page 291), there\u2019s also a separate limit  on the number of .ibd files InnoDB can keep open at any time. This is  handled by the InnoDB storage engine, not the MySQL server, and is  controlled by innodb_open_files. InnoDB doesn\u2019t open files the same way  MyISAM does: whereas MyISAM uses the table cache to hold file  descriptors for open tables, in InnoDB there is no direct relationship  between open tables and open files. InnoDB uses a single, global file  descriptor for each .ibd file. If you can afford it, it\u2019s best to set  innodb_open_files large enough that the server can keep all .ibd files  open simultaneously.<\/p>\n<p>\uff082\uff09&lt;<a href=\"http:\/\/www.mysqlperformanceblog.com\/2009\/11\/18\/how-innodb_open_files-affects-performance\/\">How innodb_open_files affects performance<\/a>&gt;<\/p>\n<p>\uff083\uff09&lt;<a href=\"http:\/\/www.mysqlperformanceblog.com\/2008\/12\/14\/show-open-tables-what-is-in-your-table-cache\/\">SHOW OPEN TABLES \u2013 what is in your table cache<\/a>&gt;\u91cc\u9762\u6709\u4e00\u53e5\u63cf\u8ff0\uff1a<\/p>\n<p>Note however if you\u2019re starting MySQL Command line client without  \u201c-A\u201d option it opens all tables in the active database to allow tab  completion which can screw results.<\/p>\n<p>\u5728\u5b9e\u9645\u7684\u6d4b\u8bd5\u4e2d\uff0c\u5bf9\u4e8e5.0.67-log\u7248\u672c\u4f1a\u51fa\u73b0\u4e0a\u9762\u7684\u60c5\u51b5\uff0c\u5bf9\u4e8e5.1\u7248\u672c\u7684\u6d4b\u8bd5\uff0c\u4e0a\u9762\u7684\u73b0\u8c61\u662f\u4e0d\u5b58\u5728\u3002<\/p>\n<p>\u2013EOF\u2013<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u3001\u72b6\u6001\u503c\u548c\u53d8\u91cf\uff1a 1.1 \u5728MySQL\u7684show status\u4e2d\u67092\u4e2a\u72b6\u6001\u503c\uff1aOpen_tables\u548cOpened_tables\u3002\u8fd92\u4e2a\u503c\u4ee3\u8868\u7684\u610f\u601d\u5982\u4e0b\uff1a Open_tables \uff1a\u4ee3\u8868\u5f53\u524d\u6253\u5f00\u8868\u7684\u6570\u91cf\u3002 Opened_tables\uff1a\u4ee3\u8868\u81ea\u4eceMySQL\u542f\u52a8\u540e\uff0c\u6253\u5f00\u8868\u7684\u6570\u91cf\u3002<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33],"tags":[],"class_list":["post-147","post","type-post","status-publish","format-standard","hentry","category-centos-6"],"_links":{"self":[{"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/posts\/147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/comments?post=147"}],"version-history":[{"count":0,"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/posts\/147\/revisions"}],"wp:attachment":[{"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/media?parent=147"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/categories?post=147"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/shan.info\/new\/wp-json\/wp\/v2\/tags?post=147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}