Mysql 统计数据量

凡是涉及到数据库，数据表名字段都需要修改。

• data_length 、index_length 字段的存储单位是字节，所以我们要除以 2 个 1024 把字节转换为MB。

• 务必将代码中 kalacloud_test_data改为要查询的数据库名。

统计【单个表】数据条目

use '<Database>';

select
  count(*)
from
  < TableName >;

# 统计具有GET字段值的记录条数

select
  count(*)
from
  < TableName >
where
  operation = "GET";

查看 MySQL【所有库】的容量大小

SELECT
  table_schema as 'Database',
  sum(table_rows) as 'Number',
  sum(
    truncate (data_length / 1024 / 1024, 2)
  ) as 'Data Capacity(MB)',
  sum(
    truncate (index_length / 1024 / 1024, 2)
  ) as 'Index Capacity(MB)',
  sum(
    truncate (DATA_FREE / 1024 / 1024, 2)
  ) as 'Debris Occupancy(MB)'
from
  information_schema.tables
group by
  table_schema
order by
  sum(data_length) desc,
  sum(index_length) desc;

查看 MySQL【指定库】的容量大小

SELECT
  table_schema as 'Database',
  sum(table_rows) as 'Number',
  sum(
    truncate (data_length / 1024 / 1024, 2)
  ) as 'Data Capacity(MB)',
  sum(
    truncate (index_length / 1024 / 1024, 2)
  ) as 'Index Capacity(MB)',
  sum(
    truncate (DATA_FREE / 1024 / 1024, 2)
  ) as 'Debris Occupancy(MB)'
from
  information_schema.tables
where
  table_schema = '<Database Name>'
order by
  data_length desc,
  index_length desc;

查看 MySQL【指定库】中【所有表】的容量大小

SELECT
  table_schema as 'Database',
  table_name as 'table name',
  table_rows as 'Number',
truncate (data_length / 1024 / 1024, 2) as 'DataCapacity(MB)',
truncate (index_length / 1024 / 1024, 2) as 'IndexCapacity(MB)',
truncate (DATA_FREE / 1024 / 1024, 2) as 'DebrisCapacity(MB)'
from
  information_schema.tables
where
  table_schema = '<DatabaseName>'
order by
  data_length desc,
  index_length desc;

实际用到的

SELECT table_name AS "表名",
    ROUND((data_length + index_length)/1024/1024/1024,2) AS "表大小GB"
FROM 
     `information_schema`.`TABLES` 
WHERE 
     `TABLE_SCHEMA` = '<table_name>'

查看 MySQL【指定库】中【指定表】的容量大小

SELECT
  table_name AS "Table Name",
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Table Size (MB)"
FROM
  information_schema.TABLES
WHERE
  table_schema = 'invoice_test'
  AND table_name = 'info';

查看 MySQL 数据库中，容量Top10 的表

USE information_schema;

SELECT
  TABLE_SCHEMA as 'Database',
  table_name as 'TableName',
  table_rows as 'Number',
  ENGINE as 'StorageEngine',
truncate (data_length / 1024 / 1024, 2) as 'DataCapacity(MB)',
truncate (index_length / 1024 / 1024, 2) as 'IndexCapacity(MB)',
truncate (DATA_FREE / 1024 / 1024, 2) as 'DebrisCapacity(MB)'
from
  tables
order by
  table_rows desc
limit
  10;

查看 MySQL【指定库】中，容量Top10 的表

USE information_schema;

SELECT
  TABLE_SCHEMA as 'Database',
  table_name as 'TableName',
  table_rows as 'Number',
  ENGINE as 'StorageEngine',
truncate (data_length / 1024 / 1024, 2) as 'DataCapacity(MB)',
truncate (index_length / 1024 / 1024, 2) as 'IndexCapacity(MB)',
truncate (DATA_FREE / 1024 / 1024, 2) as 'DebrisCapacity(MB)'
from
  tables
where
  table_schema = '<DatabaseName>'
order by
  table_rows desc
limit
  10;