MySQL 8.0 中 4 个默认的系统数据库

最新推荐文章于 2023-08-24 17:37:07 发布

不剪发的Tony老师

最新推荐文章于 2023-08-24 17:37:07 发布

阅读量8.9k

点赞数 8

分类专栏： MySQL 文章标签： mysql 数据字典表性能数据库 sys

本文链接：https://blog.csdn.net/horses/article/details/106667755

版权

MySQL 专栏收录该内容

55 篇文章 73 订阅

订阅专栏

文章目录

大家好，我是只谈技术不剪发的 Tony 老师。今天给大家介绍一下 MySQL 8.0 中默认安装的几个系统数据库/模式。

当我们安装 MySQL 8.0 并初始化数据库之后，默认会创建以下系统数据库：

mysql，存储了 MySQL 服务器正常运行所需的各种信息。
information_schema，提供了访问数据库元数据的各种视图，包括数据库、表、字段类型以及访问权限等。
performance_schema，为 MySQL 服务器的运行时状态提供了一个底层的监控功能。
sys，包含了一系列方便 DBA 和开发人员利用 performance_schema 性能数据库进行性能调优和诊断的视图。

使用SHOW DATABASES或者SHOW SCHEMAS命令查看所有的数据库/模式：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql 系统数据库

mysql 数据库存储了 MySQL 服务器正常运行所需的各种系统信息，包含了关于数据库对象元数据（metadata）的数据字典表和系统表。

从 MySQL 8.0 开始，mysql 系统表和数据字典表使用 InnoDB 存储引擎，存储在 MySQL 数据目录下的 mysql.ibd 表空间文件中。在 MySQL 5.7 之前，这些系统表使用 MyISAM 存储引擎，存储在 mysql 数据库文件目录下各自的表空间文件中。

为了便于理解和使用，mysql 系统数据库中的表可以分为多种类别，下面我们分别进行介绍。

数据字典表

这些表构成了 MySQL 的数据字典，也就是关于数据库对象的元数据。数据字典表对于用户而言是不可见的，不能使用 SELECT 进行查询，也不会出现在 SHOW TABLES 或者 INFORMATION_SCHEMA.TABLES 表中。不过，大多数表可以通过 INFORMATION_SCHEMA 数据库中相应的视图进行查询。

例如，我们不能直接查询 mysql.tables 表：

mysql> select * from mysql.tables;
ERROR 3554 (HY000): Access to data dictionary table 'mysql.tables' is rejected.

不过，可以通过 INFORMATION_SCHEMA.TABLES 查询数据库中的表：

mysql> select * from information_schema.tables;

具体来说，MySQL 8.0 包括以下数据字典表：

catalogs：系统目录（Catalog）信息。
character_sets：系统支持的字符集。
check_constraints：CHECK 约束信息。
collations：字符集支持的排序规则。
column_statistics：字段的直方图统计信息。
column_type_elements：字段的数据类型信息。
columns：表中的字段信息。
dd_properties：存储数据字典的属性，例如版本。服务器利用这些信息决定是否需要升级数据字典。
events：事件调度器中的事件信息。
foreign_keys、foreign_key_column_usage：外键相关的信息。
index_column_usage：索引字段信息。
index_partitions：索引分区信息。
index_stats：ANALYZE TABLE 命令生成的动态索引统计。
indexes：表中的索引信息。
innodb_ddl_log：支持原子 DDL 操作的日志。
parameter_type_elements：存储过程和函数的参数信息，以及存储函数的返回值类型
parameters：关于存储过程和函数的信息。
resource_groups：关于资源组的信息。
routines：关于存储过程和函数的信息。
schemata：关于数据库/模式的信息。在 MySQL 中，模式和数据库是相同的概念。
st_spatial_reference_systems：可以用于空间数据的空间参照系统。
table_partition_values：表分区使用的分区值。
table_partitions：表的分区信息。
table_stats：ANALYZE TABLE 命令生成的动态表统计。
tables：数据库中的表。
tablespace_files：表空间使用的文件。
tablespaces：活动表空间。
triggers：触发器。
view_routine_usage：视图和存储函数之间的依赖关系。
view_table_usage：视图和基表之间的依赖关系。

到目前为止，mysql.foreign_keys 和 mysql.foreign_key_column_usage 还没有完全对应的 INFORMATION_SCHEMA 表。按照 SQL 标准，可以使用 INFORMATION_SCHEMA 数据库中的 REFERENTIAL_CONSTRAINTS 和 KEY_COLUMN_USAGE 查询外键相关的信息。

从 MySQL 8.0 开始，一些 INFORMATION_SCHEMA 数据字典表取代了之前的系统表，这些系统表不再存在于 mysql 系统数据库中：

