MySQL information_schema库-嗨客网

MySQL information_schema教程

MySQL 的 information_schema 数据库保存了 MySQL 服务器所有数据库的信息，比如数据库名、数据库的表、访问权限、数据库表的数据类型、数据库索引的信息等等。

MySQL information_schema表作用

MySQL information_schema 的表的查询操作可以替代一些 show 查询语句（例如：SHOW DATABASES，SHOW TABLES等），与使用 show 语句相比，通过查询 information_schema 下的表获取数据有以下优势：

它符合 “Codd法则”，所有的访问都是基于表的访问完成的。
可以使用 SELECT 语句的 SQL 语法，只需要学习你要查询的一些表名和列名的含义即可。
基于 SQL 语句的查询，对来自 information_schema 中的查询结果可以做过滤、排序、联结操作，查询的结果集格式对应用程序来说更友好。
但是访问 MySQL information_schema 是需要权限的。所有用户都有访问 information_schema 下的表权限,但只能访问 Server 层的部分数据字典表，Server 层中的部分数据字典表以及 InnoDB 层的数据字典表需要额外授权才能访问，如果用户权限不足，当查询Server 层数据字典表时将不会返回任何数据，或者某个列没有权限访问时，该列返回NULL值。
当查询InnoDB 数据字典表时将直接拒绝访问(要访问这些表需要有 process 权限，注意不是 select 权限)。

MySQL information_schema表定义

首先，我们使用 use 命令，切换到 mysql information_schema 数据库，命令如下：

mysql> use information_schema;
Database changed

执行成功后，此时终端显示如下：

02_Mysql information_schema数据库.png

接下来，我们使用 show tables 命令，查看 mysql information_schema 数据库里面所有的表定义，命令如下：

mysql> show tables;

执行成功后，此时终端显示如下：

03_Mysql information_schema数据库.png

此时，我没看到了 information_schema 数据库里面的所有的表。

MySQL information_schema相关表说明

MySQL information_schema权限相关表

表名称	说明
SCHEMA_PRIVILEGES	提供了数据库的相关权限，这个表是内存表是从mysql.db中拉去出来的。
TABLE_PRIVILEGES	提供的是表权限相关信息，信息是从 mysql.tables_priv 表中加载的
COLUMN_PRIVILEGES	这个表可以清楚就能看到表授权的用户的对象，哪张表哪个库以及授予的是什么权限，如果授权的时候加上 with grant option 的话，我们可以看得到 PRIVILEGE_TYPE 这个值必须是 YES。
USER_PRIVILEGES	提供的是表权限相关信息，信息是从 mysql.user 表中加载的。通过该表我们可以很清晰看得到 MySQL 授权的层次，SCHEMA，TABLE，COLUMN级别，当然这些都是基于用户来授予的。可以看得到 MySQL 的授权也是相当的细密的，可以具体到列，这在某一些应用场景下还是很有用的，比如审计等。

MySQL information_schema字符集相关表

表名称	说明
CHARACTER_SETS	存储数据库相关字符集信息
COLLATIONS	字符集对应的排序规则
COLLATION_CHARACTER_SET_APPLICABILITY	就是一个字符集和连线校对的一个对应关系而已

MySQL information_schema数据库实体对象相关表

表名称	说明
COLUMNS	存储表的字段信息，所有的存储引擎
ENGINES	引擎类型，是否支持这个引擎，描述，是否支持事物，是否支持分布式事务，是否能够支持事物的回滚点
EVENTS	记录MySQL中的事件，类似于定时作业
FILES	这张表提供了有关在MySQL的表空间中的数据存储的文件的信息，文件存储的位置，这个表的数据是从InnoDB in-memory中拉取出来的，所以说这张表本身也是一个内存表，每次重启重新进行拉取。
PARAMETERS	参数表存储了一些存储过程和方法的参数，以及存储过程的返回值信息。存储和方法在ROUTINES里面存储。
PLUGINS	基本上是MySQL的插件信息，是否是活动状态等信息。其实SHOW PLUGINS本身就是通过这张表来拉取道德数据
ROUTINES	关于存储过程和方法function的一些信息，不过这个信息是不包括用户自定义的，只是系统的一些信息。
SCHEMATA	这个表提供了实例下有多少个数据库，而且还有数据库默认的字符集
TRIGGERS	这个表记录的就是触发器的信息，包括所有的相关的信息。系统的和自己用户创建的触发器。
VIEWS	视图的信息，也是系统的和用户的基本视图信息。

