mysql数据库空间统计sql代码示例

目录

• 说明
• 一、数据库存储代码
• 二、查询某个数据库的所有表的 代码
• 三、列出所有已经产生碎片的表
• 总结

select coalesce(table_schema, '合计') as table_schema ,
concat(round(sum(data_length/1024/1024/1024),2),'GB') as data_length_GB, 
concat(round(sum(index_length/1024/1024/1024),2),'GB') as index_length_GB  ,
concat(round(sum(index_length/1024/1024/1024),2)+round(sum(data_length/1024/1024/1024),2),'GB')  as tal_GB 
from information_schema.tables t where table_Type='BASE TABLE'
and table_schema not in ('document','mysql','performance_schema','sys')
group by table_schema

select coalesce(table_schema, '合计') as table_schema,
concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, 
concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB  ,
concat(round(sum(index_length/1024/1024),2)+round(sum(data_length/1024/1024),2),'MB')  as tal_MB
from information_schema.tables t where table_Type='BASE TABLE'
and table_schema not in ('document','mysql','performance_schema','sys')
group by table_schema WITH ROLLUP order by round(sum(data_length/1024/1024),2) desc 

SELECT
    TABLE_NAME, ENGINE, VERSION, ROW_FORMAT, 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
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW TABLE STATUS
  FROM db_name
  [LIKE 'wild']

SELECT
  TABLE_NAME, TABLE_TYPE
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW FULL TABLES
  FROM db_name
  [LIKE 'wild']

-- 列出所有已经产生碎片的表 ('information_schema', 'mysql'这两个库是mysql自带的库)
select 
table_schema db, 
table_name,
 data_free, 
 engine,
 table_rows,
 data_length+index_length length 
from
information_schema.tables   
where 
table_schema not in ('information_schema', 'mysql') and data_free > 0
ORDER BY data_free desc 

alter table gd_channel_app_retention engine=innodb;