Server 层混杂信息字典表 | 全方位认识 information_schema（上）

Original 罗小波·沃趣科技沃趣技术 2023-03-10

作者罗小波·沃趣科技高级数据库技术专家

出品沃趣科技

在上一篇《Server层表级别对象字典表 | 全方位认识 information_schema》中，我们详细介绍了information_schema系统库的表级别对象字典表，本期我们将为大家带来系列第四篇《Server层混杂信息字典表 | 全方位认识 information_schema》，下面请跟随我们一起开始information_schema系统库的系统学习之旅吧。

1. GLOBAL_STATUS、GLOBAL_VARIABLES、SESSION_STATUS、SESSION_VARIABLES

GLOBAL_STATUS表提供查询具备全局级别的状态变量值，该表中的值对应着show global status;语句的输出信息

SESSION_STATUS表提供查询会话级别的状态变量值（如果某个状态变量不具备会话级别，则显示全局级别变量值），该表中的值对应着show session status;或show status;语句的输出信息

GLOBAL_VARIABLES表提供具备全局级别的系统变量值，该表中对应着show global variables;语句的输出信息

SESSION_VARIABLES表提供会话级别的系统变量值（如果某个系统变量不具备会话级别，则显示全局级别变量值），该表中的值对应着show session variables;或show variables;语句的输出信息

PS：

从MySQL 5.7.6版本起，information_schema.global_status表中是否记录数据依赖于系统变量show_compatibility_56如何设置，如果设置为OFF，则记录内容迁移到performance_schema.global_status表中，information_schema.global_status表不再记录
这些表为Memory引擎临时表

下面是该表中存储的信息内容

# GLOBAL_STATUS表
root@localhost Wed Jan 24 00:12:23 2018 00:12:23 [(none)]>select * from information_schema.global_status limit 2;
+------------------+----------------+
| VARIABLE_NAME    | VARIABLE_VALUE |
+------------------+----------------+
| ABORTED_CLIENTS  | 1              |
| ABORTED_CONNECTS | 0              |
+------------------+----------------+
2 rows in set (0.00 sec)

# SESSION_STATUS表
root@localhost Wed Jan 24 00:25:59 2018 00:25:59 [(none)]>select * from information_schema.session_status limit 2;
+------------------+----------------+
| VARIABLE_NAME    | VARIABLE_VALUE |
+------------------+----------------+
| ABORTED_CLIENTS  | 2              |
| ABORTED_CONNECTS | 0              |
+------------------+----------------+
2 rows in set (0.00 sec)

# GLOBAL_VARIABLES表
root@localhost Wed Jan 24 00:25:56 2018 00:25:56 [(none)]>select * from information_schema.global_variables limit 2;
+-------------------------+----------------+
| VARIABLE_NAME          | VARIABLE_VALUE |
+-------------------------+----------------+
| MAX_PREPARED_STMT_COUNT | 16382          |
| OPTIMIZER_PRUNE_LEVEL  | 1              |
+-------------------------+----------------+
2 rows in set (0.00 sec)

# SESSION_VARIABLES表
root@localhost Wed Jan 24 00:26:22 2018 00:26:22 [(none)]>select * from information_schema.session_variables limit 2;
+-------------------------+----------------+
| VARIABLE_NAME          | VARIABLE_VALUE |
+-------------------------+----------------+
| MAX_PREPARED_STMT_COUNT | 16382          |
| OPTIMIZER_PRUNE_LEVEL  | 1              |
+-------------------------+----------------+
2 rows in set (0.00 sec)

字段含义如下：

VARIABLE_NAME：全局状态变量名称
VARIABLE_VALUE：全局状态变量统计值

对于系统变量，查询的方式还可以使用 "select语句+@@variable_name" 的方式，如下：

# 查询全局级别系统变量
select @@global.optimizer_switch;

# 查询会话级别系统变量
select @@session.optimizer_switch;
select @@optimizer_switch;

2. OPTIMIZER_TRACE

该表提供优化程序跟踪功能产生的信息。跟踪功能默认关闭，使用optimizer_trace系统变量启用跟踪功能

