MySQL 中的mysql库探秘

恬淡虚无真气从之

已于 2022-11-02 07:38:15 修改

阅读量953

点赞数 2

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

于 2022-10-11 21:59:30 首次发布

本文链接：https://blog.csdn.net/u012565458/article/details/127272194

版权

mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

简介：以前面试的时候，被面试官问到，你能大概说说mysql 中的mysql库都是什么，起什么作用吗？忽然一时还想不起来了，只知道是有权限用户。然后今天来写个文章，探究下。

mysql 中有个库叫mysql,总共31张表，我们常用的可能是用户表user，还有time_zone表等。作为一个合格的dba，或者是程序员等it从业者，了解mysql的mysql库的表的作用，也算是mysql基本功。

mysql库的mysql表列表如图所示：

root@mysql 09:30:05>show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

下面对这些表做些介绍：

第一部分：授权系统表

1、表columns_priv：对列进行授权，例如

root@mysql 09:57:48>grant select(id) on test_1.sbtest1 to test_user@'%';
Query OK, 0 rows affected (0.03 sec)

查看权限：

root@mysql 09:58:17>select * from columns_priv;
+------+--------+-----------+------------+-------------+---------------------+-------------+
| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |
+------+--------+-----------+------------+-------------+---------------------+-------------+
| % | test_1 | test_user | sbtest1 | id | 0000-00-00 00:00:00 | Select |
+------+--------+-----------+------------+-------------+---------------------+-------------+
1 row in set (0.00 sec)

2、表tables_priv:对表进行授权。

3、表db：db 表比较常用，是 MySQL 数据库中非常重要的权限表，表中存储了用户对某个数据库的操作权限。

5、表user：db 表中的权限列和 user 表中的权限列大致相同，只是user 表中的权限是针对所有数据库的，而 db 表中的权限只针对指定的数据库。

需要注意的是，在 user 表里启用的所有权限都是全局级的，适用于所有数据库。

6、表procs_priv: 存放存储过程和函数的权限。

表proxies_priv: 模拟角色的权限。

注释：proxies_priv(模拟角色)实现类似用户组(role)管理，需要安装插件。

二、调优系统表

1、engine_cost表: 在存储引擎层面进行一些操作对应的成本常数;

server_cost表: 在server层面进行一些操作对应的成本常数;

I/O 成本

MySQL中的数据和索引都存储到磁盘上，当查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作，这个从磁盘到内存这个加载的过程损耗的时间称为I/O成本

CPU成本

读取以及检测记录是否满足对应的搜索条件，对结果集进行排序等这些操作损耗的时间称之为CPU成本
在Server层进行连接管理、查询缓存（8.0以后去掉了）、语法解析、查询优化等操作，在存储引擎层执行具体的数据存取操作。也就是说一条SQL语句在Server层中执行的成本和它所操作表使用的存储引擎是没有关系的，所以关于这些操作对应的成本常数就存储到了server_cost表中，而依赖存储引擎的一些操作对应的成本常数就存储到了engine_cost表中。

成本常量可以通过 mysql 系统数据库中的 server_cost 和 engine_cost 两个表进行查询和设置。

server_cost 中存储的是常规服务器操作的成本估计值：

select * from mysql.server_cost

select * from mysql.server_cost;
cost_name                   |cost_value|last_update        |comment|default_value|
----------------------------|----------|-------------------|-------|-------------|
disk_temptable_create_cost  |          |2018-05-17 10:12:12|       |         10.0|
disk_temptable_row_cost     |          |2018-05-17 10:12:12|       |            1|
key_compare_cost            |          |2018-05-17 10:12:12|       |          0.1|
memory_temptable_create_cost|          |2018-05-17 10:12:12|       |          2.0|
memory_temptable_row_cost   |          |2018-05-17 10:12:12|       |          0.2|
row_evaluate_cost           |          |2018-05-17 10:12:12|       |          0.2|

