首页 / MYSQL / MySQL8.0-INFORMATION_SCHEMA增强

MySQL8.0-INFORMATION_SCHEMA增强

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL8.0-INFORMATION_SCHEMA增强，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含13566字，纯文字阅读大概需要20分钟。

内容图文

此文转载自：https://blog.csdn.net/n88Lpo/article/details/100907209#commentBox

导读

作者：Gopal Shankar
翻译：徐晨亮原文地址： https://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/

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 查询未使用优化，则可以很容易碰到上面的性能问题。例如，让我们考虑下面的两个查询

mysql > EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA. TABLES WHERE
-> TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: TABLES
partitions: NULL
type: ALL
possible_keys: NULL
key: TABLE_SCHEMA,TABLE_NAME
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Using where; Skip_open_table; Scanned 0 databases
1 row in set, 1 warning ( 0. 00 sec)

mysql > EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA. TABLES WHERE
-> TABLE_SCHEMA like 'test%' AND TABLE_NAME like 't%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: TABLES
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Using where; Skip_open_table; Scanned all databases
1 row in set, 1 warning ( 0. 00 sec)

As we can see from the EXPLAIN output, we see that the former query would use the values provided in WHERE clause for the TABLE_SCHEMA and TABLE_NAME field as a key to read the desired FRM files from the file system. However, the latter query would end up reading all the FRM in the entire data directory, which is very costly and does not scale. 从 EXPLAIN 的输出可以看到。我们看到前一个查询将使用 WHERE 子句中为 TABLE_SCHEMA 和 TABLE_NAME 字段提供的值作为键，从文件系统读取所需 FRM 文件。但是，后一个查询最终将读取整个数据目录中的所有 FRM ，这非常昂贵且无法扩展。
Changes in MySQL 8.0
One of the major changes in 8.0 is the introduction of a native data dictionary based on InnoDB. This change has enabled us to get rid of file-based metadata store ( FRM files) and also help MySQL to move towards supporting transactional DDL. For more details on introduction of data dictionary feature in 8.0 and its benefits, please look at Staale’s post here. 8.0中的一个主要变化是引入了基于InnoDB的数据字典。这一变化使我们能够摆脱基于文件的元数据存储（ FRM 文件），并帮助MySQL转向支持事务DDL。有关在8.0中引入数据字典功能及其优点的更多详细信息，请在此处查看Staale的文章 <链接2> 。 Now that the metadata of all database tables is stored in transactional data dictionary tables, it enables us to design an INFORMATION_SCHEMA table as a database VIEW over the data dictionary tables. This eliminates costs such as the creation of temporary tables for each INFORMATION_SCHEMA query during execution on-the-fly, and also scanning file-system directories to find FRM files. It is also now possible to utilize the full power of the MySQL optimizer to prepare better query execution plans using indexes on data dictionary tables. 既然所有数据库表的元数据都存储在事务数据字典表中，它使我们能够将INFORMATION_SCHEMA表设计为数据字典表上的数据库视图。这消除了成本，例如在执行期间为每个 INFORMATION_SCHEMA 查询创建临时表，以及扫描文件系统目录以查找FRM文件。现在还可以利用MySQL优化器的全部功能，使用数据字典表上的索引来获得更好的执行计划。 The following diagram explains the difference in design in MySQL 5.7 and 8.0. 下面的图解释了MySQL 5.7和8.0设计上的区别 MySQL8.0-INFORMATION_SCHEMA增强 - 文章图片

If we consider the above example under Background, we see that the optimizer plans to use indexes on data dictionary tables, in both the cases. 如果我们在之前介绍的背景下考虑上面的例子，我们会看到优化器在两种情况下都会使用数据字典表上的索引。

