「MySQL」- INFORMATION_SCHEMA

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 STATUS 和 SHOW SESSION STATUS 输出信息来自这里。

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

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

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

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

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

PARTITIONS
表分区的相关信息。

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

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

PROFILING
提供了语句分析信息。对应了 SHOW PROFILES 和 SHOW 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 Manual

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

CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS 与 connection_control 插件有关。该表已经添加到 MySQL 5.7.17 中。

此表提供的信息是关于失败连接的次数，每个客户端的 user/host 组合的连续失败连接尝试次数。

相关注意事项

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 加入相关的注意事项和说明。

Filed under: K4NZDROID - @ 9:31 PM