[MySQL] データベースと各テーブルのサイズ(容量)を確認する方法
3 min read
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 |
+------------+--------+----------+------+-----------------+---------------------+------------------------------+