MySQL 8.0 InnoDB Tablespaces之File-per-table tablespaces（单独表空间）

2023-12-20 17:53:43

文章目录

MySQL 8.0 InnoDB Tablespaces之File-per-table tablespaces（单独表空间）

【免责声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)

MySQL 8.0 InnoDB Tablespaces之File-per-table tablespaces（单独表空间）

InnoDB表空间是MySQL中用于存储InnoDB存储引擎表数据和索引的物理文件。
InnoDB Architecture
InnoDB表空间根据用途可以分成多种类型：

数据表空间:
- System tablespace（系统表空间）
- File-per-table tablespaces（单独表空间）
- General tablespaces（一般表空间）
Undo 表空间
临时表空间（Temporary table tablespaces）

File-per-table tablespaces（单独表空间）

MySQL中在InnoDB中创建表的时候默认使用File-per-table tablespaces（单独表空间），即创建一个InnoDB 表会创建一个独立的表空间用于保存表的数据、索引以及元数据。
表空间的数据文件会保存在MySQL data目录中，并且.ibd文件的命名以表的名称命名（table_name.ibd）。
一个表对应一个表空间一个数据文件。

例：创建一个测试表

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test(a int);
Query OK, 0 rows affected (0.02 sec)

OS上会创建对应的.ibd文件。

root@mysql-vm:/var/lib/mysql/testdb# ls -l test.ibd
-rw-r----- 1 mysql mysql 114688 Dec 11 23:00 test.ibd

Command-Line Format	–innodb-file-per-table
System Variable	innodb_file_per_table
Scope	Global
Dynamic	Yes
SET_VAR Hint Applies	No
Type	Boolean
Default Value	ON

使用TABLESPACE子句指定表空间

当变量innodb_file_per_table设置为关闭状态时，默认创建的表会创建在system表空间中。

变量innodb_file_per_table设置为关闭状态时创建表

例：

mysql> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set global innodb_file_per_table='OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> create table test_file_pertable(a int);
Query OK, 0 rows affected (0.03 sec)

mysql> select
    ->     name as 'table_name',
    ->     space_type
    -> from
    ->     information_schema.innodb_tables
    -> where
    ->     name like '%test_file_pertable%' \g
+---------------------------+------------+
| table_name                | space_type |
+---------------------------+------------+
| testdb/test_file_pertable | System     |
+---------------------------+------------+
1 row in set (0.00 sec)

指定TABLESPACE：innodb_file_per_table创建表

当变量innodb_file_per_table设置为关闭状态时，也可以通过使用TABLESPACE子句指定使用单独表空间（innodb_file_per_table）。

例：

mysql> show variables like  'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.01 sec)

mysql> create table test_file_pertable_tablespace(a int)
    -> tablespace =innodb_file_per_table;
Query OK, 0 rows affected (0.04 sec)

mysql> select
    ->     name as 'table_name',
    ->     space_type
    -> from
    ->     information_schema.innodb_tables
    -> where
    ->     name like '%test_file_pertable_tablespace%' \g
+--------------------------------------+------------+
| table_name                           | space_type |
+--------------------------------------+------------+
| testdb/test_file_pertable_tablespace | Single     |
+--------------------------------------+------------+
1 row in set (0.01 sec)

mysql>

通过information_schema.innodb_tables表可以看到创建表的表空间类型为Single（单独表空间），并且创建了表同名的ibd文件。

root@mysql-vm:/var/lib/mysql/testdb# ls -l test_file_pertable_tablespace.ibd
-rw-r----- 1 mysql mysql 114688 Dec 20 12:56 test_file_pertable_tablespace.ibd
root@mysql-vm:/var/lib/mysql/testdb#

例题

Choose two.
Your MySQL server was upgraded from an earlier major version.
The sales database contains three tables, one of which is the transactions table, which has 4million rows.
You are running low on disk space on the datadir partition and begin to investigate.
Examine these commands and output:

在这里插入代码片

Which two statements are true?
A) The transactions table was created with innodb_file_per_table=OFF.
B) Truncating the sales and leads table will free up disk space.
C) Executing SET GLOBAL innodb_row_format=COMPRESSED and then ALTER TABLE transactions will free up disk space.
D) Executing ALTER TABLE transactions will enable you to free up disk space.
E) Truncating the transactions table will free up the most disk space.

答案 AB

例题解析：
1.innodb_file_per_table=on时，表会创建在 File-per-table tablespaces（单独表空间）中
MySQL中在InnoDB中创建表的时候默认使用File-per-table tablespaces（单独表空间），即创建一个InnoDB 表会创建一个独立的表空间用于保存表的数据、索引以及元数据。
表空间的数据文件会保存在MySQL data目录中，并且.ibd文件的命名以表的名称命名（table_name.ibd）。
一个表对应一个表空间一个数据文件。
2. 单独表空间中的表被truncate 或者drop时，会释放的空间将返回给操作系统。

参考

15.6.3.2 File-Per-Table Tablespaces
https://dev.mysql.com/doc/refman/8.0/en/innodb-file-per-table-tablespaces.html

文章来源:https://blog.csdn.net/lukeUnique/article/details/133869218
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！

MySQL 8.0 InnoDB Tablespaces之File-per-table tablespaces（单独表空间）

文章目录

MySQL 8.0 InnoDB Tablespaces之File-per-table tablespaces（单独表空间）

File-per-table tablespaces（单独表空间）

相关变量：innodb_file_per_table

使用TABLESPACE子句指定表空间

变量innodb_file_per_table设置为关闭状态时创建表

指定TABLESPACE：innodb_file_per_table创建表

例题

参考