嗨客网搜索
数据库教程
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 服务器所有数据库的信息,比如数据库名、 数据库的表、 访问权限、 数据库表的数据类型、 数据库索引的信息等等。

嗨客网顶部