MySQL运维内参读书笔记-Day2

一、监控

工具篇

工具：天兔*（复制监控、表空间分析、数据库健康状态、告警系统、进程监控、慢查询分析、InnoDB监控、AWR报告）

首选推荐，金融级别DBA都在用，还是开源的产品。好不好，不用我说了吧~

Monyog（非常轻量级，可以部署windows平台，告警触发、权限预警比较好用。亚马逊xx产品都在用）、

Zabbix（可定制化非常高，只要玩的好，什么都能监控，对MySQL的内部监控实现起来略微复杂，时间维度高，所以用它来监测MySQL不推荐）

spotlight on mysql （没怎么玩过，UI做的好~，做压测时候，可以用它来做报表截图，哈哈）

do DBA  tools、命令行式实时监控，支持ssh，弥补了只有报表没有具体数据的尴尬，远程抓取linux的数据，进行解析。类似于抓包了。

MySQL之前版本的监控，如果不使用工具，没有报警机制，预警的性能指标，24小时救火对DBA来说是身心疲惫，用开源产品缺点就是定制化困难，脚本好一点呢团队大呢，就是左一个脚本，右一个脚本。甚至有的用ruby，有的用python。有的甚至用java. 

如果脚本不好的DBA，也可以找移动端开发的童学，做一款移动监控的小程序，跟数据库做交互，是不是很爽。只要需求明确，关键监控指标数据接口给到开发，接入到小程序端，就可以随时随地收到服务器的告警了。在可控的的情况下，如果开发精力比较旺盛，可以定制一些自动化触发机制，最基本的让程序自动改变mysql的临时连接数。自动kill掉，滥用资源的大事务。如果后期我们的数据库日志足够庞大的时候，可以利用成熟的BI机制，AI机制。更好的让程序自动审核，防住拖库高危SQL..并且通知到相对应的开发。以及服务器硬件性能告警，做出自动扩容等措施。

内控篇

MySQL5.7以后监控是越来越成熟了，提供了比较强大的Performance sehema的库，内置了80多张表。很多的监控指标MySQL自己完成了，并且存入表里面，可以通过sql可以很直观的进行监控。

MySQL Performance Schema  用于监视MySQL服务器，且运行时消耗很少的性能。Performance Schema 收集数据库服务器性能参数，并且表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。Performance Schema 具有以下特征：

Performance Schema 提供了一种在服务器运行时检查服务器的内部执行的方法。它使用PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现。性能模式主要关注性能数据。这与用于检查元数据的INFORMATION_SCHEMA不同。

Performance Schema 事件特定于MySQL服务器的给定实例。 Performance Schema 表被视为本地服务器，并且对其进行的更改不会被复制或写入二进制日志。

Performance Schema 中的表是内存表，不使用磁盘存储，在 datadir 的 performance_schema 目录下，只有.frm表结构文件，没有数据文件。表内容在服务器启动时重新填充，并在服务器关闭时丢弃。

数据收集是通过修改服务器源代码来实现的。 不同于其他功能（如复制或Event Scheduler），不存在与Performance Schema相关联的单独线程。

服务器监控持续不中断地进行，花费很少。 开启Performance Schema不会使服务器不可用。

从MySQL5.6开始，Performance Schema 默认打开，这里讲述一些在数据库使用当中PERFORMANCE_SCHEMA的一些比较常用的功能。具体的信息可以查看（https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html）

阅读目录

CHARACTER_SETS

COLLATIONS

COLLATION_CHARACTER_SET_APPLICABILITY

COLUMNS

COLUMN_PRIVILEGES

ENGINES

EVENTS

FILES

GLOBAL_STATUS & SESSION_STATUS & GLOBAL_VARIABLES & SESSION_VARIABLES

KEY_COLUMN_USAGE

OPTIMIZER_TRACE

PARAMETERS

PARTITIONS

PLUGINS

PROCESSLIST

PROFILING

REFERENTIAL_CONSTRAINTS

ROUTINES

SCHEMATA

SCHEMA_PRIVILEGES

STATISTICS

TABLES

TABLESPACES

TABLE_CONSTRAINTS

TABLE_PRIVILEGES

TRIGGERS

USER_PRIVILEGES

VIEWS

InnoDB Tables

refman mysql 5.7

INFORMATION_SCHEMA提供了对数据库元数据的访问，MySQL服务器信息，如数据库或表的名称，列的数据类型，访问权限等。 有时也把这些信息叫做数据字典或系统目录。

每个数据库实例都会有一个 INFORMATION_SCHEMA 库，保存的是本实例下其他所有库的信息。INFORMATION_SCHEMA数据库包含多个只读表。 它们实际上是视图，而不是基础表，所以没有与它们关联的文件，并且你不能在它们上设置触发器。此外，数据库目录下也没有该库的目录。

虽然可以使用USE语句将INFORMATION_SCHEMA选择为缺省数据库，但只能读取表的内容，不能对它们执行INSERT，UPDATE或DELETE操作。

