mysql数据库元数据统计

1、统计数据库资产信息（数据资产），获取每个库中表的个数和名称信息；

   select TABLE_SCHEMA,TABLE_NAME  from information_schema.tables where TABLE_SCHEMA not in ('sys','performance_schema','mysql','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;

2、统计数据库资产信息（数据资产），获取每个数据库数据占用磁盘空间

   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;

3、统计数据库资产信息（数据资产），获取具有碎片信息的表

   select table_schema,table_name,data_free 
   from information_schema.tables 
   where table_schema not in ('mysql','sys','performance_schema','information_') and data_free >0 ;

4、统计数据库资产信息（数据资产），处理具有碎片信息的表

   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 data_free >0 ;

5、统计数据库资产信息（数据资产），获取数据库中非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  engine !='innodb' into outfile '/tmp/alter.sql'; ;