MySQL8.0之数据字典

1、MySQL8.0之数据字典一、 简介及原理MySQL 8.0 将数据库元信息都存放于InnoDB存储引擎表中，在之前版本的MySQL中，数据字典不仅仅存放于特定的存储引擎表中，还存放于元数据文件、非事务性存储引擎表中。本文将会介绍MySQL 8.0对数据字典的改进，以及改进带来的好处、影响以及局限性。1、什么是数据字典？用来存储元数据信息的表。比如表的描述、字段、对象、对象之间的关系。InnoDB中大多以系统表的形式保存。主要包括sys_tables,sys_columns,sys_indexs,sys_fields sys_table存储表的信息，包括表面，ID，表空间号 sys_colum

2、ns存储表中列的信息，包括列名，列ID，列的序号，列的类型，长度等信息 sys_index存储表的索引信息，包括索引名，索引对应的表空间，表ID，索引ID,索引类型 sys_fields存储索引中定义的索引列2、InnoDB数据字典启动过程1）innobase_start_or_create_for_mysql函数调用dict_create()函数进行数据字典的创建和加载工作。2）dict_hdr_create完成系统表空间第7号页面dict header的初始化及创建SYS_TABLES两个索引、SYS_COLUMNS一个索引、SYS_INDEXES一个索引、SYS_FIELDS一个索引，其

3、创建索引的函数是btr_create。3）创建B+树索引后，通过dict_boot函数加载常驻内存的4个系统表。具体流程见流程图的部分。4）加载完成后，将这4个系统表挂在一个全局字典中： dict0dict.h:/* Dictionary system struct */ struct dict_sys_t ib_mutex_t mutex; /*!< mutex protecting the data dictionary; protects also the disk-based dictionary system tables; this mutex serializes CREA

4、TE TABLE and DROP TABLE, as well as reading the dictionary data for a table from system tables */ row_id_t row_id; /*!< the next row id to assign; NOTE that at a checkpoint this must be written to the dict system header and flushed to a file; in recovery this must be derived from the log records

5、*/ hash_table_t* table_hash; /*!< hash table of the tables, based on name */ hash_table_t* table_id_hash; /*!< hash table of the tables, based on id */ ulint size; /*!< varying space in bytes occupied by the data dictionary table and index objects */ dict_table_t* sys_tables; /*!< SYS_TA

6、BLES table */ dict_table_t* sys_columns; /*!< SYS_COLUMNS table */ dict_table_t* sys_indexes; /*!< SYS_INDEXES table */ dict_table_t* sys_fields; /*!< SYS_FIELDS table */ /*=*/ UT_LIST_BASE_NODE_T(dict_table_t) table_LRU; /*!< List of tables that can be evicted from the cache */ UT_LIST_

7、BASE_NODE_T(dict_table_t) table_non_LRU; /*!< List of tables that can't be evicted from the cache */ ;结构体中sys_tables、sys_columns、sys_indexes、sys_fields四个结构存储上述对应的4个系统表。结构体中HASH表及链表用来存储InnoDB中的所有表的缓存，包括系统表及用户表。table_hash哈希表按名字缓存，table_id_hash按表ID进行hash，LRU链表用来管理表对象缓存。5）普通用户表加载流程见流程图的、部分。当用户访问一

8、个用户表时，首先需要从表对象缓存中查找这个表的SHARE对象，如果找到则直接从其实例化表对象链表中拿一个使用；如果没有找到，则需要重新打开这个表，需要找到这个表的字典信息。即的流程。具体加载一个表的字典是流程，dict_load_table的工作。a)首先需要找到SYS_TABLES表，也是先找缓存，缓存找不到再从系统表加载： dict_table_get_lowb)找到之后构建一个查询键值，从SYS_TABLES的name主键索引进行查询，如果诶呦找到或者该记录已经被删除则返回，否则解析找到的这条记录。然后根据这些信息创建表的内存对象table。c)加载列操作与加载表的原理基本一样，对应系统

9、表的SYS_COLUMNS，聚集索引为（TABLE_ID,POS），查找时，如果TABLE_ID相同，在POS从小到大排序，所以构造所有列的键值时，只需要通过TABLE_ID查询即可，按顺序取出所有列信息一一构造内存对象。d)加载索引信息类似的流程二、 数据字典（1）新版本之前的数据字典数据字典是数据库重要的组成部分之一，那么什么是数据字典？数据字典包含哪些内容呢？数据字典是对数据库中的数据、库对象、表对象等的元信息的集合。在MySQL中，数据字典信息内容就包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容。MySQL INFORMATION_SCHEM

10、A库提供了对数据局元数据、统计信息、以及有关MySQL server的访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典。 在MySQL8.0之前，MySQL的数据字典信息，并没有全部存放在系统数据库表中，部分数据库数据字典信息存放于文件中，其余的数据字典信息存放于数据字典库中（INFORMATION_SCHEMA,mysql,sys）。例如表结构信息存放在.frm文件中,数据库表字段信息存放于INFORMATION_SCHEMA下的COLUMNS表中。早期，5.6版本之前，MyISAM是MySQL的默认存储引擎，而作为MyISAM存储

11、引擎，它是没有数据字典的。只有表结构信息记录在.frm文件中。MySQL5.6版本之后，将InnoDB存储引擎作为默认的存储引擎。在InnoDB存储引擎中，添加了一些数据字典文件用于存放数据字典元信息，例如：.opt文件，记录了每个库的一些基本信息，包括库的字符集等信息，.TRN，.TRG文件用于存放触发器的信息内容。（2）新版本数据字典的改进最新的MySQL 8.0 发布之后，对数据库数据字典方面做了较大的改进。首先是，将所有原先存放于数据字典文件中的信息，全部存放到数据库系统表中，即将之前版本的.frm,.opt,.par,.TRN,.TRG,.isl文件都移除了，不再通过文件的方式存储数

