【MySQL】mysql | 数据量统计

一、概览

        1、统计数据库信息

        2、主要指标涉及： 数据表数量/字段总量/存储条数/存储量

二、命令

1、数据表数量

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;
所有记录的磁盘占比，数据大小；参考值，非精确值
~~