每个MySQL用户都可以访问 INFORMATION_SCHEMA，但是只能看到自己有权限的那些行。

CHARACTER_SETS

• 字符集：对 Unicode 的一套编码。

• collation：用于指定数据集如何排序，以及字符串的比对规则。

CHARACTER_SETS表提供了有关可用字符集的信息。

> desc CHARACTER_SETS;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME | varchar(32) | NO | | | |
| DEFAULT_COLLATE_NAME | varchar(32) | NO | | | |
| DESCRIPTION | varchar(60) | NO | | | |
| MAXLEN | bigint(3) | NO | | 0 | |
+----------------------+-------------+------+-----+---------+-------+
> select * from CHARACTER_SETS where CHARACTER_SET_NAME like 'utf8%';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
| utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 |
+--------------------+----------------------+---------------+--------+

COLLATIONS

COLLATIONS表提供有关每个字符集的排序规则的信息。

> desc COLLATIONS;

+--------------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------------------+-------------+------+-----+---------+-------+| COLLATION_NAME | varchar(32) | NO | | | || CHARACTER_SET_NAME | varchar(32) | NO | | | || ID | bigint(11) | NO | | 0 | || IS_DEFAULT | varchar(3) | NO | | | || IS_COMPILED | varchar(3) | NO | | | || SORTLEN | bigint(3) | NO | | 0 | |+--------------------+-------------+------+-----+---------+-------+

COLLATION_NAME is the collation name.

CHARACTER_SET_NAME is the name of the character set with which the collation is associated.

ID is the collation ID.

IS_DEFAULT indicates whether the collation is the default for its character set.

IS_COMPILED indicates whether the character set is compiled into the server.

SORTLEN is related to the amount of memory required to sort strings expressed in the character set.

每个character set会对应一定数量的collation：

> select * from COLLATIONS where COLLATION_NAME like 'utf8%general%';
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN 
|+--------------------------+--------------------+-----+------------+-------------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 || utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 || utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 
|+--------------------------+--------------------+-----+------------+-------------+---------+

ci 是 case insensitive 的缩写（大小写敏感）， cs 是 case sensitive 的缩写。即，指定大小写是否敏感。

COLLATION_CHARACTER_SET_APPLICABILITY

COLLATION_CHARACTER_SET_APPLICABILITY表显示了哪种字符集适用于哪种排序方式。这些列相当于我们从SHOW COLLATION获得的前两个字段。

> desc COLLATION_CHARACTER_SET_APPLICABILITY;
+--------------------+-------------+------+-----+---------+-------+| 
Field | Type | Null | Key | Default | Extra 
|+--------------------+-------------+------+-----+---------+-------+
| COLLATION_NAME | varchar(32) | NO | | | || CHARACTER_SET_NAME | varchar(32) | NO | | | 
|+--------------------+-------------+------+-----+---------+-------+

COLUMNS

COLUMNS表提供表格中列的信息。

> desc COLUMNS;
+--------------------------+---------------------+------+-----+---------+-------+| 
Field | Type | Null | Key | Default | Extra 
|+--------------------------+---------------------+------+-----+---------+-------+| 
TABLE_CATALOG | varchar(512) | NO | | | || TABLE_SCHEMA | varchar(64) | NO | | | || 
TABLE_NAME | varchar(64) | NO | | | || COLUMN_NAME | varchar(64) | NO | 
| | || ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | || COLUMN_DEFAULT | longtext | YES | | NULL | 
|| IS_NULLABLE | varchar(3) | NO | | | || DATA_TYPE | varchar(64) | NO | | | || CHARACTER_MAXIMUM_LENGTH 
| bigint(21) unsigned | YES | | NULL | || CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | 
|| NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | || NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL |
 || DATETIME_PRECISION | bigint(21) unsigned | YES | | NULL | || CHARACTER_SET_NAME | varchar(32) | YES | | NULL | 
 || COLLATION_NAME | varchar(32) | YES | | NULL | || COLUMN_TYPE | longtext | NO | | NULL | || COLUMN_KEY | varchar(3) 
 | NO | | | || EXTRA | varchar(30) | NO | | | || PRIVILEGES | varchar(80) | NO | | | || COLUMN_COMMENT | varchar(1024) 
 | NO | | | || GENERATION_EXPRESSION | longtext | NO | | NULL | 
 |+--------------------------+---------------------+------+-----+---------+-------+21 rows in set (0.01 sec)

COLUMN_PRIVILEGES

COLUMN_PRIVILEGES表提供有关列权限的信息。 这些信息来自mysql.columns_priv授权表。

> desc COLUMN_PRIVILEGES;

+----------------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------------+--------------+------+-----+---------+-------+| GRANTEE | varchar(81) | NO | | | || TABLE_CATALOG | varchar(512) | NO | | | || TABLE_SCHEMA | varchar(64) | NO | | | || TABLE_NAME | varchar(64) | NO | | | || COLUMN_NAME | varchar(64) | NO | | | || PRIVILEGE_TYPE | varchar(64) | NO | | | || IS_GRANTABLE | varchar(3) | NO | | | |+----------------+--------------+------+-----+---------+-------+

