MySQL information_schema 系统库介绍

分手后的思念是犯贱 2022-09-07 12:20 148阅读 0赞

**前言：**

当我们安装好 MySQL 数据库后，会发现数据库实例自带有 information\_schema 系统库，你是否有去关注过这个系统库呢？是否有查询过此库中的表数据呢？又是否清楚此库存在的具体作用呢？带着这些疑问，我们一起来看本篇文章。

#### 1. information\_schema 简介 ####

information\_schema 顾名思义就是一个信息库，是用来存储数据库的元数据（比如数据库，表的名称，列的数据类型或者访问权限等），在每个 MySQL 实例中，information\_schema 保存了它维护的所有数据库的信息，这个库中包含了很多只读的表（它们实际上可看作为视图，因此并没有与之关联的文件，你也无法为它们创建触发器）。

我们来具体看下 information\_schema 下的表，不同版本的数据库稍有区别，以 5.7.23 版本为例，打开 information\_schema 库，我们发现共有 61 个表。

![image.png][]

可以很明显看出，information\_schema 下的表大部分是 MEMORY 存储引擎，有个别是 InnoDB 存储引擎，再仔细看这些表的创建语句，发现这些表都是临时表。下面展示部分表的作用：

*  CHARACTER\_SETS：可用的字符集信息表。
 *  COLLATIONS：字符集排序规则信息表。
 *  COLUMNS：每个表中的列的信息。
 *  ENGINES：存储引擎的信息，可以用于检查引擎是否支持。
 *  FILES：表空间数据存储文件的信息。
 *  GLOBAL\_STATUS：全局状态变量值。
 *  GLOBAL\_VARIABLES：全局系统变量值。
 *  INNODB\_BUFFER\_PAGE：InnoDB 缓冲池中页的信息。
 *  INNODB\_BUFFER\_POOL\_STATS：InnoDB 缓冲池统计信息。
 *  INNODB\_LOCK\_WAITS：InnoDB 事务锁等待信息
 *  INNODB\_LOCKS：包含了事务请求但是未获得的锁或者阻塞其它事务的锁的信息。
 *  INNODB\_TRX：所有当前正在执行的事务的信息。
 *  PARTITIONS：记录表分区信息。
 *  PLUGINS：服务器安装的插件信息。
 *  PROCESSLIST：记录正在运行的线程的各种信息。
 *  ROUTINES：存储过程及函数信息。
 *  SCHEMATA：数据库的信息。
 *  STATISTICS：表索引信息。
 *  TABLES：表的信息。
 *  TRIGGERS：触发器信息。
 *  VIEWS：数据库视图信息。

#### 2. information\_schema 相关查询 ####

其实，在使用数据库的过程中，你经常与 information\_schema 打交道，当我们想查询 MySQL 中各种对象的信息时，基本上都是从 information\_schema 库中查询得到的。一些常见的 show 语句背后的逻辑也是查询 information\_schema 库，例如：show tables 其实查的就是 information\_schema.TABLES 表；show databases、show processlist 等语句查询的都是 information\_schema 库中的相关表。

我们想了解数据库中的各种信息时，都可以查询 information\_schema 库，下面分享几条笔者积累的相关查询语句，来看下吧。

# 查看某个库中的表信息
    SELECT
    	table_name,
    	table_type,
      ENGINE 
    FROM
    	information_schema.TABLES 
    WHERE
    	table_schema = 'db5' 
    ORDER BY
    	table_name
    
    # 查看整个实例占用空间
    SELECT
    	concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
    	concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB 
    FROM
    	information_schema.TABLES;
    
    # 查看各个库占用空间
    SELECT
    	TABLE_SCHEMA,
    	concat( TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ), ' MB' ) AS data_size,
    	concat( TRUNCATE ( sum( index_length )/ 1024 / 1024, 2 ), 'MB' ) AS index_size 
    FROM
    	information_schema.TABLES 
    GROUP BY
    	TABLE_SCHEMA 
    ORDER BY
    	data_length DESC;
      
    # 查看某个表占用空间
    SELECT
    	concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
    	concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB 
    FROM
    	information_schema.TABLES 
    WHERE
    	table_schema = 'test' 
    	AND table_name = 'test_tb'
      
    # 查看所有线程信息
    SELECT
    	* 
    FROM
    	information_schema.PROCESSLIST
      
    # 查看非睡眠线程信息
    SELECT
    	* 
    FROM
    	information_schema.PROCESSLIST 
    WHERE
    	command != 'sleep'
      
    # 查看某个用户发起的线程信息
    SELECT
    	* 
    FROM
    	information_schema.PROCESSLIST 
    WHERE
    	USER = 'testuser'
     
    # 查看某个字符集支持的所有排序规则
    SELECT
    	COLLATION_NAME,
    	CHARACTER_SET_NAME,
    	IS_DEFAULT
    FROM
    	`information_schema`.`COLLATIONS` 
    WHERE
    	`CHARACTER_SET_NAME` = 'utf8' 
      
    # 查看某个表的分区信息（如果有）
    SELECT
    	TABLE_SCHEMA,
    	TABLE_NAME,
    	PARTITION_NAME 
    FROM
    	`information_schema`.`PARTITIONS` 
    WHERE
    	`TABLE_SCHEMA` = 'test' 
    	AND `TABLE_NAME` = 'tbname'
      
    # 查看某个表的索引信息
    SELECT
    	* 
    FROM
    	`information_schema`.`STATISTICS` 
    WHERE
    	`TABLE_SCHEMA` = 'test' 
    	AND `TABLE_NAME` = 'tbname' 
      
    # 查看 innodb 事务相关信息
    SELECT
    	* 
    FROM
    	information_schema.INNODB_TRX

**总结：**

本文主要介绍 information\_schema 系统库相关内容，我们想知道的数据库元数据信息基本上都可以查到，熟悉 information\_schema 库有助于我们对数据库更加了解，有兴趣的同学可以看下 information\_schema 下的表，一定会有收获的。

[image.png]: /images/20220829/297bf01e699f43f298f489e5295f0c41.png