数据字典表 events 取代了之前的 event 系统表。
数据字典表 parameters 和 routines 共同取代了之前的 proc 系统表。

权限信息表

这些系统表存储了用户账户的授权信息以及它们拥有的权限。

从 MySQL 8.0 开始，这些权限表使用事务型的 InnoDB 存储引擎替代了之前的 MyISAM 存储引擎。存储引擎的改变也带来了账户管理行为的变化。例如之前的 CREATE USER 和 GRANT 语句如果同时操作多个用户，可能导致部分用户操作成功而其他用户操作失败；现在这些操作具有事务性，要么全部用户都操作成功，要么出现错误回滚所有的操作。

MySQL 8.0 中的权限信息表如下：

user：用户账户、全局权限以及其他信息。
global_grants：用户的动态全局权限。
db：数据库级别的权限。
tables_priv：表级别的权限。
columns_priv：字段级别的权限。
procs_priv：存储过程和函数上的权限。
proxies_priv：代理用户权限。
default_roles：用户连接并认证后默认激活的角色，或者执行 SET ROLE DEFAULT 命令后设置的角色。
role_edges：角色的授予关系。user 表中的一行数据既可能代表一个用户账户，也可能代表一个角色。
password_history：密码修改历史。

对象信息表

这些系统表包含了关于存储过程、组件、用户定义函数以及服务器端插件的信息：

component：服务器组件的注册信息。
func：用户定义函数（UDF）信息。
plugin：服务器端插件信息。

查询日志表

general_log：通用查询日志表。
slow_log：慢查询日志表。

这些日志表的存储引擎为 CSV。

服务器端帮助信息表

这些表中存储了服务器端相关的帮助信息：

help_category：帮助信息分类。
help_keyword：帮助信息关键字。
help_relation：关键字和帮助主题之间的关系。
help_topic：帮助主题的具体内容。

时区信息表

这些系统表包含了时区相关的信息：

time_zone：时区 ID 以及是否包含闰秒。
time_zone_leap_second：闰秒发生的情况。
time_zone_name：时区 ID 和名称的映射。
time_zone_transition、time_zone_transition_type：时区描述。

复制信息表

MySQL 服务器使用这些表维护复制功能：

gtid_executed：存储 GTID 数据。
ndb_binlog_index： NDB 集群复制的二进制日志信息。
slave_master_info、slave_relay_log_info、slave_worker_info：在从服务器上存储复制信息。

优化器系统表

这些系统表会被优化器使用：

innodb_index_stats、innodb_table_stats：InnoDB 优化器持久性统计信息。
server_cost、engine_cost：优化器成本模型需要使用这些表中存储的各种操作的评估成本进行优化。server_cost 包含了通用服务器操作的优化器成本估计，engine_cost 包含了特定存储引擎操作的优化器成本估计。

其他系统表

audit_log_filter、audit_log_user：如果安装了 MySQL Enterprise Audit，这些审计日志表中会存储关于审计日志过滤器和审计的用户账户信息。
firewall_users、firewall_whitelist：如果安装了 MySQL Enterprise Firewall，这些表中会存储企业防火墙使用的信息。
servers：FEDERATED 存储引擎使用的远程服务器连接信息。
innodb_dynamic_metadata：InnoDB 存储的快速变化的元数据，例如 auto-increment 计数值和索引损坏标识。该表用于替代 InnoDB 系统表空间中的数据字典缓冲表。

关于 mysql 系统数据库的更多信息，可以参考官方文档。

information_schema 信息数据库

INFORMATION_SCHEMA 数据库提供了访问数据库元数据的各种视图，包括数据库、表、字段类型以及访问权限等。这些信息有时候也被称为数据字典（data dictionary ）或者系统目录（system catalog），主要来源就是 mysql 系统数据库中的数据字典表。

INFORMATION_SCHEMA 中的表实际上都是只读的视图，只能执行查询操作，不能执行 DML 语句。通常使用SHOW语句查看的信息都存在对应的数据字典表，例如SHOW DATABASES语句也可以使用以下查询实现：

mysql> select schema_name
    -> from information_schema.schemata;
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

information_schema.tables 存储了数据库中的所有表和视图信息，包括它自己的系统视图：

mysql> select table_name, table_type 
    -> from information_schema.tables
    -> where table_schema = 'information_schema';
+---------------------------------------+-------------+
| TABLE_NAME                            | TABLE_TYPE  |
+---------------------------------------+-------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS     | SYSTEM VIEW |
| APPLICABLE_ROLES                      | SYSTEM VIEW |
| CHARACTER_SETS                        | SYSTEM VIEW |
...
| VIEWS                                 | SYSTEM VIEW |
| VIEW_ROUTINE_USAGE                    | SYSTEM VIEW |
| VIEW_TABLE_USAGE                      | SYSTEM VIEW |
+---------------------------------------+-------------+
73 rows in set (0.01 sec)