PRIVILEGE_TYPE 可以包含一个（且只能有一个）这些值：SELECT，INSERT，UPDATE，REFERENCES。

如果用户具有GRANT OPTION权限，则IS_GRANTABLE为YES。 否则，IS_GRANTABLE 为NO。 输出不会将GRANT OPTION列为单独的权限。

ENGINES

ENGINES表提供了有关存储引擎的信息。

> desc ENGINES;

+--------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------------+-------------+------+-----+---------+-------+| ENGINE | varchar(64) | NO | | | || SUPPORT | varchar(8) | NO | | | || COMMENT | varchar(80) | NO | | | || TRANSACTIONS | varchar(3) | YES | | NULL | || XA | varchar(3) | YES | | NULL | || SAVEPOINTS | varchar(3) | YES | | NULL | |+--------------+-------------+------+-----+---------+-------+

ENGINES表是非标准表，其内容对应于SHOW ENGINES语句的列。

EVENTS

EVENTS表提供了有关定时事件（scheduled events）的信息，events类似与linux crontab计划任务，用于时间触发。通过单独或调用存储过程使用，在某一特定的时间点，触发相关的SQL语句或存储过程。

> desc events;

+----------------------+---------------+------+-----+---------------------+-------+| Field | Type | Null | Key | Default | Extra |+----------------------+---------------+------+-----+---------------------+-------+| EVENT_CATALOG | varchar(64) | NO | | | || EVENT_SCHEMA | varchar(64) | NO | | | || EVENT_NAME | varchar(64) | NO | | | || DEFINER | varchar(77) | NO | | | || TIME_ZONE | varchar(64) | NO | | | || EVENT_BODY | varchar(8) | NO | | | || EVENT_DEFINITION | longtext | NO | | NULL | || EVENT_TYPE | varchar(9) | NO | | | || EXECUTE_AT | datetime | YES | | NULL | || INTERVAL_VALUE | varchar(256) | YES | | NULL | || INTERVAL_FIELD | varchar(18) | YES | | NULL | || SQL_MODE | varchar(8192) | NO | | | || STARTS | datetime | YES | | NULL | || ENDS | datetime | YES | | NULL | || STATUS | varchar(18) | NO | | | || ON_COMPLETION | varchar(12) | NO | | | || CREATED | datetime | NO | | 0000-00-00 00:00:00 | || LAST_ALTERED | datetime | NO | | 0000-00-00 00:00:00 | || LAST_EXECUTED | datetime | YES | | NULL | || EVENT_COMMENT | varchar(64) | NO | | | || ORIGINATOR | bigint(10) | NO | | 0 | || CHARACTER_SET_CLIENT | varchar(32) | NO | | | || COLLATION_CONNECTION | varchar(32) | NO | | | || DATABASE_COLLATION | varchar(32) | NO | | | |+----------------------+---------------+------+-----+---------------------+-------+24 rows in set (0.00 sec)

• The EVENTS table is a nonstandard table.  # EVENTS表是非标准表

• EVENT_CATALOG: The value of this column is always def.

• EVENT_SCHEMA: # events 所属的库

• EVENT_NAME: # event 名称

• DEFINER:  # 创建EVENT的账号，'user_name'@'host_name'的格式

• TIME_ZONE: The event time zone, which is the time zone used for scheduling the event and that is in effect within the event as it executes. The default value is SYSTEM.

• EVENT_BODY: The language used for the statements in the event's DO clause; in MySQL 5.7, this is always SQL. This column is not to be confused with the column of the same name (now named EVENT_DEFINITION) that existed in earlier MySQL versions.  # 不要与早期版本的同名项（现在是EVENT_DEFINITION）混淆

• EVENT_DEFINITION: # 时间需要执行的部分，即组成时间的Do子句。

• EVENT_TYPE:  #事件重复类型，一次（transient）或重复（repeating）。

• EXECUTE_AT: #对于一次性事件，这是在用于创建事件的CREATE EVENT语句的AT子句中指定的DATETIME值；如果事件的计时由EVERY子句而不是AT子句确定（也就是说，事件类型为repeating），则此列的值为NULL。

• INTERVAL_VALUE:  #对于定期事件，此列包含事件的EVERY子句的数字部分。 对于一次性事件（即其定时由AT子句确定的事件），此列为NULL。

• INTERVAL_FIELD:  #对于定期事件，此列包含EVERY子句的单位部分，用于管理事件的时间。因此，此列包含“YEAR”，“QUARTER”，“DAY”等值。对于一次性事件（即其定时由AT子句确定的事件），此列为NULL

• SQL_MODE:   #在创建或更改事件并在其下执行事件时有效的SQL模式。

• STARTS: #事件定义时包含了STARTS子句的事件，此列包含相应的DATETIME值。 与EXECUTE_AT列一样，此值可解析所使用的任何表达式。 如果没有影响事件时间的STARTS子句，则此列为NULL

