Shared tablespaces and independent tablespaces of MySQL InnoDB

Concepts of shared tablespaces and independent tablespace in MySQL.


Guide: When you learn more about MySQL, it is always habitual to compare with Oracle database. Particularly, when learning the storage structure of MySQL InnoDB, you inevitably compare it with Oracle. There are tablespaces, segments, regions, blocks, and data files involved in Oracle's data storage,and MySQL InnoDB is similar. However, in MySQL, we have concepts of shared tablespaces and independent tablespace.241.png

1. Basic Concepts

Shared tablespace: All data of InnoDB is stored in a single tablespace, and this tablespace can be composed of many files. A table can exist across multiple files, so the size limit is no longer a limit of the file’s size, but its own limit. As you can see from the InnoDB's official documents, the maximum limit of its tablespace is 64TB, that is to say, InnoDB's single table limit is basically around 64TB. Of course, this size includes all indexes of this table and other related data.

Independent tablespace: All table data and index files of a certain database are placed in one file. By default, the file path of this shared tablespace is in the data directory. The default file name is: ibdata1, which is initialized to 10M

2. Check tablespace of database242.png
My local library is pretty small, and my tablespace is mainly composed of one file: ibdata1. There will be multiple files if the local library becomes larger. The size of each file is 10M. When all files are full, ibdata will automatically expand:

When the current storage space is full, you can add data files to other disks. The syntax is as follows:

If you use the autoextend option to describe the last data file, when InnoDB runs out of all free tablespace, it will automatically expand the last data file with an increment of 8 MB each time. For example:

Both a shared tablespace and an independent tablespace have the innodb_data_file file, because these files not only store data, but also serve as some roles similar to ORACLE's UNDO tablespace.

3. Advantages and disadvantages of shared tablespace

Since InnoDB has shared tablespaces and independent tablespaces, there must be different but reasonable application scenarios of the two tablespaces. Here are some MySQL official introductions:

Advantages of shared tablespace

The tablespace can be divided into multiple files and stored on each disk, so the table can be divided into multiple files and stored on the disk. The size of the table is not limited by the disk size (while the descriptions of many documents are wrong).

Data and files are put together for easy management.

Disadvantages of shared tablespace

When all data and indexes are stored in one file, although a large file can be divided into multiple small files, multiple tables and indexes are mingled in the tablespace. When the amount of data is very large, there will be a lot of gaps in the table space after a large number of deleting operations in the table, especially for statistical analysis. It isn’t suitable for such applications with frequently delete operations to use shared tablespace.

The shared tablespace cannot be retracted after allocation: After building a temporary index or creating a temporary table, the tablespace is expanded. Then, you cannot retract that part of the space, even if the related table is deleted (it can be understood as the Oracle tablespace is 10G, and only 10M is used, while the operating system shows that the tablespace occupied by MySQL is 10G). Consequently, the cold backup of the database is very slow;

4. Advantages and disadvantages of independent table space

Advantages of independent tablespace

Each table has its own independent tablespace, and the data and indexes of each table will be stored in its own tablespace, so that a single table can be moved in different databases.

The space can be reclaimed (except for the drop table operation, the table space cannot be reclaimed by itself). Drop table operation automatically reclaims table space. As for statistical analysis or daily value tables, after deleting a large amount of data, you can retract space by: alter table TableName engine=innodb.

If you are using the InnoDB with innodb-plugin, you can also retract space by: turncate table.

For tables that use independent tablespaces, the fragments of the tablespace will not seriously affect performance, no matter how they are deleted. Also, there are still opportunities to deal with them.

Disadvantages of independent table space

The single table increases too much. When the single table occupies too much space, if the storage space is insufficient, you can only consider the solutions on the operating system level;

5. Conversion between shared tablespace and independent tablespace

Check the tablespace management type of the current database.

On stands for independent tablespace management. OFF stands for shared tablespace management. (To check the tablespace management type of a single table, you need to check whether each table has a separate data file)

Modify the tablespace management type of the database

Modification of the parameter value of innodb_file_per_table would work, but the modification cannot affect the shared tablespace and independent tablespace that have been used before.

The method of converting a shared tablespace into an independent tablespace (Set innodb_file_per_table=1)