在上一篇《Server层表级别对象字典表 | 全方位认识 information_schema》中,我们详细介绍了information_schema系统库的表级别对象字典表,本期我们将为大家带来系列第四篇《Server层混杂信息字典表 | 全方位认识 information_schema》,下面请跟随我们一起开始information_schema系统库的系统学习之旅吧。
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:
下面是该表中存储的信息内容
# 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)
字段含义如下:
对于系统变量,查询的方式还可以使用 "select语句+@@variable_name" 的方式,如下:
# 查询全局级别系统变量
select @@global.optimizer_switch;
# 查询会话级别系统变量
select @@session.optimizer_switch;
select @@optimizer_switch;
该表提供优化程序跟踪功能产生的信息。 跟踪功能默认关闭,使用optimizer_trace系统变量启用跟踪功能
下面是该表中存储的信息内容
# 启用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)
字段含义如下:
该表提供查询关于MySQL Server中支持哪些插件的信息
下面是该表中存储的信息内容
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" 列)
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)
该表提供查询一些关于线程运行过程中的状态信息
下面是该表中存储的信息内容
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" 列)
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)
限于篇幅,本期就先为大家介绍到这,下期《Server 层混杂信息字典表 | 全方位认识 information_schema(中)》将继续为大家介绍。
| 作者简介
《千金良方——MySQL性能优化金字塔法则》、《数据生态:MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构,擅长数据库的整体调优,喜好专研开源技术,并热衷于开源技术的推广,在线上线下做过多次公开的数据库专题分享,发表过近100篇数据库相关的研究文章。
全文完。