clickhouse查看数据库及表空间大小语句
1、查看数据库容量、行数、压缩率
select sum(rows) AS `总行数`,formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率` from system.parts;
注意在clickhouse-client客户端执行不能有换行
2、查看数据表容量、行数、压缩率
SELECT table AS `表名`,sum(rows) AS `总行数`,formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率` FROM system.parts WHERE table IN ('t_order') GROUP BY table
3、查看数据表分区信息
SELECT partition AS `分区`,sum(rows) AS `总行数`,formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率` FROM system.parts WHERE (database IN ('dd_order')) AND (table IN ('t_order')) AND (partition LIKE '2019-12-%') GROUP BY partition ORDER BY partition ASC
4、查看数据表字段的信息
SELECT column AS `字段名`,any(type) AS `类型`,formatReadableSize(sum(column_data_uncompressed_bytes)) AS `原始大小`,formatReadableSize(sum(column_data_compressed_bytes)) AS `压缩大小`,sum(rows) AS `行数` FROM system.parts_columns WHERE (database = 'dd_order') AND (table = 't_order') GROUP BY column ORDER BY column ASC
