Mysql数据统计

1、数据表数量

-- 某个库中，有多少个表

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '库名';SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '库名';

2、字段总量

-- 某个库，所有表的字段数累加

select
  sum(FIELD_COUNT)
from
  (
    SELECT
      TABLE_NAME,
      COUNT(COLUMN_NAME) AS FIELD_COUNT
    FROM
      INFORMATION_SCHEMA.COLUMNS
    WHERE
      TABLE_SCHEMA = '库名'
    GROUP BY
      TABLE_NAME
  ) t;
  

3、存储条数

-- 所有表的记录条数

select
  sum(TABLE_ROWS)
from
  (
    SELECT
      TABLE_NAME,
      TABLE_ROWS
    FROM
      INFORMATION_SCHEMA.TABLES
    WHERE
      TABLE_SCHEMA = '库名'
  ) t;

4、存储量

-- 所有记录的磁盘占比，数据大小；参考值，非精确值

select
  sum(mb)
from
  (
    SELECT
      TABLE_SCHEMA AS "Database",
      TABLE_NAME AS "Table",
      round(((data_length + index_length) / 1024 / 1024), 2) as mb
    FROM
      INFORMATION_SCHEMA.TABLES
    WHERE
      TABLE_SCHEMA = '库名'
  ) t;

5、综合统计

-- 数据量统计
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;

-- 总记录数
select SUM(TABLE_ROWS) from information_schema.tables where TABLE_TYPE = 'BASE TABLE';

-- 存储数据容量
select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'GB') from information_schema.tables where TABLE_TYPE = 'BASE TABLE'; 

参考文章：https://blog.csdn.net/myloverisxin/article/details/132669854