当前位置：首页 > news >正文

KingbaseES数据库大小查询全攻略：从单表到整个数据库的5种实用SQL

news 2026/5/12 10:03:28

KingbaseES数据库空间管理实战：5种精准查询与优化策略

数据库空间管理是每位DBA和开发者的必修课。当KingbaseES数据库运行一段时间后，表膨胀、索引碎片、临时文件堆积等问题会逐渐显现，直接影响查询性能和系统稳定性。本文将分享5种不同维度的空间查询方法，帮助您从微观到宏观全面掌握数据库存储状况。

1. 单表空间分析：从基础查询到深度诊断

单表空间查询是最基础也是最常用的操作。在KingbaseES中，我们可以通过sys_relation_size系列函数获取表的详细空间信息：

-- 查询表数据部分大小 SELECT sys_size_pretty(sys_relation_size('schema_name.table_name')) AS data_size; -- 查询表索引部分大小 SELECT sys_size_pretty(sys_indexes_size('schema_name.table_name')) AS index_size; -- 查询表总大小（包含数据和索引） SELECT sys_size_pretty(sys_total_relation_size('schema_name.table_name')) AS total_size;

实际工作中，我们更常需要批量分析模式下的所有表：

SELECT table_name, sys_size_pretty(sys_table_size(table_name)) AS table_size, sys_size_pretty(sys_indexes_size(table_name)) AS index_size, sys_size_pretty(sys_total_relation_size(table_name)) AS total_size, round((sys_indexes_size(table_name)::numeric / NULLIF(sys_total_relation_size(table_name),0))*100,2) AS index_ratio FROM ( SELECT ('"'||table_schema||'"."'||table_name||'"') AS table_name FROM information_schema.tables WHERE table_schema = 'your_schema' ) AS all_tables ORDER BY total_size DESC;

这个查询结果会显示：

表名
数据部分占用空间
索引部分占用空间
总占用空间
索引占比（有助于发现过度索引的表）

提示：当发现某张表的索引占比超过50%时，建议检查是否有冗余或低效索引

2. 模式级空间分析：定位资源消耗热点

模式(Schema)作为表的逻辑分组，其空间分析能帮助我们定位资源消耗的热点区域。以下是模式空间分析的进阶方法：

SELECT schema_name, sys_size_pretty(SUM(table_size)::bigint) AS "disk_space", sys_size_pretty(SUM(indexes_size)::bigint) AS "index_space", SUM(table_size)::bigint AS "raw_data_size", SUM(indexes_size)::bigint AS "raw_index_size", COUNT(*) AS "object_count" FROM ( SELECT nspname AS schema_name, sys_total_relation_size(c.oid) AS table_size, sys_indexes_size(c.oid) AS indexes_size FROM sys_class c JOIN sys_namespace n ON c.relnamespace = n.oid WHERE nspname NOT IN ('pg_catalog','information_schema') AND c.relkind = 'r' ) t GROUP BY schema_name ORDER BY "raw_data_size" DESC;

这个查询提供了比简单模式大小更丰富的信息：

按模式分组的磁盘空间使用情况
数据和索引的分离统计
每个模式包含的对象数量
原始字节数便于后续计算处理

对于大型数据库，可以添加筛选条件只分析特定模式：

-- 只分析特定模式 WHERE nspname IN ('sales','inventory','hr') -- 或者排除测试模式 WHERE nspname NOT LIKE 'test%'

3. 数据库级空间监控：全面容量规划

数据库级别的空间监控是容量规划的基础。KingbaseES提供了多种查看数据库大小的方法：

基础查询：

-- 单个数据库大小 SELECT sys_size_pretty(sys_database_size('dbname')); -- 所有数据库大小排序 SELECT datname, sys_size_pretty(sys_database_size(datname)) AS size, sys_database_size(datname) AS raw_size FROM sys_database ORDER BY raw_size DESC;

高级分析：

SELECT d.datname AS database_name, sys_size_pretty(pg_database_size(d.datname)) AS total_size, sys_size_pretty(blks_hit * current_setting('block_size')::int) AS cache_size, sys_size_pretty(blks_read * current_setting('block_size')::int) AS disk_read_size, numbackends AS active_connections, (blks_hit::float / NULLIF((blks_hit + blks_read),0)) AS cache_hit_ratio FROM sys_database d JOIN sys_stat_database s ON d.oid = s.datid WHERE d.datname NOT IN ('template0','template1') ORDER BY pg_database_size(d.datname) DESC;

这个查询不仅显示数据库大小，还包含：

缓存命中率（反映查询效率）
磁盘读取量（I/O压力指标）
活动连接数（并发负载）
原始数据便于设置监控阈值

4. 表空间使用分析：存储架构优化

表空间(Tablespace)是KingbaseES中控制物理存储位置的重要功能。分析表空间使用情况有助于优化存储架构：

SELECT spcname AS tablespace_name, sys_size_pretty(sys_tablespace_size(spcname)) AS total_size, sys_size_pretty( SELECT SUM(sys_total_relation_size(c.oid)) FROM sys_class c JOIN sys_tablespace t ON c.reltablespace = t.oid WHERE t.spcname = st.spcname ) AS used_size, pg_tablespace_location(oid) AS location, (SELECT COUNT(*) FROM sys_class c WHERE c.reltablespace = st.oid) AS object_count FROM sys_tablespace st WHERE spcname NOT LIKE 'pg%' ORDER BY total_size DESC;

关键分析点：

