「MySQL」- INFORMATION_SCHEMA

  CREATED BY JENKINSBOT

INFORMATION_SCHEMA,有时也称作数据字典(data dictionary)和系统目录(system catalog)。MySQL中默认存在的数据库之一,保存了数据库中其他所有的SCHEMA的元数据、表名、列名、类型、权限等。

字符类型的列(例如,TABLES.TABLE_NAME)的定义通常是VARCHAR(N) CHARACTER SET utf8,其中N至少为64。对于所有搜索,排序,比较,对比,MySQL使用此字符集的默认排序规则(utf8_general_ci)。

因为一些MySQL对象被表示为文件,所以在INFORMATION_SCHEMA字符串列中的搜索可能受文件系统区分大小写的影响。

使用INFORMATION_SCHEMA来替代默认的SHOW语句

SELECT … FROM INFORMATION_SCHEMA语句旨在提供比各种SHOW语句(SHOW DATABASES,SHOW TABLES等)提供的信息的更一致的方式。与SHOW相比,使用SELECT具有以下优点:

  • 它符合Codd的规则,因为所有访问都在表上完成。
  • 可以使用SELECT语句的语法,只需要学习一些表和列名,使用简单。
  • 实施者不用担心添加关键字。
  • 可以将INFORMATION_SCHEMA查询中的结果进行过滤,排序,连接和转换为应用程序需要的任何格式,更加的灵活方便。
  • 这种技术与其他数据库系统更能互操作。例如,Oracle数据库用户熟悉Oracle数据字典中的查询表。

因为SHOW语句的熟悉和使用的广泛,所以SHOW语句作为可替代的功能保留了下来。

关于这些表的访问权限

每个MySQL用户都有权访问这些表,但只能看到表中具有访问权限的行。在某些情况下(例如,INFORMATION_SCHEMA.ROUTINES表中的ROUTINE_DEFINITION列),权限不足的用户将看到NULL。这些限制不适用于InnoDB表;你只能看到PROCESS权限。

相同的权限,从INFORMATION_SCHEMA中获得的信息与通过SHOW语句查看到的信息是相同的。在任一情况下,必须对对象有一定的权限才能查看有关该对象的信息。

性能相关的问题

从多个数据库搜索信息的INFORMATION_SCHEMA查询可能需要很长时间并影响性能。要检查查询的效率,可以使用EXPLAIN。

标准的注意事项

MySQL中的INFORMATION_SCHEMA表结构的实现遵循ANSI/ISO SQL:2003 standard Part 11 Schemata。MySQL的意图是大致符合SQL:2003 core feature F021 Basic information schema

虽然其他DBMS使用各种名称,如syscat或system,但是标准名称是INFORMATION_SCHEMA。

为避免使用在标准或DB2,SQL Server或Oracle中保留的任何名称,MySQL更改了标记为“MySQL扩展名”的列的名称。 (例如,我们将COLLATION更改为TABLES表中的TABLE_COLLATION)。请参阅本文末尾附近的保留字列表:https://web.archive.org/web/20070428032454/http://www.dbazine.com/db2/db2-disarticles/gulutzan5

INFORMATION_SCHEMA参考部分中的约定

以下部分介绍INFORMATION_SCHEMA中的每个表和列。对于每一列,有三条信息:

  • “INFORMATION_SCHEMA NAME”表示INFORMATION_SCHEMA表中列的名称。这对应了标准SQL名称,除非“Remarks”字段说“MySQL extension”。
  • “SHOW Name”表示最接近的SHOW语句中的等效字段名称(如果有的话)。
  • “Remarks”在适用的情况下提供其他信息。如果此字段为NULL,则表示该列的值始终为NULL。如果该字段表示“MySQL extension”,则该列是标准SQL的MySQL扩展。

许多部分表示什么SHOW语句相当于从INFORMATION_SCHEMA检索信息的SELECT。对于SHOW语句,如果省略了一个FROM db_name子句,则显示当前数据库的信息,通常可以通过向查询的WHERE子句添加AND TABLE_SCHEMA = SCHEMA()条件来选择查看默认数据库的信息,该WHERE子句从INFORMATION_SCHEMA中检索信息表。

与SCHEMA相关的表

CHARACTER_SETS
提供了数据库中可用的字符集的信息。

COLLATIONS
提供了每种字符集的排序信息。

COLLATION_CHARACTER_SET_APPLICABILITY
显示了什么字符集适用于什么排序规则。

