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虚拟列的使用场景

Mysql虚拟列的使用场景

《Mysql虚拟列的使用场景》MySQL虚拟列是一种在查询时动态生成的特殊列,它不占用存储空间,可以提高查询效率和数据处理便利性,本文给大家介绍Mysql虚拟列的相关知识,感兴趣的朋友一起看看吧... 目录1. 介绍mysql虚拟列1.1 定义和作用1.2 虚拟列与普通列的区别2. MySQL虚拟列的类型2
阅读更多...
mysql数据库分区的使用

mysql数据库分区的使用

《mysql数据库分区的使用》MySQL分区技术通过将大表分割成多个较小片段,提高查询性能、管理效率和数据存储效率,本文就来介绍一下mysql数据库分区的使用,感兴趣的可以了解一下... 目录【一】分区的基本概念【1】物理存储与逻辑分割【2】查询性能提升【3】数据管理与维护【4】扩展性与并行处理【二】分区的
阅读更多...
MySQL中时区参数time_zone解读

MySQL中时区参数time_zone解读

《MySQL中时区参数time_zone解读》MySQL时区参数time_zone用于控制系统函数和字段的DEFAULTCURRENT_TIMESTAMP属性,修改时区可能会影响timestamp类型... 目录前言1.时区参数影响2.如何设置3.字段类型选择总结前言mysql 时区参数 time_zon
阅读更多...
Python MySQL如何通过Binlog获取变更记录恢复数据

Python MySQL如何通过Binlog获取变更记录恢复数据

《PythonMySQL如何通过Binlog获取变更记录恢复数据》本文介绍了如何使用Python和pymysqlreplication库通过MySQL的二进制日志（Binlog）获取数据库的变更记录... 目录python mysql通过Binlog获取变更记录恢复数据1.安装pymysqlreplicat
阅读更多...
使用SQL语言查询多个Excel表格的操作方法

使用SQL语言查询多个Excel表格的操作方法

《使用SQL语言查询多个Excel表格的操作方法》本文介绍了如何使用SQL语言查询多个Excel表格,通过将所有Excel表格放入一个.xlsx文件中,并使用pandas和pandasql库进行读取和... 目录如何用SQL语言查询多个Excel表格如何使用sql查询excel内容1. 简介2. 实现思路3
阅读更多...
Mysql DATETIME 毫秒坑的解决

Mysql DATETIME 毫秒坑的解决

《MysqlDATETIME毫秒坑的解决》本文主要介绍了MysqlDATETIME毫秒坑的解决,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着... 今天写代码突发一个诡异的 bug，代码逻辑大概如下。1. 新增退款单记录boolean save = s
阅读更多...
mysql-8.0.30压缩包版安装和配置MySQL环境过程

mysql-8.0.30压缩包版安装和配置MySQL环境过程

《mysql-8.0.30压缩包版安装和配置MySQL环境过程》该文章介绍了如何在Windows系统中下载、安装和配置MySQL数据库,包括下载地址、解压文件、创建和配置my.ini文件、设置环境变量... 目录压缩包安装配置下载配置环境变量下载和初始化总结压缩包安装配置下载下载地址：https://d
阅读更多...
MySQL中的锁和MVCC机制解读

MySQL中的锁和MVCC机制解读

《MySQL中的锁和MVCC机制解读》MySQL事务、锁和MVCC机制是确保数据库操作原子性、一致性和隔离性的关键,事务必须遵循ACID原则,锁的类型包括表级锁、行级锁和意向锁,MVCC通过非锁定读和... 目录mysql的锁和MVCC机制事务的概念与ACID特性锁的类型及其工作机制锁的粒度与性能影响多版本
阅读更多...
MYSQL行列转置方式

MYSQL行列转置方式

《MYSQL行列转置方式》本文介绍了如何使用MySQL和Navicat进行列转行操作,首先,创建了一个名为`grade`的表,并插入多条数据,然后,通过修改查询SQL语句,使用`CASE`和`IF`函... 目录mysql行列转置开始列转行之前的准备下面开始步入正题总结MYSQL行列转置环境准备：mysq
阅读更多...
MySQL不使用子查询的原因及优化案例

MySQL不使用子查询的原因及优化案例

《MySQL不使用子查询的原因及优化案例》对于mysql,不推荐使用子查询,效率太差,执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响,本文给大家... 目录不推荐使用子查询和JOIN的原因解决方案优化案例案例1：查询所有有库存的商品信息案例2：使用EX
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2