元数据

元数据

元数据：表示数据的数据，简单来点说就是只要不是我们存储到数据库里的数据都可以理解为元数据

比如库表属性、状态信息、权限、库名、列名、用户名、版本以及从SHOW语句得到的结果中的大部分字符串是元数据

获取

show 语句

show databases;
show tables;
show charset;
show engines; 
......

库表

mysql

权限表

• user
• db

统计信息表

• innodb_index_stats
• innodb_table_stats

is(information_schema库)

对象属性(表)：
tables
columns

ps(performance_schema库)

性能相关

sys库

性能相关

举例

统计

-- 统计每个业务库表的个数和名称
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;

-- 统计每个库的数据量
 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;
-- 查询业务表中，所有不是InnoDB引擎的表
select  table_schema,table_name
from information_schema.tables
where 
table_schema not in ('mysql','information_schema','performance_schema','sys')
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','information_schema','performance_schema','sys')
and engine!='innodb'；