데이터베이스 / 테이블 용량 알아보는 쿼리 in MySQL

-- 데이터베이스 용량
SELECT s.schema_name,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb")  data_used,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used,
COUNT(table_name) total_tables
FROM INFORMATION_SCHEMA.SCHEMATA s
LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema
WHERE s.schema_name = "sakila"
GROUP BY s.schema_name
ORDER BY pct_used DESC


-- 테이블 용량

SELECT 

concat(table_schema,'.',table_name),   

concat(round(table_rows/1000000,2),'M') rows,   

concat(round(data_length/(1024*1024*1024),2),'G') DATA,   

concat(round(index_length/(1024*1024*1024),2),'G') idx,   

concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,   

round(index_length/data_length,2) idxfrac    

FROM 

information_schema.TABLES  

where 

table_name = '테이블명'  ;