mysql > EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA. TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
+ --+-----------+-----++------+------------------+----------++----------------------+----+--------+----------------------------------+
|id|select_type|table ||type |possible_keys |key ||ref |rows|filtered|Extra |
+ --+-----------+-----++------+------------------+----------++----------------------+----+--------+----------------------------------+
| 1|SIMPLE |cat ||index |PRIMARY |name ||NULL | 1| 100. 00|Using index |
| 1|SIMPLE |sch ||eq_ref|PRIMARY,catalog_id|catalog_id || mysql. cat.id,const | 1| 100. 00|Using index |
| 1|SIMPLE |tbl ||eq_ref|schema_id |schema_id || mysql. sch.id,const | 1| 10. 00|Using index condition; Using where|
| 1|SIMPLE |col ||eq_ref|PRIMARY |PRIMARY || mysql. tbl.collation_id| 1| 100. 00|Using index |
+ --+-----------+-----++------+------------------+----------++----------------------+----+--------+----------------------------------+

mysql > EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA. TABLES WHERE TABLE_SCHEMA like 'test%' AND TABLE_NAME like 't%';
+ --+-----------+-----++------+------------------+----------++-----------------------+----+--------+---------------------------------+
|id|select_type|table ||type |possible_keys |key || ref |rows|filtered|Extra |
+ --+-----------+-----++------+------------------+----------++-----------------------+----+--------+---------------------------------+
| 1|SIMPLE |cat ||index |PRIMARY |name || NULL | 1| 100. 00|Using index |
| 1|SIMPLE |sch ||ref |PRIMARY,catalog_id|catalog_id || mysql. cat.id | 6| 16. 67|Using where; Using index |
| 1|SIMPLE |tbl ||ref |schema_id |schema_id || mysql. sch.id | 26| 1. 11|Using index condition;Using where|
| 1|SIMPLE |col ||eq_ref|PRIMARY |PRIMARY || mysql. tbl.collation_id| 1| 100. 00|Using index |
+ --+-----------+-----++------+------------------+----------++-----------------------+----+--------+---------------------------------+

When we look at performance gain with this new INFORMATION_SCHEMA design in 8.0, we see that it is much more efficient than MySQL 5.7. As an example, this query is now ~100 times faster (with 100 databases with 50 tables each). A separate blog will describe more about performance of INFORMATION_SCHEMA in 8.0. 当我们通过这个全新的8.0设计的 INFORMATION_SCHEMA 来看性能提升时，我们发现它比MySQL 5.7更有效。例如，此查询现在快?100倍（100个数据库，每个50个表）。另外一篇博客将详细介绍8.0 中 INFORMATION_SCHEMA 性能。

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT
FROM information_schema. tables
WHERE TABLE_SCHEMA LIKE 'db%';

Sources of Metadata

Not all the INFORMATION_SCHEMA tables are implemented as a VIEW over the data dictionary tables in 8.0. Currently we have the following INFORMATION_SCHEMA tables designed as views: 并非所有 INFORMATION_SCHEMA 表都通过8.0中的数据字典表作为视图实现。目前，我们将以下 INFORMATION_SCHEMA 表设计为视图：

SCHEMATA
TABLES
COLUMNS
VIEWS
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
STATISTICS
KEY_COLUMN_USAGE
TABLE_CONSTRAINTS

Upcoming MySQL 8.0 versions aims to provide even the following INFORMATION_SCHEMA tables as views: 即将推出的MySQL 8.0版本将提供以下 INFORMATION_SCHEMA 表作为视图：

EVENTS
TRIGGERS
ROUTINES
REFERENTIAL_CONSTRAINTS

To describe the INFORMATION_SCHEMA queries which are not directly implemented as VIEWs over data dictionary tables, let me first describe that there are two types of meta data which are presented in INFORMATION_SCHEMA tables: 为了描述INFORMATION_SCHEMA查询，这些查询没有直接实现为数据字典表上的视图，让我首先描述在 INFORMATION_SCHEMA 表中有两种类型的元数据：

