导读

作者：Gopal Shankar

翻译：徐晨亮

Coinciding with the new native data dictionary in MySQL 8.0, we have made a number of useful enhancements to our  INFORMATION_SCHEMA  subsystem design in MySQL 8.0. In this post I will first go through our legacy implementation as it has stood since MySQL 5.1, and then cover what’s changed.

与MySQL 8.0原生数据字典一致，在MySQL 8.0的 INFORMATION_SCHEMA 子系统设计中，我们做了一些很有用的增强。在这篇文章中，我将会介绍自MySQL 5.1以来的旧的实现方式，然后介绍我们做了什么改变。

Background

INFORMATION_SCHEMA  was first introduced into MySQL 5.0, as a standards compliant way of retrieving meta data from a running MySQL server. When we look at the history of  INFORMATION_SCHEMA  there have been a number of complaints about the performance of certain queries, particularly in the case that there are many database objects (schemas, tables etc).

INFORMATION_SCHEMA 首次引入MySQL 5.0，作为一种从正在运行的MySQL服务器检索元数据的标准兼容方式。当我们回顾 INFORMATION_SCHEMA 的历史时，对于某些特定查询性能总是有很多的抱怨，特别是在有许多数据库对象（schema，表等）的情况下。

In an effort to address these reported issues, since MySQL 5.1 we have made a number of performance optimizations to speed up the execution of  INFORMATION_SCHEMA  queries. The optimizations are described in the MySQL manual, and apply when the user provides an explicit schema name or table name in the query.

为了解决这些上报的问题，从MySQL 5.1开始，我们进行了许多性能优化来加快 INFORMATION_SCHEMA 查询的执行速度。MySQL手册 <链接1> 中描述了这些优化，当用户在查询中提供显式schema名称或表名时，将会应用这些。

Alas, despite these improvements  INFORMATION_SCHEMA  performance is still a major pain point for many of our users. The key reason behind these performance issues in the current  INFORMATION_SCHEMA  implementation is that  INFORMATION_SCHEMA  tables are implemented as temporary tables that are created on-the-fly during query execution. These temporary tables are populated via:



Meta data from files, e.g. table definitions from .FRM files.

Details from storage engines, e.g. dynamic table statistics.

Data from global data structures in the MySQL server.



尽管有这些改进， INFORMATION_SCHEMA的 性能仍然是我们许多用户的主要痛点。在当前 INFORMATION_SCHEMA 实现方式下产生的性能问题背后的关键原因是， INFORMATION_SCHEMA 表的查询实现方式是在查询执行期间创建临时表。这些临时表通过以下方式填充：



元数据来自文件，例如：表定义来自FRM文件

细节来自于存储引擎，例如：动态表的统计信息

来自MySQL server层中全局数据结构的数据





For a MySQL server having hundreds of database, each with hundreds of tables within them, the  INFORMATION_SCHEMA query would end-up doing lot of I/O reading each individual FRM files from the file system. And it would also end-up using more CPU cycles in effort to open the table and prepare related in-memory data structures. It does attempt to use the MySQL server table cache (the system variable ‘ table_definition_cache ‘), however in large server instances it’s very rare to have a table cache that is large enough to accommodate all of these tables.

对于一个MySQL实例来说可能有上百个库，每个库又有上百张表， INFORMATION_SCHEMA 查询最终会从文件系统中读取每个单独的FRM文件，造成很多I/O读取。 并且最终还会消耗更多的CPU来打开表并准备相关的内存数据结构。 它确实尝试使用MySQL server层的表缓存（系统变量 table_definition_cache  ），但是在大型实例中，很少有一个足够大的表缓存来容纳所有的表。

One can easily face the above mentioned performance issue if the optimization is not used by the  INFORMATION_SCHEMA query. For example, let us consider the two queries below

如果 INFORMATION_SCHEMA 查询未使用优化，则可以很容易碰到上面的性能问题。 例如，让我们考虑下面的两个查询

EXPLAIN
WHERE
TABLE_SCHEMA
TABLE_NAME
FRM
FRM
EXPLAIN
WHERE
TABLE_SCHEMA
TABLE_NAME
FRM
FRM
FRM
FRM
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
ENGINE
AUTO_INCREMENT
AVG_ROW_LENGTH
DATA_FREE
DATA_FREE
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
ENGINE
AUTO_INCREMENT
AVG_ROW_LENGTH
DATA_FREE
DATA_FREE
information_schema_stats
cached
information_schema_stats=latest
information_schema_stats
缓存
information_schema_stats = latest
ANALYZE TABLE
ANALYZE TABLE
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA
INFORMATION_SCHEMA的
INFORMATION_SCHEMA