Memo

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 |
+------------+--------+----------+-------+--------+---------+
Memo

Advertisement
