MySQL数据库查看相关库、表大小

迷南。 2022-01-26 01:41 357阅读 0赞

> 查看所有数据库各表容量大小；便于清理内存。清理内存可以语句删除，也可以把不要的表删除再新建，这样没有索引内存，要数据再去做一点，方便快捷

*  查看所有数据库各表容量大小
    
        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)'
          from information_schema.tables
          order by data_length desc, index_length desc;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70]

*  查看所有数据库容量大小
    
        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)'
          from information_schema.tables
          group by table_schema
          order by sum(data_length) desc, sum(index_length) desc;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70 1]

*  查看指定数据库各表容量大小
    
        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)'
          from information_schema.tables
          where table_schema='mysql'
          order by data_length desc, index_length desc;
    
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70 2]
 *  查看指定数据库容量大小
    
        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)'
          from information_schema.tables
          where table_schema='mysql';
    
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70]: /images/20220126/7272a8db891045098308eebf9d367100.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70 1]: /images/20220126/c024f49f4ac94908bf9815ae5647ae91.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70 2]: /images/20220126/59c387da8a1346dba99c33833a4a8fe8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70 3]: /images/20220126/da6b9ed1a0b94a4f9244d3902f2cb84c.png