超万张表时查询information_schema.tables现象趣谈

很多小伙伴在使用华为云DAS数据管理服务、其他工具软件（Navicat, Mysql Workbench, Dbeaver等）访问mysql数据库（5.5, 5.6, 5.7版本）时都遇到过如下情况：单库表数量比较多的时候，移动滚动条查看左侧树表列表时会出现列表刷新过慢，不同程度的卡顿，甚至卡死的情况，针对该问题我们来分析下原因。

Mysql有四个系统库：information_schema, performance_schema, sys, mysql;

information_schema：保存了mysql服务器所有数据库的信息。比如数据库的库名、数据库的表、数据库表的数据类型，索引，外键、存储过程、视图，函数，事件，触发器等结构信息。

performance_schema：主要用于收集数据库服务器性能参数，可用于监控服务器在一个较低级别的运行过程中的资源消耗、资源等待等情况。

sys：库中所有的数据源来自performance_schema，目标是降低performance_schema的复杂度。

mysql：mysql的核心数据库，主要负责存储数据库的用户、权限设置、关键字等mysql自身需要使用的控制和管理信息。

当我们使用华为云DAS数据管理服务查看左侧树表列表时，服务会下发类似查询sql：

select table_name from information_schema.tables where table_schema = '库名' and table_type = 'BASE TABLE';

也就是说表列表数据是从information_schema.tables这个表查询而来，并且查询的是库全量表，当库中表数量过多，应用程序过多时就会出现内存不足卡顿的情况；一次查询大数据量（10万级）也会影响mysql数据库性能，甚至引起数据库宕机。

关于表数量过多引起msyql服务器out of memory的官方bug链接:

https://bugs.mysql.com/bug.php?id=72322

下面我们来分析一下有那些优化方法来解决上述出现的问题（一次查询表数量过大，内存占用过多）：

方式一：分页查询

select table_name from information_schema.tables where table_schema = '库名' and table_type = 'BASE TABLE' limit 0, 100;

分页查询是想到的最直接的解决方案，这种方式能解决减少查询数据量的难点，但是并不能解决查询效率和搜索效率的问题；

我们分析mysql 5.7版本的information_schema.tables表发现，该表采用memory存储引擎，表结构数据都存储在mysql服务器内存中，但是任何列上都没有索引存在，意味着分页查询也是从头遍历所有表数据，表数量过多时，服务器内存占用过大和查询效率依旧不理想；这也是mysql 8.0之前版本系统表性能不足的地方；

方式二：分页查询+8.0版本myql server

5.7版本mysql的information_schema.tables表无法满足用户的大数据量查询，那么到了8.0版本是否有所改善呢？

查看information_schema.tables表创建脚本可以发现，8.0版本information_schema.tables其实是一个视图，数据来源来自系统库‘mysql’中各个系统表（myql.tables, myql.tbl, mysql. schemata等），各个系统表统一采用了Innodb存储引擎，各表间通过主键id关联，并且关联主键上加了索引；

通过测试数据我们观察8.0版本的information_schema.tables分页查询效率：

准备数据：单库中创建8万张测试表

分页查询：

从结果分析查询时长在毫秒级，对于系统表的查询效率是可以接受的。

分析explain执行结果：

多个系统表的联合查询都使用了索引。

方式三：云上解决方案

华为云DAS数据管理服务新版本参考Mysq8.0版本实现方式，在云上建立使用Innodb引擎的类系统表table_meta，该表存储数据库实例下所有表的结构元数据(实例名称，库名，表名，存储引擎，表大小，索引大小等)，在实例名称，库名，表名上建立联合索引。在用户授权的前提下，按实例和库维度定期同步用户实例下各个库结构元数据信息（如：information_schema.tables）；

同步元数据可采用的查询方案：

1. 分页查询：

select table_name from information_schema.tables where table_schema = '库名' and table_type = 'BASE TABLE' limit 0, 100;

1. 流式读取：

   PreparedStatement ps = connection.prepareStatement("select table_name from information_schema.tables where table_schema = '库名' and table_type = 'BASE TABLE';", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

   ps.setFetchSize(Integer.MIN_VALUE);

   ResultSet rs = ps.executeQuery();

   while (rs.next()) {

   　　String tableName = rs.getString("table_name");

   }

由于8.0以下版本mysql的系统元数据表（information_schema.tables等）没有任何索引存在，无论是直接分页查询还是通过流式读取的方式，当表数量比较大（万级）依然会存在查询效率和mysql服务器内存彪高问题；慢sql的存在除了影响用户库的性能也会影响整个采集性能。

针对这种场景需要限制表数量：myqsl8.0以下版本单实例表数量大于10000（覆盖99.99%业务场景），限制该实例使用左侧树表列表功能，同时也不会做元数据同步；这即是对数据管理服务的保护也是对用户mysql库的保护；

除表元数据外，华为云DAS数据管理服务还在云上建立各个结构（列，索引，外键，视图，存储过程，函数，触发器，事件）的元数据表，在实例名称，库名，结构名称上建立联合索引；按实例和库维度定期同步用户实例下库结构元数据信息，并且通过DDL触发自动同步和界面手动下发同步的方式来确保DAS服务云上元数据表数据和用户库信息的一致性；

各个库结构的列表展示直接查询DAS元数据表，通过中间层存储的方式提高查询效率和减少数据库宕机风险；同时支持各个结构的实例级别和库级别搜索，让数据管理更高效，更安全。