mysql8.0版本如何查询元数据?统计企业资源?

1.概述:

1.1 什么是数据库的元数据?

mysql的information_schema数据库是MySQL自带的数据库，它提供了访问数据库元数据的方式.
元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。
  在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。
如数据库名，数据库的表，表栏的数据类型与访问权限等。

　　

1.2 如何查询数据库的元数据?

1):利用各种show命令获取元数据信息

语法: show table status from <库名>;
-- 查询数据库服务中的相应数据表状态 （数据表的状态信息/统计信息-元数据)

show index from db_name.table.name;
-- 查看指定表的索引元数据信息

show status like '%lock%';
-- 查看数据库锁信息元数据

 

2).利用各种select命令获取元数据信息

库:information_schema中的表获取数据.

SCHEMATA表：	提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
TABLES表	提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。
COLUMNS表：	提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
STATISTICS表：	提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。

更多相关表介绍:

https://zhuanlan.zhihu.com/p/88342863

 

3) 利用各种mysql命令行下的命令，不是SQL语句

 

 

SHOW DATABASES;       -- 显示所有的数据库
USE database_name;    -- 切换到指定数据库
SHOW TABLES;          -- 显示所有的数据表
DESC table_name;      -- 显示指定数据表的字段信息
DESCRIBE table_name;
EXPLAIN table_name;   -- 显示指定数据表的索引信息

 

　　

 

2.sql语句查询元数据的实例使用.

企业练习题01： 统计数据库资产信息（数据资产），获取每个库中表的个数和名称信息？

use information_schema;

select table_schema,count(*),group_concat(table_name)

from information_schema.tables

where table_schema

not in ('mysql','sys','performance_schema','information_schema')

group by table_schema;

企业练习题02：统计数据库资产信息（数据资产），获取每个数据库数据占用磁盘空间 --- 单位M

每个数据库数据占用磁盘空间=数据库中每个表大小做求和运算

每个表的大小=行（100）* 每行数据平均大小

select table_schema,sum(table_rows*avg_row_length+index_length)/1024/1024

from information_schema.tables

where table_schema

 not in ('mysql','sys','performance_schema','information_schema')

group by table_schema;

　　

 

企业练习题03：统计数据库资产信息（数据资产），获取具有碎片信息的表

select table_schema,table_name,data_free

from information_schema.tables

where table_schema

not in ('mysql','sys','performance_schema','information_schema') and data_free >0 ;

 

企业练习题04：进行数据库中表的碎片优化（性能优化操作）

alter table a1 engine=innodb;   

select concat("alter table ",table_schema,".",table_name," engine=innodb")

from information_schema.tables

where table_schema

not in ('mysql','sys','performance_schema','information_schema') and data_free >0 ;

　　

 

查询拼接出优化碎片的sql语句命令:

按列显示

alter table test.t1 engine=innodb;

alter table test01.a1 engine=innodb;  

alter table test02.c1 engine=innodb;

　　

查询拼接优化指令导出为文件,再重新导入数据库执行即可完成优化.

查询并拼接指令导出文件
select concat("alter table ",table_schema,".",table_name," engine=innodb")

from information_schema.tables

Where table_schema

not in ('mysql','sys','performance_schema','information_schema') and data_free >0 into outfile /tmp/alter.sql;

 
查看导出的sql语句
cat /tmp/alter.sql

alter table test.t1 engine=innodb;

alter table test01.a1 engine=innodb;  

alter table test02.c1 engine=innodb;

 
加载sql语句,进行修改数据库表的优化碎片
mysql -uroot -p123456 </tmp/alter.sql

alter table test.t1 engine=innodb;

alter table test01.a1 engine=innodb;  

alter table test02.c1 engine=innodb;

　　

 

企业练习题05：统计数据库资产信息（数据资产），修改数据库中非innodb表信息替换成innodb

模拟创建非innodb（行级锁）--myisam（表级锁）

 

步骤一：获取数据库中所有非innodb

select table_schema,table_name,engine

from information_schema.tables

where table_schema not in ('mysql','sys','performance_schema','information_') and engine!='innodb';

 

步骤二：将非innodb表改为innodb

拼凑修改引擎命令

select concat("alter table ",table_schema,".",table_name," engine=innodb") from information_schema.tables

where table_schema

not in ('mysql','sys','performance_schema','information_') and  engine !='innodb';

或者

select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables

where table_schema not in ('mysql','sys','performance_schema','information_') and  engine!='innodb' into outfile '/tmp/alter.sql';　

 

注:由于一些目录的特殊权限的问题,数据库服务无法访问一些目录,所以数据的导出默认是导出到数据库服务的数据目录.data/下
如果要指定数据库服务导入数据的路径,可以配置加载权限.

vim /etc/my.cnf

[mysqld]

secure-file-priv=/tmp

-- 修改配置文件参数信息，实现将数据库操作的数据信息导入到系统文件中，配置完毕重启数据库服务


数据库客户端连接服务端加载sql文件,执行命令.
mysql> source /tmp/alter.sql

　　alter table test.t1 engine=innodb;

　　alter table test01.a1 engine=innodb;  

　　alter table test02.c1 engine=innodb;