CLOVER🍀

That was when it all began.

MySQL 8.0で、InnoDBのテーブルサイズを見たい

これは、なにをしたくて書いたもの?

MySQLで、InnoDBのテーブルサイズを見たい、と思いまして。

その方法を調べてみようかなと。いくつかあるようです。

環境

今回の環境は、こちら。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.24    |
+-----------+
1 row in set (0.00 sec)

MySQL 8.0.24です。

また、データはEmployees Sample Databaseを使って登録しているものとします。

MySQL :: Employees Sample Database

mysql> use employees;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

では、見ていってみましょう。

ちなみに、実際のサイズが確認できるのはinformation_schema.innodb_tablespacesテーブルのみで、それ以外のものは
統計情報が元の概算値になります。

SHOW TABLE STATUS/INFORMATION_SCHEMA TABLES テーブル

まずは、show table status。特定のデータベース内のテーブルに関する情報を表示します。

SHOW TABLE STATUS ステートメント

テーブル、viewに関する情報が出力されます。

mysql> show table status\G
*************************** 1. row ***************************
           Name: current_dept_emp
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2021-04-23 15:01:45
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
*************************** 2. row ***************************
           Name: departments
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 9
 Avg_row_length: 1820
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-04-23 15:01:44
    Update_time: 2021-04-23 15:01:45
     Check_time: NULL
      Collation: utf8mb4_ja_0900_as_cs_ks
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 3. row ***************************
           Name: dept_emp
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 331143
 Avg_row_length: 36
    Data_length: 12075008
Max_data_length: 0
   Index_length: 5783552
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2021-04-23 15:01:44
    Update_time: 2021-04-23 15:02:01
     Check_time: NULL
      Collation: utf8mb4_ja_0900_as_cs_ks
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 4. row ***************************
           Name: dept_emp_latest_date
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2021-04-23 15:01:45
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
*************************** 5. row ***************************
           Name: dept_manager
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 24
 Avg_row_length: 682
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-04-23 15:01:44
    Update_time: 2021-04-23 15:02:01
     Check_time: NULL
      Collation: utf8mb4_ja_0900_as_cs_ks
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 6. row ***************************
           Name: employees
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 299157
 Avg_row_length: 50
    Data_length: 15220736
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2021-04-23 15:01:44
    Update_time: 2021-04-23 15:01:52
     Check_time: NULL
      Collation: utf8mb4_ja_0900_as_cs_ks
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 7. row ***************************
           Name: salaries
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2839899
 Avg_row_length: 27
    Data_length: 79282176
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2021-04-23 15:01:45
    Update_time: 2021-04-23 15:03:05
     Check_time: NULL
      Collation: utf8mb4_ja_0900_as_cs_ks
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 8. row ***************************
           Name: titles
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 442010
 Avg_row_length: 46
    Data_length: 20512768
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2021-04-23 15:01:44
    Update_time: 2021-04-23 15:02:15
     Check_time: NULL
      Collation: utf8mb4_ja_0900_as_cs_ks
       Checksum: NULL
 Create_options: 
        Comment: 
8 rows in set (0.20 sec)

ポイントは、このあたりでしょうか。

  • Rows … 行数(概算値)
  • Data_length … クラスタ化されたインデックス(=主キー)のおよそのサイズ(バイト数)。クラスタ化されたインデックスは行のデータそのものを持つので、主キーを含むサイズになる
  • Data_free … 割り当てられているが、使用されていないバイト数。

なお、これらの値は概算値です。

InnoDB テーブルの場合、SHOW TABLE STATUS では、テーブルで予約されている物理サイズを除き、正確な統計は提供されません。 行カウントは、単に SQL 最適化で使用される概算見積もりです。

SHOW TABLE STATUS ステートメント

続いては、information_schema.tablesテーブルです。

INFORMATION_SCHEMA TABLES テーブル

ちょっと表示内容が多かったので、出力するデータベース(スキーマ)を絞りました。

