mita2 database life

主にMySQLに関するメモです

MySQL 各インデックスごとの容量を確認する

MySQL

お題

  • 不要そうなインデックスを削除して、どれぐらい容量が減るのか確認したい
  • information_schema.tables の INDEX_LENGTH では合計容量しか見えない
mysql> SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH 
  FROM information_schema.tables WHERE TABLE_NAME = 'sbtest1' \G
*************************** 1. row ***************************
TABLE_NAME< TABLE_ROWS: 1
           DATA_LENGTH: 225132544
          INDEX_LENGTH: 85295104
1 row in set, 1 warning (0.00 sec)
  • 複数のインデックスの合計サイズではなく、個別のインデックスのサイズが知りたい

innodb_index_stats テーブルを見る

v5.6 で試しています。innodb_index_stats テーブル でインデックスのページ数が確認できます。 InnoDBのページサイズ x ページ数 でだいたいの容量がわかりそうです。

mysql > SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb 
FROM innodb_index_stats WHERE index_name = 'pad_1' and stat_name = 'size';
+------------+------------+-------------+
| stat_value | index_name | size_mb     |
+------------+------------+-------------+
|       4341 | pad_1      | 67.82812500 |
+------------+------------+-------------+
1 row in set (0.00 sec)

このpad_1 インデックスの論理容量は67MBぐらいでした。インデックスが断片化している場合は、実際の容量より大きな容量が表示される可能性があります。 今回は断片化してないデータで試したため、正確な値が出ているはずです。

インデックスを削除して減る容量を確認する

innodb_index_stats で見積もった容量が正しいかどうか、実際に削除してみます。

削除前：300MB です。

$  ls -alh ./data/sbtest/sbtest1.ibd
-rw-rw---- 1 samitani users 300M Mar 29 16:18 ./data/sbtest/sbtest1.ibd

DROPし、ALTER TABLE で空いた物理領域を開放します。

mysql > DROP INDEX pad_1 ON sbtest.sbtest1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > ALTER TABLE sbtest.sbtest1 ENGINE=InnoDB;
Query OK, 0 rows affected (7.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

削除後：232MBです。だいたい、見積もり通りに小さくなりました！

$  ls -alh ./data/sbtest/sbtest1.ibd
-rw-rw---- 1 samitani users 232M Mar 29 16:23 ./data/sbtest/sbtest1.ibd