什么是表空间，为什么要使用它?

While investigating an issue, I came across this error:

在调查一个问题时，我发现了这个错误:

30503 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_index_stats uses space ID: 2 at filepath: ./mysql/innodb_index_stats.ibd. Cannot open tablespace Mydb/be_command_log which uses space ID: 2 at filepath: ./Mydb/be_command_log.ibd

30503[错误]InnoDB:试图打开之前打开的表空间。以前的表空间mysql/innodb_index_stats使用空间ID: 2在filepath: ./mysql/innodb_index_stats.ibd。无法在filepath: ./Mydb/be_command_log中打开使用空格ID: 2的表空间Mydb/be_command_log.ibd

After reading a bit about this issue, I came to know that this is a known issue of MySQL.

在阅读了一些关于这个问题的文章后，我了解到这是一个众所周知的MySQL问题。

But my problem is that I don't know much about how tablespaces really work. How are they useful? I read this definition, but it does not give all the information.

但我的问题是，我不太了解表空间是如何工作的。他们是如何有用吗?我读过这个定义，但它并没有给出所有的信息。

Can someone share some detailed information about what are tablespaces and how do they work?

有人能分享一些关于什么是表空间以及它们如何工作的详细信息吗?

4 个解决方案

#1

A data file that can hold data for one or more InnoDB tables and associated indexes.

可以为一个或多个InnoDB表和相关索引保存数据的数据文件。

There are many types of tablespaces based on the configuration w.r.t the information clubbing per table. These are,

基于配置w.r，有许多类型的表空间。每个桌子上都有信息。这些都是,

a. System tablespace b. File per tablespace c. General tablespace

a.系统表空间b.每个表空间的文件c.一般表空间

System tablespace contains,

系统表空间包含,

InnoDB data dictionary.
InnoDB数据字典。
DoubleWrite Buffer.
DoubleWrite缓冲区。
Change buffer
改变缓冲
Undo Logs.
Undo日志。

Apart from this it also contains,

除此之外，它还包含，

Tables &
表&
Index data
索引数据

Associated file is .idbdata1

.idbdata1相关文件

The innodb_file_per_table option, which is enabled by default in MySQL 5.6 and higher, allows tables to be created in file-per-table tablespaces, with a separate data file for each table. Enabling the innodb_file_per_table option makes available other MySQL features such as table compression and transportable tablespaces.

innodb_file_per_table选项(在MySQL 5.6或更高版本中默认启用)允许在每个表的表空间中创建表，并为每个表创建单独的数据文件。启用innodb_file_per_table选项可以提供其他MySQL特性，如表压缩和可传输表空间。

Associated file is .idbd

.idbd相关文件

InnoDB introduced general tablespaces in MySQL 5.7.6. General tablespaces are shared tablespaces created using CREATE TABLESPACE syntax. They can be created outside of the MySQL data directory, are capable of holding multiple tables, and support tables of all row formats.

InnoDB在MySQL 5.7.6中引入了一般的表空间。一般的表空间是使用CREATE表空间语法创建的共享表空间。它们可以在MySQL数据目录之外创建，能够保存多个表，并支持所有行格式的表。

#2

By default, InnoDB contains only one tablespace called the system tablespace whose identifier is 0. More tablespaces can be created indirectly using the innodb_file_per_table configuration parameter. A tablespace consists of a chain of files. The size of the files does not have to be divisible by the database block size, because we may just leave the last incomplete block unused. When a new file is appended to the tablespace, the maximum size of the file is also specified. At the moment, we think that it is best to extend the file to its maximum size already at the creation of the file, because then we can avoid dynamically extending the file when more space is needed for the tablespace. Data files are dynamically extended, but redo log files are pre-allocated. Also, as already mentioned earlier, only the system tablespace can have more than one data file. It is also clearly mentioned that even though the tablespace can have multiple files, they are thought of as one single large file concatenated together. So the order of files within the tablespace is important.

默认情况下，InnoDB只包含一个名为system tablespace的表空间，该表空间的标识符为0。可以使用innodb_file_per_table配置参数间接创建更多的表空间。表空间由一系列文件组成。文件的大小不需要被数据库块大小整除，因为我们可能只保留最后一个不完整的块未使用。当一个新文件附加到表空间时，也指定了文件的最大大小。目前，我们认为最好在创建文件时将文件扩展到最大大小，因为这样可以避免在需要更多空间时动态扩展文件。数据文件是动态扩展的，但是重做日志文件是预先分配的。同样，正如前面提到的，只有系统表空间可以有多个数据文件。很明显，尽管表空间可以有多个文件，但是它们被认为是一个单独的大文件连接在一起。因此表空间中文件的顺序很重要。

From https://blogs.oracle.com/mysqlinnodb/entry/data_organization_in_innodb

从https://blogs.oracle.com/mysqlinnodb/entry/data_organization_in_innodb

#3

MySQL Innodb TableSpace is a location where the data resides in the disk called data directory (By default "system tablespace"). Example:

MySQL Innodb表空间是数据驻留在称为数据目录的磁盘中的位置(默认情况下是“系统表空间”)。例子:

"/var/lib/mysql"

“/ var / lib / mysql”

From MySQL version 5.6.6, an user can create and specify the tablespace they want to store the data which enables throughput of data manipulation and recovery process. InnoDB's file-per-table feature offers each tables to have separate .ibd data & index files which represents an individual general tablespace. So that each table in a database can point various locations of data directories.

从MySQL版本5.6.6开始，用户可以创建并指定要存储数据的表空间，从而实现数据操作和恢复过程的吞吐量。InnoDB的文件-每个表特性提供了每个表有单独的.ibd数据和索引文件，这些文件表示单个的普通表空间。这样，数据库中的每个表都可以指向数据目录的不同位置。

Ex :

例:

/home/{user}/test/data/{dbName}/{tableName}.ibd

/home/{用户} /测试/数据/ { dbName } / {表} .ibd

/home/{user}/work/data/{dbName}/{tableName}.ibd

/home/{用户} /工作/数据/ { dbName } / {表} .ibd

For more about the file-per-table tablespace, refer this mysql documentation.

有关每个表的表空间文件的更多信息，请参考这个mysql文档。

#4

sql clients shall only use sql objects and not be concerned with where the database server physically stores that information.

sql客户机应该只使用sql对象，而不关心数据库服务器在哪里物理地存储该信息。

that's why the concept of tablespace is needed. sql objects like table data goes into a tablespace from the point of view of the sql client.

这就是为什么需要表空间的概念。从sql客户机的角度来看，像表数据这样的sql对象进入表空间。

The db server admins are now free to physically place the tablespace where they want it to go physically, the sql client programs still work.

db服务器管理员现在可以*地将表空间放到他们想要的物理位置上，sql客户机程序仍然有效。

#1