1、表结构为:
CREATE TEMPORARY TABLE `TABLES` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
`ENGINE` varchar(64) DEFAULT NULL,
`VERSION` bigint(21) unsigned DEFAULT NULL,
`ROW_FORMAT` varchar(10) DEFAULT NULL,
`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_FREE` bigint(21) unsigned DEFAULT NULL,
`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
`CHECK_TIME` datetime DEFAULT NULL,
`TABLE_COLLATION` varchar(32) DEFAULT NULL,
`CHECKSUM` bigint(21) unsigned DEFAULT NULL,
`CREATE_OPTIONS` varchar(255) DEFAULT NULL,
`TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8;
2、表字段说明:
字段 | 含义 |
---|---|
table_catalog | 数据表登记目录 |
table_schema | 数据表所属的数据库名 |
table_name | 表名称 |
table_type | 表类型[system view base table] |
engine | 使用的数据库引擎[MyISAM、CSV、InnoDB] |
version | 版本,默认值10 |
row_format | 行格式[Compact、Dynamic、Fixed] |
table_rows | 表里所存多少行数据 |
avg_row_length | 平均行长度 |
data_length | 数据长度 |
max_data_length | 最大数据长度 |
index_length | 索引长度 |
data_free | 空间碎片 |
auto_increment | 做自增主键的自动增量当前值 |
create_time | 表的创建时间 |
update_time | 表的更新时间 |
check_time | 表的检查时间 |
table_collation | 表的字符校验编码集 |
checksum | 校验和 |
create_options | 创建选项 |
table_comment | 表的注释、备注 |
3、统计每个表的总行数,数据大小,索引大小和总大小
select CONCAT_WS('.',table_schema,table_name) '库表', table_schema,table_name
,table_rows '总行数'
,concat(truncate(sum(data_length)/1024/1024/1024,2),'G') as data_length_G -- 数据大小
,concat(truncate(sum(index_length)/1024/1024/1024,2),'G') as index_length_G -- 索引大小
,concat( truncate(SUM(data_length)+SUM(index_length),2)/1024/1024/1024,'G' ) as all_length_G -- 总大小
,SUM(data_length)+SUM(index_length) as all_length -- 总大小
from information_schema.tables
group by table_name
-- HAVING all_length > 20000000000
order by all_length desc;
select CONCAT_WS('.',table_schema,table_name) '库表', table_schema,table_name
,table_rows '总行数'
,concat(truncate(sum(data_length)/1024/1024/1024,2),'G') as data_length_G -- 数据大小
,concat(truncate(sum(index_length)/1024/1024/1024,2),'G') as index_length_G -- 索引大小
,concat( truncate(SUM(data_length)+SUM(index_length),2)/1024/1024/1024,'G' ) as all_length_G -- 总大小
,SUM(data_length)+SUM(index_length) as all_length -- 总大小
from information_schema.tables
WHERE table_schema != 'information_schema'
group by table_name
order by table_schema,all_length desc;