• ENDS:  #事件定义时包含了ENDS子句的循环事件，此列包含相应的DATETIME值。 与EXECUTE_AT列一样，此值可解析所使用的任何表达式。 如果没有影响事件时间的ENDS子句，则此列为NULL。

• STATUS:  #ENABLE, DISABLE或SLAVESIDE_DISABLED 三个值之一。 SLAVESIDE_DISABLED表示事件的创建发生在另一个作为复制主服务器的MySQL服务器上，并被复制到当前作为从服务器的MySQL服务器上，但事件目前不在从服务器上执行。

• ON_COMPLETION: One of the two values PRESERVE or NOT PRESERVE.

• CREATED:  #事件创建时间

• LAST_ALTERED:  #事件最后修改时间

• LAST_EXECUTED:  #事件最后一次执行的时间

• EVENT_COMMENT: #事件的注释

• ORIGINATOR:  #在其上创建事件的MySQL服务器的服务器ID; 用于复制。 默认值是0。

• CHARACTER_SET_CLIENT: #事件创建时character_set_client系统变量的session值

• COLLATION_CONNECTION: #事件创建时collation_connection系统变量的session值

• DATABASE_COLLATION:  #与事件关联的数据库的排序规则。

FILES

FILES表提供有关存储MySQL表空间数据的文件信息。

INFORMATION_SCHEMA.FILES提供有关InnoDB数据文件信息。在MySQL Cluster中，此表还提供有关存储MySQL Cluster磁盘数据表的文件信息。

> desc FILES;

+----------------------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------------------+---------------------+------+-----+---------+-------+| FILE_ID | bigint(4) | NO | | 0 | || FILE_NAME | varchar(4000) | YES | | NULL | || FILE_TYPE | varchar(20) | NO | | | || TABLESPACE_NAME | varchar(64) | YES | | NULL | || TABLE_CATALOG | varchar(64) | NO | | | || TABLE_SCHEMA | varchar(64) | YES | | NULL | || TABLE_NAME | varchar(64) | YES | | NULL | || LOGFILE_GROUP_NAME | varchar(64) | YES | | NULL | || LOGFILE_GROUP_NUMBER | bigint(4) | YES | | NULL | || ENGINE | varchar(64) | NO | | | || FULLTEXT_KEYS | varchar(64) | YES | | NULL | || DELETED_ROWS | bigint(4) | YES | | NULL | || UPDATE_COUNT | bigint(4) | YES | | NULL | || FREE_EXTENTS | bigint(4) | YES | | NULL | || TOTAL_EXTENTS | bigint(4) | YES | | NULL | || EXTENT_SIZE | bigint(4) | NO | | 0 | || INITIAL_SIZE | bigint(21) unsigned | YES | | NULL | || MAXIMUM_SIZE | bigint(21) unsigned | YES | | NULL | || AUTOEXTEND_SIZE | bigint(21) unsigned | YES | | NULL | || CREATION_TIME | datetime | YES | | NULL | || LAST_UPDATE_TIME | datetime | YES | | NULL | || LAST_ACCESS_TIME | datetime | YES | | NULL | || RECOVER_TIME | bigint(4) | YES | | NULL | || TRANSACTION_COUNTER | bigint(4) | YES | | NULL | || VERSION | bigint(21) unsigned | YES | | NULL | || ROW_FORMAT | varchar(10) | YES | | NULL | || TABLE_ROWS | bigint(21) unsigned | YES | | NULL | || AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | || DATA_LENGTH | bigint(21) unsigned | YES | | NULL | || MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | || INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | || DATA_FREE | bigint(21) unsigned | YES | | NULL | || CREATE_TIME | datetime | YES | | NULL | || UPDATE_TIME | datetime | YES | | NULL | || CHECK_TIME | datetime | YES | | NULL | || CHECKSUM | bigint(21) unsigned | YES | | NULL | || STATUS | varchar(20) | NO | | | || EXTRA | varchar(255) | YES | | NULL | |+----------------------+---------------------+------+-----+---------+-------+38 rows in set (0.00 sec)

InnoDB Notes

以下说明适用于InnoDB数据文件。 下面没有描述的INFORMATION_SCHEMA.FILES字段不适用于InnoDB并报告NULL值。

INFORMATION_CHEMA.FILES报告的数据来自于InnoDB打开文件的内存缓存。相比之下，INFORMATION_SCHEMA.INNODB_SYS_DATAFILES 从InnoDB SYS_DATAFILES内部数据字典表中报告数据。

INFORMATION_SCHEMA.FILES报告的数据包括临时表空间数据。此数据在内部SYS_DATAFILES数据字典表中不可得，因此不由INNODB_SYS_DATAFILES报告。

当使用innodb_undo_tablespaces配置单独的undo表空间时，INFORMATION_SCHEMA.FILES则会报告undo表空间的数据。

