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手册。

MySQL8.0-INFORMATION_SCHEMA增强的中文翻译

标签：man   static   很多   mysql8   issue   jpg   use   临时表   clu