统计mysql每一个库的相关信息

 

sql代码:perschema.sql

作用:

    1、统计库的大小，数据大小，索引大小，表的数量。

    2、按库，引擎，校验字符集分类并统计各种类型含有的表的数量

    3、统计指定库每个表大小，数据大小，索引大小，引擎，数据行的格式，表的行数，平均每行的长   度。

     4、统计含有text，blog大字段的表。

     5、统计每个表含有的大于255长度的varchar大字段，字段的最大长度。

     6、统计库中含有bigint字段且为auto_increment的表名，字段名字。

     7、统计库中含有的存储过程总数，视图总数，触发器总数。

SELECT NOW(), VERSION();

# Per Schema Queries

SET @schema = IFNULL(@schema,DATABASE());

# One Line Schema Summary
SELECT table_schema,
         SUM(data_length+index_length)/1024/1024 AS total_mb,
         SUM(data_length)/1024/1024 AS data_mb,
         SUM(index_length)/1024/1024 AS index_mb,
         COUNT(*) AS tables,
         CURDATE() AS today
FROM information_schema.tables
WHERE table_schema=@schema
GROUP BY table_schema;

# Schema Engine/Collation Summary
SELECT table_schema,engine,table_collation,
         COUNT(*) AS tables
FROM information_schema.tables
WHERE table_schema=@schema
GROUP BY table_schema,engine,table_collation;


# Schema Table Usage
SELECT @schema as table_schema, CURDATE() AS today;
SELECT if(length(table_name)>20,concat(left(table_name,18),'..'),table_name) AS table_name,
         engine,row_format as format, table_rows, avg_row_length as avg_row,
         round((data_length+index_length)/1024/1024,2) as total_mb,
         round((data_length)/1024/1024,2) as data_mb,
         round((index_length)/1024/1024,2) as index_mb
FROM information_schema.tables
WHERE table_schema=@schema
ORDER BY 6 DESC;

# Schema Table BLOB/TEXT Usage
select table_schema,table_name,column_name,data_type
from information_schema.columns
where table_schema= @schema
and ( data_type LIKE '%TEXT' OR data_type like '%BLOB');

# Large varchars
select table_schema,table_name,column_name,character_maximum_length from information_schema.columns where data_type='varchar' and character_maximum_length > 255 and table_schema = @schema;

select table_schema,table_name,column_name,data_type,extra from information_schema.columns where data_type='bigint' and extra like '%auto_increment%' and table_schema = @schema;

select 'routines',count(*) from information_schema.routines where routine_schema= @schema
union
select 'views',count(*) from information_schema.views where table_schema= @schema
union
select 'triggers',count(*) from information_schema.triggers where trigger_schema= @schema;

set @schema = NULL;