FILE_ID指表空间ID，也被称为space_id或fil_space_t :: id。

FILE_NAME  # FILE_NAME是数据文件的名称。 每个表文件和常规表空间都有一个.ibd文件扩展名。undo表空间的前缀为undo。 系统表空间的前缀是ibdata。 临时表空间由ibtmp作为前缀。 文件名包括文件路径，可能与MySQL数据目录（datadir）有关。

FILE_TYPE  # FILE_TYPE是表空间文件类型。 InnoDB文件有三种可能的文件类型。 TABLESPACE是用于保存表，索引或其他形式的用户数据的表空间文件类型。 TEMPORARY是临时表空间的文件类型。 UNDO LOG是undo日志表空间的文件类型，用于保存undo记录。 默认情况下，undo记录存储在系统表空间中。 可以使用innodb_undo_tablespaces选项来添加独立的undo日志表空间。

TABLESPACE_NAME  # TABLESPACE_NAME是表空间的SQL名称。 常规表空间（general tablespace）名称是SYS_TABLESPACES.NAME值。 对于其他表空间文件，名称以innodb_开头，例如innodb_system，innodb_undo和innodb_file_per_table。 file-per-table表空间名称格式是innodb_file_per_table _ ##，其中##是表空间ID。

ENGINE  # ENGINE是指存储引擎。对于InnoDB文件，值总是InnoDB。

innodb 表空间结构：page（页）—>  extent（区）—> segment（段）—> tablespace（表空间）

FREE_EXTENTS  # FREE_EXTENTS是当前数据文件中完全可用的区的数量。

TOTAL_EXTENTS # TOTAL_EXTENTS是当前数据文件中使用的完全区（full extents）的数量，文件末尾的局部区（partial extent）不计算在内。

EXTENT_SIZE  #对于4k，8k或16k页面大小的文件，EXTENT_SIZE是1048576（1MB）。 对于32k页面大小的文件，扩展大小为2097152字节（2MB），对于64k页面大小的文件，扩展大小为4194304（4MB）。 INFORMATION_SCHEMA.FILES不报告InnoDB页面大小。页面大小由innodb_page_size选项定义。扩展大小信息也可以从INNODB_SYS_TABLESPACES中检索到，其中FILES.FILE_ID = INNODB_SYS_TABLESPACES.SPACE_ID。

INITIAL_SIZE # INITIAL_SIZE是文件的初始大小，以字节为单位。

MAXIMUM_SIZE # MAXIMUM_SIZE是文件中允许的最大字节数。 除预定义的系统表空间数据文件外，所有数据文件的值均为NULL。 最大系统表空间文件大小由innodb_data_file_path定义。 最大临时表空间文件大小由innodb_temp_data_file_path定义。 预定义的系统表空间数据文件的NULL值表示没有明确定义文件大小限制。

AUTOEXTEND_SIZE  # AUTOEXTEND_SIZE是由innodb_data_file_path为系统表空间定义的自动扩展大小，或由临时表空间的innodb_temp_data_file_path定义。

DATA_FREE # DATA_FREE是整个表空间的可用空间总量（以字节为单位）。 预定义的系统表空间（包括系统表空间和临时表空间）可能有一个或多个数据文件。

STATUS  # STATUS默认为NORMAL。 InnoDB file-per-table表空间可能会报告IMPORTING，这表明表空间不可用。

NDB Notes

太多了，这里先不列举了，有兴趣的可以自行查看手册。

important

一旦某个extent（区）被使用，delete from 语句不会释放磁盘空间，drop 数据文件才能释放。

GLOBAL_STATUS & SESSION_STATUS & GLOBAL_VARIABLES & SESSION_VARIABLES

GLOBAL_STATUS和SESSION_STATUS表提供有关服务器状态变量的信息。 它们的内容对应于SHOW GLOBAL STATUS和SHOW SESSION STATUS语句产生的信息

GLOBAL_VARIABLES和SESSION_VARIABLES表提供有关服务器状态变量的信息。 它们的内容对应于SHOW GLOBAL VARIABLES和SHOW SESSION VARIABLES语句所产生的信息。

从MySQL 5.7.6开始，这里描述的4张表中的信息也可在Performance Schema中获取。 INFORMATION_SCHEMA表不推荐优先于Performance Schema表，并将在未来的MySQL版本中被删除。（从mysql 5.7.6开始 information_schema.global_status 已经开始被舍弃，为了兼容性，此时需要打开 show_compatibility_56）

> desc GLOBAL_STATUS;

+----------------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------------+---------------+------+-----+---------+-------+| VARIABLE_NAME | varchar(64) | NO | | | || VARIABLE_VALUE | varchar(1024) | YES | | NULL | |+----------------+---------------+------+-----+---------+-------+

KEY_COLUMN_USAGE

KEY_COLUMN_USAGE表描述了哪些键列有约束条件。

如果约束是一个外键，那么这是外键的列，而不是外键引用的列。

ORDINAL_POSITION的值是列在约束中的位置，而不是列在表中的位置。 列位置从1开始编号。

