漫谈MySQL七-系统数据库mysql库详解

最新推荐文章于 2024-07-30 20:26:56 发布

王老狮

最新推荐文章于 2024-07-30 20:26:56 发布

阅读量1.4k

点赞数 9

分类专栏： mysql 文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/b379685397/article/details/122850101

版权

mysql 专栏收录该内容

14 篇文章 13 订阅

订阅专栏

大家好，我是王老狮，Mysql库是数据库的核心，它存储了 MySQL 的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。今天我们就来聊聊mysql库中的一些重要的表。

1.权限系统表

因为权限管理是 DBA 的职责，所以对于这个部分的表，我们大概了解下即可。在 mysql 系统库中，MySQL 访问权限系统表，放在 mysql 库中，主要包含如下几个表。

user：包含用户账户、全局权限和其他非权限列表（安全配置字段和资源控制字段）。
db：数据库级别的权限表。该表中记录的权限信息代表用户是否可以使用这些权限来访问被授予访问的数据库下的所有对象（表或存储程序）。
tables_priv：表级别的权限表。
columns_priv：字段级别的权限表。
procs_priv：存储过程和函数权限表。
proxies_priv：代理用户权限表。

提示：要更改权限表的内容，应该使用账号管理语句（如：CREATE USER、GRANT、REVOKE 等）来间接修改，不建议直接使用 DML 语句修改权限表。

2.统计信息表

持久化统计功能是通过将内存中的统计数据存储到磁盘中，使其在数据库重启时可以快速重新读入这些统计信息而不用重新执行统计，从而使得查询优化器可以利用这些持久化的统计信息准确地选择执行计划（如果没有这些持久化的统计信息，那么数据库重启之后内存中的统计信息将会丢失，下一次访问到某库某表时，需要重新计算统计信息，并且重新计算可能会因为估算值的差异导致查询计划发生变更，从而导致查询性能发生变化）。

如何启用统计信息的持久化功能呢？当innodb_stats_persistent = ON 时全局的开启统计信息的持久化功能，默认是开启的.

show variables like 'innodb_stats_persistent';

如果要单独关闭某个表的持久化统计功能，则可以通过 ALTER TABLE tbl_name STATS_ PERSISTENT = 0 语句来修改。

2.1.innodb_table_stats

innodb_table_stats 表提供查询与表数据相关的统计信息。

select * from innodb_table_stats where table_name = 'order_exp'\G

database_name：数据库名称。
table_name：表名、分区名或子分区名。
last_update：表示 InnoDB 上次更新统计信息行的时间。
n_rows：表中的估算数据记录行数。
clustered_index_size：主键索引的大小，以页为单位的估算数值。
sum_of_other_index_sizes：其他（非主键）索引的总大小，以页为单位的估算数值。

其中的估算数据记录行数，页为单位的估算数值等等.

2.2.innodb_index_stats

innodb_index_stats 表提供查询与索引相关的统计信息。

select * from innodb_index_stats where table_name = 'order_exp';

表字段含义如下。

database_name：数据库名称。
table_name：表名、分区表名、子分区表名。
index_name：索引名称。
last_update：表示 InnoDB 上次更新统计信息行的时间。
stat_name：统计信息名称，其对应的统计信息值保存在 stat_value 字段中。
stat_value：保存统计信息名称 stat_name 字段对应的统计信息值。
sample_size：stat_value 字段中提供的统计信息估计值的采样页数。
stat_description：统计信息名称 stat_name 字段中指定的统计信息的说明。从表的查询数据中可以看到：
stat_name 字段一共有如下几个统计值。
size：当 stat_name 字段为 size 值时，stat_value 字段值表示索引中的总页数量。
n_leaf_pages：当 stat_name 字段为 n_leaf_pages 值时，stat_value 字段值表示索引叶子页的数量。
n_diff_pfxNN：NN 代表数字（例如 01、02 等）。当 stat_name 字段为n_diff_pfxNN 值时，stat_value 字段值表示索引的 first column（即索引的最前索引列，从索引定义顺序的第一个列开始）列的唯一值数量。例如：当 NN 为 01 时，stat_value 字段值就表示索引的第一个列的唯一值数量；当 NN 为 02 时，stat_value 字段值就表示索引的第一个和第二个列组合的唯一值数量，依此类推。此外，在 stat_name = n_diff_pfxNN 的情况下，stat_description 字段显示一个以逗号分隔的计算索引统计信息字段的列表。
从 index_name 字段值为 PRIMARY 数据行的 stat_description 字段的描述信息“id”中可以看出，主键索引的统计信息只包括创建主键索引时显式指定的列。
从index_name 字段值为u_idx_day_status 数据行的stat_description 字段的描述信息“insert_time,order_status,expire_time”中可以看出，唯一索引的统计信息只包括创建唯一索引时显式指定的列。
从index_name 字段值为idx_order_no 数据行的stat_description 字段的描述信息“order_no,id”中可以看出，普通索引（非唯一的辅助索引）的统计信息包括了显式定义的列和主键列。

