MySQL的那些schema库
2012-09-04 17:04:18 
阿炯
information_schema
performance_schema
information_schema
在安装或使用MySQL时，会发现有一个information_schema数据库，它是MySQL自带的虚拟库，提供了访问数据库元数据的方式。
什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。
在MySQL中把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此无法看到与之相关的任何文件。
information_schema数据库表说明：
SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
TABLES表：提供了关于数据库中的表的信息(包括视图)。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。
COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
USER_PRIVILEGES(用户权限)表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
SCHEMA_PRIVILEGES(方案权限)表：给出了关于方案(数据库)权限的信息。该信息来自mysql.db授权表。是非标准表。
TABLE_PRIVILEGES(表权限)表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
COLUMN_PRIVILEGES(列权限)表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
CHARACTER_SETS(字符集)表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。
COLLATIONS表：提供了关于各字符集的对照信息。
COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。
KEY_COLUMN_USAGE表：描述了具有约束的键列。
ROUTINES表：提供了关于存储子程序(存储程序和函数)的信息。此时，ROUTINES表不包含自定义函数(UDF)。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。
TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表。
performance_schema
一、什么是 performance_schema
MySQL的PERFORMANCE schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。它具有以下特点：
1.提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息。
2.performance_schema通过监视server的事件来实现监视server内部运行情况，“事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里？一般来说，事件可以是函数调用、操作系统的等待、sql语句执行的阶段（如sql语句执行过程中的parsing 或 sorting阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。
3.performance_schema中的事件与写入二进制日志中的事件（描述数据修改的events）、事件计划调度程序（这是一种存储程序）的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。
4.performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。
5.当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象（如mutex或file）相关联的活动。
6.PERFORMANCE_SCHEMA存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同。
7.收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（如动态修改performance_schema的setup_*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）。
8.performance_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表在内的整个performance_schema下的所有数据）。
9.MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异。
其实现机制遵循以下设计目标：
1.启用performance_schema不会导致server的行为发生变化。例如它不会改变线程调度机制，不会导致查询执行计划（如EXPLAIN）发生变化。
2.启用performance_schema之后，server会持续不间断地监测，开销很小。不会导致server不可用。
3.在该实现机制中没有增加新的关键字或语句，解析器不会变化。
4.即使performance_schema的监测机制在内部对某事件执行监测失败，也不会影响server正常运行。
5.如果在开始收集事件数据时碰到有其他线程正在针对这些事件信息进行查询，那么查询会优先执行事件数据的收集，因为事件数据的收集是一个持续不断的过程，而检索(查询)这些事件数据仅仅只是在需要查看的时候才进行检索。也可能某些事件数据永远都不会去检索。
6.需要很容易地添加新的instruments监测点。
7.instruments(事件采集项)代码版本化：如果instruments的代码发生了变更，旧的instruments代码还可以继续工作。
注意：MySQL sys schema是一组对象（包括相关的视图、存储过程和函数），可以方便地访问performance_schema收集的数据。同时检索的数据可读性也更高(例如：performance_schema中的时间单位是皮秒，经过sys schema查询时会转换为可读的us,ms,s,min,hour,day等单位)，sys schem在5.7.x版本默认安装。
二、performance_schema 使用快速入门
2.1. 检查当前数据库版本是否支持
performance_schema被视为存储引擎。如果该引擎可用，则应该在INFORMATION_SCHEMA.ENGINES表或SHOW ENGINES语句的输出中都可以看到它的SUPPORT值为YES，如下：使用 INFORMATION_SCHEMA.ENGINES表来查询你的数据库实例是否支持INFORMATION_SCHEMA引擎
使用show命令来查询你的数据库实例是否支持INFORMATION_SCHEMA引擎
当我们看到PERFORMANCE_SCHEMA 对应的Support 字段输出为YES时就表示我们当前的数据库版本是支持performance_schema的。但知道我们的实例支持performance_schema引擎就可以使用了吗？NO，很遗憾，performance_schema在5.6及其之前的版本中，默认没有启用，从5.7及其之后的版本才修改为默认启用。现在，我们来看看如何设置performance_schema默认启用吧！
2.2. 启用performance_schema
从上已经知道，performance_schema在5.7.x及其以上版本中默认启用（5.6.x及其以下版本默认关闭），如果要显式启用或关闭时，需要使用参数performance_schema=ON|OFF设置，并在my.cnf中进行配置。
mysqld启动之后，通过如下语句查看performance_schema是否启用生效（值为ON表示performance_schema已初始化成功且可以使用了。如果值为OFF表示在启用performance_schema时发生某些错误。可以查看错误日志进行排查）：
现在可以在performance_schema下使用show tables语句或者通过查询 INFORMATION_SCHEMA.TABLES表中performance_schema引擎相关的元数据来了解在performance_schema下存在着哪些表，通过从INFORMATION_SCHEMA.tables表查询有哪些performance_schema引擎的表：
直接在performance_schema库下使用show tables语句来查看有哪些performance_schema引擎表：
现在知道了在 MySQL 5.7.17 版本中，performance_schema 下一共有87张表，那么这87帐表都是存放什么数据的呢？如何使用他们来查询我们想要查看的数据呢？先来看看这些表是如何分类的。
2.3. performance_schema表的分类
performance_schema库下的表可以按照监视不同的纬度进行了分组，例如：或按照不同数据库对象进行分组，或按照不同的事件类型进行分组，或在按照事件类型分组之后，再进一步按照帐号、主机、程序、线程、用户等，如下：
按照事件类型分组记录性能事件数据的表
语句事件记录表，这些表记录了语句事件信息，当前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分)
等待事件记录表，与语句事件类型的相关记录表类似：
阶段事件记录表，记录语句执行的阶段事件的表，与语句事件类型的相关记录表类似：
事务事件记录表，记录事务相关的事件的表，与语句事件类型的相关记录表类似：
监视文件系统层调用的表：
监视内存使用的表：
动态对performance_schema进行配置的配置表：
现在已经大概知道了performance_schema中的主要表的分类，但如何使用他们来为我们提供需要的性能事件数据呢？下面介绍如何通过performance_schema下的配置表来配置与使用它。
2.4. performance_schema简单配置与使用
数据库刚刚初始化并启动时，并非所有instruments(事件采集项，在采集项的配置表中每一项都有一个开关字段，或为YES，或为NO)和consumers(与采集项类似，也有一个对应的事件类型保存表配置项，为YES就表示对应的表保存性能数据，为NO就表示对应的表不保存性能数据)都启用了，所以默认不会收集所有的事件，可能你需要检测的事件并没有打开，需要进行设置，可以使用如下两个语句打开对应的instruments和consumers（行计数可能会因MySQL版本而异）。
例如以配置监测等待事件数据为例进行说明：
打开等待事件的采集器配置项开关，需要修改setup_instruments 配置表中对应的采集器配置项
打开等待事件的保存表配置开关，修改修改setup_consumers 配置表中对应的配置项
配置好之后就可以查看server当前正在做什么，可以通过查询events_waits_current表来得知该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件（正在做的事情）：
_current表中每个线程只保留一条记录，且一旦线程完成工作，该表中不会再记录该线程的事件信息，_history表中记录每个线程已经执行完成的事件信息，但每个线程的只事件信息只记录10条，再多就会被覆盖掉，*_history_long表中记录所有线程的事件信息，但总记录数量是10000行，超过会被覆盖掉，现在查看一下历史表events_waits_history 中记录了什么：
summary表提供所有事件的汇总信息。该组中的表以不同的方式汇总事件数据（如：按用户，按主机，按线程等等）。例如：要查看哪些instruments占用最多的时间，可以通过对events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列进行查询（这两列是对事件的记录数执行COUNT（*）、事件记录的TIMER_WAIT列执行SUM（TIMER_WAIT）统计而来），如下：
instance表记录了哪些类型的对象会被检测。这些对象在被server使用时，在该表中将会产生一条事件记录，例如file_instances表列出了文件I/O操作及其关联文件名：
相信很多人都认为大多数时候并不会直接使用performance_schema来查询性能数据，而是使用sys schema下的视图代替，为什么不直接学习sys schema呢？那知道sys schema中的数据是从哪里吐出来的吗？performance_schema 中的数据实际上主要是从performance_schema、information_schema中获取，所以要想玩转sys schema，全面了解performance_schema必不可少。
performance_schema
information_schema
在安装或使用MySQL时，会发现有一个information_schema数据库，它是MySQL自带的虚拟库，提供了访问数据库元数据的方式。
什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。
在MySQL中把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此无法看到与之相关的任何文件。
information_schema数据库表说明：
SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
TABLES表：提供了关于数据库中的表的信息(包括视图)。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。
COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
USER_PRIVILEGES(用户权限)表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
SCHEMA_PRIVILEGES(方案权限)表：给出了关于方案(数据库)权限的信息。该信息来自mysql.db授权表。是非标准表。
TABLE_PRIVILEGES(表权限)表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
COLUMN_PRIVILEGES(列权限)表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
CHARACTER_SETS(字符集)表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。
COLLATIONS表：提供了关于各字符集的对照信息。
COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。
KEY_COLUMN_USAGE表：描述了具有约束的键列。
ROUTINES表：提供了关于存储子程序(存储程序和函数)的信息。此时，ROUTINES表不包含自定义函数(UDF)。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。
TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表。
performance_schema
一、什么是 performance_schema
MySQL的PERFORMANCE schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。它具有以下特点：
1.提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息。
2.performance_schema通过监视server的事件来实现监视server内部运行情况，“事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里？一般来说，事件可以是函数调用、操作系统的等待、sql语句执行的阶段（如sql语句执行过程中的parsing 或 sorting阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。
3.performance_schema中的事件与写入二进制日志中的事件（描述数据修改的events）、事件计划调度程序（这是一种存储程序）的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。
4.performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。
5.当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象（如mutex或file）相关联的活动。
6.PERFORMANCE_SCHEMA存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同。
7.收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（如动态修改performance_schema的setup_*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）。
8.performance_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表在内的整个performance_schema下的所有数据）。
9.MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异。
其实现机制遵循以下设计目标：
1.启用performance_schema不会导致server的行为发生变化。例如它不会改变线程调度机制，不会导致查询执行计划（如EXPLAIN）发生变化。
2.启用performance_schema之后，server会持续不间断地监测，开销很小。不会导致server不可用。
3.在该实现机制中没有增加新的关键字或语句，解析器不会变化。
4.即使performance_schema的监测机制在内部对某事件执行监测失败，也不会影响server正常运行。
5.如果在开始收集事件数据时碰到有其他线程正在针对这些事件信息进行查询，那么查询会优先执行事件数据的收集，因为事件数据的收集是一个持续不断的过程，而检索(查询)这些事件数据仅仅只是在需要查看的时候才进行检索。也可能某些事件数据永远都不会去检索。
6.需要很容易地添加新的instruments监测点。
7.instruments(事件采集项)代码版本化：如果instruments的代码发生了变更，旧的instruments代码还可以继续工作。
注意：MySQL sys schema是一组对象（包括相关的视图、存储过程和函数），可以方便地访问performance_schema收集的数据。同时检索的数据可读性也更高(例如：performance_schema中的时间单位是皮秒，经过sys schema查询时会转换为可读的us,ms,s,min,hour,day等单位)，sys schem在5.7.x版本默认安装。
二、performance_schema 使用快速入门
2.1. 检查当前数据库版本是否支持
performance_schema被视为存储引擎。如果该引擎可用，则应该在INFORMATION_SCHEMA.ENGINES表或SHOW ENGINES语句的输出中都可以看到它的SUPPORT值为YES，如下：使用 INFORMATION_SCHEMA.ENGINES表来查询你的数据库实例是否支持INFORMATION_SCHEMA引擎
使用show命令来查询你的数据库实例是否支持INFORMATION_SCHEMA引擎
当我们看到PERFORMANCE_SCHEMA 对应的Support 字段输出为YES时就表示我们当前的数据库版本是支持performance_schema的。但知道我们的实例支持performance_schema引擎就可以使用了吗？NO，很遗憾，performance_schema在5.6及其之前的版本中，默认没有启用，从5.7及其之后的版本才修改为默认启用。现在，我们来看看如何设置performance_schema默认启用吧！
2.2. 启用performance_schema
从上已经知道，performance_schema在5.7.x及其以上版本中默认启用（5.6.x及其以下版本默认关闭），如果要显式启用或关闭时，需要使用参数performance_schema=ON|OFF设置，并在my.cnf中进行配置。
mysqld启动之后，通过如下语句查看performance_schema是否启用生效（值为ON表示performance_schema已初始化成功且可以使用了。如果值为OFF表示在启用performance_schema时发生某些错误。可以查看错误日志进行排查）：
现在可以在performance_schema下使用show tables语句或者通过查询 INFORMATION_SCHEMA.TABLES表中performance_schema引擎相关的元数据来了解在performance_schema下存在着哪些表，通过从INFORMATION_SCHEMA.tables表查询有哪些performance_schema引擎的表：
直接在performance_schema库下使用show tables语句来查看有哪些performance_schema引擎表：
现在知道了在 MySQL 5.7.17 版本中，performance_schema 下一共有87张表，那么这87帐表都是存放什么数据的呢？如何使用他们来查询我们想要查看的数据呢？先来看看这些表是如何分类的。
2.3. performance_schema表的分类
performance_schema库下的表可以按照监视不同的纬度进行了分组，例如：或按照不同数据库对象进行分组，或按照不同的事件类型进行分组，或在按照事件类型分组之后，再进一步按照帐号、主机、程序、线程、用户等，如下：
按照事件类型分组记录性能事件数据的表
语句事件记录表，这些表记录了语句事件信息，当前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分)
等待事件记录表，与语句事件类型的相关记录表类似：
阶段事件记录表，记录语句执行的阶段事件的表，与语句事件类型的相关记录表类似：
事务事件记录表，记录事务相关的事件的表，与语句事件类型的相关记录表类似：
监视文件系统层调用的表：
监视内存使用的表：
动态对performance_schema进行配置的配置表：
现在已经大概知道了performance_schema中的主要表的分类，但如何使用他们来为我们提供需要的性能事件数据呢？下面介绍如何通过performance_schema下的配置表来配置与使用它。
2.4. performance_schema简单配置与使用
数据库刚刚初始化并启动时，并非所有instruments(事件采集项，在采集项的配置表中每一项都有一个开关字段，或为YES，或为NO)和consumers(与采集项类似，也有一个对应的事件类型保存表配置项，为YES就表示对应的表保存性能数据，为NO就表示对应的表不保存性能数据)都启用了，所以默认不会收集所有的事件，可能你需要检测的事件并没有打开，需要进行设置，可以使用如下两个语句打开对应的instruments和consumers（行计数可能会因MySQL版本而异）。
例如以配置监测等待事件数据为例进行说明：
打开等待事件的采集器配置项开关，需要修改setup_instruments 配置表中对应的采集器配置项
打开等待事件的保存表配置开关，修改修改setup_consumers 配置表中对应的配置项
配置好之后就可以查看server当前正在做什么，可以通过查询events_waits_current表来得知该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件（正在做的事情）：
_current表中每个线程只保留一条记录，且一旦线程完成工作，该表中不会再记录该线程的事件信息，_history表中记录每个线程已经执行完成的事件信息，但每个线程的只事件信息只记录10条，再多就会被覆盖掉，*_history_long表中记录所有线程的事件信息，但总记录数量是10000行，超过会被覆盖掉，现在查看一下历史表events_waits_history 中记录了什么：
summary表提供所有事件的汇总信息。该组中的表以不同的方式汇总事件数据（如：按用户，按主机，按线程等等）。例如：要查看哪些instruments占用最多的时间，可以通过对events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列进行查询（这两列是对事件的记录数执行COUNT（*）、事件记录的TIMER_WAIT列执行SUM（TIMER_WAIT）统计而来），如下：
instance表记录了哪些类型的对象会被检测。这些对象在被server使用时，在该表中将会产生一条事件记录，例如file_instances表列出了文件I/O操作及其关联文件名：
相信很多人都认为大多数时候并不会直接使用performance_schema来查询性能数据，而是使用sys schema下的视图代替，为什么不直接学习sys schema呢？那知道sys schema中的数据是从哪里吐出来的吗？performance_schema 中的数据实际上主要是从performance_schema、information_schema中获取，所以要想玩转sys schema，全面了解performance_schema必不可少。