Database
INFORMATION_SCHEMA

MySQL 进阶:INFORMATION_SCHEMA

mylxsw, 10/30/2019 mysql

在使用命令行连接 MySQL 的时候,我们在执行 SHOW DATABASES 命令时,会发现除了自己拥有权限的数据库之外,还有另外一个名为 INFORMATION_SCHEMA 的表,这个表示用来做什么用的呢?

-w677

在 MySQL 中, INFORMATION_SCHEMA 是用来访问数据库的元数据(比如数据库,表的名称,列的数据类型或者访问权限等)的,在每个 MySQL 的实例中,INFORMATION_SCHEMA 保存了它维护的所有数据库的信息,这个库中包含了很多只读的表(它们实际上是一些视图,因此并没有与之关联的文件,你可以无法为他们创建触发器),用于满足对 MySQL 服务本身的不同查询需求。

你可以通过 USE 语句选择使用 INFORMATION_SCHEMA 作为默认的数据库,但是只能对其执行读取操作,无法执行 INSERTUPDATEDELETE 操作。

比如,下面的的 SQL 可以查询出数据库 wizard 中所有的表以及数据类型,存储引擎。

mysql> SELECT table_name, table_type, engine
    -> FROM information_schema.tables
    -> WHERE table_schema = 'wizard'
    -> ORDER BY table_name;
+----------------------+------------+---------+
| table_name           | table_type | engine  |
+----------------------+------------+---------+
| migrations           | BASE TABLE | InnoDB  |
| notifications        | BASE TABLE | InnoDB  |
| wz_attachments       | BASE TABLE | InnoDB  |
| wz_categories        | BASE TABLE | InnoDB  |
| wz_comments          | BASE TABLE | InnoDB  |
| wz_groups            | BASE TABLE | InnoDB  |
| wz_operation_logs    | BASE TABLE | ARCHIVE |
| wz_pages             | BASE TABLE | InnoDB  |
| wz_page_histories    | BASE TABLE | InnoDB  |
| wz_page_share        | BASE TABLE | InnoDB  |
| wz_page_tag          | BASE TABLE | InnoDB  |
| wz_password_resets   | BASE TABLE | InnoDB  |
| wz_projects          | BASE TABLE | InnoDB  |
| wz_project_catalogs  | BASE TABLE | InnoDB  |
| wz_project_group_ref | BASE TABLE | InnoDB  |
| wz_project_stars     | BASE TABLE | InnoDB  |
| wz_tags              | BASE TABLE | InnoDB  |
| wz_templates         | BASE TABLE | InnoDB  |
| wz_users             | BASE TABLE | InnoDB  |
| wz_user_group_ref    | BASE TABLE | InnoDB  |
+----------------------+------------+---------+
20 rows in set (0.00 sec)

在 MySQL 中,每个用户都有对 INFORMATION_SCHEMA 的访问权限,但是只能看到表中他们有权限的对象的信息,也有点些场景下用户如果没有权限,看到的是 NULL。对于 InnoDB (opens in a new tab) 表来说,必须拥有 PROCESS (opens in a new tab) 权限才能查看。

由于使用 INFORMATION_SCHEMA 查询可能会从多个数据库检索信息,所以查询可能会比较耗时,对性能产生一定的影响。在执行之前,可以使用 EXPLAIN 命令检查一下查询的效率,关于如何优化 INFORMATION_SCHEMA 查询效率,参考 Optimizing INFORMATION_SCHEMA Queries (opens in a new tab)

不同表的用途

在不同版本的 MySQL/MariaDB 中, INFORMATION_SCHEMA 中的表并不完全一样,但是大部分都是一致的,下面是 MariaDB 10.3 中包含的表,我对它们一一做了注释

