[MySQL] データベースと各テーブルのサイズ(容量)を確認する方法

DB の容量を確認する事案が出たので調べてみた。

データベースのサイズを確認する

MB単位

SELECT
    table_schema,
    sum (data_length + index_length) / 1024 / 1024 as MB
FROM
    information_schema.tables
GROUP BY
    table_schema
ORDER BY
    sum (data_length + index_length) desc;

mysql> SELECT table_schema, sum(data_length+index_length) /1024 /1024 as MB FROM information_schema.tables GROUP BY table_schema ORDER BY sum(data_length+index_length) desc;
+--------------------+---------------+
| table_schema       | MB            |
+--------------------+---------------+
| weather            | 3599.71875000 |
| information_schema |    0.00878906 |
+--------------------+---------------+
2 rows in set (8.41 sec)

GB単位

SELECT
    table_schema,
    sum (data_length + index_length) / 1024 / 1024 / 1024 as GB
FROM
    information_schema.tables
GROUP BY
    table_schema
ORDER BY
    sum (data_length + index_length) desc;

mysql> SELECT table_schema, sum(data_length+index_length) /1024 /1024/1024 as GB FROM information_schema.tables GROUP BY table_schema ORDER BY sum(data_length+index_length) desc;
+--------------------+----------------+
| table_schema       | GB             |
+--------------------+----------------+
| weather            | 3.515350341797 |
| information_schema | 0.000008583069 |
+--------------------+----------------+
2 rows in set (3.40 sec)

特定のデータベースのサイズを確認する

SELECT
    table_name,
    engine,
    table_rows as tbl_rows,
    avg_row_length as rlen,
    floor ((data_length + index_length) / 1024 / 1024) as allMB_総容量,
    floor ((data_length) / 1024 / 1024) as dMB_データ容量,
    floor ((index_length) / 1024 / 1024) as iMB_インデックス容量
FROM
    information_schema.tables
WHERE
    table_schema = database()
ORDER BY
    (data_length + index_length) desc;

mysql> use weather;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT
    ->   table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
    ->   floor((data_length+index_length)/1024/1024) as allMB_総容量,
    ->   floor((data_length)/1024/1024) as dMB_データ容量,
    ->   floor((index_length)/1024/1024) as iMB_インデックス容量
    -> FROM information_schema.tables
    -> WHERE table_schema=database()
    -> ORDER BY (data_length+index_length) desc;

+------------+--------+----------+------+-----------------+---------------------+------------------------------+
| table_name | engine | tbl_rows | rlen | allMB_総容量    | dMB_データ容量      | iMB_インデックス容量         |
+------------+--------+----------+------+-----------------+---------------------+------------------------------+
| ****       | InnoDB |  4748447 |  382 |            3403 |                1734 |                         1669 |
| ******     | InnoDB |   536513 |  226 |             184 |                 115 |                           69 |
| ******     | InnoDB |    11721 |  759 |              11 |                   8 |                            2 |
| ******     | InnoDB |     2402 |  197 |               0 |                   0 |                            0 |
| ******     | InnoDB |      373 |  307 |               0 |                   0 |                            0 |
| ******     | InnoDB |      609 |  161 |               0 |                   0 |                            0 |
| ******     | InnoDB |        5 | 3276 |               0 |                   0 |                            0 |
| *******    | InnoDB |       23 |  712 |               0 |                   0 |                            0 |
| *****      | InnoDB |        8 | 2048 |               0 |                   0 |                            0 |
| ******     | InnoDB |        0 |    0 |               0 |                   0 |                            0 |
| ******     | InnoDB |       27 |  606 |               0 |                   0 |                            0 |
| *****      | InnoDB |        4 | 4096 |               0 |                   0 |                            0 |
| ******     | InnoDB |        3 | 5461 |               0 |                   0 |                            0 |
| ******     | InnoDB |        3 | 5461 |               0 |                   0 |                            0 |
+------------+--------+----------+------+-----------------+---------------------+------------------------------+

引用参考