メモ > サーバ > 各論: コマンド > MySQLのデータ容量を確認
■MySQLのデータ容量を確認
MySQLでDBとテーブルのサイズを確認するSQL
http://qiita.com/iKenji/items/b868877492fee60d85ce
■DBのサイズ
SELECT
table_schema AS DB, SUM(data_length) / 1024 / 1024 AS MB
FROM
information_schema.tables
GROUP BY
table_schema
ORDER BY
SUM(data_length + index_length) DESC;
+------------------------+-------------+
| DB | MB |
+------------------------+-------------+
| test | 40.50000000 |
| company | 29.70312500 |
| abc | 13.28125000 |
+------------------------+-------------+
■テーブルのサイズ
SELECT
table_name, engine, table_rows AS tbl_rows,
FLOOR((data_length + index_length) / 1024) AS allKB, #総容量
FLOOR((data_length) / 1024) AS dataKB, #データ容量
FLOOR((index_length) / 1024) AS indexKB #インデックス容量
FROM
information_schema.tables
WHERE
table_schema = database()
ORDER BY
(data_length + index_length) DESC;
+------------+--------+----------+-------+--------+---------+
| table_name | engine | tbl_rows | allKB | dataKB | indexKB |
+------------+--------+----------+-------+--------+---------+
| rooms | InnoDB | 1829 | 1552 | 1552 | 0 |
| buildings | InnoDB | 554 | 160 | 160 | 0 |
| users | InnoDB | 2 | 96 | 16 | 80 |
+------------+--------+----------+-------+--------+---------+