注意，上述的描述中出现的诸如叶子页，索引的最前索引列等等，以及这个统计表的具体作用，后续单独拿出来给大家详解。

3.日志记录表

MySQL 的日志系统包含：普通查询日志、慢查询日志、错误日志（记录服务器启动时、运行中、停止时的错误信息）、二进制日志（记录服务器运行过程中数据变更的逻辑日志）、中继日志（记录从库 I/O 线程从主库获取的主库数据变更日志）、DDL 日志（记录 DDL 语句执行时的元数据变更信息。在 MySQL 5.7 中只支持写入文件中，在 MySQL 8.0 中支持写入 innodb_ddl_log 表中。在 MySQL5.7 中，只有普通查询日志、慢查询日志支持写入表中（也支持写入文件中）,可以通过 log_output=TABLE 设置保存到 mysql.general_log 表和 mysql.slow_log 表中，其他日志类型在 MySQL 5.7 中只支持写入文件中。

3.1.general_log

general_log 表提供查询普通 SQL 语句的执行记录信息，用于查看客户端到底在服务器上执行了什么 SQL 语句。默认不开启

show variables like 'general_log';

开启

set global log_output='TABLE'; -- 'TABLE,FILE'表示同时输出到表和文件set global
general_log=on;
show variables like 'general_log';

任意执行一个查询后

 select * from mysql.general_log\G

3.2.slow_log

slow_log 表提供查询执行时间超过 long_query_time 设置值的 SQL 语句、未使用索引的语句（需要开启参数 log_queries_not_using_indexes=ON）或者管理语句（需要开启参数 log_slow_admin_statements=ON）。

更详细的信息，我们在 SQL 优化的部分再了解。

4.复制信息表

复制信息表在从库复制主库的数据期间，用于保存从主库转发到从库的binlog（二进制日志）事件，记录有关 relay log（中继日志）当前状态和位置的信息。我们大致了解即可。

master.info 文件或者 mysql.slave_master_info 表：用于保存从库的 I/O 线程连接主库的连接状态、账号、IP 地址、端口、密码，以及 I/O 线程当前读取主库binlog 的文件和位置信息（称为 I/O 线程信息日志）。在默认情况下，I/O 线程的连接信息和状态保存在 master.info 文件中（默认位置在 datadir 下，可以使用master_info_file 参数指定 master.info 文件路径。注：在 MySQL 5.7.x 较新的版本以及 8.0.x 版本中该参数已经被移除）。如果需要保存在 mysql.slave_master_info 表中，则需要在服务器启动之前设置 master_info_repository=TABLE。

relay_log.info 文件或者 mysql.slave_relay_log_info 表：当从库的 I/O 线程从主库获取到最新的 binlog 事件信息后会先写入从库本地的 relay log 中，然后SQL 线程再去读取 relay log 解析并重放。relay_log.info 文件或者mysql.slave_relay_log_info 表就是用于记录最新的 relay log 的文件和位置，以及SQL 线程当前重放的事件对应的主库 binlog 的文件和位置信息的（SQL 线程位置被称为 SQL 线程信息日志）。在默认情况下，relay log 的位置信息和 SQL 线程的位置信息保存在 relay-log.info 文件中（默认位置在 datadir 下，可以使用relay_log_info_file 选项指定 relay-log.info 文件路径）。如果需要保存在mysql.slave_relay_log_info 表中，则需要在服务器启动之前设置relay_log_info_repository=TABLE。