第一章 什么是元数据
什么是元数据: 除了表之外的都是元数据,也称之为基表。
比如库表属性、状态信息、权限、列名、数据库名、用户名、版本以及从SHOW语句得到的结果中的大部分字符串是元数据。
第二章 元数据获取
1.通过show语句获取
- 常用show语句
help show;
show databases; # 查询所有库名
show tables; # 查询当前库的所有表名
show tables from world; # 查询world库下的所有表名
show create database world; # 查询world建库语句
show create table city; # 当前库下的city表建表语句
show create table world.city; # world库下的建表语句
show privileges; # 数据库中所有权限
show engines; # 数据库中支持的存储引擎
show grants for root@'localhost' # 查询某个用户权限
show charset; # 查询数据库字符集支持
show collation; # 查询所有校对规则的支持
show variables like '%trx%' # 查询数据库参数
show status like 'com_%' # 查询数据库的状态
show processlist; # 查询所有会话信息
show engine innodb status # 查询innodb引擎相关的状态
show binary logs # 查询二进制日志文件信息
show binlog events in 'xxx' # 查看二进制日志事件
show master status ; # 当前正在使用的二进制日志信息
show slave status\G # 查看主从状态相关信息
show slave hosts; # 查看从库主机信息
2.通过系统表查询元数据
①库表分类
- mysql库
权限表：
user
db
统计信息表 :
innodb_index_stats
innodb_table_stats
- IS(information_schema库)
对象属性(表)：
tables
columns
- PS(performance_schema库)
性能有关
- SYS(库)
性能有关
②IS中的tables和columns的应用
- 数据库资产统计
use information_schema;
desc tables;
-- table_schema 表所在的库
-- table_name 表名
-- engine 存储引擎
-- table_rows 表行数
-- avg_row_length 平均行长度
-- index_length 索引长度
-- data_free 碎片的大小
- 统计案例
--- 例子1 ：每个业务库表的个数和名称
mysql> select table_schema,count(*),group_concat(table_name)
from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys')
group by table_schema;
+--------------+----------+------------------------------+
| table_schema | count(*) | group_concat(table_name) |
+--------------+----------+------------------------------+
| younggirl | 1 | student |
| oldli | 2 | student,stu |
| school | 4 | sc,course,teacher,student |
| world | 3 | countrylanguage,country,city |
+--------------+----------+------------------------------+
4 rows in set (0.01 sec)
--- 例子2： 统计每个库的数据量
mysql> select table_schema,SUM(table_rows*avg_row_length+index_length)/1024 as total_KB
from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys')
group by table_schema;
+--------------+----------+
| table_schema | total_KB |
+--------------+----------+
| oldzhang | 16.0000 |
| oldli | 0.0000 |
| school | 63.9795 |
| world | 779.7744 |
+--------------+----------+
4 rows in set (0.01 sec)
--- 例子3： 查询业务表中，所有不是InnoDB引擎的表
mysql> select table_schema,table_name
from information_schema.tables
where
table_schema not in ('mysql','information_schema','performance_schema','sys')
and engine!='innodb';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| oldli | test |
+--------------+------------+
1 row in set (0.00 sec)
- 命令拼接
# 将数据库中不表的引擎不是innodb的全部查询出来并替换成innodb，这时候需要用到mysql命令拼接了。
# 另外IS是虚拟表，无法直接在这个表里面去修改存储引擎，可以使用一下方法实现：
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;")
from information_schema.tables
where
table_schema not in ('mysql','information_schema','performance_schema','sys')
and engine!='innodb'；
+----------------------------------------------------------------------+
| concat("alter table ",table_schema,".",table_name," engine=innodb;") |
+----------------------------------------------------------------------+
| alter table oldli.test engine=innodb; |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
# 或者直接将上述结果导出，然后直接source（生产勿用！！！）
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;")
from information_schema.tables
where
table_schema not in ('mysql','information_schema','performance_schema','sys')
and engine!='innodb' into outfile '/tmp/alter.sql';
$ cat /tmp/alter.sql
alter table oldli.test engine=innodb;
mysql> source /tmp/alter.sql
以上就是MySQL元数据的使用，仅供参考！