Static table metadata. For example: TABLE_SCHEMA , TABLE_NAME , TABLE_TYPE , ENGINE . These statistics will be read directly from the data dictionary.
Dynamic table metadata. For example: AUTO_INCREMENT , AVG_ROW_LENGTH , DATA_FREE . Dynamic metadata frequently changes (for example: the auto_increment value will advance after each insert).In many cases the dynamic metadata will also incur some cost to accurately calculate on demand, and accuracy may not be beneficial for the typical query. Consider the case of the DATA_FREE statistic which shows the number of free bytes in a table – a cached value is usually sufficient.
静态表元数据。例如： TABLE_SCHEMA , TABLE_NAME , TABLE_TYPE , ENGINE 。这些静态数据将会从数据字典中直接读取
动态表元数据。例如： AUTO_INCREMENT , AVG_ROW_LENGTH , DATA_FREE 。动态元数据经常会变更(例如：自增值会在每次插入后自增)。在许多情况下，动态元数据也会产生一些成本，以便按需准确计算，并且对于某些特定的查询这个准确性并没有用。考虑 DATA_FREE 统计信息的情况，该统计信息显示表中的空闲字节数 - 缓存值通常就足够了。

In MySQL 8.0, the dynamic table metadata will default to being cached. This is configurable via the setting information_schema_stats (default cached ), and can be changed to information_schema_stats=latest in order to always retrieve the dynamic information directly from the storage engine (at the cost of slightly higher query execution). 在MySQL 8.0中，动态表元数据将默认为缓存。这可以通过设置 information_schema_stats （默认 缓存 ）进行配置，并且可以更改为 information_schema_stats = latest ，以便始终直接从存储引擎检索动态信息（以稍高的查询执行为代价） As an alternative, the user can also execute ANALYZE TABLE on the table, to update the cached dynamic statistics. 作为替代方案，用户还可以在表上执行 ANALYZE TABLE ，以更新缓存的动态统计信息。
Conclusion
The INFORMATION_SCHEMA design in 8.0 is a step forward enabling:

- Simple and maintainable implementation.
- Us to get rid of numerous INFORMATION_SCHEMA legacy bugs.
- Proper use of the MySQL optimizer for INFORMATION_SCHEMA queries.
- INFORMATION_SCHEMA queries to execute ~100 times faster, compared to 5.7, when retrieving static table metadata, as show in query Q1.

8.0中的 INFORMATION_SCHEMA 设计是向前迈出的一步：

- 简单且可维护的实现。
- 我们摆脱了很多的 INFORMATION_SCHEMA 遗留漏洞。
- 正确使用MySQL优化器进行 INFORMATION_SCHEMA 查询。
- 与检索静态表元数据时的5.7相比， INFORMATION_SCHEMA 查询执行速度快~100倍，如查询Q1中所示。

There is more to discuss about INFORMATION_SCHEMA in 8.0. The new implementation comes with a few changes in behavior when compared to the old INFORMATION_SCHEMA implementation. Please check the MySQL manual for more details about it. Thanks for using MySQL! 在8.0中还有更多关于 INFORMATION_SCHEMA的 讨论。与旧的 INFORMATION_SCHEMA 实现相比，新的实现方式有一些变化。有关它的更多详细信息，请查看MySQL手册。感谢您使用MySQL！

链接1： http://dev.mysql.com/doc/refman/5.7/en/information-schema-optimization.html 链接2： http://mysqlserverteam.com/a-new-data-dictionary-for-mysql/

END

扫码加入MySQL技术Q群

（群号：529671799）

内容总结

以上是互联网集市为您收集整理的MySQL8.0-INFORMATION_SCHEMA增强全部内容，希望文章能够帮你解决MySQL8.0-INFORMATION_SCHEMA增强所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/864733.html

来源：【匿名】

【上一篇】排查mysql的str_to_date查的时间不准，date_format准。【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL8.0-INFORMATION_SCHEMA增强】教程文章相关的互联网学习教程文章

Mysql Row_Format 参数讲解【代码】

今天更改数据引擎的时候,突然出现了Table storage engine for ‘#sql-3e9_132‘ doesn‘t have this option这样的提示:通过搜索,发现了一些端倪,下面是对于Row_Format参数的讲解:在MYSQL中，若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表[static/fixed ]，即该表的row_format是fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。那么实际开发中...

mySQL中information_schema数据库的介绍

information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。...

MySQL的information_schema的介绍

MySQL中information_schema 数据库是干什么的

