MySQL统计各种数据库对象大小

MySQL统计各种数据库对象大小；包含：数据库、表、索引等

脚本使用示例

统计实例中各数据库大小
SELECT TABLE_SCHEMA,
round(SUM(data_length+index_length)/1024/1024,2) AS TOTAL_MB,
round(SUM(data_length)/1024/1024,2) AS DATA_MB,
round(SUM(index_length)/1024/1024,2) AS INDEX_MB,
COUNT(*) AS TABLES
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN (‘sys’,
‘mysql’,
‘INFORMATION_SCHEMA’,
‘performance_schema’)
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;


统计某库下各表大小
SELECT TABLE_SCHEMA,
TABLE_NAME TABLE_NAME,
CONCAT(ROUND(data_length / (1024 * 1024), 2),‘M’) data_length,
CONCAT(ROUND(index_length / (1024 * 1024), 2),‘M’) index_length,
CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),2),‘M’) total_size,
engine
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN (‘INFORMATION_SCHEMA’ ,
‘performance_schema’,
‘sys’,
‘mysql’)
AND TABLE_SCHEMA=‘db’
ORDER BY (data_length + index_length) DESC LIMIT 10;


查看某库下表的基本信息
SELECT TABLE_SCHEMA,
TABLE_NAME,
table_collation,
engine,
table_rows
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN (‘INFORMATION_SCHEMA’ ,
‘sys’,
‘mysql’,
‘performance_schema’)
AND TABLE_TYPE=‘BASE TABLE’
AND TABLE_SCHEMA=‘db’
ORDER BY table_rows DESC ;