MySQL information_schema 系统库介绍

MySQL技术

共 4367字,需浏览 9分钟

 ·

2021-08-21 06:35

前言:


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


  1. information_schema 简介


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


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


undefined

可以很明显看出,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 库,下面分享几条笔者积累的相关查询语句,来看下吧。

undefined

# 查看某个库中的表信息
SELECT
 table_name,
 table_type,
  ENGINE 
FROM
 information_schema.TABLES 
WHERE
 table_schema = 'db5' 
ORDER BY
 table_name

# 查看整个实例占用空间
SELECT
 concatroundsum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
 concatroundsum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB 
FROM
 information_schema.TABLES;

# 查看各个库占用空间
SELECT
 TABLE_SCHEMA,
 concatTRUNCATE ( sum( data_length )/ 1024 / 10242 ), ' MB' ) AS data_size,
 concatTRUNCATE ( sum( index_length )/ 1024 / 10242 ), 'MB' ) AS index_size 
FROM
 information_schema.TABLES 
GROUP BY
 TABLE_SCHEMA 
ORDER BY
 data_length DESC;
  
# 查看某个表占用空间
SELECT
 concatroundsum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
 concatroundsum( 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 下的表,一定会有收获的。

推荐阅读


(点击标题可跳转阅读)

MySQL安全加固方法分享
创建视图与函数,你注意过 DEFINER 是啥意思吗

MySQL触发器介绍

- End -
浏览 37
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报