003-mysql查询表的数据大小、索引大小

在需要备份数据库里面的数据时，我们需要知道数据库占用了多少磁盘大小，可以通过一些sql语句查询到整个数据库的容量，也可以单独查看表所占容量。

1、查看数据库表 数据大小，索引大小

select 
concat(round(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_SIZE, 
concat(round(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS INDEX_SIZE, 
concat(round(SUM(DATA_FREE / 1024 / 1024), 2), 'MB') AS DATA_FREE
from information_schema.tables 
where table_schema='库名';

2、查询数据库所有表数据，所有的数据大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from information_schema.tables; -- 查询所有的数据大小

3、查询某个表的结构大小，行数

SELECT
    TABLE_NAME 表名,
    CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024,2),'M') 表占用空间大小,
    TABLE_ROWS 数据行数
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'copyright_pre' and TABLE_NAME='test_table_idx'
ORDER BY TABLE_ROWS desc

 不过需要注意的是，对于InnoDB表，table_rows行计数仅是大概估计值。

如果想要准确查询条数

select concat(
    'select "',
    TABLE_name, 
    '", count(*) from ', 
    TABLE_SCHEMA, 
    '.',
    TABLE_name,
    ' union all' 
)  as a  from information_schema.tables 
where TABLE_SCHEMA='数据库名';

将得到的sql 语句进行拼接查询即可，注意最后一个的union all

 select * from (

select "表名" tname, 总数 trows from 表名 union all --将第一行的 字段起一个别名 tname trows

......
) a order by trows desc 增加排序