disk_temptable_create_cost 和 disk_temptable_row_cost 代表了在基于磁盘的存储引擎（InnoDB 或 MyISAM）中使用内部临时表的评估成本。增加这些值会使得优化器倾向于较少使用内部临时表的查询计划。（group by / distinct等建立临时表）

key_compare_cost 代表了比较记录键的评估成本。增加该值将导致需要比较多个键值的查询计划变得更加昂贵。例如，执行 filesort 排序的查询计划比通过索引避免排序的查询计划相对更加昂贵。

memory_temptable_create_cost 和 memory_temptable_row_cost 代表了在 MEMORY 存储引擎中使用内部临时表的评估成本。增加这些值会使得优化器倾向于较少使用内部临时表的查询计划。

row_evaluate_cost 代表了计算记录条件的评估成本。增加该值会导致检查许多数据行的查询计划变得更加昂贵。例如，与读取少量数据行的索引范围扫描相比，全表扫描变得相对昂贵。

engine_cost 中存储的是特定存储引擎相关操作的成本估计值：

select * from mysql.engine_cost;
engine_name|device_type|cost_name             |cost_value|last_update        |comment|default_value|
-----------|-----------|----------------------|----------|-------------------|-------|-------------|
default    |          0|io_block_read_cost    |          |2018-05-17 10:12:12|       |          1.0|
default    |          0|memory_block_read_cost|          |2018-05-17 10:12:12|       |         0.25|

engine_name 表示存储引擎，“default”表示所有存储引擎，也可以为不同的存储引擎插入特定的数据。cost_value 为空表示使用 default_value。其中，

io_block_read_cost 代表了从磁盘读取索引或数据块的成本。增加该值会使读取许多磁盘块的查询计划变得更加昂贵。例如，与读取较少块的索引范围扫描相比，全表扫描变得相对昂贵。

memory_block_read_cost 表示从数据库缓冲区读取索引或数据块的成本。
执行以下语句：

mysql> explain format=json select * from user where birthday between "2000-01-01" and "2020-11-01"; 
 
+ -------------------------------------------------------------------- +
| EXPLAIN |
+ -------------------------------------------------------------------- +
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "9822.60"
    },
    "table": {
      "table_name": "user",
      "access_type": "ALL",
      "possible_keys": [
        "index_birthday"
      ],
      "rows_examined_per_scan": 48308,
      "rows_produced_per_join": 18209,
      "filtered": "37.70",
      "cost_info": {
        "read_cost": "6180.60",
        "eval_cost": "3642.00",
        "prefix_cost": "9822.60",
        "data_read_per_join": "14M"
      },
      "used_columns": [
        "id",
        "sex",
        "name",
        "age",
        "birthday"
      ],
      "attached_condition": "(`test`.`user`.`birthday` between '2000-01-01' and '2020-11-01')"
    }
  }
}

查询计划显示使用了全表扫描（access_type = ALL），而没有选择 index_birthday。可以在上面看到全表扫描的成本是9822.6，这个值是怎么来的呢？这就得提到MYSQL为每个表维护的一系列的统计信息了。可以通过SHOW TABLE STATUS查看表的统计信息。

查看表 user 的统计信息（show table status like 'user';）：

Rows：表中的记录条数。对于MyISAM存储引擎，该值是准确的；对于InnoDB，该值是一个估值。

Data_length：表占用的存储空间字节数。对于MyISAM存储引擎，该值就是数据文件的大小；对于InnoDB引擎，该值就相当于聚簇索引占用的存储空间的大小。所以对于使用InnoDB引擎的表，Data_length = 聚簇索引的页面数量 * 每个页面的大小（默认16k）。

再来算一下上面的全表扫描的总成本9822.6怎么来的：

聚簇索引的页面数量(IO读取的页面数量) = 2637824 ÷ 16 ÷ 1024 = 161 
I/O成本：161 * 1.0 = 161 
CPU成本：48308 * 0.2 = 9661.6 
总成本：161 + 9661.6 = 9822.6

2、innodb_index_stats : 索引的统计信息

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(199) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0

