MySQL进阶:INFORMATION_SCHEMA 简介

Wesley13
• 阅读 521

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

MySQL进阶:INFORMATION_SCHEMA 简介

在 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 表来说,必须拥有 PROCESS 权限才能查看。

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

不同表的用途

在不同版本的 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 的统计信息

KEY_COLUMN_USAGE

描述了索引列有哪些约束

PARAMETERS

存储过程参数,返回值信息

PARTITIONS

表分区信息,没一行对应了一个独立的分区或者分区表的子分区

PROCESSLIST

提供了哪些线程正在运行的信息

PROFILING

提供了语句剖析信息,它的内容对应了 SHOW PROFILE 和 SHOW PROFILES 语句的信息

REFERENTIAL_CONSTRAINTS

外键信息

ROUTINES

存储过程信息

SCHEMATA

数据库的信息

SCHEMA_PRIVILEGES

数据库权限信息,数据来源于 mysql.db 系统表

STATISTICS

表索引信息

SYSTEM_VARIABLES

所有系统变量当前的值和各种元数据

TABLES

表的信息

TABLESPACES

MySQL 集群的表空间信息

TABLE_CONSTRAINTS

描述了哪个表有约束

TABLE_PRIVILEGES

表权限信息,数据来源于 mysql.table_priv 系统表

TRIGGERS

关于触发器的信息,必须有表的 TRIGGER 权限才能查看

USER_PRIVILEGES

全局权限信息,数据来源于 mysql.user 系统表

VIEWS

数据库视图信息

GEOMETRY_COLUMNS

表中存储空间数据的列的信息

SPATIAL_REF_SYS

存储了数据库中使用的每个空间参考系统的信息

CLIENT_STATISTICS

客户端连接的统计信息,作为 用户统计 特性的一部分,默认不开启

USER_STATISTICS

用户活动的统计信息,作为 用户统计 特性的一部分,默认不开启

INDEX_STATISTICS

索引使用统计,用于定位未使用的索引以及生成删除命令,作为 用户统计 特性的一部分,默认不开启

TABLE_STATISTICS

表使用的统计信息,作为 用户统计 特性的一部分,默认不开启

在所有的存储引擎中,我们最常用的就是 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

关于 数据清理 的信息

INNODB_CMPMEM

缓冲池中压缩页的信息,可用于度量表压缩效率

INNODB_CMPMEM_RESET

INNODB_CMPEM,但是每次查询这个表会清空 RELOCATION_TIME 字段的值

INNODB_CMP_PER_INDEX

包含了以独立的索引分组的与压缩操作相关的状态信息

INNODB_CMP_PER_INDEX_RESET

INNODB_CMP_PER_INDEX, 但是每次查询之后都会清空数据

INNODB_CMP

包含了与压缩操作相关的状态信息

INNODB_CMP_RESET

INNODB_CMP,但是每次查询之后会清空数据

INNODB_LOCK_WAITS

阻塞的事务信息

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_DELETED

OPTIMIZE TABLE 正在执行中,此时发生了与 INNODB_FT_DELETED 有关的文档

INNODB_FT_DEFAULT_STOPWORD

包含了用于创建全文索引的停止词列表

INNODB_FT_CONFIG

全文索引的元数据

INNODB_TRX

所有当前正在执行的事务的信息

INNODB_LOCKS

包含了事务请求但是未获得的锁或者阻塞其它事务的锁的信息

INNODB_METRICS

一些有用的性能指标

INNODB_MUTEXES

监控互斥锁和读写锁

总结

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

本文将会持续修正和更新,最新内容请参考我的 GITHUB 上的 程序猿成长计划 项目,欢迎 Star,更多精彩内容请 follow me

参考文档

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这