Advantages and disadvantages of MySQL independent tablespace

I believe everyone has some knowledge about the MySQL standalone tablespace. The following describes the advantages and disadvantages of the standalone tablespace in MySQL. I hope you can learn more about the standalone tablespace in MySQL.

Independent tablespace:

In the configuration file my. cnf), set innodb_file_per_table.

Advantages:

1. Each table has its own independent tablespace.

2. Data and indexes of each table are stored in its own tablespace.

3. A single table can be moved in different databases.

4. space can be recycled. Except for the drop table operation, the table cannot be recycled if it is empty)

A) The Drop table operation automatically recycles tablespaces. For statistical analysis or daily value tables, you can use alter table TableName engine = innodb to delete a large amount of data and reduce unnecessary space.

B) using turncate table for innodb-plugin will also shrink the space.

C) For tables that use independent tablespaces, no matter how they are deleted, the tablespace fragments will not seriously affect performance, and there is a chance to process them.

Disadvantages:

The increase in a single table is too large, for example, more than 100 GB.

If a single table grows too much, you can separate the files using the shared tablespace, but there is also a problem. If the access range is too large, multiple files will be accessed, it will be slow. There is also a solution for standalone tablespaces: to use a partition table, you can also move that large tablespace to another space and then make a connection. In fact, from the perspective of performance, when a table contains more than 100 GB, the response may be slow, and it is easy to detect problems in the independent tablespace.
 

Case sensitivity of mysql Databases

Restoration of MySQL MyISAM Table Structure

MySQL multi-Table Union query syntax example

Alternative usage of MySQL table alias

MySQL left join query Experience Summary