MySQL默认数据库说明

最新推荐文章于 2024-09-07 22:40:02 发布

大白快跑

最新推荐文章于 2024-09-07 22:40:02 发布

阅读量4.2k

点赞数 2

分类专栏： MySQL 文章标签：数据库 mysql 服务器

本文链接：https://blog.csdn.net/u010704350/article/details/122755825

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

前言

本文主要对MySQL默认数据库说明,文章内容来源于官方文档，翻译不当请见谅。

测试环境

虚拟机环境：VirtualBox 6.0.24
操作系统：Oracle Linux Server release 6.5 x86_64
MySQL版本：5.7.33

MySQL默认数据库有那些？

如下所示：

root@localhost 13:27:44 [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

各个数据库的作用是什么？

一.information_schema

1、INFORMATION_SCHEMA提供对数据库元数据，MySQL服务器的信息的访问，如数据库或表的名称，列的数据类型，或访问权限。这些信息也可以称为MySQL的数据字典和系统目录。
2、INFORMATION_SCHEMA是每个MySQL实例中的一个数据库，它存储关于MySQL服务器维护的所有其他数据库的信息。INFORMATION_SCHEMA数据库包含几个只读表。它们实际上是视图，而不是基表，因此没有与它们相关联的文件，并且不能在它们上设置触发器。此外，也没有具有该名称的数据库目录。
3、尽管可以使用USE语句选择INFORMATION_SCHEMA作为默认数据库，但是只能读取表的内容，而不能对表执行INSERT、UPDATE或DELETE操作。
4、INFORMATION_SCHEMA下包含一些通用表（如TABLES/TABLESPACES等）、Innodb相关的表
详细信息可以参考官方文档。

二.mysql

mysql数据库是系统数据库 。它包含存储 MySQL 服务器运行时所需信息的表。
数据库中的表mysql分为以下几类：
授予系统表、对象信息系统表、日志系统表、服务器端帮助系统表、时区系统表、复制系统表、优化器系统表

 1. 授予系统表
这些系统表包含有关用户帐户及其所拥有权限的授权信息：
user：用户帐户、全局权限和其他非权限列。
db: 数据库级权限。
tables_priv：表级权限。
columns_priv：列级权限。
procs_priv: 存储过程和函数权限。
proxies_priv: 代理用户权限。
 2. 对象信息系统表
这些系统表包含有关存储程序、可加载函数和服务器端插件的信息：
event：使用CREATE Event创建。如果服务器是用——skip-grant-tables选项启动的，那么事件调度器将被禁用，并且表中注册的事件不会运行。
func: 使用CREATE FUNCTION创建。在正常启动过程中，服务器加载在该表中注册的函数。如果服务器是用——skip-grant-tables选项启动的，那么在表中注册的函数将不会被加载，并且不可用。
plugin：使用INSTALL PLUGIN安装服务器端插件。在正常的启动顺序中，服务器加载在该表中注册的插件。如果服务器是用——skip-grant-tables选项启动的，表中注册的插件将不会被加载，并且不可用。
MySQL 5.7.6版本之后，plugin表使用InnoDB存储引擎，在MySQL 5.7.6版本之前使用MyISAM存储引擎。
proc: 有关存储过程和函数的信息。
 3. 日志系统表
服务器使用这些系统表进行日志记录：
general_log：一般查询日志表。
slow_log：慢查询日志表。
日志表使用CSV存储引擎。
 4. 服务器端帮助系统表
这些系统表包含服务器端帮助信息：
help_category：有关帮助类别的信息。
help_keyword：与帮助主题相关的关键字。
help_relation：帮助关键字和主题之间的映射。
help_topic: 帮助主题内容。
 5. 时区系统表
这些系统表包含时区信息：
time_zone: 时区 ID 以及它们是否使用闰秒。
time_zone_leap_second: 当闰秒发生时。
time_zone_name: 时区 ID 和名称之间的映射。
time_zone_transition, time_zone_transition_type: 时区说明。
 6. 复制系统表
服务器使用这些系统表来支持复制：
gtid_executed：用于存储 GTID 值的表。该gtid_executed表使用 InnoDB存储引擎。
slave_master_info, slave_relay_log_info, slave_worker_info: 用于在副本服务器上存储复制信息。
这三个表都使用InnoDB 存储引擎。
 7. 优化器系统表
这些系统表供优化器使用：
innodb_index_stats, innodb_table_stats: 用于 InnoDB持久优化器统计。
server_cost, engine_cost: 优化器成本模型使用的表包含有关在查询执行期间发生的操作的成本估计信息。 server_cost包含一般服务器操作的优化器成本估算。 engine_cost包含特定于特定存储引擎的操作的估计。
这些表使用InnoDB存储引擎。

详细信息可以参考官方文档。

三.performance_schema

The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. The Performance Schema has these characteristics:

The Performance Schema provides a way to inspect internal execution of the server at runtime. It is implemented using the PERFORMANCE_SCHEMA storage engine and the performance_schema database. The Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata.

The Performance Schema monitors server events. An “event” is anything the server does that takes time and has been instrumented so that timing information can be collected. In general, an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements. Event collection provides access to information about synchronization calls (such as for mutexes) file and table I/O, table locks, and so forth for the server and for several storage engines.

Performance Schema events are distinct from events written to the server's binary log (which describe data modifications) and Event Scheduler events (which are a type of stored program).

Performance Schema events are specific to a given instance of the MySQL Server. Performance Schema tables are considered local to the server, and changes to them are not replicated or written to the binary log.

Current events are available, as well as event histories and summaries. This enables you to determine how many times instrumented activities were performed and how much time they took. Event information is available to show the activities of specific threads, or activity associated with particular objects such as a mutex or file.

The PERFORMANCE_SCHEMA storage engine collects event data using “instrumentation points” in server source code.

Collected events are stored in tables in the performance_schema database. These tables can be queried using SELECT statements like other tables.

Performance Schema configuration can be modified dynamically by updating tables in the performance_schema database through SQL statements. Configuration changes affect data collection immediately.

Tables in the Performance Schema are in-memory tables that use no persistent on-disk storage. The contents are repopulated beginning at server startup and discarded at server shutdown.

Monitoring is available on all platforms supported by MySQL.

Some limitations might apply: The types of timers might vary per platform. Instruments that apply to storage engines might not be implemented for all storage engines. Instrumentation of each third-party engine is the responsibility of the engine maintainer. See also Section 25.21, “Restrictions on Performance Schema”.

Data collection is implemented by modifying the server source code to add instrumentation. There are no separate threads associated with the Performance Schema, unlike other features such as replication or the Event Scheduler.

The Performance Schema is intended to provide access to useful information about server execution while having minimal impact on server performance. The implementation follows these design goals:

Activating the Performance Schema causes no changes in server behavior. For example, it does not cause thread scheduling to change, and it does not cause query execution plans (as shown by EXPLAIN) to change.

Server monitoring occurs continuously and unobtrusively with very little overhead. Activating the Performance Schema does not make the server unusable.

The parser is unchanged. There are no new keywords or statements.

Execution of server code proceeds normally even if the Performance Schema fails internally.

When there is a choice between performing processing during event collection initially or during event retrieval later, priority is given to making collection faster. This is because collection is ongoing whereas retrieval is on demand and might never happen at all.

It is easy to add new instrumentation points.

Instrumentation is versioned. If the instrumentation implementation changes, previously instrumented code continues to work. This benefits developers of third-party plugins because it is not necessary to upgrade each plugin to stay synchronized with the latest Performance Schema changes.

四.sys

MySQL 5.7包含了sys模式，这是一组帮助dba和开发人员解释性能模式收集的数据的对象。系统模式对象可以用于典型的调优和诊断用例。该模式中的对象包括:
性能模式数据汇总成更易于理解的形式的Views 。
执行性能架构配置和生成诊断报告等操作的procedures 。
提供查询Performance Schema配置和格式化服务的functions 。
详细信息可以参考官方文档。