MySQL8.0新特性--新数据字典
一、简介
数据字典(Data Dictionary),存储数据库的元数据信息,如database、table、index、column等。由于MySQL数据库是插件式数据库,分为SERVER层和存储引擎层,存储引擎层可以有多个不同的引擎,SERVER层只有一个,所以只能引擎层配合SERVER层;如早期MySQL默认的引擎是MyISAM,其不属于关系型数据库的引擎,没有数据字典,他的表结构只有.frm文件,所以导致INNODB也必须要有这个文件才能使得SERVER层识别他,这带来了一定的弊端。
二、MySQL 8.0之前的数据字典
8.0之前的数据字典主要分布在SERVER层、mysql库下的系统表、INNODB内部系统表(不同存储引擎都有自己的元数据),信息如下:
SERVER层文件
l .frm:表元数据文件
l .opt:数据库配置文件,每个数据库下都有一个,内容是数据库默认的字符集
l .TRN .TRG:触发器命名文件和配置文件
mysql库下的表
l mysql.user:MySQL用户信息表
l mysql.proc:MySQL存储过程、函数等信息表
l mysql.event:MySQL EVENT信息表
l 等等。
InnoDB系统内部表
l SYS_TABLES:存储所有为INNODB引擎的表信息
l SYS_COLUMNS:存储所有为INNODB引擎的表的列信息
l SYS_INDEXS:存储所有为INNODB引擎的表的索引信息
l SYS_FIELDS:存储所有索引中定义的索引列
l 等等。
这种数据字典架构存在如下问题:
1. 数据字典分散存储,维护管理没有统一接口
2. MyIASM系统表和文件易损坏,没有Crash Safe
3. 无法支持DDL原子性
4. 通过Information_schema查询数据字典时生成临时表,体验不好
三、MySQL 8.0的数据字典
鉴于旧数据字典的各种缺点,MySQL在8.0的版本对数据字典做了较大的改进,首先所有的元数据信息都存在Innodb dictionary table中,并且存储在单独的表空间mysql.idb中,
具体的改进如下:
1、 存储引擎由默认的MyIASM调整为InnoDB
5.7版本中mysql库下的MyIASM表均调整为InnoDB,同时废除了event和proc表。
2、 去掉了.frm、.TRG、.opt等文件,不在通过文件的形式存储数据字典。
3、 数据字典对象缓存,与其他缓存机制一样采用LRU策略。
schema_definition_cache:存储在数据字典缓存中schema定义对象的数量。
stored_program_definition_cache:存储在数据字典缓存中proc和func定义对象的数量。
tablespace_definition_cache:存储在数据字典缓存中存储过程定义的数量
table_definition_cache:存储在数据字典缓存中表定义的数量
4、 information_schema变化
l 部分表名变化
8.0中全部去掉了SYS,同时新增部分表。
l 以前是memory/innodb引擎,现在是试图
查询infomation_schema里面的表时,不在需要生成临时表,而是直接从数据字典获取,优化器可以合理的使用索引。
l 不需要像以前一样扫描文件夹获取数据库列表,不需要打开frm文件获取表信息,而是直接从数据字典表获取
5、 对于InnoDB引擎,支持原子DDL
具体信息见:http://eip.teamshub.com/t/4546132
6、 持久化自增长
MySQL8.0以前自增值没有持久化,重启时通过select MAX(id)的方式获取当前自增值,这种方式自增值可能存在重复利用。MySQL8.0开始支持自增值持久化,通过增加redo日志和Data Dictonary 表mysql.innodb_dynamic_metadata来实现持久化;每次insert/update更新自增值时会写到redo日志中,checkpoint时将自增值更新到mysql.innodb_dynamic_metadata,crash重启时,先从mysql.innodb_dynamic_metadata获取持久化的自增值,再从redo日志中读取最新的自增值。
四、8.0使用上的一些注意事项
1、 innodb_read_only 影响范围
8.0以前启动innodb_read_only只会阻止innodb引擎创建或者删除表;8.0后将对所有引擎生效,因为数据字典表是innodb引擎。同样的想ANALYZE TABLE和ALTER TABLE XXX ENGINE=XXXX也会失败,因为这些操作也需要更新数据字典。
2、 mysqldump/mysqlpump
l 不再备份information_schema数据库,即使显示命令指定;因为该库的表都是数据字典的试图。
l 只备份mysql库中非数据字典的表。
l 要导出存储过程、触发器等,需要添加--routines和--events选项;8.0以前--all-databases不需要指定也会导出存储过程、EVENT等,因为这信息存储在mysql.proc、mysql.event中,8.0已删除这两张表。
l 以前--routines需要SELECT mysql.proc的权限,8.0需要全部表的SELECT权限。
l 之前版本中,导出触发器、存储过程可以同时导出触发器、存储过程的创建和修改的时间戳,8.0中不再支持。
3、数据字典表不可见
之前mysql系统数据库里面的表是对DDL和DML可见的,MySQL8.0的数据字典表是不可见的,无法直接对其进行查询和修改,但是可以通过查询INFORMATION_SCHEMA来替代。
也可以用debug模式启动数据库,执行SET SESSION debug='+d,skip_dd_table_access_check';然后可以查询mysql库下的数据字典表。
4、表统计信息变化
表的统计信息保存在 STATISTICS 和 TABLE 表里面,为了提高INFORMATION_SCHEMA的性能,这两个表里面的内容被放入了缓存中,放入缓存中的数据时限可以通过系统变量information_schema_stats_expiry来设置。与之前直接查询获得当前统计信息不同,现在需要执行ANALYZE TABLE来获取最新统计信息,或者将information_schema_stats_expiry设置为0。
5、CREATE TABLE LIKE变化
CREATE TABLE dst_tbl LIKE src_tbl,如果源表是基于数据字典表的INFORMATION_SCHEMA视图,执行将会报错。
五、非InnoDB引擎表处理方式
MySQL8.0不仅将元数据信息存储在数据字典表中,同时也冗余存储了一份在SDI中。对于非InnoDB表,SDI数据在后缀为.sdi的文件中,而对于innodb,SDI数据则直接存储与ibd中。
如mysql库中的slow_log为CSV的引擎,在data/mysql目录中存在slow_log_202.sdi
以slow_log_202.sdi文件为例,202为table id,使用json格式化一下可以看到更多的元数据信息:
对于InnoDB引擎的表,则可以使用8.0推出的ibd2sdi工具解析,如:
idb2sdi工具还可以解析mysql.idb,可以查看mysql库下所有的表,包括不可见的数据字典表等信息:
参考:
https://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html
https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-object-cache.html
https://lefred.be/content/mysql-8-0-data-dictionary-tables-and-why-they-should-stay-protected/