devbox
从前慢现在也慢
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

information_schema 系统库 —— 简介与组成对象_information 库名

作者：从前慢现在也慢 | 2024-05-12 16:21:16

赞

踩

information 库名

系列文章参考自《MySQL 性能优化金字塔法则》，删除了书里重复说明和过于复杂的一些解释，完整版请参考原书。

information_schema 比 sys系统库又要简单一些，不需要另外配置，只需要了解里面有哪些表，能查询哪些信息即可。

 

一、 什么是information_schema

1. 简介

information_schema提供对数据库元数据、统计信息、以及有关MySQL Server的信息访问。它类似于MySQL的数据字典或系统目录。

  • 每个MySQL实例中都有一个独立的information_schema，用来存储MySQL实例中所有其他数据库的基本信息

  • 库中的表都在内存中（非持久表），所以在磁盘的数据目录下没有对应的关联文件，重启后数据会丢失

  • 库中的表都是只读的，不能修改，也不能对这些表设置触发器。

  • 查询information_schema下的表可以替代一些show语句（例如SHOW DATABASES，SHOW TABLES等）

 

2. 访问information_schema需要的权限

  • 所有用户都有访问information_schema下的表权限，但默认只能访问Server层的部分数据字典表

  • 要访问Server层中的另一些数据字典表及InnoDB层的数据字典表，用户需要有process权限（注意不是select权限，授权select information_schema下的表会报错权限不足）。

  • 所需权限也适用于SHOW语句。无论使用哪种查询方式，都必须拥有某个对象的权限才能看到相关的数据。

 

3. 简要变化史

  • 5.6版本共59张表，其中10张MyISAM引擎临时表（数据字典表），49张Memory引擎临时表（统计信息和一些临时信息）。
  • 5.7版本共61张表，其中10张InnoDB存储引擎临时表（数据字典表），51张Memory引擎临时表。
  • 8.0版本将数据字典表及部分原memory引擎临时表迁移到了mysql库，且在mysql库中这些表被隐藏无法直接访问，需要通过information_schema下的同名表进行访问。统计信息表保留在information_schema下且仍然为Memory引擎。

 

二、 information_schema 组成对象

按照这些表的用途及相似度，我们把information_schema下的表做了如下归类。

1. Server层 统计信息字典表

COLUMNS：

  • 提供表中的列(字段)信息

  • 该表为InnoDB 存储引擎的临时表

KEY_COLUMN_USAGE：

  • 查询哪些索引列存在约束条件，包含主键、唯一索引、外键等约束的信息。表中的信息与TABLE_CONSTRAINTS表中记录的信息有些类似，但TABLE_CONSTRAINTS表中没有记录约束引用的库表列信息。

  • 该表为Memory引擎临时表

REFERENTIAL_CONSTRAINTS：

  • 提供查询关于外键约束的一些信息

  • 该表为Memory引擎临时表

STATISTICS：

  • 提供查询关于索引的一些统计信息，一个索引对应一行记录

  • 该表为Memory引擎临时表

TABLE_CONSTRAINTS：

  • 提供查询表相关的约束信息

  • 该表为Memory引擎临时表

FILES：

  • 提供查询MySQL的数据表空间文件相关的信息，包含InnoDB和NDB存储引擎相关的数据文件信息

  • 该表为Memory存储引擎表

ENGINES：

  • 提供查询MySQL Server支持的引擎相关的信息

  • 该表为Memory引擎临时表

TABLESPACES：

  • 提供查询关于活跃表空间的相关信息（主要记录的是NDB存储引擎表空间信息）

  • 该表不提供有关InnoDB存储引擎的表空间的信息。 InnoDB表空间元数据信息请查询INNODB_SYS_TABLESPACES和INNODB_SYS_DATAFILES表。从5.7.8开始，INFORMATION_SCHEMA.FILES表也提供InnoDB表空间的元数据信息

  • 该表为Memory引擎临时表。

SCHEMATA：

  • 提供查询MySQL中的数据库列表信息，一个schema就是一个database

  • 该表为Memory引擎临时表

 

2. Server层 对象字典表

VIEWS：

  • 提供查询数据库中的视图相关的信息，查询该表的帐号需要拥有show view权限

  • 该表为InnoDB引擎临时表

TRIGGERS：

  • 提供查询关于某个数据库下的触发器相关的信息，要查询某个表的触发器，查询的账户必须要有trigger权限

  • 该表为InnoDB引擎临时表

TABLES：

  • 提供查询数据库内的表相关的基本信息

  • 该表为Memory引擎临时表

