MySQL 8.0 InnoDB Tablespaces之File-per-table tablespaces(单独表空间)

2023-12-21 00:44

本文主要是介绍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

相关文章

使用Dify访问mysql数据库详细代码示例

《使用Dify访问mysql数据库详细代码示例》:本文主要介绍使用Dify访问mysql数据库的相关资料,并详细讲解了如何在本地搭建数据库访问服务,使用ngrok暴露到公网,并创建知识库、数据库访... 1、在本地搭建数据库访问的服务,并使用ngrok暴露到公网。#sql_tools.pyfrom

MySQL中实现多表查询的操作方法(配sql+实操图+案例巩固 通俗易懂版)

《MySQL中实现多表查询的操作方法(配sql+实操图+案例巩固通俗易懂版)》本文主要讲解了MySQL中的多表查询,包括子查询、笛卡尔积、自连接、多表查询的实现方法以及多列子查询等,通过实际例子和操... 目录复合查询1. 回顾查询基本操作group by 分组having1. 显示部门号为10的部门名,员

MyBatis-Plus中静态工具Db的多种用法及实例分析

《MyBatis-Plus中静态工具Db的多种用法及实例分析》本文将详细讲解MyBatis-Plus中静态工具Db的各种用法,并结合具体案例进行演示和说明,具有很好的参考价值,希望对大家有所帮助,如有... 目录MyBATis-Plus中静态工具Db的多种用法及实例案例背景使用静态工具Db进行数据库操作插入

MySQL中COALESCE函数示例详解

《MySQL中COALESCE函数示例详解》COALESCE是一个功能强大且常用的SQL函数,主要用来处理NULL值和实现灵活的值选择策略,能够使查询逻辑更清晰、简洁,:本文主要介绍MySQL中C... 目录语法示例1. 替换 NULL 值2. 用于字段默认值3. 多列优先级4. 结合聚合函数注意事项总结C

通过ibd文件恢复MySql数据的操作方法

《通过ibd文件恢复MySql数据的操作方法》文章介绍通过.ibd文件恢复MySQL数据的过程,包括知道表结构和不知道表结构两种情况,对于知道表结构的情况,可以直接将.ibd文件复制到新的数据库目录并... 目录第一种情况:知道表结构第二种情况:不知道表结构总结今天干了一件大事,安装1Panel导致原来服务

Java实现将byte[]转换为File对象

《Java实现将byte[]转换为File对象》这篇文章将通过一个简单的例子为大家演示Java如何实现byte[]转换为File对象,并将其上传到外部服务器,感兴趣的小伙伴可以跟随小编一起学习一下... 目录前言1. 问题背景2. 环境准备3. 实现步骤3.1 从 URL 获取图片字节数据3.2 将字节数组

mysql关联查询速度慢的问题及解决

《mysql关联查询速度慢的问题及解决》:本文主要介绍mysql关联查询速度慢的问题及解决方案,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql关联查询速度慢1. 记录原因1.1 在一次线上的服务中1.2 最终发现2. 解决方案3. 具体操作总结mysql

Linux搭建Mysql主从同步的教程

《Linux搭建Mysql主从同步的教程》:本文主要介绍Linux搭建Mysql主从同步的教程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录linux搭建mysql主从同步1.启动mysql服务2.修改Mysql主库配置文件/etc/my.cnf3.重启主库my

MySql中的数据库连接池详解

《MySql中的数据库连接池详解》:本文主要介绍MySql中的数据库连接池方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql数据库连接池1、概念2、为什么会出现数据库连接池3、原理4、数据库连接池的提供商5、DataSource数据源6、DBCP7、C

MySQL的隐式锁(Implicit Lock)原理实现

《MySQL的隐式锁(ImplicitLock)原理实现》MySQL的InnoDB存储引擎中隐式锁是一种自动管理的锁,用于保证事务在行级别操作时的数据一致性和安全性,本文主要介绍了MySQL的隐式锁... 目录1. 背景:什么是隐式锁?2. 隐式锁的工作原理3. 隐式锁的类型4. 隐式锁的实现与源代码分析4