COLUMNS
所有表中的列的信息。

COLUMN_PRIVILEGES
提动有关列权限的信息。

ENGINES
提供了存储引擎相关信息。

EVENTS
提供了计划事件相关信息。

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

GLOBAL_STATUS and SESSION_STATUS
提供了服务器状态变量的信息。对应了SHOW GLOBAL STATUSSHOW SESSION STATUS输出信息来自这里。

GLOBAL_VARIABLES and SESSION_VARIABLES
提供有关服务器状态变量的信息。对应了SHOW GLOBAL STATUSSHOW SESSION STATUS的输出。

KEY_COLUMN_USAGE
描述哪些索引列具有约束。

ndb_transid_mysql_connection_map
提供NDB事务,NDB事务协调器和作为API节点附加到NDB群集的MySQL服务器之间的映射。

OPTIMIZER_TRACE
提供了由优化器跟踪功能生成的信息。

PARAMETERS
存储过程和函数的参数、及关于存储函数的返回值。

PARTITIONS
表分区的相关信息。

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

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

PROFILING
提供了语句分析信息。对应了SHOW PROFILESSHOW PROFILE的输出。

REFERENTIAL_CONSTRAINTS
提供了外键相关信息。

ROUTINES
提供了存储过程和函数的信息。这里不包括UDF。

SCHEMATA
该表提供了关于数据库的信息。

SCHEMA_PRIVILEGES
提供了数据库权限信息。

STATISTICS
提供了表索引的相关信息。

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

TABLESPACES
提供了活动表空间的信息。

TABLE_CONSTRAINTS
描述l了那些表具有约束。

TABLE_PRIVILEGES
提供了表权限信息。

TRIGGERS
提供了触发器相关的信息。

USER_PRIVILEGES
提供了关于全局权限的信息。

VIEWS
提供了数据库中视图的信息。

与INNODB相关的表

这些表可用于监控当前InnoDB的一些状态,以便在发生问题之前检测出存在的低效率问题、排除性能和容量问题。

INNODB_BUFFER_PAGE
保存了在InnoDB Buffer Pool中每个页面的信息。

INNODB_BUFFER_PAGE_LRU
保存有关InnoDB Buffer Pool中的页面的信息,特别是在LRU列表中它们是如何排序的,以确定当缓冲池变满时从缓冲池中排出哪些页面

INNODB_BUFFER_POOL_STATS
提供了缓冲池的信息,与SHOW ENGINE INNODB STATUS输出有相同的地方。使用InnoDB Buffer Pool服务器状态变量也可以获得大部分相同的信息。

INNODB_CMP and INNODB_CMP_RESET
压缩的InnoDB表相关的操作的状态信息,都保存在这两张表中。

INNODB_CMPMEM and INNODB_CMPMEM_RESET
包含InnoDB缓冲池中的压缩页面上的状态信息。

INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET
包含与压缩的InnoDB表和索引相关的操作的状态信息,以及数据库,表和索引的每个组合的单独统计信息,以帮助评估特定表的压缩性能和有用性。

INNODB_FT_BEING_DELETED
INNODB_FT_BEING_DELETED表是仅在OPTIMIZE TABLE维护操作中使用的INNODB_FT_DELETED表的快照。当OPTIMIZE TABLE运行时,INNODB_FT_BEING_DELETED表被清空,DOC_ID从INNODB_FT_DELETED表中删除。由于INNODB_FT_BEING_DELETED的内容通常具有较短的生命周期,因此该表具有有限的监视或调试实用程序。

INNODB_FT_CONFIG
INNODB_FT_CONFIG表显示有关InnoDB表的FULLTEXT索引和关联处理的元数据。

INNODB_FT_DEFAULT_STOPWORD
INNODB_FT_DEFAULT_STOPWORD表包含在InnoDB表上创建FULLTEXT索引时默认使用的列表。

INNODB_FT_DELETED
INNODB_FT_DELETED表记录从InnoDB表的FULLTEXT索引中删除的行。为了避免在InnoDB FULLTEXT索引的DML操作期间昂贵的索引重组,关于新删除的单词的信息是单独存储的,当您执行文本搜索时,将其从搜索结果中过滤掉,并且仅在您发出OPTIMIZE TABLE时从主搜索索引中删除InnoDB表的声明。