对于唯一主键约束，POSITION_IN_UNIQUE_CONSTRAINT的值为NULL。 对于外键约束，它是被引用的表的键中的序数位置。

OPTIMIZER_TRACE

OPTIMIZER_TRACE表提供了优化器跟踪功能产生的信息。要启用跟踪（tracking），使用optimizer_trace系统变量。

PARAMETERS

PARAMETERS表提供有关存储过程和函数参数的信息，以及有关存储函数的返回值的信息。 参数信息与mysql.proc表中param_list列的内容类似。

PARTITIONS

PARTITIONS表提供有关表分区的信息。 有关分区表的更多信息，请参见第21章分区。

> desc PARTITIONS;

+-------------------------------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------------------------------+---------------------+------+-----+---------+-------+| TABLE_CATALOG | varchar(512) | NO | | | | always def

| TABLE_SCHEMA | varchar(64) | NO | | | | 分区所属的库

| TABLE_NAME | varchar(64) | NO | | | | 分区所属的表

| PARTITION_NAME | varchar(64) | YES | | NULL | | 分区名

| SUBPARTITION_NAME | varchar(64) | YES | | NULL | | 子分区名

| PARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | | 分区的编号

| SUBPARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | | 子分区编号

| PARTITION_METHOD | varchar(18) | YES | | NULL | | 分区类型，RANGE, LIST, HASH, LINEAR HASH, KEY, or LINEAR KEY;

| SUBPARTITION_METHOD | varchar(12) | YES | | NULL | | 子分区类型

| PARTITION_EXPRESSION | longtext | YES | | NULL | | 分区函数的表达式

| SUBPARTITION_EXPRESSION | longtext | YES | | NULL | || PARTITION_DESCRIPTION | longtext | YES | | NULL | | 用于range和list，定义分区范围的值

| TABLE_ROWS | bigint(21) unsigned | NO | | 0 | | 该分区的行数

| AVG_ROW_LENGTH | bigint(21) unsigned | NO | | 0 | | 行的平均长度（字节）= DATA_LENGTH/ TABLE_ROWS

| DATA_LENGTH | bigint(21) unsigned | NO | | 0 | | 所有行的总长度（字节）

| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | 可以存储在此分区或子分区中的最大字节数

| INDEX_LENGTH | bigint(21) unsigned | NO | | 0 | | 索引长度（字节）

| DATA_FREE | bigint(21) unsigned | NO | | 0 | | 分配给分区或子分区但未使用的字节数。

| CREATE_TIME | datetime | YES | | NULL | | 分区创建时间

| UPDATE_TIME | datetime | YES | | NULL | | 分区更新时间

| CHECK_TIME | datetime | YES | | NULL | || CHECKSUM | bigint(21) unsigned | YES | | NULL | || PARTITION_COMMENT | varchar(80) | NO | | | || NODEGROUP | varchar(12) | NO | | | || TABLESPACE_NAME | varchar(64) | YES | | NULL | | 分区所属的表空间

+-------------------------------+---------------------+------+-----+---------+-------+25 rows in set (0.00 sec)

PLUGINS

PLUGINS表提供了有关服务器插件的信息。

> desc PLUGINS;

+------------------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------------------+-------------+------+-----+---------+-------+| PLUGIN_NAME | varchar(64) | NO | | | || PLUGIN_VERSION | varchar(20) | NO | | | || PLUGIN_STATUS | varchar(10) | NO | | | | 插件状态，ACTIVE，INACTIVE，DISABLED或DELETED

| PLUGIN_TYPE | varchar(80) | NO | | | || PLUGIN_TYPE_VERSION | varchar(20) | NO | | | || PLUGIN_LIBRARY | varchar(64) | YES | | NULL | | 插件共享库文件的名称，如server_audit.so，如果为NULL，则插件不能卸除

| PLUGIN_LIBRARY_VERSION | varchar(20) | YES | | NULL | || PLUGIN_AUTHOR | varchar(64) | YES | | NULL | || PLUGIN_DESCRIPTION | longtext | YES | | NULL | || PLUGIN_LICENSE | varchar(80) | YES | | NULL | || LOAD_OPTION | varchar(64) | NO | | | | 如何加载插件，值为OFF，ON，FORCE或FORCE_PLUS_PERMANENT

+------------------------+-------------+------+-----+---------+-------+

PROCESSLIST

PROCESSLIST表提供有关正在运行的线程信息。

> desc PROCESSLIST;

+---------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+---------------------+------+-----+---------+-------+| ID | bigint(21) unsigned | NO | | 0 | || USER | varchar(32) | NO | | | || HOST | varchar(64) | NO | | | || DB | varchar(64) | YES | | NULL | || COMMAND | varchar(16) | NO | | | || TIME | int(7) | NO | | 0 | || STATE | varchar(64) | YES | | NULL | || INFO | longtext | YES | | NULL | |+---------+---------------------+------+-----+---------+-------+

