mysql中information_schema.tables字段说明、mysql统计表大小sql

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;