当前位置:   article > 正文

【MySQL】 MySQL 8.0 新特性之数据字典_mysql数据字典

mysql数据字典

MySQL Server 8.0 合并了一个全局的事务数据字典,用于存储有关数据库对象的信息,也有了原子DDL功能。在 MySQL 以前的版本中,字典数据存储在元数据文件、非事务性表和特定于存储引擎的数据字典中。

在学习 MySQL 数据字典之前,先一起了解一下什么是数据字典元数据

  • 数据字典就类似于系统编目或花名册,它保存数据库服务器上的元数据信息(数据库的整体属性信息)。

  • 元数据信息包括:数据库的属性信息、数据表的属性信息、字段的属性信息、视图的属性信息、用户信息、统计类信息等。

1. 数据字典

数据字典表是受保护的,只有在 MySQL 的 debug 版本中才能访问。但是,MySQL 支持通过 INFORMATION_SCHEMA 表和 SHOW 命令来访问存储在数据字典表中的数据。

MySQL 系统表在 MySQL 8.0 中仍然存在,可以通过 SHOW TABLES 语句在 MySQL 系统数据库来查看。MySQL 数据字典表和系统表的区别在于,数据字典表包含执行SQL查询所需的元数据,而系统表包含时区、帮助信息等辅助数据。

1.1 数据字典如何升级

MySQL 数据字典升级通常是随着整个MySQL服务器版本的升级而自动完成的一部分过程。数据字典是MySQL内部用来存储关于数据库对象(如表、列、索引等)的元数据的地方,特别是在MySQL 8.0以后引入了改进的数据字典体系结构,使得管理更加可靠和高效。

以下是MySQL数据字典升级的一般步骤:

  1. 选择合适的升级路径:

    • 当您从一个低版本(如5.7)升级到MySQL 8.0及以上版本时,首先需要确定这是一个支持的升级路径,并遵循官方提供的指南。
  2. 备份数据:

    • 在进行任何重大升级之前,务必先完整备份您的数据库,包括数据文件和相关的配置文件。
  3. 关闭MySQL服务:

    • 卸载旧版本的MySQL服务,并停止运行的所有MySQL实例。
  4. 安装新版本的MySQL:

    • 下载并安装符合您操作系统的MySQL最新稳定版二进制包或源代码并进行编译安装。
  5. 升级数据字典:

    • 启动新版本的MySQL服务后,系统会自动检测到现有数据目录,并在启动过程中更新数据字典和其他内部元数据表,这通常由mysql_upgrade工具来完成。
 sudo mysql_upgrade
  • 1
  1. 验证升级结果:

    • 升级完成后,检查MySQL服务器日志以确认升级成功,并验证数据库和表的功能正常。
  2. 解决兼容性问题:

    • 根据MySQL官方文档提示,处理任何因新版本带来的语法变更、功能弃用或其他可能影响现有应用程序的兼容性问题。
      请注意,对于大规模生产环境下的MySQL升级,还需要考虑更多因素,例如停机窗口、滚动升级策略以及在升级过程中对应用程序连接行为的影响等。

1.2 使用 MySQL 的 debug 版本查看数据字典表

数据字典表是受保护的,只有在 MySQL 的 debug 版本中才能访问。编译 debug 版本参考:Compiling MySQL for Debugging

不建议直接修改或写入数据字典表,这可能会导致您的 MySQL 实例无法运行。

  • 使用调试支持编译 MySQL 后,使用此 SET 语句使数据字典表对mysql客户端会话可见:
mysql> SET SESSION debug='+d,skip_dd_table_access_check';
  • 1

在这里插入图片描述

  • 使用此查询检索数据字典表:
mysql> SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';
  • 1

  • 用于 SHOW CREATE TABLE 查看数据字典表定义。例如:
mysql> SHOW CREATE TABLE mysql.catalogs\G
  • 1

在这里插入图片描述

2. 删除了基于文件的元数据存储

在以前的 MySQL 版本中,字典数据部分存储在元数据文件中。基于文件的元数据存储有以下问题:

  • 昂贵的文件扫描成本;
  • 对文件系统相关错误的敏感性太低;
  • 会有一大堆处理复制和容灾故障恢复的复杂代码;
  • 缺乏可扩展性导致难以为新功能和关系对象添加元数据。