MySQL中information_schema是什么大家在安装或使用MYSQL时，会发现除了自己安装的数据库以外，还有一个information_schema数据库。 information_schema数据库是做什么用的呢，使用WordPress博客的朋友可能会想，是不是安装模板添加的数据库呀？看完本片文章后，你就会对information_schema数据库有所了解。information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数...

解析MySQL的information_schema数据库

information_schema数据库是在mysql的版本5.0之后产生的，一个虚拟数据库，物理上并不存在。information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）。其中重要的表有：SCHEMATA表---SCHEMATA表提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。TABLES表---TABLES表提供了关于数据库中的表的信息（包括视图...

MySQL INFORMATION_SCHEMA 使用【代码】

-- 查看指定数据库的表信息SET@table_schema=‘employees‘; SELECT table_name,table_type,engine,table_rows,avg_row_length,data_length,index_length,table_collation,create_time FROMinformation_schema.tables WHEREtable_schema =@table_schemaORDERBY table_name; -- 查看会话连接信息SELECT THREAD_ID,name,type,PROCESSLIST_ID,PROCESSLIST_USER ASuser,PROCESSLIST_HOST AS host,PROCESSLIST_DB AS db,PROCESSLIST_CO...

Mysql 日期时间 DATE_FORMAT(date,format)【代码】

本文转自：http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-formatDATE_FORMAT(date,format) Formats the date value according to the format string. The following specifiers may be used in the format string. As of MySQL 3.23, the “%” character is required before format specifier characters. In earlier versions of MySQL, “%” was optional. SpecifierDescription%aAbbre...

pymysql连接提示format: a number is required, not str【代码】【图】

最近想随手写一个简单的员工管理系统，第一次使用python连接数据库，在这个过程中就遇到了一些问题，遂记录遇到问题习惯性百度一下，很多教程都不适合新手，有些还不知道是不是瞎写的，所以我觉得有必要自己写出来。上面的错误就是说这个port或者主机的值应该是非字符串，于是我试着在变量后面加上intport(int)=‘3309‘结果提示：也就是这个语法是错误的，所以只能换一种方法。connect = pymysql.Connect(host=‘localhost‘...

MySql用DATE_FORMAT截取DateTime字段的日期值【代码】

用 DATE_FORMAT 来格式化日期字段 SELECT DATE_FORMAT(crt_time,‘%Y-%m-%d‘) FROM ad_n_advertise_t原文：http://www.jb51.net/article/53293.htm

Mysql Date_format 及时间相关用法【图】

下面的查询选择了所有记录，其date_col的值是在最后30天以内： Sql代码 mysql> SELECT something FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30; DAYOFWEEK(date) 返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。 Sql代码 mysql> select DAYOFWEEK(‘1998-02-03‘); -> 3 WEEKDAY(date) 返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。 Sql代码...

mysql中information_schema.tables字段说明

1. 获取所有表结构(TABLES)SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA=‘数据库名‘; TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。各字段说明如下: 字段含义table_catalog数据表登记目录table_schema数据表所属的数据库名table_name表名称table_type表类型[system view|base table]engine使用的数据库引擎[MyISAM|CSV|InnoDB]ve...

mysql切换数据库提示警告：Reading table information for completion of table and column names【图】

登录数据库后，选择数据库时发现以下提示，mysql> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 意思是预读这个库中表以及表列信息，一般原因是当库中表很多，表中数据很大时，就会出现执行use <库名>后半天没反应，连接很慢的情况，解决办法就是 -A 方式登录数据库，不会预读库中表信息。shell> mysql...

mysql - information_schema.tables视图【代码】

查看information_schema.tables：DESC information_schema.TABLES TABLE_SCHEMA ---->库名 TABLE_NAME ---->表名 ENGINE ---->引擎 TABLE_ROWS ---->表的行数 AVG_ROW_LENGTH ---->表中行的平均行（字节） INDEX_LENGTH ---->索引的占用空间大小（字节）使用案例：查询整个数据库中所有库和所对应的表信息：SELECT table_schema,GROUP_CONCAT(table_name) FROM information_schema.tables GROUP BY tab...