MySQL information_schema 系统库介绍

2023年 8月 12日 106.8k 0

前言:

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

1. information_schema 简介

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

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

可以很明显看出,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

相关文章

SQL数据库触发器语法详解 (sql数据库触发器语法)

2023-08-06
数据库
语法
触发器

快速简单的删除Oracle数据库字段方法 (删除oracl数据库字段)

2023-08-06
数据库
字段
删除

如何打开社工数据库bak文件 (社工数据库bak怎么打开)

2023-08-06
数据库
打开
社工

实现数据库按拼音排序的方法和技巧 (数据库按拼音排序)

2023-08-06
数据库
排序
按拼音

探究Sybase数据库的性能和功能特点 (sybase数据库怎么样)

2023-08-06
数据库
性能
探究

SQL Server 如何成功建立自己的数据库? (sql server 建立数据库)

2023-08-06
数据库
自己的
建立

如何在Oracle中查看数据库触发器? (oracle查看数据库触发器)

2023-08-06
数据库
查看
触发器

数据库表数据量千万级,对性能影响有多大? (数据库表千万级数据量多吗)

2023-08-06
数据库
级数
有多大

如何使用Oracle按时间导出表数据库? (oracle按时间导出表数据库)

2023-08-06
数据库
导出
如何使用

数据库存储:帖子长期保存,信息永不丢失 (帖子存数据库)

2023-08-06
数据库
丢失
帖子

小米六数据库:全方位数据保障和优化方案 (小米六数据库)

2023-08-05
数据库
优化
小米

简易教程:使用dbe数据库实现数据连接 (dbe数据库 数据连接)

2023-08-05
数据
数据库
连接

Oracle实现多个数据库链接的简便方法 (oracle链接多个数据库)

2023-08-05
数据库
多个
链接

数据库索引:用哪种方法建立? (数据库索引用什么建的)

2023-08-05
索引
数据库
哪种

实现高效缓存同步:Redis数据库技巧大全 (redis 数据库缓存同步)

2023-08-05
数据库
缓存
同步

如何利用数据库实现高效的模糊匹配查询? (数据库实现模糊查询)

2023-08-05
查询
数据库
模糊

数据库有哪些安装方式和位置? (数据库是装在什么上)

2023-08-05
数据库
位置
装在

Lactmed数据库:妈妈们必备的喂养指南 (lactmed 数据库)

2023-08-05
数据库
必备
喂养

数据库设计中的主属性定义及作用 (数据库主属性是什么)

2023-08-05
数据库
定义
属性

数据库分区操作不当常常造成分区不清的问题 (分区不清数据库)

2023-08-05
数据库
分区
不清

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论