利用这些视图，我们可以方便地查看 MySQL 数据库、表结构、约束、索引以及视图、存储过程/函数、触发器、计划任务等信息。

另外，关于这些 INFORMATION_SCHEMA 表的完整介绍，可以参考官方文档。

performance_schema 性能数据库

performance_schema 性能数据库为 MySQL 服务器的运行时状态提供了一个底层的监控功能。

MySQL 默认启动了性能数据库，也可以在启动服务时通过参数 performance_schema 指定是否启用。例如，在配置文件 my.cnf 中进行如下设置：

[mysqld]
performance_schema=ON

性能数据库中的表的存储引擎为 PERFORMANCE_SCHEMA，数据存储在内存中：

mysql> select table_name, table_type, engine
    -> from information_schema.tables
    -> where table_schema = 'performance_schema';
+------------------------------------------------------+------------+--------------------+
| TABLE_NAME                                           | TABLE_TYPE | ENGINE             |
+------------------------------------------------------+------------+--------------------+
| accounts                                             | BASE TABLE | PERFORMANCE_SCHEMA |
| binary_log_transaction_compression_stats             | BASE TABLE | PERFORMANCE_SCHEMA |
| cond_instances                                       | BASE TABLE | PERFORMANCE_SCHEMA |
...
| users                                                | BASE TABLE | PERFORMANCE_SCHEMA |
| variables_by_thread                                  | BASE TABLE | PERFORMANCE_SCHEMA |
| variables_info                                       | BASE TABLE | PERFORMANCE_SCHEMA |
+------------------------------------------------------+------------+--------------------+
104 rows in set (0.01 sec)

MySQL 服务每次启动时都会重新初始化性能数据库，我们可以使用以下命令查看是否成功初始化：

mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

ON 表示成功初始化；OFF 意味着出现了错误，此时需要检查服务器的错误日志进行处理。

performance_schema 数据库的名称是小写形式，所有的表名也是小写。我们可以使用 SHOW CREATE TABLE 命令查看表的定义，例如：