ROUTINES：

  • 提供查询关于存储过程和存储函数的信息（不包括用户自定义函数UDF），该表中的信息与mysql.proc表相对应

  • 该表为InnoDB引擎临时表

PARTITIONS：

  • 提供查询关于分区表的信息

  • 该表为InnoDB引擎临时表

EVENTS：

  • 提供查询计划任务事件相关的信息

  • 该表是InnoDB引擎临时表

PARAMETERS：

  • 提供有关存储过程和函数的参数信息，以及有关存储函数的返回值的信息。 这些参数信息与mysql.proc表中的param_list列记录的内容类似

  • 该表为InnoDB引擎临时表

 

3. Server 层 混杂信息字典表

GLOBAL_STATUS、GLOBAL_VARIABLES、SESSION_STATUS、SESSION_VARIABLES：

  • 提供查询全局、会话级别的的状态变量与系统变量信息，这些表为Memory引擎临时表

OPTIMIZER_TRACE：

  • 提供优化程序跟踪功能产生的信息。

  • 跟踪功能默认关闭，使用optimizer_trace系统变量启用跟踪功能。如果开启，每个会话只能跟踪自己执行的语句，且只能记录最后一个跟踪的SQL语句

  • 该表为InnoDB引擎临时表

PLUGINS：

  • 提供查询关于MySQL Server中支持哪些插件的信息

  • 该表为InnoDB引擎临时表

PROCESSLIST：

  • 提供查询一些关于线程运行过程中的状态信息

  • 该表为InnoDB引擎临时表

PROFILING：

  • 提供查询关于语句性能分析的信息。记录内容对应于SHOW PROFILES和SHOW PROFILE语句产生的信息。该表需要在会话变量 profiling=1时才会记录语句性能分析信息，否则不记录。

  • 注意：从MySQL 5.7.2开始，此表不再推荐使用，在未来的MySQL版本中删除。改用Performance Schema代替

  • 该表为Memory引擎临时表

CHARACTER_SETS：

  • 提供查询MySQL Server支持的可用字符集有哪些

  • 该表为Memory引擎临时表

COLLATIONS：

  • 提供查询MySQL Server支持的可用校对规则有哪些

  • 该表为Memory引擎临时表

COLLATION_CHARACTER_SET_APPLICABILITY：

  • 提供查询MySQL Server中哪种字符集适用于什么校对规则。查询结果集相当于从SHOW COLLATION获得的结果集中的前两个字段值。该表目前并没有发现有太大作用，

  • 该表为Memory引擎临时表

COLUMN_PRIVILEGES：

  • 提供查询关于列的权限信息，表中的内容来自mysql.column_priv列权限表（需要针对一个表的列单独授权之后才会有内容）

  • 该表为Memory引擎临时表

SCHEMA_PRIVILEGES：

  • 提供查询关于库级别的权限信息，每种类型的库级别权限记录一行信息，该表中的信息来自mysql.db表

  • 该表为Memory引擎临时表

TABLE_PRIVILEGES：

  • 提供查询关于表级别权限信息，该表中的内容来自mysql.tables_priv

  • 该表为Memory引擎临时表

USER_PRIVILEGES：

  • 提供查询全局权限的信息，该表中的信息来自mysql.user表

  • 该表为Memory引擎临时表

 

4. InnoDB层 系统字典表

INNODB_SYS_DATAFILES：

  • 提供查询InnoDB file-per-table和常规表空间数据文件的路径信息，等同于InnoDB数据字典内部SYS_DATAFILES表中的信息

  • 表中信息包含InnoDB所有表空间类型的元数据，包括独立表空间、常规表空间、系统表空间、临时表空间和undo表空间

  • 该表为memory引擎临时表，查询该表的用户需要有process权限。

INNODB_SYS_VIRTUAL：

  • 提供查询有关InnoDB虚拟生成列和与之关联的列的元数据信息，等同于InnoDB数据字典内部SYS_VIRTUAL表中的信息。INNODB_SYS_VIRTUAL表中展示的行信息是虚拟生成列相关联列的每个列的信息。

  • 该表为memory引擎临时表，查询该表的用户需要有process权限

INNODB_SYS_INDEXES：

  • 提供查询有关InnoDB索引的元数据信息，等同于InnoDB数据字典内部SYS_INDEXES表中的信息

  • 该表为memory引擎临时表，查询该表的用户需要具有process权限

INNODB_SYS_TABLES：

  • 提供查询有关InnoDB表的元数据，等同于InnoDB数据字典内部SYS_TABLES表的信息。

  • 该表为memory引擎临时表，查询该表的用户需要有process权限