mysql> select * from information_schema.tables where table_schema = 'employees';
+---------------+--------------+----------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME           | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME         | CHECK_TIME | TABLE_COLLATION          | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+----------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------------+----------+----------------+---------------+
| def           | employees    | current_dept_emp     | VIEW       | NULL   |    NULL | NULL       |       NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2021-04-23 15:01:45 | NULL                | NULL       | NULL                     |     NULL | NULL           | VIEW          |
| def           | employees    | departments          | BASE TABLE | InnoDB |      10 | Dynamic    |          9 |           1820 |       16384 |               0 |        16384 |         0 |           NULL | 2021-04-23 15:01:44 | 2021-04-23 15:01:45 | NULL       | utf8mb4_ja_0900_as_cs_ks |     NULL |                |               |
| def           | employees    | dept_emp             | BASE TABLE | InnoDB |      10 | Dynamic    |     331143 |             36 |    12075008 |               0 |      5783552 |   4194304 |           NULL | 2021-04-23 15:01:44 | 2021-04-23 15:02:01 | NULL       | utf8mb4_ja_0900_as_cs_ks |     NULL |                |               |
| def           | employees    | dept_emp_latest_date | VIEW       | NULL   |    NULL | NULL       |       NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2021-04-23 15:01:45 | NULL                | NULL       | NULL                     |     NULL | NULL           | VIEW          |
| def           | employees    | dept_manager         | BASE TABLE | InnoDB |      10 | Dynamic    |         24 |            682 |       16384 |               0 |        16384 |         0 |           NULL | 2021-04-23 15:01:44 | 2021-04-23 15:02:01 | NULL       | utf8mb4_ja_0900_as_cs_ks |     NULL |                |               |
| def           | employees    | employees            | BASE TABLE | InnoDB |      10 | Dynamic    |     299157 |             50 |    15220736 |               0 |            0 |   4194304 |           NULL | 2021-04-23 15:01:44 | 2021-04-23 15:01:52 | NULL       | utf8mb4_ja_0900_as_cs_ks |     NULL |                |               |
| def           | employees    | salaries             | BASE TABLE | InnoDB |      10 | Dynamic    |    2839899 |             27 |    79282176 |               0 |            0 |   4194304 |           NULL | 2021-04-23 15:01:45 | 2021-04-23 15:03:05 | NULL       | utf8mb4_ja_0900_as_cs_ks |     NULL |                |               |
| def           | employees    | titles               | BASE TABLE | InnoDB |      10 | Dynamic    |     442010 |             46 |    20512768 |               0 |            0 |   4194304 |           NULL | 2021-04-23 15:01:44 | 2021-04-23 15:02:15 | NULL       | utf8mb4_ja_0900_as_cs_ks |     NULL |                |               |
+---------------+--------------+----------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------------+----------+----------------+---------------+
8 rows in set (0.00 sec)

show table statusの説明にもありますが、show table statusの元になっている情報は、このinfomation_schema.tablesのようです。
なので、こちらも統計情報からの概算値ですね。

テーブル情報は、INFORMATION_SCHEMA TABLES テーブルからも入手できます。

SHOW TABLE STATUS ステートメント

計算して表示する分には、SQLでアクセスできる方がよいかもしれません。

mysql> select table_schema, table_name, table_rows, floor(data_length / 1024 / 1024) as data_length_mb from information_schema.tables where table_schema = 'employees';
+--------------+----------------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME           | TABLE_ROWS | data_length_mb |
+--------------+----------------------+------------+----------------+
| employees    | current_dept_emp     |       NULL |           NULL |
| employees    | departments          |          9 |              0 |
| employees    | dept_emp             |     331143 |             11 |
| employees    | dept_emp_latest_date |       NULL |           NULL |
| employees    | dept_manager         |         24 |              0 |
| employees    | employees            |     299157 |             14 |
| employees    | salaries             |    2839899 |             75 |
| employees    | titles               |     442010 |             19 |
+--------------+----------------------+------------+----------------+
8 rows in set (0.00 sec)

INFORMATION_SCHEMA INNODB_TABLESPACES テーブル

information_schema.innodb_tablespacesテーブル。

INFORMATION_SCHEMA INNODB_TABLESPACES テーブル

このテーブルへのアクセスには、PROCESS権限が必要になります。

