データベース毎の容量
SELECT
table_schema AS "データベース名"
, FLOOR(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
;
+-----------------------+------------------+
| データベース名 | 合計容量(MB) |
+-----------------------+------------------+
| xxxxxxxx_xxxxxxxx | 1995 |
| information_schema | 0 |
+-----------------------+------------------+
テーブル毎の容量
use DATABASE_NAMEで確認したいデータベースに切り替えた後に実行。
SELECT
table_name AS "テーブル名"
, FLOOR((data_length + index_length) / 1024 / 1024) AS "合計容量(MB)"
, FLOOR((data_length) / 1024 / 1024) AS "データ容量(MB)"
, FLOOR((index_length) / 1024 / 1024) AS "インデックス容量(MB)"
, table_rows AS "行数"
, avg_row_length AS "平均行容量"
, engine AS "ストレージエンジン"
FROM information_schema.tables
WHERE table_schema = database()
ORDER BY
(data_length + index_length) DESC
;
+-------------------+------------------+---------------------+------------------------------+----------+-----------------+-----------------------------+
| テーブル名 | 合計容量(MB) | データ容量(MB) | インデックス容量(MB) | 行数 | 平均行容量 | ストレージエンジン |
+-------------------+------------------+---------------------+------------------------------+----------+-----------------+-----------------------------+
| aaa | 1781 | 1132 | 649 | 10340935 | 114 | InnoDB |
| bbb | 99 | 42 | 56 | 842684 | 52 | InnoDB |
| ccc | 53 | 50 | 3 | 97275 | 545 | InnoDB |
| ddd | 48 | 34 | 13 | 614400 | 58 | InnoDB |
| eee | 12 | 5 | 6 | 84417 | 68 | InnoDB |
| fff | 1 | 0 | 0 | 7083 | 53 | InnoDB |
| ggg | 0 | 0 | 0 | 2598 | 63 | InnoDB |
| hhh | 0 | 0 | 0 | 1543 | 63 | InnoDB |
| iii | 0 | 0 | 0 | 233 | 70 | InnoDB |
| jjj | 0 | 0 | 0 | 233 | 70 | InnoDB |
| kkk | 0 | 0 | 0 | 69 | 237 | InnoDB |
| lll | 0 | 0 | 0 | 265 | 61 | InnoDB |
| mmm | 0 | 0 | 0 | 1 | 16384 | InnoDB |
+-------------------+------------------+---------------------+------------------------------+----------+-----------------+-----------------------------+
