mysql数据字典生成器_MySQL 8.0 数据字典有哪些变化？

原文链接：https://www.modb.pro/db/25548

摘要：MySQL8.0 数据字典(Data Dictionary)也在进化中。MyISAM系统表全部换成InnoDB表 ，支持原子DDL。复杂度增加了。 考虑过是否跟业务数据库有资源抢夺的现象，这些都是实际使用中需要观察关注的问题。

1.数据字典

MySQL中数据字典是数据库重要的组成部分之一，包含表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等对象的基础信息。MySQL INFORMATION_SCHEMA库提供了对数据局元数据、统计信息、以及有关MySQL server的访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。

1.1. 架构对比

MySQL8.0之前和MySQL8.0 数据字典的区别

备注：
.frm文件 存放表结构信息
.opt文件，记录了每个库的一些基本信息，包括库的字符集等信息
.TRN，.TRG文件用于存放触发器的信息内容。

备注：原先存放于数据字典文件中的信息，全部存放到数据库系统表中，即将之前版本的.frm,.opt,.par,.TRN,.TRG,.isl文件进行了移除.
对mysql，sys系统库中的存储引擎做了改进，原使用MyISAM存储引擎的数据字典表都改为使用InnoDB存储引擎存放。从不支持事务的MyISAM存储引擎转变到支持事务的InnoDB存储引擎，典数据由相同的提交、回滚和崩溃恢复功能保护，为原子DDL的实现，提供了可能性。

1.2. 引擎变化

##5.7.30

mysql> SELECT TABLE_SCHEMA ,ENGINE ,COUNT(*) from information_schema.tables where table_schema in ('information_schema' ,'mysql','performance_schema', 'sys') group by TABLE_SCHEMA ,ENGINE; +--------------------+--------------------+----------+ | TABLE_SCHEMA | ENGINE | COUNT(*) | +--------------------+--------------------+----------+ | information_schema | InnoDB | 10 | | information_schema | MEMORY | 51 | | mysql | CSV | 2 | | mysql | InnoDB | 20 | | mysql | MyISAM | 11 | | performance_schema | PERFORMANCE_SCHEMA | 87 | | sys | NULL | 100 | | sys | InnoDB | 1 | +--------------------+--------------------+----------+ 8 rows in set (0.01 sec) ##

8.0.20

mysql> SELECT TABLE_SCHEMA ,ENGINE ,COUNT(*) from information_schema.tables where table_schema in ('information_schema' ,'mysql','performance_schema', 'sys') group by TABLE_SCHEMA ,ENGINE; +--------------------+--------------------+----------+ | TABLE_SCHEMA | ENGINE | COUNT(*) | +--------------------+--------------------+----------+ | mysql | InnoDB | 31 | | information_schema | NULL | 73 | | performance_schema | PERFORMANCE_SCHEMA | 106 | | mysql | CSV | 2 | | sys | NULL | 100 | | sys | InnoDB | 1 | +--------------------+--------------------+----------+ 6 rows in set (0.03 sec)
备注：sys.NULL:x$开头的表 ，主要是VIEW 如：x$waits_global_by_latency，x$user_summary_by_stages
information_schema 里也有 TEMPORARY TABLE ,VIEW等等 对比5.7统计的准确度差很多。但确定myisam表是没有。

1.3. 文件结构

MySQL5.7版本:frm ,MYD,MYI文件

MySQL8.0.20 存储在单独的表空间mysql.ibd

以后要mysql.ibd的重要性。需要定期做好备份。还要看看用什么办法解读ibd文件

1.4. 新缓存
INFORMATION_SCHEMA.STATISTICS统计索引的信息:
8.0中通过缓存的方式，以提高查询的性能。information_schema_stats_expiry系统变量定义了缓存的表统计信息过期之前的一段时间。默认时间是86400秒(24小时)。如果没有缓存的统计信息或统计信息已过期，那么直接查询表存储引擎。可以使用ANALYZE table 或information_schema_stats_expiry=0保持数据在缓存中.
definition缓存:
mysql> SHOW VARIABLES LIKE '%definition%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | schema_definition_cache | 256 | | stored_program_definition_cache | 256 | | table_definition_cache | 3000 | | tablespace_definition_cache | 256 | +---------------------------------+-------+

• table_definition_cache：存储表定义数量（5.7就存在）
• schema_definition_cache：存储schema定义数量
• stored_program_definition_cache：存储proc和func定义数量
• tablespace_definition_cache：存储tablespace定义数量
  备注：多了内存开销。

1.5. 使用索引
information_schem,mysql字典表可以使用索引。
5.7explain 分析

8.0 explain 分析