如果开启该功能，则每个会话只能跟踪他自己执行的语句，不能看到其他会话执行的语句，且每个会话只能记录最后一个跟踪的SQL语句
该表为InnoDB引擎临时表

下面是该表中存储的信息内容

# 启用trace功能
root@localhost : (none) 11:45:21> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

# 执行查询语句
root@localhost : (none) 11:55:02> select * from sbtest.sbtest1 where id=1;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-----+
| id | k      | c                                                                                                                      | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-----+
|  1 | 2493947 | 44401167605-95921109806-49205991371-78375263823-83309869774-25157184837-97554765438-15989585205-62089403228-04207686848 | xxx |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-----+
1 row in set (0.00 sec)

# 查询 INFORMATION_SCHEMA.OPTIMIZER_TRACE表
root@localhost : (none) 11:57:28> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;
*************************** 1. row ***************************
                        QUERY: select * from sbtest.sbtest1 where id=1
                        TRACE: {
"steps": [
{
  "join_preparation": {
    "select#": 1,
    "steps": [
      {
        "expanded_query": "/* select#1 */ select `sbtest`.`sbtest1`.`id` AS `id`,`sbtest`.`sbtest1`.`k` AS `k`,`sbtest`.`sbtest1`.`c` AS `c`,`sbtest`.`sbtest1`.`pad` AS `pad` from `sbtest`.`sbtest1` where (`sbtest`.`sbtest1`.`id` = 1)"
......
  "join_optimization": {
    "select#": 1,
    "steps": [
      {
        "condition_processing": {
          "condition": "WHERE",
          "original_condition": "(`sbtest`.`sbtest1`.`id` = 1)",
          "steps": [
            {
              "transformation": "equality_propagation",
              "resulting_condition": "multiple equal(1, `sbtest`.`sbtest1`.`id`)"
            },
            {
              "transformation": "constant_propagation",
              "resulting_condition": "multiple equal(1, `sbtest`.`sbtest1`.`id`)"
            },
            {
              "transformation": "trivial_condition_removal",
              "resulting_condition": "multiple equal(1, `sbtest`.`sbtest1`.`id`)"
......
        "substitute_generated_columns": {
......
        "table_dependencies": [
          {
            "table": "`sbtest`.`sbtest1`",
            "row_may_be_null": false,
            "map_bit": 0,
            "depends_on_map_bits": [
......
        "ref_optimizer_key_uses": [
          {
            "table": "`sbtest`.`sbtest1`",
            "field": "id",
            "equals": "1",
            "null_rejecting": false
......
        "rows_estimation": [  # 通常， 我们需要特别留意这一段信息中的rows和cost的数值，代表着语句扫描的数据行数和成本开销
          {
            "table": "`sbtest`.`sbtest1`",
            "rows": 1,
            "cost": 1,
            "table_type": "const",
            "empty": false
......
        "condition_on_constant_tables": "1",
        "condition_value": true
      },
      {
        "attaching_conditions_to_tables": {
          "original_condition": "1",
          "attached_conditions_computation": [
          ],
          "attached_conditions_summary": [
......
        "refine_plan": [
......
  "join_execution": {
    "select#": 1,
    "steps": [
......
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
      INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

# 使用完trace功能之后，记得关闭。关闭之后该表中仍然会记录着关闭之前的最后一条跟踪记录
root@localhost : (none) 11:57:40> SET optimizer_trace="enabled=off";
Query OK, 0 rows affected (0.00 sec)

字段含义如下：

QUERY：被跟踪的SQL语句文本
TRACE：JSON格式的跟踪信息
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：optimizer_trace_max_mem_size系统变量(默认16384字节)设置跟踪SQL语句时用于存放跟踪信息的内存总量限制，当跟踪的信息超过了该变量设置的值时，则将会被截断（记录不完整），在OPTIMIZER_TRACE表中的MISSING_BYTES_BEYOND_MAX_MEM_SIZE字段用于记录被截断的字节数
INSUFFICIENT_PRIVILEGES：被跟踪的SQL如果是带有"SQL SECURITY DEFINER"语句的视图或存储过程时，可能会因为跟踪该SQL的会话对应的用户缺少执行权限而被拒绝执行该SQL，在这种情况下，跟踪信息为空，在OPTIMIZER_TRACE表中的INSUFFICIENT_PRIVILEGES列将显示数字“1”

3. PLUGINS

该表提供查询关于MySQL Server中支持哪些插件的信息

该表为InnoDB引擎临时表

下面是该表中存储的信息内容

admin@localhost : information_schema 06:06:32> select * from PLUGINS limit 1\G;
*************************** 1. row ***************************
      PLUGIN_NAME: binlog
    PLUGIN_VERSION: 1.0
    PLUGIN_STATUS: ACTIVE
      PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 50718.0
    PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
    PLUGIN_AUTHOR: MySQL AB
PLUGIN_DESCRIPTION: This is a pseudo storage engine to represent the binlog in a transaction
    PLUGIN_LICENSE: GPL
      LOAD_OPTION: FORCE
1 rows in set (0.00 sec)

字段含义如下（该表中所有字段都为 "MySQL extension" 列）

PLUGIN_NAME：插件名称，该名称可被用于：install plugin和uninstall plugin语句执行插件安装和卸载（plugin_name）
PLUGIN_VERSION：插件通用类型描述符的版本
PLUGIN_STATUS：插件状态，有效值为：ACTIVE、INACTIVE、DISABLED、DELETED
PLUGIN_TYPE：插件类型，有效值为：STORAGE ENGINE、INFORMATION_SCHEMA、AUTHENTICATION.
PLUGIN_TYPE_VERSION：插件类型描述符的版本
PLUGIN_LIBRARY：插件的共享库文件的名称。该名称可被用于INSTALL PLUGIN和UNINSTALL PLUGIN等语句执行安装与卸载插（soname）件。该文件位于plugin_dir系统变量指定的目录中。如果插件库名称为NULL，则表示该插件是被编译到发行版本中默认支持的，且不能使用UNINSTALL语句卸载该插件
PLUGIN_LIBRARY_VERSION：插件API接口版本
PLUGIN_AUTHOR：认证插件的名称
PLUGIN_DESCRIPTION：关于插件的间简短描述信息
PLUGIN_LICENSE：表示插件许可方式，例如：GPL
LOAD_OPTION：表示如何加载插件，有效值为：OFF、ON、FORCE、FORCE_PLUS_PERMANENT

PS：该表中的信息也可以使用show语句查询（但该语句能查询的信息非常有限），如下：

root@localhost : information_schema 02:10:36> SHOW PLUGINS;
+----------------------------+----------+--------------------+---------+---------+
| Name                      | Status  | Type              | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                    | ACTIVE  | STORAGE ENGINE    | NULL    | GPL    |
| mysql_native_password      | ACTIVE  | AUTHENTICATION    | NULL    | GPL    |
| sha256_password            | ACTIVE  | AUTHENTICATION    | NULL    | GPL    |
......
44 rows in set (0.00 sec)

4. PROCESSLIST

该表提供查询一些关于线程运行过程中的状态信息

与SHOW processlist；语句输出信息一样，如果你没有process权限，则只能看到你自己的线程信息，如果有super权限，则可以看到所有其他用户的线程信息，如果是匿名用户，则不能看到任何线程信息
INFORMATION_SCHEMA.PROCESSLIST表中只记录线程当前正在执行的语句信息，一旦语句执行完成，或者是多语句的事务中，先执行完成的语句，在该表中是无法查看到的
processlist信息也可从perform_schema.threads表中获取。并且查询该表中的线程信息不需要互斥体，对服务器性能的影响最小(查询INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST语句需要互斥体，因此会一定程度上影响性能)，该表中还包含后台线程的信息(INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST不显示后台线程信息)。threads表能够获取渠道不能获取的信息，也就意味着可以用该表中的信息来监听其他所有线程的活动情况
该表为InnoDB引擎临时表
PS：
* SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST和SHOW FULL PROCESSLIST两个语句查询的结果集完全相同
* 另外还可以使用mysqladmin processlist命令查看线程信息
* 在使用show命令查看线程信息时，如果你不使用show full子句，则info列只会显示语句的前100个字节字符串
* 在尝试连接MySQL时报"too many connections"时，可以使用带有super权限的用户(前提是你没有把super权限分配给管理员之外的其他用户)连接到MySQL中，使用这些语句查看是什么用户或者主机占用了过多的连接数
* 线程信息中的ID列，可以用kill语句杀死(但需要用户具有super权限)，kill query id;只杀死该线程正在执行的查询语句，kill id;可以把该线程的会话连接杀死，当kill一个线程或者一个查询时，会释放掉占用的资源并回滚正在执行的操作（例如：删除临时表，释放锁，回滚DML事务操作等，但不支持事务的DML语句除外），所以根据具体执行的语句不同kill掉一个连接或者查询的时间开销不同。详见链接：https://dev.mysql.com/doc/refman/5.7/en/kill.html

下面是该表中存储的信息内容

admin@localhost : information_schema 06:06:57> select * from PROCESSLIST;
+----+-------+-------------------+--------------------+------------------+-------+---------------------------------------------------------------+---------------------------+
| ID | USER  | HOST              | DB                | COMMAND          | TIME  | STATE                                                        | INFO                      |
+----+-------+-------------------+--------------------+------------------+-------+---------------------------------------------------------------+---------------------------+
|  4 | admin | localhost        | information_schema | Query            |    0 | executing                                                    | select * from PROCESSLIST |
|  9 | admin | localhost        | performance_schema | Sleep            |  8900 |                                                              | NULL                      |
|  3 | qfsys | 10.10.20.15:60481 | NULL              | Binlog Dump GTID | 34076 | Master has sent all binlog to slave; waiting for more updates | NULL                      |
+----+-------+-------------------+--------------------+------------------+-------+---------------------------------------------------------------+---------------------------+
3 rows in set (0.00 sec)

表字段含义（该表中所有字段都为 "MySQL extension" 列）

ID：连接进程标识符。这与在INFORMATION_SCHEMA.PROCESSLIST表的ID列，performance_schema.threads表的PROCESSLIST_ID列中显示的值是相同的值，都是由CONNECTION_ID（）函数返回的值
USER：执行语句的MySQL用户名称。如果显示的是“system user”，它指的是由服务器生成的非客户端线程正在执行内部任务。例如主备复制中从库上使用的I/O或SQL线程或延迟行处理程序的线程。“unauthenticated user”指的是已经建立客户端连接但是还没有对客户端连接的用户进行客户端用户的认证的线程。 “event_scheduler”是指监视计划任务调度事件的线程。对于“system user”，在Host列中显示为Null值
HOST：执行语句的客户端的主机名（除了没有主机信息的“system user”之外）。 SHOW PROCESSLIST的Host列以host_name：client_port格式显示TCP/IP连接的主机名，以便更容易确定哪个客户端正在做什么事情
DB：客户端连接的默认数据库（如果连接时指定了库名），否则显示为NULL值
COMMAND：线程正在执行的命令的类型。此列的值对应于C/S协议和Com_xxx状态变量的COM_xxx命令。
TIME：线程处于当前状态的时间数（以秒为单位）。对于从库SQL线程，该值是最后复制事件的时间戳和从库的实际时间之间的秒数（也可以理解为事件等待的时间）
STATE：提示线程正在做什么样的操作，事件或状态。大多数状态所对应于的操作都执行的非常快。如果线程停留在某个状态很长时间，则表名该线程可能执行过程中碰到了某个问题，需要进行排查。对于SHOW PROCESSLIST语句，State列的值始终为NULL
INFO：线程正在执行的语句，如果没有执行任何语句，则显示为NULL。语句可以是发送到服务器的语句，或者如果语句内部调用执行其他语句，即指的最内层调用的语句。例如，如果CALL语句调用存储过程，而存储过程中执行SELECT语句，则Info值将显示存储过程中的SELECT语句

PS：该表中的信息还可以使用show [full] processlist;语句查看

root@localhost : information_schema 02:30:15> show full processlist\G;
*************************** 1. row ***************************
Id: 4
User: qfsys
Host: 192.168.2.120:48489
db: NULL
Command: Binlog Dump
Time: 15548
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
............
2 rows in set (0.00 sec)