InnoDB でテーブルのデータサイズを算出する際の注意点

nbeppu 585views 更新:2014年8月27日

こちらの方法で MySQL のデータサイズを算出することができますが、 InnoDB の場合、標準環境でページサイズが16KBとなるため、データサイズの算出も 16KB 区切りとなります。なお、レコードの件数が 0 件の場合も最低16KB割り当てられますので、データサイズも16KBとなることに注意しましょう。

mysql> select table_name, table_rows, engine, row_format, data_length from information_schema.tables where table_schema=database();
+-------------------------------+------------+--------+------------+-------------+
| table_name                    | table_rows | engine | row_format | data_length |
+-------------------------------+------------+--------+------------+-------------+
| activity                      |          0 | InnoDB | Compact    |       16384 |
| activity_map                  |          0 | InnoDB | Compact    |       16384 |
| aipo_license                  |          0 | InnoDB | Compact    |       16384 |
| app_data                      |          0 | InnoDB | Compact    |       16384 |
| application                   |          0 | InnoDB | Compact    |       16384 |
| container_config              |          3 | InnoDB | Compact    |       16384 |
| eip_facility_group            |          0 | InnoDB | Compact    |       16384 |
| eip_m_address_group           |          0 | InnoDB | Compact    |       16384 |
| eip_m_addressbook             |          0 | InnoDB | Compact    |       16384 |
| eip_m_addressbook_company     |          0 | InnoDB | Compact    |       16384 |
| eip_m_company                 |          0 | InnoDB | Compact    |       16384 |
| eip_m_config                  |          2 | InnoDB | Compact    |       16384 |
| eip_m_facility                |          0 | InnoDB | Compact    |       16384 |
| eip_m_facility_group          |          0 | InnoDB | Compact    |       16384 |
| eip_m_facility_group_map      |          0 | InnoDB | Compact    |       16384 |
| eip_m_gpdb_kubun              |          0 | InnoDB | Compact    |       16384 |
| eip_m_gpdb_kubun_value        |         47 | InnoDB | Compact    |       16384 |
| eip_m_inactive_application    |          2 | InnoDB | Compact    |       16384 |
| eip_m_mail_account            |          0 | InnoDB | Compact    |       16384 |
| eip_m_mail_notify_conf        |          8 | InnoDB | Compact    |       16384 |
| eip_m_position                |          0 | InnoDB | Compact    |       16384 |
| eip_m_post                    |          0 | InnoDB | Compact    |       16384 |
| eip_m_user_position           |          2 | InnoDB | Compact    |       16384 |
| eip_t_acl_map                 |          0 | InnoDB | Compact    |       16384 |
| eip_t_acl_portlet_feature     |         31 | InnoDB | Compact    |       16384 |
| eip_t_acl_role                |         28 | InnoDB | Compact    |       16384 |
| eip_t_acl_user_role_map       |         56 | InnoDB | Compact    |       16384 |
| eip_t_addressbook_group_map   |          0 | InnoDB | Compact    |       16384 |
| eip_t_blog                    |          0 | InnoDB | Compact    |       16384 |
| eip_t_blog_comment            |          0 | InnoDB | Compact    |       16384 |
| eip_t_blog_entry              |          0 | InnoDB | Compact    |       16384 |
| eip_t_blog_file               |          0 | InnoDB | Compact    |       16384 |
| eip_t_blog_footmark_map       |          0 | InnoDB | Compact    |       16384 |
| eip_t_blog_thema              |          0 | InnoDB | Compact    |       16384 |
| eip_t_cabinet_file            |          0 | InnoDB | Compact    |       16384 |
| eip_t_cabinet_folder          |          0 | InnoDB | Compact    |       16384 |
| eip_t_cabinet_folder_map      |          0 | InnoDB | Compact    |       16384 |
| eip_t_common_category         |          0 | InnoDB | Compact    |       16384 |
| eip_t_eventlog                |          2 | InnoDB | Compact    |       16384 |
| eip_t_ext_timecard            |          0 | InnoDB | Compact    |       16384 |
| eip_t_ext_timecard_system     |          0 | InnoDB | Compact    |       16384 |
| eip_t_ext_timecard_system_map |          0 | InnoDB | Compact    |       16384 |
| eip_t_gpdb                    |          0 | InnoDB | Compact    |       16384 |
| eip_t_gpdb_item               |          0 | InnoDB | Compact    |       16384 |
| eip_t_gpdb_record             |          0 | InnoDB | Compact    |       16384 |
| eip_t_gpdb_record_file        |          0 | InnoDB | Compact    |       16384 |
| eip_t_mail                    |          0 | InnoDB | Compact    |       16384 |
| eip_t_mail_filter             |          0 | InnoDB | Compact    |       16384 |
| eip_t_mail_folder             |          0 | InnoDB | Compact    |       16384 |
| eip_t_memo                    |          0 | InnoDB | Compact    |       16384 |
| eip_t_msgboard_category       |          0 | InnoDB | Compact    |       16384 |
| eip_t_msgboard_category_map   |          0 | InnoDB | Compact    |       16384 |
| eip_t_msgboard_file           |          0 | InnoDB | Compact    |       16384 |
| eip_t_msgboard_topic          |          0 | InnoDB | Compact    |       16384 |
| eip_t_note                    |          0 | InnoDB | Compact    |       16384 |
| eip_t_note_map                |          0 | InnoDB | Compact    |       16384 |
| eip_t_report                  |          0 | InnoDB | Compact    |       16384 |
| eip_t_report_file             |          0 | InnoDB | Compact    |       16384 |
| eip_t_report_map              |          0 | InnoDB | Compact    |       16384 |
| eip_t_report_member_map       |          0 | InnoDB | Compact    |       16384 |
| eip_t_schedule                |          0 | InnoDB | Compact    |       16384 |
| eip_t_schedule_map            |          0 | InnoDB | Compact    |       16384 |
| eip_t_timecard                |          0 | InnoDB | Compact    |       16384 |
| eip_t_timecard_settings       |          0 | InnoDB | Compact    |       16384 |
| eip_t_timeline                |          0 | InnoDB | Compact    |       16384 |
| eip_t_timeline_file           |          3 | InnoDB | Compact    |       16384 |
| eip_t_timeline_like           |          0 | InnoDB | Compact    |       16384 |
| eip_t_timeline_map            |          0 | InnoDB | Compact    |       16384 |
| eip_t_timeline_url            |          0 | InnoDB | Compact    |       16384 |
| eip_t_todo                    |          0 | InnoDB | Compact    |       16384 |
| eip_t_todo_category           |          0 | InnoDB | Compact    |       16384 |
| eip_t_whatsnew                |          0 | InnoDB | Compact    |       16384 |
| eip_t_wiki                    |          0 | InnoDB | Compact    |       16384 |
| eip_t_wiki_file               |          0 | InnoDB | Compact    |       16384 |
| eip_t_workflow_category       |          9 | InnoDB | Compact    |       16384 |
| eip_t_workflow_file           |          0 | InnoDB | Compact    |       16384 |
| eip_t_workflow_request        |          0 | InnoDB | Compact    |       16384 |
| eip_t_workflow_request_map    |          0 | InnoDB | Compact    |       16384 |
| eip_t_workflow_route          |          0 | InnoDB | Compact    |       16384 |
| jetspeed_group_profile        |          2 | InnoDB | Compact    |       16384 |
| jetspeed_role_profile         |          2 | InnoDB | Compact    |       16384 |
| jetspeed_user_profile         |         31 | InnoDB | Compact    |       81920 |
| module_id                     |          0 | InnoDB | Compact    |       16384 |
| oauth_consumer                |          0 | InnoDB | Compact    |       16384 |
| oauth_entry                   |          0 | InnoDB | Compact    |       16384 |
| oauth_token                   |          0 | InnoDB | Compact    |       16384 |
| turbine_group                 |          3 | InnoDB | Compact    |       16384 |
| turbine_permission            |          8 | InnoDB | Compact    |       16384 |
| turbine_role                  |          3 | InnoDB | Compact    |       16384 |
| turbine_role_permission       |         15 | InnoDB | Compact    |       16384 |
| turbine_user                  |          5 | InnoDB | Compact    |       16384 |
| turbine_user_group_role       |         14 | InnoDB | Compact    |       16384 |
+-------------------------------+------------+--------+------------+-------------+
92 rows in set (0.03 sec)

ログイン / 新規登録してコメントする

このソースコードをストックして後で利用したり、作業に利用したソースコードをまとめることができます。

こちらもお役に立つかもしれません