简介

MySQL 安装完成之后会生成, information_schema , mysql, performance_schema, sys 四个数据库,下面我们解析这几个数据库

方法 / 步骤

🚩MySQL 系统数据库解析

🌈 一: information_schema 系统库

供了访问数据库元数据的方式。（元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等）
换句换说，information_schema是一个信息数据库，它保存着关于MySQL服务器所维护的所有其他数据库的信息。

PS: information_schema 系统库 有几张只读表，它们实际上是视图，而不是基本表

1.1 主要表
•SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

•TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。 

•COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。 

•STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。 

•USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。 

•SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。 

•TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。 

•COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

•CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。 

•COLLATIONS表：提供了关于各字符集的对照信息。

•COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。 

•TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。 

•KEY_COLUMN_USAGE表：描述了具有约束的键列。 

•ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

•VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。 

•TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表。
1.1.1 TABLES表
-- 用法：
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='数据库名';
  • 字段说明

字段

含义

Table_catalog

数据表登记目录

Table_schema

索引所属表的数据库名

Table_name

索引所属的表名

Non_unique

字段不唯一的标识

Index_schema

索引所属的数据库名（一般与table_schema值相同）

Index_name

索引名称

Seq_in_index

Column_name

索引列的列名

Collation

校对，列值全显示为A

Cardinality

基数（一般与该表的数据行数相同）

Sub_part

Packed

是否包装过，默认为NULL

Nullable

是否为空 YES / NO

Index_type

索引的类型，列值全显示为BTREE（平衡树索引）

Comment

索引注释、备注

1.1.2 COLUMNS表

提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息

-- 用法
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名'；
  • 各字段说明：

字段

含义

Table_catalog

数据表登记目录

Table_schema

数据表所属的数据库名

Table_name

所属的表名称

Column_name

列名称

Ordinal_position

字段在表中第几列

Column_default

列的默认数据

Is_nullable

字段是否可以为空

Data_type

数据类型

Character_maximum_length

字符最大长度

Character_octet_length

字节长度？

Numeric_precision

数据精度

Numeric_scale

数据规模

Character_set_name

字符集名称

Collation_name

字符集校验名称

Column_type

列类型

Column_key

关键列[NULL

Extra

额外描述 NULL / on / update / CURRENT_TIMESTAMP / auto_increment

Privileges

字段操作权限 select / select / insert / update / references

Column_comment

字段注释、描述

1.1.3 KEY_COLUMN_USAGE表

存取表的健值

-- 用法
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名';
  • 各字段的说明:

字段

含义

Constraint_catalog

约束登记目录

Constraint_schema

约束所属的数据库名

Constraint_name

约束的名称

Table_catalog

数据表等级目录

Table_schema

键值所属表所属的数据库名（一般与Constraint_schema值相同）

Table_name

键值所属的表名

Column_name

键值所属的列名

Ordinal_position

键值所属的字段在表中第几列

Position_in_unique_constraint

键值所属的字段在唯一约束的位置（若为外键值为1）

Referenced_talble_schema

外键依赖的数据库名（一般与Constraint_schema值相同）

Referenced_talble_name

外键依赖的表名

Referenced_column_name

外键依赖的列名

1.1.4 TABLE_CONSTRAINTS表

存储主键约束、外键约束、唯一约束、check约束

-- 用法
SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名';
  • 各字段的说明:

字段

含义

Constraint_catalog

约束登记目录

Constraint_schema

约束所属的数据库名

Constraint_name

约束的名称

Table_schema

约束依赖表所属的数据库名（一般与Constraint_schema值相同）

Table_name

约束所属的表名

Constraint_type

约束类型 primary key / foreign key / unique / check

1.1.5 STATISTICS表

提供了关于表索引的信息

-- 用法
SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名';
  • 各字段的说明:

字段

含义

Table_catalog

数据表登记目录

Table_schema

索引所属表的数据库名

Table_name

索引所属的表名

Non_unique

字段不唯一的标识

Index_schema

索引所属的数据库名（一般与table_schema值相同）

Index_name

索引名称

Seq_in_index

Column_name

索引列的列名

Collation

校对，列值全显示为A

Cardinality

基数（一般与该表的数据行数相同）

Sub_part

Packed 是否包装过，默认为NULL

Nullable

是否为空 YES / NO

Index_type

索引的类型，列值全显示为BTREE（平衡树索引）

Comment

索引注释、备注

🌈 二: mysql 系统库

mysql的核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。(常用的，在mysql.user表中修改root用户的密码)

🌈 三: performance_schema 系统库

主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。

🌈 四: sys 系统库

Sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。

🚩 MySQL 指标查询

一: 查看所有数据库容量大小

select
    table_schema as '数据库',
    sum(table_rows) as '记录数',
    sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
    sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
    from information_schema.tables
    group by table_schema
    order by sum(data_length) desc, sum(index_length) desc;

二: 查看所有数据库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc

三: 查看指定数据库容量大小

例：查看mysql库容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

四: 查看指定数据库各表容量大小

例：查看mysql库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;
-- 以单行1k大小,预算数据库下每个数据库每张表可以存储最大多少数据 如果直接指定表 可以在where 条件添加  AND TABLE_NAME = 'table_name'
-- TABLE_SCHEMA 字段对应目标数据库的库名称
SELECT
	TABLE_NAME,
	CONCAT( ROUND( SUM( AVG_ROW_LENGTH / 1024 ), 2 ), 'KB' ) AS recordSize,
	CONCAT( 1 / ( AVG_ROW_LENGTH / 1024 ) * 2500, ' w' ) AS preMaxStoreSize 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = 'database_name' AND AVG_ROW_LENGTH > 0 
GROUP BY
	TABLE_NAME 
ORDER BY
	preMaxStoreSize DESC