MySQL 5.7和更早版本中为什么访问 information_schema.TABLES 运行缓慢 ...

适用范围

MySQL Server - Version 5.0 to 5.7

问题描述

数据库有许多表时，查询information_schema.TABLES没有限制数据库/表名的WHERE子句，尤其会发生访问慢的情况。

问题原因

information_schema.TABLES是虚拟的，即内容是在被请求时即时生成的。因此，请求有关表的信息需要找到表。MySQL使用文件系统作为数据字典，因此定位表需要文件系统操作，tables表中的一些数据甚至需要从文件中读取信息。这些都是缓慢的操作，所以当有许多表要扫描时，查询可能会花费很长时间，并对数据库的其余活动造成相对较大的影响。

请注意额外信息Open_full_table and Scanned all databases。MySQL在所有数据库中查找表，这意味着对于找到的每个表，它都被打开​。

优化建议

表缓存足够大,MySQL支持缓存表句柄和表定义。如果高速缓存已满，那么打开表或表定义将导致高速缓存中的现有表被移除(假设它当前未被使用)。这是一个相对较慢的过程，可能会对其他查询造成影响。

确保table_open_cache 与 table_definition_cache 足够大，能够容纳所有打开的表和打开的表定义，那么它将对 information_schema.TABLES  进行更快的查询，并减少对其他查询的影响。

解决办法

​低版本升级 MySQL 8.0 or Later

MySQL 8.0引入了一个全新的数据字典，数据存储在InnoDB表中，而不是磁盘上的frm文件。这意味着information_schema.TABLES现在是底层数据字典表的视图，这将大大提高整体性能。