MySQL数据库及表空间占用信息统计
这里用到一个表, information_schema.tables;对应主要字段含义如下:TABLE_SCHEMA : 数据库名TABLE_NAME:表名ENGINE:所使用的存储引擎TABLES_ROWS:记录数DATA_LENGTH:数据大小INDEX_LENGTH:索引大小1.查询数据库,表,行数统计记录select table_schema,table_...
·
这里用到一个表, information_schema.tables;对应主要字段含义如下:
TABLE_SCHEMA : 数据库名
TABLE_NAME:表名
ENGINE:所使用的存储引擎
TABLES_ROWS:记录数
DATA_LENGTH:数据大小
INDEX_LENGTH:索引大小
1.查询数据库,表,行数统计记录
select table_schema,table_name,table_rows from information_schema.tables order by table_rows desc;
2.查询所有数据库占用磁盘空间大小
select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),' MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc;
3.查询单个库中所有表磁盘占用大小
select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,
concat(truncate(index_length/1024/1024,2),' MB') as index_size
from information_schema.tables where TABLE_SCHEMA = '数据库名'
group by TABLE_NAME
order by data_length desc;
4.查看单个库的使用情况
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',
CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size',
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS 'Total'
FROM information_schema.TABLES
WHERE table_schema LIKE '数据库名';
更多推荐
已为社区贡献1条内容
所有评论(0)