MySQL 各テーブル毎に容量を確認する

2014年11月10日 at 1:08 PM

MySQLを使用する際、DBのサイズが肥大化しサーバのHDDが逼迫することがあります。そういったときは「どのテーブルのサイズが大きいのか」を次のSQLを使い確認します。

SELECT
  table_name
  , engine
  , table_rows AS tbl_rows
  , avg_row_length AS rlen
  , floor((data_length + index_length) / 1024) AS allkb
  , floor((data_length) / 1024) AS dkb
  , floor((index_length) / 1024) AS ikb 
FROM
  information_schema.tables 
ORDER BY
  (data_length + index_length) DESC;

上記のSQLを実行すると次のような結果が表示されます。(サンプルとしてRedmineのテーブルを使っています。)

2014-1110-a_mysql-query-result

なお、表示されている項目の内容は次の通りです。

・table_name(テーブルの名称)
・engine(DBエンジンの種類) ・・・ InnoDB、MyISAM、MEMORYなど
・tbl_rows(レコード件数)
・rlen(平均レコードサイズ[単位: Byte])
・allkb(テーブル全体のサイズ[単位: KB])
・dkb(テーブル内のデータが占めるサイズ[単位: KB])
・ikb(テーブル内のインデックスが占めるサイズ[単位: KB])

もし各々のテーブルのサイズ大きいときは、SQL内の「1024」の部分をもっと大きい値にすると良いでしょう。(次の例は単位をMBに変えたものです。)

SELECT
  table_name
  , engine
  , table_rows AS tbl_rows
  , avg_row_length AS rlen
  , floor((data_length + index_length) / (1024 * 1024)) AS allkb
  , floor((data_length) / (1024 * 1024)) AS dkb
  , floor((index_length) / (1024 * 1024)) AS ikb 
FROM
  information_schema.tables 
ORDER BY
  (data_length + index_length) DESC;