简介
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 MUL PRI] 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💖 其他相关链接
🔗 MySQL经典练习50题
🔗 MySQL 主从复制部署与配置