mysql> select * from information_schema.innodb_tablespaces;
+------------+------------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE      | NAME                   | FLAG  | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE  |
+------------+------------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967294 | mysql                  | 18432 | Any                  |     16384 |             0 | General    |          4096 |  25165824 |       25165824 |               0 | 8.0.24         |             1 | N          | normal |
| 4294967293 | innodb_temporary       |  4096 | Compact or Redundant |     16384 |             0 | System     |          4096 |  12582912 |       12582912 |               0 | 8.0.24         |             1 | N          | normal |
| 4294967279 | innodb_undo_001        |     0 | Undo                 |     16384 |             0 | Undo       |          4096 |  16777216 |       16777216 |               0 | 8.0.24         |             1 | N          | active |
| 4294967278 | innodb_undo_002        |     0 | Undo                 |     16384 |             0 | Undo       |          4096 |  16777216 |       16777216 |               0 | 8.0.24         |             1 | N          | active |
|          1 | sys/sys_config         | 16417 | Dynamic              |     16384 |             0 | Single     |          4096 |    114688 |         114688 |               0 | 8.0.24         |             1 | N          | normal |
|          2 | employees/employees    | 16417 | Dynamic              |     16384 |             0 | Single     |          4096 |  23068672 |       23072768 |               0 | 8.0.24         |             1 | N          | normal |
|          3 | employees/departments  | 16417 | Dynamic              |     16384 |             0 | Single     |          4096 |    131072 |         114688 |               0 | 8.0.24         |             1 | N          | normal |
|          4 | employees/dept_manager | 16417 | Dynamic              |     16384 |             0 | Single     |          4096 |    131072 |         114688 |               0 | 8.0.24         |             1 | N          | normal |
|          5 | employees/dept_emp     | 16417 | Dynamic              |     16384 |             0 | Single     |          4096 |  26214400 |       26218496 |               0 | 8.0.24         |             1 | N          | normal |
|          6 | employees/titles       | 16417 | Dynamic              |     16384 |             0 | Single     |          4096 |  28311552 |       28315648 |               0 | 8.0.24         |             1 | N          | normal |
|          7 | employees/salaries     | 16417 | Dynamic              |     16384 |             0 | Single     |          4096 | 109051904 |      109056000 |               0 | 8.0.24         |             1 | N          | normal |
+------------+------------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
11 rows in set (0.04 sec)

こちらのポイントは、このあたりでしょうか。

  • FILE_SIZE … 圧縮解除されたファイルの最大サイズを表す、ファイルの見かけ上のサイズ(バイト)
  • ALLOCATED_SIZE … 実際にディスクに割り当てられたサイズ(バイト)

今回の中で、唯一実際のサイズが取得できるテーブルです。

計算例。

mysql> select name, floor(file_size * 1.0 / 1024 / 1024) as file_size_mb, floor(allocated_size * 1.0 / 1024 / 1024) as allocated_size_mb from information_schema.innodb_tablespaces;
+------------------------+--------------+-------------------+
| name                   | file_size_mb | allocated_size_mb |
+------------------------+--------------+-------------------+
| mysql                  |           24 |                24 |
| innodb_temporary       |           12 |                12 |
| innodb_undo_001        |           16 |                16 |
| innodb_undo_002        |           16 |                16 |
| sys/sys_config         |            0 |                 0 |
| employees/employees    |           22 |                22 |
| employees/departments  |            0 |                 0 |
| employees/dept_manager |            0 |                 0 |
| employees/dept_emp     |           25 |                25 |
| employees/titles       |           27 |                27 |
| employees/salaries     |          104 |               104 |
+------------------------+--------------+-------------------+
11 rows in set (0.00 sec)

実際のファイル。

$ sudo ls -lh /var/lib/mysql/employees
total 179M
-rw-r----- 1 mysql mysql 128K Apr 23 15:01 departments.ibd
-rw-r----- 1 mysql mysql  25M Apr 23 15:02 dept_emp.ibd
-rw-r----- 1 mysql mysql 128K Apr 23 15:02 dept_manager.ibd
-rw-r----- 1 mysql mysql  22M Apr 23 15:01 employees.ibd
-rw-r----- 1 mysql mysql 104M Apr 23 15:03 salaries.ibd
-rw-r----- 1 mysql mysql  27M Apr 23 15:02 titles.ibd

確かに、実際のサイズが見れているようです。

INFORMATION_SCHEMA INNODB_TABLESTATS ビュー

information_schema.innodb_tablestatsビュー。

INFORMATION_SCHEMA INNODB_TABLESTATS ビュー

このビューにアクセスするには、PROCESS権限が必要になります。

