Today we are going to start learning about InnoDB, another very important storage engine in MySQL. Talking about InnoDB, we have to talk about another important topic, table Spaces and shared table Spaces, which is the subject of this article.

Independent and shared table Spaces

For the InnoDB storage engine, it can store each table in a separate table space, tablename.ibd file. You can also store data in a shared ibData tablespace, usually named ibdataX, followed by X as a specific number. Let’s experiment and see how the two differ. To check whether a separate tablespace is enabled, run the following code:

show variables like 'innodb_file_per_table';
Copy the code

As you can see, independent table Spaces are enabled by default. Create a table using InnoDB engine (default storage engine InnoDB) :

create table book(id int,name varchar(255));
Copy the code

After creation, we go to the directory where the data files are stored, and we can see the following files:

As you can see, the current table is stored in a separate book.ibd file.

Each table in a separate tablespace will be stored as a separate file. Each table will have an.frm table description file (which is the same as the MyISAM engine) and an.ibd file, which contains the data contents and index contents of a separate table. By default, its location is also in the table location.

Mysql > alter innodb_file_per_table = OFF; mysql > alter innodb_file_per_table = OFF

After closing, create a new table:

create table book2(id int,name varchar(255));
Copy the code

Ibd = book2.ibd; ibd = book2.ibd; ibd = book2.ibd;

Where are the ibDATA1 files that share the table space? MySQL > select * from ‘MySQL’;

To check the default ibdata1 size, run the following command:

show variables like 'innodb_data_file_path';
Copy the code



As you can see, the default size is 12M.

Innodb_file_per_table can be modified before the table is created. Innodb_file_per_table can be modified after the table is created without affecting the existing table structure.

Which one should I use?

To figure out which table space to use, we need to understand the characteristics of the two different table Spaces.

Independent table space

advantage

  • Each table has its own independent table space.
  • Each table’s data and indexes are stored in its own table space.
  • It is possible to move a single table across different databases (because each table has a separate data table file).
  • Space can be reclaimed (via the optimize Table command).
  • No matter how you remove it, tablespace fragmentation does not significantly affect system performance.

disadvantages

  • The number of single tables is too large.

Shared table space

advantage

  • You can divide a tablespace into multiple files and store them on each disk (the size of a tablespace file is not limited by the size of the table, for example, a table can be distributed among different files).
  • Data and files are kept together for easy management.

disadvantages

  • All data and index to a file, this would mean that there is one big file exists (although you could take a large file into multiple small files), but multiple tables and indexes mixed stored in table space, a lot of delete operations such as a table after table space will have a lot of space, especially for statistical analysis, log system in terms of such applications, Shared tablespaces are the worst (for example, when the system is running out of space and we want to free up some tablespaces by deleting some invalid data, we find that if we use shared tablespaces, the tablespace will not shrink even if the invalid data is deleted).
  • A shared tablespace cannot be scaled back after being allocated. When a temporary index is created or a temporary table is created, the tablespace cannot be scaled back even if the related table is deleted.
  • For the second problem, you can use mysqldump to export data, delete shared tablespace data files, and import them again.
  • Because multiple table data is stored in a file, concurrent operations can cause IO bottlenecks, so scenarios requiring frequent writes are not suitable for shared table Spaces.

In fact, independent table Spaces have been the default option since MySQL5.6.

The migration

Finally, if you create a shared table space initially, can you migrate to a separate table space? Of course you can! Refer to the following four steps: Use mysqldump to export all database table data. Stop MySQL service, change innodb_file_per_table parameter, and delete InnoDB related files (if master/Slave, you can do this from Slave). Restart MySQL server and rebuild InnoDB shared tablespace (no data in it). Re-import data. InnoDB shared table space and independent table space