innodb_table_stats ：表统计信息

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(199) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0

database_name : 数据库名
table_name : 数据表名
last_update : 本条记录的最后更新时间
n_rows : 该表的记录条数
clustered_index_size : 该表聚簇索引使用的页面数量
sum_of_other_index_sizes : 该表其他索引使用的页面数量

在InnoDB存储引擎下，其对表中记录数量的统计值n_rows是不准确的。

其统计方法是先通过算法选取若干个(聚簇索引的)叶子节点页面，然后计算叶子节点页面中记录数量的均值，最后将均值乘以(聚簇索引的)叶子节点的数量得到n_rows值。故其不是一个精确值，而是一个估计值。

在计算均值过程中，如果选取的叶子节点越多，则n_rows值越准确。故在MySQL中，可通过系统变量innodb_stats_persistent_sample_pages来设置在计算永久性的统计数据时统计过程所需的页面采样数量。显然innodb_stats_persistent_sample_pages值越大，统计过程所需耗时也就越多。

三、对象信息系统表

1、plugin：记录安装的插件的表。

2、event: 存储定时器的表

查看定时器：

SELECT * FROM mysql.event;

开启定时器 0：off 1：on 
SET GLOBAL event_scheduler = 1;

3、func：存储定义函数的表

表结构如下:

CREATE TABLE `func` (
`name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`ret` tinyint(1) NOT NULL DEFAULT '0',
`dl` char(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`type` enum('function','aggregate') CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User defined functions'

4、proc：记录存储过程的表。

四、log 系统表

1、general_log：记录通用日志的表。

2、slow_log：记录慢日志的表。

五、系统复制相关的表

1、gtid_executed：存储gtid的值。

CREATE TABLE gtid_executed (
    source_uuid CHAR(36) NOT NULL,
    interval_start BIGINT(20) NOT NULL,
    interval_end BIGINT(20) NOT NULL,
    PRIMARY KEY (source_uuid, interval_start)
)

注意：mysql.gtid_executed表修改时机

mysql作为主库时：

mysql.gtid_executed不是实时更新的，是在binlog发生切换(rotate)的时候更新的，保存直到上一个binlog文件执行过的全部Gtid。

mysql作为从库时：

1）binlog开启同时参数log_slave_updates开启的情况：

进行日志切换的时候进行更新，同主库。

2）binlog关闭或者binlog开启参数log_slave_updates关闭的情况

实时将gtid持久化到mysql.gtid_executed表中。

2、1）slave_master_info表:IO线程信息日志，用于保存从库IO线程连接主库的连接状态、帐号、IP、端口、密码以及IO线程当前读取主库binlog的file和position等信息。

默认保存在master.info文件中，当参数设置master_info_repository=table，会将master.info文件中的信息写入该表。

参数sync_master_info 控制表slave_master_info多久更新一次，默认是10000，即每10000个events更新一次。

2）slave_relay_log_info表: 记录SQL线程读取Master binlog的位置，用于Slave 宕机后根据文件中记录的pos点恢复Sql线程。

默认保存在relay-log.info文件中，当参数设置 relay-log-info-repository = TABLE，会将relay-log.info 保存在mysql.slave_relay_log_info表。

参数sync-relay-log-info =N ,

当表mysql.slave_relay_log_info为事务引擎比如innodb时，每个事务之后都更新表；
当表mysql.slave_relay_log_info 为非事务引擎(myisam)时，N个event后更新表。

sync-relay-log-info =N的 mysql 官方手册：

sync_relay_log_info = N > 0

If relay_log_info_repository is set to FILE, the replica synchronizes its relay-log.info file to disk (using fdatasync()) after every N transactions.

If relay_log_info_repository is set to TABLE, and the storage engine for that table is transactional, the table is updated after each transaction. (The sync_relay_log_info setting is effectively ignored in this case.)

If relay_log_info_repository is set to TABLE, and the storage engine for that table is not transactional, the table is updated after every N events.