mysql> select * from information_schema.innodb_tablestats;
+----------+------------------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME                                                       | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+------------------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|     1025 | mysql/db                                                   | Initialized       |        2 |                1 |                1 |                0 |       0 |         1 |
|     1026 | mysql/user                                                 | Initialized       |        5 |                1 |                0 |                1 |       0 |         1 |
|     1027 | mysql/default_roles                                        | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1028 | mysql/role_edges                                           | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1029 | mysql/global_grants                                        | Initialized       |       81 |                3 |                0 |                8 |       0 |         1 |
|     1030 | mysql/password_history                                     | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1031 | mysql/func                                                 | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1032 | mysql/plugin                                               | Initialized       |        1 |                1 |                0 |                0 |       0 |         1 |
|     1056 | mysql/help_topic                                           | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         1 |
|     1057 | mysql/help_category                                        | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         1 |
|     1059 | mysql/help_relation                                        | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         1 |
|     1036 | mysql/servers                                              | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1037 | mysql/tables_priv                                          | Initialized       |        2 |                1 |                1 |                0 |       0 |         1 |
|     1038 | mysql/columns_priv                                         | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1058 | mysql/help_keyword                                         | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         1 |
|     1040 | mysql/time_zone_name                                       | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1041 | mysql/time_zone                                            | Initialized       |        0 |                1 |                0 |                0 |       1 |         1 |
|     1042 | mysql/time_zone_transition                                 | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1043 | mysql/time_zone_transition_type                            | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1044 | mysql/time_zone_leap_second                                | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1045 | mysql/procs_priv                                           | Initialized       |        0 |                1 |                1 |                0 |       0 |         1 |
|     1046 | mysql/component                                            | Uninitialized     |        0 |                0 |                0 |                0 |       1 |         1 |
|     1047 | mysql/slave_relay_log_info                                 | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1048 | mysql/slave_master_info                                    | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1049 | mysql/slave_worker_info                                    | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|     1050 | mysql/gtid_executed                                        | Initialized       |       18 |                1 |                0 |                0 |       0 |         4 |
|     1051 | mysql/replication_asynchronous_connection_failover         | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         1 |
|     1052 | mysql/replication_asynchronous_connection_failover_managed | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         1 |
|     1053 | mysql/server_cost                                          | Initialized       |        6 |                1 |                0 |                0 |       0 |         1 |
|     1054 | mysql/engine_cost                                          | Initialized       |        2 |                1 |                0 |                0 |       0 |         1 |
|     1055 | mysql/proxies_priv                                         | Initialized       |        1 |                1 |                1 |                0 |       0 |         1 |
|     1060 | sys/sys_config                                             | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         1 |
|     1061 | employees/employees                                        | Initialized       |   299157 |              929 |                0 |                0 |       0 |         3 |
|     1062 | employees/departments                                      | Initialized       |        9 |                1 |                1 |                0 |       0 |         3 |
|     1063 | employees/dept_manager                                     | Initialized       |       24 |                1 |                1 |                0 |       0 |         3 |
|     1064 | employees/dept_emp                                         | Initialized       |   331143 |              737 |              353 |                0 |       0 |         5 |
|     1065 | employees/titles                                           | Initialized       |   442010 |             1252 |                0 |                0 |       0 |         3 |
|     1066 | employees/salaries                                         | Initialized       |  2838426 |             6120 |                0 |                0 |       0 |         3 |
+----------+------------------------------------------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
38 rows in set (0.01 sec)

こちらのポイントは、このあたりかなと。統計情報が元なので、概算値ですね。

  • STATS_INITIALIZED … 統計情報が収集されている場合はInitialized、そうでない場合はUninitialized
  • NUM_ROWS … テーブル内の行数(推定)。DML実行後に更新されるが、コミットされていないトランザクションでデータの登録・削除が行われている場合は、値が正しくない可能性がある
  • CLUST_INDEX_SIZE … クラスタ化されたインデックスのディスク上のページ数。つまり、主キー+データのページ数。統計情報が収集されていない場合は、NULLとなることがある

サイズを計算してみましょう。CLUST_INDEX_SIZEはページ数ということなので、ページサイズ(innodb_page_size)を確認。

mysql> select @@innodb_page_size;
+--------------------+
| @@innodb_page_size |
+--------------------+
|              16384 |
+--------------------+
1 row in set (0.00 sec)

計算例。

