0%

数据量统计

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

Oracle数据量统计

查看表空间及表空间大小

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

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

1
SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES;

统计占用空间

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

统计数据总行数

1
SELECT SUM(SAMPLE_SIZE) FROM USER_TABLES;

SQL Server 数据量统计

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--方法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

统计数据库占用空间大小

1
EXEC sp_spaceused

MySql 数据量统计

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

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

查询所有数据库容量大小

1
2
3
4
5
6
7
8
9
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;

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

1
2
3
4
5
6
7
8
9
10
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;

查询单表每个索引大小

1
2
3
4
5
6
7
8
9
10
11
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: 以上语句均来自网络,暂未找到可以直接统计一个月的数据增量,因每个月统计一次,可以使用本月的数据量减去上月统计的数据量来获取数据增量。