在上一篇《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:认证插件的名称