数据量统计

为体现系统运营情况，了解数据库中数据增长量，公司需每月统计一次数据总量和数据增长量。以下是简单的语句记录

Oracle数据量统计

查看表空间及表空间大小

SELECT TABLESPACE_NAME,SUM(BYTES) / 1024 / 1024 AS MB　FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ORDER BY MB DESC;

查看表空间及对应的表空间文件

SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES;

统计占用空间

SELECT SUM(BYTES) / 1024 / 1024 AS MB FROM DBA_SEGMENTS;

统计数据总行数

SELECT SUM(SAMPLE_SIZE) FROM USER_TABLES;

SQL Server 数据量统计

查询数据库所有表的数据的行数

--方法1：
SELECT A.NAME, B.ROWS
  FROM SYSOBJECTS AS A
 INNER JOIN SYSINDEXES AS B
    ON A.ID = B.ID
 WHERE (A.TYPE = 'u')
   AND (B.INDID IN (0, 1))
 ORDER BY B.ROWS DESC


--方法2：
SELECT SCHEMA_NAME(T.SCHEMA_ID) AS [ SCHEMA ],
       T.NAME AS TABLENAME,
       I.ROWS AS [ ROWCOUNT ]
  FROM SYS.TABLES AS T, SYSINDEXES AS I
 WHERE T.OBJECT_ID = I.ID
   AND I.INDID <= 1
--如果只看总量，使用以下语句
SELECT SUM(ROWS)
  FROM SYS.TABLES AS T, SYSINDEXES AS I
 WHERE T.OBJECT_ID = I.ID
   AND I.INDID <= 1

统计数据库占用空间大小

EXEC sp_spaceused

MySql 数据量统计

简单快捷，直接查询数据库中所有行数

select sum(table_rows) as '记录数' from information_schema.tables 
-- where table_schema = 'esp'
;

查询所有数据库容量大小

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 = 'your_database_name'
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

查询所有数据库各表容量大小

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 = 'your_database_name'
where table_schema = 'esp'
order by table_schema asc, data_length desc, index_length desc;

查询单表每个索引大小

select
sum(stat_value) pages,
table_name,
index_name,
concat(round(sum(stat_value)/1000000 * @@innodb_page_size, 2), 'M') size
from mysql.innodb_index_stats
where
table_name = 'user_info_index'
and database_name = 'db_my_test'
and stat_description LIKE 'Number of pages in the index'
group by table_name, index_name;

PS: 以上语句均来自网络，暂未找到可以直接统计一个月的数据增量，因每个月统计一次，可以使用本月的数据量减去上月统计的数据量来获取数据增量。