MySQL information_schema数据库外键相关表

表名称	说明
REFERENTIAL_CONSTRAINTS	这个表提供的外键相关的信息，而且只提供外键相关信息
TABLE_CONSTRAINTS	这个表提供的是相关的约束信息
INNODB_SYS_FOREIGN_COLS	这个表也是存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的
INNODB_SYS_FOREIGN	存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的，只不过是单独对于INNODB来说的
KEY_COLUMN_USAGE	数据库中所有有约束的列都会存下下来，也会记录下约束的名字和类别

MySQL information_schema数据库管理相关表

表名称	说明
PARTITIONS	MySQL分区表相关的信息，通过这张表我们可以查询到分区的相关信息
PROCESSLIST	show processlist其实就是从这个表拉取数据，PROCESSLIST的数据是他的基础。由于是一个内存表，所以我们相当于在内存中查询一样，这些操作都是很快的。
INNODB_CMP_PER_INDEX	存储的是关于压缩INNODB信息表的时候的相关信息,有关整个表和索引信息都有.
INNODB_CMPMEM	存放关于MySQL INNODB的压缩页的buffer pool信息
INNODB_BUFFER_POOL_STATS	表提供有关INNODB 的buffer pool相关信息，和show engine innodb status提供的信息是相同的。也是show engine innodb status的信息来源。
INNODB_BUFFER_PAGE_LRU	维护了INNODB LRU LIST的相关信息
INNODB_BUFFER_PAGE	存的是buffer里面缓冲的页数据。查询这个表会对性能产生很严重的影响，千万不要再我们自己的生产库上面执行这个语句，除非你能接受服务短暂的停顿
INNODB_SYS_DATAFILES	这张表就是记录的表的文件存储的位置和表空间的一个对应关系(INNODB)
INNODB_TEMP_TABLE_INFO	这个表惠记录所有的INNODB的所有用户使用到的信息，但是只能记录在内存中和没有持久化的信息。
INNODB_METRICS	提供INNODB的各种的性能指数，是对INFORMATION_SCHEMA的补充，收集的是MySQL的系统统计信息。这些统计信息都是可以手动配置打开还是关闭的。
INNODB_SYS_VIRTUAL	表存储的是INNODB表的虚拟列的信息
INNODB_CMP	存储的是关于压缩INNODB信息表的时候的相关信息

MySQL information_schema优化相关表

表名称	说明
OPTIMIZER_TRACE	提供的是优化跟踪功能产生的信息.
PROFILING	SHOW PROFILE可以深入的查看服务器执行语句的工作情况。以及也能帮助你理解执行语句消耗时间的情况。
INNODB_FT_BEING_DELETED	这张表是INNODB_FT_DELETED的一个快照,只在OPTIMIZE TABLE 的时候才会使用。

MySQL information_schema事物和锁相关表

表名称	说明
INNODB_LOCKS	现在获取的锁，但是不含没有获取的锁，而且只是针对INNODB的。
INNODB_LOCK_WAITS	系统锁等待相关信息，包含了阻塞的一行或者多行的记录，而且还有锁请求和被阻塞改请求的锁信息等。
INNODB_TRX	包含了所有正在执行的的事物相关信息（INNODB），而且包含了事物是否被阻塞或者请求锁。

MySQL information_schema数据库总结

MySQL 的 information_schema 数据库保存了 MySQL 服务器所有数据库的信息，比如数据库名、数据库的表、访问权限、数据库表的数据类型、数据库索引的信息等等。