MYSQL 表空间分析

1

drop table table_name 

1

truncate table table_name

1

delete from table_name 

1

delete from table_name where xx 

1
2
3
4
5
6
7
8
9

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)',
sum(truncate(data_free/1024/1024, 2)) as '碎片空间容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)',
sum(truncate(data_free/1024/1024, 2)) as '碎片空间容量(MB)'
from information_schema.tables
where table_schema='mysql' -- 数据库名称
order by data_length desc, index_length desc;

1
2
3
4
5
6
7
8

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,
concat(truncate(sum(data_free)/1024/1024,2),' MB') as data_free_size
from information_schema.tables
group by TABLE_SCHEMA
ORDER BY data_size desc;

1
2
3
4
5
6
7
8
9

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,
concat(truncate(sum(data_free)/1024/1024,2),' MB') as data_free_size
from information_schema.tables 
where TABLE_SCHEMA = '查询的表名'
group by TABLE_NAME
order by data_length desc;