MySQL查看数据库、表占用磁盘大小

查询所有数据库占用磁盘空间大小

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_size desc;

单个库磁盘占用大小

select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
         concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
         concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
         concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
  from information_schema.tables
where TABLE_SCHEMA = 'databasename'; # databasename 具体库名

查看单个库每张表占用磁盘大小

SELECT table_name, data_length + index_length AS len, table_rows,
CONCAT(ROUND((data_length + index_length)/1024/1024,2),'MB') AS datas FROM information_schema.tables 
WHERE table_schema = 'database_name' ORDER BY len DESC; # 修改database_name

使用optimize命令

optimize table tb_report_inventory;

使用的时间比较长，需要耐心等待。

注意：optimize执行时会将表锁住，所以不要在高峰期使用。也不要经常使用，每月一次就足够了

参考：
https://www.cnblogs.com/leizia/p/16027325.html
https://www.bbsmax.com/A/ZOJP4rRPJv/