MySQL表的打开和关闭
本文问题
- MySQL为什么要打开表？
- MySQL打开表对操作系统有什么影响，哪种存储引擎有额外的影响？
- MySQL在什么情况下打开表，在什么情况下关闭表？
- MySQL表缓存相关的参数有哪些？都有什么作用？
- MySQL表缓存的释放机制是怎样的？
- 如何查看MySQL缓存的表的数量？
- 在什么情况下应该增加表缓存？
- 如何查看当前缓存了哪些表？
MySQL表的打开和关闭
MySQL是多线程的，因此可能有多个客户端同时对同一个表进行查询，为了减少多个客户端会话在同一个表上具有不同状态的问题，并发的每个会话都会独立的打开表。这样会使用额外的内存，但是通常可以提升性能。
为了减少会话间对表缓存的争用，提高扩展性，表缓存还可以被分区为多个小的缓存实例。对于DML操作，会话只需要锁定它访问的一个实例就可以执行。DDL操作还是需要锁定整个表缓存。
MySQL表的打开
在MySQL执行查询时，会先打开并缓存表。
MyISAM表的特殊情况
对于MyISAM存储引擎，每个打开的表都需要两个文件描述符，一个用于数据文件，一个用于索引文件。当一个表已经打开后，再进行打开只需要占用一个文件描述符，因为索引文件描述符在所有线程之间共享。
对于使用分区的MyISAM表，打开表的每个分区都需要两个文件描述符（当MyISAM打开一个使用分区的表时，无论是否使用分区，都会打开表中的所有分区。）
MyISAM为每个并发的访问打开表。这意味着，如果有两个线程访问同一个表，或者一个线程在同一个查询中访问两次表（例如join表本身），MyISAM表需要被打开两次。每个打开表的请求都会占用表缓存的一个条目。
MySQL表的关闭
在以下情况下，MySQL关闭未使用的表并将他们从缓存中删除
- 当表缓存已满并且线程尝试打开一个不在缓存中的表的时候
- 当表缓存中的内容超过
table_open_cache的数量并且一个在缓存中的表不再被任何线程使用的时候 - 当发出表刷新语句的时候。表刷新语句包括
FLUSH TABLES,mysqladmin flush-tables,mysqladmin reflush
MySQL表缓存的清理机制
当表缓存已满的时候，使用以下机制释放缓存
- 从最近最少使用的表开始，释放当前未使用的表
- 如果必须打开一个不在缓存中的表，但是缓存已满并且没有表可以释放，则可以根据需要临时扩展缓存。
- 当缓存处于临时扩展状态并且其中的表从使用中的状态转换为未使用的状态时，这个表会关闭并从缓存从释放
表缓存相关的参数
table_open_cache
全局变量，可以动态修改，默认值2000。取值范围400-524288
所有线程打开的表的数量。
table_open_cache_instances
全局变量，不可以动态修改，默认值16，取值范围1-64
在16核或更高内核数量的操作系统中，建议将值设置为8或16
table_definition_cache
全局变量，可以动态修改，默认值-1，表示自动调节。取值范围400-524288
默认值 400 + ( table_open_cache / 2 )，最高默认值为2000
可以存储在表定义缓存中的表定义（.frm文件）数量。如果表的数量很多，可以增加该值来提升打开表的速度。表定义缓存使用较小的空间，并且不像表缓存一样需要使用文件描述符。
表缓存相关的状态
全局状态
Open_files
当前打开的文件数。只包括在数据库级别打开的文件，不包括Socked文件和管道文件，也不包过存储引擎使用自己内部函数打开的文件。
Opened_files
打开过的文件数。只计算通过my_open()函数打开的文件数
全局+会话状态
Open_tables
当前打开的表数量
Opened_tables
打开过的表数量
Open_table_definitions
当前打开的.frm文件数量
Opened_table_definitions
打开过的.frm文件数量
Table_open_cache_hits
表缓存命中次数
Table_open_cache_misses
表缓存未命中次数
Table_open_cache_overflows
表缓存溢出次数。就是在一个表打开或关闭后，一个缓存实例具有空白条目并且缓存实例实际大小大于缓存实例理论大小（table_open_cache/table_open_chche_instances）的次数。
查看缓存的表
语句
SHOW OPEN TABLES
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
show open tables;
+--------------------+----------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+----------------+--------+-------------+
| performance_schema | session_status | 0 | 0 |
+--------------------+----------------+--------+-------------+
输出说明
Database
库名
Table
表名
In_use
对于该表的锁或锁请求数量（表级锁）。
Name_locked
表名称是否被锁定。当删除表或者重命名表时会锁定表名称。
问题答案
- MySQL为什么要打开表？
为了提升性能。 - MySQL打开表对操作系统有什么影响，哪种存储引擎有额外的影响？
打开表需要占用系统的文件描述符。对于MyISAM表，首次打开时需要两个文件描述符，一个用于数据文件，一个用户索引文件。当MyISAM表第二次打开时，只需要一个文件描述符，因为索引文件在所有线程之间共享。对于MyISAM分区表，在打开表时会打开所有表分区，每个分区都需要两个文件描述符。 - MySQL在什么情况下打开表，在什么情况下关闭表？
每个并发的查询都会打开表。当表缓存已满但是需要缓存新表的时候，会关闭缓存中未使用的表并将它从缓存中删除。也可以使用FLUSH TABLES主动关闭表。 - MySQL表缓存相关的参数有哪些？都有什么作用？
table_open_cache定义了打开表的数量
table_open_cache_instances定义了表缓存的实例数量。在进行DML操作时，只需要锁定一个缓存实例
table_definition_cache定义了.frm文件的缓存数量 - MySQL表缓存的释放机制是怎样的？
在表缓存已满但是需要缓存新的表的时候，首先检查缓存中是否存在未在使用的表，如果存在，按照最近最少使用的原则关闭并释放表，如果没有未使用的表，临时扩展缓存并缓存新表，当缓存中的表不再使用时，对未使用的表进行关闭和释放，直到缓存的表数量小于等于open_table_cache - 如何查看MySQL缓存的表的数量？
SHOW GLOBAL STATUS LIKE '%open%' - 在什么情况下应该增加表缓存？
在没使用FLUSH TABLES的情况下，发现Opened_tables值很大。 - 如何查看当前缓存了哪些表？
SHOW OPEN TABLES
还可以通过SHOW OPEN TABLES WHERE in_use>0查看哪些表持有表锁。