mysql> select name, num_rows, floor(clust_index_size * @@innodb_page_size / 1024 / 1024) as clust_index_size_mb from information_schema.innodb_tablestats;
+------------------------------------------------------------+----------+---------------------+
| name                                                       | num_rows | clust_index_size_mb |
+------------------------------------------------------------+----------+---------------------+
| mysql/db                                                   |        2 |                   0 |
| mysql/user                                                 |        5 |                   0 |
| mysql/default_roles                                        |        0 |                   0 |
| mysql/role_edges                                           |        0 |                   0 |
| mysql/global_grants                                        |       81 |                   0 |
| mysql/password_history                                     |        0 |                   0 |
| mysql/func                                                 |        0 |                   0 |
| mysql/plugin                                               |        1 |                   0 |
| mysql/help_topic                                           |        0 |                   0 |
| mysql/help_category                                        |        0 |                   0 |
| mysql/help_relation                                        |        0 |                   0 |
| mysql/servers                                              |        0 |                   0 |
| mysql/tables_priv                                          |        2 |                   0 |
| mysql/columns_priv                                         |        0 |                   0 |
| mysql/help_keyword                                         |        0 |                   0 |
| mysql/time_zone_name                                       |        0 |                   0 |
| mysql/time_zone                                            |        0 |                   0 |
| mysql/time_zone_transition                                 |        0 |                   0 |
| mysql/time_zone_transition_type                            |        0 |                   0 |
| mysql/time_zone_leap_second                                |        0 |                   0 |
| mysql/procs_priv                                           |        0 |                   0 |
| mysql/component                                            |        0 |                   0 |
| mysql/slave_relay_log_info                                 |        0 |                   0 |
| mysql/slave_master_info                                    |        0 |                   0 |
| mysql/slave_worker_info                                    |        0 |                   0 |
| mysql/gtid_executed                                        |       18 |                   0 |
| mysql/replication_asynchronous_connection_failover         |        0 |                   0 |
| mysql/replication_asynchronous_connection_failover_managed |        0 |                   0 |
| mysql/server_cost                                          |        6 |                   0 |
| mysql/engine_cost                                          |        2 |                   0 |
| mysql/proxies_priv                                         |        1 |                   0 |
| sys/sys_config                                             |        0 |                   0 |
| employees/employees                                        |   299157 |                  14 |
| employees/departments                                      |        9 |                   0 |
| employees/dept_manager                                     |       24 |                   0 |
| employees/dept_emp                                         |   331143 |                  11 |
| employees/titles                                           |   442010 |                  19 |
| employees/salaries                                         |  2838426 |                  95 |
+------------------------------------------------------------+----------+---------------------+
38 rows in set (0.00 sec)

mysql.innodb_table_stats

最後は、mysql.innodb_table_statsテーブルです。

InnoDB 永続的統計テーブル

テーブルがmysqlデータベースにあるので、mysqlデータベースへのアクセス権限が必要です。

mysql> select * from mysql.innodb_table_stats;
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name    | last_update         | n_rows  | clustered_index_size | sum_of_other_index_sizes |
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
| employees     | departments   | 2021-04-23 15:01:45 |       9 |                    1 |                        1 |
| employees     | dept_emp      | 2021-04-23 15:02:01 |  331143 |                  737 |                      353 |
| employees     | dept_manager  | 2021-04-23 15:02:14 |      24 |                    1 |                        1 |
| employees     | employees     | 2021-04-23 15:02:01 |  299157 |                  929 |                        0 |
| employees     | salaries      | 2021-04-23 15:03:08 | 2838426 |                 6120 |                        0 |
| employees     | titles        | 2021-04-23 15:02:15 |  442010 |                 1252 |                        0 |
| mysql         | component     | 2021-04-22 14:08:57 |       0 |                    1 |                        0 |
| mysql         | gtid_executed | 2021-04-23 15:03:18 |      18 |                    1 |                        0 |
| sys           | sys_config    | 2021-04-22 14:08:59 |       6 |                    1 |                        0 |
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
9 rows in set (0.00 sec)

ポイント。

  • n_rows … テーブル内の行数
  • clustered_index_size … クラスタ化されたインデックスのサイズ(ページ数)

こちらも、統計情報が元なので概算値です。

計算例。clustered_index_sizeは、ページ数なのでinnodb_page_sizeをかけています。

mysql> select table_name, n_rows, floor(clustered_index_size * @@innodb_page_size / 1024 / 1024) as clustered_index_size_mb from mysql.innodb_table_stats;
+---------------+---------+-------------------------+
| table_name    | n_rows  | clustered_index_size_mb |
+---------------+---------+-------------------------+
| departments   |       9 |                       0 |
| dept_emp      |  331143 |                      11 |
| dept_manager  |      24 |                       0 |
| employees     |  299157 |                      14 |
| salaries      | 2838426 |                      95 |
| titles        |  442010 |                      19 |
| component     |       0 |                       0 |
| gtid_executed |      18 |                       0 |
| sys_config    |       6 |                       0 |
+---------------+---------+-------------------------+
9 rows in set (0.00 sec)

まとめ

InnoDBで、テーブルのサイズを確認する方法をいくつか調べてみました。

何種類か方法があり、統計情報から取得するので概算値だったりするものや、実際のサイズが取れるものは意外と少なかったり
するようです。

一方で、一般的な権限のユーザーで使えそうなのはshow table statusとinformation_schema.tables`テーブルのようです。

このあたりを覚えておきましょう。