INNODB_SYS_FIELDS：

  • 提供查询有关InnoDB索引列的元数据信息，等同于InnoDB数据字典内部SYS_FIELDS表的信息

  • 该表为memory引擎临时表，查询该表的用户需要有process权限

INNODB_SYS_TABLESPACES：

  • 提供查询有关InnoDB独立表空间和普通表空间的元数据信息（也包含了全文索引表空间），等同于InnoDB数据字典内部SYS_TABLESPACES表中的信息

  • 该表为memory引擎临时表，查询该表的用户需要有process权限

INNODB_SYS_FOREIGN_COLS：

  • 提供查询有关InnoDB外键列的状态信息，等同于InnoDB数据字典内部SYS_FOREIGN_COLS表的信息

  • 该表为memory引擎临时表，查询该表的用户需要有process权限

INNODB_SYS_COLUMNS：

  • 提供查询有关InnoDB表列的元数据信息，等同于InnoDB数据字典内部SYS_COLUMNS表的信息

  • 该表为memory引擎临时表，查询该表的用户需要具有process权限

INNODB_SYS_FOREIGN：

  • 提供查询有关InnoDB外键的元数据信息，等同于InnoDB数据字典内部SYS_FOREIGN表的信息

  • 该表为memory引擎临时表，查询该表的用户需要有process权限

INNODB_SYS_TABLESTATS：

  • 提供查询有关InnoDB表的较低级别的状态信息视图。 优化器会使用这些统计信息数据来计算并确定在查询InnoDB表时要使用哪个索引。InnoDB内部无对应的系统表。

  • 该表为memory引擎临时表，查询该表的用户需要有process权限

 

5. InnoDB层 锁、事务、统计信息字典表

INNODB_LOCKS：

  • 提供查询innodb引擎发生阻塞时的事务和锁信息

  • 该表为memory引擎临时表，访问该表需要拥有具有process权限

INNODB_TRX：

  • 提供查询当前在InnoDB引擎中执行的每个事务（不包括只读事务）的信息，包括事务是否正在等待锁、事务开始时间、正在执行的SQL等。

  • 该表为memory引擎临时表，查询该表的用户需要有process权限

INNODB_BUFFER_PAGE_LRU：

  • 提供查询缓冲池中的页面信息，保存有关innodb buffer pool中的页如何进入LRU链表以及在buffer pool不够用时确定需要从缓冲池中逐出哪些页

  • 该表为Memory引擎临时表

INNODB_LOCK_WAITS：

  • 提供查询关于每个被阻塞的InnoDB事务的锁等待记录，包括被阻塞事务请求的锁和阻塞者被授予的锁

  • 该表为memory引擎表，访问该表用户需要有process权限

INNODB_TEMP_TABLE_INFO：

  • 提供查询有关在InnoDB实例中当前处于活动状态的用户创建的InnoDB临时表的信息，不提供查询优化器使用的内部InnoDB临时表的信息查询。

  • INNODB_TEMP_TABLE_INFO表在首次查询时创建。

  • 该表为memory引擎临时表，查询该表的用户需要有process权限

INNODB_BUFFER_PAGE：

  • 提供查询关于buffer pool中的页相关的信息

  • 查询该表需要用户具有PROCESS权限，该表为Memory引擎临时表

INNODB_METRICS：

  • 提供更详细的性能信息，是对PERFORMANCE_SCHEMA的补充。可用于检查innodb的整体健康状况、诊断性能瓶颈、资源短缺和应用程序的问题等。

  • 该表为memory引擎临时表，查询该表的用户需要有process权限

INNODB_BUFFER_POOL_STATS：

  • 提供查询一些Innodb buffer pool中的状态信息，该表中记录的信息与SHOW ENGINE INNODB STATUS输出的信息类似相同，另外，innodb buffer pool的一些状态变量也提供了部分相同的值

  • 查看该表需要有process权限，该表为Memory引擎临时表

 

6. InnoDB层 全文索引字典表

INNODB_FT_CONFIG：

  • 提供查询有关InnoDB表的FULLTEXT索引和关联的元数据信息。查询此表之前，需要先设置innodb_ft_aux_table='db_name/tb_name'，db_name/tb_name为包含全文索引的表名和库名。

  • 查询该表的账户需要有PROCESS权限，该表为Memory引擎临时表