INNODB_FT_INDEX_CACHE
INNODB_FT_INDEX_CACHE:包含有关FULLTEXT索引中新插入的行的令牌信息。为了避免在DML操作期间昂贵的索引重组,关于新索引字的信息被单独存储,并且仅在运行OPTIMIZE TABLE时,当服务器关闭时,或者当高速缓存大小超过innodb_ft_cache_size或innodb_ft_total_cache_size。

INNODB_FT_INDEX_TABLE
根据InnoDB表的FULLTEXT索引来显示关于用于处理文本搜索的反向索引的信息

INNODB_LOCKS
包含有关InnoDB事务已请求获取但未获取的每个锁的信息,以及事务持有的阻止另一个事务的每个锁。

INNODB_LOCK_WAITS
包含每个被阻挡的InnoDB事务的一行或多行,指示其请求的锁和阻塞该请求的任何锁。

INNODB_METRICS
提供了各种InnoDB性能信息,补充了InnoDB的PERFORMANCE_SCHEMA表的特定焦点区域。通过简单的查询,您可以检查系统的整体运行状况。
通过更详细的查询,可以诊断性能瓶颈,资源短缺和应用程序问题等问题

INNODB_SYS_COLUMNS
有关InnoDB表列的元数据,相当于InnoDB数据字典中SYS_COLUMNS表的信息。

INNODB_SYS_DATAFILES
提供InnoDB文件每个表和一般Tablespace的数据文件路径信息,相当于InnoDB数据字典中SYS_DATAFILES表中的信息。

INNODB_SYS_FIELDS
提供有关InnoDB索引的KEY列(字段)的元数据,相当于InnoDB数据字典中SYS_FIELDS表中的信息。

INNODB_SYS_FOREIGN
提供关于InnoDB外键的元数据。

INNODB_SYS_FOREIGN_COLS
提供InnoDB外键列的状态信息。

INNODB_SYS_INDEXES
提供InnoDB索引的元数据。

INNODB_SYS_TABLES
提供InnoDB表的元数据。

INNODB_SYS_TABLESPACES
提供了有关InnoDB file-pre-table和一般表空间的元数据。

INNODB_SYS_TABLESTATS
提供了有关InnoDB表的低级状态信息的视图。该数据由MySQL优化器用于计算在查询InnoDB表时要使用的索引。该信息来自内存中数据结构,而不是与存储在磁盘上的数据相对应。
没有相应的内部InnoDB系统表。

INNODB_SYS_VIRTUAL
提供有关InnoDB虚拟生成列和虚拟生成列所基于的列的元数据,等同于InnoDB数据字典中的SYS_VIRTUAL表中的信息。

INNODB_TEMP_TABLE_INFO
包含有关活动的InnoDB临时表的元数据。除了InnoDB使用的优化内部临时表外,INNODB_TEMP_TABLE_INFO报告了在给定的InnoDB实例中处于活动状态的所有用户和系统创建的临时表。
表保存在内存中,而不是保留到磁盘。

INNODB_TRX
包含有关InnoDB当前正在执行的每个事务(不包括只读事务)的信息,包括事务是否正在等待锁定,事务启动时以及事务正在执行的SQL语句(如果有的话)。

与Thread Pool相关的表

下面的这几张表都与Thread Pool插件有关:

TP_THREAD_GROUP_STATE
线程池线程组的状态信息。

TP_THREAD_GROUP_STATS
线程组的统计信息。

TP_THREAD_STATE
线程池线程状态信息。

与连接控制相关的表

CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
该表与MySQL中的连接控制有关。记录了连续登录失败的次数等信息。

相关注意事项

INFORMATION_SCHEMA是每个MySQL实例中的一个数据库,其中存储了有关MySQL服务器的所有的其他数据库的信息。

INFORMATION_SCHEMA数据库包含几个只读表。它们实际上是视图,而不是基本的表,所以它们没有相关联任何文件,并且不能对它们设置触发器。此外,没有INFORMATION_SCHEMA的数据库目录

虽然可以使用USE语句选择INFORMATION_SCHEMA数据库,但只能读取表的内容,而不对其执行INSERT、UPDATE、DELETE操作。

相关信息

以下列出的部分讨论了更多的与INFORMATION_SCHEMA相关的主题:

参考文献

MySQL Manual / INFORMATION_SCHEMA Tables

更新日志

06/18/2017 完善文章,对表的作用作出简单的解释。
06/23/2017 加入相关的注意事项和说明。