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 中的连接控制有关。记录了连续登录失败的次数等信息。
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 相关的主题:
- 有关 InnoDB 存储引擎特有的 INFORMATION_SCHEMA 表的信息:https://dev.mysql.com/doc/refman/5.7/en/innodb-i_s-tables.html
- 关于特定于线程池插件的 INFORMATION_SCHEMA 表的信息:https://dev.mysql.com/doc/refman/5.7/en/thread-pool-i_s-tables.html
- 有关 INFORMATION_SCHEMA 数据库的 FQA:https://dev.mysql.com/doc/refman/5.7/en/faqs-information-schema.html
- INFORMATION_SCHEMA 查询和优化器:https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimization.html
- 排序规则对 INFORMATION_SCHEMA 比较的影响:https://dev.mysql.com/doc/refman/5.7/en/charset-collation-information-schema.html
参考文献
MySQL Manual / INFORMATION_SCHEMA Tables
更新日志
06/18/2017 完善文章,对表的作用作出简单的解释。
06/23/2017 加入相关的注意事项和说明。