1.6. 表变动
mysql.fun mysql.proc 迁移到 information_schema.ROUTINES
1.7. sdi文件
MySQL8.0不仅将元数据信息存储在数据字典表中，对于非InnoDB表，同时冗余存储在后缀为.sdi的文件中

2.原子DDL

MySQL 8.0支持原子数据定义语言(DDL)语句。这个特性称为原子DDL。原子DDL语句将与DDL操作关联的数据字典更新、存储引擎操作和二进制日志写入组合到单个原子操作中。操作要么提交，对数据字典、存储引擎和二进制日志进行持久的更改，要么回滚。
为了支持DDL操作的重做和回滚，InnoDB将DDL日志写到mysql.innodb_ddl_log表，它是一个隐藏的数据字典表，驻留在mysql中。ibd数据字典表空间。
目前，只有InnoDB存储引擎支持原子DDL
原子DDL 操作步骤:
1.准备：创建所需的对象并将DDL日志写入 mysql.innodb_ddl_log表中。DDL日志定义了如何前滚和回滚DDL操作。
2.执行：执行DDL操作。例如，为CREATE TABLE操作执行创建。
3.提交：更新数据字典并提交数据字典事务。
4.Post-DDL：重播并从mysql.innodb_ddl_log表格中删除DDL日志。为确保回滚可以安全执行而不引入不一致性，在此最后阶段执行文件操作（如重命名或删除数据文件）。这一阶段还从 mysql.innodb_dynamic_metadata的数据字典表删除的动态元数据为了DROP TABLE，TRUNCATE和其它重建表的DDL操作。
按照官方提供的方式模拟一下：
msyql> SET GLOBAL LOG_ERROR_VERBOSITY=3; mysql> SET GLOBAL innodb_print_ddl_logs=1; mysql> CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;

加载说明：https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html
原子DDL那些情况能用到：
支持：

• DDL： CREATE, ALTER, DROP 对象是：databases, tablespaces, tables, indexes,
  甚至TRUNCATE TABLE
• non-table DDL： CREATE DROP , ALTER ：对象是：triggers, views, and user-defined functions (UDFs).
• 账户管理: CREATE, ALTER, DROP, RENAME 对象： users ， roles, GRANT REVOKE

不支持：

• 不是InnoDB引擎 的DDL语句
• 插件命令：INSTALL PLUGIN and UNINSTALL PLUGIN statements.
• 组件命令：INSTALL COMPONENT and UNINSTALL COMPONENT statements.
• 服务命令CREATE SERVER, ALTER SERVER, and DROP SERVER statements.

3.其他

3.1. 小版本升级
数据字典表成功升级之后，不可能使用旧的服务器二进制文件重新启动服务器。因此，在升级数据字典表之后，不支持将MySQL服务器二进制文件降级为以前的MySQL版本
使用mysqld——no-dd-upgrade选项来防止在启动时自动升级数据字典表。如果指定了——no-dd-upgrade，并且服务器发现服务器的数据字典版本与存储在数据字典中的版本不同，则启动失败，并出现一个错误，表示禁止数据字典升级。

3.2. Debug
有些元数据表访问会提示如下提示：

可通过Mysql源码编译 -DWITH_DEBUG=1 CMake 查看一些隐藏的表的信息和数据
mysql> SET SESSION debug='+d,skip_dd_table_access_check'; mysql> SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';

3.3. innodb_read_only
启用innodb_read_only系统变量可以防止只为InnoDB存储引擎创建和删除表。从MySQL 8.0开始，启用innodb_read_only可以防止对所有存储引擎进行这些操作。表创建和删除操作的任何存储引擎修改mysql系统数据库中的数据字典表，但这些表使用InnoDB存储引擎，不能在innodb_read_only启用时进行修改。同样的原则也适用于需要修改数据字典表的其他表操作.

3.4. mysqldump mysqlpump导出的内容影响
8.0版本开始all-databases参数导出数据的时候，必须要加–routines和–events选项，才可以导出触发器、存储过程等信息。
权限方向 –routines选项导出的时候，备份账户需要有proc表的SELECT权限，在8.0中需要对所有表的SELECT权限。

3.5. 限制
不支持在data目录下手动创建数据库目录(例如，使用mkdir)。MySQL服务器无法识别手动创建的数据库目录

4.总结

需要考虑的点：
MySQL8.0数据字典的改进有很多方便的特性，例如带来了原子DDL，提升了INFORMATION_SCHEMA的查询性能等，但是它并不是完美的，新版数据字典还是存在一些局限性：
DDL操作会花费更长的时间，因为之前的DDL操作是直接对.frm文件进行更改操作，只要写一个文件，现在是需要更新数据字典表，代表着需要将数据写到存储引擎、read log、undo log中。
参考：https://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html