下面列出已从 MySQL 中删除的元数据文件。除非另有说明,以前存储在元数据文件中的数据现在存储在数据字典表中。

  • .frm 文件:表定义文件,是描述数据表结构的文件。、

  • .par 文件:分区定义文件。 InnoDB 引入了对 InnoDB 表的本机分区支持后,在 MySQL 5.7 中停止使用分区定义文件

  • TRN 文件:触发命名空间文件。

  • .TRG 文件:触发参数文件。

  • .isl 文件:InnoDB 符号链接文件,包含在数据目录之外创建的 file-per-table 表空间文件的位置。

  • db.opt 文件:数据库配置文件。这些文件(每个数据库目录一个)包含数据库默认字符集属性。

  • ddl_log.log文件:该文件包含由数据定义语句(例如:DROP TABLE 和ALTER TABLE)生成的元数据操作的记录。

3. 字典数据的事务存储

数据字典模式将字典数据存储在事务 ( InnoDB) 表中。数据字典表与非数据字典系统表一起位于 MySQL 数据库中。

数据字典表是在一个名为 mysql.ibd 的 InnoDB 表空间中创建的,它位于 MySQL 数据目录中。mysql.ibd 表空间文件必须位于 MySQL 数据目录下,并且其名称不能被其他表空间修改或使用。

字典数据受提交、回滚和崩溃恢复功能的保护,与保护存储在InnoDB表中的用户数据相同。

4. 数据字典对象缓存

字典对象缓存是一个共享的全局缓存,它将以前访问的数据字典对象存储在内存中,以支持对象重用和最小化磁盘I/O。与 MySQL 使用的其他缓存机制类似,字典对象缓存使用基于lru的驱逐策略从内存中驱逐最近最少使用的对象。

mysql> show variables like '%definition%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| schema_definition_cache         | 256   |
| stored_program_definition_cache | 256   |
| table_definition_cache          | 2000  |
| tablespace_definition_cache     | 256   |
+---------------------------------+-------+
4 rows in set (0.01 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

字典对象缓存包括存储不同对象类型的缓存分区。一些缓存分区大小限制是可配置的,而另一些则是硬编码的。

  • tablespace_definition_cache - 表空间定义缓存分区:存储表空间定义对象,默认值为 256。

  • schema_definition_cache - 架构定义缓存分区:存储架构定义对象,默认值为 256。

  • table_definition_cache - 表定义缓存分区,存储表定义对象。

  • stored_program_definition_cache - 存储程序定义缓存分区:存储存储程序定义对象,默认值为 256。

  • 字符集定义缓存分区:存储字符集定义对象,硬编码对象限制为 256。

  • 归类定义缓存分区:存储归类定义对象,硬编码对象限制为 256。

5. INFORMATION_SCHEMA和数据字典集成

随着数据字典的引入,一些 INFORMATION_SCHEMA 表被实现为数据字典表的视图,对这些表的查询现在效率更高,因为它们从数据字典表获得信息,而不是通过其他较慢的方法。

5.1 查询优化

MySQL 8.0 数据字典存放在表中,对他们进行查询时,可以充分利用 SQL 语句的特性,如索引、连接等。而 MySQL 8.0 之前,需要从文件中读取数据。

MySQL 5.7

mysql> explain select * from information_schema.tables where table_schema = 'sbtest';
+----+-------------+--------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                            |
+----+-------------+--------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | tables | NULL       | ALL  | NULL          | TABLE_SCHEMA | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned 1 database |
+----+-------------+--------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
-----------------------------------
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

MySQL 8.0

mysql> explain select * from information_schema.tables where table_schema = 'sbtest';
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys      | key        | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | cat   | NULL       | index  | PRIMARY            | name       | 194     | NULL                    |    1 |   100.00 | Using index |
|  1 | SIMPLE      | sch   | NULL       | eq_ref | PRIMARY,catalog_id | catalog_id | 202     | mysql.cat.id,const      |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | tbl   | NULL       | ref    | schema_id          | schema_id  | 8       | mysql.sch.id            |   92 |   100.00 | Using where |
|  1 | SIMPLE      | col   | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.collation_id  |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | ts    | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.tablespace_id |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | stat  | NULL       | eq_ref | PRIMARY            | PRIMARY    | 388     | const,mysql.tbl.name    |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
6 rows in set, 1 warning (0.73 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/不正经/article/detail/628240
推荐阅读
相关标签
  

闽ICP备14008679号