MySQL系统库之mysql

梦里梦外; 2023-01-19 14:50 7阅读 0赞

# 权限系统表 #

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

下几个表：

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

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

# 统计信息表 #

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

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

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

## innodb\_table\_stats ##

innodb\_table\_stats表提供查询与表数据相关的统计信息。

mysql> select * from innodb_table_stats where table_name = 't_order'\G
    *************************** 1. row ***************************
               database_name: test
                  table_name: t_order
                 last_update: 2021-05-10 21:37:27
                      n_rows: 0
        clustered_index_size: 1
    sum_of_other_index_sizes: 2
    1 row in set (0.00 sec)

字段说明：

*  database\_name：数据库名称。
 *  table\_name：表名、分区名或子分区名。
 *  last\_update：表示 InnoDB 上次更新统计信息行的时间。
 *  n\_rows：表中的估算数据记录行数。
 *  clustered\_index\_size：主键索引的大小，以页为单位的估算数值。
 *  sum\_of\_other\_index\_sizes：其他（非主键）索引的总大小，以页为单位  
    的估算数值。

## innodb\_index\_stats ##

innodb\_index\_stats表提供查询与索引相关的统计信息。

表字段含义如下：

*  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字段值为udx\_order\_no数据行的stat\_description字段  
    的描述信息“order\_no”中可以看出，唯一索引的统  
    计信息只包括创建唯一索引时显式指定的列。
 *  从index\_name字段值为idx\_insert\_time\_order\_status数据行的stat\_description字段的描  
    述信息“insert\_time,order\_status,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语句，缺省不开启  
：

开启  
general\_log  
：

mysql> set global log_output='TABLE'; 
     -- 'TABLE,FILE'表示同时输出到表和文件
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> set global general_log=on;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show variables like 'general_log';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | general_log   | ON    |
    +---------------+-------+
    1 row in set (0.01 sec)

任意执行一个查询后  
：

mysql> select * from test.t_order;
    Empty set (0.01 sec)
    
    mysql> select * from mysql.general_log\G;
    *************************** 1. row ***************************
      event_time: 2021-05-10 21:45:01.663504
       user_host: root[root] @ localhost []
       thread_id: 2
       server_id: 0
    command_type: Query
        argument: show variables like 'general_log'
    *************************** 2. row ***************************
      event_time: 2021-05-10 21:45:48.746697
       user_host: root[root] @ localhost []
       thread_id: 2
       server_id: 0
    command_type: Query
        argument: select * from test.t_order
    *************************** 3. row ***************************
      event_time: 2021-05-10 21:45:57.698880
       user_host: root[root] @ localhost []
       thread_id: 2
       server_id: 0
    command_type: Query
        argument: select * from mysql.general_log
    3 rows in set (0.00 sec)

## slow\_log ##

slow\_log表提供查询执行时间超过long\_query\_time设置值的SQL语句、未  
使用索引的语句（需要开启参数log\_queries\_not\_using\_indexes=ON）或者管理语

句（需要开启参数log\_slow\_admin\_statements=ON）。

mysql> show variables like '%long_query_time%';
    +-----------------+-----------+
    | Variable_name   | Value     |
    +-----------------+-----------+
    | long_query_time | 10.000000 |
    +-----------------+-----------+
    1 row in set (0.00 sec)
    
    mysql> show variables like '%slow_query%';
    +---------------------+-----------------------------------+
    | Variable_name       | Value                             |
    +---------------------+-----------------------------------+
    | slow_query_log      | OFF                               |
    | slow_query_log_file | /usr/local/mysql/data/192-slow.log|
    +---------------------+-----------------------------------+
    
    mysql> set global slow_query_log=ON;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show variables like '%slow_query%';
    +---------------------+--------------------------------+
    | Variable_name       | Value                          |
    +---------------------+--------------------------------+
    | slow_query_log      | ON                             |
    | slow_query_log_file | /var/lib/mysql/morris-slow.log |
    +---------------------+--------------------------------+
    2 rows in set (0.01 sec)
    
    mysql> select sleep(20);
    +-----------+
    | sleep(20) |
    +-----------+
    |         0 |
    +-----------+
    1 row in set (20.07 sec)
    
    mysql> select * from slow_log\G;
    *************************** 1. row ***************************
        start_time: 2021-05-10 21:52:05.944032
         user_host: root[root] @ localhost []
        query_time: 00:00:20.073073
         lock_time: 00:00:00.000000
         rows_sent: 1
     rows_examined: 0
                db: mysql
    last_insert_id: 0
         insert_id: 0
         server_id: 0
          sql_text: select sleep(20)
         thread_id: 2
    1 row in set (0.00 sec)

# 复制信息表 #

复制信息表在从库复制主库的数据期间，用于保存从主库转发到从库的  
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文件路径。注：在 MySQL5.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。

# 演示数据 #

create table t_order (
    	id bigint primary key auto_increment comment '订单的主键',
    	order_no varchar(50) not null comment '订单编号',
    	order_desc varchar(100) not null comment '订单说明',
    	insert_time datetime not null comment '订单生成时间',
    	expire_time datetime not null comment '订单过期时间',
    	order_status smallint not null default 0 comment '订单状态 0:待支付，-1：已支付，-1：已过期',
    	unique index udx_order_no(order_no),
    	index idx_insert_time_order_status(insert_time, order_status)
    )