admin管理员组文章数量:1638806
MySQL学习系列
1. 什么是 information_schema
information_schema 提供了对数据库元数据、 统计信息以及有关 MySQL Server 信息的访问(例如: 数据库名或表名、 字段的数据类型和访问权限等)。该库中保存的信息也可以称为 MySQL 的数据字典或系统目录。
在每个MySQL 实例中都有一个独立的information_schema, 用来存储MySQL实例中所有其他数据库的基本信息。information_schema 库下包含多个只读表(非持久表) , 所以在磁盘中的数据目录下没有对应的关联文件, 且不能对这些表设置触发器。 虽然在查询时可以使用 USE 语句将默认数据库设置为information_schema, 但该库下的所有表是只读的, 不能执行 INSERT、 UPDATE、DELETE 等数据变更操作。
针对 information_schema 下的表的查询操作可以替代一些 SHOW 查询语句(例如: SHOW DATABASES、 SHOW TABLES 等) 。
注意: 根据 MySQL 版本的不同, 表的个数和存放是有所不同的。 在 MySQL 5.6版本中总共有 59 个表, 在 MySQL 5.7 版本中, 该 schema 下总共有 61 个表,
在 MySQL 8.0 版本中, 该 schema 下的数据字典表(包含部分原 Memory 引擎临时表) 都迁移到了 mysql schema 下, 且在 mysql schema 下这些数据字典表被隐藏, 无法直接访问, 需要通过 information_schema 下的同名表进行访问。
information_schema 下的所有表使用的都是 Memory 和 InnoDB 存储引擎,且都是临时表, 不是持久表, 在数据库重启之后这些数据会丢失。 在 MySQL 的4 个系统库中,information_schema 也是唯一一个在文件系统上没有对应库表的目录和文件的系统库。
2. information_schema 表分类
Server 层的统计信息字典表
-
COLUMNS
提供查询表中的列(字段) 信息。 -
KEY_COLUMN_USAGE
1. 提供查询哪些索引列存在约束条件。
2. 该表中的信息包含主键、 唯一索引、 外键等约束信息, 例如: 所在的库表列名、 引用的库表列名等。 该表中的信息与 TABLE_CONSTRAINTS 表中记录的信息有些类似, 但 TABLE_CONSTRAINTS 表中没有记录约束引用的库表列信息,而 KEY_COLUMN_USAGE 表中却记录了 TABLE_CONSTRAINTS 表中所没有的约束类型。 -
REFERENTIAL_CONSTRAINTS
提供查询关于外键约束的一些信息。 -
STATISTICS
提供查询关于索引的一些统计信息, 一个索引对应一行记录。 -
TABLE_CONSTRAINTS
提供查询与表相关的约束信息。 -
FILES
提供查询与 MySQL 的数据表空间文件相关的信息。 -
ENGINES
提供查询 MySQL Server 支持的引擎相关信息。 -
TABLESPACES
提供查询关于活跃表空间的相关信息(主要记录的是 NDB 存储引擎的表空间信息) 。
注意: 该表不提供有关 InnoDB 存储引擎的表空间信息。 对于 InnoDB 表空间的元数据信息, 请查询 INNODB_SYS_TABLESPACES 表和INNODB_SYS_DATAFILES 表。 另外, 从 MySQL 5.7.8 开始,INFORMATION_SCHEMA.FILES 表也提供查询 InnoDB 表空间的元数据信息。
- SCHEMATA
提供查询 MySQL Server 中的数据库列表信息, 一个 schema 就代表一个数据库。
Server 层的表级别对象字典表
-
VIEWS
提供查询数据库中的视图相关信息。 查询该表的账户需要拥有 show view权限。 -
TRIGGERS
提供查询关于某个数据库下的触发器相关信息。 -
TABLES
提供查询与数据库内的表相关的基本信息。 -
ROUTINES
提供查询关于存储过程和存储函数的信息(不包括用户自定义函数。该表中的信息与 mysql.proc 中记录的信息相对应(如果该表中有值的话) 。 -
PARTITIONS
提供查询关于分区表的信息。 -
EVENTS
提供查询与计划任务事件相关的信息。 -
PARAMETERS
提供有关存储过程和函数的参数信息, 以及有关存储函数的返回值信息。这些参数信息与 mysql.proc 表中的 param_list 列记录的内容类似。
Server 层的混杂信息字典表
-
GLOBAL_STATUS、GLOBAL_VARIABLES、SESSION_STATUS、SESSION_VARIABLES
提供查询全局、 会话级别的状态变量与系统变量信息。 -
OPTIMIZER_TRACE
提供优化程序跟踪功能产生的信息。
跟踪功能默认是关闭的, 使用 optimizer_trace 系统变量启用跟踪功能。如果开启该功能, 则每个会话只能跟踪它自己执行的语句, 不能看到其他会话执行的语句, 且每个会话只能记录最后一条跟踪的 SQL 语句。 -
PLUGINS
提供查询关于 MySQL Server 支持哪些插件的信息。 -
PROCESSLIST
提供查询一些关于线程运行过程中的状态信息。 -
PROFILING
提供查询关于语句性能分析的信息。 其记录内容对应于 SHOW PROFILES和 SHOW PROFILE 语句产生的信息。 该表只有在会话变量 profiling=1 时才会记录语句性能分析信息, 否则该表不记录。
注意: 从 MySQL 5.7.2 开始, 此表不再推荐使用, 在未来的 MySQL 版本中删除, 改用 Performance Schema 代替。 -
CHARACTER_SETS
提供查询 MySQL Server 支持的可用字符集。 -
COLLATIONS
提供查询 MySQL Server 支持的可用校对规则。 -
COLLATION_CHARACTER_SET_APPLICABILITY
提供查询 MySQL Server 中哪种字符集适用于什么校对规则。 查询结果集相当于从 SHOW COLLATION 获得的结果集的前两个字段值。 目前并没有发现该表有太大的作用。 -
COLUMN_PRIVILEGES
提供查询关于列( 字段) 的权限信息, 表中的内容来自 mysql.column_priv列权限表( 需要针对一个表的列单独授权之后才会有内容) 。 -
SCHEMA_PRIVILEGES
提供查询关于库级别的权限信息, 每种类型的库级别权限记录一行信息,该表中的信息来自 mysql.db 表。 -
TABLE_PRIVILEGES
提供查询关于表级别的权限信息, 该表中的内容来自 mysql.tables_priv表。 -
USER_PRIVILEGES
提供查询全局权限的信息, 该表中的信息来自 mysql.user 表。
InnoDB 层的系统字典表
-
INNODB_SYS_DATAFILES
提供查询 InnoDB 所有表空间类型文件的元数据( 内部使用的表空间 ID和表空间文件的路径信息) , 包括独立表空间、 常规表空间、 系统表空间、 临时表空间和 undo 空间( 如果开启了独立 undo 空间的话) 。
该表中的信息等同于 InnoDB 数据字典内部 SYS_DATAFILES 表的信息。 -
INNODB_SYS_VIRTUAL
提供查询有关 InnoDB 虚拟生成列和与之关联的列的元数据信息, 等同于InnoDB 数据字典内部 SYS_VIRTUAL 表的信息。 该表中展示的行信息是与虚拟生成列相关联列的每个列的信息。 -
INNODB_SYS_INDEXES
提供查询有关 InnoDB 索引的元数据信息, 等同于 InnoDB 数据字典内部SYS_INDEXES 表中的信息。 -
INNODB_SYS_TABLES
提供查询有关 InnoDB 表的元数据信息, 等同于 InnoDB 数据字典内部SYS_TABLES 表的信息。 -
INNODB_SYS_FIELDS
提供查询有关 InnoDB 索引键列( 字段) 的元数据信息, 等同于 InnoDB数据字典内部 SYS_FIELDS 表的信息。 -
INNODB_SYS_TABLESPACES
提供查询有关 InnoDB 独立表空间和普通表空间的元数据信息( 也包含了全文索引表空间) , 等同于 InnoDB 数据字典内部 SYS_TABLESPACES 表的信息。 -
INNODB_SYS_FOREIGN_COLS
提供查询有关 InnoDB 外键列的状态信息, 等同于 InnoDB 数据字典内部SYS_FOREIGN_COLS 表的信息。 -
INNODB_SYS_COLUMNS
提供查询有关 InnoDB 表列的元数据信息, 等同于 InnoDB 数据字典内部SYS_COLUMNS 表的信息。 -
INNODB_SYS_FOREIGN
提供查询有关 InnoDB 外键的元数据信息, 等同于 InnoDB 数据字典内部SYS_FOREIGN 表的信息。 -
INNODB_SYS_TABLESTATS
提供查询有关 InnoDB 表的较低级别的状态信息视图。 MySQL 优化器会使用这些统计信息数据来计算并确定在查询 InnoDB 表时要使用哪个索引。 这些信息保存在内存中的数据结构中, 与存储在磁盘上的数据无对应关系。 在 InnoDB内部也无对应的系统表。
InnoDB 层的锁、 事务、 统计信息字典表
-
INNODB_LOCKS
提供查询 InnoDB 引擎中事务正在请求的且同时被其他事务阻塞的锁信息(即没有发生不同事务之间锁等待的锁信息, 在这里是查看不到的。 例如, 当只有一个事务时, 无法查看到该事务所加的锁信息) 。 该表中的内容可用于诊断高并发下的锁争用信息。 -
INNODB_TRX
提供查询当前在 InnoDB 引擎中执行的每个事务(不包括只读事务) 的信息, 包括事务是否正在等待锁、 事务什么时间点开始, 以及事务正在执行的 SQL语句文本信息等(如果有 SQL 语句的话) 。 -
INNODB_BUFFER_PAGE_LRU
提供查询缓冲池中的页面信息。 与 INNODB_BUFFER_PAGE 表不同,INNODB_BUFFER_PAGE_LRU表保存有关InnoDB缓冲池中的页如何进入LRU链表,以及在缓冲池不够用时确定需要从中逐出哪些页的信息。 -
INNODB_LOCK_WAITS
提供查询 InnoDB 事务的锁等待信息。 如果查询该表为空, 则表示无锁等待信息; 如果查询该表中有记录, 则说明存在锁等待, 表中的每一行记录表示一个锁等待关系。 在一个锁等待关系中包含: 一个等待锁(即, 正在请求获得锁)的事务及其正在等待的锁等信息、 一个持有锁(这里指的是发生锁等待事务正在请求的锁) 的事务及其所持有的锁等信息。 -
INNODB_TEMP_TABLE_INFO
提供查询有关在 InnoDB 实例中当前处于活动状态的用户(只对已建立连接的用户有效, 断开的用户连接对应的临时表会被自动删除) 创建的 InnoDB 临时表的信息。 它不提供查询优化器使用的内部 InnoDB 临时表的信息。 该表在首次查询时创建。 -
INNODB_BUFFER_PAGE
提供查询关于缓冲池中的页相关信息。 -
INNODB_METRICS
提供查询 InnoDB 更为详细的性能信息, 是对 InnoDB 的performance_schema 的补充。 通过对该表的查询, 可用于检查 InnoDB 的整体健康状况, 也可用于诊断性能瓶颈、 资源短缺和应用程序的问题等。 -
INNODB_BUFFER_POOL_STATS
提供查询一些 InnoDB 缓冲池中的状态信息, 该表中记录的信息与 SHOW ENGINE INNODB STATUS 语句输出的缓冲池统计部分信息类似。 另外, InnoDB 缓冲池的一些状态变量也提供了部分相同的值
InnoDB 层的全文索引字典表
- INNODB_FT_CONFIG
- INNODB_FT_BEING_DELETED
- INNODB_FT_DELETED
- INNODB_FT_DEFAULT_STOPWORD
- INNODB_FT_INDEX_TABLE
InnoDB 层的压缩相关字典表
-
INNODB_CMP 和 INNODB_CMP_RESET
这两个表中的数据包含了与压缩的 InnoDB 表页有关的操作状态信息。 表中记录的数据为测量数据库中的 InnoDB 表压缩的有效性提供参考。 -
INNODB_CMP_PER_INDEX 和 INNODB_CMP_PER_INDEX_RESET
这两个表中记录了与 InnoDB 压缩表数据和索引相关的操作状态信息, 对数据库、 表、 索引的每个组合使用不同的统计信息, 以便为评估特定表的压缩性能和实用性提供参考数据。 -
INNODB_CMPMEM 和 INNODB_CMPMEM_RESET
这两个表中记录了 InnoDB 缓冲池中压缩页的状态信息, 为测量数据库中InnoDB 表压缩的有效性提供参考。
2. information_schema 应用
查看索引列的信息
INNODB_SYS_FIELDS 表提供查询有关 InnoDB 索引列( 字段) 的元数据信息,等同于 InnoDB 数据字典中 SYS_FIELDS 表的信息。
INNODB_SYS_INDEXES 表提供查询有关 InnoDB 索引的元数据信息, 等同于InnoDB 数据字典内部 SYS_INDEXES 表中的信息。
INNODB_SYS_TABLES 表提供查询有关 InnoDB 表的元数据信息, 等同于InnoDB 数据字典中 SYS_TABLES 表的信息。
select t.name as d_t_name,i.name as i_name ,i.type as i_type,i.N_FIELDS as i_column_numbers,f.name as
i_column_name,f.pos as i_position from INNODB_SYS_TABLES as t join INNODB_SYS_INDEXES as i on
t.TABLE_ID=i.TABLE_ID left join INNODB_SYS_FIELDS as f on i.INDEX_ID=f.INDEX_ID where t.name='world/city';
结果中的列都很好理解, 唯一需要解释的是i_type(INNODB_SYS_INDEXES.type), 它是表示索引类型的数字 ID, 0 =二级索引、1=集群索引、 2 =唯一索引、 3 =主键索引、 32 =全文索引、 64 =空间索引、 128 =包含虚拟生成列的二级索引
本文标签: 系统mysqlinformationschema
版权声明:本文标题:MySQL 中的系统库之information_schema 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://m.elefans.com/dongtai/1729277627a1193700.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论