PROFILING

PROFILING表提供语句分析信息。 其内容对应于SHOW PROFILES和SHOW PROFILE声明所产生的信息。

warning：INFORMATION_SCHEMA.PROFILING 在以后的版本将被移除，请使用 Performance Schema 代替。

REFERENTIAL_CONSTRAINTS

REFERENTIAL_CONSTRAINTS表提供有关外键的信息。

ROUTINES

ROUTINES表提供有关routines（存储过程procedures和函数functions）的信息。 ROUTINES表不包括用户定义的函数（UDF）。

SCHEMATA

schema是一个数据库，所以SCHEMATA表提供有关数据库的信息。

SCHEMA_PRIVILEGES

SCHEMA_PRIVILEGES表提供有关schema（数据库）特权的信息。 这些信息来自mysql.db授权表。

STATISTICS

STATISTICS表提供有关表索引的信息。

TABLES

TABLES表提供有关数据库中表的信息。

TABLESPACES

TABLESPACES表提供有关active表空间的信息。

INFORMATION_SCHEMA.TABLESPACES表不提供有关InnoDB表空间的信息。对于InnoDB表空间元数据，请参阅INNODB_SYS_TABLESPACES和INNODB_SYS_DATAFILES。 从MySQL 5.7.8开始，INFORMATION_SCHEMA.FILES表还提供了InnoDB表空间的元数据。

TABLE_CONSTRAINTS

TABLE_CONSTRAINTS表描述哪些表有约束。

CONSTRAINT_TYPE值可以是UNIQUE，PRIMARY KEY或FOREIGN KEY。

当Non_unique字段为0时，UNIQUE和PRIMARY KEY信息与从SHOW INDEX输出中的Key_name字段获得的信息大致相同。

CONSTRAINT_TYPE列可以包含以下值之一：UNIQUE，PRIMARY KEY，FOREIGN KEY，CHECK。 这是一个CHAR（不是ENUM）列。 直到我们支持CHECK，CHECK值才可用。

TABLE_PRIVILEGES

TABLE_PRIVILEGES表提供有关表特权的信息。 这些信息来自mysql.tables_priv授权表。

PRIVILEGE_TYPE可以包含一个（且只能一个）这些值：SELECT，INSERT，UPDATE，REFERENCES，ALTER，INDEX，DROP，CREATE VIEW。

TRIGGERS

TRIGGERS表提供关于触发器的信息。 要查看有关表触发器的信息，您必须具有该表的TRIGGER权限。

USER_PRIVILEGES

USER_PRIVILEGES表提供有关全局权限的信息。 这些信息来自mysql.user授权表。

VIEWS

VIEWS表提供关于数据库中视图的信息。 您必须具有SHOW VIEW权限才能访问此表

InnoDB Tables

另外还有 INNODB 相关的表：

INNODB_LOCKS，INNODB_TRX，INNODB_SYS_DATAFILES，INNODB_FT_CONFIG，INNODB_SYS_VIRTUAL，INNODB_CMP，INNODB_FT_BEING_DELETED，INNODB_CMP_RESET，INNODB_CMP_PER_INDEX，INNODB_CMPMEM_RESET，INNODB_FT_DELETED，INNODB_BUFFER_PAGE_LRU，INNODB_LOCK_WAITS，INNODB_TEMP_TABLE_INFO，INNODB_SYS_INDEXES，INNODB_SYS_TABLES，INNODB_SYS_FIELDS，INNODB_CMP_PER_INDEX_RESET，INNODB_BUFFER_PAGE，INNODB_FT_DEFAULT_STOPWORD，INNODB_FT_INDEX_TABLE，INNODB_FT_INDEX_CACHE，INNODB_SYS_TABLESPACES，INNODB_METRICS，INNODB_SYS_FOREIGN_COLS，INNODB_CMPMEM，INNODB_BUFFER_POOL_STATS，INNODB_SYS_COLUMNS，INNODB_SYS_FOREIGN，INNODB_SYS_TABLESTATS

相关信息可查询官方手册。

MySQL5.7中的mysql  performance和sys 监控参数

performance schema有如下功能：

①：元数据锁：

  对于了解会话之间元数据锁的依赖关系至关重要。从MySQL5.7.3开始，就可以通过metadata_locks表来了解元数据锁的相关信息；

   --哪些会话拥有哪些元数据锁

   --哪些会话正在等待元数据锁

   --哪些请求由于死锁被杀掉，或者锁等待超时而被放弃

②：进度跟踪：

  跟踪长时间操作的进度（比如alter table），从MySQL5.7.7开始，performance schema自动提供了语句进度信息。我们可以通过events_stages_current表来查看当前事件的进度信息；

③：事务：

  监控服务层和存储引擎层事务的全部方面。从MySQL5.7.3开始，新增了 events_transactions_current表，可以通过setup_consumers、setup_instruments表打开事务监控，通过该表查询到当前事务的状态。如果线上数据库遇到undo log大量增长、数据库性能急剧下降的情况，可以通过该表查询当前是否存在处于未提交状态的事务。如果发现的确有大量事务的state处于active，这时可以确定数据库有大量的事务未提交；