12、据字典信息。其次是对INFORMATION_SCHEM，mysql，sys系统库中的存储引擎做了改进，原先使用MyISAM存储引擎的数据字典表都改为使用InnoDB存储引擎存放。从不支持事务的MyISAM存储引擎转变到支持事务的InnoDB存储引擎，为原子DDL的实现，提供了可能性。三、 新数据字典带来的影响（1）INFORMATION_SCHEMA性能提升8.0中对数据字典进行改进之后，很大程度上提高了对INFORMATIONS_SCHEMA的查询性能，通过可以通过查表快速的获得想要查询的数据，原因是：数据库在查询INFORMATION_SCHEMA的表时，不再一定需要创建一张临时表，可以直

13、接查询数据字典表。在之前版本中，数据字典信息不一定是存放于表中，所以在获取数据字典信息时候，不仅仅是查表操作。例如读取数据库表结构信息，底层其实是读取.frm文件来获得，是一个文件打开读取的操作。而在新版本中，数据字典信息都可以通过直接查表的方式获取，替代那些获取信息慢的方式。对存储引擎的改进之后，在查询INFORMATIONS_SCHEMA表时，如果表上有索引，优化器会合理的利用索引。对于INFORMATION_SCHEMA下的STATISTICS表和TABLES表中的信息，8.0中通过缓存的方式，以提高查询的性能。可以通过设置information_schema_stats_expiry参

14、数设置缓存数据的过期时间，默认是86400秒。查询这两张表的数据的时候，首先是到缓存中进行查询，缓存中没有缓存数据，或者缓存数据过期了，查询会从存储引擎中获取最新的数据。如果需要获取最新的数据，可以通过设置information_schema_stats_expiry参数为0或者ANALYZE TABLE操作。（2）原子DDLMySQL8.0开始支持原子DDL操作，一个原子DDL操作，具体的操作内容包括：数据字典更新，存储引擎层的操作，在binlog中记录DDL操作。并且这些操作都是原子性的，表示中间过程出现错误的时候，是可以完整回退的。这在之前版本的DDL操作中是不支持的。之前数据库版本中一

15、直没有支持原子DDL的特性，是有原因的，因为在早期的数据库版本中，数据库元信息存放于元信息文件中、非事务性表中以及特定存储引擎的数据字典中。这些都无法保证DDL操作内容在一个事务当中，无法保证原子性。 具体的原子DDL，后续会有专门的文章。（3）innodb_read_only对所有存储引擎生效在8.0之前版本中，innodb_read_only参数可以阻止对InnoDB存储引擎表的create和drop等更新操作。但是在MySQL8.0中，开启innodb_read_only参数阻止了所有存储引擎的这些操作。create或者drop表的操作都需要更新数据字典表，8.0中这个数据字典表都改为了

16、InnoDB存储引擎，所以对于数据字典表的更新会失败，从而导致各存储引擎create和drop表失败。同样的像ANALYZE TABLE和ALTER TABLE tbl_name ENGINE=engine_name这种操作也会失败，因为这些操作都要去更新数据字典表。（4）mysqldump mysqlpump导出的内容影响MySQL8.0之后，在使用mysqldump和mysqlpump导出数据时候，与之前有了一些不同，主要是以下几点：之前版本的mysqldump和mysqlpump可以导出mysql系统库中的所有表的内容，8.0之后，只能导出mysql系统库中没有数据的数据字典表。之前版本

17、当使用 -all-databases 参数导出数据的时候，不加 -routines和 -events选项也可以导出触发器、存储过程等信息，因为这些信息都存放于proc和event表中，导出所有表即可导出这些信息。但是在8.0中，proc表和event表都不再使用，并且定义触发器、存储过程的数据字典表不会被导出，所以在8.0中使用mysqldump、mysqlpump导出数据的时候，如果需要导出触发器、存储过程等内容，一定需要加上 -routines和 -events选项。之前版本中 -routines选项导出的时候，备份账户需要有proc表的SELECT权限，在8.0中需要对所有表的SELEC

18、T权限之前版本中，导出触发器、存储过程可以同时导出触发器、存储过程的创建和修改的时间戳，8.0中不再支持。（5）新数据字典的局限性MySQL8.0数据字典的改进有很多方便的特性，例如带来了原子DDL，提升了INFORMATION_SCHEMA的查询性能等，但是它并不是完美的，新版数据字典还是存在一些局限性：通过手动mkdir的方式在数据目录下创建库目录，这种方式是不会被数据库所识别到。DDL操作会花费更长的时间，因为之前的DDL操作是直接对.frm文件进行更改操作，只要写一个文件，现在是需要更新数据字典表，代表着需要将数据写到存储引擎、read log、undo log中。四、 总结目前已经正

19、式GA的MySQL 8.0是令人很期待的一个版本，从数据字典方面的改进，到原子DDL，到数据库self tuning等等新特性，都让人为8.0感到激动。8.0中有许多新特性等待去尝试，去发现。五、mysql查看数据字典查看数据库：show databases;查看表：show tables;查看表的所有列：show columns from user; 同 describe user;查看表的索引：show indexs from user;查看当前数据库：select database();查看当前用户：select user();查看数据库变量：show global|session va