--clickhouse库表大小

select

database,

sum(rows) as rows,

formatReadableSize(sum(data_compressed_bytes)) as size

from

system.parts

group by

database

order by

sum(data_compressed_bytes) desc;

select

database,

table,

rows,

formatReadableSize(size) as table_size,

formatReadableSize(size/rows*10000) as 1w_size,

formatReadableSize(size/rows*100000) as 10w_size,

formatReadableSize(size/rows*1000000) as 100w_size

from

(

select

database,

table,

sum(rows) as rows,

sum(bytes) as size

from system.parts

where active

and database = 'ods'

group by

database,

table

)

order by size desc;

--mysql库表大小

SELECT

table_schema,

CONCAT(ROUND((SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) / 1024 / 1024,2),'M') as size

FROM

information_schema.tables

GROUP BY table_schema

order by SUM(DATA_LENGTH) + SUM(INDEX_LENGTH) desc;

SELECT

table_schema,

table_name,

table_rows,

CONCAT(ROUND(SUM(DATA_LENGTH / 1024 / 1024), 2),'M') AS table_size,

CONCAT(ROUND(SUM(DATA_LENGTH / table_rows / 1024 / 1024 / 1024 * 10000),2),'G') AS 1w_size,

CONCAT(ROUND(SUM(DATA_LENGTH / table_rows / 1024 / 1024 / 1024 * 100000),2),'G') AS 10w_size,

CONCAT(ROUND(SUM(DATA_LENGTH / table_rows / 1024 / 1024 / 1024 * 1000000),2),'G') AS 100w_size

FROM

information_schema.tables

WHERE

table_schema = 'hbo'

GROUP BY table_name , table_rows

ORDER BY SUM(DATA_LENGTH) DESC;