mysql> show create table performance_schema.users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `USER` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `CURRENT_CONNECTIONS` bigint NOT NULL,
  `TOTAL_CONNECTIONS` bigint NOT NULL,
  UNIQUE KEY `USER` (`USER`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

performance_schema 数据库中的表可以按照收集信息的类型分成不同的组：

设置表，显示和修改监控配置。这些表的名称都以 setup_ 开始，例如 setup_objects 表存储了需要进行监控的对象。
当前事件表，events_waits_current 表包含了每个线程最新的等待事件。其他类似的表包含了不同级别的等待事件：events_stages_current 代表每个线程当前执行阶段的事件，events_statements_current 代表了当前语句事件，events_transactions_current 代表了当前事务事件。
历史事件表，这些表的结构和当前事件表相同，但是包含了更多的历史数据。例如 events_waits_history 表包含了每个线程最近的 10 个等待事件，events_waits_history_long 表包含了所有线程最近的 10000 个事件。阶段事件、语句事件以及事务事件也存在类似的历史事件表。
事件汇总表，包含了按照不同事件分组汇总的信息，包括已经从历史事件表中移除的事件。例如，events_waits_summary_by_instance 代表了每个监测实例的等待事件汇总。
监测实例表，记录了被检测的对象类型。每个监测对象会产生一个事件，这些表存储了事件名称和解释性说明或者状态信息。例如，file_instances 表存储了 I/O 监测涉及到的文件。
其他表，例如 threads 表包含了每个线程的信息。

关于 performance_schema 表的分类和详细介绍，可以参考官方文档。

初始情况下，并不是所有的监测项目（等待事件）和消费者（performance_schema 表）都处于启用状态，因此性能数据库不会收集所有事件并更新相应的 performance_schema 表。如果想要启用所有的监测事件和计时功能，可以执行以下两个语句：

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES';
Query OK, 560 rows affected (0.04 sec)
mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES';
Query OK, 10 rows affected (0.00 sec)

当然，我们也可以根据需要启用或者禁用某个监测事件和 performance_schema 表的统计更新。

然后就可以通过上面介绍的各种等待事件表查看服务器的运行状态和性能数据。例如，以下查询可以获取占用最多等待时间的等待事件：

mysql> select event_name, sum_timer_wait
    -> from performance_schema.events_waits_summary_global_by_event_name
    -> order by sum_timer_wait desc limit 10;
+---------------------------------------+--------------------+
| event_name                            | sum_timer_wait     |
+---------------------------------------+--------------------+
| idle                                  | 709703548630225000 |
| wait/io/file/innodb/innodb_data_file  |     12991466035548 |
| wait/io/file/innodb/innodb_dblwr_file |      5866787225266 |
| wait/io/file/innodb/innodb_log_file   |      4101617858470 |
| wait/io/table/sql/handler             |      2467223162844 |
| wait/io/file/innodb/innodb_temp_file  |       518447928064 |
| wait/io/file/sql/binlog               |       324876178484 |
| wait/io/file/sql/binlog_index         |        71481172202 |
| wait/io/file/myisam/kfile             |        47731122384 |
| wait/io/file/sql/ERRMSG               |        42268213086 |
+---------------------------------------+--------------------+
10 rows in set (0.02 sec)

显然，我们的系统大部分时间都处于空闲状态（idle）。

关于性能数据库的更多信息，可以参考官方文档。

sys 数据库

MySQL 5.7.7 引入了 sys 数据库，其中包含了一系列的视图，可以方便 DBA 和开发人员解释 performance_schema 性能数据库中收集的数据。sys 数据库中的对象主要用于性能调优和诊断，包括：

视图，将性能数据库中的数据进行汇总，以更加容易理解的形式进行展示。
存储过程，配置性能数据库以及生成诊断报告等操作。
存储函数，用于查询性能数据库的配置和格式化服务。

对于全新的安装，如果使用 --initialize 或者 --initialize-insecure 选项执行 mysqld 初始化操作，默认会安装 sys 数据库。如果不想要安装 sys 数据库，可以在初始化之后手动删除。

sys 数据库中提供了一个存储配置选项的 sys_config 表以及该表上的两个触发器 sys_config_insert_set_user 和 sys_config_update_set_user。

sys 数据库中的视图大部分都是成对出现的，它们的名称相同，其中一个加上了 x$ 前缀。例如，视图 host_summary_by_file_io 汇总了按照主键分组的文件 I/O 并显示了更易阅读的延迟信息（而不是原本以皮秒为单位的延迟）：

mysql> select * from sys.host_summary_by_file_io;
+------------+------+------------+
| host       | ios  | io_latency |
+------------+------+------------+
| background | 1630 | 4.53 s     |
| localhost  |    6 | 81.64 ms   |
+------------+------+------------+
2 rows in set (0.06 sec)

同时，视图 x$host_summary_by_file_io 汇总了相同的信息，但是延迟以皮秒为单位进行显示：

mysql> select * from sys.x$host_summary_by_file_io;
+------------+------+---------------+
| host       | ios  | io_latency    |
+------------+------+---------------+
| background | 1630 | 4528276024976 |
| localhost  |    6 |   81639950348 |
+------------+------+---------------+
2 rows in set (0.00 sec)

没有 x$ 前缀的视图是为了方便用户使用和查看p，包含 x$ 前缀的视图是为了其他工具进一步的处理。

sys 数据库中的以下视图可以为长时间运行的事务提供进度报告：

processlist
session
x$processlist
x$session

如果以及启动相应的测试项目和消费者，视图中的 progress 列会显示每个操作阶段（必须支持进度报告）的完成百分比。

mysqldump 和 mysqlpump 工具默认都不会导出 sys 数据库。如果想要导出该数据库，可以在命令行中明确指定 sys 数据库的名称：

mysqldump --databases --routines sys > sys_dump.sql
mysqlpump sys > sys_dump.sql

使用以下命令还原导出的备份文件：

mysql < sys_dump.sql

总结

数据字典、系统信息表和性能数据库，对于我们了解 MySQL 数据库的结构、日常维护管理以及性能的诊断和优化都至关重要。因此，无论是 DBA 还是开发人员，都应该熟悉并掌握这些数据库对象的意义和使用场景。我们也会在后续文章中针对每个系统数据库做进一步的详细介绍和演示。

如果觉得文章对你有用，请不要白嫖！欢迎关注❤️、评论📝、点赞👍！

不剪发的Tony老师

关注

8
点赞
踩
35

收藏

觉得还不错? 一键收藏
打赏
2
评论
MySQL 8.0 中 4 个默认的系统数据库

MySQL 8.0 默认安装的 4 个系统数据库：mysql 数据库存储了 MySQL 服务器正常运行所需的各种信息；information_schema 提供了访问数据库元数据的各种视图，包括数据库、表、字段类型以及访问权限等； performance_schema 为 MySQL 服务器的运行时状态提供了一个底层的监控功能；sys 包含了一系列方便 DBA 和开发人员利用 performance_schema 性能数据库进行性能调优和诊断的视图。
复制链接

扫一扫