揭秘MySQL系统库：mysql库全解析

MySQL中的mysql系统库

权限系统表

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

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

提示
要更改权限表的内容，应该使用账号管理语句（如：CREATE USER、GRANT、REVOKE等）来间接修改，不建议直接使用 DML 语句修改权限表。
(grant ，revoke 语句执行后会变更权限表中相关记录，同时会更新内存中记录用户权限的相关对象。DML 语句直接修改权限表只是修改了表中权限信息，需要执行flush privileges;来更新内存中保存用户权限的相关对象)

统计信息表

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

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

show variables like 'innodb_stats_persistent';

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

innodb_table_stats

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

select * from innodb_table_stats where table_name = 'order_exp'

表字段含义如下。

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

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”中可以看出，普通索引（非唯一的辅助索引）的统计信息包括了显式定义的列和主键列。

注意，上述的描述中出现的诸如叶子页，索引的最前索引列等等，这些东西若不是很清楚，可以查看下之前的文章。

日志记录表

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 中只支持写入文件中。

general_log

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

show variables like 'general_log';

开启

-- 'TABLE,FILE'表示同时输出到表和文件
set global log_output='TABLE'; 
-- 开启
set global general_log=on;
-- 查询SQL是否记录状态
show variables like 'general_log';

任意执行一个查询后

select * from mysql.general_log

slow_log

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

show variables like 'log_queries_not_using_indexes';
show variables like 'log_slow_admin_statements';

开启

set global log_queries_not_using_indexes=on;
set global log_slow_admin_statements=on;
show variables like 'log_queries_not_using_indexes';
show variables like 'log_slow_admin_statements';

我们已经知道慢查询日志可以帮助定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。但是默认值为关闭的，需要我们手动开启。

show VARIABLES like 'slow_query_log';

-- 开启1，关闭0
set GLOBAL slow_query_log=1;

慢是广义的概念，但是多慢算慢呢？MySQL中可以设定一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志中。long_query_time参数就是这个阈值。默认值为10，代表10秒。

show  variables like '%long_query_time%';
-- 默认10s，这里为了方便演示设置为0
set global long_query_time = 0;

然后我们测试一把，随便写一个SQL

select * from mysql.slow_log