统计mysql库大小 表大小 表结构

表大小：

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024/1024, 2) as '数据容量(GB)',
truncate(index_length/1024/1024/1024, 2) as '索引容量(GB)'
from information_schema.tables
where table_schema in ('')
order by data_length desc, index_length desc;

库大小

select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024/1024,2),'GB') as data_size,
concat(truncate(sum(index_length)/1024/1024/1024,2),'GB') as index_size 
from information_schema.tables group by TABLE_SCHEMA order by data_length desc;

SELECT A.TABLE_SCHEMA '数据库',A.TABLE_NAME '表名',A.COLUMN_NAME '表列名',A.ORDINAL_POSITION '表字段顺序号',A.DATA_TYPE '列的数据类型',A.COLUMN_TYPE '列的类型',A.COLUMN_COMMENT '列的备注' FROM INFORMATION_SCHEMA.COLUMNS A
 

大表统计，记录数超500万