表名用途
ALL_PLUGINS服务器所有插件的信息,无论是否已经安装
PLUGINS服务器安装的插件信息
APPLICABLE_ROLES当前用户可以使用的角色信息
CHARACTER_SETS可用的字符集信息
CHECK_CONSTRAINTS表上定义的 CHECK 约束信息
COLLATIONS字符集排序规则信息
COLLATION_CHARACTER_SET_APPLICABILITY字符集和排序规则的对应关系
COLUMNS表中的列信息
COLUMN_PRIVILEGES列的权限信息,数据来源于 mysql.columns_priv 系统表
ENABLED_ROLES当前会话的角色信息
ENGINES存储引擎的信息,可以用于检查引擎是否支持
EVENTS关于事件管理器的事件信息
FILES表空间数据存储文件的信息
GLOBAL_STATUS所有的状态变量值,对应命令 SHOW GLOBAL STATUS
GLOBAL_VARIABLES所有的系统变量值,对应命令 SHOW GLOBAL VARIABLES
SESSION_STATUS所有的会话的状态变量值,对应命令 SHOW SESSION STATUS
SESSION_VARIABLES所有的会话变量,对应命令 SHOW SESSION VARIABLES
KEY_CACHES关于 Segmented Key Cache (opens in a new tab) 的统计信息
KEY_COLUMN_USAGE描述了索引列有哪些约束
PARAMETERS存储过程参数,返回值信息
PARTITIONS表分区信息,没一行对应了一个独立的分区或者分区表的子分区
PROCESSLIST提供了哪些线程正在运行的信息
PROFILING提供了语句剖析信息,它的内容对应了 SHOW PROFILE 和 SHOW PROFILES 语句的信息
REFERENTIAL_CONSTRAINTS外键信息
ROUTINES存储过程信息
SCHEMATA数据库的信息
SCHEMA_PRIVILEGES数据库权限信息,数据来源于 mysql.db 系统表
STATISTICS表索引信息
SYSTEM_VARIABLES所有系统变量当前的值和各种元数据
TABLES表的信息
TABLESPACESMySQL 集群的表空间信息
TABLE_CONSTRAINTS描述了哪个表有约束
TABLE_PRIVILEGES表权限信息,数据来源于 mysql.table_priv 系统表
TRIGGERS关于触发器的信息,必须有表的 TRIGGER 权限才能查看
USER_PRIVILEGES全局权限信息,数据来源于 mysql.user 系统表
VIEWS数据库视图信息
GEOMETRY_COLUMNS表中存储空间数据的列的信息
SPATIAL_REF_SYS存储了数据库中使用的每个空间参考系统的信息
CLIENT_STATISTICS客户端连接的统计信息,作为 用户统计 (opens in a new tab) 特性的一部分,默认不开启
USER_STATISTICS用户活动的统计信息,作为 用户统计 (opens in a new tab) 特性的一部分,默认不开启
INDEX_STATISTICS索引使用统计,用于定位未使用的索引以及生成删除命令,作为 用户统计 (opens in a new tab) 特性的一部分,默认不开启
TABLE_STATISTICS表使用的统计信息,作为 用户统计 (opens in a new tab) 特性的一部分,默认不开启

在所有的存储引擎中,我们最常用的就是 InnoDB 存储引擎了,下面是 InnoDB 相关的表

表名用途
INNODB_SYS_DATAFILES数据文件存储路径信息
INNODB_SYS_TABLESTATS状态信息,可以用于开发性能相关的扩展或者高级的性能监控
INNODB_SYS_FIELDS索引的字段信息
INNODB_SYS_COLUMNS字段信息
INNODB_SYS_FOREIGN_COLS外键列的信息
INNODB_SYS_FOREIGN外键信息
INNODB_SYS_TABLES表信息
INNODB_SYS_TABLESPACES表空间信息
INNODB_SYS_INDEXES索引信息
INNODB_SYS_VIRTUAL虚拟列的元信息
INNODB_SYS_SEMAPHORE_WAITS当前的信号量等待信息
INNODB_TABLESPACES_SCRUBBING关于 数据清理 (opens in a new tab) 的信息
INNODB_CMPMEM缓冲池中压缩页的信息,可用于度量表压缩效率
INNODB_CMPMEM_RESETINNODB_CMPEM,但是每次查询这个表会清空 RELOCATION_TIME 字段的值
INNODB_CMP_PER_INDEX包含了以独立的索引分组的与压缩操作相关的状态信息
INNODB_CMP_PER_INDEX_RESETINNODB_CMP_PER_INDEX, 但是每次查询之后都会清空数据
INNODB_CMP包含了与压缩操作相关的状态信息
INNODB_CMP_RESETINNODB_CMP,但是每次查询之后会清空数据
INNODB_LOCK_WAITS (opens in a new tab)阻塞的事务信息
INNODB_TABLESPACES_ENCRYPTION加密的表空间信息
INNODB_BUFFER_PAGE_LRU有关缓冲池中页的信息,以及出于清除目的如何对页进行排序
INNODB_BUFFER_PAGE缓冲池中页的信息
INNODB_BUFFER_POOL_STATS缓冲池中页的信息,与 SHOW ENGINE INNODB STATUS 语句返回的内容类似
INNODB_FT_INDEX_TABLE全文索引信息
INNODB_FT_DELETED包含了从全文索引中已经删除的行,这些信息用于过滤查询请求的结果,解决每次删除一行时昂贵的重新组织索引操作
INNODB_FT_INDEX_CACHE最近插入到全文索引的行信息,为了避免每次改变都去重新组织索引,新的变更只在 OPTIMIZE TABLE 命令运行之后才会合并到全文索引
INNODB_FT_BEING_DELETEDOPTIMIZE TABLE 正在执行中,此时发生了与 INNODB_FT_DELETED 有关的文档
INNODB_FT_DEFAULT_STOPWORD包含了用于创建全文索引的停止词列表
INNODB_FT_CONFIG全文索引的元数据
INNODB_TRX (opens in a new tab)所有当前正在执行的事务的信息
INNODB_LOCKS (opens in a new tab)包含了事务请求但是未获得的锁或者阻塞其它事务的锁的信息
INNODB_METRICS一些有用的性能指标
INNODB_MUTEXES监控互斥锁和读写锁

总结

本文只是对 INFORMATION_SCHEMA 数据库是什么,都有哪些表以及它们的用途做了个简要的概述,在了解这个数据库的基础之后,我们在下篇文章 (opens in a new tab)中将会详细介绍 事务,锁相关表以及如何排查死锁问题 (opens in a new tab),敬请关注。

参考文档