MySQL/mariadb Table 및 Index Size 확인

Mysql기반 테이블과 인덱스 사이즈 확인방법에 대해 알아보겠습니다.

test 결과 row 건수는 정확하게 일치하지는 않는거 같습니다.

-- 테이블 및 전체 인덱스 사이즈 확인
mysql> select table_schema, table_name,
    ->        round(sum(data_length)/1024/1024,2) as 'table Size(Mb)',
    ->        round(sum(index_length)/1024/1024,2) as 'index Size(Mb)',
    ->        max(table_rows) as rows_number,
    ->        max(auto_increment) as auto_increment_value
    ->  from information_schema.tables
    ->  where table_schema = 'test'
    ->   group by table_schema, table_name
    ->  order by 3 desc ;
+--------------+---------------+----------------+----------------+-------------+----------------------+
| table_schema | table_name    | table Size(Mb) | index Size(Mb) | rows_number | auto_increment_value |
+--------------+---------------+----------------+----------------+-------------+----------------------+
| test         | tb_dual       |           0.02 |           0.00 |           0 |                 NULL |
| test         | dept_manager  |           0.02 |           0.00 |           0 |                 NULL |
| test         | tab_test      |           0.02 |           0.00 |          10 |                 NULL |
| test         | dept_emp      |           0.02 |           0.03 |           0 |                 NULL |
| test         | salaries      |           0.02 |           0.02 |           0 |                 NULL |
| test         | titles        |           0.02 |           0.02 |           0 |                 NULL |
| test         | departments   |           0.02 |           0.02 |           0 |                 NULL |
| test         | employees     |           0.02 |           0.03 |           0 |                 NULL |
| test         | test          |           0.02 |           0.00 |           9 |                   10 |
| test         | employee_name |           0.00 |           0.00 |           0 |                 NULL |
+--------------+---------------+----------------+----------------+-------------+----------------------+

-- Index별 사이즈를 확인
mysql> SELECT database_name, table_name, index_name,
    -> round(stat_value*@@innodb_page_size/1024/1024, 2) size_in_mb
    -> FROM mysql.innodb_index_stats
    -> WHERE database_name = 'test'
    -> AND stat_name = 'size'
    -> ORDER BY 1,2,3 ;
+---------------+--------------+-------------------+------------+
| database_name | table_name   | index_name        | size_in_mb |
+---------------+--------------+-------------------+------------+
| test          | departments  | PRIMARY           |       0.02 |
| test          | departments  | ux_deptname       |       0.02 |
| test          | dept_emp     | PRIMARY           |       0.02 |
| test          | dept_emp     | ix_empno_fromdate |       0.02 |
| test          | dept_emp     | ix_fromdate       |       0.02 |
| test          | dept_manager | PRIMARY           |       0.02 |
| test          | employees    | PRIMARY           |       0.02 |
| test          | employees    | ix_firstname      |       0.02 |
| test          | employees    | ix_hiredate       |       0.02 |
| test          | salaries     | PRIMARY           |       0.02 |
| test          | salaries     | ix_salary         |       0.02 |
| test          | tab_test     | GEN_CLUST_INDEX   |       0.02 |
| test          | tb_dual      | PRIMARY           |       0.02 |
| test          | test         | PRIMARY           |       0.02 |
| test          | titles       | PRIMARY           |       0.02 |
| test          | titles       | ix_todate         |       0.02 |
+---------------+--------------+-------------------+------------+

이상 MySQL 테이블과 인덱스 사이즈 확인방법에 대해 설명하였습니다.