如何利用MySQL数据字典优化数据库管理？

MySQL 数据字典是数据库中关于表、列、索引等对象的描述性信息集合，用于存储和管理数据库结构元数据。

MySQL 8.0 对数据字典进行了重大改进，将所有元数据信息集中存储在 InnoDB 系统表中，这一改进带来了许多优势，包括改善性能、统一管理、支持原子性 DDL 和事务安全等。

MySQL 8.0 之前的数据字典

在 MySQL 8.0 之前，数据字典的信息分布较为分散，主要存在于以下几处：

位置	文件类型
Server 层	.frm 文件	表结构信息。
MyISAM 系统表	.par 文件	分区定义文件（MySQL 5.7 版本后停止使用）。
InnoDB 内部系统表	.TRN 文件	触发器命名空间文件。
	.TRG 文件	触发器参数文件。
	.isl 文件	InnoDB 符号链接文件。
	.db.opt 文件	数据库配置文件（包含库的默认字符集属性）。
mysql 库下的系统表	mysql.user, mysql.db	用户、数据库等系统信息。
InnoDB 内部系统表	SYS_DATAFILES, SYS_FOREIGN, SYS_TABLESPACES	数据文件、外键等信息。

这些分散的存储方式导致了一些问题，例如维护管理不统一、MyISAM 系统表易损坏、DDL 没有原子性、扩展性差以及通过 INFORMATION_SCHEMA 查询时生成临时表不友好。

MySQL 8.0 的数据字典改进

1. 集中存储元数据

MySQL 8.0 将所有元数据信息集中存储在 InnoDB 系统表中，不再使用旧的文件存储方式，具体变化如下：

原文件类型	新存储方式	备注
.frm 文件	InnoDB 表`dd_properties`	存储表结构信息。
.par 文件	InnoDB 表`table_partitions`	存储分区信息。
.TRN 文件	InnoDB 表`triggers`	存储触发器信息。
.TRG 文件	InnoDB 表`trigger_parameter`	存储触发器参数信息。
.isl 文件	InnoDB 表`file_name_charset`	存储文件名和字符集信息。
.db.opt 文件	InnoDB 表`collations`	存储数据库字符集和校对规则信息。
MyISAM 系统表	InnoDB 表`columns`,`indexes`	存储字段和索引信息。
InnoDB 内部系统表	InnoDB 表`innodb_dynamic_metadata`	存储动态元数据。

2. 性能和管理优势

新的数据字典架构带来了多方面的优势：

1、改善 INFORMATION_SCHEMA 性能：由于 INFORMATION_SCHEMA 的部分表通过视图实现，服务器无需为每次查询创建临时表，从而降低了磁盘 I/O 开销。

2、统一管理：所有元数据集中存储在 InnoDB 表中，避免了不同存储引擎之间的数据同步问题，提高了可扩展性和管理效率。

3、事务性和崩溃安全：数据字典保存在具有完整事务功能的 InnoDB 表中，确保了原子性的 DDL 操作和崩溃安全性。

4、缓存优化：为了减少磁盘 I/O，数据字典对象会被缓存以便重复利用，采用 LRU（最近最少使用）策略进行管理。

FAQs

Q1: 为什么 MySQL 8.0 选择将数据字典全部迁移到 InnoDB 表中？

A1: MySQL 8.0 选择将数据字典全部迁移到 InnoDB 表中，主要是为了解决之前版本中数据字典分散存储带来的维护复杂、性能低下、扩展性差等问题，通过集中存储在支持事务的 InnoDB 表中，可以实现更高效的管理和更好的性能，同时提供原子性 DDL 和崩溃安全性。

Q2: 如何查看 MySQL 8.0 中的数据字典表？

A2: 在默认情况下，数据字典表是受系统保护的，无法直接访问，但可以通过设置调试选项来查看：

SET SESSION debug='+d,skip_dd_table_access_check';
SELECT name FROM mysql.tables WHERE hidden='System' AND type='BASE TABLE';

这将显示数据字典中的所有表名。

原创文章，作者：未希，如若转载，请注明出处：https://www.kdun.com/ask/1108177.html

本网站发布或转载的文章及图片均来自网络，其原创性以及文中表达的观点和判断不代表本网站。如有问题，请联系客服处理。