INNODB_FT_BEING_DELETED：

  • 该表仅在OPTIMIZE TABLE语句执行维护操作期间作为INNODB_FT_DELETED表的快照数据存放使用。运行OPTIMIZE TABLE语句时，会先清空INNODB_FT_BEING_DELETED表中的数据，保存INNODB_FT_DELETED表中的快照数据到INNODB_FT_BEING_DELETED表，并从INNODB_FT_DELETED表中删除DOC_ID。由于INNODB_FT_BEING_DELETED表中的内容通常生命周期较短，因此该表中的数据对于监控或者调试来说用处并不大。

  • 表中默认不记录数据，需要设置系统配置参数innodb_ft_aux_table=string（string表示db_name.tb_name字符串），并创建好全文索引，设置好停用词等。

  • 查询该表的账户需要有PROCESS权限，该表为Memory引擎临时表

INNODB_FT_DELETED：

  • 提供查询从InnoDB表的FULLTEXT索引中删除的行信息。它的存在是为了避免在InnoDB FULLTEXT索引的DML操作期间进行昂贵的索引重组操作，新删除的全文索引中单词的信息将单独存储在该表中，在执行文本搜索时从中过滤出搜索结果，该表中的信息仅在执行OPTIMIZE TABLE语句时清空。

  • 该表中的信息默认不记录，需要使用innodb_ft_aux_table选项(该选项默认值为空串)指定需要记录哪个innodb引擎表的信息，例如：test/test。

  • 查询该表的账户需要有PROCESS权限，该表为Memory引擎临时表

INNODB_FT_DEFAULT_STOPWORD：

  • 该表为默认的全文索引停用词表，提供查询停用词列表值。启用停用词表需要开启参数innodb_ft_enable_stopword=ON，该参数默认为ON，启用停用词功能之后，如果innodb_ft_user_stopword_table选项（针对指定的innodb引擎表中的全文索引生效）自定义了停用词库表名称值，则停用词功能使用innodb_ft_user_stopword_table选项指定的停用词表，如果innodb_ft_user_stopword_table选项未指定，而innodb_ft_server_stopword_table选项（针对所有的innodb引擎表中的全文索引生效）自定义了停用词库表名称值，则同停用词功能使用innodb_ft_server_stopword_table选项指定的停用词表，如果innodb_ft_server_stopword_table选项也未指定，则使用默认的停用词表，即INNODB_FT_DEFAULT_STOPWORD表。

  • 查询该表需要账户有PROCESS权限，该表为Memory引擎临时表

INNODB_FT_INDEX_TABLE：

  • 提供查询关于innodb表全文索引中用于反向文本查找的倒排索引的分词信息。

  • 查询该表的账户需要有PROCESS权限，该表为Memory引擎临时表

INNODB_FT_INDEX_CACHE：

  • 提供查询包含FULLTEXT索引的innodb存储引擎表中新插入行的全文索引标记信息。它存在的目的是为了避免在DML操作期间进行昂贵的索引重组，新插入的全文索引的单词的信息被单独存储在该表中，直到对表执行OPTIMIZE TABLE语句时、或者关闭服务器时、或者当高速缓存中存放的信息大小超过了innodb_ft_cache_size或innodb_ft_total_cache_size系统配置参数指定的大小才会执行清理。默认不记录数据，需要使用innodb_ft_aux_table系统配置参数指定需要记录哪个表中的新插入行的全文索引数据。

  • 查询该表的账户需要有PROCESS权限，该表为Memory引擎临时表

 

7. InnoDB层 压缩相关字典表

INNODB_CMP和INNODB_CMP_RESET：

  • 与压缩的InnoDB表页有关的操作的状态信息，表中数据为测量数据库中的InnoDb表压缩的有效性提供参考。

  • 查询表的用户必须具有PROCESS权限，该表为Memory引擎临时表

INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET：

  • InnoDB压缩表数据和索引相关的操作状态信息，对数据库、表、索引的每个组合使用不同的统计信息，以便为评估特定表的压缩性能和实用性提供参考数据。

  • 对于InnoDB压缩表，会对表中的数据和所有二级索引都进行压缩。此时表中的数据被视为包含所有数据列的聚集索引。

  • 注意：由于为每个索引收集单独的度量值会导致性能大幅度降低，因此默认情况下不收集INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET表统计信息。如果确有需要，启用系统配置参数innodb_cmp_per_index_enabled即可（该配置参数为动态变量，默认为OFF）。

  • 查询该表的账户需要有PROCESS权限，该表为Memory引擎临时表

INNODB_CMPMEM和INNODB_CMPMEM_RESET：

  • 这两个表中记录着InnoDB缓冲池中压缩页上的状态信息，为测量数据库中InnoDB表压缩的有效性提供参考

  • 查询该表的账户需要有PROCESS权限，该表为Memory引擎临时表

 

更多内容参考  https://dev.mysql.com/doc/refman/5.7/en/information-schema.html

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/559731
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号