[Mysql] Table size 조회

SELECT
    dbname,
      table_name,
    Concat(Lpad(Format(sdsize / Power(1024, pw), 3), 17, ' '), ' ', Substr(' KMGTP', pw + 1, 1), 'B') "Data Size",
    Concat(Lpad(Format(sxsize / Power(1024, pw), 3), 17, ' '), ' ', Substr(' KMGTP', pw + 1, 1), 'B') "Index Size",
    Concat(Lpad(Format(stsize / Power(1024, pw), 3), 17, ' '), ' ', Substr(' KMGTP', pw + 1, 1), 'B') "Total Size" 
FROM
    (SELECT
            Ifnull(db, 'All Databases') DBName,
            table_name,
            Sum(dsize) SDSize,
            Sum(xsize) SXSize,
            Sum(tsize) STSize 
        FROM (SELECT
                    table_schema DB,
                    table_name,
                    data_length DSize,
                    index_length XSize,
                    data_length + index_length TSize 
                FROM information_schema.tables 
                WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
                and table_schema = 'test' -- DB명
                
            ) AAA 
        GROUP BY db ,table_name WITH rollup
    ) AA,
    (SELECT 1 pw) BB -- 0: Byte, 1:KB, 2:MB, 3:GB
ORDER BY ( sdsize + sxsize ); 

참고사이트의 SQL 를 토대로 SQL를 만들어봄.

참고사이트

webmasters.stackexchange.com/questions/61684/how-can-i-tell-how-big-my-amazon-rds-database-mysql-is