InnoDB 테이블스페이스와 파일구조

Tablespace

1. Tablespace란?

• system tablespace file + file-per-table tablespace + general tablespace + undo tablespace + Global Temporary Tablespace

2. Tablespace 파일

• ibdata
• file-per-table tablespace
  • single InnoDB table로 데이타와 인덱스 포함
  • 환경변수 innodb_file_per_table = ON 으로 설정시 활성화
  • .ibd 확장자 이름으로 생성
• general tablespace
  • shared tablespace로 한개의 데이터 파일로 구성. CREATE TABLESPACE 명령어로 생성

💻 CREATE GENERAL TABLESPACE

CREATE TABLESPACE tb1 ADD DATAFILE 'tb1.ibd' ENGINE=INNODB;

MySQL8.0 Architecture 👇

👉 information_schema에서 SPACE_TYPE, SPACE 정보 확인 가능

mysql> SELECT A.NAME,A.SPACE,A.SPACE_TYPE,B.NAME
    -> FROM information_schema.INNODB_TABLES A, information_schema.INNODB_TABLESPACES B
    -> WHERE A.SPACE = B.SPACE;
+------------------------------------------------------------+------------+------------+-----------------+
| NAME                                                       | SPACE      | SPACE_TYPE | NAME            |
+------------------------------------------------------------+------------+------------+-----------------+
| mysql/proxies_priv                                         | 4294967294 | General    | mysql           |
| mysql/engine_cost                                          | 4294967294 | General    | mysql           |
| mysql/server_cost                                          | 4294967294 | General    | mysql           |
| mysql/replication_group_configuration_version              | 4294967294 | General    | mysql           |
| mysql/replication_group_member_actions                     | 4294967294 | General    | mysql           |
| mysql/replication_asynchronous_connection_failover_managed | 4294967294 | General    | mysql           |
| mysql/replication_asynchronous_connection_failover         | 4294967294 | General    | mysql           |
| mysql/gtid_executed                                        | 4294967294 | General    | mysql           |
| mysql/slave_worker_info                                    | 4294967294 | General    | mysql           |
| mysql/slave_master_info                                    | 4294967294 | General    | mysql           |
| mysql/slave_relay_log_info                                 | 4294967294 | General    | mysql           |
| mysql/component                                            | 4294967294 | General    | mysql           |
| mysql/procs_priv                                           | 4294967294 | General    | mysql           |
| mysql/time_zone_leap_second                                | 4294967294 | General    | mysql           |
| mysql/time_zone_transition_type                            | 4294967294 | General    | mysql           |
| mysql/time_zone_transition                                 | 4294967294 | General    | mysql           |
| mysql/time_zone                                            | 4294967294 | General    | mysql           |
| mysql/time_zone_name                                       | 4294967294 | General    | mysql           |
| mysql/help_keyword                                         | 4294967294 | General    | mysql           |
| mysql/columns_priv                                         | 4294967294 | General    | mysql           |
| mysql/tables_priv                                          | 4294967294 | General    | mysql           |
| mysql/servers                                              | 4294967294 | General    | mysql           |
| mysql/help_relation                                        | 4294967294 | General    | mysql           |
| mysql/help_category                                        | 4294967294 | General    | mysql           |
| mysql/help_topic                                           | 4294967294 | General    | mysql           |
| mysql/plugin                                               | 4294967294 | General    | mysql           |
| mysql/func                                                 | 4294967294 | General    | mysql           |
| mysql/password_history                                     | 4294967294 | General    | mysql           |
| mysql/global_grants                                        | 4294967294 | General    | mysql           |
| mysql/role_edges                                           | 4294967294 | General    | mysql           |
| mysql/default_roles                                        | 4294967294 | General    | mysql           |
| mysql/user                                                 | 4294967294 | General    | mysql           |
| mysql/db                                                   | 4294967294 | General    | mysql           |
| sys/sys_config                                             |          1 | Single     | sys/sys_config  |
| testdb/tesbtb01                                            |          2 | Single     | testdb/tesbtb01 |
| testdb/testdb02                                            |          3 | Single     | testdb/testdb02 |
| testdb/testtb03                                            |          4 | Single     | testdb/testtb03 |
| testdb/testtb04                                            |          5 | Single     | testdb/testtb04 |
+------------------------------------------------------------+------------+------------+-----------------+
38 rows in set (0.00 sec)

---

Data Dictionary

1. 메타데이터 파일들의 삭제

• frm, par, TRN, TRG, isl, db.opt, ddl_log.log 삭제
• 데이터 딕셔너리로 통합

The metadata files listed below are removed from MySQL. Unless otherwise noted, data >previously stored in metadata files is now stored in data dictionary tables.

• .frm files : Table metadata files. With the removal of .frm files:
  • The 64KB table definition size limit imposed by the .frm file structure is removed.
  • The INFORMATION_SCHEMA.TABLES VERSION column reports a hardcoded value of 10, which is >the last .frm file version used in MySQL 5.7.

# MySQL 8.0 Reference Manual
14.2 Removal of File-based Metadata Storage

2. mysql.ibd

• MySQL8.0의 데이터 딕셔너리 파일
• mysql 데이터베이스의 데이터들은 innodb엔진으로 변경되며 mysql.ibd 테이블 스페이스에 저장
• general tablespace
• InnoDB 엔진 이외의 엔진들에 대한 metadata 저장은 Serialized Dictionary Information (SDI) 파일에 JSON으로 저장.