What are the advantages and disadvantages of MySQL InnoDB independent tablespace schema?

In this issue, the editor will bring you about the advantages and disadvantages of MySQL InnoDB independent tablespace mode. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Advantages of File-Per-Table:

1. When the table is deleted or truncate, the space can be reclaimed by OS.

2.Truncate table executes faster on a single .ibd file.

3. You can specify a specific storage for each table. Optimize IO, space management. CREATE TABLE... DATA DIRECTORY = absolute_path_to_directory.

4. Run OPTIMEIZE TABLE, compress or rebuild to create tablespaces. Running OPTIMIZE TABLE InnoDB creates a new ibd file. When finished, the old tablespace will be replaced by the new one.

5. You can move a single table without having to move the entire database

6. You can copy the table to another instance

The Barracuda file format cannot be used until 7.innodb_file_per_table is started.

8. Tables with BLOB,TEXT using dynamic row mode can be stored more efficiently.

9. The use of innodb_file_per_table can improve the success rate of recovery and reduce the recovery time of damage errors.

10. You can quickly back up and restore a single table.

11.innodb_file_per_table can remove a table from a backup

When 12.innodb_file_per_table backs up and copies tables, it is easy to observe the status of each table.

13. You can observe the size of the table directly through the file system.

14. When innodb_flish_method is set to O_DIRECT, the linux file system usually does not allow concurrent writes to the same file. There is a performance improvement with innodb_file_per_table.

15. If innodb_file_per_table is not enabled, the data is placed in the system tablespace, the maximum 64TB, and each table can be 64TB if you use innodb_file_per_table.

Disadvantages of File-Per-Table:

1. The space in the table space can only be used by this table

The 2.fsync operation must be run on each table

3.mysqld must keep an open file handle, and too many tables can affect performance.

4. It consumes a lot of file descriptions.

5.innodb_file_per_tablezhiyou 5.6.6 or later is only available, with backward compatibility issues.

6. If many tables grow, there will be file fragmentation problems. Causes degradation of drop table and table scan performance.

7. The buffer pool is scanned when the drop table is used, which is time-consuming if it is too large.

8.innodb_autoextend_increment specifies the space to grow when the file is full.

These are the advantages and disadvantages of the MySQL InnoDB independent tablespace schema shared by the editor. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.