MySQL 5.7：由访问I_S.tables慢做的简要分析

本文只是分析5.7，因为遇到的问题是在5.7上，8.0有了很大的改变。由于水平有限只看感兴趣的主要流程。

一、问题描述

最近有朋友说访问 information_schema.tables 比较慢，因此做了一些学习。实际上在学习这部分的时候，我发现实际上如下命令都有一定的关联

show tables（不需要实际的open table）
show databases（不需要实际的open table，流程稍有不同）
show tables status
show full fields from
show index from
select * from information_schema.tables

它们有着类似的调用路径，只是返回条件不同，或者填充的回调函数不同。下面我们先看看流程，然后最后来说说为什么访问 information_schema.tables 比较慢。这里我们用 table cache/table define 来表示open的table和table share。

二、大体过程分析

总的来说除了show databases其他都是调用get_all_tables做为总的接口，

1. get_all_tables

主要完成的功能是构建需要访问的db和table，然后以循环每个库每个表的方式依次打开每个表调用fill_schema_table_by_open，但是需要注意的点如下。

如果select * from information_schema.tables带了where条件那么构建的时候可以过滤掉其他的表。get_lookup_field_values是构建的查询条件，过滤条件应该是这里过滤的，而对于show tables status/show full fields from/show index from 需要open 的表只有1个，循环只是进行一次。
make_db_list是实际的访问我们的数据目录（OS层面）得到的库名，其中参数ignore_db_dir 的实现就在其中，而对于show databases实际上也会调用这个函数但是总体接口是fill_schema_schemata。
make_table_name_list是实际的访问我们的物理文件（OS层面）得到的表名，当前看起来就是访问的.frm文件
对于show tables跑完这里就结束了，不需要调用fill_schema_table_by_open进行实际的表打开，因为不需要table cache的信息。

这里我们发现5.7的show databases/show tables 都是实际的访问的物理文件得到的库名和表名，因此即便innodb字典不存在也能在show databases/show tables看到，当然到8.0肯定是不行了。

2. fill_schema_table_by_open

如果需要获取的表信息很多，本函数会多次调用，比如我们访问tables表。这里主要完成的功能是调用open_tables_for_query实际的打开表，然后通过 table cache来填充我们需要的信息，最后调用close_thread_tables来关闭表并且释放MDL LOCK。这里需要注意的是。

schema_table->process_table处是一个回调函数，表示的是对实际需要的信息进行填充。对于访问tables表调用的是get_schema_tables_record /对于show index调用的是get_schema_stat_record/对于show fileds调用的是get_schema_column_record，但是如果打开看看，它们都使用到table cache中的信息，因此进行实际的表的打开是必须的。而不像show tables和/show databases只需要访问物理文件即可。
使用了标记MYSQL_OPEN_IGNORE_FLUSH进行实际的表打开，所以几乎不会应为flush tables而堵塞操作的进行
使用了标记MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL，对于MDL LOCK来说是最轻量级的SH类型。

3. open_table

open_tables_for_query
  -> open_tables（进入opening tables状态）
    -> open_and_process_table
        -> open_table

最终会调用open_table打开表，具体的打开的过程很多文章都涉及，不分析了。但是需要注意如下：

MDL LOCK在此处进行获取或者堵塞，但是对于我们当前分析这种操作MDL LOCK是最轻量的SH类型
本处比较很容易看到如果table cache在缓存中，则直接获取。如果table cache不在则调用open_table_from_share进行从table define进行建立。如果table define也不在调用get_table_share_with_discover进行table define的建立。
普通操作进行打开table cache的时候会判定是否有老的table share版本，如果存在就会进行堵塞，但是我们前面说了当前分析的操作加了MYSQL_OPEN_IGNORE_FLUSH而不做判断
table define的缓存是全局的，但是table cache的缓存是分实例的由table_cache_manager进行管理，从分配来看就是table_cache_manager.get_cache(thd)，因此一个会话通常只能使用一个table cache实例，默认参数table_open_cache_instances为16，即table_open_cache/table_open_cache_instances就是每个session可以使用的table cache数量，我们很容易找到如下定义

table_cache_size_per_instance= table_cache_size / table_cache_instances;

如果大于则会进行淘汰实现在 Table_cache::free_unused_tables_if_necessary中，这也是我开始单session打开3000多个表测试的时候，查看Open_tables一直上不去的原因。

LOCK_open锁是用于保护table define的全局结构的，从历史上看本锁以前也负责table cache结构的保护，但是在WL#5772 “Add partitioned Table Definition Cache to avoid using LOCK_open and its derivatives in DML queries”.进行了拆分。每个table cache实例有自己的锁Table_cache::m_lock进行保护。
在关闭table的时候会将其进行缓存在table cache的缓存中我们来看Table_cache::release_table中一段注释

  /* Remove TABLE from the list of used objects for the table in this cache. */
  /* Add TABLE to the list of unused objects for the table in this cache. */
  /* Also link it last in the list of unused TABLE objects for the cache. */

如果能够缓存下次使用就方便了，直接拿出来就可以了。

三、回到问题本身

我们做了这些分析后已经知道访问tables实际上会打开所有的表，经过我的测试打开3000+表如下，

如果需要建立table cache和table define 大约12秒+
如果只需要建立table cache为10秒+
如果都不需要为1秒+（我修改了table_open_cache_instances为1，不建议修改原因如上文分析）

访问tables表通常线程CPU耗用很高，几乎80%的时间花在对table cache的构建上，这点也可以通过perf进行确认，并且状态长期处于opening tables状态，如下，

但是由于table_open_cache/table_open_cache_instances是单个session可以用到最大table cache缓存数量，如果有大量的表不可能会全部缓存，所有访问tables表慢也没办法，但是这对普通的业务会话影响不大，因为业务语句不可能如此集中的打开大量的表。因此建议如下：