【MySQL】各データベースのサイズを確認する

MySQLには、PostgreSQLのpg_database_size関数のようにデータベースのサイズを直接返す関数はない。
information_schema.tablesのDATA_LENGTH・INDEX_LENGTHカラムを集計してサイズを求める。

参考: 【PostgreSQL】各データベースのサイズを確認する

information_schema.tablesでテーブルごとのサイズを確認する

information_schema.tablesは、テーブルごとにDATA_LENGTH（データ部分のサイズ）とINDEX_LENGTH（インデックス部分のサイズ）をバイト単位で保持する。

mysql> SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH
    -> FROM information_schema.tables
    -> WHERE TABLE_SCHEMA = 'testdb';
+------------+-------------+--------------+
| TABLE_NAME | DATA_LENGTH | INDEX_LENGTH |
+------------+-------------+--------------+
| orders     |      212992 |            0 |
| users      |       49152 |        16384 |
+------------+-------------+--------------+

ordersにはセカンダリインデックスがないためINDEX_LENGTHは0であり、usersには作成したインデックスの分だけINDEX_LENGTHに値が入っている。
テーブル1件のサイズはDATA_LENGTH + INDEX_LENGTHで求められる。

データベース単位で集計する

データベース全体のサイズを求めるには、TABLE_SCHEMAでグループ化してDATA_LENGTH + INDEX_LENGTHを合計する。

mysql> SELECT
    ->   TABLE_SCHEMA,
    ->   SUM(DATA_LENGTH + INDEX_LENGTH) AS total_bytes
    -> FROM information_schema.tables
    -> GROUP BY TABLE_SCHEMA
    -> ORDER BY total_bytes DESC;
+--------------------+-------------+
| TABLE_SCHEMA       | total_bytes |
+--------------------+-------------+
| mysql              |     8241152 |
| testdb             |      278528 |
| sys                |       16384 |
| information_schema |           0 |
| performance_schema |           0 |
+--------------------+-------------+

mysqlデータベースにはユーザー権限などの管理情報が格納されており、他のシステムデータベースと同様に集計対象に含まれる。
特定のデータベースだけを見たい場合はWHERE TABLE_SCHEMA = 'testdb'のように絞り込む。

バイト単位を読みやすい形式に変換する

バイト単位のままでは読みにくいため、ROUND関数と1024での除算を組み合わせてMB単位に変換する。

mysql> SELECT
    ->   TABLE_SCHEMA,
    ->   ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
    -> FROM information_schema.tables
    -> GROUP BY TABLE_SCHEMA
    -> ORDER BY size_mb DESC;
+--------------------+---------+
| TABLE_SCHEMA       | size_mb |
+--------------------+---------+
| mysql              |    7.86 |
| testdb             |    0.27 |
| sys                |    0.02 |
| information_schema |    0.00 |
| performance_schema |    0.00 |
+--------------------+---------+

GB単位で見たい場合は1024 / 1024 / 1024で割る。
PostgreSQLのpg_size_prettyのように単位を自動選択する関数はなく、桁数に応じて自分で単位を決める必要がある。

InnoDBのDATA_LENGTHとINDEX_LENGTHは概算値

InnoDBテーブルのDATA_LENGTH・INDEX_LENGTHは、統計情報として保持されているページ数から算出した概算値であり、正確なディスク使用量とは一致しない。
統計情報が古いままだと、実際のデータ量から大きくずれることがある。

3,000行を一括挿入した直後のbigtableテーブルで確認すると、DATA_LENGTHが実際のデータ量に対して明らかに小さい。

mysql> SELECT TABLE_ROWS, DATA_LENGTH FROM information_schema.tables
    -> WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'bigtable';
+------------+-------------+
| TABLE_ROWS | DATA_LENGTH |
+------------+-------------+
|       3000 |       16384 |
+------------+-------------+

ANALYZE TABLEで統計情報を更新すると、DATA_LENGTHは実際のデータ量に近い値まで増える。

mysql> ANALYZE TABLE bigtable;
mysql> SELECT TABLE_ROWS, DATA_LENGTH FROM information_schema.tables
    -> WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'bigtable';
+------------+-------------+
| TABLE_ROWS | DATA_LENGTH |
+------------+-------------+
|       3000 |      131072 |
+------------+-------------+

正確なサイズを求めたい場合は、集計対象のテーブルにANALYZE TABLEをあらかじめ実行しておく。
なおTABLE_ROWSも同様に概算値であり、MySQL公式ドキュメントによると実際の行数と40〜50%ずれる場合がある。
正確な行数が必要な場合はSELECT COUNT(*)を使う。