④：内存使用：

  提供内存使用信息统计，有利于了解和调整服务器的内存消耗。从MySQL5.7.2开始，performance schema新增内存有关的统计信息，分别从账户、访问主机、线程、用户及事件的角度统计了内存的使用过程；

⑤：存储程序：

  存储过程、存储方法、事件调度器和表触发器的检测器。在MySQL5.7中的setup_objects表中，新增了event、function、procedure、trigger的检测器。performance schema用于检测该表中匹配object_schema和object_name的对象；

2、sys schema介绍：

  在MySQL5.7中新增的sys schema。是由一系列对象（视图、存储过程、存储方法、表和触发器）组成的schema，它本身不采集和存储什么信息，而是将performance_schema 和 information_schema中的数据以更容易理解的方式总结出来归纳为“视图”。

---sys schema可用于典型的调优和诊断用例，这些对象包括如下三个：

①：将性能模式数据汇总到更容易理解的视图；

②：诸如性能模式配置和生成诊断报告等操作的存储过程

③：用于查询性能模式配置并提供格式化服务的存储函数

---sys schema在查询中的功能，可以查看数据库服务资源的使用情况？哪些主机对数据库服务器的访问量最大？实例上的内存使用情况？

3、sys schema里面的表的分类：

①：主机相关信息：

   以host_summary开头的视图，主要汇总了IO延迟的信息，从主机、文件事件类型、语句类型等角度展示文件IO的信息；

②：innodb相关信息：

   以innodb开头的视图，汇总了innodb buffer page信息和事务等待innodb锁信息；

③：IO使用情况：

   以IO开头的视图，总结了IO使用者的信息，包括等待IO的情况、IO使用量情况，从各个角度分组展示；

④：内存使用情况：

   以memory开头的视图，从主机、线程、用户、事件角度展示内存使用情况；

⑤：连接与会话信息：

   其中，processlist 和 session相关的视图，总结了会话相关的信息；

⑥：表相关信息：

   以schema_table开头的视图，从全表扫描、innodb缓冲池等方面展示了表统计信息；

⑦：索引信息：

   其中包含index的视图，统计了索引使用的情况，以及重复索引和未使用的索引情况；

⑧：语句相关信息：

   以statement开头的视图，统计的规范化后的语句使用情况，包括错误数、警告数、执行全表扫描的、使用临时表、执行排序等信息；

⑨：用户相关信息：

   以user开头的视图，统计了用户使用的文件IO，执行的语句统计信息等；

⑨：等待事件相关信息：

   以wait开头的视图，从主机和事件角度展示等待类事件的延迟情况；

4、sys  schema使用列子：

---查看表的访问量：(可以监控每张表访问量的情况，或者监控某个库的访问量的变化)

select table_schema,table_name,sum(io_read_requests+io_write_requests) from schema_table_statistics;

select table_schema,table_name,io_read_requests+io_write_requests as io_total from schema_table_statistics;

---冗余索引和未使用索引的检查：（schema_redundant_indexes和schema_unused_indexes查看索引的情况）

select * from sys.schema_redundant_indexesG

select * from sys.schema_unused_indexes;

(如果有冗余索引和长期未使用的索引，应该及时清理，)

---查看表自增ID使用情况：

select * from schema_auto_increment_columnsG 

(可以使用schema_auto_increment_columns视图，就能很简单的查到每个表的自增量使用情况，甚至可以精确到某个表的自增量情况)

---监控全表扫描的sql语句：

select * from sys.statements_with_full_table_scans where db='test2'G

(使用statements_with_full_table_scans视图可以查看哪些表查询使用了全表扫描，其中exec_count是执行的次数，等信息)

---查看实例消耗的磁盘I/O情况：()

select file,avg_read+avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10;

(查看io_global_by_file_by_bytes视图可以检查磁盘I/O消耗过大的原因，定位问题)

根据去哪网DBA的总结，开启MySQL sys Schema的风险：因为它的数据来源是 infomation_schema 和 performance_schema.

其中performance_schema 表的数据收集服务，对MySQL服务器的性能消耗是巨大的。到底它对性能影响多大，这里参考去哪网的基准测试工具 （Sysbench）的压测结果，来给大家一个参考。

这个到底开不开启，还需要自己亲自压测。毕竟服务器，mysql分支版本，业务场景有很大不同。  去哪用的是5.7.14进行的压测，压测结果是是确实在TPS上下降了百分之10左右的结果，如果业务能受得了，那就开启。

告警：线上环境，业务繁忙，千万不要直接sql查询performance表的相关监控信息，严重会导致业务请求被堵塞。操作须谨慎。

来源参考：

http://blog.51cto.com/fengfeng688/1952334

http://www.cnblogs.com/waynechou/p/information_schema.html#_label23

打赏

微信扫一扫，打赏作者吧～