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

2023-12-21 00:44
文章标签 mysql 空间 table file 8.0 innodb database 单独 per tablespaces

本文主要是介绍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创建表
    • 例题
    • 参考

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

MySQL 8.0 OCP (1Z0-908) 考点概要

MySQL 8.0 OCP (1Z0-908) 考点精析-安装与配置考点1：设置系统变量
【MySQL】控制MySQL优化器行为方法之optimizer_switch系统变量
【MySQL】MySQL系统变量（system variables）列表（mysqld --verbose --help的结果例）
【MySQL】MySQL系统变量（system variables）列表（SHOW VARIABLES 的结果例）
MySQL 8.0 OCP (1Z0-908) 考点精析-备份与恢复考点1：MySQL Enterprise Backup概要
MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点1：sys.statement_analysis视图
MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点2：系统变量的确认
MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点3：EXPLAIN ANALYZE
MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点4：慢速查询日志（slow query log）
MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点5：表连接算法（join algorithm）
MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点6：MySQL Enterprise Monitor之Query Analyzer
MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点1：二进制日志文件（Binary log）
MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点5：数据字典(Data Dictionary)
MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点6：InnoDB Tablespaces之系统表空间（System Tablespace）

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 -ADatabase 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

相关变量：innodb_file_per_table

MySQL使用变量innodb_file_per_table控制创建表的表空间。

  • 当启用时，表会创建在 File-per-table tablespaces（单独表空间）中。
  • 当关闭时，表会创建在System tablespace（系统表空间）中。

innodb_file_per_table变量可以使用SET GLOBAL语句在运行时进行配置，在启动时在命令行中指定，或在选项文件中指定。
当位于File-per-table tablespaces（单独表空间）中的表被truncate 或者drop时，释放的空间将返回给操作系统。
位于系统表空间中的表被truncate 或者drop时，仅释放系统表空间中的空间。系统表空间中的空闲空间可以再次用于InnoDB数据，但不会返回给操作系统。
系统表空间数据文件永远不会缩小。

Command-Line Format–innodb-file-per-table
System Variableinnodb_file_per_table
ScopeGlobal
DynamicYes
SET_VAR Hint AppliesNo
TypeBoolean
Default ValueON

例：

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

使用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 -ADatabase 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

这篇关于MySQL 8.0 InnoDB Tablespaces之File-per-table tablespaces（单独表空间）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/518110。

相关文章

Mysql用户授权(GRANT)语法及示例解读

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》：本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权（GRANT）语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT
阅读更多...
Mysql如何解决死锁问题

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》：本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1
阅读更多...
SQL BETWEEN 的常见用法小结

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中，BETWEEN是一个操作符，用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用
阅读更多...
MySQL索引的优化之LIKE模糊查询功能实现

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》：本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分
阅读更多...
MySql match against工具详细用法

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引（Full-Textindex）的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七
阅读更多...
数据库面试必备之MySQL中的乐观锁与悲观锁

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》：本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁（一）原理（二）应用场景（三）示例代码三、悲观锁（一）原理（二）应用场景（三）示例
阅读更多...
SQL表间关联查询实例详解

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括：左连接（leftjoin）、右连接（rightjoin）、全连接（fulljoin）、内连接（innerjoin）、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表
阅读更多...
SQL server配置管理器找不到如何打开它

SQL server配置管理器找不到如何打开它

《SQLserver配置管理器找不到如何打开它》最近遇到了SQLserver配置管理器打不开的问题,尝试在开始菜单栏搜SQLServerManager无果,于是将自己找到的方法总结分享给大家,对SQ... 目录方法一：桌面图标进入方法二：运行窗口进入方法三：查找文件路径方法四：检查 SQL Server 安
阅读更多...
MySQL 中的 LIMIT 语句及基本用法

MySQL 中的 LIMIT 语句及基本用法

《MySQL中的LIMIT语句及基本用法》LIMIT语句用于限制查询返回的行数,常用于分页查询或取部分数据,提高查询效率,：本文主要介绍MySQL中的LIMIT语句,需要的朋友可以参考下... 目录mysql 中的 LIMIT 语句1. LIMIT 语法2. LIMIT 基本用法(1) 获取前 N 行数据(
阅读更多...
MySQL 分区与分库分表策略应用小结

MySQL 分区与分库分表策略应用小结

《MySQL分区与分库分表策略应用小结》在大数据量、复杂查询和高并发的应用场景下,单一数据库往往难以满足性能和扩展性的要求,本文将详细介绍这两种策略的基本概念、实现方法及优缺点,并通过实际案例展示如... 目录mysql 分区与分库分表策略1. 数据库水平拆分的背景2. MySQL 分区策略2.1 分区概念
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2