使用率计算：可以添加计算列显示使用百分比
位置信息：确认表空间是否分布在不同的存储设备
对象分布：了解哪些表空间承载了主要负载

对于使用默认表空间的表，可以这样查询：

SELECT n.nspname AS schema_name, c.relname AS table_name, sys_size_pretty(sys_total_relation_size(c.oid)) AS size, CASE WHEN c.reltablespace = 0 THEN (SELECT spcname FROM sys_tablespace WHERE oid = (SELECT dattablespace FROM sys_database WHERE datname = current_database())) ELSE (SELECT spcname FROM sys_tablespace WHERE oid = c.reltablespace) END AS tablespace FROM sys_class c JOIN sys_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' ORDER BY size DESC LIMIT 50;

5. 高级空间监控：定期收集与趋势分析

建立历史数据收集机制，才能发现空间增长趋势和异常情况。以下是创建空间监控系统的关键步骤：

1. 创建监控表：

CREATE TABLE db_space_history ( collect_time TIMESTAMP PRIMARY KEY, db_size BIGINT, largest_table_size BIGINT, largest_table_name TEXT, index_ratio NUMERIC(5,2), growth_rate NUMERIC(6,2) );

2. 设置定期收集任务：

-- 每日收集脚本 INSERT INTO db_space_history SELECT now(), sys_database_size(current_database()), (SELECT sys_total_relation_size(('"'||table_schema||'"."'||table_name||'"')) FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema') ORDER BY sys_total_relation_size(('"'||table_schema||'"."'||table_name||'"')) DESC LIMIT 1), (SELECT ('"'||table_schema||'"."'||table_name||'"') FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema') ORDER BY sys_total_relation_size(('"'||table_schema||'"."'||table_name||'"')) DESC LIMIT 1), (SELECT SUM(sys_indexes_size(('"'||table_schema||'"."'||table_name||'"')))::numeric / NULLIF(SUM(sys_total_relation_size(('"'||table_schema||'"."'||table_name||'"'))),0) FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema')), (SELECT (sys_database_size(current_database()) - db_size)::numeric / NULLIF(db_size,0) * 100 FROM db_space_history ORDER BY collect_time DESC LIMIT 1);

3. 创建趋势分析视图：

CREATE VIEW db_space_trend AS SELECT collect_time, sys_size_pretty(db_size) AS db_size, sys_size_pretty(largest_table_size) AS largest_table_size, largest_table_name, index_ratio, growth_rate, db_size - LAG(db_size) OVER (ORDER BY collect_time) AS daily_growth_bytes FROM db_space_history ORDER BY collect_time DESC;

4. 设置自动清理策略：

-- 保留90天数据 CREATE TRIGGER clean_space_history AFTER INSERT ON db_space_history EXECUTE FUNCTION DELETE FROM db_space_history WHERE collect_time < now() - interval '90 days';

这套监控系统可以帮助您：

识别异常增长的表
预测未来存储需求
规划归档和清理策略
评估索引优化效果

空间优化实战技巧

掌握了空间查询方法后，下面分享几个实际优化案例中的经验：

1. 大表分区策略：

-- 创建分区表 CREATE TABLE sales_data ( id SERIAL, sale_date DATE NOT NULL, product_id INTEGER, amount NUMERIC(10,2) ) PARTITION BY RANGE (sale_date); -- 添加月度分区 CREATE TABLE sales_data_202301 PARTITION OF sales_data FOR VALUES FROM ('2023-01-01') TO ('2023-02-01'); -- 查询分区大小 SELECT partition_name, sys_size_pretty(sys_total_relation_size(partition_name)) AS size FROM ( SELECT inhrelid::regclass::text AS partition_name FROM sys_inherits WHERE inhparent = 'sales_data'::regclass ) parts;

2. 索引优化方法：

-- 查找重复索引 SELECT indrelid::regclass AS table_name, array_agg(indexrelid::regclass) AS duplicate_indexes FROM ( SELECT indrelid, indexrelid, indkey, indclass, indpred, array_to_string(indkey, ' ') AS key_columns, row_number() OVER ( PARTITION BY indrelid, indkey, indclass, COALESCE(indpred,'') ORDER BY indexrelid::text ) AS dup_num FROM sys_index ) dups WHERE dup_num > 1 GROUP BY indrelid; -- 查找低效索引（从未被扫描） SELECT schemaname || '.' || relname AS table_name, indexrelname AS index_name, sys_size_pretty(sys_relation_size(schemaname || '.' || indexrelname)) AS index_size, idx_scan AS scans_since_start FROM sys_stat_all_indexes WHERE idx_scan = 0 ORDER BY sys_relation_size(schemaname || '.' || indexrelname) DESC;

3. 自动维护脚本示例：

#!/bin/bash # 数据库空间维护脚本 DB_NAME="production_db" LOG_FILE="/var/log/db_maintenance.log" # 1. 分析数据库 psql -d $DB_NAME -c "ANALYZE;" >> $LOG_FILE # 2. 重建膨胀严重的表 psql -d $DB_NAME <<EOF >> $LOG_FILE VACUUM FULL VERBOSE ANALYZE top_10_tables(); REINDEX DATABASE $DB_NAME; EOF # 3. 归档旧数据 psql -d $DB_NAME -c "SELECT archive_old_data();" >> $LOG_FILE # 4. 生成报告 psql -d $DB_NAME -c "SELECT * FROM generate_space_report();" >> $LOG_FILE

查看全文

http://www.jsqmd.com/news/484582/